Which client is quietly about to leave?

Forty-four client years scored on four account-health signals. Whether the executive who bought the work is still in post multiplies the renewal odds by thirty-seven, and the tenure everybody treats as safety does nothing at all.

Work Intermediate Logistic Regression free

Install free Machine learning, in a sheet

After you install, this is the model to open.

Which Engagements Renew?

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

Sponsor still in post: 37x the odds of renewal, p = 0.011
Tenure: no signal odds ratio 0.98 a month, p = 0.77
Model accuracy: 82% on the 11 engagements kept back, against 73% for guessing everyone renews
Non-renewals caught: 7 of 9 in the rows it fitted, and the calls that actually save a client

Forty-four client years with four things a partner already knows about each account. Thirty-two renewed, so predicting that everyone renews scores 72.7% and is the bar any model has to beat. Click Run: the model scores 90.9% on the 33 rows it trains on, catching 23 of the 24 renewals and, more importantly, 7 of the 9 non-renewals, with a McFadden R2 of 0.418, and 81.8% on the 11 rows it kept back.

Now read the odds ratios, which come back one row per feature in the order the feature columns sit in. Whether the original sponsor is still in post comes back at 36.68 with a p-value of 0.011: an account whose buyer is still in the chair has roughly thirty-seven times the odds of renewing, everything else held constant. Nothing else on the sheet is close.

Stakeholders we work with comes back at 2.22 with a p-value of 0.049, pointing the right way and just inside the bar, which is the familiar finding that an account rooted in several people is harder to cancel than one rooted in a single relationship. Then the two that do nothing. Tenure comes back at 0.98 for each month with a p-value of 0.770, so a client of three years is not measurably safer than a client of one, and every account plan that treats a long relationship as protection is treating the wrong thing as protection.

Budget consumed comes back at 1.020 for each point with a p-value of 0.418, and eleven of these engagements ran past 100% of budget without it costing the renewal, which is worth knowing before the next overrun conversation. The practical output is a watchlist rather than a coefficient. Put a live account's four numbers into a spare block on the sheet and point Predict rows at it, feature columns only and in the same order as the training columns: the model hands back a renewal probability, and the accounts to work are the low scorers whose sponsor has changed, because that is the one input in the model you can actually respond to.

Two limits. Forty-four rows and four features is a small model, so treat the two significant results as signals and the two null ones as unproven rather than disproven. And logistic regression cannot fix the direction of cause: sponsors leave failing engagements as well as failing engagements following sponsors out of the door, and this data cannot separate the two.

To use your own book, keep renewal as a 1 or a 0 in the last column of the training range, leave the client name outside it, and widen the range.

The model

It arrives on a tab called Template: Which Engagements Renew, carrying these columns:

Tenure (months)
Budget consumed (%)
Stakeholders we work with (count)
Original sponsor still in post (1 = yes)
Renewed (1 = yes)
Client

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

Every model like this one, and the method behind them: Machine learning in Google Sheets.

Install free All 379 templates