Build a cohort-retention analysis tab

Northwind tracks its newsletter subscribers, and the raw log answers “how many people signed up” easily enough. The harder question is whether they stick around: of everyone who joined in January, how many were still reading by March? That is a retention question, and a flat signups list cannot show it.

This script builds a cohort grid. It groups subscribers by the month they joined, then walks forward month by month and works out what share of each cohort is still active. The result is a triangle of percentages that makes a drop-off — or an improvement — impossible to miss.

What you’ll need

• An Activity sheet with a header row and columns userId, signupDate and lastActiveDate. Both date columns must hold real dates, not text.
• A Cohort sheet — the script clears and rewrites it on every run, so it can start empty.

The script

// Source activity log and the grid this script writes.
const ACTIVITY_SHEET_ID = '1abcActivitySheetId';
const COHORT_SHEET_ID = '1abcCohortSheetId';

// How many months forward each cohort is tracked (M0 through M11).
const MAX_MONTHS = 12;

/**
 * Groups subscribers by signup month and rewrites the Cohort sheet as a
 * retention grid: each row a cohort, each column a month since signup.
 */
function buildCohortGrid() {
  const users = readSheet(ACTIVITY_SHEET_ID);

  if (!users.length) {
    Logger.log('No activity rows — nothing to analyse.');
    return;
  }

  // 1. Bucket every user into their signup-month cohort ('YYYY-MM').
  const cohorts = new Map();
  for (const u of users) {
    const key = ym(u.signupDate);
    if (!cohorts.has(key)) cohorts.set(key, []);
    cohorts.get(key).push(u);
  }

  // 2. Sort the cohort keys so the oldest month is the top row.
  const cohortKeys = [...cohorts.keys()].sort();

  // 3. Build the header: Cohort, Size, then M0..M(MAX_MONTHS-1).
  const header = ['Cohort', 'Size',
    ...Array.from({ length: MAX_MONTHS }, (_, i) => `M${i}`)];

  // 4. For each cohort, work out the retained percentage at each month.
  const rows = cohortKeys.map((key) => {
    const list = cohorts.get(key);
    const size = list.length;
    const cells = [];
    for (let m = 0; m < MAX_MONTHS; m++) {
      // A user counts as retained at month m if they were active at
      // least m months after they signed up.
      const stillActive = list.filter((u) =>
        monthsBetween(u.signupDate, u.lastActiveDate) >= m).length;
      cells.push(size === 0 ? '' : Math.round((stillActive / size) * 100) + '%');
    }
    return [key, size, ...cells];
  });

  // 5. Clear the Cohort sheet and write the header plus every cohort row.
  const sheet = SpreadsheetApp.openById(COHORT_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, header.length).setValues([header]);
  sheet.getRange(2, 1, rows.length, header.length).setValues(rows);
}

/**
 * Formats a date as a 'YYYY-MM' cohort key.
 */
function ym(d) {
  return Utilities.formatDate(d, 'GMT', 'yyyy-MM');
}

/**
 * Whole months between two dates (ignores the day of the month).
 */
function monthsBetween(a, b) {
  return (b.getFullYear() - a.getFullYear()) * 12 + b.getMonth() - a.getMonth();
}

/**
 * Reads a sheet into an array of objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id).getSheets()[0]
    .getDataRange().getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. buildCohortGrid reads the Activity sheet into user objects and stops early if there is nothing to analyse.
2. It loops through every user, derives their signup month with ym, and pushes them into a Map bucket for that month — these buckets are the cohorts.
3. It sorts the cohort keys so the grid runs from the oldest month down.
4. It builds the header row: Cohort, Size, then M0 to M11, where M0 is the signup month itself.
5. For each cohort it walks months 0 to 11. At month m it counts users whose lastActiveDate is at least m months after their signupDate, then expresses that count as a percentage of the cohort’s size.
6. It clears the Cohort sheet and writes the header plus one row per cohort, producing the retention triangle.

Example run

Given an Activity sheet of subscribers and their last-active dates, the Cohort sheet ends up looking like this:

Cohort	Size	M0	M1	M2	M3
2026-01	120	100%	78%	61%	52%
2026-02	95	100%	84%	70%
2026-03	140	100%	88%

Each row starts at 100% in M0 — everyone is active in their signup month — and declines as people drop off. Newer cohorts have fewer filled cells because they have not been around long enough to measure later months.

Trigger it

Run this on a weekly time-driven trigger so the grid keeps up as activity is logged:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose buildCohortGrid, Time-driven, Week timer, Monday.

Watch out for

• Retention is measured from lastActiveDate alone. The script assumes a user active up to that date was active for every month in between — it cannot see a gap and a return. For true period-by-period activity, log each active month, not just the last one.
• Both date columns must be real dates. Text dates make formatDate and monthsBetween fail or produce nonsense.
• monthsBetween counts calendar-month boundaries, not 30-day spans. Someone who signs up on the 31st and is last active on the 1st of the next month counts as one month, which is usually what you want — but be aware of it.
• The grid is capped at MAX_MONTHS (12). Cohorts older than a year are still shown, but only their first 12 months. Raise the constant for a longer view.
• The later cells of recent cohorts are blank by design — there is not enough history yet. Do not read a blank cell as 0% retention.