LLMs and Duplicate Conversions: A GA4 transaction_id Check

GA4 de-duplicates purchases that share a transaction ID, but only when the ID is present, unique and sent from a web stream. Google's help says "Transaction ID deduplication only works for data collected through web streams, not app streams", and that purchases sent with an empty-string ID are all de-duplicated against each other. The BigQuery export is raw collected data, so a count of purchase events isn't an order count until you compare it with the distinct IDs. No source cited here measures how often GA4 exports hold the same purchase twice.

What do the studies and documentation show?

Documentation covers the GA4 rules and one study covers model-written counting mistakes. No source cited here measures how often duplicates occur in a real store.

• Google Analytics Help (official documentation) says you must add a transaction ID "to every ecommerce event to deduplicate transactions from the same user and to properly process refunds". Google names two ways the match goes wrong: "If your tag sends Google Analytics the same ID for different transactions, you could significantly undercount your key events", and "Don't send an empty string as the transaction ID".
• GA4 BigQuery Export help and Google for Developers (official documentation and guidance) describe the export as containing "only the raw event data" that Google Analytics receives. The developer post says export data "will not match with the standard reporting surfaces" once data filtering is set up or "your daily event export volume has exceeded the limit", which for Standard properties is "a daily BigQuery Export limit of 1 million events". For implementations that send delayed events, it advises comparing on data older than 72 hours.
• Shen et al. (peer-reviewed, FSE 2026) read 4602 wrong queries from four text-to-SQL systems on the BIRD and Spider development sets, run with GPT-3.5 and GPT-4o. Its taxonomy names "Wrong COUNT Object", where a model uses "COUNT(*) instead of counting the unique molecule IDs". In the four BIRD runs it accounts for 7 to 16 errors per run, out of 448 to 786 errors per run.

The counting mistake is small next to the paper's larger categories, but it is the exact mistake a double-counted order needs. No source cited here measures how often an LLM-written query counts one order twice, or how often a GA4 export repeats one.

Why does a missing or reused ID matter?

De-duplication is a match on an ID, so it fails in two opposite ways. With no usable ID, repeated purchases are not recognised and stay in the export. With an empty or reused ID, different orders match each other and collapse, which undercounts. Which one you have depends on your tag, not on the model that writes your SQL.

Two neighbouring problems need other checks. Google's developers say a person on three browsers shows up as three separate user_pseudo_id values in the export, because Google Signals data isn't available there. And each ad platform reports its own conversions, so compare their totals with your orders. That is an attribution question, not a SQL one.

How do I check for duplicates on my own data?

Count purchase events against distinct IDs, then list the IDs that repeat. Swap in your own project and dates.

-- 1. Purchase events versus distinct order IDs
SELECT COUNT(*)                                 AS purchase_events,
       COUNT(DISTINCT ecommerce.transaction_id) AS distinct_ids,
       COUNTIF(ecommerce.transaction_id IS NULL
               OR ecommerce.transaction_id IN ('', '(not set)')) AS no_usable_id
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20260901' AND '20260927';

-- 2. Which IDs repeat
SELECT ecommerce.transaction_id AS order_id, COUNT(*) AS times_sent
FROM `your_project.analytics_123456789.events_*`
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20260901' AND '20260927'
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY times_sent DESC
LIMIT 50;

-- 3. Revenue with one row per order ID (purchases without an ID are counted in query 1)
SELECT SUM(ecommerce.purchase_revenue) AS revenue_once_per_order
FROM (
  SELECT ecommerce
  FROM `your_project.analytics_123456789.events_*`
  WHERE event_name = 'purchase'
    AND ecommerce.transaction_id IS NOT NULL
    AND _TABLE_SUFFIX BETWEEN '20260901' AND '20260927'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY ecommerce.transaction_id ORDER BY event_timestamp) = 1
);

Read the first row like this, and compare distinct_ids with your Shopify order count for the same dates and time zone:

1. Pass. purchase_events equals distinct_ids, no_usable_id is zero, and distinct_ids matches Shopify's orders apart from differences you can list order by order.
2. purchase_events above distinct_ids. The tag sent some purchases more than once. The second query lists them. Count orders with the distinct count, and revenue with the third query.
3. no_usable_id above zero. Those purchases cannot be de-duplicated, and empty IDs collapse into one. Fix the tag so each order sends its own ID.
4. distinct_ids above Shopify's count. Look for test orders, which Shopify's API flags with a test field, and cancelled orders. Below it: look for purchases GA4 never received, such as a blocked tag, and for the export limit.

An LLM can draft the COUNT(DISTINCT) and the window query. It doesn't see your tag, and the ID check is what tells you which of the two failures you have.

Sources, 30 September 2026: Minimize duplicate key events with transaction IDs (Google Analytics Help, 2026); BigQuery Export (Google Analytics Help, 2026); Bridge the gap between the Google Analytics UI and BigQuery export (Google for Developers, 2023); ICL-based text-to-SQL error study (Shen et al., FSE 2026); Shopify Admin API, Order (Shopify, 2026).

Related answers

• How a join drops or repeats orders
• Time zones and currencies in order totals
• Why Shopify and analytics numbers differ
• Attribution discrepancy