Customer Data Requirements

1. Purpose

Hang's offer-matching pipeline selects an eligible audience of loyalty members, builds a purchase-behavior profile for each member, and uses an AI model to generate and then assign each member a personalized bundle of offers from your offer catalog. After launch, redemption outcomes feed back into the next cycle.

This document lists the data we need shared from your data warehouse account. Your schema does not need to match ours; we map and transform on our side. Treat the entity and field names below as concepts, and tell us which of your tables and columns correspond to each.

What we do not need: no personally identifiable information. No names, emails, phone numbers, street addresses, payment data, or device identifiers. A stable, opaque loyalty member ID is the only identifier we use.

2. Data model at a glance

LOYALTY MEMBERS ──< ORDERS ──< ORDER LINE ITEMS
      │               │
      │               ├──< APPLIED DISCOUNTS / REDEMPTIONS ──> OFFER DEFINITIONS
      │               │
      │               └──> STORES
      │
      └──< ISSUED OFFERS ──> OFFER DEFINITIONS

Priority legend used throughout:

Priority	Meaning
Required	The pipeline cannot run without it
Recommended	Materially improves audience selection or matching quality; we degrade gracefully without it
Optional	Used for analytics, QA, or reporting; not used for matching

3. Entities and fields

3.1 Loyalty Members

One row per loyalty account. Include all active members, not only purchasers. New sign-ups with no purchase yet are part of the audience.

Field	Type	Priority	Description and how we use it
`member_id`	string	Required	Stable, opaque loyalty ID. Primary key; joins to every other entity. Treated case-insensitively.
`country_code`	string (ISO-2)	Required	Member's market of record (e.g. US, CA). Drives market eligibility, catalog selection, and stratified test/control splits.
`account_created_at`	timestamp (UTC)	Required	Sign-up date. Admits brand-new members with no purchase and drives new-customer scoring.
`is_deleted` / `is_active`	boolean	Required	Excludes closed or deleted accounts. Hard-deleting the row also works; tell us which.
`offer_eligible` / `marketing_opt_in`	boolean	Recommended	Any flag meaning "do not target this member." Honored as a hard exclusion.
`updated_at`	timestamp (UTC)	Recommended	Change watermark for incremental refresh.
`email_opt_in`	boolean	Recommended if applicable	Consent to marketing email. Gates email delivery of offer notifications and sizes the reachable audience per channel.
`sms_opt_in`	boolean	Recommended if applicable	Consent to marketing SMS. Same use as above for text.
`push_opt_in`	boolean	Recommended if applicable	Consent to app push notifications. Same use as above for push.
`opt_in_updated_at`	timestamp (UTC)	Optional	When consent last changed, per channel if available. Lets us honor recent opt-outs before a send.
`points_balance`	number	Optional	Current loyalty points balance.

3.2 Orders (transaction headers)

One row per order or check. Guest (non-member) orders may be included; they are ignored for matching but useful for store-level baselines.

Field	Type	Priority	Description and how we use it
`order_id`	string	Required	Primary key.
`member_id`	string, nullable	Required	FK to Loyalty Members. Null for guest orders.
`ordered_at`	timestamp	Required	When the order was placed. UTC preferred; if store-local, say so. Drives recency, frequency, tenure, and daypart.
`store_id`	string	Required	FK to Stores. Drives location, timezone, market/DMA, and store-level opt-out rules.
`subtotal`	decimal	Required	Post-discount, pre-tax, pre-tip order total. Authoritative spend figure. State the unit (dollars vs. cents) and keep it consistent across all rows.
`status`	string	Required	Order lifecycle status. We need to know which values mean completed vs. voided, refunded, or pending; the latter are excluded.
`channel`	string	Required	Ordering channel (in-store POS, app, web, third-party delivery, etc.). Splits in-store vs. online behavior and routes members to the correct offer set. Must also identify non-commerce ledger rows (support adjustments, point grants, balance syncs, sign-up rows) so we can exclude them from purchase metrics.
`fulfillment_type`	string	Required	Delivery, pickup/takeout, dine-in, drive-thru. Rendered to the model as service-mode preference.
`country_code`	string (ISO-2)	Required	Market the order belongs to.
`is_deleted`	boolean	Required	Soft-delete flag (or hard delete).
`updated_at`	timestamp (UTC)	Recommended	Incremental watermark; also captures late status changes such as voids.
`total_discount`	decimal	Recommended	Sum of discounts applied. If absent, derived from Applied Discounts (3.4).
`receipt_number` / `short_id`	string	Optional	Human-readable receipt or check number. Used only for drill-down and reconciliation with your own reports.
`points_earned`, `points_redeemed`	number	Optional	Loyalty accrual on the order.
`tax`, `tip`, `total_paid`	decimal	Optional	Not used for matching.

