Northwind Telecom — churn on the next billing cycle
Northwind exports its subscriber book nightly. Retention wants a score for every
account in data/holdout.csv so they can call the top of the list first. The
export is not clean; nobody has ever cleaned it.
Time: 90 minutes. Write everything in solution.py. Every function is graded
on its own, so a task you skip costs only its own points.
Files
| File | Rows | What it is |
|---|---|---|
data/customers.csv |
~6 100 | Accounts whose outcome is known. Has churn. |
data/holdout.csv |
~7 100 | Accounts to score. No churn column. |
Data dictionary
| Column | Notes |
|---|---|
customer_id |
Account identifier. Not unique in the file — see below. |
tenure_months |
Whole months since signup. Some cells are empty. |
monthly_charges |
Current monthly bill, in soles. |
total_charges |
Billed to date. Exported as text; an account billed nothing yet is written as a blank. |
contract |
One of Month-to-month, One year, Two year. Casing and stray spaces vary by export batch. |
internet_service |
Fiber optic, DSL or None. None is a product — the account has no internet line — not a missing value. |
payment_method |
Four levels. |
support_tickets |
Tickets raised in the last 90 days. |
senior_citizen |
1 if over 65. |
region |
Five regions; one of them is rare. |
satisfaction_score |
Survey, 1–5. -1 means the account was never surveyed — it is not a score of minus one. |
data_partition |
Set by the export job. |
retention_offer_accepted |
1 if the account took a retention offer. Written by the retention team after the account has given notice. |
churn |
Target. 1 if the account left in the following cycle. |
Known defects, stated as rules
- Re-synced rows. When an account is edited the exporter re-appends it, so some
customer_idvalues occur twice. The rows are identical copies. total_chargesis text. Blank means "nothing billed yet", which is missing, not zero.satisfaction_scoreuses-1for "not surveyed".- Billing unit slip. A batch of rows recorded
monthly_chargesin céntimos instead of soles. Any value above 1000 is 100× too large; divide it by 100. contractcasing varies (two year,TWO YEAR,Month-to-month). Three products exist.internet_servicehas a level literally spelledNone. Roughly a fifth of the book buys voice only. That column has no missing values at all, and if yours does, the reader made them.
Tasks
T1 — load_data(path) -> pd.DataFrame (10 pts)
Read path. total_charges must come back numeric with blanks as NaN, internet_service
must keep all three of its levels (rule 6), and nothing may be dropped, de-duplicated or
filtered: the row count of the returned frame must equal the row count of the file.
T2 — audit_missing(df) -> dict[str, int] (15 pts)
Given a loaded frame, return {column: number_of_missing_values} for every column that
has at least one missing value, and no others. Rule 3 means -1 in satisfaction_score
counts as missing.
T3 — duplicate_ids(df) -> list[str] (10 pts)
Return the customer_id values that appear more than once. Each id once, order irrelevant.
T4 — clean(df) -> pd.DataFrame (15 pts)
Return a cleaned frame that:
- has exactly one row per customer_id;
- has exactly the three canonical contract levels;
- has the billing unit slip repaired (rule 4);
- has -1 in satisfaction_score replaced by missing;
- no longer carries data_partition.
Keep every other column. Keep churn if it was there.
T5 — engineer(df) -> pd.DataFrame (10 pts)
Given a cleaned frame, return it with two columns added:
- avg_charge_per_month = total_charges / tenure_months, and NaN where
tenure_months is 0 or missing — not infinity, not zero;
- is_new = true where tenure_months <= 1.
T6 — columns_to_drop(df) -> list[str] (10 pts)
Return the columns that must not be given to a model that will score holdout.csv.
Three columns qualify, for three different reasons: one identifies the row, one never
varies, and one is only known once the outcome has already happened.
T7 — fit_predict(train, holdout) -> np.ndarray (30 pts)
train and holdout are the raw frames, exactly as the two CSVs were read — every
defect above still present, holdout without churn. Return P(churn = 1) as a float
array, one value per row of holdout, in the same order. The scoring file is not
yours to de-duplicate.
Graded on ROC-AUC against the true outcomes: >= 0.770 clears a lazy baseline, >= 0.782
is competent, >= 0.792 is good. An AUC above 0.94 is treated as a defect, not a triumph
— it means something is in the feature set that would not exist when the model runs.
Allowed: pandas, numpy, scikit-learn.