Google Analytics (Universal) - Google BigQuery Backup

• Introduction
  • Example of Operation
  • Data Schema
• Configuration
  • Start creating the module
  • Authorization with Google Analytics
  • Select Metrics and Dimensions of Interest
  • Set Historical Data Retrieval Range
  • Wait for the Task to Complete

Introduction

Google has announced the end of the popular Google Analytics Universal tool - the free version of this service will no longer collect data starting from July 1, 2023. In the future, access to the panel of this version will be permanently revoked, which means losing access to previously collected historical data. If you don't want to lose access to your data, it's essential to create a backup and save it in a secure location like Google BigQuery. This module allows you to create a backup of historical data from Google Analytics Universal.

Example of Operation

1. Configure the module in the WitCloud platform.
2. The selected data will automatically be retrieved for the chosen period and saved in Google BigQuery.
3. Once the data is in Google BigQuery, you can visualize it anywhere, such as in Looker Studio.

Data Schema

This module allows you to choose an e-commerce data schema (predefined report types and tables) or define your custom table based on the metrics and dimensions available in the Google Analytics API. The data is retrieved day by day and saved in daily partitions in Google BigQuery, so you don't need to select the "date" field when defining the data schema.

Predefined e-commerce data schema:

ga_backup_source_medium_ Expand to view the data schema

field name	mode	type
transactionRevenue	NULLABLE	FLOAT
sessionDuration	NULLABLE	FLOAT
hits	NULLABLE	INTEGER
pageviews	NULLABLE	INTEGER
newUsers	NULLABLE	INTEGER
page	NULLABLE	STRING
campaign	NULLABLE	STRING
hostname	NULLABLE	STRING
bounces	NULLABLE	INTEGER
deviceCategory	NULLABLE	STRING
sessions	NULLABLE	INTEGER
keyword	NULLABLE	STRING
transactions	NULLABLE	INTEGER
adContent	NULLABLE	STRING
medium	NULLABLE	STRING
users	NULLABLE	INTEGER
adwordsCampaignId	NULLABLE	STRING
source	NULLABLE	STRING

ga_backup_transactions_ Expand to view the data schema

field name	mode	type
transactionShipping	NULLABLE	FLOAT
transactionTax	NULLABLE	FLOAT
page	NULLABLE	STRING
hostname	NULLABLE	STRING
campaign	NULLABLE	STRING
medium	NULLABLE	STRING
transactionRevenue	NULLABLE	FLOAT
transactionId	NULLABLE	STRING
deviceCategory	NULLABLE	STRING
adwordsCampaignId	NULLABLE	STRING
source	NULLABLE	STRING

ga_backup_products_ Expand to view the data schema

field name	mode	type
productRevenue	NULLABLE	FLOAT
productQuantity	NULLABLE	INTEGER
productVariant	NULLABLE	STRING
v2ProductCategory	NULLABLE	STRING
v2ProductName	NULLABLE	STRING
productSKU	NULLABLE	STRING
transactionId	NULLABLE	STRING

Configuration

Start creating the module

From the left menu, select the "Collect" tab and click on "Add new Data Collect".

From the list of available modules, choose Google Analytics.

Authorization with Google Analytics

In the first step, grant WitCloud application access to your Google Analytics service.

To do this, click on Sign in with Google. After successful authorization, choose your account, service, and Google Analytics view from the list, then click Next.

Select Metrics and Dimensions of Interest

In the second step, provide a name for the module configuration and choose the type of data you are interested in. It can be predefined e-commerce data or custom data (where you select the metrics and dimensions of interest).

The module supports the metrics and dimensions available in the Google Analytics API: link (opens new window)

For advanced needs, when configuring a custom module, you can also apply a segment or filter to the data.

Set Historical Data Retrieval Range

In the last step, determine how many days back you want to retrieve your historical data.

In the default configuration view, you can define a maximum of 730 days back from the current date. If you need a larger date range, it will be possible in the "Workflow" section after the selected data retrieval is completed.

Wait for the Task to Complete

After completing the configuration, the task of retrieving historical data from Google Analytics will be initiated. The data will be stored in Google BigQuery on your Google Cloud Platform project. You can monitor the progress of data retrieval in the Workflow -> Auto-Generated section. Look for the workflow with the "Historical" label.