Churn tracker in Excel: status, renewal date, and MRR
A churn tracker is one row per account. You type the account name, a status, the renewal date you are watching, and the MRR you are watching. The status is a word from a list: active, at-risk, or churned. The renewal flag compares the date with today, inside a window you typed in one cell. Churned rows do not get a renewal flag. SUMIFS adds the MRR on the rows whose status is at-risk. COUNTIFS counts those rows. This sheet is not a ticket queue. A ticket queue is one line per case, and one account can have many cases. If you put the cases on this sheet, one account becomes many rows and you can no longer answer "what is the status of this account" by reading a single word. Keep the case history on its own list. Keep the account here. A call can be a note. It does not become a new row. The sheet does not know product usage, a health score, or a reason you did not type. You type the status. You type the date from the contract, or from the review you booked. You type the MRR as a number, in the currency of this file. IF and SUMIFS only read those cells. Change the window in the named cell when you want to look further ahead. Do not hide 45, or any other number, inside the formula. Before you tell someone the book is at risk, read three accounts you can check. The status must be the word you meant. The renewal date must match the contract or the booking in front of you. The MRR must be the monthly amount you are willing to add up. Elyx can write the flag and the sums. You still decide who to call. The trial lasts 7 days, with no card.
The Problem
The sheet fails when status is a sentence. "Unhappy after the last release, maybe saveable" cannot be summed. Put the sentence in a note. Put active, at-risk, or churned in the status. The sum reads the status. It also fails when each call starts a new row for the same account. You then have five statuses and five MRR figures, and the total is fiction. One account, one row. You edit the status in place. The note can say what the last call changed. A third failure is letting a formula invent MRR from a seat count and a price you do not maintain. If the price on the sheet is old, the MRR is a story. This sheet uses the amount you typed. If the contract changed, type the new amount. Do not leave a formula that multiplies seats by a price nobody owns. A fourth failure is keeping churned accounts inside the renewal flag. A gone account does not have a renewal to prepare. The flag stays blank when status is churned. The MRR on that row stays out of the at-risk sum, because the status is not at-risk. If you still want to see how much MRR left, use a separate sum on status churned, and label it as gone. Do not mix it into the book you still have.
Benefits
One row per account, edited in place when the status changes.
Status is one of three words, so SUMIFS can add the MRR you typed.
The renewal flag compares a date you typed with today, and skips churned rows.
The look-ahead window is a cell, not a number buried in the formula.
Tickets stay on a case list so the book does not grow a row per complaint.
Step-by-Step Tutorial
Lay out one row per account
Name the sheet Book. Headers in row 1: Account, Status, Renewal, MRR, Flag, Owner, Note. Account is the name your team already uses, unique on this sheet. Status, Renewal, MRR, Owner, and Note are entries. Flag is a formula. MRR is the monthly amount you typed, not an annual figure you forgot to divide. One account occupies one row for as long as you keep it in this book. Add a row when an account joins. Do not add a row because you had a call, and do not add a row because a month started. If you track several products for one customer as separate contracts, each contract can be a row only if the account name plus a contract id is unique. Write that id in Account or in a column of its own. Two rows with the same name and no id will both land in the sum.
Sort by Account and scan for duplicates before you rely on the MRR sum. Two rows for one contract will be added twice.
Restrict the status words
On a sheet named Lists, type active, at-risk, and churned in three cells. Apply data validation to the Status column so only those words can be entered. Active means you are not treating the account as a save. At-risk means you typed that word because you intend to work it. Churned means it has gone, on the date you accept. If you need the story, use Note. Do not add a fourth status unless you also change every SUMIFS that defines the book. Agree the list before the first week of use. "At risk" with a space, or "At-Risk" with capitals, will not match the sum. The words in the list and the words in the formula must be the same characters.
Validation stops new typos. It does not fix a status that was pasted before the validation existed. Clean those cells once.
Type the renewal date you actually have
Renewal is a real date: the contract end, the renewal notice date, or the review you booked. Leave it blank if you do not have a date. Do not type a guess to make the column look full. Format the column as a date. A date entered as text will not compare with TODAY. If you are unsure, retype the date and check that it sits as a date, not as text aligned to the left. Keep one date, the soonest one you care about. A second date, such as a notice deadline versus the term end, needs a second column. The flag can compare only one date. Do not pack two dates into one cell.
If the contract renews unless someone cancels, the date you type is still the date you are watching. The sheet does not know the legal rule. You do.
Type MRR as a monthly amount
MRR is a number you type. It is the monthly amount this row represents, in the currency of the file. If the contract is annual, divide it yourself before you type, or type the annual amount in a different column that the sum does not read. Do not mix monthly and annual figures in the MRR column. A blank MRR is not zero. Leave it blank if you do not have a figure you will stand behind. A zero means you typed zero. The at-risk sum ignores text. If someone types "unknown" in MRR, that row will not add, and it will not warn you with a word. Keep MRR numeric. Put the unknown in the note, and leave the amount blank until you have it.
Write the currency once, in the header or in a cell above the sum. The formula does not convert currencies.
Flag a renewal inside the window
Put the look-ahead in a cell named Window, for example 45, meaning you want to see renewal dates that fall within 45 days as well as dates already past. With Status in B2 and Renewal in C2, the flag is =IF(B2="churned","",IF(C2="","",IF(C2<=TODAY()+Window,"renew","ok"))). A churned row stays blank, so a gone account is not a renewal to chase. A blank date stays blank, so an account with no date is not marked ok. A date on or before today plus the window is renew. Any later date is ok. Change Window when you want a longer look-ahead. Every row reads that one cell. Name the cell with Formulas, Define Name, so the formula stays readable.
=IF(B2="churned","",IF(C2="","",IF(C2<=TODAY()+Window,"renew","ok")))If you move the Window cell, update the name. A formula that still points at the old cell will use a stale number.
Add the MRR on the rows you marked
At-risk MRR is =SUMIFS(D:D,B:B,"at-risk"). That is the sum of the amounts you typed on rows whose status is at-risk. It does not look at the flag. A second sum, MRR whose flag is renew, is =SUMIFS(D:D,E:E,"renew"). Read both. The first is the book you marked as work. The second is the book whose date is inside the window and whose status is not churned. Active rows with a far renewal date sit in neither story unless you add a third sum for active. Churned MRR, if you want it, is =SUMIFS(D:D,B:B,"churned"), and you label it gone. The count of at-risk accounts is =COUNTIFS(B:B,"at-risk"). Put the sums at the top and label them in words. A number without a label will be read as the whole book.
=SUMIFS(D:D,B:B,"at-risk")Format the sums as numbers, not as dates. A large MRR formatted as a date looks like a year in the distant future and will be sent that way.
Update the row when the account moves
When you decide an account needs work, change Status from active to at-risk and type the owner. When the risk passes, set Status back to active. When the account leaves, set Status to churned. Do not delete the row on the day it leaves if you still want the gone sum. Do not leave Status as active because you hope they will stay. The sheet describes the status you typed now. The note can say what you hope. Hope is not a status. If the renewal date moves because the contract was extended, type the new date. The flag will follow. If MRR changes, type the new amount. The old amount is not kept on this row. A log can keep the history if you need it. This sheet keeps the current figures.
If two people own the book, agree that the person who last spoke to the account updates the row before the weekly read.
Check three accounts before you publish the sums
Pick three rows. For each one, look at the contract or the note you trust. Status must be the word you meant. If Renewal is filled, the date must match. If MRR is filled, the amount must be the monthly figure you meant, in the currency of the file. The flag must be blank when status is churned or the date is empty, renew when the date is inside the window, and ok when the date is later. If one row fails, do not send the at-risk sum. Fix the cell. The sum is the sum of the numbers on this sheet. It is not a forecast of who will leave, and it is not a billing-system balance. A ticket list can hold the cases. Do not paste those rows onto the book.
Read the label above the sum out loud before you paste it into a note. "At-risk MRR" and "renewal-window MRR" are different numbers.
Template Features
One current status
Status is active, at-risk, or churned. SUMIFS adds MRR for the word you choose. Note holds the sentence.
Renewal flag that skips churned rows
The flag is renew when the date is inside the window and the account is not churned. A blank date stays blank.
=IF(B2="churned","",IF(C2="","",IF(C2<=TODAY()+Window,"renew","ok")))Window in one cell
Change the named cell Window to look further ahead. Every row reads it. You do not edit the formula.
At-risk MRR
SUMIFS adds the MRR you typed where status is at-risk. It does not include churned rows.
=SUMIFS(D:D,B:B,"at-risk")Renewal-window MRR
A second SUMIFS adds MRR where the flag is renew, so the date problem stays separate from the status you typed.
=SUMIFS(D:D,E:E,"renew")Concrete Examples
An account marked at risk, date inside the window
You typed at-risk. The renewal date is 20 days from today. Window is 45. MRR is the monthly amount on the contract.
Status: at-risk. Renewal: 20 days from today. Window: 45. MRR: the monthly figure you typed.
Result: Flag is renew. The at-risk sum includes the MRR. The renewal-window sum includes it too. The row is one row. You did not create a second line for the risk.
An account with no date yet
A new account has no renewal date entered. You do not want it marked ok.
Status: active. Renewal: empty. Window: 45. MRR: filled.
Result: Flag is blank. The renewal-window sum does not include the MRR, because the flag is not renew. The at-risk sum does not include it either, because status is active. Fill the date when you have it.
An account that has left
The customer ended the contract. The row still says at-risk because nobody edited it.
You set Status to churned. Renewal stays as the old date. MRR stays as the last amount you typed.
Result: Flag becomes blank, because churned rows are excluded. The at-risk sum drops by that MRR. A separate churned sum, if you built one, includes it. The row remains one row.
Pro Tips
The account name is the key
People shorten names. If two contracts share a customer, put a contract id on the row. The sum cannot tell which row you meant from a nickname.
MRR is an entry
Type the monthly amount when you have it. Do not let a formula multiply seats by a price you stopped maintaining. A made-up MRR will be added as if it were real.
Notice date and term end are two dates if you track both
Add a second date column and a second flag if you truly watch two dates. Do not pack both into Renewal. The flag compares one date.
A gone account can leave the book
If you no longer want churned rows in the file, cut them to a sheet named Left. Leaving them as active keeps their MRR in the wrong sum.