How to combine data from multiple sources
By the Analistable team · Updated · 5 min read
To combine data from different systems — a CRM, a billing tool, ad platforms, spreadsheets — export each to a table, agree on a common key (customer ID, email, SKU, date), and build a mapping table where systems use different IDs. Align dates, time zones and currencies, then stack sources that describe the same thing and join sources that describe different things about the same records.
Part of our guide: How to join spreadsheets on a common column
The four problems you'll meet
| Problem | Example | Fix |
|---|---|---|
| Different IDs | CRM uses 0031x…, billing uses cus_… | A mapping table (CRM ID ↔ billing ID), or match on email |
| Different granularity | Ads by day, revenue by month | Aggregate the detailed source to the coarser level |
| Different time zones | Ad account in UTC, shop in London time | Convert to one zone before grouping by day |
| Different currencies | Costs in USD, revenue in GBP | Convert with a rate table joined on date |
Build a mapping table
When two systems don't share an ID, match the records once — usually on email or company name — and keep the result as its own table:
| crm_id | billing_id | matched_on |
|---|---|---|
| 0031A00001 | cus_P8x2 | |
| 0031A00002 | cus_Q1k9 | |
| 0031A00007 | cus_Z4m0 | manual |
Every later join goes CRM → mapping → billing. Fix a bad match once in the mapping table instead of in every report. For spelling differences, Power Query's fuzzy merge helps build the first version.
Stack or join
- Stack sources that are the same kind of record: orders from Shopify and from Amazon, spend from Google Ads and Meta. Add a Source column. See combining Excel sheets into one.
- Join sources that describe different things about the same key: customers and their invoices, campaigns and their conversions. See joining spreadsheets.
Tools for the job
| Situation | Tool |
|---|---|
| A few exports, one-off | Excel or Google Sheets with lookups |
| The same exports every month | Power Query (Excel, Power BI) |
| Live connections to many systems | A BI tool or an ETL/ELT pipeline into a warehouse |
| Spreadsheets and Google Analytics, ad-hoc questions | Analistable |
Write down which source is the system of record for each field (for example, revenue comes from billing, not the CRM). Most disagreements in combined reports come from two sources holding the same number.
Worked example: CRM, billing and ad spend
A small agency wants revenue per acquisition channel for September. Three exports are involved: CRM contacts (email, channel), billing invoices (customer email, amount, date) and ad spend (channel, date, cost).
- Normalise the key: lower-case and trim emails in both the CRM and billing exports.
- Join invoices to CRM contacts on email, so each invoice gets a channel. Invoices with no CRM match get the channel “Unknown”.
- Aggregate both invoices and ad spend to channel by month.
- Join the two monthly summaries on channel and month.
| Channel | Revenue | Ad cost | Revenue per £1 spent |
|---|---|---|---|
| Google Ads | 18400 | 4600 | 4 |
| Meta | 9000 | 3600 | 2.5 |
| Organic | 12300 | 0 | n/a |
| Unknown | 2100 | 0 | n/a |
Total revenue is 18,400 + 9,000 + 12,300 + 2,100 = 41,800 against ad cost of 4,600 + 3,600 = 8,200. The Unknown row is the useful warning: 2,100 of revenue couldn't be attributed, which usually means invoices billed to a different email than the CRM holds. Fix those in the mapping table rather than in the report.
Order of operations
Most wrong totals in combined reports come from doing the steps in the wrong order. A dependable sequence:
- Export each source with an explicit date range and note the time zone.
- Clean keys (trim, case, leading zeros) in each source separately.
- Stack sources that are the same kind of record, adding a Source column.
- Aggregate detailed data to the level of the report (day, month, customer).
- Join the summaries. Joining before aggregating repeats rows and inflates sums.
- Reconcile: each source's total in the combined table should equal its total in the original export.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Revenue higher than the billing system shows | Joined invoices to a table with several rows per customer | De-duplicate the lookup table or aggregate first |
| Daily figures off by one day | Sources use different time zones | Convert timestamps to one zone before taking the date |
| Many unmatched records | IDs formatted differently (cus_123 vs 123) | Strip prefixes or use a mapping table |
| Totals change between runs | Exports cover slightly different date ranges | Fix the range and record it with the export |
Repeating it every month
Keep the raw exports untouched in a dated folder, keep the mapping table as its own file that you add to over time, and rebuild the combined table from those each month with the same steps. Power Query, a SQL script or a short Python notebook all work; what matters is that the steps are recorded rather than redone by hand. For ad and analytics sources specifically, combining Google Analytics and Search Console data and merging GA4 data with Google Ads cost data cover the source-specific quirks.
Checking the combined data
- Totals per source: revenue in the combined table should equal the billing export's total for the same period; ad cost should equal the sum of the ad exports.
- Unmatched share: track the percentage of revenue that lands in Unknown each month. A sudden rise usually means a source changed its ID format.
- Duplicate keys: the mapping table should have each CRM ID and each billing ID once. Count both before joining.
- Date coverage: every day of the period should appear in each daily source; a missing day often means an export ran before the day closed.
Frequently asked questions
- How do I combine data from two systems that use different IDs?
- Match the records once on a shared attribute such as email, store the result as a mapping table, and join through it.
- Should I combine data in Excel or a database?
- Excel and Power Query are fine for exports up to around a million rows per table. For larger data or many live sources, use a database or BI tool.
- How do I combine daily and monthly data?
- Aggregate the daily data to months first, then join on the month.
- What is a system of record?
- The one source that is treated as correct for a given field, such as billing for revenue or the CRM for account owner. When sources disagree, the system of record wins.