Export to BigQuery

How to Export Google Analytics Data to BigQuery

/in /by

Google has announced that Universal Analytics will be sunsetting on July 1, 2023.  Per their website, “On July 1, 2023, standard Universal Analytics properties will no longer process data.” On top of the challenge of learning an entirely new analytics platform in GA4, digital marketers are also facing the daunting prospect of losing their historical Universal Analytics data.  Google states:

  • Until July 1, 2023, you can continue to use and collect new data in your Universal Analytics properties.
  • After July 1, 2023, you’ll be able to access your previously processed data in your Universal Analytics property for at least six months. We know your data is important to you, and we strongly encourage you to export your historical reports during this time.

While the exact data deletion date is not yet announced, Google is encouraging advertisers to take action and export their historical data in anticipation of this date.

The “exporting” process is unfortunately easier said than done. Google recommends exporting data in Excel/CSV files, but when we tried to do that for our clients, we quickly realized that that process would not be feasible.  Specifically, we ran into the following issues:

  • To pull unsampled data for multiple years, we had to run hundreds of smaller reports and stitch them together.
  • Attempting to stitch together hundreds of reports took significant time (too much time to complete), and it ultimately crashed Excel and exceeded Google Sheets’ data limits.
  • Most exported data for long date ranges was sampled, making it highly inaccurate.

Ideally, we’d be able to export and download our historical Universal Analytics data directly to Google’s cloud-based data warehouse, BigQuery.  Unfortunately, only paid GA360 accounts have API access to BigQuery (this will become a standard GA feature in GA4), so in standard analytics, data needs to first be extracted into some other type of file format like CSV, TSV or Excel before it can be uploaded to Google BigQuery.  That leaves us right back where we started and facing the issues listed above.

Terrified at the prospect of losing all of our hard-earned data, we’ve spent the last six months working to develop a solution that can export accurate and complete data to Google BigQuery in automated fashion.

We’re excited to announce the launch of our new Analytics Data Extractor (ADE), which:

  • Accurately backs-up 5 years of data with no-sampling and 100% data accuracy.
  • Archives and store data in a cloud-based database (Google BigQuery).
  • Links archived historical GA3 data directly to Google’s Looker Studio (formerly Google Data Studio), where both pre-formatted and custom reports (with Excel exports) will be available.

For more information on how to back-up your Google Analytics data and store it in BigQuery, visit our Analytics Data Extractor website here!