Analytics & BI · Sales funnel

A sales funnel built from 13 source tables

An automotive retail group collected leads through five channels across 13 CRM and ERP tables. The pipeline combines these records into one sales funnel, with consistent lead counts and conversion at each 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

Lead counts differed between systems

Online forms, calls, showroom visits, manual entries and CRM leads used different formats. Comparing conversion meant first reconciling records across 13 tables.

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

The dashboard shows funnel KPIs, the share of each lead channel and conversion by brand using the same data model.

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

Managers can compare conversion by brand and consultant, then examine the stage where results differ.

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

How lead counts are checked

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

Sales reporting from a shared model

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