Interview guides · Kepler Group

Kepler Group Data Scientist Interview Questions & Guide 2026

Case practice for Kepler Group data scientist candidates: scope a vague prompt into a measurable question before you analyze it.

The 60-second brief

As a Data Scientist at Kepler Group, you play a pivotal role in harnessing the power of data to drive business decisions and strategy. Your expertise in statistical analysis, machine learning, and data visualization will directly influence the effectiveness of client campaigns and the overall success of the organization. You will be challenged with complex data sets, requiring you to extract meaningful insights that can lead to actionable recommendations.

When randomisation is off the table, the skill being checked is naming an identification strategy together with the assumption it rests on: parallel trends for difference-in-differences, relevance and exclusion for an instrument, overlap and conditional ignorability for matching. Say the assumption out loud and say how you would try to break it.

Focus areas

Measure catalogue breadth beyond head consumption
Diagnose rebuffering by device, network and POP
Separate release-calendar spikes from underlying trend

Overview

In this role, you'll work closely with cross-functional teams, including marketing, product, and engineering, to develop models that improve customer engagement and optimize marketing strategies. Your contributions will not only enhance the precision of analytics but also help shape the strategic direction of Kepler Group's offerings. Expect to engage with cutting-edge tools and technologies while addressing real-world problems in a fast-paced, dynamic environment.

This role is critical because it combines technical prowess with strategic insight. You will be responsible for transforming raw data into compelling narratives, impacting how clients understand and interact with their markets. The complexity and scale of the projects you will tackle make this position both challenging and rewarding, offering significant opportunities for innovation and professional growth.

The Kepler Group interview process

Kepler Group candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

HR Interview

An extra round usually exists because something is still open after the standard loop: a skill the earlier interviews did not sample, a level decision, or two interviewers who disagreed. It is rarely a rerun of what you already did well. Ask the recruiter who you are meeting, what function they sit in, and how long the session runs. That is an ordinary scheduling question, and the answer changes what you should prepare. What separates a strong candidate here is treating the round as a fresh evaluation with its own bar, rather than assuming earlier performance carries you through or sinks you.

What to demonstrate

Whether you can answer well on ground the earlier rounds did not cover, without leaning on what you already said to someone else
Consistency of the facts in your stories: the same sample size, timeframe, team size and scope of your own role as in earlier conversations
How you handle an unfamiliar format live, including whether you ask what kind of answer is wanted before producing one

How to prepare

Ask the recruiter for the interviewer's function, the length, and whether to expect a coding surface, a discussion, or a presentation. Preparing for a 30 minute conversation with a partner team is not the same work as preparing for a 60 minute technical block.
Write out what each earlier round actually covered, then list the two or three areas nobody probed. That gap is the most likely subject of the extra round.
Re-read the numbers in the project stories you have already told, so a second telling does not quietly contradict the first.

Technical Assessment

A handful of shapes account for most of what gets asked in this format: a ranking or deduplication inside groups, a running or rolling total, a period-over-period comparison, and a cohort tracked forward over time. Recognising the shape quickly is most of the speed here; deriving it from scratch while a clock runs is where the time goes. Know that a window function keeps every row while a GROUP BY collapses them, and know which one the question needs. If the exercise is in Python instead of SQL, the same shapes arrive as groupby with transform, shift and merge, and the same grain mistakes are available.

What to demonstrate

Whether you reach the right construct without a detour, such as ROW_NUMBER over a partition to deduplicate instead of a self-join against a MAX subquery
Whether you know what your window frame actually is, since adding ORDER BY inside OVER changes the default frame and silently changes a running total
Whether the thing runs. A near-miss that throws an error scores below a plainer query that returns the right rows.

How to prepare

Write each of the four shapes once from memory against a small schema and keep the working version somewhere you will reread it: dedupe with ROW_NUMBER, a running total, a month-over-month change with LAG, and a retention table
Compute one running total twice on data with tied timestamps, once on the default frame and once with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and look at where the two disagree
If Python is on the table, rebuild the dedupe and the running total with groupby and cumsum, then assert the two implementations return identical rows

Team Interviews

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Where candidates lose points

