BigQuery Toolbox

While BigQuery is a lightning fast data warehouse solution, the BigQuery UI leaves a lot to wish for. Not only is it painfully slow, it is also very poorly designed with lots of white space that takes away precious screen real estate that could be used for your queries.

Enter BigQuery Toolbox.

The BigQuery Toolbox is a lightweight Chrome browser extension that I made for users of Google BigQuery. The BigQuery Toolbox comes with a set of features designed to make your live in BigQuery a lot easier. Top features include:

  • Toggle the “Explorer” side panel with the click of a button
  • Toggle the “Query Results” with the click of a button
  • Show (smaller, redesigned) query tabs in multiple rows (if “Editor Tabs” is enabled)
  • Hide the top toolbar (“Features & Info” / “Shortcut” / “Disable Editor Tabs”)
  • Minimize the “Compose new query” button

The idea for the BigQuery Toolbox was born when I felt annoyed by the huge “Explorer” side panel that shows your projects, data sets and tables, but takes up a huge part of your screen and cannot be minimized.

read more

Introducing NaviChat

Since Chatbots are the so hot right now, I decided to have another go with Chatfuel and build what the world will remember as … the Navibot, the world’s first chat-based navigation bot! Then, however, I typed that into Google and found out that Samsung already has a vacuum cleaner called NaviBot, which doesn’t even navigate. Lame.

Anyway. I then decided to build what the world will remember as NaviChat, the world’s first chat-based navigation bot.

That is probably not true, but anyway, my main mission was to play around a bit with the Google Directions API and the Google Places API and see what they can do in terms of navigating the public transport network.

Give it a try by going to on your mobile with Facebook Messenger installed.

Both the Google Directions API and the Google Places API are part of the Google Maps Platform, which in turn is part of the Google Coud Platform.

(to be continued)

read more

Customizing posts with the new Facebook API update

In an attempt to fight misleading news on their platform, Facebook has recently changed the way you can use their API to edit previews of posts. While before you could specify the header, preview text, and the image, this is no longer possible. Instead, Facebook picks the relevant information from the website that the post links to.

This article presents a solution to continue to post customizable post previews for certain contexts.

Facebook takes action to fight fake news

To fight misleading posts with false preview texts and misleading images,  Facebook has started to disable the ability for Pages to edit the previews of the links they post – both for the Page composer and the API.

Says Facebook (July 18, 2017):

By removing the ability to alter link metadata (i.e. headline, description, image) from all link sharing entry points on Facebook, we are eliminating a channel that has been abused to post false news. (…) Specifically all API versions will no longer support this capability, and Page admins will no longer be able to make these edits in Page composer. (source)

Now I’ve been doing exactly that with Jazzity: Everyday a cron job would run a script that took a few snippets from a data base and post them to Facebook using the API:

postPicture($post_message, $post_link, $post_picture, $post_name, $post_caption, $post_description);

The function postPicture (we’ll go into more details later) would take six parameters, which will make up the post as follows:

read more

Introducing Jazzity – Your pocket guide to jazz in Berlin

A while ago I moved to Berlin. Being a huge fan of live jazz music I was really excited to move to the jazz capital of Europe, as Berlin is frequently called.

Berlin has a lot of amazing jazz clubs, from well-known and established jazz clubs such as the A-Trane, the b-flat or the Quasimodo, to smaller and less well known clubs such as the Donau 115, the Hangar 49 or the Hat Bar.

I quickly came to realize, however, that there was no good overview of all jazz clubs in Berlin. There was no convenient way to know which club was playing what and when, or when there were jam sessions at which club. I decided to change that.

Enter Jazzity.

read more

Calculating your Marketing ROI – a practical guide

Calculating your Marketing ROI sounds super simple, right? You just take the income, divide it by the marketing expenses, and boom! – there’s your marketing ROI. Super simple – or is it?

Well, the truth is, when you google “marketing roi” you will find that there is a huge number of different definitions out there. So many, in fact, that Forbes Magazine has called this situation “ROI Anarchy”.

In this article, we’ll look at what a marketing ROI calculation should take into account in order to deliver meaningful insights to evaluate a campaign.

read more

How selection bias will skew your activity mailing results

Here’s an interesting thing that I came across when evaluating a mailing for a client that I need to share.


