The Analyst: Where and When Did This Start?

Ben has seven tables open and a mandate that fits on a sticky note. Customers, subscriptions, usage events, tickets, payments, surveys, campaigns. Somewhere inside the joins between them is the shape of a $1.7m problem, and for the past six weeks the company has been describing that shape from memory. Sales says the product is stale. Product says pricing broke it. Maya says three of her six labelled accounts are leaving for reasons nobody upstairs has mentioned once.

None of them are lying. They are each working from the handful of customers they personally spoke to, which is how every organisation reasons before somebody does the arithmetic. Ben’s week is the week the arguing stops — not because he is cleverer than the executives, but because he is the first person in this chain whose job is to count things rather than believe things. By Friday he will have four answers and one embarrassment, and one of those answers makes the rest of this course possible.

What lands on Ben’s desk

The first is Sofia’s requirements document from module 4, which already settled a question Ben would otherwise have had to settle himself: churn is counted by accounts, not revenue, measured monthly. It also named the four things worth watching — login frequency, core-feature usage, unused seats, failed payments. Ben did not choose those. He inherits them, and if a fifth signal turns out to matter, it is not in scope this quarter.

The second is Maya’s labelled sample from module 5: a small set of accounts where a human has written down what was really going on, along with her warning that a law firm going quiet in August is a holiday and not a crisis. Behind both sit Daniel’s $150k for AI and analytics and Omar’s insistence on a 12% minimum churn reduction, which is the number Ben’s findings will eventually be judged against.

What Ben adds is the part nobody upstream can do: he takes definitions written in prose and turns them into arithmetic that produces the same number every time it is run.

What a Data Analyst actually does

The job is equal parts precision, scepticism and translation. Precision, because a question like “how bad is churn” has at least six defensible answers and an analyst has to pick one and write down why. Scepticism, because the data is always dirtier than anyone admits and the first plausible number is usually wrong. Translation, because the output is not a query result — it is a sentence a Chief Information Officer can repeat in a board meeting without being embarrassed a month later.

Ben works mostly in SQL, the language for asking questions of tables, and keeps his metric definitions in a tool called dbt — which matters for an unglamorous reason: the definition of churn then lives in one place everything else reads from, rather than being retyped slightly differently into four dashboards.

The software on Ben’s desk

SQL is where most of this job happens. It is not really programming: it is a way of asking a precise question of a very large table — group these, count those, compare against that. The precision is the point, because the difference between two apparently identical questions is exactly where misleading numbers come from. It was a SQL query that noticed the customer table contained 412 active customers when finance counted 400.

Power BI then turns the finding into the picture that ends the argument — the ten-week slide before cancellation is far more persuasive drawn than described. Excel stays on the desk as the sanity check, because poking a suspicious number by hand is still the fastest way to catch a mistake before it is published. And dbt is where the definition of churn gets written down as tested code, so that when someone builds a different dashboard next quarter it counts the same thing. That last tool is the difference between an organisation that agrees on numbers and one that holds meetings about whose number is right.

Five questions, asked precisely

In every case there is a wrong version that runs perfectly and returns a believable number rather than an error message. That is exactly how real dashboards lie: not by breaking, but by quietly answering a slightly different question from the one that was asked.

How churn gets counted

The decision is what goes on the top of the fraction and what goes underneath. Ben could count cancelled accounts, or he could count the revenue attached to them; he could divide by the customers he had at the start of the month, at the end, or by the total on the books. Each combination is a defensible metric. Each produces a different figure.

He follows the PRD: cancelled accounts in the month, divided by accounts active at the start of it.

SELECT month, cancelled_accounts * 1.0 / active_accounts_start AS churn
FROM monthly_summary ORDER BY month;

Revenue churn is not wrong — it is a genuinely useful metric — but it is a different metric, and mixing the two mid-project is precisely how two teams end up in a meeting arguing about whose number is right instead of what to do. A metric is a contract about meaning; the arithmetic is the easy half.

What it reveals settles the first argument entirely. Churn sat at roughly 0.8% a month for years and then broke upward in March — the month the export bug shipped and the month prices rose. Two suspects, one timestamp, and no way yet to tell them apart.

Where it concentrates

Knowing when it started does not tell you who it is happening to. The instinct is to count cancellations by industry and read off the top of the list, and that instinct is a trap: the biggest segment tops every count list by construction. Manufacturing appears first because Northwind has more manufacturers, not because manufacturers are unhappy. Teams have been restructured around lists like that, sending help to the healthiest part of the business.

