LEAD → MQL → SAL → DSO → WIN, end to end: raw CRM and marketing-automation extracts in, a layered SQL warehouse in the middle, and a filterable two-page PDF dashboard plus a written findings set out.
| Stage | Meaning |
|---|---|
| LEAD | Record created from any inbound or outbound source |
| MQL | Marketing qualified - meets the scoring threshold |
| SAL | Sales accepted - a rep has agreed to work it |
| DSO | Deal-stage opportunity - qualified into the pipeline with an amount |
| WIN | Closed won |
Built to answer five questions a revenue leader actually asks:
- Where is the funnel leaking, and is the leak volume or quality?
- Is new-business ($NB) bookings growth holding up, or is expansion carrying the number?
- Which channels are worth the spend once you measure past the MQL?
- How long does each stage take, and what moves it?
- Is there enough pipeline behind the number we are forecasting?
Dashboard: outputs/revops_funnel_dashboard.pdf
Written findings: outputs/findings.md
A PDF has no dropdowns, so filtering happens at render time and every applied filter is stamped on a banner across the top of each page. The artefact is therefore always self-describing - you can tell from the page which slice produced the numbers on it.
python src/dashboard.py # everything
python src/dashboard.py --region EMEA --from 2025-01-01 --to 2026-06-30
python src/dashboard.py --segment Enterprise --channel "Paid Social"
python src/dashboard.py --business-type "New Business" --region NA APAC
python src/dashboard.py --book # one page set per regionAvailable filters: --region, --segment, --channel, --business-type,
--from, --to. All accept multiple values. Each run writes a matching
findings_<slug>.md alongside the PDF, so the narrative always matches the
filtered numbers rather than being written once against the total.
One caveat the dashboard states on itself: campaign spend is only recorded at channel level, so cost-per-win cannot be split by region or segment. When those filters are active, the chart says so rather than letting the reader assume the spend was filtered too.
On the visuals: these reports follow Power BI report conventions - page tabs, filter rail, granularity controls, cumulative rows with a conditional change-over-prior-year band - but they are rendered in Python from the SQL layer, not exported from a .pbix. A Power BI-ready star schema and the DAX measures are in
powerbi/if you want to build the interactive version.
| Layer | Tool |
|---|---|
| Warehouse | DuckDB |
| Transformation | Layered SQL - staging views to conformed facts and dimensions to marts |
| Testing | 16 automated data tests: uniqueness, not-null, referential integrity, accepted values, ranges, business rules |
| Analysis & reporting | Python (pandas, matplotlib) rendering a print-ready PDF |
pip install -r requirements.txt
python src/generate_data.py # writes the raw source extracts
python src/build.py # builds the warehouse, runs the data tests
python src/dashboard.py # renders the PDF and findings.mdsources (6 CSV extracts: leads, stage events, opportunities, reps, campaigns, targets)
|
+-- staging/ cleaning and flagging only, one model per source
| stg_leads dedupe form postings, normalise region and country
| stg_stage_events collapse to earliest posting per lead per stage
| stg_opportunities flag non-positive amounts and impossible timelines
| stg_reps / stg_campaigns / stg_targets
|
+-- marts/
dim_rep, dim_campaign
fct_lead grain: one lead, whole journey flattened on
fct_opportunity grain: one opportunity, validity rules applied
mart_funnel_monthly conversion by creation cohort, all four steps
mart_channel_quality volume vs quality vs cost, by channel
mart_velocity stage duration medians and p90
mart_pipeline_coverage open pipeline against target by quarter
mart_nb_growth new-business bookings, share, QoQ and YoY growth
mart_enrichment_impact enriched vs not, like for like
mart_rep_performance win rate and cycle by rep
mart_data_quality the pipeline's own health
Two modelling decisions worth calling out:
Conversion is cohorted on lead creation, not on stage date. Measuring MQL→SQO by the month the SQO happened makes conversion move whenever lead volume moves, which is how a demand-gen team ends up celebrating a mix shift. Cohorting fixes the denominator to the leads that entered together.
New business is reported separately from expansion. Total bookings can hold up on renewals and upsell while new-logo acquisition shrinks underneath. $NB growth is shown year on year rather than sequentially, because YoY survives seasonality - and the growth line suppresses both the right-censored quarters at the end of the window and the ramp quarters at the start, where a growth rate off a near-zero base is arithmetic rather than information.
Bad rows are flagged and counted, never silently dropped. Duplicate lead postings, missing regions, orphan campaign references, negative amounts and deals closing before they opened all get quarantined, and the counts are published as a data product that sits in the same dashboard as the KPIs.
- New-business growth is the number that moved. $NB bookings are down year on year in the latest comparable quarter even though total bookings look steadier, because expansion is carrying the difference. Reporting them blended would have hidden it.
- The highest-volume channel is nearly the worst channel. Paid Social ranks first on MQL volume and last on MQL→SAL, at roughly a third of the best channel's rate, and costs about twice the median per win. This matters because MQL volume is what most demand-gen targets are written against.
- Splitting SAL from DSO separates two different problems. MQL→SAL is lead quality and routing; SAL→DSO is qualification discipline. They have different owners and different fixes, and a single combined qualification rate averages them into one uninformative number.
- One region broke for two quarters and recovered. EMEA's win rate fell well below the other regions through Q4 2025 and Q1 2026 and then returned - a time-boxed, single-region dip invisible in the blended figure.
- Enterprise pipeline coverage sits below the 3x convention while other segments clear it, and Enterprise also carries the longest cycle.
- Enriched leads convert better, stated as an association rather than a causal claim, since enrichment coverage is itself higher on the better inbound channels.
- Source rows failing validation are quarantined and counted before any KPI is calculated, and 21 automated data tests pass on the modelled layer.
This project exports a Power BI-ready star schema. Rebuild it with:
python ../export_powerbi.py # or python export_powerbi.py from the portfolio rootpowerbi/ then contains one CSV per fact, dimension and reporting mart, plus a
generated dim_date. powerbi/measures.dax holds every measure, written by
hand rather than dragged in - time intelligence, like-for-like prior-year
comparison, and the guards that stop a partial period reading as a decline.
Build instructions, the model layout and the colour palette are in
POWERBI_GUIDE.md at the portfolio root.
The dataset is simulated, and this README says so on purpose - it is a transformation, modelling and analysis project, not a claim to proprietary CRM data. The generator plants the signals; the analysis recovers them without being told where they are, and the raw extracts carry realistic defects so the pipeline has to earn its numbers. Swapping the generator for a real Salesforce or Marketo extract means rewriting the staging models and nothing else.