Ad Attribution and Reconciliation: Why Two Correct Reports Can Disagree

When advertising reports disagree, the first reaction is often to investigate data loss. But if one report groups conversions by click date and another by conversion date, their daily totals should differ even when they use identical input. Aligning the comparison before investigating logs saves a great deal of unfocused debugging.

The models and diagnostic steps below are educational examples, not any platform’s billing policy.

Store three kinds of facts separately

Consider a user who clicks ad A on Monday, clicks ad B on Tuesday, and makes a payment on Wednesday. The payment is an observed event. Assigning it to B is the output of an attribution rule. Whether it creates a charge, and for how much, depends on a separate billing rule.

These can be represented as three types of records:

• Event facts: each click or payment’s event ID, occurrence time, and receipt time.
• Attribution results: conversion ID, credited touchpoint, weight, model version, and calculation batch.
• Settlement details: settlement key, currency, amount, rule version, accounting period, and adjustments.

This follows the fact-modeling principle of declaring grain first: define the observation represented by one row before choosing dimensions and measures. Copying a payment amount to every touchpoint and then summing it violates that grain. Kimball: Fact Tables

For a conversion worth 100 currency units, weights of 0.4 and 0.6 give A and B attributed amounts of 40 and 60. Store the original amount and the weights; do not sum two copies of the original amount into 200. Precision, rounding, and allocation of the remainder must also be defined so the final amounts reconcile.

Define the reconciliation contract before writing SQL

An executable reconciliation contract needs at least the data scope, time axis, time zone, conversion window, model version, deduplication key, refund treatment, cutoff time, and rounding precision. Omitting one can turn a difference in definitions into an apparent processing defect.

Google Ads documents differences between reporting by conversion time and by the time of the preceding ad interaction. Even columns with the same business label may use different time semantics. Verify the actual API and column definitions used by an integration. Google Ads: Attribution reports

For the example above, I would expose two views. A conversion-date view answers “How many conversions occurred on Wednesday?” A touchpoint-date view answers “How many conversions did Tuesday’s advertising eventually generate?” The first describes business activity; the second evaluates a cohort of advertising activity. They can reconcile over a complete observation period without matching on every day.

Why real-time results need revisions

Wednesday’s early report may be missing a payment callback that arrives that afternoon. A refund may arrive on Thursday, followed later by evidence that a touchpoint was invalid. Maintaining only an overwritable aggregate makes these changes difficult to explain.

One traceable design keeps as_of_time and result versions. The event table preserves source facts, the attribution table records the decision at a particular cutoff, and aggregates are generated from a selected version. Externally, distinguish provisional results from closed periods. After closure, record adjustments rather than silently replacing the old result.

A longer window tolerates later data, but increases state, storage, and recomputation costs. There is no universal “seven days is best” setting. Observe callback-delay distributions, then choose a window that also meets the business and contractual requirements. Events beyond that window still need an explicit disposition; closing a window must not erase them from the audit trail.

Drill from aggregate differences down to events

My preferred sequence starts by aligning cutoff times, then narrows the comparison:

1. Compare source inventories, partition completion, and collected record counts to identify incomplete input.
2. Compare pre- and post-deduplication counts, invalid events, and late arrivals to locate filtering differences.
3. Split the delta by source, event date, and event type so positive and negative differences cannot cancel each other out.
4. Full-outer-join the problematic slice by conversion ID. Separate left-only records, right-only records, and shared records with different fields.
5. For shared records, compare touchpoints, weights, window decisions, and rule versions.

Difference                 Next evidence to inspect
Present only on the left   Raw callback, receipt time, right-side filter reason
Different attribution      Candidate touchpoints, time zone, model and rule versions
Same count, different sum  Currency, refunds, weights, precision, rounding

The investigation should end with a small set of reproducible examples, not merely “a 0.2% difference.” The same percentage can mean very different risks for a small test campaign and a major settlement period. Acceptance thresholds should consider absolute record counts, money, and critical sources. Every accepted discrepancy needs an explanation.

References

• Ralph Kimball: Fact Tables , on consistent fact grain and measures.
• Google Ads: About attribution reports , on attribution reports, standard reports, and time semantics. Platform policies can change.