3.3 Order Line Items

One row per line on the receipt. Modifiers (add-ons, size, bread choice) may be included as child lines.

Field	Type	Priority	Description and how we use it
`line_id`	string	Required	Primary key (unique within order at minimum).
`order_id`	string	Required	FK to Orders.
`item_name`	string	Required	Menu item name at time of sale. Drives favorite items, recent-order rendering, and category inference.
`line_amount`	decimal	Required	Extended line price (quantity already applied) or unit price. Tell us which, and the unit.
`quantity`	number	Required	Units on the line.
`is_deleted`	boolean	Required	Soft-delete flag.
`item_code` / `plu` / `sku`	string	Recommended	Stable menu item identifier. Resolves blank names, joins to a menu master, and joins to item cost data (3.9).
`category`	string	Recommended	Menu category or product type (e.g. Sub, Side, Drink, Combo). Rendered as top categories.
`parent_line_id`	string, nullable	Recommended	For modifier rows, the parent item line. Null = top-level item. Lets us count and render real menu items rather than condiments.
`created_at`, `updated_at`	timestamp (UTC)	Recommended	Incremental watermark.
`line_index`	integer	Optional	Display order on the receipt.
`product_id`	string	Optional	Internal product key if different from PLU.

3.4 Applied Discounts / Redemptions

One row per offer, reward, or promotion applied to an order. This is how we measure redemption and how the model learns from past outcomes.

Field	Type	Priority	Description and how we use it
`order_id`	string	Required	FK to Orders.
`offer_instance_id`	string	Required	ID of the issued offer or reward applied. Joins to Issued Offers (3.6) or to a reward/promotion definition (3.7).
`discount_type`	string	Required	Targeted offer, points reward, tier reward, systemwide promotion, etc. Only targeted offers count toward offer affinity and redemption history.
`status`	string	Required	Applied/claimed vs. voided/pending. Only successful applications count as redemptions.
`savings_amount`	decimal	Required	Monetary value of the discount on this order. State the unit. Used to compute pre-discount spend (for offer thresholds) and offer economics.
`member_id`	string	Recommended	Redeeming member; should match the order's member.
`line_id`	string	Optional	Line the discount applied to, if item-level.
`product_id`, `product_name`	string	Optional	Item the discount targeted.
`source`	string	Optional	System that applied the discount (e.g. loyalty engine, POS, third-party).
`promotion_subtype`	string	Optional	Finer-grained classification if your platform has one (e.g. systemwide BOGO vs. targeted offer). Lets us identify brand-wide promotions that reach every member so they don't distort measured lift.
`applied_at`	timestamp (UTC)	Optional	If different from order time.

3.5 Stores / Locations

One row per restaurant.

