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

Process should analyze order and item behavior to create **structured customer segments** based on visit frequency, spending patterns, dining behavior, menu preferences, service context, and payment methods.

Output must contain **customer segment profiles only**, without explanations or descriptions of methodology.

## Known Tables & Columns

### `curated.pos_orders`
- `order_id` (varchar) - order identifier
- `customer_number` (varchar) - customer linkage surface for identifiable customers
- `created_at` (timestamp) - local order timestamp for visit-pattern analysis
- `business_date` (date) - business trading date
- `total` (decimal) - order value
- `table_number` (varchar) - table identifier when dine-in context is present
- `payment_method` (varchar) - payment method when populated
- `service_channel` (varchar) - dine-in, pickup, delivery, or similar channel label when populated
- `fulfillment_channel` (varchar) - downstream fulfillment grouping when populated

### `curated.pos_order_items`
- `order_id` (varchar) - join key to `curated.pos_orders.order_id`
- `item_id` (integer) - menu item identifier
- `item_name` (varchar) - sold item name
- `quantity` (integer) - sold quantity
- `category_name` (varchar) - sold item category when populated

### `curated.pos_menu_items` (optional metadata enrichment)
- `item_id` (integer) - menu item identifier
- `category_name` (varchar) - configured category

### `curated.customers` (optional enrichment)
- `external_id` (varchar) - possible customer identifier to compare with `curated.pos_orders.customer_number`
- `name` (varchar) - customer name when populated
- `email` (varchar) - customer email when populated
- `phone` (varchar) - customer phone when populated

## Analysis Approach

### Stage 1 - Customer Activity Aggregation
Extract identifiable order data from last **90 days** using `curated.pos_orders` where `customer_number` is populated.

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 current order and item surfaces:

- **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 `created_at`, `service_channel`, and `fulfillment_channel`:
  - lunch
  - dinner
  - late night.

- **Service-context tendency** inferred from `service_channel`, `fulfillment_channel`, and `table_number`.

- **Payment method preferences** derived from `curated.pos_orders.payment_method`.

- **Menu preference signals** derived from recurring purchase patterns in `curated.pos_order_items`, optionally normalized with `curated.pos_menu_items.category_name`.

Use `curated.customers` only for optional enrichment after confirming `curated.customers.external_id` matches `curated.pos_orders.customer_number` for current tenant data.

Do not infer precise party size unless runtime schema discovery confirms trustworthy guest-count data.

### Stage 3 - Customer Segmentation
Combine behavioral attributes into structured customer segments including:
- segment name
- visit frequency segment
- spending tier
- dominant dining pattern
- menu preference profile
- service-context tendency
- preferred payment methods
- percentage of customers
- percentage of revenue.

## Output Constraints

- Use historical data from **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.