BigQuery & GA4 - session source / medium

Google Cloud Data Analytics
,
7

Dealing with Discrepancies in Direct/None Traffic and Ecommerce Metrics in GA4 BigQuery Queries

Understanding the Challenges:

  • Discrepancies in fetching direct/none traffic data.
  • Issues with additional metrics like ecommerce.purchase_revenue.

Strategies to Address These Issues:

1. Understanding Direct/None Traffic

  • Identification: Ensure your queries correctly identify direct/none traffic, which typically occurs when there’s no referrer information or the source/medium cannot be determined.
  • Session Start Logic: Review how sessions are identified, as direct/none traffic should be associated with the first event of a new session or a session_start event.

2. Refining Query for Direct/None Traffic

  • Query Adjustment: Modify your SQL query to accurately capture direct/none traffic by checking for null or missing values in the source/medium fields and labeling them accordingly.
  • Example Query Snippet:
CASE WHEN traffic_source.source IS NULL OR traffic_source.source = '' THEN 'direct' ELSE traffic_source.source END AS source,
CASE WHEN traffic_source.medium IS NULL OR traffic_source.medium = '' THEN '(none)' ELSE traffic_source.medium END AS medium

3. Handling ecommerce.purchase_revenue

  • Data Consistency: Ensure consistent population of ecommerce.purchase_revenue to avoid mixed results.
  • Join Logic: Verify correct join conditions when working with multiple tables or datasets to align revenue data with corresponding sessions and traffic sources.

4. Query Optimization for Additional Metrics

  • Metric Alignment: Align queries with GA4’s calculation of ecommerce.purchase_revenue, potentially summing up revenue per session or user.
  • Aggregation and Grouping: Properly aggregate and group data to reflect correct revenue figures at the appropriate level (session or user).

5. Testing and Validation

  • Small Data Sets: Test queries on smaller subsets to ensure expected results.
  • Compare with GA4 Interface: Regularly compare query results with standard reports in the GA4 interface for consistency.

6. Advanced Debugging

  • Event-Level Analysis: Dive into event-level data to understand how different events contribute to metrics.
  • Data Layer Inspection: Review data layer implementation on your website to ensure accurate capture of events and traffic source data.
2 Likes