---
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.