Not matching results from GA4: campaign_name and purchase_revenue in BQ

[ric_99]

Hello there! 

So lately I've been trying to pull out the data from campaign_name and purchase_revenue_value in one table but the data displayed is wrong. Althought I get different campaign names in my columns, most of them have null values. I've tried different queries but every time I get the same error, does anyone has faced this escenario before? 

[ms4446]

There are a few reasons why your GA4 campaign_name and purchase_revenue data in BQ may not be matching.

First, make sure that you are querying the correct dataset and table. GA4 data is exported to BigQuery as multiple tables, so you need to make sure that you are querying the table that contains the data you need.

Second, check to see if your campaign_name and purchase_revenue data is being collected correctly in GA4. You can do this by checking the GA4 reporting interface. If you see data for these dimensions and metrics in the reporting interface, then it is likely that the data is also being exported to BigQuery correctly.

Third, make sure that your BigQuery query is correct. Here is a simple query that you can use to pull out the campaign_name and purchase_revenue_value data from GA4:

SELECT
  campaign_name,
  purchase_revenue_value
FROM
  `project.dataset.events_*`
WHERE
  event_name = 'purchase'

Fourth, check to see if your campaign_name data is being populated correctly. If you are using UTM parameters to track your campaigns, then it is important to make sure that the campaign names are being passed to GA4 correctly. You can check this by looking at the collected_traffic_source field in the GA4 events table.

Fifth, check to see if your purchase_revenue data is being populated correctly. If you are using ecommerce tracking in GA4, then you need to make sure that the purchase_revenue_value field is being populated correctly. You can check this by looking at the ecommerce.purchase_revenue field in the GA4 events table.

If you are still having problems matching your GA4 campaign_name and purchase_revenue data in BQ, then you can try the following:

• Try using a different BigQuery query.
• Try using the GA4 BigQuery Report Builder to generate a query for you.
• Contact Google Analytics support for assistance.

Here are some additional tips for troubleshooting GA4 data in BQ:

• Make sure that you are using the correct BigQuery project and dataset.
• Make sure that you are querying the correct table.
• Make sure that your BigQuery query is correct.
• Use the WHERE clause to filter your data to the specific dimensions and metrics that you need.
• Use the GROUP BY clause to group your data by the dimensions that you want to analyze.
• Use the ORDER BY clause to sort your data in the order that you want.

[ric_99 · Accepted solution]

Hello!

Thanks for taking the time to suggest different ways to solve this problem. After conducting some research and attempting several queries, I found the solution to this issue:

The problem was that when querying 'collected_traffic_source.manual_campaign_name' for example, like this

SELECT 
collected_traffic_source.manual_campaign_name AS campaign_name,
SUM(ecommerce.purchase_revenue) AS revenue
FROM 
data_set.name
GROUP BY collected_traffic_source.manual_campaign_name

The output will be something like this: 

campaign_name	revenue
null	371928
(referral)	12127
name_campaign_1	null
name_campaign_2	null

In other words, all the campaigns within the property will have null values for revenue, except for 'referral' and 'null'. After conducting some research, I found that the correct field should have been 'traffic_source.name'. Just by making this change, I obtained the results I needed. However, I still don't know why the 'manual_campaign_name' field couldn't match the revenue per campaign as GA4 has it.

[bambi-solutions]

For others who come across this, please read the GA4 table schema to understand the difference between `collected_taffic_source` and `traffic_source`.

With collected_traffic_source, you can get the event_based and latest marketing touchpoint. traffic_source is not changing after first acquiring the user and is therefore the user_acquisition_traffic_source.

> The collected_traffic_source RECORD contains the traffic source data that was collected with the event.

> The traffic_source RECORD contains information about the traffic source that first acquired the user. This record is not populated in intraday tables.
Note: The traffic_source values do not change if the user interacts with subsequent campaigns after installation.