Ad export pitfalls — metric duplication
Three common ad-platform export issues to confirm before import. · Updated 2026-10-01
Before confirming import, make sure these situations do not apply — or that you will handle them as described. Locply does not auto-split file imports yet; you confirm and choose the method.
Confirm-step checklist (summary)
At Confirm, acknowledge: (1) expanded columns, (2) ratio metrics like CTR, (3) date formats. Each section below lists what to confirm, what to do, and a concrete case.
Checklist
| Scenario | Confirm (no issue / acknowledged) | What to do | Case |
|---|---|---|---|
| 1. Expanded columns | Confirm the file has NO array-style dimensions unnested with Cost/Impressions copied on each row — or you will skip those dims and dedupe before SUM. | Base table: skip explode columns → Cleaning Deduplicate (all columns) → SUM metrics. Detail table (optional): keep explode columns, skip all performance metrics. | 1 ad + 3 Ad assets → 3 rows, Cost=100 each. Direct SUM(Cost)=300 (wrong). After skip assets + dedupe → 1 row, SUM=100. |
| 2. Ratio metrics (e.g. CTR) | Confirm you will NOT SUM ratio columns (CTR, CPC, CPA, Conv. rate, ROAS, frequency…). Or skip importing them as facts. | Import only additive facts (Impressions, Clicks, Cost, Conversions). Recompute ratios in charts: CTR = SUM(Clicks)/SUM(Impressions). Skip or hide CTR/CPC columns in mapping. | Two rows CTR 2% and 4%. SUM(CTR)=6% (nonsense). Correct: clicks 20+40, imps 1000+1000 → CTR=3%. |
| 3. Date formats | Confirm date columns are YYYY-MM-DD (Meta / API) or US M/D/YYYY (common Google Ads UI / Sheets). Ambiguous 1/2/2024 is treated as January 2 (US MDY). | Prefer exporting YYYY-MM-DD. Locply also accepts M/D/YYYY and unambiguous D/M/YYYY (e.g. 31/12/2024). Dates stay account wall-clock — no timezone conversion. | Google UI “8/15/2022” → 2022-08-15. Meta date_start “2022-08-15” unchanged. “1/2/2024” → Jan 2 (not 1 Feb). |
1. Expanded columns
What it is: Connector exports (e.g. Supermetrics) unnest array fields — Ad assets, labels, URL tags, sitelinks, creative components, etc. — into multiple rows and copy Cost / Impressions / Clicks onto each row.
Confirm: Either your file does not have this pattern, or you will build a base dataset without explode columns (and dedupe) before summing spend.
Method:
- Field mapping: skip all explode / multi-value dimension columns.
- Cleaning: Deduplicate with no columns selected (unique on all remaining columns).
- Optional second import for detail: keep explode columns, skip all performance metrics; never SUM spend there.
Case:
Date Ad Ad assets Cost
D1 A001 Headline 1 100
D1 A001 Headline 2 100
D1 A001 Headline 3 100
Wrong: SUM(Cost) = 300
Right: skip “Ad assets” → dedupe → 1 row → SUM(Cost) = 100
2. Ratio metrics (CTR and similar)
What it is: Rates and averages exported as columns: CTR, CPC, CPM, CPA, Conv. rate, Cost/conv., ROAS, frequency, Avg. CPC, etc. They are not additive.
Confirm: You will not use SUM on these columns. Prefer skipping them at mapping and recomputing from facts.
Method:
- Keep facts: Impressions, Clicks, Cost, Conversions, Conversion value.
- In charts / metrics: CTR = SUM(Clicks)/SUM(Impressions), CPC = SUM(Cost)/SUM(Clicks), etc.
- In field mapping: skip CTR / CPC / rate columns, or map them but never aggregate with SUM.
Case:
Ad Impressions Clicks CTR
A 1000 20 2.00%
B 1000 40 4.00%
Wrong: SUM(CTR) = 6.00%
Right: SUM(Clicks)/SUM(Impressions) = 60/2000 = 3.00%
At import
Ads Import upserts into pre-built tables with system strip/dedupe. File import does not auto-split beyond Performance vs detail grain rules.