fbpx

Google Analytics BigQuery Export Part One: Why Export Google Analytics Data?


June 12, 2019

Google Analytics 360 (GA 360) lets you track the performance of websites and applications, and measure advertising ROI. It comes with several stock reports sufficient to measure most basic marketing initiatives. It’s part of the Google Marketing Platform, which also includes a tag manager, an analytics dashboard and a tool to optimize websites based on GA 360 data. The one thing it lacks is hit-level data for complex analysis and custom integration with other databases or products which may not belong to Google.  

Enter BigQuery (BQ). Google BigQuery is a data warehouse that delivers super-fast results from SQL queries, which it accomplishes using a powerful engine dubbed Dremel. With BQ, there’s no spinning up (and down) clusters of machines as you work with your data. With that said, it’s clear why some claim that BQ prioritizes querying over administration. It’s super fast, and that’s why most people use it.  

Overcoming Limitations with BigQuery

For organizations that pay for GA 360 and want to answer complex marketing funnel questions and perform further reaching analytics like which events most influence users and lead to goal conversion, hit and event level data is required. However, it’s not possible to report or query hit-level data from GA 360 directly. The GA APIs are only capable of retrieving aggregated data similar to the web user interface reports.

GA 360 does the option to automatically export data to BQ for analysis, (read about 10 example benefits here). Follow the steps here to setup the export. Once the linkage is complete, data should start flowing to your BQ project within 24 hours. One file will be exported each day that contains the previous day’s data, and three files will be exported each day that contain the current day’s data. A historical export of the smaller of 10 billion hits or 13 months of data will  be available within four weeks after the integration is complete.

Exploring Google Analytics Data in BigQuery

Once the data is in BQ, there are many ways to explore it. For programmatic analysis we recommend using datalab. See this post for instructions. Other options are to use the BQ API to move, manipulate, delete and analyze the data. The most common method for analysis is to use the BQ web user interface though and write SQL queries (recipes).

 

 

The above figure is annotated as follows:

  1. The dataset number represents the viewID linked for export from GA. The screenshot below shows where the viewID can be found in GA

 

 

  1. A daily table (ga_sessions_YYYMMDD) is produced with yesterday’s data on a daily basis. Today’s data is exported three times per day to a table (ga_sessions_intraday_YYYYMMDD). The number after the table name represents the number of tables (days) in that table set.
  2. Multiple views can be linked for export from GA to BQ.
  3. Use #standardSQL dialect.  The recipes below are in standard SQL format.  The other option is #legacySQL, which still works but will not be supported one day.
  4. The SQL query development pane
  5. The SQL query results.

Stay tuned for our next post, “Google Analytics BigQuery Export Part Two: Example SQL recipes” to learn how to get the most from your GA BQ export.