Data Analyst Interview Questions and Answers, Part 2: The Metric Investigation Case

Part 1 of the data analyst interview questions and answers covers the rounds and a worked take-home task. This part is about the round that trips up more analysts than SQL does: the metric investigation case. The interviewer gives you one sentence, "Cancellations rose six points this spring. Why?", and watches how you think.

There is no single right answer. What interviewers score is the order of your steps: whether you check the data before explaining it, separate a change in mix from a change in behaviour, and narrow down to a driver you can act on. Here is a real case worked through those steps, on public booking data from two Portuguese hotels, a resort in the Algarve and a city hotel in Lisbon (Antonio, de Almeida and Nunes, 2019), using the single-file copy published by the TidyTuesday project (TidyTuesday: hotels).

import pandas as pd

# Hotel booking demand (Antonio, Almeida and Nunes, Data in Brief 2019, CC BY 4.0): two Portuguese hotels
h = pd.read_csv("hotels.csv")
q = h[h.arrival_date_month.isin(["April", "May", "June"]) & h.arrival_date_year.isin([2016, 2017])].copy()
q["year"] = q.arrival_date_year

# Step 1: confirm the metric, the period and the volumes
print("S1 cancellation rate of bookings arriving April-June")
print(q.groupby("year").is_canceled.agg(bookings="size", cancellation_rate="mean").round(3).to_string())

# Step 2: sanity-check the data before explaining anything
print("\nS2 cancellation rate by deposit type, all years")
print(h.groupby("deposit_type").is_canceled.agg(bookings="size", cancellation_rate="mean").round(3).to_string())

# Step 3: is it a change in mix, or a change in rates?
def mix_vs_rate(col):
    g = q.groupby(["year", col]).is_canceled.agg(["size", "mean"]).unstack(0).fillna(0)
    share = g["size"] / g["size"].sum()
    rate = g["mean"]
    mix = ((share[2017] - share[2016]) * rate[2016]).sum()
    within = (share[2017] * (rate[2017] - rate[2016])).sum()
    contribution = share[2017] * rate[2017] - share[2016] * rate[2016]
    return mix, within, share, rate, contribution

print("\nS3 change of +{:.1f} points, split into mix and within-group rate changes".format(
    100 * (q[q.year == 2017].is_canceled.mean() - q[q.year == 2016].is_canceled.mean())))
for col in ["hotel", "market_segment", "deposit_type"]:
    mix, within, *_ = mix_vs_rate(col)
    print(f"   by {col:<15} mix {100 * mix:+5.1f} points | within-group rates {100 * within:+5.1f} points")

# Step 4: which segment contributes most?
_, _, share, rate, contribution = mix_vs_rate("market_segment")
table = pd.DataFrame({"share_2016": share[2016], "share_2017": share[2017], "rate_2016": rate[2016],
                      "rate_2017": rate[2017], "contribution_points": 100 * contribution})
print("\nS4 market segments, largest contributions first")
print(table.sort_values("contribution_points", ascending=False).head(4).round(3).to_string())

# Step 5: inside the biggest contributor, does booking lead time matter?
ota = q[q.market_segment == "Online TA"].copy()
ota["lead"] = pd.cut(ota.lead_time, [-1, 30, 90, 180, 1000], labels=["0-30 days", "31-90", "91-180", "181+"])
print("\nS5 Online TA bookings by lead time: share of bookings and cancellation rate")
s5 = ota.groupby(["lead", "year"], observed=True).is_canceled.agg(["size", "mean"]).unstack("year")
s5_share = s5["size"] / s5["size"].sum()
print(pd.DataFrame({"share_2016": s5_share[2016], "share_2017": s5_share[2017],
                    "rate_2016": s5["mean"][2016], "rate_2017": s5["mean"][2017]}).round(3).to_string())

S1 cancellation rate of bookings arriving April-June
      bookings  cancellation_rate
year                             
2016     16198              0.375
2017     17621              0.435

S2 cancellation rate by deposit type, all years
              bookings  cancellation_rate
deposit_type                             
No Deposit      104641              0.284
Non Refund       14587              0.994
Refundable         162              0.222

S3 change of +6.0 points, split into mix and within-group rate changes
   by hotel           mix  +0.3 points | within-group rates  +5.8 points
   by market_segment  mix  -1.1 points | within-group rates  +7.1 points
   by deposit_type    mix  -1.1 points | within-group rates  +7.1 points

