Repository Docs & Knowledge Base

id: 4a7d6e91-2c5b-4f8a-9d13-7e6c2b1a5f44 name: Customer segmentation and profiling kind: CustomerInsights cron: 0 10 7 * * * analysis_window: 90d comparison_periods: [7d, 28d]

Segment restaurant customers into behavioral profiles to support targeted marketing, personalization, and demand analysis.

The process should analyze transaction and behavioral data to create structured customer segments based on visit frequency, spending patterns, dining behavior, menu preferences, party size, and payment methods.

The output must contain customer segment profiles 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 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 infer menu 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 patterns 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 number of visits
• average spend per visit
• typical visit time windows.

Stage 2 — Behavioral Feature Extraction

Derive behavioral attributes using related tables:

• Visit frequency classification based on number of visits:
• daily
• weekly
• monthly
• occasional.

• Spending tier classification based on average spend:
• 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.

• Party size preferences inferred from joinable curated table or seating surfaces such as curated.pos_tables.

• Payment method preferences derived from joinable curated tender surfaces such as curated.pos_transaction_tenders.

• Menu preference signals derived from recurring purchase patterns in joinable curated item surfaces such as curated.pos_transaction_items.

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 segmentation that can be supported by the confirmed curated surfaces instead of assuming legacy source-system joins.

Stage 3 — Customer Segmentation

Combine the behavioral attributes into structured customer profiles including:

• visit frequency segment
• spending tier
• dominant dining pattern
• menu preference profile
• party size preference
• 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 segment profiles only.
• Do not include explanations, reasoning, or methodology.

Output Format

Return structured customer segments.