Affiliate Reporting: Separate Status Snapshots from Event Logs

An affiliate conversion may appear in several records as its status changes. Before counting orders or comparing CPS campaigns, establish what each row represents. A current-status snapshot and a status-change event log answer different questions. The workflow below is a proposed reporting control for media buyers, publishers, and advertisers.

Write the row definition before the calculation

Ask the data owner whether an export contains one current record per conversion, a historical snapshot taken at a stated time, or a series of events. Record the conversion identifier, event identifier if available, status field, and relevant timestamps. Do not infer the export type from the filename alone.

A snapshot can answer which status a conversion had when that snapshot was captured. An event log can describe recorded transitions over time. Neither should be treated as a count of distinct orders until the identity and row definition have been verified.

Trace one conversion through the file

Start with a known example and inspect all its rows. Determine whether multiple rows reflect status changes, separate order lines, separate commissions, or repeated delivery of the same event. Those are different structures and require different handling.

Consider a hypothetical log in which conversion A appears once as pending and later as approved, while conversion B appears once as pending. There are three event rows and two distinct conversions. Counting rows as orders would report three orders. A current-state summary would show one approved conversion and one pending conversion only if the log is complete and those are the latest valid states at the chosen cutoff.

Define a reproducible reporting cutoff

For a current-state report reconstructed from events, agree on the cutoff time, time zone, event ordering rule, and treatment of corrections. An ingestion timestamp may describe when a system received a record rather than when the underlying change occurred. Ask which timestamp and sequence field govern status order.

• Retain raw records so a transformation can be audited.
• Use the documented conversion key rather than a campaign name as order identity.
• Resolve repeated event deliveries with a documented event key when available.
• Flag conflicting records with indistinguishable ordering instead of choosing arbitrarily.

Do not collapse legitimate order-line records into one commission row unless the reporting model calls for that aggregation. If the export does not contain enough history to establish the latest state, use a verified snapshot or label the reconstruction incomplete.

Reconcile counts and money separately

Compare distinct conversion counts and status counts with the source report under the same cutoff and filters. Then reconcile commission values using the provider's field definitions. A commission amount repeated on every status event is not necessarily an additional payable amount; a ledger of incremental adjustments is a different data model.

Teams managing performance marketing media buying can attach this row-definition note to recurring reports. The outcome is a traceable basis for campaign comparisons: what was counted, when its state was measured, and which unresolved records still limit the conclusion.