ETL, short for extract, transform, load, is the process of pulling data out of the systems that hold it, cleaning and reshaping it so it is consistent, and loading it into one place where it can be reported on. In marketing, that usually means moving figures from ad platforms, analytics and a CRM into a spreadsheet, a dashboard or a data warehouse.
How ETL works
Extract is the collection step. A scheduled job connects to each system’s API, such as Google Ads, Meta, GA4, Search Console, HubSpot or Shopify, and pulls the fields you need for a date range. Tools that handle this step are often called connectors.
Transform is where most of the value lies. Typical marketing transformations include converting every platform’s spend into pounds sterling; lining up dates when one ad account reports in UK time and another in a different time zone; renaming campaigns to a single naming convention; mapping sources and mediums to channels; joining ad clicks to CRM deals on a click ID or hashed email; and removing duplicates.
Load writes the result into its destination, usually by adding each new day and replacing the last few days, because platforms revise recent figures as late conversions arrive.
A common variant is ELT, where raw data is loaded first and transformed inside the warehouse afterwards, using SQL. Small businesses often run a lighter version: a connector such as Supermetrics pulling data into Google Sheets each morning, formulas doing the transformation, and Looker Studio reading from the sheet.
Why it matters
Every platform reports in its own format, currency and time zone, with its own definition of a conversion. Without a transformation step, a dashboard that puts them side by side compares unlike things. ETL is where a business decides, once and in writing, what counts as a lead, which revenue figure to use and how each channel is labelled.
For UK businesses, two transformations come up again and again. Revenue needs a consistent VAT basis, because some systems record what the customer paid and others the net amount. And the UK GDPR principle of data minimisation means extracting only the fields you need: a hashed ID usually does the job of a customer’s name and email address. Check where each tool processes data and make sure you have a processor agreement with it.
Common mistakes
- Silent failures. An expired login token returns no rows, the dashboard shows zero spend, and nobody notices for a week.
- Pulling each day only once and never refreshing it, so conversions that platforms add later never reach the report.
- Undocumented formulas in a shared sheet that someone edits, changing every figure further down the line.
- Mixing accounts set to London time with accounts set to UTC, so during British Summer Time each source’s “day” covers a different 24 hours and daily totals never quite line up.
- Extracting every available field just in case, which slows the job, raises costs and stores personal data with no purpose.
How to act on it
Write down the transformations before choosing a tool: which currency, which VAT basis, which time zone, how campaigns map to channels and which conversion counts as a lead. Those decisions matter more than the software.
Then pick the lightest tool that does the job. A connector feeding Google Sheets is enough for many small businesses; a warehouse with scheduled transformations suits larger volumes or many sources. Set an alert for failed runs and zero-spend days, re-pull at least the last week on every run, and keep a one-page note of what each step does. Tying spend to real outcomes this way is how I report in performance marketing, where every channel answers to the same cost per lead.