When merging ad files, avoid counting the same record twice. Check row scope, ID fields, and totals to prepare data more reliably.
If merging two ad files suddenly increases your total expenditure, it doesn't necessarily mean the campaign truly spent more. The same record might have been added twice, total rows counted along with detail rows, or different breakdowns combined into one table. Using a "remove duplicates" command without understanding what the rows represent may delete authentic data.
This guide explains basic checks you can perform before merging ad reports. The goal is not to delete as many rows as possible but to assess each record exactly once in its correct scope. Examples here are hypothetical and should be adapted to your file's structure.
First, Define the Meaning of a Row
One table's row might represent a campaign's daily total. In another file, the same campaign may be represented by multiple rows at placement, device, or ad level. Having the same visible name doesn’t necessarily mean those rows are duplicates.
Write a brief sentence describing the detail level of the file. For example, “This report contains one row per campaign per day.” Then verify this with a few example records. If a campaign has multiple rows for the same day, find which other columns distinguish these rows.
Preserve the Original File
Do not start cleaning on the original file copy. Keep the source file unchanged and work on a copy labeled with date and processing notes. Being able to see which rows were removed if an error is discovered later is more reliable than guessing totals afterwards.
Note the file extraction time, selected date range, and report filters. Two files downloaded the same day may have different coverage. Separate helper columns you add in the working copy from original data, so exported cleaned files won't mistakenly treat notes as metrics.
Broaden the Matching Key Beyond the Name
If campaign ID exists, keep it in a separate column. Consider channel, ad account, date, and necessary breakdown fields together. The combination of fields that distinguish a record can be termed the “matching key.” This is not a software feature but a working rule defining which rows represent the same scope.
Don't delete rows only based on campaign name. The same name can be used across different accounts, or names may have changed over time. If ID or scope is missing and matching is uncertain, separate those records as “pending review.” Don’t assume ambiguous matches are duplicates automatically.
Distinguish Exact Duplicates from Different Versions
If all scope fields and metrics are identical, rows might be exact duplicates. But if scope is the same and numbers differ, one report might have been taken later. Instead of summing or randomly deleting, check the report time and source.
Keep a small decision log: which file was chosen as primary, why, and which rows were excluded. Apply your source priority consistently. Do not blindly assume “the latest file is always correct”—coverage might also have changed.
Keep Total Rows Separate
Some files include a general total row alongside detail records. Including totals together with details may double-count values. Pay special attention to summary rows frequently at the file start or end.
Similarly, do not combine campaign-level totals with that campaign’s ad-level rows into one sum. First, confirm the report is at a single detail level. Even if column headers look identical across files, verify separately that rows represent the same type of information.
Do Not Sum Ratios Directly
Numbers like spend and clicks can be summed directly, but percentages or rates calculated from those numbers cannot. For example, summing click-through rates of two rows won’t yield the overall CTR. A simple average can be misleading if rows have different weights.
Recalculate rates in merged reports from the appropriate underlying numbers wherever possible. If numerator or denominator is missing, indicate the gap rather than producing incorrect results. Also, do not combine amounts in different currencies or metrics with different definitions as if they are uniform. First establish comparable scope.
Perform Reconciliation After Cleaning
Record row counts, date coverage, and summable metrics totals before and after processing. If there’s a reduction, verify it corresponds to removed true duplicates. Totals merely approaching expected numbers is not sufficient proof.
Manually review a sample day and a few campaigns by comparing back to the source. Can you explain why each record was kept? Is it clear that a deleted row was not actually a different device or placement record? If you cannot answer, stop and adjust matching rules rather than reporting wrong totals.
Use a Repeatable Checklist
For each new file, proceed in the same order: keep the original copy, define row meaning, check ID and date fields, separate total rows, review suspicious matches, then compare before and after totals. Also note the person performing the task and the applied rule.
When planning your report uploading and analysis process, consider AdLens’s working approach. Marking missing or uncertain fields explicitly instead of zero-filling is an important part of avoiding giving clients false certainty. A good report is not just a tidy table, but a table with traceable source and transformations.
