Segmentation from a CSV

1. The business decision

You're a growth marketer at a B2B SaaS company. The head of marketing wants to spend Q3 on two targeted campaigns instead of one generic one. She's asked you, by tomorrow, for three customer cohorts with: (a) a name, (b) how many customers are in it, (c) one specific message that would resonate, and (d) one channel that's most likely to reach them.

The data she's given you is a single CSV with 800 rows and no pre-built segments. You have an evening.

2. Data available

The CSV has these columns, with the usual data-quality surprises. Download the sample dataset (800 rows) to follow along.

customer_id, company_name, industry, employee_count,
plan_tier, mrr_usd, signup_date, last_login_days_ago,
features_used_30d, support_tickets_90d, csm_assigned

Real-world messiness: industry is free-text, plan_tier has inconsistent capitalization, ~12% of rows have null employee_count. A sample row:

c_00271, Acme Logistics, "logistics / 3pl", 340,
Pro, 1450, 2024-03-11, 4, 12, 2, "Sarah K."

3. The build

Total session was about 25 minutes, broken into three rounds.

Round 1 · Plan before execute

I started by asking the agent to plan, not act:

You are a growth marketing analyst. I have a customer CSV
(schema below) and need to produce three actionable segments
for next quarter's campaign planning.

Before writing any code, propose:
1. A segmentation approach (which dimensions to use and why)
2. Three plausible segment definitions, with the business
   rationale for each
3. The data quality issues you anticipate based on the schema
   and what to do about each

Don't run anything yet. Just the plan.

Schema:
[paste the columns + 5 sample rows]

The plan it came back with was good but proposed five segments. I asked it to cut to three and prioritize segments where the campaign action would be most concrete. It did.

Round 2 · Execute

Once the plan was approved:

Now execute the plan. Write Python that:
- Loads the CSV from ./customers.csv
- Normalizes the messy fields (industry buckets, plan_tier
  casing, handle null employee_count)
- Applies the three segment definitions from your plan
- Outputs three files: segment_growth.csv,
  segment_atrisk.csv, segment_expansion.csv
- Prints a summary table with count, avg MRR, and avg
  tenure (months since signup) per segment

After running, show me the summary table and the first 3 rows
of each output file.

Round 3 · Verify and refine

The summary looked plausible but I noticed the "at risk" segment was suspiciously large. Asked:

The at-risk segment is 31% of the base. That feels high.
Walk me through the logic — what's the single threshold
driving the most assignments? Show me the histogram of
last_login_days_ago for at-risk vs not. Then propose a
tighter definition.

Turned out the threshold was 14 days, which swept in a lot of weekly-active users. We moved it to 21 days, re-ran, and the segment dropped to 14% — a number marketing could actually act on.

4. Working code

The final Python script the agent produced, lightly cleaned:

import pandas as pd

df = pd.read_csv("customers.csv")

# --- Normalize messy fields ---
df["plan_tier"] = df["plan_tier"].str.strip().str.title()
df["industry_bucket"] = df["industry"].str.lower().map(
    lambda x: "logistics" if "logistic" in str(x) or "3pl" in str(x)
    else "tech" if "tech" in str(x) or "software" in str(x)
    else "retail" if "retail" in str(x) or "ecommerce" in str(x)
    else "other"
)
df["employee_count"] = df["employee_count"].fillna(
    df.groupby("industry_bucket")["employee_count"].transform("median")
)
df["tenure_months"] = (
    (pd.Timestamp("today") - pd.to_datetime(df["signup_date"]))
    .dt.days / 30.4
).round(1)

# --- Segments ---
growth = df[
    (df["plan_tier"] == "Starter") &
    (df["features_used_30d"] >= 5) &
    (df["last_login_days_ago"] <= 7)
]
atrisk = df[
    (df["mrr_usd"] >= 500) &
    (df["last_login_days_ago"] >= 21) &
    (df["support_tickets_90d"] >= 3)
]
expansion = df[
    (df["plan_tier"].isin(["Pro", "Business"])) &
    (df["employee_count"] >= 200) &
    (df["tenure_months"] >= 6) &
    (df["features_used_30d"] >= 8)
]

growth.to_csv("segment_growth.csv", index=False)
atrisk.to_csv("segment_atrisk.csv", index=False)
expansion.to_csv("segment_expansion.csv", index=False)

# --- Summary ---
for name, seg in [("Growth", growth), ("At risk", atrisk), ("Expansion", expansion)]:
    print(f"{name}: n={len(seg)}, "
          f"avg MRR=${seg['mrr_usd'].mean():.0f}, "
          f"avg tenure={seg['tenure_months'].mean():.1f}mo")

5. What good looks like

The deliverable for the marketing meeting wasn't the script — it was three rows in a one-pager:

You'd run the segment counts past your analytics team before sending real campaigns, but for a planning meeting tomorrow, this is more than enough to make an informed decision.

6. Extensions

If you have more time, ask the agent to:

• Add a fourth "untouched-by-CSM" cut across all three segments to surface CSM coverage gaps.
• Generate a per-segment slide with the count, the message, and a chart of MRR distribution.
• Wrap the script as a recurring job that re-runs weekly and emails you the deltas.

Each of these maps to a later module — the recurring-job version is covered in Module 6's section on hooks.

7. Skills used