Find the join that inflated campaign spend

A daily dashboard shows three times the spend in the source export. The increase starts on the day a conversion report was added to the query. Before looking for a billing error, find out what a row represents in each export. Two reports can agree on campaign and date while describing different slices of the same activity.

Follow one campaign through the join

Take one campaign, one account and one reporting date. In this example, the delivery export has a row for each of two placements. Their costs are USD 35 and USD 21. The conversion export has three creative rows with four, two and one conversion. Both use the same date window, and the counts reflect the same export cutoff. The totals to carry into a campaign-day dashboard are USD 56 and seven conversions.

Join the raw exports on campaign and date, and each delivery row matches all three creative rows. The result has six rows. The costs are counted three times and the conversions twice. The resulting USD 168 divided by fourteen conversions gives a cost per conversion of USD 12. The original totals give USD 8. The dashboard can therefore show a plausible-looking ratio while overstating both of its inputs. PostgreSQL describes how an inner join returns a row for each matching pair in its documentation on joined tables.

Pull those six joined rows into a small worksheet or a query result. Keep the placement ID and creative ID visible along with the exact join columns. The repeating placement costs tell you where the extra spend came from. If every cost is repeated twice or three times, compare that factor with the number of opposite-side rows per key. An overall multiplier can hide a problem that varies by campaign.

Choose what a row in the output represents

For a campaign-day dashboard, aggregate the delivery rows to one row per campaign and date. Do the same for the conversion rows using the same keys and time basis. Include the account ID when campaign IDs are unique only within an account. Keep amounts in a single agreed currency per output row or apply the reporting team's approved currency conversion before combining them. If a key still occurs twice after aggregation, inspect the grouping before joining the results. BigQuery shows the same query pattern in its guide to aggregating before a join.

If the buyer needs a placement-by-creative view, each source must provide the necessary attribution detail or an agreed way to assign it. A creative-level total shared across placements does not reveal how much each placement contributed. Use separate placement and creative reports until the mapping is available, and label the campaign total so readers know which level supports the comparison.

Check what happens to a campaign-day that appears in only one export. A spend row can be present before a conversion file is ready. Preserve it with an explicit missing or provisional status until you can distinguish a complete row with zero conversions from a missing row. Also check the report date used by each system. A conversion grouped by purchase date can land on a different day from the ad interaction, even after the join has one row per key.

Reconcile the result against both exports

Freeze the source files or record their exact query windows for one closed reporting period. Compare total spend before and after the join. Then do the equivalent comparison for conversions. Set aside rows the dashboard excludes and list their values so the reason for any difference can be traced. Check one campaign with no matching conversion row, one with multiple creatives and one with both reports fully populated. Use source-level reports and qualified conversion records to validate the buyer's totals over a consistent window. The FroggyAds campaign reporting guide describes those checks in the context of a live campaign.

Save the small example beside the pipeline's scheduled run. Re-run it when a new breakdown or source table is added. If the production ratio moves, compare its two inputs with the same-day extracts before changing the campaign. The example gives the team a way to see which value duplicated and where to look in the query.

Prepared for FroggyAds with AI drafting assistance.