Multiple Google Analytics accounts in one Tableau data source!
So, I previously wrote about how to use data blending to get data from multiple Google Analytics data sources into a single Tableau view. But that’s somewhat tedious and so 2013.
There’s now a much better way!
Using Google Sheets to track Google Analytics
It kind of makes sense that Google Sheets can talk to Google Analytics. And the great thing is that you can union multiple Google Sheets together in Tableau 10. You probably see where I’m going with this…
(And there is a caveat: you must be able to access all your Google Analytics accounts from a single login and use that to create your Google Sheets doc. You could create multiple Google Sheets docs for each account, but as of this writing, Tableau will only union together sheets from a single Google Sheets doc — but I hear 10.1 is coming soon!)
…here are some details:
- Create a new Google Sheets doc and then use Add-ons from the menu
- Locate the Google Analytics addon and add it.
- Then use the Add-ons menu to create a new Google Analytics report. Select the account, measures and dimensions you want and then “Create Report”
- This will create a Report Configuration tab in your document.
- We’ll look at the report configuration in a second, but for now, repeat the above steps to create a new report for each account you want to include in the data (you’ll want to keep the same measures and dimensions for each report). Once you do, you’ll see the configuration for each report in that single tab:
- Set the configuration however you’d like. Specifically, you’ll probably want to set Start and End dates or the Last N Days to get the data you want.
- Use the Add-ons menu to run the report. This will create a tab for each report. You now have data! And best of all, you have all the data for all your Google Analytics accounts in one Google sheets document!
- Use the Add-ons menu to set scheduling for your report. You can now have refreshed data! (Your day is just getting better and better)
- Save your document
- Now, in Tableau, connect to the Google Sheets document you just created.
- And now, for the beautiful part – Tableau 10’s ability to union together sheets of a Google document! Skip the Report Configuration Sheet, but union together all the other sheets that contain that wonderful, refreshed GA data for each one of your accounts:
- And now, you’ll have a bit of cleanup. The Data Interpreter doesn’t quite fix some of the excess summary information that Google includes in the reports. So, don’t even bother with it. But the great thing is that there is a column that is always NULL for unwanted rows:
- So, just add a data source filter to Exclude NULL values for column F3.
- And, speaking of F3, who wants that for a column name? Go ahead and rename the columns to reflect what your data really is.
And now, you have a single Tableau data source with all your Google Analytics data for all your Google Analytics accounts. And you even have Sheet and Table Name fields that will let you know which account is which! How great is that!
(By the way, this post was inspired by a question on the Tableau Forums.)
Hope it helps!
Joshua
Joshua Milligan is a five-time Tableau Zen Master. His passion is training, mentoring, and helping people gain insights and make decisions based on their data through data visualization using Tableau.
This is potentially incredibly useful but is there any way to make it bring back more than 1,000 rows?
Dan,
Not currently (though Google continues to improve things). But you can setup multiple reports to run, each indexed at 1000 row increments. Tedious, I know, but possible.
Hi Joshua,
This is awesome. Thanks for sharing this incredible post.
As I began rolling this out, I increased the Max Results row in the Google sheet up to 10,000 (the GA API limit). It ended up creating an error for me, essentially increasing the number of cells above the 2mill limit. Have you run into this error yet with any of your larger data sets?
– Kelly
Yes, I’ve seen that. And there is a work-around, but I don’t remember the specifics or where I found it (sorry). It had to do with removing un-needed cells. If I come across it again, I’ll be sure to update the post/comments.
You could also do that in 5 minutes in Alteryx: pull as many GA connectors as you need with copy paste your settings, some optional cleansing / renaming, a big Union and generate a TDE that you can automatically upload to your Tableau server, on a scheduled pattern if needed for refresh… Full disclosure: I do that for work…
Definitely!
I cant seem to get this to work for me…I have 5 domains that I want to bring into a single report, to show users (as best I can), sessions, pageviews and bounces by date only.
I can set up and run the reports in Google but when I union the sheets in Tableau I get a single data table but with the date measure from each report in its own column (the column is the name of the sheet name). Any idea what I might be doing wrong? I am guessing it is a newbie mistake…
Amazing Tips
Amazing. Thank you!
It works well until we hit the 10k result per call and 2000000 Google sheet cell limit.
Yeah, I’ve been thinking of ways to get around that. Unioning multiple files together holds some promise but still has issues
Hi Joshua – Thank you for the detailed solution. Because of the 2m Google Sheet limit, I find myself having to Union multiple Google Sheets files to support multiple time frames (quarterly) with multiple GA accounts. Would you be able to provide a link to instructions on how to Union the multiple Google Sheets that are using the above method to bring in GA data? – Thank you!