Sales versus Leads
The founder brief references a "Custar Method Sales vs Lead". That method is not defined; this project covers the standard sales-versus-leads relationship. The Custar method will be added once its definition is supplied.
The scenario and data are provided, but tasks that depend on the undefined business rule are marked and must not be assumed.
Business scenario
Management sees new subscriptions and leads moving differently month to month and asks whether more leads actually produce more sales.
Problem statement
Relate monthly lead volume to new subscriptions (with lags), by source and region, and identify where lead growth does not translate into sales.
Learning objectives
- Align leads and sales on a time axis with appropriate lags
- Measure lead-to-sale efficiency by source/region
- Avoid causal over-interpretation
Instructions
Sales = new subscribers from converted leads (leads.converted_subscriber_id / converted_at). Compare monthly leads with conversions in the same and following month.
Tasks
- Monthly leads, conversions (by conversion month) and ratio.
- Lag analysis (0, 1 month).
- Efficiency by source and region.
- Dashboard with leads vs sales trend.
- Short interpretation with caveats.
Deliverables
- SQL
- Dashboard
- Interpretation
Evaluation rubric
| Criterion | Weight | Excellent | Proficient | Developing | Insufficient |
|---|---|---|---|---|---|
| Data preparation & validation | 20% | Correct filters and grain; validation checks documented and totals reconciled. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Time alignment & efficiency | 25% | Lags and ratios correct. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Causal caution | 15% | Correlation vs causation addressed. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Interpretation & recommendations | 25% | Clear, quantified conclusions tied to the business question, with limitations stated. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Presentation | 15% | Concise, well-labelled outputs a manager can act on without explanation. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |