Manually build a Google Sheets Customer Snowball Analysis (Step-by-Step)

Google Sheets Customer Snowball Analysis (Step-by-Step)

Most teams can tell you how many customers they have. Fewer can explain why that number changed. That’s what a Customer Snowball is for: it breaks month-to-month customer count changes into a small set of movements you can actually act on:

• Starting customers
• New customers
• Reactivated customers (returned after a gap)
• Churned customers
• Ending customers If you do customer analysis in Google Sheets today, this is one of the most useful “executive-ready” views you can build—because it turns a single number into a clear story.

What is a Customer Snowball?

A Customer Snowball explains net customer change by tracking how customers move in and out of your “active” base each period. A simple mental model: Ending Customers = Starting Customers + New + Reactivated − Churned This view is especially helpful because it separates:

• growth driven by new acquisition
• growth driven by win-backs
• declines caused by churn

Before you start: define “active customer”

You need one clear definition for “active” each month. Common choices:

• Paid customers: customer has > $0 revenue in the month
• Active users: customer has > 0 usage events in the month
• Contracted: customer has an active subscription flag Pick the one that matches how your business reports “customer count.” The rest of the steps are the same.

Data you need (minimum)

Create a tab called raw with:

• customer_id (required)
• date (required; invoice date, event date, etc.)
• amount (optional; required only if “active” is based on revenue > 0) Optional but strongly recommended:
• plan
• segment (SMB/Enterprise, region, channel, etc.)

How to manually create a Customer Snowball 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 (or your month column):

• =EOMONTH(B2, 0) Fill down.

Step 2) Convert your raw data into a monthly “active customers” table

Create a new tab: active_customer_month Goal output format:

Option A (if “active” = any appearance in raw)

If any row means “active,” you just need a de-duplicated list per month:

• Use a pivot table (Rows: month, customer_id) with no values, or
• Use a formula approach.

Option B (if “active” = revenue > 0)

If you need revenue logic, first aggregate monthly revenue per customer, then filter to > 0:

1. Create mrr_by_customer_month: | month | customer_id | monthly_revenue | |---|---|---| You can build this with a Pivot Table:

• Rows: month, customer_id
• Values: SUM(amount)

2. Then create active_customer_month by filtering monthly_revenue > 0 and keeping only (month, customer_id). Tip (Jetti Sheets improvement): this “reshape + dedupe + validate active definition” step is where spreadsheets become fragile when exports change. If you plan to do this monthly, Jetti Sheets can standardize formats and reduce the amount of manual cleanup.

Step 3) Determine each customer’s “first active month”

Create a tab customer_first_month with:

• first_month = MINIFS(active_customer_month!A:A, active_customer_month!B:B, customer_id) This gives you a stable way to tell if a customer is truly “new” versus “reactivated.”

Step 4) Determine the prior month and whether the customer was active

In a tab called snowball_detail, create one row per (month, customer_id) from active_customer_month. Add:

• prior_month = EOMONTH(month, -1) Now we need to know whether the customer was active in the prior month. Create a key in both places:
• In snowball_detail: key = customer_id & "|" & TEXT(month, "yyyy-mm-dd")
• In active_customer_month: same key Then use a lookup to check if the prior-month key exists. Example:
• was_active_prior = IFNA(MATCH(customer_id & "|" & TEXT(prior_month,"yyyy-mm-dd"), active_customer_month_keys, 0), "") Return TRUE/FALSE based on whether it matched.

Step 5) Classify movement type for each customer-month

Create a column movement using these rules:

• New if month = first_month
• Reactivated if month > first_month AND was_active_prior = FALSE
• Retained if was_active_prior = TRUE So far, we’ve classified customers who are active this month. Now we need to compute churned customers (customers active last month but not this month).

Step 6) Compute churned customers for each month

Create a tab churned_customer_month. For each month M:

• Take the set of customers active in prior_month
• Subtract the set of customers active in month In Sheets terms, it’s a “set difference.” The easiest approach:

1. Build active_customers_current for month M
2. Build active_customers_prior for month M-1
3. Churned = customers in prior not in current You can do this with FILTER() + MATCH(). Example concept:

• =FILTER(prior_list, ISNA(MATCH(prior_list, current_list, 0))) Now you have churned customers explicitly listed per month. Tip (Jetti Sheets improvement): churn calculation is where many DIY snowballs quietly break (because of missing months, duplicates, or inconsistent IDs). Jetti Sheets’ Snowball view handles the period-to-period movement logic and makes churn lists easier to drill into.

Step 7) Summarize into the Snowball table

Create a tab snowball_summary with columns:

• ending = count of active customers in month
• starting = prior month’s ending
• new = count of customers where movement = New
• reactivated = count where movement = Reactivated
• churned = count of churned customers in churned_customer_month Reconciliation check:
• ending should equal starting + new + reactivated − churned If it doesn’t:
• check for duplicates in customer-month
• check month bucketing (inconsistent months)
• check missing months (gaps) and date parsing

How to use the Customer Snowball for real customer analysis

Once you have the Snowball, the best next questions are:

If churned spiked:

• Which segment churned (plan, channel, geography)?
• Was it concentrated in a few big accounts or spread across many?
• Were those customers “young” (early lifecycle) or “mature”?

If reactivations increased:

• What triggered the win-back?
• Was there a product change, pricing change, or outreach campaign?

If new customers grew but ending is flat:

• churn is offsetting acquisition (a classic “leaky bucket” signal)

Where Jetti Sheets fits

If you only do this once, the manual approach may be fine. If you do this monthly, most teams run into the same issues:

• exports change shape and break formulas
• duplicates and inconsistent IDs cause silent errors
• producing customer drilldown lists becomes a separate project That’s where a dedicated Google Sheets add-on like Jetti Sheets is useful: it’s designed to generate Snowball views inside Sheets and make the “what changed + who caused it” loop easier to run consistently.

FAQ

What is a “Customer Snowball” in Google Sheets?

A Customer Snowball is a month-by-month breakdown of customer count changes into starting customers, new customers, reactivations, churned customers, and ending customers.

What’s the difference between “new” and “reactivated”?

New customers are active for the first time. Reactivated customers were active in the past, went inactive, then returned after a gap.

Can I segment a Customer Snowball?

Yes. Add a segment column (plan, channel, etc.) and compute Snowball metrics within each segment to identify where churn or reactivations concentrate.