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!
Want to understand how Tableau works and how you can use it like a master?
Joshua Milligan is a three-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. He is a principal consultant at Teknion Data Solutions, where he has served clients in numerous industries since 2004.
Joshua served as technical reviewer on several Tableau titles and is the author of the book Learning Tableau.