Skip to content

Churn Nudge

Identifies users at risk of churning from their own activity rhythm, so CRM can run targeted re-engagement. It is a pragmatic alternative to the deprioritised ML churn model.

A fixed "30 days of silence = churned" rule fails because 30 days means different things for a daily and a monthly user. This model compares each user's current silence with their own history.

Methodology

Activity

Type Examples Counted
Intentional Calling, MTU, manual payments Yes
Passive Automatic subscription renewals, burnin revenue, expiry adjustments No

Payments with OrderSource like 'subscription%' are automatic and excluded. Null OrderSource comes from legacy non-marketplace payments and is user-initiated.

Guardrails

  • Active users only (from DimUser)
  • Complete days only, today is excluded
  • Users with fewer than 4 active days are excluded

Formulae

  1. User rhythm: take intentional activity dates over the last year, deduplicate to days, and compute intervals between consecutive active days. IntervalP90 is the 90th percentile of those intervals
  2. Deviation Ratio: CurrentInactiveDays / IntervalP90Days. Below 1.0 is normal, above 1.0 is beyond their usual longest break
  3. Floor: every boundary is at least 14 days, so a daily user is not flagged after a weekend

Segments

Segment Condition on days inactive Meaning
Active <= max(P90, 14) Within their normal rhythm
At Risk <= max(P90 * 1.3, 14) Slightly overdue
Nudge Now <= max(P90 * 1.5, 14) High priority for CRM
Likely Churned > max(P90 * 1.5, 14) Probably gone

Thresholds come from the distribution of DeviationRatio across eligible users: the 1.3 line is the shoulder of the curve and 1.5 is in the tail. It holds across destination corridors because the personal baseline absorbs corridor differences.

Model

  • Name: UserChurnNudge, table, nightly
  • Tags: NightlyRun, TestsEnabled, ConsumptionLayer
  • Primary key: UserId (unique, not_null)
  • Depends on: FactUserTransaction, DimUser
Column Description
UserId User identifier
LastActivityDate Most recent intentional activity
ActiveDayCount Active days in the last year
IntervalP90Days / IntervalP90Weeks 90th percentile interval
CurrentInactiveDays / CurrentInactiveWeeks Time since last intentional activity
DeviationRatio CurrentInactiveDays / IntervalP90Days
Churn 1 if likely churned
ChurnRiskSegment Active, At Risk, Nudge Now, Likely Churned
RevenueUSD, MarginUSD Last year, per user

Consuming it

  • Looker: joined to DimUser and FactUser. Starter dashboard: Beyond the Acquisition Lens and into the Retention Realm
  • Zendesk: UserId, ChurnRiskSegment, margin and revenue are synced so agents can route overdue high-margin users to retention. Reactive only: it covers users who contact support
  • Braze: filter ChurnRiskSegment = 'Nudge Now' for proactive outreach

Maintenance

  • Create the consumption layer view with:
dbt run-operation consumption_layer --args '{specific_models: [UserChurnNudge], dryrun: False}'
  • No PII in the output
  • When tuning thresholds, update both the Churn flag and the ChurnRiskSegment case in the final CTE

Limitations and next steps

  • Backward-looking, timing-only, equal weight for all intervals, and scored per user rather than per corridor
  • v2 ideas: recency-weighted intervals, per-destination churn, revenue trend signal, DeviationRatio and IntervalP90 as ML features