The Dealership CRM Challenge
A multi-brand dealership receives thousands of digital leads a month from Meta and Google campaigns. Those leads are called by a CRM team, handed to sales executives as enquiries in the dealer management system, routed to sub-dealers in other emirates, or declined. Each step lives in a different system: lead sheets, call logs, SE task exports, sales invoices. Nobody could answer the simplest question: of the leads we paid for, how many did we actually reach, qualify, and sell?
What Makes This Different
This is not a tutorial dataset. It is the anonymized twin of a live model that a CRM team runs its day on.
- Journey Resolution Engine: calls are clustered into journeys per customer and each lead is resolved once to a single journey key; every downstream status is a thin read off that key rather than an independent re-match.
- Daily Pacing & Forecasting: a run-rate engine that sets each agent's target for today and tomorrow from a frozen through-yesterday snapshot, with 7- and 14-day median projection bands.
- Fair Attribution: invoices attributed back to the CRM agent who last touched the journey, validated 100% against the sales executive of record, with an explicit Attribution Gap measure for broken enquiry chains.
- PII-Safe by Construction: a Power Query anonymization layer that pseudonymizes customers, staff, brands, campaigns and task codes after all real-data matching has run, so relationships survive and identities do not.
Key Features & Technologies
Fully interactive. All customers, staff, brands, branches, campaigns and task codes are pseudonymized (Brand 01, Agent 07, TC-25...). Counts, rates and timings are the real production figures for the September to August window.
Deep-Dive Analysis by Page
Built for the CRM manager, the sales director and the agent on the phone, each with their own view of the same truth.
- Executive Overview: total leads, qualified, sub-dealer, invoiced and retail sales with month-over-month arrows on every KPI card. Every comparison is period-aware: select a month and it compares to last month; select a quarter and it compares to last quarter.
- Lead Source Report: leads, qualification tiers (Qualified / + Sub Dealer / + Unqualified) and decline reasons by campaign, ad set and lead source bucket, with a three-tier qualification taxonomy so marketing and CRM finally count the same thing.
- Agent Performance: calls made, connected rate, qualified rate, average journey duration, open retries and invoiced attribution per agent, colour-coded against team thresholds and trended month-over-month.
- Journey Intelligence: how many calls it takes to resolve a lead, percentage resolved on the first call, average lag from lead to first contact, and the age of leads never contacted.
- Qualification Pacing & Workload Forecast: a live "are we on track" engine. Last month's conversion rate becomes this month's target; the gap is spread across agents by fair share; each agent sees a fixed plan for today and a projected requirement for tomorrow that accounts for retry backlog, new-lead intake and expected inbound interruptions.
- Sales & Attribution: CRM-attributed versus walk-in sales, sales executive conversion and enquiry ageing, top closer by branch, and days from lead to invoice, tied back to the originating campaign.
Engineering NotesEngineering Notes
The parts of this project that do not show up in a screenshot.
Resolve Once, Hydrate Many
Lead Status used to be a 150-line independent matching engine, and so was Terminal Reason. Both were rebuilt as thin wrappers reading off a single Matched Journey Key with outcome-priority tie-breaking. Validated row-for-row against the old logic (54,403 of 54,403 exact) before going live.
Anonymization That Survived an Audit
Fourteen mapping expressions and two helper functions pseudonymize every identifier at the end of each table's M partition. A final sweep of calculated-column expressions caught a hidden column that had bypassed the mapping and two measures hard-coding real names as SWITCH keys. Both fixed; zero real identifiers remain outside the sealed raw tables.
Two Calendars, One Answer
Leads are dated by submission, journeys by last activity. Every fact table relates to both a submission Calendar and a Last Activity calendar, and every time-intelligence measure detects which one the viewer filtered so KPI cards never silently ignore a slicer.
Measures as Documentation
All 410 measures carry a written description of what they count, what they exclude and why, including the date and reason of every fix. Each KPI follows one pattern: base value, period-aware last-period sibling, MoM change, arrow text, and a colour measure for conditional formatting.
Explore More ShowcasesExplore More Showcases
See our other enterprise-grade solutions.
View PortfolioRunning a dealership or a call center on spreadsheets?
We connect your lead sources, call logs and DMS into one model your team can act on every morning. Let's start with your data.
Book a Strategy Call