Merging CRM and till exports before a finance review
You must build a 'customer spend' table by combining a CRM export (14,200 customer rows) with a till-system transaction export (63,400 rows) for the finance team's quarterly review. Known issues: CRM emails are inconsistently cased with trailing spaces; 620 CRM rows share an email with another row under a different customer ID; 1,900 transactions carry a customer ID that is absent from CRM (the tills allow guest checkout); transaction amounts are stored as text with a currency symbol, and about 400 of those rows use a comma as the decimal separator; 11% of CRM rows have a blank postcode. Finance insists your totals must reconcile to the monthly revenue report, which is generated directly from the till system.
1. How should you construct the join between the two tables, and what should happen to the 1,900 unmatched transactions?
Read the worked answer
Clean the key before joining: trim whitespace, lower-case emails, and strip the currency symbol and spaces from IDs. Join on the CRM customer ID where present, keeping normalised email as a secondary check - do not join on email alone, because emails are not guaranteed unique.
On the 1,900 unmatched transactions (3.0% of 63,400):
- Do not inner-join the two tables and then report the result as total revenue. An inner join silently discards about 3% of turnover, and the figure will not tie to the till-generated revenue report.
- Use a left join from transactions to CRM so every transaction survives, with guest rows carrying null customer attributes. Mark them clearly with a channel flag.
- Quantify and disclose the match rate in the output: '96.9% of transactions matched to a known customer.'
Then reconcile deliberately: state the matched revenue, the guest revenue, and the sum, and show that the total equals the till control figure. Finance can then see that the gap is a definition (guest checkout), not an error.
2. How would you handle the 620 CRM rows that share an email address under different customer IDs?
Read the worked answer
Treat this as an investigation first, a merge second.
- Examine a sample by hand. Shared email usually means a duplicate record created at a second purchase, but it can also mean a shared household address or a generic account (info@). The correct action differs.
- Check whether both IDs carry transactions. If so, merging the IDs changes the spend attributed to each - that decision must be documented, because it moves customers between segments.
- Where they are true duplicates, keep a deterministic survivor (e.g. the most recently updated record, or the one with the most complete fields) and record the mapping old ID → surviving ID in a crosswalk table.
- Never delete the superseded rows. Keep them and a 'merged' flag so the change is auditable and reversible.
Build the crosswalk before the join so transactions from both IDs consolidate onto one customer. If you deduplicate after aggregating spend, you will double-count or lose spend depending on which row survived. Report the count: '14,200 CRM rows reduced to 13,580 unique customers, 620 merged.'
3. How do you convert the amount field safely, and how do you make the output reconcile to the finance control total?
Read the worked answer
Parse in stages and keep the raw text:
- Strip the currency symbol and non-breaking spaces.
- Do not blanket-replace commas. Apply a documented rule: if the value contains both a period and a comma, the right-most separator is the decimal mark; if it contains only a comma followed by exactly two digits at the end, treat it as a decimal comma; otherwise treat the comma as a thousands separator.
- Convert to a numeric type and validate: check the minimum, maximum and null count of the parsed column, and hand-check the 20 largest values and 20 random rows against the raw text.
Reconciliation:
- Sum parsed amounts by month and compare with the till-generated revenue report.
- If they differ, explain the difference line by line (guest rows excluded, refunds, rounding) rather than adjusting the data to fit.
- Do not drop the 11% of rows with a blank postcode - postcode is irrelevant to spend and dropping them would remove 11% of customers from every total. Use an 'Unknown' category if a geographic cut is needed, and report coverage.
Publish the control total alongside your table so a reviewer can verify it in one step.