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