Practical framework / 5 MIN READ
Keep a data join from multiplying campaign spend
Prove the relationship between two tables before adding their measures.
AI-assisted research and analysis for the SNS Creative Fieldbook
Sources checked 2026-11-06
A combined report can look plausible while repeating the same spend across many matching records. The repair begins with expected table relationships and conservation checks, rather than a last-minute divide or deduplication trick.
What you will leave with: Create a join review that preserves spend, exposes unmatched records and explains the intended output grain.
Use this when
- Spend rises when a lead-quality table is added to a report.
- Two exports match individually but not after being combined.
This is a reporting integrity lesson, not a guide to assigning causal channel credit.
Name one row on each side
Write what one row represents in each input: one campaign per day, one lead, or one status update. List the fields that uniquely identify that row. A campaign name rarely suffices if names can repeat across accounts, platforms or dates.
Count duplicate candidate keys before joining. Distinguish a legitimate one-to-many relationship from accidental duplicates. PostgreSQL documents that joins produce a row for every matching pair. That behavior explains why a valid join can still multiply a measure when the analyst expected one match.
Choose the output before the join
If the decision compares daily campaign spend with daily qualified leads, aggregate each input to that common grain before combining. If the decision needs individual leads, keep a separate spend table or define an explicit allocation rule rather than repeating total spend as though every lead incurred it.
Do not use distinct monetary amounts as a repair. Two campaigns can genuinely spend the same amount. Removing repeated values would erase legitimate cost. The correct operation depends on the identity and meaning of records, not whether their displayed numbers happen to match.
Test totals and unmatched records
Record input rows, unique keys and total spend. After each join, repeat the checks and inspect several keys with many matches. Keep a small expected-results fixture containing one matched campaign, one unmatched campaign and one campaign with several leads.
Review unmatched data separately. An inner join can drop spend for campaigns with no reported leads, making acquisition cost look better. A left join can preserve spend but introduce missing lead values. Decide whether those missing values mean no lead, a delayed export or an unidentified campaign before replacing them.
Make the invariant a release check
Define the invariant for this report: joining descriptive information should preserve the original spend total within the same scope. If the join intentionally allocates or filters cost, document the reconciliation instead. Have someone reproduce one campaign manually without relying on the final dashboard.
Keep the relationship test with the transformation. When a provider adds multiple daily rows or changes identifiers, the failure should stop publication or display a warning. Record which past reports used the faulty join and whether correcting them changes a business decision; do not silently overwrite a consequential historical conclusion.
Make the reasoning concrete.
A hypothetical campaign-day table has one $120 row. A lead table contains three leads for that campaign and day. Joining directly creates three rows each carrying $120, so summing the joined column produces $360.
Deduplicate amounts
Opening: Keep one occurrence of each spend value.
Hypothesis: The example total returns to $120.
Risk: Other campaigns with the same genuine spend would be lost.
Aggregate and reconcile
Opening: Count leads by campaign-day, then join one summary row to spend.
Hypothesis: Spend stays $120 and the lead count stays three.
Risk: Incomplete keys can still merge different accounts.
Publish $120 with three leads only after confirming the complete key and unmatched-record policy. The arithmetic diagnoses duplicated cost, not marketing effectiveness.
Take the questions into your next brief.
Use these prompts in your own notes. Nothing is submitted from this page.
Input grain
What does one row represent in each table?
Relationship
Which keys should be unique, and where are multiple matches legitimate?
Output
What decision requires the combined grain?
Invariant
Which totals should remain unchanged?
Exceptions
How will unmatched or delayed records appear?
Watch for the wrong conclusion.
- Joining only on a reusable campaign name.
- Dropping campaigns that produced no recorded leads.
- Repairing duplicates by distinct monetary value.
- Checking only the final grand total.
Your next action
Take one existing combined campaign report and document its join relationship plus a spend-preservation test.
Sources & scope
Frameworks, worksheets and hypothetical examples are original editorial synthesis. Linked primary sources support only the stated concepts. Source checking is not an independent audit of effectiveness. No named human author or reviewer is claimed.
- PostgreSQL: Table expressions →Primary technical documentation · Supports: Documents that a join returns a row for each matching pair, with different unmatched-row behavior by join type. The campaign reconciliation workflow is original editorial analysis.