Using Fivetran’s Google Sheets Integration to Get Monthly Unique Visitors Into BigQuery and Looker

fivetran-google-sheets-bigquery-looker.png

At Help Scout we use Fivetran to get data from various data sources (like our internal MySQL database, HubSpot, and Salesforce) into BigQuery so that we can analyze it all using Looker.

One of the challenges I’ve run into is that even though Fivetran supports Google Analytics, the data is limited to what’s available via the Google Analytics API. One of the main metrics we want – Unique Visitors – is available, but only on a 1-day, 7-day, 14-day, or 30-day basis:

Screen Shot 2017-12-05 at 9.41.27 AM.png

This means that if we want to know how many Unique Visitors we had each month, there’s no way to get that out of the Google Analytics via its API, which means Fivetran can’t grab it and automatically put it into BigQuery, which means we can’t have it in Looker.

Enter Fivetran’s Google Sheets integration

Fortunately, there is a hacky way to do it by taking advantage of Fivetran’s Google Sheets integration. Big picture, we’re going to manually export monthly Unique Visitors from the Google Analytics UI, put that data into a Google Sheet, then configure Fivetran to pull that data out of the Google Sheet and put it into BigQuery so we can analyze it with Looker. Here’s how the setup works:

In our Google Sheet, we select the range of data we want then go to Data > Named ranges… to give it a name:

Screen Shot 2017-12-05 at 9.48.53 AM.png

We give it a name that begins with fivetran_ such as fivetran_monthly_unique_visitors_test:

Screen Shot 2017-12-05 at 9.51.44 AM.png

Next, we grab the Google Sheet ID from the URL:

Screen Shot 2017-12-05 at 9.54.01 AM.png

And paste that into Fivetran:

Screen Shot 2017-12-05 at 9.54.52 AM.png

Authorize Fivetran to access Google Sheets:

Screen Shot 2017-12-05 at 9.55.39 AM.png

Fivetran will then kick off the initial sync:

Screen Shot 2017-12-05 at 9.56.14 AM.png

And then shoot you an email when it’s done:

Screen Shot 2017-12-05 at 10.04.31 AM.png

Now over in BigQuery, we can check to verify the table is synced:

Screen Shot 2017-12-05 at 10.05.29 AM.png

Step 1, complete.

Next, lets create this chart in Looker

We’ll tell Looker we want to create a view from a table:

Screen Shot 2017-12-05 at 10.07.07 AM.png

I had to click the Refresh button to get the Google Sheets table to appear:

Screen Shot 2017-12-05 at 10.07.56 AM.png

Before tinkering with the view, here’s what the Google Sheet model might look like:

And here’s the view:

There is some hacky stuff going on here so let me explain what’s what:

  • In the Google Sheet, our dates are formatted like “1-Jan-16″. Looker has no idea what to do with that out of the box, so we need to use Standard SQL’s PARSE_DATE function to tell it how to parse it. The dimension expects a timestamp so we then need to cast the date to a timestamp. And because we’re only ever going to be using months here, we can get rid of the other date/time options leaving just months.
  • Similarly, the spreadsheet has numbers like “743,353″. This string isn’t a valid number, so we use REPLACE to eliminate the commas before casting it to an integer.
  • You might be wondering about the measure: in order to use this value in a chart, it has to be a measure, not a dimension. A hacky way to do that is just to use the measure type of max (though min or average or others will do too) which will just grab the maximum value for that date range – and since this value is the only value in the date range, it just returns the number of monthly uniques.

Back in Looker we can now create the chart:

Screen Shot 2017-12-05 at 10.24.28 AM.png

The main downside to this is of course that you have to manually update the Google Sheet if you want the chart to update, but for something like Monthly Unique Visitors, updating it once a month isn’t a big deal. Though if you can think of an automated way to do it, I’d be interested to learn how.

Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s