Affiliate Reporting: Prevent Duplicate Totals When Joining Tables

A campaign export can be correct on its own and still produce a misleading summary after it is combined with another table. One common mechanism is a join that finds several matches for a row carrying spend, orders, or commission. Each match can repeat the original measure.

For performance marketing media buying, a small control-total check can expose this problem before a report informs a budget decision. The following workflow is a proposed reporting practice, illustrated with hypothetical data rather than BlueFriday campaign results.

Write down what one row represents

Before merging files, describe each table in one sentence. For example: “Each row in the performance export represents one campaign on one reporting date.” A separate creative register might contain one row for each creative assigned to that campaign. Those tables have different levels of detail.

If the first table has no creative-level breakdown, adding creative names does not create one. Repeating a campaign total beside every creative gives the appearance of detail without evidence of how results were distributed. This distinction applies equally to ecommerce CPS offers and virtual-product campaigns.

Count matches before adding measures

Consider a hypothetical campaign with reported spend of 120 units. Its campaign identifier appears three times in a creative register because three creatives belong to it. Joining on that identifier can produce three rows, each displaying 120. Summing the joined spend gives 360, even though the original report contained only 120.

The error is multiplication through repeated matches. It is not evidence of extra delivery. Do not divide the repeated spend evenly among creatives unless an explicit allocation model is required, and label any such allocation as an estimate rather than observed creative performance.

Choose the output you actually need

• Campaign summary: Keep one row per campaign and date, with creative names represented as descriptive information where appropriate.
• Creative performance: Obtain measures reported at creative level rather than inheriting campaign totals.
• Campaign classification: Prepare a lookup with one valid classification per complete join key, resolving conflicting entries first.

Use the complete identifier context. If campaign identifiers are only unique within an account, include the account identifier. If classifications change over time, define which version applies to each reporting date. Do not silently choose the first matching label just to make duplicates disappear.

Keep a reconciliation beside the merge

Record the original row count, distinct key count, and totals for each additive measure. After the join, check those values again. Inspect keys with multiple matches and count unmatched rows separately. A join that drops unmatched rows can understate totals even when it creates no duplicates.

Rates require a separate check: carry the underlying numerator and denominator through the validated merge, then calculate the intended rate at the final reporting level. A sum of conversion rates has no useful interpretation as a campaign conversion rate.

Document any intentional exclusions or aggregation so another analyst can explain the difference between input and output totals. BlueFriday's media buyer overview provides the broader partner context. For the report itself, the practical release gate is narrower: every repeated match and every changed total should have an explicit, reviewable explanation.