S4 market segments, largest contributions first
                share_2016  share_2017  rate_2016  rate_2017  contribution_points
market_segment                                                                   
Online TA            0.430       0.532      0.338      0.425                8.072
Groups               0.175       0.166      0.571      0.681                1.339
Direct               0.081       0.094      0.154      0.162                0.286
Corporate            0.041       0.033      0.184      0.293                0.217

S5 Online TA bookings by lead time: share of bookings and cancellation rate
           share_2016  share_2017  rate_2016  rate_2017
lead                                                   
0-30 days       0.329       0.240      0.244      0.257
31-90           0.347       0.254      0.399      0.417
91-180          0.272       0.313      0.356      0.464
181+            0.052       0.192      0.420      0.581

Step 1: pin down the metric

Before any explanation, restate the metric precisely: the share of bookings arriving in April to June that were cancelled, comparing 2016 with 2017. The rate rose from 37.5% to 43.5%, on about 16,000 and 17,600 bookings. Saying the definition aloud matters, because "cancellation rate" could also mean cancellations made in the month, which is a different number. In an interview, also ask: compared with what, and is six points unusual for this business?

Step 2: check the data before trusting it

One table already contains a surprise: bookings with a "Non Refund" deposit were cancelled 99.4% of the time, against 28.4% with no deposit. Taken literally, that is backwards: people who pay a non-refundable deposit should cancel less. The dataset's paper defines the category but does not explain this pattern, so the honest move in an interview is to flag it, not to explain it away: "This looks like a recording or business-process effect, perhaps group or tour-operator blocks. I would ask the hotel team before using deposit type as an explanation."

This step is what interviewers most often see skipped. A candidate who builds a story on "non-refundable bookings drive cancellations" has explained a data quirk, not customer behaviour.

Step 3: mix or rate?

A rate can rise for two reasons: the mix of bookings shifted towards groups that always cancel more, or the same groups started cancelling more. The decomposition splits the six points:

• By hotel: almost all within-hotel rates, +5.8 points. The city hotel cancels more, but its share barely moved.
• By market segment: within-segment rates explain +7.1 points; mix actually pulled the other way by 1.1 points.

So this is mostly a change in behaviour, not just a different mix of customers. That one sentence is often what the interviewer wants to hear first.

Step 4: find where it is concentrated

Online travel agents dominate the change. Their share of bookings rose from 43% to 53%, and their cancellation rate rose from 33.8% to 42.5%, contributing about 8 points on its own. Other segments, mainly the shrinking offline travel agent business, pulled the total back to six. Groups also cancelled more, but they are a smaller share.

Step 5: drill one level down, then stop

Within online travel agent bookings, lead time changed sharply. Bookings made more than 180 days ahead went from 5% to 19% of the segment, and their cancellation rate rose from 42% to 58%. Bookings made 91 to 180 days ahead also cancelled more. Long-lead bookings through online agents are a plausible driver to investigate: they may reflect free-cancellation offers or customers booking several options early.

Then stop and state the limits. The data shows where the change sits, not why customers cancelled. The next step would be a conversation with the revenue team about online agent cancellation terms in 2017, not more slicing.

How to present the answer

In the interview, give the conclusion first, then the evidence, then what you would do:

"The six-point rise is mostly behaviour, not mix. It is concentrated in online travel agent bookings, which grew and cancelled more, especially those booked more than three months ahead. I'd check whether agent cancellation terms changed in 2017, and I'd flag the non-refundable deposit data as unreliable until confirmed."

That answer is short, structured and honest about what is unknown, which is exactly what this round tests.

Mistakes that cost points

• Jumping to a cause before checking the definition and the data.
• Slicing by everything without a reason. Pick dimensions that could plausibly change behaviour.
• Ignoring mix. A segment-level story is wrong if the change came from a shift in volume.
• Not stopping. Endless drill-downs look like searching, not reasoning.

For the statistics behind comparing two rates, see correlation vs causation, and for choosing metrics worth investigating, metrics that matter.

Case-style analysis, from metric definitions to presenting findings, is practised in the Data Analytics course.

Sources

• Hotel booking demand datasets (Antonio, de Almeida and Nunes, Data in Brief, 2019) — Data in Brief (Elsevier)
• Hotels dataset, TidyTuesday 2020-02-11 (R for Data Science community) — R for Data Science Online Learning Community