Connecting Tableau to Google Analytics BigQuery Data

Google Analytics and Tableau make for a powerful pair. Exporting your data to BigQuery is an amazing asset as a Google Analytics 360 customer. On its own, BigQuery can create and store powerful datasets. And Tableau is an excellent tool for visualizing those data sets. Combining them is incredibly helpful but, sometimes, getting started with that connection can feel challenging. Below is a step-by-step walkthrough of how to get started working with these two amazing technologies. While the example query here is minimal, the methodology gets you started and can be applied to any number of queries. It’s important to note that each tool has its own strengths. If you’re doing significant data processing, you should probably set up those tables in BigQuery first and connect to them directly vs. trying to have it all done live via a custom query.

Getting Started

Let’s start by opening up Tableau and connecting to BigQuery.

 

Connecting tableau to Big Query

 

After logging in you’ll select a project and data set, then drag the New Custom SQL to the area that says, “Drag tables here.”

 

 

Now you enter the query. If you haven’t already created the parameter, you can do so at this time.

 

SELECT

  fullvisitorid,

CAST(PARSE_DATE('%Y%m%d',_TABLE_SUFFIX) AS DATE) as table_date

FROM

  `analyticspros.com:spotted-cinnamon-834.102887025.ga_sessions_*`

WHERE

  _TABLE_SUFFIX BETWEEN REGEXP_REPLACE(STRING(TIMESTAMP(<Parameters.Start Date>)),"-","")

     AND REGEXP_REPLACE(STRING(TIMESTAMP(<Parameters.End Date>)),"-","")

 

 

With the Query in place, you’ll want to create two new parameters. These will act as your date controls in Tableau.

 

 

In the example Query, the parameters are “Start Date” and “End Date.” Be sure to set the start date before the end date. You can preview results to ensure your query will run then select OK. You’re now set to work with your data! Select Sheet 1 to see the available dimensions and metrics. To make it easier to change the date range you’ll want to display the parameter controls.

 

 

This will give you access to calendar selectors on your sheet (and dashboards).

 

 

As an example, let’s create a metric from our full visitor ID dimension and show users by Date.

 

 

All queries run via Tableau can be found in your BigQuery history.

 

 

This can be really helpful as you’re working with Tableau to ensure what’s being queried reflects what you were hoping for, as well as get an understanding of the cost impact of working with your data. You should now be all set to explore your google analytics BigQuery data in Tableau. If you have any questions about Tableau and connecting to BigQuery don’t hesitate to contact us.

2 Comments

  1. Is there something that you need to do in BigQuery admin console in order for the Dataset/tables to be available for connection? I’m not seeing my datasets/tables once I select my project.

    Reply
  2. This is such a helpful post for those of us just starting to use BigQuery and need to work with big data sets. As a VMware partner, this information will help me a better worker. Thanks for sharing you made it all seem so simple!

    Reply

Leave a Comment