Repository Docs & Knowledge Base

id: 9c2e7b54-6f31-4a8d-b2c7-3d9f5e1a6b72 name: Create detailed customer profiles kind: CustomerInsights cron: 0 15 7 * * * analysis_window: 90d comparison_periods: [7d, 28d]

Create detailed customer profiles to support targeted marketing, personalization, and behavioral analysis.

The process should analyze transaction and behavioral data to construct structured customer profiles capturing visit habits, spending patterns, menu preferences, party size tendencies, and payment behavior.

The output must contain customer profile 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
• time-related columns such as dob, openhour, openmin, orderhour, ordermin, closehour, or closemin - use them to infer visit patterns and dining periods when present

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 to detect item preferences when present

curated.pos_tables

• table and seating-related columns such as id, lookupname, desc, revcenter, or numseats - use them to infer party size tendencies and dining context when joinable

curated.pos_orders

• order-level linkage columns such as order_id, customer_number, table_number, created_at, payment_method, or equivalent order descriptors

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.

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 table-related 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 visits
• total spend
• average spend per visit
• typical visit times.

Stage 2 — Behavioral Attribute Extraction

Derive behavioral attributes including:

• Visit frequency classification
• daily
• weekly
• monthly
• occasional.

• Spending tier classification
• low
• medium
• high
• very high.

• Dining pattern detection based on transaction and time-related columns such as dob, openhour, openmin, orderhour, ordermin, closehour, or closemin
• lunch
• dinner
• late night.

Stage 3 — Preference Analysis

Analyze preference signals using related tables:

• Menu preferences derived from recurring purchases in joinable curated item surfaces such as curated.pos_transaction_items
• Party size tendencies inferred from joinable curated table or seating surfaces such as curated.pos_tables
• Preferred payment methods derived from joinable curated tender surfaces such as curated.pos_transaction_tenders.

Use curated.pos_orders.customer_number joined to curated.loyalty_customers.customer_number where per-customer or per-visit aggregation is needed.

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

Stage 4 — Profile Construction

Combine behavioral metrics and preference signals into structured customer profiles including:

• visit frequency
• spending tier
• dominant dining pattern
• menu preference tendencies
• typical party size
• preferred payment methods.

Output Constraints

• Use historical data from the last 90 days only.
• Prefer aggregated summaries.
• Avoid requesting more than 3 database queries unless necessary.
• Return structured customer profiles only.
• Do not include explanations, reasoning, or methodology.

Output Format

Return structured customer profiles.