• Google Analytics in Tableau: Blending Data From Multiple Accounts

    (This is one of the most popular blog posts here… but it was written when Cuban cigars were still under embargo… so check out a 2016 approach for Google Analytics data in Tableau!)

    The Tableau Google Analytics connection only supports one account.  What if you track multiple websites and you want to be able create Tableau visualizations that combine data from different accounts?

    For example, what if you have a separate Google Analytics account for CNN.com, FoxNews.com, and ABCNews.com (this is a fictitious example) and you wanted to compare website visits like this:

    I'm sure these websites get quite a few more visits per month.

    You could pull data from each account and combine it all in a central database or data warehouse.  You may want to consider that approach in the long-term.  But, for now, what if you just want the ease of using the built-in connection?

    Tableau has an answer: Data Blending

    Data blending has been around for a long time – it was there before the Google Analytics connection.  It has always been powerful; but now with cloud based connections that cannot be joined in any other way, data blending is amazing.  There are just a few complications…

     

    1. You’ll need your own set of master data.

    When using data blending, you must have a primary data source.  And the primary data source will define the domain of values for dimensions.  So, for example, if you used the Google Analytics connection for ABCNews.com as your primary data source and there is no data for May, then when you blended in the other connections no data will be displayed for May (even if the other connections have data for May).

    To get around this, use your own data set that contains all the possible values (Tableau Jedi and Zen Masters call this scaffolding data).  I’ve used an Excel document with a tab containing the name of each account and then another tab for each dimension I’ll be using from Google Analytics (in this case, I’ve narrowed down to date as the only dimension)

     
    Note that I’ll include every date (at the day level) that I want for reporting.
    The JoinID is key, because when you connect to the Excel document with Tableau, use the Multiple Tables option and join every table on the JoinID (since every value is 1, this is the equivalent of a cross join).

    This will be your primary data source.

    2. Create a Google Analytics Connection for Each Account

     

     

     

    Create a connection for each Google Analytics Account.  Include only the dimensions you have in your master data and whatever measures you need.  Use the same dimensions and measures in each connection.

    These will all be secondary data sources.

     

    3. Create a Calculated Field to Link Account

    In each account, create a calculated field named [GA Account] hard-coded to the name you had in the master data.  For example, the [GA Account]  field in the ABCNews.com connection looks like this:

    4. Create some final calculations and start building the visualization!

    Make sure the fields are linked correctly:

      

    For any measure, create a calculated field in the Master data connection that is simply the aggregate of the measure from the blended source.  Then create a final calculation that sums them together.

     

     

    Now you’ve got everything you need!  You’ll only use fields from your Master data set.   you are ready to start building visualizations using data from multiple accounts!

6 Responsesso far.

  1. Shawn says:

    Nicely done Joshua! Thx.

    –Shawn

  2. Scott says:

    I’m trying to include the dimensions medium and keyword (not just date like in your example). I went through my 5 data GA data accounts and made an exhaustive list of all the unique values of mediums and keywords. Mediums has like 8 values and keywords has like 65,000. Once all the connections are established like you explained and I attempt to add date my Tableau instance just gives me calculating view… indefinitely. I let it run 6 minutes and then canceled. Guessing I created some sort of crazy join (cartesian maybe?). Any thoughts?

    • Scott says:

      It says Executing query, not calculating view…

      • Joshua Milligan says:

        Scott, if you have lots of dimensions or a many distinct values of a dimension (high cardinality, e.b. 65,000 keywords), then this approach probably won’t work well. Your getting a Cartesian product, so lots and lots and lots of rows that may use up your memory before they ever generate a view.

  3. Hi Joshua,

    After extensive research your blog post kind of saved my life. Only kind of unfortunately since I´m using Tableau 10 and all looks different 🙂

    I followed your instructions but am now stuck on the joining of Excel tables. The mentioned option “Multiple Tables” is what I can´t find.
    How would you join the Excel sheets nowadays? Or is there an even better way now to connect more than 1 GA accounts?

    I´d be eternally grafteful if you´d still answer this old post of yours! 🙂

    Regards from Germany,
    Stefanie

  4. Sara Robaty says:

    Hi

    I wonder if there is a way to blend GA data with our marketing analytic tools ?

    Thanks

Leave a Reply

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