Field	Type	Priority	Description and how we use it
`store_id`	string	Required	Primary key; matches `Orders.store_id`.
`store_name`	string	Required	Display name rendered to the model as the member's home location.
`timezone`	string (IANA)	Required	e.g. `America/New_York`. Expresses order times in store-local time (daypart) and aligns campaign windows. If unavailable, provide state/province and postal code and we will derive it.
`country_code`	string (ISO-2)	Required	Market.
`offers_opted_out`	boolean	Required if applicable	Franchisee or store opted out of offer participation. Members whose recent or majority purchases are at opted-out stores are excluded or down-weighted. May be delivered as a separate list of `store_id`s.
`dma_name`, `dma_id` (or region)	string	Recommended	Designated Market Area or equivalent grouping. Used for geographic stratification and reporting; falls back to country.
`status`	string	Recommended	Open, closed, temporarily closed.
`limited_offer_menu` / `price_tier`	boolean or string	Optional	Stores with restricted offer availability or a different price tier. Used as a soft penalty.
`opened_date`, `closed_date`	date	Optional
`location_type`, `restaurant_type`	string	Optional	e.g. traditional, airport, university.
`does_delivery`, `drive_thru_type`, `has_late_night_menu`	boolean or string	Optional	Store capabilities.
`city`, `state`, `postal_code`, `latitude`, `longitude`	string or decimal	Optional	Not rendered to the model; used for timezone derivation and QA.

3.6 Issued Offers

One row per offer instance issued to a member (not the catalog template). Needed to attribute redemptions to campaigns and to build each member's offer history. Include offers from your own past campaigns, not only Hang's.

Field	Type	Priority	Description and how we use it
`offer_instance_id`	string	Required	Primary key. The ID that appears in Applied Discounts when redeemed.
`offer_definition_id`	string	Required	Parent/template offer this instance was created from (3.7).
`member_id`	string	Required	Member it was issued to.
`offer_name`	string	Required	Display name at issuance.
`issued_at`	timestamp	Required	When it was issued / became visible. State the clock (see §4).
`valid_from`, `valid_to`	timestamp	Required	Redemption window. State the clock (see §4).
`slot` / `rank`	integer	Recommended	Position in a multi-offer bundle (1, 2, 3, …).
`campaign_id` / `source`	string	Recommended	Which campaign or system issued it; separates Hang-issued offers from others.
`country_code`	string	Recommended
`created_at`, `updated_at`	timestamp (UTC)	Recommended	If instances can be rewritten, we keep the latest `updated_at` per ID.

3.7 Offer / Reward / Promotion Definitions (catalog)

One row per offer template, points reward, or systemwide promotion. Used to label discounts and to describe the candidate offers the model chooses among. These fields may alternatively come from your marketing or loyalty-platform team as a spreadsheet.

Field	Type	Priority	Description and how we use it
`offer_definition_id`	string	Required	Primary key; matches `Issued Offers.offer_definition_id`.
`name`	string	Required	Display name.
`type`	string	Required	Targeted offer, points reward, tier reward, systemwide promotion.
`description` / `details`	string	Recommended	Customer-facing copy.
`discount_mechanic`	string	Recommended	Percent off, amount off, free item, BOGO, price point, bundle.
`discount_value`	decimal	Recommended	Value of the mechanic (e.g. 20 for 20%, 5.00 for $5 off).
`max_discount`	decimal	Recommended	Cap on discount value.
`minimum_spend`	decimal	Recommended	Pre-discount spend threshold to qualify.
`applicable_items`, `required_items`	string or array	Recommended	Items the discount applies to; items required in cart.
`service_modes`	string or array	Recommended	Where redeemable (e.g. app/web only, pickup only). Critical if offers are digital-only.
`daypart_window`	string	Recommended	Time-of-day restriction on redemption (e.g. lunch only, after 5pm). The model matches this against each member's order-hour patterns, so it should be explicit rather than buried in description copy.
`redemption_limit`	string	Recommended	e.g. once per week, once per campaign.
`valid_from`, `valid_to`	timestamp	Recommended	Template validity.
`country_code`	string	Recommended	Market availability.
`cart_exclusions`	string or array	Optional	Items that disqualify.
`point_cost`	number	Optional	For points-based rewards.
`is_first_order_only`	boolean	Optional
`external_ids`	json or string	Optional	POS / CMS identifiers.

3.8 Reference data

Dataset	Priority	Description
Store opt-out list	Required if applicable	`store_id`s excluded from offer campaigns, if not a column on Stores.
Item code → item name / category	Recommended	Menu master mapping PLU/SKU to canonical name and category, if line items sometimes lack names or use inconsistent names.
Item cost (food & paper / COGS)	Optional	Unit cost per item (and size). Enables margin-aware matching and true-contribution reporting. A quarterly snapshot is sufficient. Schema in 3.9.
Loyalty program configuration	Optional	Points-per-dollar, expiration rules. Only needed if discount value must be derived from points.

