← All Help

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.

Back to Ads Import

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:

  1. Field mapping: skip all explode / multi-value dimension columns.
  2. Cleaning: Deduplicate with no columns selected (unique on all remaining columns).
  3. 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.