Not matching results for total revenue and manual content

[ric_99]

Hey guys,

Currently, I'm working on filtering campaign results based on the content within the UTM parameters. Unfortunately, I haven't been successful in doing so yet.

Here's the code I'm using:

WITH date_range AS (
     SELECT
        "20240202" AS start_date,
        "20240207" AS end_date
)
SELECT CONCAT(traffic_source.source,"/", traffic_source.medium) AS source_medium,
       traffic_source.name AS campaign_name, 
       collected_traffic_source.manual_content,
       SUM(ecommerce.purchase_revenue)
FROM `brandlive.analytics_11111.events_*`, date_range
WHERE _table_suffix BETWEEN date_range.start_date AND date_range.end_date
AND traffic_source.name = "monumental_3d_conversion_linkads_02_2024"
GROUP BY traffic_source.source, traffic_source.medium, traffic_source.name, collected_traffic_source.manual_content

The output is showing different values for manual_content, but  the revenue attribution should be linked to the "red" content, as specified in the UTM.

source_medium	campaign_name	manual_content	f0
meta/cpc_	leads_2024	null	27695
meta/cpc_	leads_2024	orange	null
meta/cpc_	leads_2024	red	null

Previously, I attempted something similar, trying to match fields from collected_traffic_source to the ecommerce ones. In fact, in the provided code, I'm using traffic_source.name instead of collected_traffic_source.manual_campaign_name to retrieve the campaign_name.

Is there a way to match the manual_content from collected_traffic_source with purchase_revenue from ecommerce? 

Link to my past question that its pretty similar to this one: https://www.googlecloudcommunity.com/gc/Data-Analytics/Not-matching-results-from-GA4-campaign-name-a... 

@ms4446 

[ms4446]

Hi @ric_99 ,

To address the issue of filtering campaign results based on the content within the UTM parameters and correctly attributing revenue to the "red" content as specified in the UTM, you'll need to ensure that your query accurately targets and filters based on the UTM content field. Given the structure of your query and the goal to match manual_content from collected_traffic_source with purchase_revenue from ecommerce, here's a possible approach:

• First, ensure that collected_traffic_source.manual_content correctly refers to the field where UTM content is stored in your GA4 BigQuery export. Based on standard GA4 schema, UTM parameters are typically under traffic_source. If manual_content is indeed the field for UTM content, your approach should work, but if not, you'll need to identify the correct field name.

• Assuming traffic_source.content (or the correct field name for UTM content) and focusing on filtering for "red" content specifically, your query can be adjusted as follows:

  WITH date_range AS ( 
    SELECT "20240202" AS start_date, 
           "20240207" AS end_date 
  ) 
  SELECT 
    CONCAT(traffic_source.source, "/", traffic_source.medium) AS source_medium,
    traffic_source.name AS campaign_name,
    traffic_source.content AS manual_content, -- Assuming this is the correct field for UTM content
    SUM(ecommerce.purchase_revenue) AS total_revenue 
  FROM `brandlive.analytics_11111.events_*` 
  WHERE 
    _TABLE_SUFFIX BETWEEN date_range.start_date AND date_range.end_date AND 
    traffic_source.name = "monumental_3d_conversion_linkads_02_2024" AND 
    traffic_source.content = "red" -- Filter specifically for "red" content
  GROUP BY 
    source_medium, 
    campaign_name, 
    manual_content 
  

  Key Adjustments:

  • Correct Field for UTM Content: Ensure traffic_source.content is the correct field for UTM content. Adjust the field name as necessary based on your dataset's schema.
  • Filtering for "red" Content: The AND traffic_source.content = "red" condition ensures that only campaign data with "red" UTM content is considered.
  • Grouping and Summation: The query groups results and sums up purchase_revenue for a clear view of how the "red" content is performing.

  Additional Considerations:

  • Schema Verification: Double-check your GA4 BigQuery export schema to confirm the exact field names and paths for UTM parameters and ecommerce data.
  • Debugging: Break down the query into smaller chunks to make sure each part is working correctly.
  • Link to Past Question: Remember that making connections between campaign attributes in UTMs and ecommerce data is key to getting accurate results.