3.9 Item Cost / COGS (optional)

One row per menu item (and per size or variant where cost differs) with its unit cost of goods: food plus paper and packaging, or your finance team's closest equivalent. Once shared, it lets us weigh candidate offers by contribution rather than revenue alone (a free side and $3 off can redeem at the same rate with very different margin) and report incremental gross profit per campaign, not just incremental sales. Include add-on and modifier items that carry a cost, since free-add-on offers are common. We do not need recipes, ingredient-level costs, supplier pricing, or labor. A quarterly snapshot is sufficient. Fields marked Required are required only if this table is shared at all.

Field	Type	Priority	Description and how we use it
`item_code` / `plu` / `sku`	string	Required if shared	Menu item identifier. Must match `Order Line Items.item_code` (3.3) or the menu master (3.8); this is the join that attaches cost to sales.
`unit_cost`	decimal	Required if shared	Cost of goods per unit sold, food plus paper. State the unit (dollars vs. cents) and keep it consistent with your other money columns (§4). If you track percent rather than dollars, provide `cost_pct` instead.
`item_name`	string	Recommended	Canonical name. Used to QA the join and as a fallback match when codes are missing.
`size` / `variant`	string, nullable	Recommended	Size or variant the cost applies to (e.g. 6-inch vs. footlong, small vs. large), if the item code does not already encode it. Null when one cost applies to all sizes.
`effective_from`, `effective_to`	date	Recommended	Period the cost applies to. Lets us attach the cost in effect at time of sale rather than today's cost. Null `effective_to` = current. If you only keep the current figure, that is fine; tell us the as-of date.
`country_code` / `region`	string	Recommended	Market or cost region if costs vary by geography. Falls back to a single national figure.
`food_cost`, `paper_cost`	decimal	Optional	Component breakdown if tracked separately. `unit_cost` is what we use; the split is for reporting only.
`cost_pct`	decimal	Optional	Cost as a share of menu price (e.g. 0.31 for 31%), if you track percent rather than dollars. Acceptable in place of `unit_cost`; we apply it to realized line price.
`cost_basis`	string	Optional	Theoretical (recipe/ideal) vs. actual (inventory-derived). Theoretical is preferred for consistency across periods.
`store_id` / `price_tier`	string, nullable	Optional	Only if you already maintain cost per store or per cost tier. No need to build this for us; national or regional is sufficient.
`currency`	string (ISO-4217)	Optional	Only if multiple markets share one table.
`updated_at`	timestamp (UTC)	Optional	Incremental watermark.

3.10 Marketing Sends (optional)

One row per message delivered to a member: email, SMS, or push. Once shared, it lets us measure which channel and send time actually reach each member, separate message lift from offer lift, and eventually personalize channel and timing as well as the offer itself. Fields marked Required are required only if this table is shared at all.

Field	Type	Priority	Description and how we use it
`send_id`	string	Required if shared	Primary key for the delivered message.
`member_id`	string	Required if shared	Recipient. FK to Loyalty Members.
`channel`	string	Required if shared	email, sms, push, in-app.
`sent_at`	timestamp (UTC)	Required if shared	When the message was sent. Drives send-time analysis by daypart and weekday.
`campaign_id`	string	Required if shared	Campaign or journey the message belongs to. Joins to Issued Offers when the message carried an offer.
`message_type`	string	Recommended	Offer notification, promotional, transactional, loyalty status, reminder. Only marketing sends inform matching.
`delivery_status`	string	Recommended	Delivered, bounced, failed, suppressed. Only delivered messages count as exposures.
`opened_at`	timestamp (UTC), nullable	Recommended	First open. Email and push only; note whether privacy-protected opens are included.
`clicked_at`	timestamp (UTC), nullable	Recommended	First click or tap-through.
`offer_instance_id`	string, nullable	Recommended	The issued offer featured in the message, if any. Ties exposure to redemption.
`template_id` / `subject`	string	Optional	Creative identifier or subject line, for creative-level reporting.
`unsubscribed_at` / `complained_at`	timestamp (UTC), nullable	Optional	Negative signals on this send. Used as a fatigue guardrail.
`converted_at`, `order_id`	timestamp / string, nullable	Optional	Your platform's own conversion attribution, if it exists. We reconcile against it rather than replace it.
`source_system`	string	Optional	ESP or engagement platform that sent it (e.g. Braze, Iterable, Salesforce MC).