The fix is to divide every segment by its own base, so that a small segment with a terrible rate can finally show up. Counts tell you where your customers are; rates tell you where your problem is.

The picture that emerges is sharp. Churn concentrates in smaller accounts on the Standard plan, typically under fifty seats, and in accounts that raised a ticket about the export feature. Enterprise churn barely moved. That is not a company in decline; that is two specific wounds. Those same cuts — plan, size, whether an export ticket exists — become model features for Priya in module 8, because a segment that predicts churn in history is a candidate for predicting it in future.

What leavers did beforehand

This is the question the entire project is really asking, and it only works if time is measured the right way. If Ben lines usage up against the calendar, the signal vanishes: accounts cancel in different months, so one company’s decline in February sits on top of another’s recovery, and averaged together they flatten into a straight line. The signal is real and the analysis destroys it.

So he anchors time to the event instead of the calendar — weeks before each account’s own cancellation, not months of the year — and looks at the slope rather than the level.

SELECT weeks_before_cancel, AVG(weekly_logins)
FROM usage_by_week WHERE cancelled GROUP BY 1 ORDER BY 1 DESC;

What comes back is the most important fact in this course. Leavers’ logins start sliding about ten weeks before they cancel. Not two days. Ten weeks.

Say it plainly: that window is what makes the product possible. Without it there is nothing to build, because a warning that arrives the morning someone cancels is a post-mortem, not a save. With it, there is time to phone a customer, escalate a bug and renegotiate a contract. Every downstream decision now has a budget of ten weeks to spend, and the first thing it buys is simplicity — Aisha’s pipeline in module 7 and Kofi’s system in module 10 can run once a day rather than in real time, because when the signal takes ten weeks to develop, refreshing it every few seconds buys nothing and costs a great deal.

Cohorts

A single churn number blends two problems that happen to produce the same headline: existing customers drifting away, and new customers never landing. They have different causes, different owners and different fixes, and whichever team gets blamed for the blended number will confidently fix the wrong one.

Grouping customers by the quarter they joined and following each group across its own life pulls them apart. What Ben finds is that cohorts from the last twelve months retain noticeably worse at month three than older ones did at the same age — a signature of onboarding getting weaker as the company grew and the process got stretched.

So Northwind does not have one problem wearing one headline. It has two. Aldgate Media never finished onboarding and was gone before it ever properly arrived. Corvex Manufacturing has been a heavy user for years and is leaving over a broken export feature. No model fixes the first of those; it is a process problem, and saying so is part of the job.

Data quality

Ben’s row count says 412 active customers. Finance says 400. Reconciling against a number somebody else owns is a habit worth stealing, because it is how the two classic faults surface.

The first is dull: Juniper Health appears twice, an exact duplicate from a bad CRM import. The second is the dangerous one. Corvex exists under two customer IDs, created either side of a rebrand, so its 23 export tickets are split across two records — and one furious large account now looks like two mildly annoyed mid-sized ones.

Consider what a model would learn from that. It would learn that accounts with a dozen tickets sometimes stay, because half of Corvex’s history is attached to a company that does not exist — the wrong threshold for anger, applied to every customer, at scale, without doubt. Duplicates do not announce themselves. They just quietly make the answer plausible and wrong.

Where this goes wrong

The classic failure here is not a bad query. It is three tools each holding their own definition of churn — one counting accounts, one counting revenue, one counting logos including trials — and nobody noticing for months. The symptom is unmistakable: recurring meetings in which senior people argue about whose number is right while the actual decision goes unmade for another fortnight.

The second failure is subtler and costs more. An analyst finds something genuinely important, presents it as a chart, and the room nods and forgets it. The finding that survives is the one compressed into a sentence a person can repeat: not “here is the retention curve by weeks-to-cancellation” but “leavers go quiet ten weeks before they leave.” Ben’s most valuable output this week is fourteen words long.

What Ben hands on

What leaves his desk is a findings pack, a metric dictionary that pins down every definition he used, and one deduplicated customer table. It goes to two people at once, with two different messages attached.

To Priya, the Data Scientist in module 8: the ten-week window is your opening, and these segment cuts are your starting features. To Aisha, the Data Engineer in module 7, something less comfortable — the analysis works, once. Ben merged Corvex by hand and deleted the Juniper duplicate by hand, and that cannot happen every week for the next three years. The rules he applied with judgement now have to become code that runs unattended at two in the morning, which is exactly the problem Aisha inherits and exactly why her module exists.

Spot the flawed question

Read each situation and decide what the analyst should say, then tap a card to flip it.