Five clients, five definitions of a campaign
An analytics firm builds one reporting model for all of its clients and finds that every client's exports describe the same things in different ways.
Illustrative example. The organization and its data are invented to show a typical pattern. This is not a customer story.
What they hold
- Ad platform exports for each client, one file per platform per month.
- Each client's own sales and lead records, sent as spreadsheets in whatever layout the client uses.
- A naming convention for campaigns that every client agreed to and each applied differently.
The mess
Campaign names that do not join
The same campaign is spring_sale_US in one platform, Spring Sale (US) in another, and Spring-Sale-us with a trailing space in the client's sheet. Spend from one file cannot be matched to results in the next, so it is matched by hand, every month.
Two currencies in one spend column
One client runs campaigns in several countries. The platform exports spend in the account's local currency, and the combined file has a single column called spend with no currency beside it.
Dates in the reporting time zone of each platform
One platform reports by the advertiser's time zone and another by a fixed one. A conversion late in the evening lands on different days in different files, so daily totals never quite agree.
Layouts that change without notice
A platform renames a column or adds one in the middle. The firm's import keeps running and quietly loads the wrong column into the right place.
What it costs
- Analysts spend the first days of every month reconciling files instead of analyzing them.
- A client is shown a return on spend that mixes currencies, makes a budget decision on it, and later finds the figure was wrong.
- Two reports for the same client disagree, and the client stops trusting either.
- Onboarding a new client takes weeks, because the cleanup is rebuilt from scratch each time.
What clean looks like
- Every campaign maps to one canonical name, through a saved mapping that is reused each month and extended when a new variant appears.
- Spend carries its currency in its own column and is converted with a stated rate before anything is added up.
- All timestamps are stored in one time zone, with the original kept alongside.
- Column names are checked against the expected layout on every import, and a change stops the load instead of passing through.
Problems in this example
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Stray whitespace and invisible charactersTwo values look identical on screen but differ by a trailing space, a non-breaking space, or a hidden character.
- Mixed currenciesAmounts in one column are in different currencies, marked by a symbol, a code, or nothing at all.
- Missing or mixed time zonesTimestamps in one column were recorded in different time zones, or carry no zone at all.
- Inconsistent column names across filesFiles that should share one layout name the same column differently, or use the same name for different things.