Analytics & BI · Sales funnel

Unifying a fragmented sales funnel from 13 source systems

An automotive retail group captured leads across five channels and 13 disparate CRM/ERP tables — and no one could agree on the numbers. I built an Apache Spark pipeline that unifies them into one tested, BI-ready funnel with conversion visible at every stage.

RoleSole Data Engineer

IndustryAutomotive retail

Scope13 sources · 8 stages

StackPySpark · Apache Airflow · PostgreSQL

13source systems unified

5lead channels into one funnel

‹3×›faster reporting (add real number)

The problem

Five channels, thirteen tables, zero agreement

Leads arrived from online forms, inbound calls, showroom visits, manual entry and the CRM — each landing in its own shape across 13 CRM/ERP tables. There was no single funnel, so the business flew blind on conversion.

Managers reconciled lead and opportunity counts by hand — days of work, every reporting cycle.
Numbers disagreed between systems; nobody trusted the funnel.
Conversion per stage (lead → qualified → test drive → deal) was invisible.
The same customer appeared in several channels — duplicates inflated the top of the funnel.

What I built

One pipeline, eight ordered stages, a single funnel

I built a universal SQL engine on Apache Spark that runs eight ordered stages. The first five normalize each lead channel into a common shape; the next two derive funnel levels and per-stage metrics; the last merges everything into one analytics-ready funnel table that Power BI reads directly.

Source tables are pulled with parallel JDBC extraction, and stages are chained in memory — no intermediate writes to the database — so the whole funnel rebuilds in one clean pass.

Sources — 5 channels · 13 tables

Online

Calls

Visits

Manual

CRM leads

1–5

Unify channelsPulled in parallel, normalized to one common shape

Lead levelsEvery lead mapped to funnel levels L1–L6

Metrics + quality checksConversion computed · duplicates removed · data validated

One funnel tableA single analytics-ready source of truth

Power BI dashboardLive funnel KPIs anyone can read

The result

One funnel dashboard the whole group reads

From the unified model, decision-makers get live funnel KPIs, channel mix and per-brand conversion — at a glance.

Sales Funnel · KPI overview 2019–2026 · all brands

368,695Traffic

249,212Leads

120,507Visits

70,394Test drives

34,815Contracts

33,901Deliveries

Sales funnel — L1 → L6

L1 · Traffic

368,695 · 100%

L2 · Target lead

249,212 · 67.6%

L3 · Showroom visit

120,507 · 32.7%

L4 · Test drive

70,394 · 19.1%

L5 · Contract

34,815 · 9.4%

L6 · Delivery

33,901 · 9.2%

Lead registration channels

Visits 49.5%

Manual 19.3%

Online 19.0%

Calls 11.8%

Other 0.4%

Top brands by conversion (L6)

1Lexus14.7%

2Mazda13.4%

3Toyota11.9%

4Volkswagen11.2%

5Renault10.4%

61.1%Lead → Visit

32.3%Lead → Test drive

23.8%Lead → Contract

19.1%Lead → Delivery

Drill-down

From the funnel down to brand and consultant

The same model powers detailed views — conversion by brand and a performance leaderboard — so managers act on facts, not gut feel.

Brand	Traffic	→ Lead	→ Visit	→ Test drive	→ Contract	→ Delivery
Luxury	99,887	63.5%	34.0%	22.0%	7.6%	7.6%
BMW	30,974	71.6%	40.0%	24.1%	6.5%	7.0%
Mercedes-Benz	45,799	49.5%	27.8%	19.0%	8.3%	8.6%
Audi	4,544	64.7%	34.8%	26.1%	4.8%	4.8%
Volvo	12,791	81.0%	42.3%	24.8%	8.6%	7.3%
Lexus	5,779	90.7%	32.4%	26.2%	6.9%	6.4%
Mass-market	240,485	72.8%	35.8%	20.1%	11.3%	10.9%
Toyota	71,644	67.3%	34.6%	17.6%	11.4%	11.9%
Renault	25,217	69.3%	31.3%	24.8%	9.6%	9.4%
Volkswagen	22,005	68.8%	27.1%	16.7%	12.9%	13.4%
Škoda	25,133	73.4%	42.1%	28.7%	13.6%	14.8%
Mazda	17,099	86.7%	49.6%	19.1%	11.0%	9.0%
Total	368,695	67.6%	32.7%	19.1%	9.4%	9.2%

Top consultants by conversion (L6)

1O. F.99 deals82.5%

2Y. C.382 deals64.6%

3P. A.451 deals49.9%

4Y. P.221 deals29.1%

5D. G.125 deals28.9%

Lowest conversion — coaching targets

1V. K.21 deals0.2%

2I. H.17 deals2.0%

3P. K.35 deals2.5%

4Y. M.16 deals3.0%

5N. L.58 deals3.2%

Built to be fast & affordable

A full rebuild in one clean pass

Parallel extraction (10 threads) pulls the 13 source tables concurrently instead of one by one.
Zero intermediate writes — stages are chained in Apache Spark memory, cutting database round-trips and storage.
Full, deterministic rebuild — the whole funnel is recomputed every run, so there's no drift or stale partial state.
Runs on a modest Apache Spark cluster — the universal engine scales by config, not rewrites.

Built to be trusted

The funnel the business actually believes

Data quality, built in — not bolted on

Deduplication across channels — the same customer in online + calls + CRM is collapsed to one lead, so the funnel top isn't inflated.
SQL data-quality checks on key stages (e.g. test-drive consistency) catch broken records before they reach a report.
Stage-count reconciliation verifies each funnel level against its sources.
Telegram alerts on every run (start · finish · failure) — I hear about a problem before the business does.
Apache Airflow orchestration with retries keeps the daily refresh reliable.

The outcome

From hand-reconciled guesswork to one trusted funnel

Before

5 channels reconciled by hand
Numbers disagreed between systems
Per-stage conversion invisible
Duplicates inflated the funnel

After

One trusted, BI-ready funnel table
Automatic refresh, validated each run
Conversion visible at every stage
Deduplicated, reconciled lead counts

Quantified business results (reporting time before/after, conversion lift) — add real numbers.

Stack & role

How it's built

Sole data engineer — architecture, build, data-quality and deployment.

Apache SparkPySparkApache AirflowPostgreSQLMinIO (S3)Power BITelegram alerts