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:
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:
We give it a name that begins with
fivetran_ such as
Next, we grab the Google Sheet ID from the URL:
And paste that into Fivetran:
Authorize Fivetran to access Google Sheets:
Fivetran will then kick off the initial sync:
And then shoot you an email when it’s done:
Now over in BigQuery, we can check to verify the table is synced:
Step 1, complete.
Next, lets create this chart in Looker
We’ll tell Looker we want to create a view from a table:
I had to click the Refresh button to get the Google Sheets table to appear:
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:
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.