• 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.

    Back in my day - Google Analytics

    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

    Google Analytics Addons

    • Locate the Google Analytics addon and add it.

    Google Analytics addon for Google Sheets

    • 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”

    Create Google Analytics 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:

    Google Analytics Report Configuration

    • 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:

    Union Together Google Analytics Data

    • 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:Excess data in Google Analytics Report
    • So, just add a data source filter to Exclude NULL values for column F3.

    Filters

    • 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

     

8 Responsesso far.

  1. Dan says:

    This is potentially incredibly useful but is there any way to make it bring back more than 1,000 rows?

    • benmelek_admin says:

      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.

  2. Kelly says:

    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

    • benmelek_admin says:

      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.

  3. Frederic says:

    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…

  4. Mark says:

    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…

Leave a Reply

Your email address will not be published. Required fields are marked *