The Ads↔Orders Join Everyone Assumed Didn't Exist
Find and prove the join between web analytics and the ERP order file, so campaign revenue can be priced from actual orders instead of platform claims.
What was getting in the way.
Ad platforms report their own revenue numbers, and everyone assumed there was no way to connect ad-driven web transactions to the company's real order system — so "real ROAS" stayed unknowable.
Context
Discovered while building a three-year portfolio analysis for a distribution client; now a standing method at the agency.
How the work runs.
- 01
Test the hypothesis nobody had tested
check whether the GA4 transaction_id matches the ERP transaction file's order number (it does, through the order number — not customer identity).
- 02
Validate the match rate
89% of web transactions join cleanly.
- 03
Re-price campaign revenue from the order file
real-revenue ROAS per campaign instead of platform-reported.
- 04
Surface what the platforms can't see
measure the share of revenue with no web session at all.
Evidence from the workflow.
Each system has a role.
Web transactions
GA4
Record of truth
ERP transaction file
Join + analysis
BigQuery
Campaign mapping
Google Ads
Why this is Coworker.
Takes assignments and reports back. You hand it work, it prepares and returns a result.
- 01
Read-only access; the join method is documented and reproducible; findings stated with their match rate, not asserted as complete.
Impact / Outcomes
89% of web transactions joined to real orders
Platform-vs-reality ROAS gap exposed (2.65x reported vs 1.29x actual on the same spend)
62% of the client's revenue turned out to have no web session at all — phone, quote, and PO revenue no ad platform can see
Method generalized into a reusable procedure worth testing on every client with a transaction file
Find where a workflow like this fits.
Start with the systems, work, constraints, and authority already present in your operation.