Counting plays without a qualification threshold, or changing the threshold without restating history.

Playback arrives as heartbeats, so a play only exists once you decide what counts, and the common 30-second convention is not a neutral analytics choice: in music it is also the boundary at which a play becomes payable, which makes the warehouse definition a payout definition. The threshold interacts violently with content length, so a catalogue of three-minute tracks and one of forty-minute episodes move in opposite directions when you change it, and a skip-heavy surface can add plays while adding no hours. Any metric mixing pre-threshold and post-threshold counts, or pooling short-form and long-form on a per-stream basis, moves by double digits for reasons that have nothing to do with the product.

Comparing consumption week over week across the release calendar and the rights calendar.

A major release, a season drop or a live event produces a spike that dwarfs almost any treatment effect, and the effect is not confined to the new title because it pulls attention from everything else in the same window. Separately, licensed content leaves the catalogue when its window expires, so consumption falls with no product change and the drop is attributed to whatever shipped that week. Both need to be handled by an explicit control: a comparison period chosen for calendar equivalence, a covariate for scheduled releases, or a pre-registered rule for excluding a window, decided before the numbers are seen.

Answering a product-sense question with a list of features

Answer with a decision and the measurement that would settle it: the hypothesis, the primary metric, the guardrails, and the result that would make you not ship. A feature brainstorm cannot be wrong, which is exactly why it earns no points.

Optimising accuracy on a heavily imbalanced target

State the base rate first, then choose the metric from the relative cost of a false positive against a false negative: precision and recall at the operating threshold, PR-AUC, or expected cost. At a 1 percent positive rate, predicting the majority class for everyone scores 99 percent accuracy and is worthless.

Questions & practice

What factors would you consider when deciding whether to scale a model in production?

Medium · machine learning and modelling

Approach

Check what information would not exist at prediction time, and exclude it.
Say how the offline result would be validated online before it is trusted.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

Where could label leakage enter this setup?
How would you choose the decision threshold, and who owns that choice?

Sessionise playback heartbeats into streams under an idle-gap rule

Hard · sessionisation · gaps-and-islands · pandas

heartbeats has profile_id, device_id, content_version_id, heartbeat_ts, playhead_seconds, is_paused and reported_at; clients emit one every 30 seconds while in the foreground. Collapse it into stream rows keyed on (profile_id, device_id, content_version_id) under a 300-second idle gap. Emit started_at, ended_at, max_position_seconds and played_seconds, where played_seconds counts advancing playback only and so excludes pause, seek and rebuffer time. Flag streams whose reported_at trails heartbeat_ts by more than an hour. Then re-run at a 60-second gap and report what moved.

Approach

Sort by the key and heartbeat_ts, take the per-row time delta inside the key with groupby().diff(), mark a new stream where that delta is null or above 300 seconds, and cumulative-sum the flag into a stream ordinal. This is gaps-and-islands, and it is O(n log n) after the sort with no per-group Python loop.
Compute played_seconds from consecutive heartbeats as min(max(playhead_delta, 0), wall_delta), summed inside the stream. The lower clamp discards a backward seek; the upper clamp is what stops a forward seek to the end of a forty-minute episode from crediting forty minutes of playback to one thirty-second interval. Note what this makes the column mean: a rewind and replay inside one stream counts the replayed interval twice, because played_seconds measures time spent advancing the playhead, not distinct coverage of the item.
Accept that the first heartbeat of each stream has no predecessor and contributes nothing, which undercounts by up to one heartbeat interval per stream. Quantify that bias as stream count times 30 seconds against the total instead of leaving it unsaid.
Take ended_at as the last heartbeat_ts, but keep it null when that heartbeat sits within one idle gap of the data cutoff: such a stream may still be running, and giving it an ended_at manufactures a completed stream.
For the offline flag, work on reported_at minus heartbeat_ts rather than on event_date, and report the distinct dates the flagged streams belong to, because that list is the recompute list.
Re-run at 60 seconds and report both the stream count and the change in total played_seconds: the count can only rise, and the total falls by exactly the contributions that now straddle a split.

Follow-up

