Repository Docs & Knowledge Base

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.