Google Analytics in Tableau: Blending Data From Multiple Accounts

• By Joshua Milligan
• Posted in All, Data Blending & Joining, Tips and Tricks
• On August 9, 2013
• 6 Comments.

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

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!