A client dies mid-playback and stops reporting. What does its stream look like under this rule, and what does that do to mean stream duration?
Which of these numbers determines a per-stream payout, and what does moving the idle gap do to somebody's money?
The same profile plays the same track on a phone and a TV at once. Your key separates them. When would you want it not to?

Attribute streams to prior impressions without merge_asof

Medium · pandas · joins · attribution

You have impressions (impression_id, profile_id, content_version_id, rendered_at, surface, slate_position, viewport_visible_ms) and streams (stream_id, profile_id, content_version_id, started_at). Attribute each stream to the most recent impression of the same profile and content version with rendered_at at or before started_at, a gap of at most 30 minutes, and viewport_visible_ms above zero; break ties on rendered_at by the smaller slate_position. pd.merge_asof and any groupby-apply over individual rows are off limits. Return streams with impression_id added, plus the attributed share by surface.

Approach

Filter impressions to viewport_visible_ms above zero first. A row that never scrolled into view is not an exposure, and leaving it in lets an unseen impression win the as-of match over a seen one.
Stack both frames into one long frame with a ts column and an is_stream flag, then sort by (profile_id, content_version_id, ts, is_stream, negative slate_position) so that at an identical timestamp impressions sort before the stream, and among tied impressions the smallest slate_position sorts last.
Forward-fill the candidate impression_id and rendered_at with groupby(['profile_id','content_version_id']).ffill(). The grouping is what stops the fill leaking across profiles; a global ffill on a sorted frame is the usual wrong answer.
Apply the 30-minute window as a post-filter on the filled candidate, and let a stream that fails it go unattributed rather than falling back to an older row. Every candidate precedes the stream, so the gap grows monotonically as you walk backwards: if the nearest in-view impression is more than 1800 seconds old, no earlier one is inside the window either, and null is the correct answer. The window cannot be pushed onto the impressions frame beforehand in any case, because "within 30 minutes" is defined against a particular stream's started_at and one impression is a candidate for many streams. Dropping impressions rendered more than 30 minutes before the earliest stream is a safe prune, but it is an optimisation, not the rule.
Slice the stream rows back out and compute the attributed share by surface, stating that unattributed streams (resume, direct link, radio seed) are not a surface and belong in their own bucket.

Worked solution (30 min)

imp = impressions[impressions.viewport_visible_ms > 0]; build an impression side with ts = rendered_at, is_stream = 0 and a sort helper neg_pos = -slate_position, and a stream side with ts = started_at, is_stream = 1, neg_pos = 0.
Concatenate both, then sort_values(['profile_id','content_version_id','ts','is_stream','neg_pos']).
Add cand_id and cand_ts columns carrying impression_id and rendered_at on impression rows and NaN on stream rows, then groupby(['profile_id','content_version_id'])[['cand_id','cand_ts']].ffill().
Slice to is_stream == 1, compute gap = (ts - cand_ts) in seconds, and set cand_id to NA where the gap is null or above 1800.
Merge cand_id back onto streams by stream_id, then group the attributed rows by the winning impression's surface for the share.

Expected result: streams with an impression_id column, row count unchanged, non-null only where an in-view impression of the same profile and content version exists within 1800 seconds before started_at, plus a per-surface share over a stated denominator of attributed streams rather than all streams.

Verify it yourself

Every attributed pair satisfies profile_id equality, content_version_id equality, and a gap between 0 and 1800 seconds. Assert all three rather than eyeballing the head.
On a 10,000-row sample the result matches pd.merge_asof(by=['profile_id','content_version_id'], tolerance=30 minutes, direction='backward', allow_exact_matches=True) once slate_position ties are made unique.
The output row count equals len(streams) exactly; a frame that grew matched several impressions to one stream.
Setting viewport_visible_ms to 0 on the winning impression of a known stream moves that stream to an earlier impression or to null, never leaves it unchanged.

Follow-up

The same item was rendered in two slates inside the window. Your tie-break picks one. What does that do to per-surface credit, and what would a fractional rule change?
How would you validate this against the impression_id already carried on fct_stream, and what would a systematic disagreement tell you?
At 2 million impressions and 500 thousand streams this fits in memory. What changes at 200 million?

