google, looker, tech

The Transaction ID Problem: A Complete Diagnostic Guide to Fixing Your GA4/SFCC Join

By Laurent Fidahoussen on April 28, 2026

You have GA4 data in BigQuery. You have SFCC orders in BigQuery. You write your first LEFT JOIN. You get a 40% NULL rate where you expected matches.

At this point, most people open their LookML model and start adjusting joins. That is the wrong move. The join is not the problem. The identifiers are.

This article covers every way the GA4/SFCC transaction ID can break, how to systematically diagnose which failure mode you are dealing with, and how to fix it in a way that benefits every dashboard automatically.

Why the Transaction ID Is Your Critical Path

The entire reconciliation model depends on one assumption: the transaction_id in GA4 is the same value as the order_id in SFCC for the same order. This assumption fails in practice more often than most teams expect, for reasons that are entirely preventable but rarely documented.

Here is why it is the critical path and not something you can work around: without a reliable join key, every reconciliation metric is either wrong or unmeasurable. You cannot calculate the revenue gap. You cannot classify orders by origin. You cannot build any of the governance metrics from the rest of this series. The join key is the foundation.

The Five Failure Modes

Transaction ID Failure Modes: Diagnostic Decision Tree Match rate below 85%? Are ID patterns the same? No Mode 1: Different IDs GA4 gets session token not SFCC order_id Mode 2: Prefix mismatch ORD-00123 vs 00123 #123 vs 123 Yes → Does LOWER(TRIM) help? Run query 2 to check normalised match rate Yes Mode 3: Case sensitivity ABC-123 vs abc-123 Fix: LOWER() both sides TRIM helps Mode 4: Whitespace “00123 ” ≠ “00123” Fix: TRIM() both sides No match Mode 5: Structural Offline / B2B / back-office No GA4 session expected
Figure 1 — Transaction ID failure mode decision tree. Run the diagnostic queries in order to identify which mode applies to your implementation before attempting any fix.

Before the Five Modes: Empty or Missing transaction_id

Check this first. Google warns: “Don’t send an empty string as the transaction ID. Google Analytics will deduplicate all purchase events that have transaction_id=””.” (Google: minimize duplicate key events with transaction IDs).

SELECT
  COUNT(*)                                              AS purchase_events,
  COUNTIF(ecommerce.transaction_id IS NULL
          OR ecommerce.transaction_id IN ('', '(not set)')) AS empty_transaction_ids
