← All files for this project · the case study

analysis.py

"""Sales funnel case study: which leads become customers, and how fast?Data (public): "Marketing Funnel by Olist" on Kaggle, CC BY-NC-SA 4.0. Olist is an online marketplace in Brazil;each lead is a business that asked to sell on it, and a deal is one that signed up. Two files:  data/olist_marketing_qualified_leads_dataset.csv   8,000 leads:  lead id, first contact date, lead source  data/olist_closed_deals_dataset.csv               842 deals:    lead id, sales rep id, date wonRun:  python3 analysis.py      (needs pandas)   ->  results.json, used by the page"""import json import pandas as pd # 1. Load the two files and join them on the lead idleads = pd.read_csv("data/olist_marketing_qualified_leads_dataset.csv")      # one row per leaddeals = pd.read_csv("data/olist_closed_deals_dataset.csv")                   # one row per won dealdf = leads.merge(deals[["mql_id", "sr_id", "won_date"]], on="mql_id", how="left")   # every lead kept; blank if not won # 2. Clean updf = df[df["first_contact_date"] >= "2018-01-01"].copy()     # deals are recorded from Dec 2017, so use leads from 2018 (Jan to May)df["origin"] = df["origin"].fillna("unknown")                 # a blank lead source counts as unknowndf["won"] = df["won_date"].notna()                            # True when the lead became a customerdf["days"] = (pd.to_datetime(df["won_date"]) - pd.to_datetime(df["first_contact_date"])).dt.days   # first contact to windf["month"] = df["first_contact_date"].str[:7]                # "2018-01" ... "2018-05"won = df[df["won"]]                                           # the customers only # 3. Funnel by lead source: leads, customers, win rateby_source = df.groupby("origin").agg(leads=("mql_id", "count"), won=("won", "sum"))by_source["rate"] = by_source["won"] / by_source["leads"]by_source = by_source.sort_values("leads", ascending=False) # 4. How fast: share of customers won within N days of first contactcurve = [round(float((won["days"] <= d).mean()), 4) for d in range(0, 181)] # 5. Reps: customers per sales rep (this file names a rep only on won deals)by_rep = won.groupby("sr_id").agg(won=("mql_id", "count"), median_days=("days", "median"))by_rep = by_rep.sort_values("won", ascending=False, kind="stable")   # stable: ties stay in rep-id order # 6. Forecast: a month's leads x the Jan-Mar win rate of each lead's sourcerate = df[df["month"] <= "2018-03"].groupby("origin")["won"].mean()      # Jan-Mar win rate per sourcemonths = []for m, g in df.groupby("month"):    f = round(float(g["origin"].map(rate).sum()), 1) if m >= "2018-04" else None   # forecast only after Jan-Mar    months.append({"month": m, "leads": len(g), "won": int(g["won"].sum()), "forecast": f}) # 7. May came in short. Which reps stopped closing? (last win before May 1)last_win = won.groupby("sr_id")["won_date"].max()stopped = last_win[last_win < "2018-05-01"].indexstopped_by_month = won[won["sr_id"].isin(stopped)].groupby("month").size().reindex(sorted(df["month"].unique()), fill_value=0) NAMES = {"organic_search": "Organic search", "paid_search": "Paid search", "social": "Social", "unknown": "Not recorded",         "direct_traffic": "Direct visit", "email": "Email", "referral": "Referral", "other": "Other",         "display": "Display ads", "other_publicities": "Other ads"}may = [x for x in months if x["month"] == "2018-05"][0]out = {    "data": {"leads_all": len(leads), "deals_all": len(deals), "first_lead": leads["first_contact_date"].min(),             "last_lead": leads["first_contact_date"].max(), "first_win": deals["won_date"].min()[:10],             "last_win": deals["won_date"].max()[:10]},    "kpi": {"leads": len(df), "won": int(df["won"].sum()), "rate": round(float(df["won"].mean()), 4),            "median_days": float(won["days"].median()), "reps": int(won["sr_id"].nunique())},    "sources": [{"source": NAMES[s], "leads": int(r.leads), "won": int(r.won), "rate": round(float(r.rate), 4)}                for s, r in by_source.iterrows()],    "curve": curve,    "reps": [{"rep": f"Rep {i + 1}", "won": int(r.won), "median_days": float(r.median_days)}             for i, (_, r) in enumerate(by_rep.iterrows())],    "top5_share": round(float(by_rep["won"].head(5).sum() / len(won)), 4),    "months": months,    "may_gap": round(may["forecast"] - may["won"], 1),    "stopped": {"reps": len(stopped), "by_month": {m: int(v) for m, v in stopped_by_month.items()},                "per_month_jan_apr": round(float(stopped_by_month[:"2018-04"].mean()), 2)},    "checks": {"deals_matched": int(deals["mql_id"].isin(leads["mql_id"]).sum()),               "duplicate_ids": int(leads["mql_id"].duplicated().sum() + deals["mql_id"].duplicated().sum()),               "won_before_contact": int((won["days"] < 0).sum()),               "blank_source": int(leads.loc[leads["first_contact_date"] >= "2018-01-01", "origin"].isna().sum())},}json.dump(out, open("results.json", "w"), indent=1) # Print the answersk = out["kpi"]print(f"{k['leads']:,} leads, {k['won']} customers ({k['rate']:.1%}), median {k['median_days']:.0f} days, {k['reps']} reps")print(by_source.round(3).to_string())print("won within 7/14/30/60/90/180 days:", [curve[d] for d in (7, 14, 30, 60, 90, 180)])print(f"top 5 reps closed {out['top5_share']:.0%} of customers")for x in months:    print(x)print(f"May gap {out['may_gap']}; {len(stopped)} reps stopped by April 30: {out['stopped']}")print(out["checks"])

