# Customer Health Forecasting

> Investigation into predictive analytics for Net Revenue Retention (NRR) and customer churn

- HTML version: https://robbiepalmer.me/projects/customer-health-forecasting
- Status: completed
- Started: 2022-09-01
- Technologies: Python, Google BigQuery, PyPika

# Vision

The goal was to shift the Account Executive (AE) and Solution Architect (SA) teams from a reactive to a proactive engagement model. Instead of waiting for a customer to complain or churn, we wanted to predict their "health" score in advance, allowing the team to intervene early, reduce churn, and identify upsell opportunities.

# Problem Statement

* **Lagging Indicators**: Traditional metrics like support tickets or NPS are lagging indicators. By the time they are bad, the customer is already at risk.
* **Manual Analysis**: AEs were manually trawling through dashboards to guess which customers needed attention.
* **Unknown Predictors**: It was unclear if our telemetry data actually contained identifying signals for churn or expansion.

# Methodology

I conducted a part-time pilot to assess the feasibility of automated health scoring:

* **Data Exploration**: Leveraged the existing **Google BigQuery** data warehouse to analyze historical usage logs, billing data, and CRM records.
* **Feature Engineering**: Used **Python** and **PyPika** to programmatically build complex SQL queries, extracted behavioral features (e.g., "active users trend", "feature adoption rate") from raw event logs.
* **Correlation Analysis**: Modeled relationships between these usage features and historical NRR/churn events.

# Historical pilot flow

The solid path shows what the pilot did. The dashed path shows the intended decision support, which the available
evidence could not justify.

```mermaid
flowchart LR
subgraph data["Existing private customer data"]
Usage["Usage logs"] --> Warehouse[("Google BigQuery")]
Billing["Billing records"] --> Warehouse
CRM["CRM records"] --> Warehouse
end

subgraph pilot["Historical pilot analysis"]
Warehouse -->|"Python and PyPika queries"| Features["Account features<br/>Active-user trend and<br/>feature-adoption rate"]
Warehouse -->|"Historical NRR and churn labels"| Analysis["Correlation analysis"]
Features --> Analysis
end

Analysis -->|"Weak signal and sparse data"| Stop["Pilot decision<br/>Do not build the forecasting platform<br/>Improve data collection first"]
Analysis -.->|"Intended, not achieved"| Forecast["Reliable account-health forecast"]
Forecast -.->|"Would have informed"| Review["AE and SA account review<br/>Intervene, retain, or expand"]

classDef intended stroke-dasharray:5 5
class Forecast,Review intended
```

## Reading the flow

1. Existing usage logs, billing records, and CRM records supplied private customer signals through the BigQuery
   warehouse.
2. The pilot used Python and PyPika queries to derive account-level features. The surviving summary names active-user
   trend and feature-adoption rate as examples.
3. Correlation analysis compared those features with historical NRR and churn labels. Sparse data and weak usage
   signals prevented a reliable account-health forecast.
4. A reliable forecast would have helped AEs and SAs decide where to intervene, protect retention, or explore an
   expansion. That dashed route was the aim, not an operational system. The evidence instead supported the solid route:
   stop the platform build and improve data collection first.

## Ownership, decision authority, and disclosure

Existing company systems supplied the source data and BigQuery warehouse. The pilot owned the exploratory feature and
correlation work. AEs and SAs would have retained authority over account actions; the proposed score would have informed
their decisions, not automated an action. The surviving material does not name source-system owners, access controls,
or data retention rules, so this view does not infer them. It exposes no customer records, identifiers, field names, or
values.

## Reconstruction limits

This repository preserves a project summary, not the working analysis. It contains no source schemas, queries,
notebooks, model definition, thresholds, sample sizes, validation split, or accuracy and calibration results. No
evidence shows that a forecast service or account-team interface reached production. The diagram stays at the labelled
data-flow level and marks the forecast and account-review path as intended rather than implemented.

# Outcome

The project was ultimately concluded after the pilot phase:

* **Weak Signals**: We discovered that purely usage-based metrics had a low correlation with enterprise buying decisions, which are often driven by external factors (budget, champions) not captured in telemetry.
* **Data Sparsity**: The available data was too sparse to build a reliable forecasting model with high confidence.
* **Strategic Pivot**: The investigation saved the company from investing in a costly "Customer Health" platform build-out, redirecting focus towards improving data collection fidelity first.

---

Markdown index of this site: https://robbiepalmer.me/llms.txt
