Customer Health & Churn Risk
Proactive Outreach Register

Behavioral churn-risk scoring for every active customer, derived from three leading indicators inside NetSuite: order-cadence decay, basket contraction, and payment-latency drift.

01Executive Summary

Key Findings

1. Karmabit is the clearest quiet-churn case in the B2B book. A steady ~31-day reorder rhythm for 20 orders — now silent for 123 days (3.9× its own cycle). Order count fell 6→2 across the last two half-years. $64.6K lifetime revenue. No open balance, no dispute on record — it simply stopped ordering.
2. Design Excellence Ltd. ($238.7K lifetime) is splitting its wallet. It still orders every few days — frequency actually rose 6→14 — but average order value halved ($11,318→$5,183), basket breadth compressed from 11 to 7.2 distinct items, and days-to-pay drifted +5.9d. High-frequency/shrinking-basket is the classic pattern of a customer diverting share to another supplier.
3. Jones Manufacturing — the single largest account ($316.7K) — shows early-stage drift. AOV down 49% ($13,838→$7,055) and days-to-pay +5.6d, while cadence still holds. This is the cheapest possible moment to intervene.
4. Entenmanns LLC ($60.5K) has gone quiet. 80 days of silence against a 44-day rhythm; order count halved. Last order Jun 3.
5. A separate "won-then-lost" cohort holds $473K of >90-day A/R. Global Information ($110.6K, 262d), Red Rivers Consulting ($102.9K, 155d), Mercury Co. ($80.1K, 460d), Gotter inc. ($68.1K, 256d) and Haskell Associates ($43.9K, 327d) each placed one large order, never paid in full, and never returned. These route to collections, not retention.
6. Payment behavior is a corroborating signal here, not a leading one. 89% of applied invoices in this account pay same-day, so days-to-pay drift rarely fires alone — but where it does (Design Excellence +5.9d, Jones +5.6d, Hugo +6.0d, Marshall +5.5d), it reliably co-occurs with basket decay. The model weights it accordingly.

02Risk Distribution

43 additional customers had fewer than 5 orders — too little history for trend scoring. They are triaged separately in Section 05; several carry the largest open balances in the book.

03Priority Accounts

04Churn-Risk Register

05The Won-Then-Lost Cohort

06Outreach Routing

Suggested operating rhythm: re-run this scoring monthly after close. An account entering At Risk generates a rep task; an account crossing 3× its own order cycle generates an immediate alert. Both are automatable as a NetSuite scheduled script — the agent-builder process in this account can take this report's model as its Step-1 blueprint.

07Methodology

Each sub-signal is clamped to [0,1] before combination. Comparison windows are trailing 180 days vs the prior 180 days, measured from the report date. The worst-plus-corroboration pillar design was chosen over simple averaging because churn signals are asymmetric: one screaming signal (123 days of silence) matters more than three mild ones, and averaging dilutes it.

08Source Queries

SELECT
    t.entity,
    TO_CHAR(t.trandate, 'YYYY-MM-DD') AS trandate,
    ROUND(SUM(ABS(tl.netamount)), 2)  AS order_total,
    COUNT(DISTINCT tl.item)           AS distinct_items,
    COUNT(DISTINCT tl.class)          AS distinct_classes,
    SUM(ABS(tl.quantity))             AS units
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type IN ('CustInvc', 'CashSale')
  AND tl.subsidiary <> 4
GROUP BY t.id, t.entity, t.trandate
ORDER BY t.entity, t.trandate

SELECT
    o.entity,
    COUNT(*)                                        AS n_orders,
    TO_CHAR(MAX(o.odate), 'YYYY-MM-DD')             AS last_order,
    ROUND(TRUNC(SYSDATE) - MAX(o.odate), 0)         AS days_since_last,
    ROUND((MAX(o.odate) - MIN(o.odate)) / NULLIF(COUNT(*) - 1, 0), 1) AS avg_gap_days,
    SUM(CASE WHEN o.odate > TRUNC(SYSDATE) - 180 THEN 1 ELSE 0 END)   AS n_recent,
    SUM(CASE WHEN o.odate <= TRUNC(SYSDATE) - 180
              AND o.odate > TRUNC(SYSDATE) - 360 THEN 1 ELSE 0 END)   AS n_prior,
    ROUND(AVG(CASE WHEN o.odate > TRUNC(SYSDATE) - 180 THEN o.total END), 2)  AS aov_recent,
    ROUND(AVG(CASE WHEN o.odate <= TRUNC(SYSDATE) - 180
                    AND o.odate > TRUNC(SYSDATE) - 360 THEN o.total END), 2)  AS aov_prior,
    ROUND(AVG(CASE WHEN o.odate > TRUNC(SYSDATE) - 180 THEN o.ditems END), 2) AS items_recent,
    ROUND(AVG(CASE WHEN o.odate <= TRUNC(SYSDATE) - 180
                    AND o.odate > TRUNC(SYSDATE) - 360 THEN o.ditems END), 2) AS items_prior,
    ROUND(SUM(o.total), 2)                          AS lifetime_rev
FROM ( /* Q1 subquery: one row per order */ ) o
GROUP BY o.entity

SELECT
    d.entity,
    COUNT(*)                                                          AS paid_invoices,
    ROUND(AVG(CASE WHEN d.inv_date >  TRUNC(SYSDATE) - 180 THEN d.dtp END), 1) AS dtp_recent,
    ROUND(AVG(CASE WHEN d.inv_date <= TRUNC(SYSDATE) - 180 THEN d.dtp END), 1) AS dtp_prior
FROM (
    SELECT inv.entity, inv.id, inv.trandate AS inv_date,
           MAX(pay.trandate) - inv.trandate AS dtp
    FROM previoustransactionlinelink ptl
    JOIN transaction pay ON pay.id = ptl.nextdoc     AND pay.type = 'CustPymt'
    JOIN transaction inv ON inv.id = ptl.previousdoc AND inv.type = 'CustInvc'
    GROUP BY inv.entity, inv.id, inv.trandate
) d
GROUP BY d.entity

SELECT
    t.entity,
    COUNT(*)                              AS open_invoices,
    ROUND(SUM(t.foreignamountunpaid), 2)  AS open_balance,
    MAX(TRUNC(SYSDATE) - TRUNC(t.duedate)) AS max_days_overdue
FROM transaction t
WHERE t.type = 'CustInvc' AND t.status = 'A' AND t.foreignamountunpaid > 0
GROUP BY t.entity

Supporting: customer identity/rep/terms joined from customer, employee, term; quarterly trend from Q1 regrouped by TO_CHAR(trandate,'YYYY-"Q"Q'). Score computation performed deterministically in JavaScript (formulas in Section 07); full scored roster exported alongside this report as CSV.

09Assumptions & Limitations