"""Sales funnel case study: which leads become customers, and how fast?

Data (public): "Marketing Funnel by Olist" on Kaggle, CC BY-NC-SA 4.0. Olist is an online marketplace in Brazil;
each lead is a business that asked to sell on it, and a deal is one that signed up. Two files:
  data/olist_marketing_qualified_leads_dataset.csv   8,000 leads:  lead id, first contact date, lead source
  data/olist_closed_deals_dataset.csv               842 deals:    lead id, sales rep id, date won
Run:  python3 analysis.py      (needs pandas)   ->  results.json, used by the page
"""
import json

import pandas as pd

# 1. Load the two files and join them on the lead id
leads = pd.read_csv("data/olist_marketing_qualified_leads_dataset.csv")      # one row per lead
deals = pd.read_csv("data/olist_closed_deals_dataset.csv")                   # one row per won deal
df = leads.merge(deals[["mql_id", "sr_id", "won_date"]], on="mql_id", how="left")   # every lead kept; blank if not won

# 2. Clean up
df = df[df["first_contact_date"] >= "2018-01-01"].copy()     # deals are recorded from Dec 2017, so use leads from 2018 (Jan to May)
df["origin"] = df["origin"].fillna("unknown")                 # a blank lead source counts as unknown
df["won"] = df["won_date"].notna()                            # True when the lead became a customer
df["days"] = (pd.to_datetime(df["won_date"]) - pd.to_datetime(df["first_contact_date"])).dt.days   # first contact to win
df["month"] = df["first_contact_date"].str[:7]                # "2018-01" ... "2018-05"
won = df[df["won"]]                                           # the customers only

# 3. Funnel by lead source: leads, customers, win rate
by_source = df.groupby("origin").agg(leads=("mql_id", "count"), won=("won", "sum"))
by_source["rate"] = by_source["won"] / by_source["leads"]
by_source = by_source.sort_values("leads", ascending=False)

# 4. How fast: share of customers won within N days of first contact
curve = [round(float((won["days"] <= d).mean()), 4) for d in range(0, 181)]