Can you explain a time complexity of O(n log n)?

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Say which table is the grain you start from, and join outward from it.
Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

What breaks if events arrive late or out of order?
How does the query change if the join becomes one-to-many?

How would you optimize a SQL query for better performance?

Medium · sql and data manipulation

Approach

Compute rates by summing numerator and denominator separately, never by averaging rates.
State the window function and its partition and ordering out loud before writing it.
Say which table is the grain you start from, and join outward from it.

Follow-up

How would you verify this result without re-running the same query?
How does the query change if the join becomes one-to-many?

Daily qualified hours on local dates with trailing seven-day sums

Hard · timezones · window functions · running aggregates · late-arriving data

fct_stream has account_id, started_at (UTC), event_date (date partition key derived from started_at), played_seconds, is_qualified and play_territory (ISO-3166-1 alpha-2, resolved at play time). A lookup tz_offset(territory, utc_offset_minutes) gives each territory a fixed offset. Product wants daily qualified hours for March 2026 on each territory's local calendar date, not UTC, plus a trailing seven-day sum per territory. Offline plays upload days late with started_at backdated. Return territory, local_date, qualified_hours and trailing_7d, and state which of event_date and started_at you filter on and why.

Approach

Derive local_date = (started_at + utc_offset_minutes * INTERVAL '1 minute')::date, joining the lookup on play_territory rather than the account's signup_country. A play belongs to the clock where it happened; signup_country is a property of the account's origin and is wrong for anyone who travels or who signed up under a bundle sold in another market. Say out loud which one the requester meant.
Scan wide, then narrow. To cover local March everywhere you must read UTC 2026-02-28 through 2026-04-01, because a territory at UTC+13 begins its 1 March at 11:00 UTC on 28 February, and one at UTC-11 is still in 31 March at 10:00 UTC on 1 April. Put the widened range on event_date so the partition prune still happens, then filter local_date to March in the outer query.
State the precondition on the offset. A single fixed offset per territory is only correct while no daylight-saving transition falls in the window, and several territories shift during March. Either resolve with a real zone (started_at AT TIME ZONE an IANA name) or accept a bounded error: at most one hour of plays per affected territory moves across one date boundary, on one day of the month.
Aggregate to (territory, local_date), then take the trailing window. Prefer SUM(...) OVER (PARTITION BY territory ORDER BY local_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW), which is calendar-correct even when a territory has a day with no qualified streams. ROWS BETWEEN 6 PRECEDING is correct only on a dense date spine — on a gapped series it silently reaches back more than seven calendar days.
Say what the number means for the recent edge. event_date follows the backdated started_at, so a late offline upload lands in a day you already published and that day's figure grows after the fact. Either restate closed days on every run and publish a revision, or carry an ingestion-time column and report the figure as-of a stated load.

Worked solution (45 min)

