IntermediateLesson 20 of 306 min read

Intermediate Project: Do Members Visit More?

Combine measures, a join, group summaries, ranges and checks into one script and write an honest report with a decision.

In this lesson you will

  • Plan an analysis from question to decision
  • Join, summarise and compare two groups
  • Give ranges and run checks
  • Write a report that states its doubts

About 32 minutes

The question

Leila has a decision to make. The workshop could spend its small budget on a members’ evening, a quiet session with tea and priority repairs. That makes sense only if members actually use the workshop more, or value it more. So the project question is: do members visit more, and are they happier or slower to serve than other customers? It follows the path from lesson 1: a question with measures, a table you can trust, a fair comparison, honest uncertainty, and a decision at the end.

The plan

  1. Define the measures as in lesson 11: visits per visitor, rating among rated visits, minutes at the bench.
  2. Join the visits to the customers table, as in lesson 12, with the validation turned on and the spelling fixed.
  3. Summarise by group, counting visitors as well as visits, because a handful of people can make a lot of visits.
  4. Give ranges for the ratings, using the bootstrap from lesson 15.
  5. Check the table with rules before you trust anything, as in lesson 11.
  6. Write the report: the answer, the evidence, the doubts and the decision.

The program below does all of it in one script, and finishes with four checks. Read it as a template: the same shape of script, with different columns, answers many questions.

import pandas as pd, numpy as np
visits = pd.read_csv("clean.csv", parse_dates=["date"])
people = pd.read_csv("customers.csv")
people["customer"] = people["customer"].replace({"Hanna Lee": "Hana Lee"})
df = visits.merge(people, on="customer", how="left", validate="many_to_one")
checks = [
    ("rows kept by the join", len(df) == len(visits)),
    ("every visit matched", df["member"].notna().all()),
    ("ratings between 1 and 5", df["rating"].dropna().between(1, 5).all()),
    ("cost never negative", (df["cost"] >= 0).all()),
]
yes, no = df[df["member"] == "yes"], df[df["member"] == "no"]
def rating_range(part, seed):
    r = part["rating"].dropna().to_numpy()
    rng = np.random.default_rng(seed)
    boot = [rng.choice(r, size=len(r), replace=True).mean() for _ in range(4000)]
    return r.mean(), np.percentile(boot, [2.5, 97.5])
print("PROJECT REPORT: do members visit more?")
print("visits: members " + str(len(yes)) + ", others " + str(len(no)))
print("people: members " + str(people["member"].eq("yes").sum()) + ", others " + str(people["member"].eq("no").sum()))
seen = df.groupby("member")["customer"].nunique()
print("visitors: members " + str(seen["yes"]) + ", others " + str(seen["no"]))
per = {k: round(len(v) / seen[k], 1) for k, v in df.groupby("member")}
print("visits per visitor: members " + str(per["yes"]) + ", others " + str(per["no"]))
for label, part, seed in (("members", yes, 1), ("others", no, 2)):
    m, (lo, hi) = rating_range(part, seed)
    print("rating " + label + ": " + str(round(m, 2)) + " (range " + str(round(lo, 1)) + " to " + str(round(hi, 1)) + ")")
print("mean minutes: members " + str(round(yes["minutes"].mean(), 1)) + ", others " + str(round(no["minutes"].mean(), 1)))
top = pd.crosstab(df["member"], df["repair"]).idxmax(axis=1)
print("most common repair: members " + top["yes"] + ", others " + top["no"])
for name, ok in checks:
    print(("PASS " if ok else "FAIL ") + name)
PROJECT REPORT: do members visit more?
visits: members 18, others 20
people: members 5, others 4
visitors: members 4, others 4
visits per visitor: members 4.5, others 5.0
rating members: 3.8 (range 3.2 to 4.3)
rating others: 3.94 (range 3.4 to 4.4)
mean minutes: members 33.8, others 28.6
most common repair: members gears, others chain
PASS rows kept by the join
PASS every visit matched
PASS ratings between 1 and 5
PASS cost never negative