# 5. Reps: customers per sales rep (this file names a rep only on won deals)
by_rep = won.groupby("sr_id").agg(won=("mql_id", "count"), median_days=("days", "median"))
by_rep = by_rep.sort_values("won", ascending=False, kind="stable")   # stable: ties stay in rep-id order

# 6. Forecast: a month's leads x the Jan-Mar win rate of each lead's source
rate = df[df["month"] <= "2018-03"].groupby("origin")["won"].mean()      # Jan-Mar win rate per source
months = []
for m, g in df.groupby("month"):
    f = round(float(g["origin"].map(rate).sum()), 1) if m >= "2018-04" else None   # forecast only after Jan-Mar
    months.append({"month": m, "leads": len(g), "won": int(g["won"].sum()), "forecast": f})

# 7. May came in short. Which reps stopped closing? (last win before May 1)
last_win = won.groupby("sr_id")["won_date"].max()
stopped = last_win[last_win < "2018-05-01"].index
stopped_by_month = won[won["sr_id"].isin(stopped)].groupby("month").size().reindex(sorted(df["month"].unique()), fill_value=0)

NAMES = {"organic_search": "Organic search", "paid_search": "Paid search", "social": "Social", "unknown": "Not recorded",
         "direct_traffic": "Direct visit", "email": "Email", "referral": "Referral", "other": "Other",
         "display": "Display ads", "other_publicities": "Other ads"}
may = [x for x in months if x["month"] == "2018-05"][0]
out = {
    "data": {"leads_all": len(leads), "deals_all": len(deals), "first_lead": leads["first_contact_date"].min(),
             "last_lead": leads["first_contact_date"].max(), "first_win": deals["won_date"].min()[:10],
             "last_win": deals["won_date"].max()[:10]},
    "kpi": {"leads": len(df), "won": int(df["won"].sum()), "rate": round(float(df["won"].mean()), 4),
            "median_days": float(won["days"].median()), "reps": int(won["sr_id"].nunique())},
    "sources": [{"source": NAMES[s], "leads": int(r.leads), "won": int(r.won), "rate": round(float(r.rate), 4)}
                for s, r in by_source.iterrows()],
    "curve": curve,
    "reps": [{"rep": f"Rep {i + 1}", "won": int(r.won), "median_days": float(r.median_days)}
             for i, (_, r) in enumerate(by_rep.iterrows())],
    "top5_share": round(float(by_rep["won"].head(5).sum() / len(won)), 4),
    "months": months,
    "may_gap": round(may["forecast"] - may["won"], 1),
    "stopped": {"reps": len(stopped), "by_month": {m: int(v) for m, v in stopped_by_month.items()},
                "per_month_jan_apr": round(float(stopped_by_month[:"2018-04"].mean()), 2)},
    "checks": {"deals_matched": int(deals["mql_id"].isin(leads["mql_id"]).sum()),
               "duplicate_ids": int(leads["mql_id"].duplicated().sum() + deals["mql_id"].duplicated().sum()),
               "won_before_contact": int((won["days"] < 0).sum()),
               "blank_source": int(leads.loc[leads["first_contact_date"] >= "2018-01-01", "origin"].isna().sum())},
}
json.dump(out, open("results.json", "w"), indent=1)

# Print the answers
k = out["kpi"]
print(f"{k['leads']:,} leads, {k['won']} customers ({k['rate']:.1%}), median {k['median_days']:.0f} days, {k['reps']} reps")
print(by_source.round(3).to_string())
print("won within 7/14/30/60/90/180 days:", [curve[d] for d in (7, 14, 30, 60, 90, 180)])
print(f"top 5 reps closed {out['top5_share']:.0%} of customers")
for x in months:
    print(x)
print(f"May gap {out['may_gap']}; {len(stopped)} reps stopped by April 30: {out['stopped']}")
print(out["checks"])