Table of Contents

A complete reverse-engineered measurement framework derived from real GA4 BigQuery export data. Every configuration decision is backed by evidence from the dataset.

Reverse Engineering Methodology

This booklet is built by working backwards from GA4's BigQuery export — reading what the analytics platform recorded to reconstruct the GTM container, dataLayer schema, and measurement plan that produced those records.

The Evidence Trail

GA4 writes everything to BigQuery. Every event, every parameter, every session attribute. By querying bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* we can infer:

• Which tag manager was used — the clean_event parameter value 'gtm.js' is GTM's container fingerprint, present on every custom event.
• Which events fire and when — event names, frequencies, and co-occurring page paths.
• Which parameters are custom vs. auto-collected — GA4 auto-populates a known set; anything outside that set was pushed via dataLayer.
• What the site architecture looks like — page_location and page_title reveal every page type.
• Consent Mode status — privacy_info.analytics_storage reveals if Consent Mode was active.
• Ad platform integrations — gclid (Google Ads), dclid (Display), gclsrc (attribution source) confirm which Google media was active.

Data Sources

Confidence Framework

Each reconstructed configuration element carries a confidence rating:

• HIGH — Directly observed in the data with unambiguous interpretation
• MEDIUM — Strongly implied by data patterns, minor assumptions
• LOW — Inferred from absence of data or indirect signals

Executive Summary & KPIs

92 days of Google Merchandise Store analytics data revealing a healthy organic-first acquisition strategy, a significant mobile conversion gap, and an ecommerce funnel with meaningful optimisation opportunity.

Site Architecture & Page Taxonomy

Derived entirely from page_location and page_title event_params observed in the BigQuery export. These are the actual URLs the measurement system tracked.

Page View Distribution (Sample)

Event Taxonomy

All events observed in the BigQuery export, classified by collection method and evidenced by actual event counts from the 2,000-row sample.

Auto-Collected Events

Fired automatically by the GA4 SDK/GTAG — no GTM tag required. Confirmed by absence of clean_event param or GA4 SDK origin.

Enhanced Measurement Events

Fired by GA4's Enhanced Measurement feature — no separate GTM tag needed when EM is on. The clean_event param appearing on scroll/page_view events suggests EM was supplemented or overridden by GTM tags.

Recommended Ecommerce Events (Custom via GTM)

These events were fired via GTM tags. Evidence: clean_event='gtm.js' param present on all of them. They follow GA4's recommended ecommerce event schema.

* Not observed in 2,000-row sample window. Confirmed in full 847,932-event dataset.

Event Parameter Reference

Complete reference of all event_params found in the BigQuery export. Parameters are stored as ARRAY<STRUCT<key STRING, value STRUCT<string_value, int_value, float_value, double_value>>>.

Auto-Collected Parameters (GA4 Native)

Google Ads Parameters (Auto-tagging)

Custom Parameters (GTM-Injected)

DataLayer Specification

Reverse-engineered dataLayer.push() calls for every ecommerce event, derived from the event_params and items[] STRUCT fields observed in BigQuery.

Page View (Enhanced Measurement — no push needed)

// Fires automatically via GA4 Enhanced Measurement
// No dataLayer.push required unless overriding params
// The all_data custom dimension IS being pushed — likely from CMS:
window.dataLayer = window.dataLayer || [];
window.dataLayer.push({
  'all_data': null  // or page metadata JSON string on live site
});

view_item — Product Detail Page

window.dataLayer.push({ ecommerce: null }); // clear previous
window.dataLayer.push({
  'event': 'view_item',
  'ecommerce': {
    'currency': 'USD',
    'value': 25.99,
    'items': [{
      'item_id': 'GGOEGAAX0104',
      'item_name': 'Google Unisex Eco Tee',
      'item_brand': 'Google',
      'item_category': 'Apparel',
      'item_category2': 'Men\'s',
      'item_list_name': 'Category - Apparel',
      'index': 1,
      'price': 25.99,
      'quantity': 1
    }]
  },
  'all_data': null  // custom dimension
});

view_promotion — Banner / Hero Impression

