← Tabrizi Analytics  ·  Sales funnel case study  ·  Code

Sales funnel · public data

Which leads become customers, and how fast?

A small dashboard on a real sales funnel. The same questions I'd ask of any team's CRM data.

leads, Jan–May 2018
became customers
win rate
median days to close
sales reps

Data: Olist, an online marketplace in Brazil. A lead is a business that asked to sell there; a customer is one that signed. Two public files, joined on lead ID.

1 · Where customers come from

Win rate by lead source

  • Search brings of leads and wins .
  • Social and email bring and win .
  • No source recorded wins most: . The best-converting group is the one nobody tagged.

2 · How fast they close

Share of customers won, by days since first contact

  • Half close within days.
  • within 60 days. The rest trickle in over the next four months.

3 · Who closes them

Customers per sales rep

  • 5 of reps closed of customers.
  • This file names a rep only on won deals, so it shows volume, not win rate.

4 · Can we see it coming?

Forecast vs. actual customers, by lead month

  • Forecast = a month's leads × each source's Jan–Mar win rate.
  • April: forecast, actual. May: forecast, actual.

Why May fell short: not fewer leads or a different mix (the forecast counts both). reps who closed about customers a month made their last sale by April 30. That could explain about half the gap.

Next step

What I'd set up in the CRM

  1. One funnel view, every week. Leads → each deal stage → signed, by source and by rep. Lead source required on every lead.
  2. Work the first two weeks. That's when most deals close. Leads quiet for 60 days move to nurture emails.
  3. Forecast and alert. Leads × win rate by source each week. When actual falls well short, find the cause: source, rep, or step.
How I did it

Steps

  1. Joined the two files on lead ID.
  2. Kept leads from January to May 2018 (the deals file starts in December 2017).
  3. Counted leads and customers by source, days to close, and customers by rep.
  4. Forecast: April and May leads × the January–March win rate of their source.

Checks

  • All deals matched a lead; no duplicate IDs.
  • deal is dated 2 days before its first contact (a data slip). Kept; without it the median is 12.5 days instead of 12.
  • Not recorded = 813 leads Olist tagged "unknown" plus blank.
  • May's leads had until mid-November to close. By the curve, that trims about 5 customers, not 45.

Source

Data: Marketing Funnel by Olist (Kaggle), two CSV files, CC BY-NC-SA 4.0.

A case study by Tabrizi Analytics, built from public data. Not affiliated with or endorsed by Olist. Code: every file, viewable and downloadable. More case studies.