Finance says the revenue in the analytics tool does not match their system and wants to know which is right. Finance is right. Working out why takes about an hour and teaches you most of what goes wrong with event data.
This is a flattened export of thirty days of GA4 style events: 9,038 rows, 597 of them purchases. Six deliberate faults. The interesting part is not any single fault, it is what they do together.
If you would rather try it first, the download includes a brief with the task and nothing else.
What you are given
Eleven columns: timestamp, event name, user pseudo id, session id, value, currency, transaction id, items count, item list name, engagement time, device category. Five event types, from page views down to purchases.
The task is three numbers. Total revenue, order count, average order value. Each has a defensible answer and an obvious wrong one.
1. Purchases that fired twice
38 purchase events share a transaction id with another row, firing between 2 and 14 seconds apart. That is a confirmation page being refreshed, or somebody hitting the back button.
pur = df[df.event_name == "purchase"]pur.transaction_id.duplicated().sum() # 38orders = pur.drop_duplicates(subset="transaction_id") # 559, not 597
Orders are overstated by 6.8%. The overstatement is variable rather than a step change, so it never looks like a bug. It survives until somebody reconciles against a finance system, which is exactly what has happened here.
Fix it in the query and at the source. Both, not either. Deduplicating downstream stops the number being wrong; fixing the tag stops it happening again.
2. Values sent as text
321 rows carry the value as a string, things like £129.00 or 1,299.00. Thirty six of those are purchases.
pd.to_numeric(df["value"], errors="coerce").isna().sum() # 321
Those rows become NaN and drop out of any sum. Revenue understated.
Worth knowing what real GA4 does here, because it is worse than this file suggests. A value it cannot parse is discarded at collection, so the event still arrives, still counts, and simply has no revenue attached. There is nothing downstream to recover, and no error anywhere to tell you it happened.
3. Values with no currency
251 rows have a value and an empty currency, 25 of them purchases.
In real GA4 this is the expensive one. A monetary value with no currency is dropped entirely. The event count stays right, so the funnel looks intact and the revenue does not. If your conversion count agrees with the source system and your revenue does not, this is the first thing to check.
4. One column, two types
Engagement time is a number on most rows and one of low, medium or high on 761 of them, which is 8.4% of the file.
df["engagement_time_msec"].str.match(r"^[a-z]+$").sum() # 761
Any average silently drops those rows. In BigQuery it is sharper than that: the two types land in different fields of the value struct, so a query reading only the integer field loses that 8% without a word.
The cause is nearly always two templates, or two developers, sending the same parameter differently. Nobody notices because both versions look correct in the debug view.
5. A parameter collected in the wrong place
2,719 rows carry an item list name, and there is no item level column for it anywhere in the export.
So you cannot attribute a product view or a purchase to the merchandising surface it came from. The parameter is collected, stored, paid for, and useless.
This one cannot be fixed in the query. The list name has to be set on each item in the items array, not once on the event. It is worth checking for, because it is the kind of thing that gets specified in a measurement plan, implemented at the wrong level, and passes QA because the field is populated.
6. Session ids are not unique
df.session_id.nunique() # 3,002df.groupby(["user_pseudo_id", "session_id"]).ngroups # 8,997
A GA4 session id is only unique within a user. Count it on its own and you merge the sessions of different people, here by a factor of three. Every per session metric built on that first number is inflated by roughly three.
The session key is always the user and the session id together.
The three numbers
| Metric | Naive query | Correct | Error |
|---|---|---|---|
| Orders | 597 | 559 | 6.8% too high |
| Revenue | £77,717 | £77,021 | 0.9% too high |
| Average order value | £130.18 | £137.78 | 5.5% too low |
Look at the revenue row for a moment. The naive answer is out by less than one percent. Nobody would ever question it. And it is wrong for two separate reasons that happen to cancel: 38 duplicate purchases add about £5,000, and 36 purchases whose value arrived as text take about £4,400 away.
Now try fixing one of them. Cast the values properly and leave the duplicates in, and revenue becomes £82,100, which is 6.6% too high. The first correct thing you do makes the headline number visibly worse.
That is the worst arrangement two faults can be in. The total looks fine, every component of it is wrong, and partial fixes look like regressions.
What to tell finance
Analytics is overstating orders by about 7%, because 38 purchases fired twice when the confirmation page was refreshed. It is also losing revenue, because 36 purchases sent the value as text and another 25 sent no currency, and both get discarded. The two errors run in opposite directions and nearly cancel, which is why the revenue total looked close enough to ignore while the order count and average order value were both visibly wrong.
Finance is the correct source for revenue. I would use analytics for behaviour and trends, and stop quoting it for absolute revenue until the duplicate purchase fires are fixed at source.
What this dataset teaches
Analytics data does not arrive wrong in ways that raise errors. It arrives wrong in ways that look like a plausible number.
Three habits catch nearly all of it. Reconcile against a system of record before you trust any absolute figure. Check the type of every column you intend to aggregate, and count what your cast throws away. And when two numbers disagree, resist fixing the first fault you find until you know how many there are, because faults in opposite directions are common and a partial fix can make the headline worse.
See you soon.