window.dataLayer.push({ ecommerce: null });
window.dataLayer.push({
  'event': 'view_promotion',
  'ecommerce': {
    'items': [{
      'item_id': 'PROMO_HOLIDAY2020',
      'item_name': 'Holiday 2020 Sale',
      'promotion_id': 'holiday2020',
      'promotion_name': 'Holiday Sale 2020',
      'creative_name': 'homepage_hero_banner',
      'creative_slot': 'hero_1'
    }]
  },
  'all_data': null
});

begin_checkout — From /basket.html

window.dataLayer.push({ ecommerce: null });
window.dataLayer.push({
  'event': 'begin_checkout',
  'ecommerce': {
    'currency': 'USD',
    'value': 51.98,
    'items': [/* all basket items */]
  },
  'all_data': null
});

add_shipping_info — Shipping Step on /payment.html

window.dataLayer.push({ ecommerce: null });
window.dataLayer.push({
  'event': 'add_shipping_info',
  'ecommerce': {
    'currency': 'USD',
    'shipping_tier': 'Standard',
    'value': 51.98,
    'items': [/* checkout items */]
  },
  'all_data': null
});

add_payment_info — Payment Step on /payment.html

window.dataLayer.push({ ecommerce: null });
window.dataLayer.push({
  'event': 'add_payment_info',
  'ecommerce': {
    'currency': 'USD',
    'payment_type': 'Credit Card',
    'value': 51.98,
    'items': [/* checkout items */]
  },
  'all_data': null
});

purchase — Order Confirmation Page

window.dataLayer.push({ ecommerce: null });
window.dataLayer.push({
  'event': 'purchase',
  'ecommerce': {
    'transaction_id': 'GS-98765',    // CRITICAL for deduplication
    'currency': 'USD',
    'value': 51.98,
    'tax': 4.16,
    'shipping': 0,
    'coupon': '',
    'items': [/* purchased items */]
  }
});

view_search_results — From /asearch.html

window.dataLayer.push({
  'event': 'view_search_results',
  'search_term': searchQueryVar,  // obfuscated in public data
  'all_data': null
});

GTM Container Architecture

Reconstructed container structure with 11 variables, 10 triggers, and 9 tags — all derived from observed BigQuery data patterns.

Tag Firing Priority

Enhanced Ecommerce Funnel Analysis

The full 6-step purchase funnel from product discovery through transaction, with step-to-step conversion rates and dropout volumes derived from the full dataset.

BigQuery Funnel Query

WITH funnel AS (
  SELECT
    user_pseudo_id,
    MAX(IF(event_name = 'view_item',         1, 0)) AS did_view_item,
    MAX(IF(event_name = 'add_to_cart',       1, 0)) AS did_add_to_cart,
    MAX(IF(event_name = 'begin_checkout',    1, 0)) AS did_begin_checkout,
    MAX(IF(event_name = 'add_shipping_info', 1, 0)) AS did_add_shipping,
    MAX(IF(event_name = 'add_payment_info',  1, 0)) AS did_add_payment,
    MAX(IF(event_name = 'purchase',          1, 0)) AS did_purchase
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  GROUP BY 1
)
SELECT
  COUNTIF(did_view_item = 1)       AS step1_view_item,
  COUNTIF(did_add_to_cart = 1)     AS step2_add_to_cart,
  COUNTIF(did_begin_checkout = 1)  AS step3_begin_checkout,
  COUNTIF(did_add_shipping = 1)    AS step4_add_shipping,
  COUNTIF(did_add_payment = 1)     AS step5_add_payment,
  COUNTIF(did_purchase = 1)        AS step6_purchase
FROM funnel;

Traffic & Device Analysis

All numbers derived directly from session_start event_params and device struct in the BigQuery export. Sample: 2,000 events covering Nov 2020 – Jan 2021.

Traffic Channel Distribution

Device Category

Browser Distribution

Geographic Distribution (Top 8)

Session Depth Analysis

From ga_session_number event_param on session_start events:

BigQuery Schema & SQL Patterns

Essential BigQuery patterns for querying GA4's nested ARRAY<STRUCT> schema. These patterns are required for any GA4 → BigQuery analysis pipeline.

Events Table Schema (key fields)

-- bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_YYYYMMDD
event_date              STRING         -- '20201115'
event_timestamp         INT64          -- microseconds since epoch
event_name              STRING         -- 'page_view', 'purchase', etc.
event_params            ARRAY
>>
user_pseudo_id          STRING         -- obfuscated user identifier
user_id                 STRING         -- NULL in public dataset
user_ltv                STRUCT<
  revenue               FLOAT64,
  currency              STRING
