---
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.

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

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

## Known Tables & Columns

### `curated.pos_transactions`
- check (integer) - order identifier  
- dob (date) - transaction date  
- amount (decimal) - total check value  

### `curated.pos_transaction_tenders`
- check (integer) - order identifier  
- tender type - payment method used  

### `curated.pos_orders`
- customer linkage surface — use `customer_number` to associate transactions with identifiable customers

### `curated.loyalty_customers`
- customer identity surface — join `curated.pos_orders.customer_number` to `curated.loyalty_customers.customer_number`

## Analysis Approach

### Stage 1 — Customer Activity Aggregation
Extract transaction data from the last **90 days** using `curated.pos_transactions`.

Calculate per-customer metrics including:
- total visits
- average spend per visit
- 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 the 90-day window.

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

- **High risk**
  - no recent visits within a 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_transaction_tenders`.

Use `curated.pos_orders.customer_number` joined to `curated.loyalty_customers.customer_number` where the prompt needs customer-level churn classification.

## Output Constraints

- Use historical data from the **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.