User segmentation: build groups from usage and revenue data

User segmentation groups individual product users by lifecycle and observed usage, then compares what happens to those groups. Use a dated roster, explicit assignment rules and a later observation period so that the comparison can be reproduced. Keep the user count separate from the account revenue calculation when several people share a contract.

Start with the user and the measurement date

An account can renew while most of its users become inactive. It can also cancel while a small group still uses the product regularly. User groups help product, customer success and finance teams distinguish those situations without treating a payment as proof of engagement.

This is different from grouping companies by size, purchase frequency or margin. Use B2B customer segmentation from the ledger for the account-level question. The unit here is an individual product user, identified consistently across exports.

For this example, a US software company closes its baseline on June 30, 2026. June usage sets the groups; July activity measures subsequent retention. Include users created by the baseline date and keep that opening roster unchanged through the comparison. Exclude internal test users under a documented rule before counting anything.

Choose a qualifying activity that reflects the product's purpose. A completed business task may be more useful than a login. An active day means a calendar day with at least one qualifying event, after converting timestamps to the company's reporting time zone. Several events on the same day still represent one active day.

Prepare the exports and assign one group

Bring a user roster containing user ID, account ID, signup date and eligibility status. Add a usage summary with qualifying active days per user for June, plus a July activity flag. Bring the billing export separately and document any allocation of account recurring revenue to users.

Do not join raw event rows directly to invoice lines. That can repeat an invoice for every event. First summarize activity to one row per user and period, then match those summaries to the unique user roster. Keep unmatched IDs in an exception list; do not quietly discard them.

Use these assignment rules in order:

• New: signup on or after June 1, 2026, within the opening roster.
• Engaged: earlier signup and at least 10 qualifying active days in June.
• Light: earlier signup and between one and nine qualifying active days.
• Inactive: earlier signup and zero confirmed qualifying active days.

These thresholds are illustrative business choices, not industry standards. A new user takes precedence over the usage groups, so every eligible user belongs to exactly one segment. If usage is missing, resolve it or retain an explicit unknown group before applying the inactive rule.

In Google Sheets, put signup in B, June active days in C and the segment in G. Enter this in G2 and fill down:

=IF(B2>=DATE(2026,6,1),"New",IF(C2>=10,"Engaged",IF(C2>0,"Light","Inactive")))

Save the assignment alongside the baseline date. This is a repeatable rule for supplied records, without assumptions about a person's motives. Behavioral segmentation covers the broader account-purchasing approach.

Work the same opening users through July

The small example below contains eight single-user paying accounts. That makes each user's assigned monthly recurring revenue, or MRR, equal to the account amount. In a multi-seat business, use a finance-approved allocation or keep revenue at the account level instead.

Columns A through G correspond to this table. July active is one if the user performed a qualifying activity during July and zero if none occurred in a complete July export. Monetary amounts are USD; MRR is a recurring run-rate measure, not recognized revenue for the month.

Put the four segment names in J2:J5. Count opening users with the COUNTIFS function, which counts records matching the supplied criteria:

=COUNTIFS($G$2:$G$9,J2)

Sum the July activity flags to count retained active users. SUMIFS sums the values whose rows meet the criteria, so the same pattern also totals each segment's MRR:

July active users: =SUMIFS($E$2:$E$9,$G$2:$G$9,J2)
June MRR: =SUMIFS($D$2:$D$9,$G$2:$G$9,J2)
July MRR: =SUMIFS($F$2:$F$9,$G$2:$G$9,J2)

Covirage is not affiliated with Google.

Compare the groups and prove the totals

Subsequent active-user retention = July active users from the frozen segment / Opening users in that segment

Cohort MRR retention = July MRR from those same users / Their June MRR

The first measure describes continued activity among the opening users. It does not require June activity: the inactive group tests whether previously inactive users become active. The second measures retained recurring value and can differ from activity retention.

Percentages are rounded to one decimal place after dividing unrounded totals. Overall active-user retention is 5 / 8 = 62.5%. MRR retention is 750 / 1,100 = 68.2%. Divide the totals instead of averaging segment percentages.

Check that segment counts sum to eight distinct opening users, that the five retained flags match the July usage records, and that both MRR columns reconcile to the billing export for the same opening accounts. New July signups belong in a separate acquisition report. They cannot repair losses in this cohort.

The light group gives customer success a place to investigate, but the table does not prove that low usage caused cancellation. Check tenure, contract status and account context before choosing an action. For repeated observations by signup period, use a cohort retention table.

Where user segmentation goes wrong

• Moving membership during measurement. Reclassifying July users before measuring July retention removes the baseline comparison.
• Counting events as users. A frequently active person can dominate an event count while still representing one user.
• Repeating account MRR. Assigning the full contract to every seat inflates segment revenue. Allocations must add back to the account total.
• Treating missing usage as inactivity. An incomplete export needs an exception flag, not a zero.
• Comparing unequal observation windows. A partial month for a recent signup is not comparable with a complete month for an established user.

Do this on your own file in Covirage

Bring a dated user roster, usage summaries, billing export and your segment rules. Covirage maps the columns, and its tools compute group sizes, retention and recurring revenue and check the totals; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for SaaS teams for the supporting business context.