---
id: 1b7e4c92-5d3a-4f8b-9c6e-2a9d7f3c5e11
name: Identify high-value customers
kind: CustomerInsights
cron: 0 20 7 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Identify **high-value customers** to support retention strategies, targeted marketing, and revenue optimization.

The process should use simple, reliable metrics to detect customers who contribute the most value based on spending and visit behavior.

The output must contain **high-value customer data 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 columns such as `type`, `typeid`, `name`, `ident`, or equivalent payment descriptors - use them to infer payment-method preferences when present  

### `curated.pos_transaction_items`
- item (integer) - menu item identifier  
- bohname (varchar) - menu item name  
- item-level columns such as `item`, `bohname`, `quantity`, `price`, or equivalent item descriptors - use them for lightweight preference signals when present  

### `curated.pos_orders`
- customer linkage surface — use `customer_number` to associate transactions with identifiable customers when that column is present and matches the customer identity surface

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

## Analysis Approach

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

If customer-level aggregation or enrichment needs multiple datasets, first inspect the available `curated` tables and columns, then join only on shared identifiers that are explicitly present in both tables.
- Look for repeated business keys such as transaction identifiers, customer identifiers, order identifiers, or item identifiers that appear across the discovered `curated` surfaces.
- Use the `curated.pos_orders.customer_number -> curated.loyalty_customers.customer_number` path only after confirming that both columns exist and represent the same customer identity surface.
- Treat similarly named fields as join candidates only after confirming that their semantics line up in both tables.

Calculate per-customer metrics including:
- total spend
- number of visits
- average spend per visit
- last visit date.

### Stage 2 — High-Value Identification
Classify customers as **high-value** using simple thresholds such as:
- top customers by total spend
- customers with high average check value
- customers with frequent visits.

Avoid complex scoring models.

### Stage 3 — Basic Profiling
Attach lightweight attributes such as:
- dominant payment method from joinable `curated` tender surfaces such as `curated.pos_transaction_tenders`
- general purchase tendencies from joinable `curated` item surfaces such as `curated.pos_transaction_items` (optional, minimal processing).

Use `curated.pos_orders.customer_number` joined to `curated.loyalty_customers.customer_number` when the prompt needs to identify repeat customers.

If no reliable join path or supporting fields are available in the visible `curated` columns, continue with the narrower high-value customer classification that can be supported by the confirmed `curated` surfaces instead of assuming legacy source-system joins.

## 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 scoring logic.
- Return **high-value customer data only**.
- **Do not include explanations, reasoning, or methodology**.

## Output Format

Return structured high-value customer records.