Build a dense (territory, local_date) spine covering 2026-02-23 through 2026-03-31, so every March row has six full preceding days available to its window.
Compute local_date over a UTC scan widened to event_date BETWEEN '2026-02-28' AND '2026-04-01' (widened further for the spine's February tail) and aggregate SUM(played_seconds)/3600 where is_qualified.
LEFT JOIN the aggregate onto the spine, COALESCE hours to 0, apply the RANGE window, then restrict output to local_date in March.
Re-run the identical query a week later and diff the early-March days to measure how much the late offline uploads moved a closed number.

Expected result: 31 rows per territory covering local_date 2026-03-01 through 2026-03-31, with qualified_hours >= 0 and trailing_7d equal to the sum of that row and the six preceding spine rows — full windows throughout, because the spine starts in February.

Verify it yourself

For a territory at UTC+00 the local-date total and the UTC event_date total for March are identical; for every other territory they differ by exactly the boundary hours, which is the evidence the conversion did something.
trailing_7d on any row equals the manual sum of qualified_hours over that row and the six preceding spine rows, including rows that are zero.
Dropping the spine's February tail changes trailing_7d for the first six March days and nothing else, confirming the frame is reaching back seven calendar days rather than seven present rows.

Follow-up

A territory changes its UTC offset mid-month. What happens to your March total, and what do you print for the local day that is 23 or 25 hours long?
Would you key on play_territory or the account's signup_country? Give a concrete case where they differ and the choice changes the answer.
What do you publish for the most recent day while offline uploads are still arriving, and how do you communicate that it will move?

Given a dataset about customer behavior, how would you approach predicting future purchases?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
Which segment would you cut first, and what would that rule out?

How do you prioritize tasks when managing multiple projects?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
Which segment would you cut first, and what would that rule out?

If you found a significant drop in conversion rates, how would you investigate the issue?

Medium · product sense and metrics

Approach

Decompose the metric into the rates that drive it, and say which one you would check first.
Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.

Follow-up

Which segment would you cut first, and what would that rule out?
How would you detect that the metric is being gamed rather than genuinely improving?

Outline the steps you would take to analyze a new product's market fit.

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.
State what result would change your recommendation, so the answer is falsifiable.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
What would you do if the primary metric and the guardrail moved in opposite directions?

Describe how you would design an A/B test to evaluate the effectiveness of a marketing campaign.

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Say whether units interfere with each other, and switch design if they do.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

What would you do if you could not randomise at all?
How would you handle interference between treated and control units?

Explain the difference between supervised and unsupervised learning.

Medium

Approach

State your assumptions explicitly before working the problem.
Work from the decision backwards to the evidence you would need.
Clarify what is being asked and what a complete answer would contain.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Trade retention against revenue on an ad-supported tier

Medium · metric design · guardrail conflict · advertising · lagged effects

The free_ad_supported tier proposes raising ad breaks per delivered hour. The company metric is net revenue per active account-month; the stated guardrails are advertising seconds per delivered content hour and trailing 90-day retention of ad-supported accounts. Using fct_stream (account_id, ad_breaks_served, ad_seconds_played, played_seconds, is_qualified) and fct_subscription_period (account_id, net_amount_usd, period_end_ts, renewal_outcome), design the decision: primary metric, guardrails, and an explicit exchange rate saying how much retention you would spend for a revenue point. A four-week test is all you are offered.

Approach

Say plainly that this is a real conflict, not a monitoring exercise: raising ad load moves the primary metric up and the guardrail down through the same mechanism, so no definition of either metric makes both improve and the job is to price the trade, not to find a metric that hides it.
Decompose the primary so the conflict is visible in the algebra: net revenue per active account-month equals ad seconds per delivered content hour times delivered content hours per active account-month times net revenue per ad second, and the change raises the first term while lowering the second, with the third term falling too if added inventory clears at a lower price.
Establish that a four-week test cannot read the guardrail: the retention response is lagged past the window, so the test is powered on the numerator and will return a significant revenue gain against a retention estimate whose confidence interval spans the effect sizes that would kill the launch. State the required holdout instead, held at least 90 days at a size chosen to detect the retention drop that would break even.
Convert both sides into one currency before arguing: expected lifetime value per ad-supported account equals net revenue per active account-month times expected retained months, so a 1 percent revenue gain is worth taking only if the retention cost is under roughly 1 percent of expected retained months, and write that break-even as the exchange rate.
Keep one guardrail non-tradeable: advertising seconds per delivered content hour gets a hard ceiling that no revenue gain buys past, because an exchange rate alone permits an unbounded walk up the ad-load curve one defensible step at a time.
If the four weeks must produce a decision, name the surrogate explicitly and its bias: weeks three and four qualified hours per account-week among treated accounts, which understates the retention harm because the accounts most likely to leave are still present and still consuming during the window.

Worked solution (35 min)

Write the decomposition of net revenue per active account-month into ad seconds per content hour, content hours per account-month and revenue per ad second, and mark the sign of the ad-load change on each term.
Write the lifetime-value identity for an ad-supported account and differentiate it into the break-even condition: the launch is positive only when the proportional revenue gain exceeds the proportional loss in expected retained months.
Plug in one concrete pair: a 3 percent revenue-per-account-month gain breaks even against roughly a 3 percent fall in expected retained months, which on a 14-month expected tenure is about 0.4 months, and translate that into the 90-day retention drop it corresponds to.
Specify the measurement plan: four-week test reads the revenue term and the ad-seconds guardrail only; a separately sized holdout of ad-supported accounts is held 90 days and is the only thing allowed to move the retention number.
Write the ship rule with the hard ceiling first, then the exchange rate, then the holdout condition, and state which of the three is not negotiable.

Expected result: Primary: net revenue per active account-month. Guardrails: advertising seconds per delivered content hour with a hard non-tradeable ceiling, and trailing 90-day retention of ad-supported accounts read from a dedicated holdout, not the four-week test. Exchange rate stated as a break-even: the proportional revenue gain must exceed the proportional loss in expected retained months, with the worked figure around 3 percent against roughly 0.4 months on a 14-month tenure. The candidate must say the conflict is genuine.

Verify it yourself

The decomposition has ad load raising one term and lowering at least one other, which is the formal statement that the conflict cannot be defined away.
The break-even is expressed as a comparison of two proportional changes, so it does not depend on getting the absolute lifetime value right.
The four-week test is explicitly not permitted to clear the retention guardrail, and the reason given is power and lag rather than a preference.
The ad-seconds ceiling is stated as a bound rather than a term in the exchange rate, which is what stops an incremental walk up the curve.

Follow-up

Net revenue per ad second falls as load rises because added inventory clears cheaper. How does that change your break-even, and does it make the decision easier or harder?
How large must the 90-day holdout be to detect a one-percentage-point retention drop, and what does that cost in forgone revenue while it runs?
The four-week result is a 3 percent revenue gain with a retention estimate of minus 0.4 points plus or minus 1.5. What do you recommend, in one sentence, and what do you refuse to claim?

Involuntary churn share jumped after a dashboard rebuild

Medium · definition-change · churn · late-resolving-data

Involuntary share of gross churn — fct_subscription_period rows with renewal_outcome = 'failed_involuntary' over rows with renewal_outcome in ('cancelled_voluntary','failed_involuntary'), keyed on period_end_ts month — reads 31% for last month against 19% for the three months before. Billing reports no change in decline rates and dunning_attempts per failed period is unchanged. Using fct_subscription_period (period_end_ts, renewal_outcome, payment_status, dunning_attempts, cancel_requested_ts, billing_provider), determine whether involuntary churn actually rose. Deliverable: the corrected month-on-month series and the specific change that produced the step.

Approach

Check the month's maturity before anything else. Periods ending recently still have retries in flight, and a row reading 'failed_involuntary' today becomes payment_status = 'retried_paid' next week. Recompute every one of the last twelve months as of a fixed age — period_end_ts plus the maximum retry window — instead of as of today.
If the step survives age alignment, hunt for a definition change: diff the model or query against the version that produced 19%, and check specifically whether the month is now keyed on cancel_requested_ts rather than period_end_ts, and whether 'refunded' or 'chargeback' rows have entered one side of the ratio.
Settle it with one query rather than a narrative. Run the old definition and the new definition on the same month. Two numbers from one month under two definitions is decisive; two numbers from two months under one definition is not.
If a definition did change, restate history rather than leaving a step in the chart: run the new definition back across all twelve months and publish both series with the cutover marked, so nobody re-discovers this in a quarter.
Cut the horizon-closed series by billing_provider before concluding nothing moved. Card-direct, app-store and carrier billing have different retry mechanics and different success rates, so a mix shift between them moves the ratio with every provider's own rate flat.

Follow-up

How would you set the dunning horizon from the data instead of taking billing's stated maximum?
Under cancel-at-period-end, the cancellation request and the entitlement end fall in different months. Which timestamp keys this metric, and what does the other choice do to the curve?

Suggested practice checklist

For a candidate whose interviews will centre on A/B testing, metric movement and causal claims. Design comes before arithmetic, arithmetic before analysis, and the week ends by rehearsing the readout rather than the derivation.

Day 1 — Design one test end to end on paper

Take a single feature change and write the full design: randomization unit, the exact point of exposure, the primary metric with its grain, guardrails, allocation, planned duration, and the decision rule committed before any data exists.
Write why the randomization unit must sit at or above the level where treatment can spill over, and give one case where user-level randomization is still contaminated (shared accounts or devices, or two participants in the same marketplace).
State in advance what you will do if the primary metric is flat while a secondary metric is significant.

Deliverable: A one-page test design with a decision rule written before launch.

Day 2 — Power arithmetic until it is automatic

Compute required sample size per arm for a binary metric with the normal approximation, n is approximately 2 times (z for alpha/2 plus z for power) squared times p(1 minus p) divided by delta squared, for baselines of 2, 10 and 40 percent at a 5 percent relative lift, and note that for a fixed relative lift the requirement falls as the baseline rises because delta grows proportionally with p.
Redo the calculation for a continuous metric using variance in place of p(1 minus p), and show why a heavy-tailed quantity such as revenue per user needs either far more traffic or a capped version with a stated cap.
Convert one of the results into weeks given a weekly eligible traffic figure, then list the two honest ways to shorten it (accept a larger detectable effect, or reduce variance) and write why quietly lowering the power target is a decision to miss more real wins, not a speedup.

Deliverable: A small script or sheet that maps baseline, minimum detectable effect, alpha and power to sample size and weeks, cross-checked against a published calculator.

Day 3 — Variance and the unit-of-analysis problem

Take a ratio metric whose denominator is not the randomization unit (clicks per session, randomized by user) and compute the standard error twice, once naively at session level and once by the delta method or a user-level bootstrap, then record how much the naive version understates it.
Implement CUPED on simulated data: choose a pre-period covariate X measured before assignment, estimate theta as Cov(Y, X) divided by Var(X), and analyse Y minus theta times (X minus its mean) in place of Y. Confirm the variance of the adjusted outcome equals the raw variance multiplied by one minus the squared correlation between Y and X, so a correlation of 0.45 removes about 20 percent of the variance and not 80.
Now run that simulation a few hundred times and confirm the adjusted effect estimate is unbiased for the same effect rather than numerically identical to the raw one. Within any single run the two differ, sometimes by a large fraction of the true effect, because the two arms' pre-period covariate means never coincide exactly in a finite sample; they agree in expectation, which is the property that matters and the one to state out loud.

Deliverable: A notebook showing the adjusted estimator with a measurably smaller variance than the raw one, plus a repeated-simulation table showing the two estimators agreeing on average while differing run by run.

Day 4 — Validity threats you can actually test for

Run a sample ratio mismatch check as a chi-square goodness-of-fit test against the intended allocation, and write the three causes you would chase first (assignment logged before exposure, an arm-specific redirect or load failure, bot filtering applied asymmetrically).
Simulate peeking: generate A/A data, test daily at alpha 0.05 across 14 looks, record the inflated false positive rate, then apply an alpha-spending boundary or commit to a fixed horizon and confirm the rate returns to nominal.
Write how you would separate a novelty effect from a durable lift using the treatment effect plotted against days since first exposure, and what shape would change your recommendation.

Deliverable: One table showing the peeking false positive rate before and after correction, plus a written SRM triage list.

Day 5 — When randomization is not available

Write the identifying assumption for difference-in-differences (parallel trends in the absence of treatment), then plot pre-period trends for two candidate control groups and justify rejecting one of them.
Design a switchback test for a change where user-level randomization would leak across participants, choosing a time-block length against the carryover you expect and saying how you would detect carryover in the data.
List what an interrupted time series or a synthetic control buys you and the one thing neither can rule out: an unobserved shock that coincides with the launch.

Deliverable: A one-page memo recommending a single quasi-experimental design and naming its weakest assumption explicitly.

Day 6 — The readout query

Write the assignment-to-exposure join that returns exactly one row per unit per experiment, and handle units appearing in both arms by excluding and counting them rather than silently keeping one.
Compute the per-arm metric, its variance and the relative lift with a confidence interval in SQL, then reproduce the identical numbers in a notebook as a cross-check.
Add a segment breakdown and write the sentence that keeps it from being p-hacking: segments declared in advance, everything else reported as exploratory and corrected for multiplicity.

Deliverable: A single query that outputs the full readout table, matched to a notebook recomputation.

Day 7 — Present it to someone who will not read the appendix

Give a 10-minute readout of a real or simulated experiment in the order decision, number, uncertainty, caveat.
Have your listener ask "can we ship it" in the case where the primary is flat and a guardrail moved, and answer with a recommendation rather than a request for more data.
Rewrite your opening line so the recommendation lands before any methodology.

Deliverable: A one-page readout whose first line is the recommendation.

Where your data came from

Data people depend on systems owned by other teams, and much of the job is negotiating for instrumentation, access, or a fix to a broken pipeline. Prepare an example of getting something changed upstream that you did not control. Describe what you asked for, what you traded, and how you worked while you waited.

How do you handle missing data in a dataset?

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
State the situation in two sentences and spend the rest on your reasoning.
Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

What did you decide not to do, and why?
How did you know the outcome was caused by your change?

Walk through an analysis you shipped that was wrong

Medium · error ownership · data quality · postmortem

Describe a case where you delivered a result that was later shown to be wrong, and it had already been acted on. Cover how the error surfaced, whether you or someone else found it, what the wrong number caused, and what you changed afterwards. Prepare an example whose root cause was a definition, a denominator or a join — not a transcription slip. The interviewer will push on the mechanism, not the apology. Deliverable: a four-minute account that ends with a specific control now running in a pipeline.

Approach

The probe is whether your account has a mechanism in it. Choose an error that generalises — a denominator that silently changed population, a join that fanned rows, a metric partitioned on event_date while offline playback arrived days late and landed in the wrong partition — rather than one that only teaches you to check your typing.
State the blast radius factually and early: which decision was taken, how long the number stood, what it cost. A candidate who softens this is answering a different and easier question, and the interviewer can hear the substitution.
Explain how it surfaced without adjusting who found it. The generalisable detail is why your own checks did not catch it, which is a statement about your checks rather than about your luck.
Name the control you added and where it now lives: a row-count assertion after the fan-out join, a reconciliation that recomputes a closed day after late-arriving offline playback and alerts above a threshold, a denominator assertion inside the query. A fix that lives in a pipeline is different in kind from a resolution to be more careful.
Close with whether the control has fired since, or how you tested that it would. That single sentence is what separates a fix from an intention, and interviewers ask for it when candidates do not offer it.

Follow-up

Why didn't your own review catch it? Be specific about what you did check.
What class of error would that control still not catch, and what would you add next?
Have you found an error in someone else's published analysis since? How did you raise it?

Give an executive a number you are not sure of

Medium · communicating uncertainty · executive communication · retention

An executive needs a figure for a board deck by end of day: the effect on month-6 paid retention of a dunning-schedule change that has been running nine weeks. Your estimate is +1.2 points with a 95 percent interval of [-0.3, +2.7], and the month-6 cohort has not matured — you are projecting from month-2 behaviour. They say "one number, no ranges, it's a board deck." Deliverable: the figure you supply, the single sentence that sits under it, and what you do if the sentence is deleted.

Approach

The probe is whether you can be useful under a constraint you disagree with. Supply the number. A refusal gets replaced within the hour by someone else's number carrying no caveat at all, which is strictly worse than yours carrying a short one.
Separate the two uncertainties, because they behave differently and only one shrinks with patience. Sampling error is the interval and narrows as weeks accumulate. Extrapolation error from month-2 to month-6 does not narrow at all and is bounded only by evidence.
Bound the extrapolation empirically before the deck goes out: on the last several fully matured cohorts, check how closely month-2 retention predicted month-6, and report that spread. This converts "we are projecting" from a hedge into a number, which is the only form of caveat that survives a slide.
Translate the interval into the decision rather than the statistic. The executive needs the range only where it flips an action, so say at which end of it the change stops paying for itself, and otherwise give the point estimate.
Commit to the date the figure becomes an observation — the cohort reaching 180 days of age, with monthly and annual billing intervals reported as separate curves since an annual account has had no opportunity to churn before day 365 — and put that date in the footnote so the projection has an expiry.

Follow-up

The deck ships with your number and the footnote removed. What do you do, and when?
How does your answer change if the interval were [-1.5, +2.7] — wide and straddling zero more evenly?
What is the difference between what you have produced here and a forecast, and does the executive need to know?