1. Home
  2. Knowledge Base
  3. Google Analytics [UA + GA4]
  4. [Online] + [Desktop] How to blend Google Analytics data with Analytics Canvas

Blending Google Analytics data using Analytics Canvas

This article shows you how to combine the data from the Universal Analytics API, the GA4 Data API, and the GA4 BigQuery data sources to create comparison or continuous reports. 

The article assumes you know how to use Analytics Canvas.  If you haven't yet made a Google Analytics query, start by reviewing this article from our Knowledge Base. 

This article shows examples using Analytics Canvas Online. The same approach can be taken using Analytics Canvas Desktop.  To learn more about blending these data sources together, review this article from our blog.

Continuous reports using the Reporting and Data APIs

To create a report that shows a continuous flow of data between the Universal Analytics Reporting API and the GA4 Data API, start by creating a query in UA and one in GA4 for the same property / view and the same (most similar) dimensions and metrics.  

As a starting point, we recommend making simple queries that include Date and one or more metrics.  This example shows Date and PageViews from UA, and Date and ScreenPageViews from GA4. 

Click here to learn more about which metrics can be combined between UA and GA4

Developing a Canvas that blends together the two data sources

Below is an example of a workflow developed in Analytics Canvas that blends data together.  The canvas uses Google Analytics Import blocks to bring in similar data from each data source.  It uses filter, calculate, and union blocks to prepare the data sets and combine them together. And it uses both the Looker Studio and BigQuery output blocks to publish the data for reporting and long term storage.   

Image

Steps to produce this output: 

  1. Create a UA query on your Canvas that either has a fixed start date and no end date.  This way the dataset will continue to update until UA is turned off.  The data will be retained in the subscription. 
  2. Add a Date filter to the UA query to stop the data at the point where you transition.  The filter will be something like "[Original.date] < Date(2023,01,01)", which would limit the data to 2022 and prior. 
  3. Add a calculation block to rename / remove un-wanted fields (such as the ViewID, View Name, etc).  In the Calculation block, standardize the field names.  Finally, add a calculated field called "Data Source" and label it "UA" so that you know which dataset each row of data belongs to.
  4. Create a GA4 query with similar fields.
  5. Add a calculation block and connect it to the GA4 query.  Rename the fields so that they are the same as those used in the UA block.
  6. Add a union block and combine the data from both calculation blocks.
  7. Output the data to Looker Studio, BigQuery, or Sheets.  

Visualize the result

In Looker Studio, the data is now shown in two different ways:  on the left by data source and on the right a continuous line.

Image

In this case, Canvas has extracted the UA data into your account. It will always be there, even after the UA API is turned off.  An alternative approach is to store this data in BigQuery or Sheets, and use those sources as the inputs.

Creating a continuous dataset

If you did not enable the BigQuery export when setting up your GA4 property, you may need to blend data from the API with the BigQuery export to create your reports. Unfortunately, Google will not back-fill your data and there is no way to do so using the API.

There are several reasons why the data in GA4 will not match between the API and the BigQuery export. That said, the differences are quite small and you should generally be able to blend these two together when making the EXACT SAME queries.  

What you will do is create high level summary tables and blend those together for your reporting. 

Aside from that, the process is identical to the one outlined above for blending data from UA to GA4 APIs.  

Image

Steps to produce this output: 

  1. Create a GA4 API query on the canvas, in this case with the fields eventName, Date, and eventCount.
  2. Connect a filter block to the GA4 API input.  Include all days up to the transition date to GA4BigQuery.  
  3. Connect a calculate block to the pass node of the filter block.  Remove all columns except for eventName, Date, eventCount
  4. Add a calculate block to the GA4BigQuery import.  Rename the SourceDataSet column to "PropertyID" and add a field called DataSource with the label "GA4 BigQuery". 
  5. Union the two calculate blocks together.
  6. Publish the output to Looker Studio, a BigQuery table, Sheets, etc. 

Visualize the result

Similar to UA, we've broken this down into two charts - one where it shows by the data source, while the other shows a continuous report. 

Image

Comparing data between Google Analytics data sources

Comparing results between these three sources is quite simple with Analytics Canvas.  As above with UA and GA4, we must be sure that we are comparing the same / very similar fields.  

To compare the data between all three data sources, we follow similar steps as above.  First, create three comparable queries.  In this case, we have only Date and Sessions from each of the three data sources. 

Image

Steps to produce this output: 

  1. Create a UA query on the canvas, in this case with the fields Date and Sessions 
  2. Connect a summarize block to the UA input.  Add the fields Date and Sessions.  on the Sessions field, rename the column to "sessions_UA". 
  3. Add a GA4 query to the canvas with Date and Sessions.  Connect a summarize block to it, including only the fields Date and Sessions, then rename the Sessions column to "sessions_GA4API"
  4. Add a join block to the Canvas and perform an inner join on the Date column. 
  5. Add a GA4 BigQuery block to the Canvas with SessionDate and Sessions.  Connect a calculation block to it and rename SessionDate to Date and including only Date and Sessions. 
  6. Add a join block to connect the results of the Calculate block to the result of the first join block.  
  7. Export the data for reporting and analysis. 

Visualize the result

Here we can see quite a difference between the UA Reporting API and GA4, however directionally they are the same.  Our own analysis shows that this is often the result of bot and spam traffic, where GA4 is doing a better job of filtering this out than UA. 

With GA4 Data API and GA4 BigQuery, the results are nearly identical - the lines are so close they can't be distinguished and in the last month, in this case, we're only off by 3 sessions - certainly nothing to chase further. 

Image
Was this article helpful?

Related Tutorials/Video