Here is what the report says. Members made 18 visits and other customers made 20, so the totals are close. But look beneath the totals. The workshop has 5 members and 4 other people on its list, and in this period 4 members and 4 others actually visited. The same count of visitors in each group means the visit totals can be compared fairly, and visits per visitor come to 4.5 for members and 5.0 for others. Members did not visit more.

Ratings are close too: 3.8 for members and 3.94 for others. The ranges, from the bootstrap, overlap heavily, as the printed ranges show, so the data cannot separate the groups. Members’ visits took longer on average, 33.8 minutes against 28.6. The members’ most common repair is gears and the others’ is chain, which might explain the time, because gear repairs are long. That is a guess, not a finding, and the sample cannot test it.

The decision, and its honesty

What would you tell Leila? Not “members visit more”, because the data shows no sign of it. Not “members are no different”, because eight visitors cannot show a difference that might exist. A good report says something like this:

  • The answer today: there is no evidence that members visit more or rate the workshop differently.
  • The doubt: only eight people visited in this period, so a real difference could easily be hidden.
  • The suggestion: do not spend the budget on a members’ evening on this evidence alone. Keep collecting visits for a season and look again, or run a small trial evening and compare.
  • What was left out: one member on the list never visited, and visits whose minutes were removed as a typo.

Checking the work

The four checks at the end of the report all passed: the join kept every row, every visit matched a customer, ratings stayed between one and five, and no cost was negative. A check that passes proves little by itself. Its value is that a failed check would have stopped you from sending a wrong report. Before you finish any analysis, ask three questions. Did the totals survive each step? Would a different reasonable choice change the answer? And what would make me wrong?

Take it further

  • Add a column for the area of each customer and look at the crosstab of area by repair.
  • Repeat the report each month and compare the same measures over time, remembering the lessons on partial periods.
  • Write a data dictionary for the joined table and put the rules in a file, so that anyone can run the checks.
Common mistake: people versus visits

Reporting a difference between groups without saying how many people are in each. Eight visitors and eight hundred visitors need very different words.

Made-up workshop

All customers, visits and money are made up. The conclusions describe this made-up workshop only. The skill to take away is the order of the steps, not the answer.

Choose a question from your own work or home with two groups in it. Write the plan in six lines, as in this lesson, before you open any data.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Why count visitors as well as visits?

  1. A few people can make many visits, so totals can mislead
  2. Visitors are always equal
  3. Visits are always wrong
  4. It makes the table smaller

Visits per visitor is the fairer comparison.

What does a heavily overlapping rating range suggest?

  1. The groups are identical
  2. The data cannot separate the groups
  3. The data is wrong
  4. The sample is large

The ranges overlapped, so the data could not tell the groups apart.

Which is the best summary of the finding?

  1. Members visit more
  2. Members and others are exactly the same
  3. The data is useless
  4. No evidence of a difference, with very few visitors to judge by

Say both what you saw and how little data there is.

Why run checks at the end of an analysis?

  1. They make the chart larger
  2. A failed check stops you sending a wrong report
  3. They change the results
  4. They are required by the law

A pass proves little, but a fail catches a mistake.

What is a sensible next step for Leila?

  1. Spend the budget at once
  2. Delete the customers file
  3. Ignore the data
  4. Collect more visits, or run a small trial evening

The evidence is too thin for a firm decision.

Summary

  • Plan the analysis in steps before opening the data.
  • Count people as well as visits.
  • State the answer, the doubt, the suggestion and what was left out.
  • Run checks; a failed check protects you.

Next lesson

Lesson 21 starts the advanced level: building cleaner pipelines that you can rerun.

Questions

Can I reuse this script for another question?

Yes. The shape stays the same: load, validate, join, summarise, give ranges, check, report. Change the columns and the question.

What if the answer is "not enough data"?

That is a real and useful answer. Say what you saw, how little there is, and what more data would be needed. It is better than guessing.

Should the report include the code?

Include it where your readers can find it if they want to check, but lead with the answer in plain words. Most readers want the decision, not the script.

Progress is saved in this browser.