With the end of Google Analytics Universal Analytics on the horizon, it's time to determine how you're going to maintain consistency in your reports after July 1st, 2023. While it's not possible to load your GA3 data into GA4, you can load data from both applications into a database and UNION similar metrics together. This will allow you to create a dashboard with access to both your current data collected from GA4 and all of your historic data from GA3, making it easy to do comparisons against previous time periods.
To make this possible, you will need the following:
- A Google Cloud Project account
- A Google Analytics 4 account with a BQ datastream linked to your GCP account
- A data transfer tool or ETL with a source integration to Google Analytics Universal and a destination integration to Google BigQuery
- A dashboard application that can connect to Google BigQuery
Loading Data into Google BigQuery
Once you have established the Google BigQuery linking in your Google Analytics 4 account, you will see a new dataset populated the following day with a table named “Events”. The Events table contains all of the events that GA4 recorded on your website for the corresponding day.
Once you have your GA4 data populated in Google BigQuery, the next step is to transfer your Google Analytics Universal data. For the purposes of this post, we are going to use a basic list of dimensions and metrics for our schema, but we recommend you do a robust transfer of all the dimensions and metrics you might want to evaluate after the Google Analytics Universal platform is sunset at the end of 2023. You can find a list of the most popular Google Analytics Universal schemas being used to archive Google Analytics Universal Data here
Here are the Google Analytics Universal dimensions and metrics to use in Launchpad's schema builder for this example:
Dimensions
- date (included by default)
- source
- medium
- campaign
- device
Metrics
- transactions
- revenue
- quantity
Note: Make sure the date selection in your settings screen loads data up to the first day of data in your GA4 dataset.
After you have transferred the Google Analytics Universal Data to BigQuery, you can use the following query to UNION your Google Analytics Universal dimensions and metrics with the same data in your new Google Analytics 4 dataset.
Example Query:
SELECT
PARSE_DATE("%Y%m%d", event_date) AS date,
traffic_source.source AS source,
traffic_source.medium AS medium,
traffic_source.name AS campaign,
device.category AS device,
SUM(ecommerce.purchase_revenue) AS revenue,
SUM(ecommerce.total_item_quantity) AS quantity,
COUNT(ecommerce.transaction_id) AS transactions
FROM `add-your-project-id.analytics_your-add-dataset-id.events_2023*` /* Use an asterisk to indicate a wildcard over your date partitioned events tables */
WHERE event_name = 'purchase' AND PARSE_DATE("%Y%m%d", event_date) BETWEEN(2023,01,01) AND CURRENT_DATE() - 1 /* adjust your dates to reflect the start of your GA 4 data */
GROUP BY
date,
source,
medium,
device,
campaign
UNION ALL
SELECT
etl_date AS date,
source,
medium,
campaign,
devicecategory AS device,
SUM(transactionrevenue) AS revenue,
SUM(itemquantity) AS quantity,
SUM(transactions) AS transactions
FROM `add-your-project-id.analytics_your-add-dataset-id.your_table_name_2022*` /* Use an asterisk to indicate a wildcard over your date partitioned events tables */
WHERE etl_date BETWEEN DATE(2022,01,01) AND DATE(2022,12,31) /* adjust your dates to reflect the desired start date of your GA Universal data and the day before your the start date in your GA 4 query */
GROUP BY
date,
source,
medium,
campaign,
device
Save the query as a view (or create a scheduled query) and connect your dashboard app to the query and/or table in Google BigQuery.
Voilà! You now have a chart with Google Analytics 4 data beginning January 1st, 2023 and Google Analytics Universal (GA 3) data as the previous year comparison.
Need Help?
If you're having any trouble automating your reports, then the experts at Calibrate would be happy to help. From walk-throughs and interactive demos to custom solutions, we'll help you set up a healthy analytics stack that fuels smart business decisions.
