Repository Docs & Knowledge Base

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.