>
device                  STRUCT<
  category              STRING,        -- 'desktop', 'mobile', 'tablet'
  mobile_brand_name     STRING,
  operating_system      STRING,
  web_info              STRUCT
>
geo                     STRUCT<
  country               STRING,
  city                  STRING,
  region                STRING
>
traffic_source          STRUCT<
  source                STRING,
  medium                STRING,
  name                  STRING         -- campaign
>
ecommerce               STRUCT<
  total_item_quantity   INT64,
  purchase_revenue_in_usd FLOAT64,
  purchase_revenue      FLOAT64,
  transaction_id        STRING
>
items                   ARRAY>
privacy_info            STRUCT<
  analytics_storage     STRING,        -- 'Yes', 'No', or NULL
  ads_storage           STRING,
  uses_transient_token  STRING
>

Pattern 1: Extract Event Parameter

SELECT
  event_name,
  -- Safe param extraction with COALESCE across value types
  (SELECT COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))
   FROM UNNEST(event_params) ep
   WHERE ep.key = 'page_location') AS page_location,
  (SELECT ep.value.int_value
   FROM UNNEST(event_params) ep
   WHERE ep.key = 'ga_session_id') AS session_id
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'page_view'
LIMIT 100;

Pattern 2: Session Reconstruction

SELECT
  CONCAT(
    user_pseudo_id, '-',
    (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id')
  ) AS session_key,
  event_name,
  event_timestamp
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
ORDER BY session_key, event_timestamp;

Pattern 3: UNNEST Items Array

SELECT
  event_name,
  item.item_id,
  item.item_name,
  item.item_category,
  item.price_in_usd,
  item.quantity
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'purchase'
ORDER BY event_timestamp DESC;

Pattern 4: Batch Mode Python Client

from google.cloud.bigquery import Client, QueryJobConfig, QueryPriority

client = Client(project="your-gcp-project")

cfg = QueryJobConfig(
    priority             = QueryPriority.BATCH,   # shared slot pool — no on-demand cost
    use_query_cache      = True,                 # zero cost on cache hit
    maximum_bytes_billed = 5_000_000_000          # 5 GB safety cap
)

job = client.query(sql, job_config=cfg)
# BATCH jobs queue for up to 6 hours — ideal for overnight ETL
df = job.result().to_dataframe()

Consent Mode Analysis

What the BigQuery data tells us about the consent state during the measurement period — and what was missing.

What Consent Mode v2 Would Add

How to Implement Consent Mode v2 (For Agencies)

// 1. Set consent defaults BEFORE GTM snippet (in <head>)
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}

gtag('consent', 'default', {
  'analytics_storage': 'denied',
  'ad_storage': 'denied',
  'ad_user_data': 'denied',         // NEW in v2
  'ad_personalization': 'denied',   // NEW in v2
  'wait_for_update': 500             // ms to wait for CMP
});

// 2. CMP callback — update after user consent choice
gtag('consent', 'update', {
  'analytics_storage': 'granted',
  'ad_storage': 'granted',
  'ad_user_data': 'granted',
  'ad_personalization': 'granted'
});

// 3. BigQuery audit query — find consent gaps
/*
SELECT
  privacy_info.analytics_storage,
  COUNT(*) AS events,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
GROUP BY 1;
*/

Consent Mode v2 Impact on Reporting

Anomalies & Root Cause Analysis

Every discrepancy found in the BigQuery data, with a root cause hypothesis and recommended fix.

Anomaly 1: Self-Referral Traffic (17.3%)

Anomaly 2: add_payment_info > add_shipping_info

Anomaly 3: debug_mode=1 in Production Data

Anomaly 4: Protocol Inconsistency (http:// vs https://)

Anomaly 5: all_data Parameter Always Null

Recommendations

Prioritised action plan based on what the BigQuery data reveals. Ordered by estimated impact.

P1 — Fix Cross-Domain Tracking (Immediate)

P2 — Implement Consent Mode v2

P3 — Address Mobile Conversion Gap

P4 — Reduce Cart Abandonment (76% drop at view→cart)

P5 — Add Enhanced Conversions

P6 — Fix Debug Mode Leak

P7 — Server-Side GTM for Safari/Firefox