---
id: e2a9c4f7-6b3d-4d8a-9f21-5c7e1a2b8d66
name: Predict customer churn risk
kind: CustomerInsights
cron: 0 25 7 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Predict **customer churn risk** to support retention strategies and proactive customer engagement.

Process should analyze recent customer activity and identify **customers at risk of stopping visits** based on simple, reliable behavioral signals.

Output must contain **churn risk classifications only**, without explanations or descriptions of methodology.

## Known Tables & Columns

### `curated.pos_orders`
- `order_id` (varchar) - order identifier
- `customer_number` (varchar) - customer linkage surface for identifiable customers
- `created_at` (timestamp) - local order timestamp
- `business_date` (date) - business trading date
- `total` (decimal) - order value
- `payment_method` (varchar) - payment method when populated

### `curated.customers` (optional enrichment)
- `external_id` (varchar) - possible customer identifier to compare with `curated.pos_orders.customer_number`
- `name` (varchar) - customer name when populated
- `email` (varchar) - customer email when populated
- `phone` (varchar) - customer phone when populated

## Analysis Approach

### Stage 1 - Customer Activity Aggregation
Extract identifiable order data from last **90 days** using `curated.pos_orders` where `customer_number` is populated.

Calculate per-customer metrics including:
- total visits
- average spend per visit
- total spend
- days since last visit.

### Stage 2 - Churn Signal Detection
Identify churn indicators such as:
- declining visit frequency over time
- increasing gaps between visits
- decreasing spend compared to earlier activity within 90-day window.

### Stage 3 - Risk Classification
Classify customers into churn risk levels using simple rules:

- **High risk**
  - no recent visits within defined recent period
  - clear drop in visit frequency and spend

- **Medium risk**
  - reduced visit frequency or increased gaps between visits

- **Low risk**
  - consistent recent activity and stable spending.

Avoid complex predictive models.

### Stage 4 - Basic Profiling
Optionally attach lightweight attributes such as:
- dominant payment method from `curated.pos_orders.payment_method`.

Use `curated.customers` only for optional enrichment after confirming `curated.customers.external_id` matches `curated.pos_orders.customer_number` for current tenant data.

## Output Constraints

- Use historical data from **last 90 days** only.
- Prefer aggregated summaries.
- Avoid requesting more than **2-3 database queries**.
- Avoid complex joins and predictive models.
- Return **churn risk classifications only**.
- **Do not include explanations, reasoning, or methodology**.

## Output Format

Return structured churn risk records.