Connecting CRM, ads, email and store data into one model without a data team
The data exists in five systems and agrees in none. The lightweight warehouse setup — a scheduled extract, one identity key, a few clean tables, a dashboard on top — that a small company can run without hiring analysts.
11 Nov 2025 · 2 min read · by HeiSyn
The question 'which campaigns produced customers who stayed' cannot be answered inside any one tool, because the campaign is in the ad platform, the lead in the CRM, the order in the store and the retention in billing. Answering it needs the data in one place with one way to link a person across systems. That used to need a data team; now it needs a schedule, a database and a few careful decisions.
Extract on a schedule
A nightly job pulls the tables that matter — contacts, deals, orders, subscriptions, campaigns, email events — from each system's API into a database. Managed connectors do this for the common tools; an orchestrator such as n8n covers the rest. Store the raw data as received; transform it separately, so a change in a transformation never loses history.
Decide the identity key
A person is linked across systems by email, normalised, with phone as a fallback and a manual mapping table for the exceptions. A company is linked by domain. These two rules, applied consistently, are most of what a 'customer data platform' does. Write them down; they are the constitution of the model.
Build a few clean tables
Customers: one row per person with source, first date, segment, value. Events: one row per thing that happened, typed, with a timestamp. Campaigns: one row per campaign with spend. Revenue: one row per order or invoice. Everything on the dashboard is a query over these four.
Put a dashboard on it and stop there
Looker Studio, Metabase or similar over the clean tables; the Monday page and a handful of drill-downs. Resist adding a chart for every question; add a query instead and answer it. The model earns its keep when a question that used to take a day of spreadsheet work takes a minute — and when the numbers from marketing, sales and finance finally agree.
Questions people ask next
- Which database should a small company use?
- A managed Postgres or a small BigQuery project is enough and costs little at this scale. Choose whatever the dashboard tool connects to easily and whatever someone on the team can query. Avoid anything that needs a specialist to operate.
- How do we handle customers with several email addresses?
- A mapping table that links aliases to one canonical identity, filled by rules (same phone, same name and address) and by hand for the exceptions that matter. Report the share of records that are unmatched so the gap is visible rather than silently distorting the numbers.
- How long does a first version take?
- One to two weeks for extracts from four or five systems, the identity rules and the four clean tables, with the Monday page on top. Refinement continues, but the first questions get answered inside a fortnight.
Want this built for your team?
A free scoping call: we map your motion, name the number that should move, and quote a fixed-scope build.
Related
- Reporting go-to-market to a board or investors: the numbers, the cadence and the honesty
- Data quality monitoring for GTM systems: catching the silent failures
- Sales activity metrics without the micromanagement: what to track, what to show, what to leave alone
- Alerts on revenue metrics: knowing on Tuesday what you would otherwise learn at quarter end