FROM `project.analytics_XXXX.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY));

Mode 1: Completely Different Identifiers

This is the most severe failure mode and the hardest to fix because it requires a product-level decision. In some implementations, the value pushed to the dataLayer as transaction_id is not the SFCC order_id. It could be:

How to detect it: Take 20 recent GA4 purchase events and look up the corresponding SFCC orders manually. If the IDs share no structural pattern at all, you have Mode 1.

How to fix it: This is not fixable in BigQuery or LookML alone. You need to change what value is pushed to the dataLayer. The correct approach is to generate the SFCC order_id before the confirmation page renders and pass it explicitly to the dataLayer. In most SFCC implementations, the order object is available server-side at the time the confirmation page is generated:

// dataLayer push on order confirmation page (SFCC ISML template or headless)
dataLayer.push({
  event: 'purchase',
  ecommerce: {
    transaction_id: '${dw.util.SecureEncoder.forJavaScriptInBlock(pdict.Order.orderNo)}',  // SFCC order number -- NOT payment ref
    value: ${pdict.Order.adjustedMerchandizeTotalPrice.value},  // items after discounts: no shipping, no tax
    shipping: ${pdict.Order.adjustedShippingTotalPrice.value},
    tax: ${pdict.Order.totalTax.value},
    currency: '${pdict.Order.getCurrencyCode()}',
    items: [...]
  }
});
// pdict.Order.orderNo is the SFCC internal order number that matches order_id in the DB

Three details matter here. GA4’s value must exclude shipping and tax (“Don’t include shipping or tax”, GA4 events reference), while SFCC’s totalGrossPrice is the grand total gross of tax (SFCC LineItemCtnr): use the same definition on the SFCC side of the reconciliation. Values printed inside a <script> block should be encoded with SecureEncoder.forJavaScriptInBlock (SFCC SecureEncoder). And SFRA confirmation templates may expose an order model (pdict.order) rather than pdict.Order: confirm in your cartridge.

Mode 2: Prefix or Suffix Mismatch

SFCC stores ORD-00123456. The dataLayer sends GA4 00123456. Or the reverse: SFCC stores 123456 and GA4 receives #123456 or order_123456.

This is extremely common when the dataLayer implementation was built by a front-end team that formatted the ID differently from the SFCC back-end team. It is also common after a platform migration where the ID format changed but the tracking code was not updated.

-- Step 1: Identify prefix patterns in SFCC
SELECT
  REGEXP_EXTRACT(order_id, r'^[^0-9]*')       AS prefix,
  LENGTH(REGEXP_EXTRACT(order_id, r'[0-9]+$')) AS numeric_len,
  LENGTH(order_id)                             AS total_length,
  COUNT(*)                                     AS order_count
FROM `project.sfcc.orders`
WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY 1, 2, 3
ORDER BY order_count DESC;

-- Step 2: Same for GA4
SELECT
  REGEXP_EXTRACT(transaction_id, r'^[^0-9]*') AS prefix,
  LENGTH(REGEXP_EXTRACT(transaction_id, r'[0-9]+$')) AS numeric_len,
  LENGTH(transaction_id)                       AS total_length,
  COUNT(*)                                     AS ga4_count
FROM `project.analytics.ga4_purchases`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY 1, 2, 3
ORDER BY ga4_count DESC;

-- If SFCC shows prefix='ORD-' but GA4 shows prefix=''
-- the fix is: REGEXP_REPLACE(sfcc.order_id, r'^ORD-', '')

Mode 3: Case Sensitivity

BigQuery string comparisons are case-sensitive by default. 'ABC-00123' does not equal 'abc-00123'. A join written as s.order_id = g.transaction_id will silently fail on any records where the case differs.

This failure mode is particularly insidious because:

Detection: If normalised match rate (with LOWER + TRIM) is significantly higher than raw match rate, you have Mode 3 or Mode 4.

Mode 4: Whitespace and Invisible Characters

Leading or trailing spaces are the most common invisible character issue. They are introduced by:

Less common but real: Unicode zero-width characters, non-breaking spaces (&nbsp;), or carriage return characters embedded in string fields. These require more aggressive cleaning:

-- Detect non-standard characters
SELECT
  order_id,
  LENGTH(order_id)               AS raw_length,
  LENGTH(TRIM(order_id))         AS trimmed_length,
  order_id != TRIM(order_id)     AS has_whitespace,
  -- Detect non-printable characters
  REGEXP_CONTAINS(order_id, r'[\x00-\x1F\x7F]') AS has_control_chars
FROM `project.sfcc.orders`
WHERE order_id != TRIM(order_id)
   OR REGEXP_CONTAINS(order_id, r'[\x00-\x1F\x7F]')
LIMIT 100;

-- Clean function for extreme cases:
SELECT
  -- strip invisible characters first (RE2 syntax: \x{...}), then TRIM
  TRIM(LOWER(REGEXP_REPLACE(order_id, r'[\x00-\x1F\x7F\x{00A0}\x{200B}-\x{200D}\x{FEFF}]', ''))) AS ultra_clean_id
FROM `project.sfcc.orders`

Mode 5: Structurally Unmatchable Orders

Some SFCC orders will never have a GA4 counterpart, not because of any data quality issue, but because they were created in environments where GA4 tracking cannot exist. These are not join failures. They are a different category of business reality.

Important: Do not try to fix Mode 5. Instead, classify these orders explicitly in your data model so they are excluded from match rate calculations and treated as their own segment. The article on dark traffic and orphan orders (Part 5 of this series) covers this in full.

The Full Diagnostic Sequence: Four Queries

Run these queries in order before building anything in Looker. Each one narrows the problem.

Step 0: Build the GA4 purchases view

The queries below read project.analytics.ga4_purchases. The raw export does not have that shape: it has daily tables events_YYYYMMDD, event_date is a STRING in YYYYMMDD format, the ID sits in ecommerce.transaction_id, and the session ID is the ga_session_id event parameter (Google: BigQuery export schema, Google: sessions). Google Analytics deduplicates purchases with the same transaction ID in reports (web streams only), but the export keeps every event, so deduplicate in the view (Google).

-- Step 0: one row per purchase, from the raw GA4 export (daily tables events_YYYYMMDD)
CREATE OR REPLACE VIEW `project.analytics.ga4_purchases` AS
SELECT
  PARSE_DATE('%Y%m%d', event_date)                      AS event_date,  -- STRING YYYYMMDD -> DATE
  event_timestamp,
  ecommerce.transaction_id                              AS transaction_id,
  (SELECT value.int_value FROM UNNEST(event_params)
    WHERE key = 'ga_session_id')                        AS ga_session_id,
  user_pseudo_id,
  ecommerce.purchase_revenue                            AS reported_revenue
FROM `project.analytics_XXXX.events_*`
WHERE event_name = 'purchase'
-- the export keeps resent purchases: keep the first event per transaction ID
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(ecommerce.transaction_id))
  ORDER BY event_timestamp) = 1;
-- In the view, event_date is already a DATE: the queries below compare it directly.

All queries below assume this view.

Query 1: Baseline match rate (raw, no normalisation)

SELECT
  COUNT(DISTINCT g.transaction_id)                           AS ga4_transactions,
  COUNT(DISTINCT s.order_id)                                 AS sfcc_web_orders,
  COUNT(DISTINCT CASE WHEN s.order_id IS NOT NULL
                      THEN g.transaction_id END)             AS matched_raw,
  ROUND(
    COUNT(DISTINCT CASE WHEN s.order_id IS NOT NULL
                        THEN g.transaction_id END)
    / NULLIF(COUNT(DISTINCT g.transaction_id), 0) * 100, 1
  )                                                          AS raw_match_rate_pct
FROM `project.analytics.ga4_purchases` g
LEFT JOIN `project.sfcc.orders` s
  ON g.transaction_id = s.order_id  -- raw, no normalisation
  AND s.channel IN ('web', 'storefront')  -- filter in the join, not in WHERE
WHERE g.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
-- (a WHERE filter on s.* would drop every unmatched GA4 row and force 100%)

-- Rules of thumb from my projects (not a standard):
-- >= 90%: small normalisation issue (Mode 3 or 4)
-- 70-89%: prefix mismatch likely (Mode 2)
-- < 70%:  structurally different IDs (Mode 1) or high offline volume

Query 2: Raw vs normalised match rate (with LOWER + TRIM)

WITH sfcc_web AS (
  SELECT order_id
  FROM `project.sfcc.orders`
  WHERE channel IN ('web', 'storefront')               -- same population as Query 1
)
SELECT
  COUNT(DISTINCT g.transaction_id)                           AS ga4_total,
  ROUND(COUNT(DISTINCT IF(r.order_id IS NOT NULL, g.transaction_id, NULL))
        / NULLIF(COUNT(DISTINCT g.transaction_id), 0) * 100, 1) AS raw_match_rate_pct,
  ROUND(COUNT(DISTINCT IF(n.order_id IS NOT NULL, g.transaction_id, NULL))
        / NULLIF(COUNT(DISTINCT g.transaction_id), 0) * 100, 1) AS normalised_match_rate_pct,
  ROUND((COUNT(DISTINCT IF(n.order_id IS NOT NULL, g.transaction_id, NULL))
         - COUNT(DISTINCT IF(r.order_id IS NOT NULL, g.transaction_id, NULL)))
        / NULLIF(COUNT(DISTINCT g.transaction_id), 0) * 100, 1) AS normalisation_gain_pct
FROM `project.analytics.ga4_purchases` g
LEFT JOIN sfcc_web r
  ON g.transaction_id = r.order_id                                  -- raw
LEFT JOIN sfcc_web n
  ON LOWER(TRIM(g.transaction_id)) = LOWER(TRIM(n.order_id))        -- normalised
WHERE g.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
-- If normalised_match_rate >> raw_match_rate:
--   Fix: apply LOWER(TRIM()) to both sides in all joins
-- If normalised_match_rate ≈ raw_match_rate:
--   The issue is structural (Mode 1 or 2) -- normalisation alone doesn't help

Query 3: ID structure comparison (detect Mode 1 and 2)

WITH ga4_ids AS (
  SELECT
    'ga4'                                          AS source,
    REGEXP_EXTRACT(transaction_id, r'^[^0-9]*')    AS prefix,
    LENGTH(REGEXP_EXTRACT(transaction_id, r'^[^0-9]*')) AS prefix_len,
    LENGTH(transaction_id)                          AS total_len,
    COUNT(*)                                        AS record_count
  FROM `project.analytics.ga4_purchases`
  WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY 1, 2, 3, 4
),
sfcc_ids AS (
  SELECT
    'sfcc'                                          AS source,
    REGEXP_EXTRACT(order_id, r'^[^0-9]*')           AS prefix,
    LENGTH(REGEXP_EXTRACT(order_id, r'^[^0-9]*'))   AS prefix_len,
    LENGTH(order_id)                                AS total_len,
    COUNT(*)                                        AS record_count
  FROM `project.sfcc.orders`
  WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    AND channel IN ('web', 'storefront')
  GROUP BY 1, 2, 3, 4
)
SELECT * FROM ga4_ids
UNION ALL
SELECT * FROM sfcc_ids
ORDER BY source, record_count DESC;

-- If GA4 shows prefix='' and SFCC shows prefix='ORD-':
--   Fix: REGEXP_REPLACE(sfcc.order_id, r'^ORD-', '')
-- If GA4 shows total_len=12 and SFCC shows total_len=18:
--   They are structurally different IDs (Mode 1) -- fix at dataLayer level

Query 4: Classify the unmatched bucket

SELECT
  s.channel,
  s.customer_type,
  CASE
    WHEN s.channel IN ('store', 'pos')      THEN 'offline_store'
    WHEN s.channel IN ('phone', 'callcenter') THEN 'offline_callcenter'
    WHEN s.customer_type IN ('b2b', 'wholesale') THEN 'b2b_wholesale'
    WHEN s.channel IN ('web', 'storefront') THEN 'web_unmatched_dark_traffic'
    ELSE 'other'
  END                                                       AS unmatched_category,
  COUNT(DISTINCT s.order_id)                                AS unmatched_orders,
  SUM(s.order_value)                                        AS unmatched_revenue,
  ROUND(
    COUNT(DISTINCT s.order_id)
    / SUM(COUNT(DISTINCT s.order_id)) OVER () * 100, 1
  )                                                         AS share_of_unmatched_pct
FROM `project.sfcc.orders` s
LEFT JOIN `project.analytics.ga4_purchases` g
  ON LOWER(TRIM(s.order_id)) = LOWER(TRIM(g.transaction_id))
WHERE g.transaction_id IS NULL
  AND DATE(s.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2, 3
ORDER BY unmatched_orders DESC;

-- Example split from one implementation; yours depends on your channel mix:
-- 'offline_store' and 'b2b_wholesale': most of the unmatched (expected, not fixable)
-- 'web_unmatched_dark_traffic': the rest (WebView, consent, blocked tags)
-- If 'web_unmatched_dark_traffic' > 50% of unmatched: tracking implementation issue

Fixing It in LookML: Three Patterns

Once you have identified the failure mode, implement the fix in LookML or BigQuery views. The correction should be centralised — not scattered across individual dashboards.

The one_to_one relationships below assume the GA4 view is deduplicated by transaction ID (Step 0). Without that, declare many_to_one from GA4 to SFCC.

Pattern 1: Normalisation only (Mode 3 + 4)

explore: ga4_purchases {
  label: "GA4 + SFCC Reconciliation"

  join: sfcc_orders {
    type: left_outer
    sql_on:
      LOWER(TRIM(${ga4_purchases.transaction_id}))
      = LOWER(TRIM(${sfcc_orders.order_id})) ;;
    relationship: one_to_one
  }
}

Pattern 2: Prefix stripping + normalisation (Mode 2)

-- SFCC uses ORD-00123456, GA4 uses 00123456
explore: ga4_purchases {
  join: sfcc_orders {
    type: left_outer
    sql_on:
      LOWER(TRIM(${ga4_purchases.transaction_id}))
      = LOWER(TRIM(
          REGEXP_REPLACE(${sfcc_orders.order_id}, r'^ORD-', '')
        )) ;;
    relationship: one_to_one
  }
}

-- Alternative: GA4 uses #123456, SFCC uses 123456
-- sql_on: REGEXP_REPLACE(${ga4_purchases.transaction_id}, r'^#', '')
--         = ${sfcc_orders.order_id}

Pattern 3: Normalised views in BigQuery (best for complex cases)

When normalisation logic is complex or involves multiple transformations, the cleanest approach is to create normalised views in BigQuery before Looker touches the data. Looker then joins on clean, pre-normalised fields:

-- BigQuery: create normalised views
CREATE OR REPLACE VIEW `project.reconciliation.norm_sfcc_orders` AS
SELECT
  order_id                                              AS raw_order_id,
  -- Apply all normalisation in one place
  LOWER(
    TRIM(
      REGEXP_REPLACE(
        REGEXP_REPLACE(order_id, r'^ORD-', ''),  -- strip prefix
        r'[\x00-\x1F\x7F]', ''                   -- strip control chars
      )
    )
  )                                                     AS norm_order_id,
  created_at,
  order_value,
  channel,
  customer_type,
  status,
  refund_amount
FROM `project.sfcc.orders`
WHERE status NOT IN ('fraud', 'test');

CREATE OR REPLACE VIEW `project.reconciliation.norm_ga4_purchases` AS
SELECT
  transaction_id                                        AS raw_transaction_id,
  LOWER(TRIM(transaction_id))                           AS norm_transaction_id,
  event_date,
  ga_session_id,
  user_pseudo_id,
  reported_revenue
FROM `project.analytics.ga4_purchases`;  -- already one row per transaction (Step 0)

-- LookML: clean join on normalised fields
explore: ga4_purchases {
  join: sfcc_orders {
    type: left_outer
    sql_on: ${ga4_purchases.norm_transaction_id}
             = ${sfcc_orders.norm_order_id} ;;
    relationship: one_to_one
  }
}

What a Healthy Match Rate Looks Like

After applying the appropriate normalisation, here are the realistic match rate targets by order segment. These are illustrative targets from my own projects — not an industry benchmark. Measure your baseline first.

Order segmentTarget match rateAcceptable minimumIf below minimum, investigate
Web orders, same-day (Chrome/Firefox)92–97%88%Tag firing on confirmation page, consent mode config
Mobile web orders78–88%70%WebView traffic, in-app browser
Mobile app orders50–75%40%Firebase/GA4 SDK missing or misconfigured in app
Call centre orders0–10%N/AExpected — offline by nature. Classify, don't fix.
B2B / wholesale orders0–20%N/AExpected — no web session. Classify, don't fix.
POS / in-store orders0%N/AExpected — fully offline. Should not appear in reconciliation scope.

Safari's tracking prevention does not stop the purchase hit from arriving; it affects attribution (which source gets credit), so check it in an attribution-quality review, not in this match-rate table (WebKit tracking prevention).

No reconciliation model reaches 100%. The goal is to understand your match rate by segment so you can make informed decisions about which orders to include in attribution analysis, and distinguish between structural gaps (not fixable) and implementation gaps (fixable).

Common Mistakes to Avoid

What Comes Next

Related reading

Sources and Further Reading