The observation

So what we’re looking at here is the revenue for a given group of customers from 06/2014 to 12/2014. (Note that the data presented here is completely fictional recreated in a spreadsheet, but follows patterns similar to the original data. See below for more details.)

This group of customers had been selected due to its inactivity as defined by the revenue in 06/2014 being smaller then a certain threshold (again, the actual selection was a lot more refined that is described in this model).

The mailing was sent out on 01/07/2014 to the group of customers. So we’re looking at a total revenue of $46,837 in 06/2014 and a subsequent jump in revenue to $271,950 in 07/2014.

At first sight, that seems to be great news – the mailing worked, revenue increased drastically, everything fine.

However, when I looked at preceding months, the following pattern emerged:


So our group of customers had a high revenue 01/2014 – 05/2014.  Then in 06/2014 – the month that was used to determine whether or not a customer was active – the revenue suddenly drops by about 80%, only to be back at the original level the following months.

Now this seems rather odd. In fact, it looks a lot like there’s something wrong with the analysis.

But as it turns out, it’s actually perfectly correct. What we’re observing is due to what I call the selection bias.

read more

How to tell if your results are significant – a practical guide

Marketers frequently face a situation like this: In a survey it is found that 57% of women prefer product A, while 60% of men prefer product B.

In this article I will show how marketers – using only simple statistical analysis tools available in Microsoft Excel – can quickly and easily decide whether or not they can draw meaningful conclusions from such a result, or whether they may be making fatal mistakes by interpreting random noise as valid data.

stats mofo

Marketers frequently face a situation like this: In a survey it is found that 57% of women prefer product A, while 60% of men prefer product B.

Some marketers will just go “Great, statistics prove that women prefer product A, and men prefer product B. We’ll market product A to women then and product B to men.”.

But is this really always a valid conclusion? Couldn’t it also be that the difference is purely coincidental? After all, we haven’t asked all people, but only a subset of people: those participating in our survey. So maybe if we took another sample, and asked different people, the results would be different? May well be!

Statistics to the rescue!

As is often the case, statistics can provide a solution. Before delving into the details, let’s look at another, more formalized example. Dice!

Suppose we take two dice, and we want to know if one of them is loaded, i.e., we want to know if one of the dice yields better values than the other. Let’s start by throwing them 10 times each. This is what the results may look like:

Example 10 dice

Well. What do we get? Let’s look at the mean value for each die. As a reminder, if the two dice were fair dice, there would be an equal likelihood of one in six for each number to turn up. More formally, the expected value would be 3.5 (=1/6*1+1/6*2+…+1/6*6).

So what do we get for our dice? For die 1, the mean is (4+4+4+…+3)/10 = 4.40, the mean for die 2 is (5+2+1+…+1)/10 = 3.20.

So is die 1 better than die 2? Well the average is higher, of course, but as you will intuitively suspect, 10 throws is quite a small number of throws to draw any meaningful conclusions.

read more

Why the new Spotify UI makes no sense. A rant.

A while back I’ve been whining about the lack of a decent music collection management Spotify. In the past months, Spotify has worked quite a bit on their UI and have changed their interface a lot. Sometimes for the better, sometimes for the worse. Bottom line however: the music collection management is still freaking useless.

Disclaimer: the following contains a lot of SHOUTING and CURSING. Sorry about that. But I’m really really passionate about my music player, and just hate to see that Spotify, which I otherwise LOVE, can’t seem to get their sh*t together.

So here we go: First, there’s the SONGS tab:

read more partners up with Spotify to offer on demand plays

Spotify Last.fmGreat news! partners up with Spotify to again offer on-demand plays:

We’ve teamed up with Spotify to bring their entire catalogue, on demand, to the world’s leading music recommendation service.

About two years ago, had dropped their on-demand plays, to “focus on the features that make unique — scrobbling, personalised radio, and being the online home for your music taste”, or, put differently, CBS wanted to cut the costs so they decided to save the royalties.

Well I’m glad they’ve finally come up with this solution! The integration works pretty well, too: if you hit the play button on, Spotify is opened in the background to play the track. Smooth. What’s great, too, ist that on a “Top Tracks” list, such as the one below, the entire list is put in a Spotify-playlist.


read more