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

Process should analyze order and item behavior to construct **structured customer profiles** capturing visit habits, spending patterns, menu preferences, dining context, and payment behavior.

Output must contain **customer profile data 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 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 `created_at`, `service_channel`, and `fulfillment_channel`
  - lunch
  - dinner
  - late night.

### Stage 3 - Preference Analysis
Analyze preference signals using current order and item surfaces:

- **Menu preferences** derived from recurring purchases in `curated.pos_order_items`, optionally normalized with `curated.pos_menu_items.category_name`
- **Service-context tendencies** inferred from `service_channel`, `fulfillment_channel`, and `table_number`
- **Preferred payment methods** derived from `curated.pos_orders.payment_method`.

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 4 - Profile Construction
Combine behavioral metrics and preference signals into structured **customer profiles** including:

- visit frequency
- spending tier
- dominant dining pattern
- menu preference tendencies
- service-context tendency
- preferred payment methods.

## 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 profiles only**.
- **Do not include explanations, reasoning, or methodology**.

## Output Format

Return structured customer profiles.