3.11 Message Engagement Events (optional)

If your platform stores engagement as an event stream rather than columns on the send, share it in this shape instead of, or in addition to, the open and click columns above.

Field	Type	Priority	Description and how we use it
`event_id`	string	Required if shared	Primary key.
`send_id`	string	Required if shared	FK to Marketing Sends.
`event_type`	string	Required if shared	delivered, opened, clicked, bounced, unsubscribed, complained, converted.
`event_at`	timestamp (UTC)	Required if shared	When the event occurred.
`link_url` / `link_id`	string	Optional	For click events, what was clicked.
`device` / `client`	string	Optional	Device or mail client, for QA of open reliability.

4. Semantics to confirm

Please answer these in writing alongside the share. Each has caused reconciliation work on prior integrations.

Units. For every money column: dollars or cents? Decimal or integer? A single column must never mix units across rows (e.g. in-store rows in dollars and online rows in cents).
Line amount basis. Is line_amount already multiplied by quantity, or per unit?
Timestamps. For each table: UTC or local? If local, whose clock: store, market, or account? Timezone-aware type or naive? Please answer table by table; a mix (e.g. offer windows on the market clock, orders in UTC) is workable but must be declared.
Status vocabularies. All valid values for order status, discount status, and offer status, and which mean completed/successful.
Channel vocabulary. All channel values; which are in-store vs. online; which are non-commerce ledger rows that should be excluded from purchase metrics.
Fulfillment vocabulary. All values and which map to delivery, pickup, dine-in.
Deletes and updates. Soft-delete flag vs. hard delete; whether rows are updated in place and whether updated_at is reliable.
Duplicates. Whether any table can have more than one row per primary key (e.g. a template row and an issued copy sharing an offer ID) and the rule to pick the canonical row.
Member ID format. Case-sensitive? UUID vs. numeric? Identical format in every table?
Guest orders. Are non-member orders present, and how are they flagged?

5. History depth and refresh

Dataset	Minimum history	Preferred	Refresh
Orders, Line Items, Applied Discounts	15 months	24+ months	Daily (hourly ideal)
Marketing Sends, Engagement Events (if shared)	12 months	24+ months	Daily
Item Cost (if shared)	Current snapshot	Effective-dated history covering the order window	Quarterly
Loyalty Members	All current	Closed accounts included and flagged	Daily
Issued Offers	12 months	All	Daily
Stores, Definitions, Reference	Current snapshot	Change history	On change

Audience selection looks back roughly 15 months for lapsed-member reactivation; profile features are lifetime; the model sees each member's five most recent orders with line items.

6. Delivery

Mechanism: Snowflake Secure Data Share or Private Listing to Hang's account (we will provide the account identifier and region). If your account is in a different cloud or region, a replication-enabled listing is required.
Object type: Tables or secure views are both acceptable. We materialize local copies on our side on a scheduled cadence.
Grants: SELECT on the shared objects only. No write access needed.
Change notification: Please notify us before schema changes (renamed or removed columns, new status values).
What we return: Per campaign, a list of (member_id, offer_definition_id, slot) assignments plus test/control/holdout membership, for your loyalty platform to issue.

7. Onboarding checklist

Map each entity in §3 to your table(s); flag any Required field you cannot supply

Answer the ten questions in §4

Confirm history depth and refresh cadence (§5)

Create the share or listing and grant Hang's account

Provide a data dictionary or DDL for the shared objects

Provide the offer catalog (§3.7) for the first campaign

Nominate a data contact for reconciliation questions