Score four early-warning signals against client renewal with a correlation matrix, so you know which metric actually predicts churn. Free Sheets template.
Paste the signals you log each quarter beside whether the client renewed and get back which signal actually predicts it. Twenty-two client quarters and four early-warning signals. The one everybody watches correlates 0.083 with renewal, and the one nobody logs correlates -0.933.
Work Intermediate Statistics free
After you install, this is the model to open.
What Moves With a Client Leaving?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Watch this
- r = -0.93 days since last exec contact, against renewal
- Second signal
- r = 0.63 share of contracted hours actually used
- Middle signal
- r = 0.31 sponsor meetings held in the quarter
- Ignore this
- r = 0.08 support tickets barely track renewal at all
Twenty-two client quarters, with renewal as a one or a zero and four signals beside it. Thirteen of the twenty-two ended in a renewal. Click Run and read down the renewal line. Days since the last executive contact correlates -0.933. Contracted hours used correlates 0.635. Sponsor meetings held correlates 0.307. Support tickets raised correlates 0.083.
That last number is the reason to run this. Ticket volume is on every account dashboard because it is easy to count, and on this book it carries no information at all about whether a client stays. The strongest signal by a distance is a number nobody logs anywhere: how long it has been since a partner spoke to the person who signs the invoice.
Correlating a one-or-zero flag against a measurement is legitimate and the matrix does it with no special setting, so a renewal flag can sit alongside the metrics in the same run. Two more readings are worth taking. Hours used and sponsor meetings correlate 0.080 with each other, so they are independent signals rather than two views of the same engagement, and an account scoring badly on both is in a different position from one scoring badly on either.
And days since exec contact correlates -0.655 with hours used, so a quiet client is also an underconsuming client, which means those two are partly the same story told twice. The obvious next run is Logistic Regression: move the renewal flag to the end of the block, point the tool at the four signals plus renewal, and the correlations turn into odds that tell you what each signal is worth once the others are known.
What this cannot tell you is cause. Executives stop returning calls because a client is leaving at least as often as the reverse, and a correlation of -0.933 is equally consistent with both. To use your own book, paste one client quarter per line, keep every analyzed column numeric, and widen the input range.
The model
It arrives on a tab called Template: What Moves With a Client Leaving, carrying these columns:
- Renewed (1 = yes)
- Contracted hours used (%)
- Sponsor meetings held (per quarter)
- Days since last exec contact
- Support tickets raised
- Client and quarter
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Which stream will move the delivery date?When Does This Engagement Actually Finish?
- How many hours will this scope actually take?What Should This Engagement Cost to Deliver?
- What if the partner with the fee book leaves?What Does Losing One Person Cost?
- A consulting engagement risk register ranking five risks by exposure and by mitigation value, since the two rankings are not the same list. Free template.What Could Go Wrong on This Engagement?
- Is your busiest person really the problem?Is Anyone Booked on Two Clients at Once?
- What does one conflict of interest cost you?Who Works on Which Client?
Every model like this one, and the method behind them: Statistics in Google Sheets.