The payment report says 150.00. The analytics export says 150.00. Both contain three distinct order IDs. It is tempting to call the integration healthy and move on.
Those checks can pass even when the reports describe different orders.
Here is a small, runnable counterexample. It uses invented data, not a customer incident or a claim about any analytics vendor. The point is to show exactly what an aggregate comparison cannot establish, then make the next check concrete.
Equal sums do not establish equal records
Suppose a shop has three paid orders: O101 for 40.00, O102 for 60.00 and O103 for 50.00. A raw purchase-event export contains O101 twice, O102 once and an unmatched O999 for 10.00. Every amount is in the same fictional two-decimal currency.
| Order ID | Paid-order amount | Raw event amounts | Event rows |
|---|---|---|---|
| O101 | 40.00 | 40.00 + 40.00 | 2 |
| O102 | 60.00 | 60.00 | 1 |
| O103 | 50.00 | No event | 0 |
| O999 | No order in this fixture | 10.00 | 1 |
| Total | 150.00 | 150.00 | 4 |
The duplicate adds 40.00. The unmatched record adds another 10.00. Together they cancel the missing 50.00 exactly. There are also three distinct order IDs on each side, but only two are shared.
The event-row count catches a difference in this fixture. The sum and distinct-ID count do not. Neither aggregate identifies which order is missing.
Decide what the two exports are supposed to mean
A browser purchase event records what the instrumentation reported. A paid-order record represents the business state maintained by the order or payment system. They are useful for different questions. One should not silently stand in for the other.
Before comparing them, write down the contract: one shop or account, one currency, a shared order identifier, the payment state included, the amount definition, and the time window. Decide whether the comparison is at order level, payment-attempt level or individual line-item level. A single order can legitimately have several payments or items.
Gross order value, captured payments, net revenue after refunds and payouts after fees are not interchangeable. Nor are event time, payment time, ingestion time and payout time. Agree on a cutoff and allow for the actual pipeline’s processing delay. A recent record that has not arrived yet is not automatically lost.
For this example, the contract is deliberately narrow: one complete synthetic snapshot, three already-paid orders, one expected purchase event per order, no refunds, taxes, fees, split payments or currency conversion. This is raw event data, not a simulation of a vendor’s deduplication rules.
Group the event side before joining
Run this with Python 3 and its sqlite3 module. It creates an in-memory database, makes no network requests and touches no production records. Amounts are integers in the fixture’s minor unit, so the comparison does not depend on binary floating-point arithmetic.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (order_id TEXT PRIMARY KEY, cents INTEGER NOT NULL);
CREATE TABLE events (event_id TEXT PRIMARY KEY, order_id TEXT NOT NULL,
cents INTEGER NOT NULL);
INSERT INTO orders VALUES ('O101', 4000), ('O102', 6000), ('O103', 5000);
INSERT INTO events VALUES ('E1', 'O101', 4000), ('E2', 'O101', 4000),
('E3', 'O102', 6000), ('E4', 'O999', 1000);
""")
for table in ("orders", "events"):
total, ids = db.execute(
f"SELECT SUM(cents), COUNT(DISTINCT order_id) FROM {table}"
).fetchone()
print(f"{table}: cents={total}, distinct_order_ids={ids}")
query = """
WITH observed AS (
SELECT order_id, COUNT(*) AS event_rows, SUM(cents) AS event_cents
FROM events GROUP BY order_id
), ids AS (
SELECT order_id FROM orders UNION SELECT order_id FROM events
)
SELECT ids.order_id, o.cents, COALESCE(e.event_rows, 0), e.event_cents
FROM ids
LEFT JOIN orders o ON o.order_id = ids.order_id
LEFT JOIN observed e ON e.order_id = ids.order_id
ORDER BY ids.order_id
"""
for row in db.execute(query):
print(row)
db.close()
The output is:
orders: cents=15000, distinct_order_ids=3
events: cents=15000, distinct_order_ids=3
('O101', 4000, 2, 8000)
('O102', 6000, 1, 6000)
('O103', 5000, 0, None)
('O999', None, 1, 1000)
The query groups events to one row per order ID before joining. Otherwise, joining a one-row order directly to several event rows can repeat the order amount and make the reconciliation itself misleading. The union of IDs also keeps unmatched records from either side; an inner join would hide them.
O103 has no event row. O999 has no corresponding order in the fixture. O101 has two event rows. These are three different observations, not one unexplained difference in a total.
For a real export, inspect missing identifiers before grouping too. Do not quietly turn missing IDs into a shared value such as unknown: unrelated records would then appear to belong to one order. Scope identifiers to the appropriate shop, account and environment. If exports use different IDs, establish a mapping rather than joining on equal amounts or nearby timestamps.
A discrepancy is a starting point, not a diagnosis
In this constructed fixture, the defects are known because we put them there. In production, an unmatched ID could instead come from a different date boundary, a test environment, an incomplete export or a legitimate business state excluded by one report. Preserve those possibilities until you check the records.
Browser delivery and payment confirmation also have different failure modes. Stripe’s Checkout fulfillment guide explains why a successful payment does not guarantee a visit to the return page. A return-page event alone is therefore not a complete payment confirmation mechanism.
Server-side delivery needs care too. Stripe documents duplicate webhook deliveries, so receiving a notification is not the same as safely applying its business effect once. A provider event ID and an order ID solve different identity problems: several distinct events can concern the same order. This article does not implement a payment handler or prescribe one universal deduplication key.
Likewise, sendBeacon() returning true means the browser queued data for transfer, not that the analytics application accepted and stored a purchase. That is the documented return-value contract, not evidence that a particular collector lost an event.
Reconcile identities before interpreting attribution
A useful reconciliation keeps separate lists for paid orders without the expected event, events without a matching paid order, repeated event rows, and amount or currency disagreements. Measure those categories over time using a stable contract. Keep unmatched revenue visible rather than inventing a channel to make an attribution chart add up.
Fixing collection coverage does not prove that a channel caused a purchase. Attribution is another question about how credit is assigned. It is worth separating that question from whether the purchase records agree in the first place.
The small lesson is not that every dashboard is wrong. It is that matching totals are weaker evidence than they look. Keep the aggregate check, but follow it with a record-level comparison before calling a revenue integration reconciled.
