Manually build a Google Sheets Cohort Analysis table (Step-by-Step)

Google Sheets Cohort Analysis (Step-by-Step)

Most teams can tell you this month’s revenue. Fewer can answer the questions that actually matter:

• Are newer customers retaining better than older cohorts?
• Did that acquisition push bring in high-quality customers or short-term spikes?
• Is growth coming from retention, or just constant replacement of churn? That’s what Cohort Analysis is for. A cohort table groups customers by when they first became “active” (usually their first paid month) and tracks what happens over time—Month 0, Month 1, Month 2, and so on.

What is a Cohort Analysis table?

A cohort table is a grid:

• Rows = Cohorts (example: “Jan 2025 cohort” = customers whose first active month was Jan 2025)
• Columns = Cohort age (Month 0, Month 1, Month 2…)
• Cells = Value you’re tracking
  • Customer count (logo retention)
  • Revenue / MRR (revenue retention) The goal is simple: stop looking at your business as one blended number—and start seeing which customer “classes” are healthy.

Before you start: pick your cohort definition and metric

You need two decisions up front: 1) What does “cohort month” mean?
Common definitions:

• First paid month (best for revenue teams)
• Signup month (best for product/activation tracking)
• First activity month (best for usage-based businesses) 2) What are you measuring?
• Customer (logo) retention: “How many are still active?”
• Revenue retention: “How much revenue from that cohort still remains?” Pick one first. You can always add the other after the table works.

Data you need (minimum)

Create a tab called raw with:

• customer_id (required)
• date (required; invoice date, usage date, etc.)
• amount (required if you want revenue retention) Optional but strongly recommended:
• plan
• segment (SMB/Enterprise, region, channel, etc.)

How to manually create Cohort Analysis in Google Sheets without an extension or addon

Step 1) Create a Month bucket

In raw, add a column month that normalizes each row to the month. If date is in column B, put this in raw!C2:

• =EOMONTH(B2, 0) Fill down.

Step 2) Build a clean “customer-month” table (deduped)

Most cohort tables break because raw exports have duplicates (multiple rows per customer in the same month). Create a tab: customer_month Goal output format:

Recommended approach (Pivot Table)

1. Insert → Pivot table (from raw)
2. Rows:
   • month
   • customer_id
3. Values:
   • SUM of amount (name it monthly_revenue) Now each customer appears once per month, with revenue consolidated. If you’re doing customer retention (count), you can still build this table—just ignore the revenue column later.

Step 3) Define “active customer” (your retention rule)

In customer_month, define an is_active flag. Examples:

Option A (active = any appearance in the table)

• =TRUE

Option B (active = monthly_revenue > 0)

• =C2>0 (if monthly_revenue is column C) Filter out inactive rows (or keep them but only count active ones in the cohort table).

Step 4) Determine each customer’s Cohort Month

Create a tab: customer_cohort

• cohort_month = MINIFS(customer_month!A:A, customer_month!B:B, customer_id) This assigns each customer to their first active month, which becomes their cohort.

Step 5) Add Cohort Month and Cohort Age to each customer-month row

Go back to customer_month and add:

1) cohort_month (lookup from customer_cohort)

Example:

• =XLOOKUP(B2, customer_cohort!A:A, customer_cohort!B:B)

2) cohort_age (months since cohort)

Use a reliable months-difference formula:

• =12*(YEAR(A2)-YEAR(D2)) + (MONTH(A2)-MONTH(D2)) Where:
• A2 = month
• D2 = cohort_month Now each row has:
• customer_id
• month
• cohort_month
• cohort_age (0, 1, 2…)

Step 6) Build the Cohort Table (Pivot Table)

Create a tab: cohort_table Insert → Pivot table (from customer_month) Rows:

• cohort_monthColumns:
• cohort_ageValues (choose one):

Option A: Customer retention (count)

• COUNTA of customer_id
  (Works because customer_month is already deduped to 1 row per customer per month.)

Option B: Revenue retention (sum)

• SUM of monthly_revenue You’ll get a cohort grid like:
• Row: 2025-01 (cohort)
• Col: Month 0, Month 1, Month 2… Note: newer cohorts will look “diagonal” because they haven’t had time to age yet. That’s expected.

Step 7) Convert the cohort table into retention percentages

Create a second tab: cohort_retention_% Copy your cohort table values in, then compute retention as a % of Month 0. If Month 0 is in column B:

• =IF(B2="",,C2/$B2) Fill across and down. Format as Percent. Now you have the classic cohort retention heatmap.

Step 8) Add a heatmap (Conditional Formatting)

Select the retention % range → Format → Conditional formatting:

• Color scale (low = red, high = green) This makes patterns jump off the page:
• “Month 2 drop across all cohorts”
• “Recent cohorts healthier”
• “One cohort unusually strong/weak”

How to use Cohort Analysis for real customer and revenue decisions

Once your cohort table exists, the questions become much sharper:

If Month 1 retention is weak:

• Is onboarding failing?
• Are customers hitting value too late?
• Is acquisition sourcing low-intent users?

If Month 3 retention drops across every cohort:

• You likely have a consistent “90-day problem” (usage decay, renewal friction, missing feature, etc.)

If revenue retention is strong but logo retention is weak:

• You may be losing smaller customers while larger ones expand
• That can be fine—but it changes how you think about CAC, support load, and growth strategy

Where Jetti Sheets fits

If you only need a cohort table once, the manual approach may be fine. If you want to refresh cohorts monthly (or segment them by plan/channel/region), the manual build usually turns into a maintenance project:

• exports change shape and break pivots
• duplicates and inconsistent IDs cause silent errors
• answering “who is in this cell?” becomes a separate workflow That’s where a dedicated Google Sheets add-on like Jetti Sheets helps: it’s designed to generate Cohort Analysis inside Sheets and make the “pattern → drilldown → action” loop easier to repeat.

FAQ

What is “Month 0” in a cohort table?

Month 0 is the cohort’s starting period (their first active month). Retention at later months is typically shown as a % of Month 0.

Should I build customer retention or revenue retention first?

If you sell subscriptions or have varying contract sizes, start with revenue retention. If you care about engagement or “logo stickiness,” start with customer retention.

Why do newer cohorts have fewer columns?

They haven’t existed long enough to reach Month 6, Month 12, etc. A diagonal shape is normal and expected.

Can I segment cohorts (SMB vs Enterprise, plan tiers, channels)?

Yes. Add a segment column to your customer_month table and create separate cohort pivots per segment (or filter the pivot by segment).