How to Do Cohort Analysis in Google Sheets With QuickBooks Online Data

What is Cohort Analysis?

Getting your data into Google Sheets

Before anything else, you need transaction-level client data in your sheet. The minimum columns required are: Customer ID (column A), Signup Date, and Purchase Date. Channel, region, and product line are optional but useful if you want to slice the data later by acquisition source or service type.

If you’re pulling this from QuickBooks Online manually, exporting reports, downloading CSVs, and reformatting date columns, you already know how that goes. It takes longer than it should, the headers never match what you expect, and within two months, nobody’s bothering to keep it current.

G-Accon connects Google Sheets directly to your QuickBooks Online account and syncs the data automatically. You connect your QBO account, select the report type, and the data lands in your sheet in the right structure. No exports, no reformatting. When you want fresh data, you click refresh.

Once your raw data is in, it should look something like this:

Before moving forward, check two things. First, make sure your Signup Date and Purchase Date columns are formatted as actual dates, not text that looks like dates. Real dates align right in their cells. Text dates align left and will cause your formulas to fail.

Second, and this one matters more than it sounds- check for customers with multiple different signup dates across their rows. If C001 shows January 10th in one row and February 5th in another, those rows land in different cohorts, which quietly corrupts your retention numbers. Add a helper column, “Clean Signup Date,”  and use:

This enforces one signup date per Customer ID. Every downstream formula should reference this clean column, not the raw one.

Step 1: Group each client by their signup month 

Cohort analysis works at the month level. A client who signed up on January 4th and a client who signed up on January 29th belong to the same cohort; you’re asking the same question about both of them.

In column J, add a header: Cohort Month. In J2, use this formula:

This takes the cleaned signup date and resets it to the first day of that month. Using the Clean Signup Date column, here is what makes the MINIFS cleanup carry through.

Drag the formula down through all your rows.

Every client now has a cohort label. Clients with multiple purchases appear in multiple rows but carry the same Cohort Month, because it’s tied to their signup date, not their purchase date.

Step 2: Calculate how far along each client is

The second column tracks each client’s position in their lifecycle at the time of each transaction. You’re not just asking “did they come back?” but “how many months after joining did they come back?”

In column K, add: Transaction Month. In K2:

Apply the same MMM YYYY custom date format, keep it as a real date, just display it as text. Then, in column L, add: Period Number. In L2:

The IF check prevents negative period numbers from data entry errors. Period 0 = signup month. Period 1 = one month later. Period 2 = two months later.

DATEDIF doesn’t appear in Sheets’ autocomplete suggestions; it’s a legacy function that Google never surfaced in the UI. Type it manually, and it works perfectly.

Step 3: Build the cohort count table

Create a new sheet; call it “Cohort Pivot.” Cohort months run down the rows. Period numbers run across the columns (0, 1, 2, 3…).

Each cell answers: how many unique clients from this cohort were active in this period? A client with three transactions in the same month should count once, not three times. In B2 (January cohort, Period 0):

FILTER pulls the matching Customer IDs, UNIQUE deduplicates them, COUNTA counts what’s left. Write it once in B2, drag across all period columns, and down all cohort rows.

Leave future periods blank, not zero. Blank means “not yet.” Zero means “no one came back.” They colour differently in the heatmap.

Step 4: Convert counts to retention rates

A drop from 5 clients to 3 means something very different if Period 0 started with 6 versus 60. You need percentages to compare cohorts fairly.

Create a third sheet, “Retention Rates.” For C2 (January cohort, Period 1):

The $B2 reference always pulls Period 0 for that cohort. The IF check keeps cells blank where Period 0 is missing rather than throwing a division error. Drag across all periods and down all rows, then format as a percentage.

Period 0 = 100% by definition. After that, the numbers drop. A cohort that goes 100% → 70% → 60% → 58% has decent early retention that plateaus. One that goes 100% → 45% → 30% → 28% lost most clients fast but held a loyal core, two completely different problems.

Step 5: Apply the heatmap

Numbers in a table work. A colour-coded heatmap is harder to ignore.

Select your retention rates table, period headers included, cohort month labels excluded. Go to Format → Conditional Formatting → Colour Scale. Set the minimum to white. Set the maximum to #2f4c47. Do not include blank cells in the range; zeros and blanks distort the scale.

Dark rows = strong cohorts. Rows that bleach out by Period 2 = early churn. A sudden shift between two adjacent rows = something changed between those acquisition periods worth investigating.

Keeping it fresh (which is where most people fall down)

A cohort table built in March and not updated since is a historical artefact, not a monitoring tool.

G-Accon’s direct connection to QuickBooks Online means refreshing is a single click. The raw data updates and every formula downstream, Cohort Month, Period Number, the COUNTA(UNIQUE(FILTER())) table, and the retention rates, are recalculated automatically.

For firms managing multiple QBO clients, G-Accon lets you pull from several accounts into one consolidated sheet. One view across your entire book of business, segmented by client or aggregated, with no separate cohort table per client.

What to actually do with the results

Once your heatmap is live, here’s what’s worth looking for:

Cohort analysis gives you the pattern. What you do next is a business decision. But at least you’re making it with clear information.

Building this takes an afternoon. Maintaining it takes almost nothing.

The setup, getting data from QBO, adding the helper columns, building the count table and the retention rates sheet, takes a few hours the first time. After that, with G-Accon keeping the data current, maintaining it takes a few minutes whenever you want fresh numbers. No rebuilding, no re-exporting, no reformatting.

For accounting firms serious about understanding client retention, not just reporting on it after the fact, that’s a meaningful shift. You stop chasing the data and start reading it.