Build an attribution reconciliation sheet without double-counting sales

Different systems answer different questions

Use a definition matrix before a number table. For every source record the outcome, counting unit, date basis, timezone, attribution window, channel scope, currency and extraction timestamp. Google explicitly documents that Analytics and Ads figures can differ even when configuration is correct. Transaction-date and counting-method differences are examples to examine in the actual configuration, not assumptions to apply automatically to every discrepancy.

Educational example, not observed platform data: the CRM contains 24 unique paid orders. A GA4 event report contains 26 purchase events, including two confirmed duplicates in the illustrative ledger. After correcting the comparison basis, 24 unique orders remain. Two advertising platforms attribute 18 and 12 outcomes respectively; a permitted order-level match identifies 8 orders claimed by both. The union is 18 + 12 − 8 = 22 unique attributed orders, leaving 2 CRM orders outside that matched union. The arithmetic depends on those verified matches; aggregate totals alone do not reveal overlap.

Keep exceptions by mechanism: duplicate events, date-boundary shifts, unmatched identifiers, refunds, non-advertising orders and unavailable observations. Some attribution reports use fractional credit or modeled outcomes, so their totals may not map one-to-one to individual orders. When exact matching is unavailable, label the discrepancy unresolved. A reconciliation that acknowledges limits is more useful than a fabricated adjustment assigning every sale to a channel.

A useful discrepancy has an explanation

• Choose a business ledger for unique fulfilled or paid orders, and treat platform attribution as another view. Neither an imported purchase event nor an attributed result is automatically an additional sale.
• A definition matrix prevents repeated troubleshooting of the same expected difference. Preserve agreed settings and the date they changed.
• An exception owner and evidence column turn discrepancies into actions: investigate tracking, clarify the counting rule or wait for maturity, rather than blindly changing campaigns.

Reconcile definitions before investigating totals

1. Select a completed cohort and a common business outcome. Freeze extraction timestamps and retain the original exports so that later revisions can be explained.
2. Complete the definition matrix for CRM, GA4 and each provider. Inspect the actual conversion columns and settings before comparing similarly named headings.
3. Reconcile within each source first: duplicates, invalid states, payment reversals and date boundaries. Use permitted identifiers or aggregate evidence; do not distribute personal customer records in an advertising report.
4. Where a reliable match exists, calculate intersections and the unique union. Where it does not, report separate source totals and state that unique overlap cannot be measured from the available data.
5. Assign unresolved differences to an owner with a next check date. AdAce Ads can support a read-only review of accessible advertising and GA4 records, but external CRM joins and their availability must be verified independently.

An unexplained gap is not evidence of fraud

Sources and further reading

• Google Ads: Analytics import and conversion discrepancies