Data Shares Data Dictionary | Bikky Help Center

This data dictionary describes the tables and columns available in the data share. These tables contain customer order data, marketing events, and customer information that you can query using SQL.

Some important usage notes:

Date Filtering: Always use BUSINESS_DATE for order date filtering, not INGESTION_TIME or DUE_AT
Customer Analysis: Filter by IS_TRACKABLE = 1 when analyzing customer behavior to exclude untrackable customers
Case Sensitivity: Fields like FULFILLMENT_METHOD, LIFECYCLE_STAGE are always lowercase
Joins: Use PERSON_ID to join customer tables, ORDER_ID to join order tables, LOCATION_ID to join location tables
Arrays: Use array indexing (e.g., EMAILS[0]) or FLATTEN() function to extract values from array columns
Fiscal Periods: Use DUE_FISCAL_* fields for fiscal reporting, not calendar dates

There are some samples of common queries from this data available at the bottom of this data dictionary.

ORDERS

Description: Core order information including customer details, timing, order characteristics, and financial metrics. This is the primary table for analyzing revenue and order patterns.

How to use: Join to ORDER_ITEMS on ORDER_ID to get item-level details. Join to PERSONS on PERSON_ID for customer information. Join to LOCATIONS on LOCATION_ID for store details. Use BUSINESS_DATE for date-based filtering and grouping.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
ORDER_ID	TEXT	Primary key. Unique order identifier. Use for joins to ORDER_ITEMS	-
REFERENCE_ID	TEXT	External order reference identifier. May match order IDs from source systems, like your POS	-
PERSON_ID	TEXT	Foreign key. Unique customer identifier. Join to PERSONS table for customer details	-
IS_TRACKABLE	NUMBER	Whether customer can be tracked. Use `WHERE IS_TRACKABLE = 1` for all guest analysis	0, 1
IS_LOYALTY_MEMBER	NUMBER	Whether customer is loyalty member. Use for loyalty program analysis	0, 1
LIFECYCLE_STAGE	TEXT	The Bikky-defined lifecycle stage of the customer at the time of the order. Untrackable guests will not have a lifecycle stage. Always lowercase. Use for customer segmentation	onboarding, re-onboarding, first-time, engaged
IS_LOYALTY_MEMBER	NUMBER	A 0/1 flag to indicate if the customer who placed the order is a member of your loyalty program	0, 1
IS_CATERING	NUMBER	A 0/1 flag to indicate if the order was for catering	0, 1
LOCATION_ID	NUMBER	Foreign key. Unique Bikky-generated location/store identifier. Join to LOCATIONS table	8163, 2645, 6905
LOCATION_NAME	TEXT	The name of the location provided by the customer	Logan Square, 1111 - Downtown LA, JFK Airport
BUSINESS_DATE	DATE	Date when order was placed, taking into account business-specific date logic (e.g. orders placed before 2am should be counted with the previous day). Use for date filtering: `WHERE business_date >= '2024-01-01'`	2023-11-24, 2023-05-23
DUE_AT	TIMESTAMP_TZ	Timestamp when order is due (UTC). Use for precise timing analysis	-
LOCAL_DUE_AT	TIMESTAMP_TZ	Timestamp when order is due (local timezone). Use for timezone-aware analysis	-
DUE_FISCAL_PERIOD	NUMBER	Fiscal period when order is due. Use for fiscal period reporting	11, 2, 1
DUE_FISCAL_DAY	NUMBER	Fiscal day when order is due. Use for day-level fiscal analysis	-
DUE_FISCAL_WEEK	NUMBER	Fiscal week when order is due. Use for weekly fiscal reporting	6, 4, 45
DUE_FISCAL_QUARTER	NUMBER	Fiscal quarter when order is due. Use for quarterly fiscal reporting	1, 2, 3, 4
DUE_FISCAL_YEAR	NUMBER	Fiscal year when order is due. Use for year-over-year comparisons	2024, 2023, 2022
INGESTION_SOURCE	TEXT	Source system that provided the order data. Use for data quality checks	-
SOURCE	TEXT	Platform where order was placed. Filter by the system that received the order	Doordash, Uber Eats
FULFILLMENT_METHOD	TEXT	How the customer received their order. Always lowercase. Use for analysis of how the customer received their order	curbside, delivery, in-store, pickup, drive-thru
PLATFORM	TEXT	Ordering platform. May differ from SOURCE in some cases	-
TOTAL_USD	FLOAT	Total order amount in USD (after discounts, taxes, fees). Use for total revenue calculations	-
SUB_TOTAL_USD	FLOAT	Primary revenue metric. Order subtotal in USD before discounts and taxes. Use `SUM(SUB_TOTAL_USD)` for revenue	16.84, 31.94, 10.47
DISCOUNT_USD	FLOAT	Total discounts applied in USD. Use to calculate net revenue	-
TIP_USD	FLOAT	Tip amount in USD. Use for tip analysis	-
FEES_USD	FLOAT	Fees charged in USD. Use for fee analysis	-
CARD_EXTERNAL_ID	TEXT	External card identifier. Use for payment method analysis	-
CARD_LAST_FOUR_DIGITS	TEXT	Last four digits of payment card. Use for payment method identification	-
CARD_TYPE	TEXT	Type of payment card. Use for payment method analysis	-
CUSTOMER_EXTERNAL_ID	TEXT	External customer identifier from source system. Likely will not match PERSON_ID	-
CUSTOMER_FIRST_NAME	TEXT	Customer first name from order. May differ from PERSONS table	-
CUSTOMER_LAST_NAME	TEXT	Customer last name from order. May differ from PERSONS table	-
CUSTOMER_LAST_INITIAL	TEXT	Customer last name initial. Use for privacy-preserving analysis	-
CUSTOMER_PHONE	TEXT	Customer phone number from order. May differ from PERSONS table	-
CUSTOMER_EMAIL	TEXT	Customer email from order. May differ from PERSONS table	-
CUSTOMER_FULL_ADDRESS	TEXT	Full customer address. Use for delivery analysis	-
CUSTOMER_CITY	TEXT	Customer city. Use for geographic analysis	-
CUSTOMER_STATE	TEXT	Customer state. Use for geographic analysis	-
CUSTOMER_ZIPCODE	TEXT	Customer zip code. Use for geographic analysis	-
CUSTOMER_ORDER_INDEX	NUMBER	Order number for this customer (1st, 2nd, etc.). Use to identify first-time vs repeat orders	-
DAYPART	TEXT	Time of day when order was placed. Use for daypart analysis	breakfast, lunch, dinner
DAYPART_INDEX	NUMBER	Numeric index for daypart. Use for ordering dayparts	-
DAY_OF_WEEK_NAME	TEXT	Day of week when order was placed. Use for weekday analysis	Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
DAYS_SINCE_LAST_ORDER	NUMBER	Days since customer's last order. Use for customer retention analysis	64, 58
ENTREE_COUNT	NUMBER	Number of entrees ordered. Use for menu mix analysis	3, 1, 0
DAYS_UNTIL_NEXT_ORDER_DAY	NUMBER	The number of days until the next order placed by the customer, not including orders placed on the same day. This is useful for calculating N day retention rates	-
INGESTION_TIME	TIMESTAMP_TZ	When the order data was ingested. Use for data freshness checks	-

ORDER_ITEMS

Description: Individual items within each order including pricing, quantity, and item details. Use this table for menu item analysis, PMIX reports, and item-level revenue.

How to use: Join to ORDERS on ORDER_ID to get order-level context. Join to ORDER_ITEM_MODIFIERS on ORDER_ITEM_ID for customization details. Filter by IS_ENTREE = 1 to focus on main courses.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
ORDER_ID	TEXT	Foreign key. Unique order identifier. Join to ORDERS table	-
ORDER_ITEM_ID	TEXT	Primary key. Unique item identifier within order. Join to ORDER_ITEM_MODIFIERS	-
PERSON_ID	TEXT	Foreign key. Unique customer identifier. Join to PERSONS for customer context	-
LOCATION_ID	NUMBER	Foreign key. Unique location identifier. Join to LOCATIONS table	-
BUSINESS_DATE	DATE	Date when order was placed. Use for date filtering	-
ITEM	TEXT	Product/menu item name. Use for menu item analysis. Join to MENU_ITEMS on ITEM_NAME	-
ITEM_ID	TEXT	Item identifier. May match MENU_ITEM_ID	-
MENU_ITEM_ID	TEXT	Foreign key. Menu item identifier. Join to MENU_ITEMS table	-
MENU_NAME	TEXT	Name of the menu. Use if multiple menus exist	-
MENU_GROUP_ID	TEXT	Menu group identifier. Use for menu category analysis	-
PRICE	FLOAT	Item price (total for quantity). Use `SUM(PRICE)` for item revenue	2.5, 12, 28.75
PRICE_PER_UNIT_USD	FLOAT	Price per unit in USD. Use for unit price analysis	2.89, 0.4, 3.48
QUANTITY	NUMBER	Number of items ordered. Use `SUM(QUANTITY)` for units sold	4, 2, 1
IS_ENTREE	NUMBER	Whether item is an entree. Filter with `WHERE IS_ENTREE = 1` for main courses	0, 1
IS_CATERING	NUMBER	Whether order is for catering. Filter with `WHERE IS_CATERING = 0` to exclude catering	0, 1
ORDER_IS_CATERING	NUMBER	Whether parent order is for catering. Use for catering analysis	0, 1
LIFECYCLE_STAGE	TEXT	Customer lifecycle stage at time of order. Always lowercase. Use for customer segmentation	onboarding, re-onboarding, first-time, engaged
IS_LOYALTY_MEMBER	NUMBER	Whether customer is loyalty member. Use for loyalty program analysis	0, 1
IS_TRACKABLE	NUMBER	Whether customer can be tracked. Use `WHERE IS_TRACKABLE = 1` for all guest analysis	0, 1
PII_CLASS	TEXT	PII classification level. Use for privacy compliance	-
FULFILLMENT_METHOD	TEXT	How the order was fulfilled. Always lowercase	curbside, delivery, in-store, pickup
SOURCE	TEXT	Platform where order was placed. Join to ORDERS.SOURCE for consistency	-
DAYPART	TEXT	Time of day when order was placed. Use for daypart analysis	-
DAYPART_INDEX	NUMBER	Numeric index for daypart. Use for ordering dayparts	-
DUE_DATE	DATE	Date when order is due. Use for fulfillment timing	-
LOCAL_DUE_AT	TIMESTAMP_TZ	Timestamp when order is due (local). Use for precise timing	-
INGESTION_SOURCE	TEXT	Source system for data. Use for data quality	-
INGESTION_TIME	TIMESTAMP_TZ	When data was ingested. Use for freshness checks	-
CUSTOMER_REPEATED_WITHIN_90D	NUMBER	Whether customer reordered within 90 days. Use for retention analysis	0, 1
WAS_REORDERED_BY_CUSTOMER_WITHIN_90D	NUMBER	Whether this item was reordered within 90 days. Use for item reorder rate	0, 1
IS_LAST_ORDER_DAY_BEFORE_CHURN	NUMBER	Whether this was last order before churn. Use for churn analysis	0, 1
RAW_ITEM_ID	TEXT	Raw item identifier from source. Use for data mapping	-
MENU_ITEM_ATTRIBUTES	OBJECT	Additional item attributes (JSON). Parse JSON for extended attributes	-
DAYS_UNTIL_NEXT_ORDER_DAY	NUMBER	Same as in the ORDERS table. The number of days until the next order placed by the customer, not including orders placed on the same day. This is useful for calculating N day retention rates	-
DAYS_UNTIL_NEXT_ITEM_ORDER_DAY	NUMBER	Similar to DAYS_UNTIL_NEXT_ORDER_DAY, but counts the number of days until the next order by the customer that also contained the value in the ITEM column. Does not take into account item quantities or modifiers.	-

ORDER_ITEM_MODIFIERS

Description: Modifications and customizations made to order items (e.g., "no onions", "extra cheese"). Use this table to analyze customization patterns and modifier popularity.

How to use: Join to ORDER_ITEMS on ORDER_ITEM_ID to get item context. Join to ORDERS on ORDER_ID for order-level details. Filter by MODIFIER_ACTION to analyze specific modification types.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
ORDER_ITEM_MODIFIER_ID	TEXT	Primary key. Unique identifier for the modifier	-
ORDER_ITEM_ID	TEXT	Foreign key. Unique order item identifier. Join to ORDER_ITEMS table	-
ORDER_ID	TEXT	Foreign key. Unique order identifier. Join to ORDERS table	-
PERSON_ID	TEXT	Foreign key. Unique customer identifier. Join to PERSONS table	-
LOCATION_ID	NUMBER	Foreign key. Unique location identifier. Join to LOCATIONS table	-
MODIFIER_ID	TEXT	Modifier identifier. Use for modifier grouping	-
MODIFIER_NAME	TEXT	Name of the modifier being applied	Onions, Ketchup, Romaine
MODIFIER_ACTION	TEXT	Type of modification made, like "added", "removed", or "substituted"	Removed, Substituted, Added
COMBINED_MODIFIER_NAME_ACTION	TEXT	Combined modifier name and action. Use for complete modifier description	Onions Added, Ketchup Removed
MODIFIER_INDEX	NUMBER	Order of modifier within item. Use for modifier sequencing	-
PRICE	FLOAT	Price impact of modifier. Use for modifier revenue analysis	-
QUANTITY	NUMBER	Quantity of modifier. Use for modifier volume	-
ITEM	TEXT	Item name. Join to ORDER_ITEMS.ITEM	-
ITEM_ID	TEXT	Item identifier. Join to ORDER_ITEMS	-
BUSINESS_DATE	DATE	Date when order was placed. Use for date filtering	-
DUE_DATE	DATE	Date when order is due. Use for timing analysis	-
LOCAL_DUE_AT	TIMESTAMP_TZ	Timestamp when order is due (local). Use for precise timing	-
FULFILLMENT_METHOD	TEXT	How order was fulfilled. Always lowercase	curbside, delivery, in-store, pickup
DAYPART	TEXT	Time of day when order was placed. Use for daypart analysis	-
DAYPART_INDEX	NUMBER	Numeric index for daypart. Use for ordering	-
LIFECYCLE_STAGE	TEXT	Customer lifecycle stage. Always lowercase	onboarding, first-time, engaged
IS_LOYALTY_MEMBER	NUMBER	Whether customer is loyalty member. Use for loyalty analysis	0, 1
IS_TRACKABLE	NUMBER	Whether customer can be tracked. Use `WHERE IS_TRACKABLE = 1` for all guest analysis	0, 1
IS_CATERING	NUMBER	Whether order is for catering. Use for catering filtering	0, 1
ORDER_IS_CATERING	NUMBER	Whether parent order is for catering. Use for catering analysis	0, 1
PII_CLASS	TEXT	PII classification. Use for privacy compliance	-
IS_LAST_ORDER_DAY_BEFORE_CHURN	NUMBER	Whether last order before churn. Use for churn analysis	0, 1
LOCATION_NAME	TEXT	Location name. Use for location analysis	-
LOCATION_AGE_MONTHS	NUMBER	Age of location in months. Use for location maturity analysis	-
INGESTION_SOURCE	TEXT	Source system. Use for data quality	-
INGESTION_TIME	TIMESTAMP_TZ	When data was ingested. Use for freshness	-
SOURCE	TEXT	Order platform. Use for platform analysis	-
SOURCE_MODIFIER_ITEM_ID	TEXT	Source system modifier identifier. Use for data mapping	-
DAYS_UNTIL_NEXT_ORDER_DAY	NUMBER	Days until customer's next order. Use for retention analysis	-

PERSONS

Description: Customer information including contact details, order history, and customer lifetime metrics. Use this table for customer segmentation, lifetime value analysis, and customer behavior patterns.

How to use: Join to ORDERS on PERSON_ID to get order details. Filter by IS_TRACKABLE = 1 when analyzing customer behavior. Use aggregated fields (ORDERS, LIFETIME_VALUE_USD) for quick analysis without joins.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
PERSON_ID	TEXT	Primary key. Unique person/customer identifier. Join to ORDERS, ORDER_ITEMS	-
FULL_NAME	TEXT	Customer's full name. Use for customer identification	-
FIRST_NAME	TEXT	Customer's first name. Use for personalization	-
LAST_NAME	TEXT	Customer's last name. Use for customer identification	-
LAST_INITIAL	TEXT	Customer's last name initial. Use for privacy-preserving displays	-
NAME_OBJECTS	ARRAY	Array of name objects. Parse array for multiple name variations	-
PHONE_NUMBERS	ARRAY	Array of phone numbers. Parse array: `PHONE_NUMBERS[0]` for primary	-
EMAILS	ARRAY	Array of email addresses. Parse array: `EMAILS[0]` for primary	-
EXTERNAL_ID_OBJECTS	ARRAY	Array of external ID objects. Parse for source system IDs	-
EXTERNAL_CARD_IDS	ARRAY	Array of external card identifiers. Use for payment method analysis	-
CARD_OBJECTS	ARRAY	Array of card objects. Parse for payment card details	-
ADDRESS_OBJECTS	ARRAY	Array of address objects. Parse for customer addresses	-
LATEST_INGESTION_TIME	TIMESTAMP_TZ	Most recent data ingestion time. Use for data freshness	-
FIRST_EVENT_DATE	DATE	Date of customer's first event. Use for customer acquisition analysis	-
LAST_EVENT_DATE	DATE	Date of customer's last event. Use for recency analysis	-
FIRST_EVENT_OCCURRED_AT	TIMESTAMP_TZ	Timestamp of first event. Use for precise timing	-
LAST_EVENT_OCCURRED_AT	TIMESTAMP_TZ	Timestamp of last event. Use for precise timing	-
ORDERS	NUMBER	Pre-aggregated. Total orders placed by person. Use for quick analysis	-
CATERING_ORDERS	NUMBER	Total catering orders. Use for catering customer analysis	-
FIRST_ORDER_TIME	TIMESTAMP_TZ	Timestamp of first order. Use for customer acquisition	-
LAST_ORDER_TIME	TIMESTAMP_TZ	Timestamp of last order. Use for recency	-
FIRST_ORDER_DATE	DATE	Date of first order. Use for cohort analysis	-
LAST_ORDER_DATE	DATE	Date of last order. Use for recency analysis	-
UNIQUE_LOCATION_ID_ARRAY	ARRAY	Array of unique location IDs visited. Parse for location diversity	-
MOST_FREQUENT_LOCATION_ID	NUMBER	Most frequently visited location. Use for location preference	-
UNIQUE_DAYPART_ARRAY	ARRAY	Array of unique dayparts. Parse for daypart preferences	-
MOST_FREQUENT_DAYPART	TEXT	Most frequent daypart. Use for daypart preference	-
UNIQUE_DAY_OF_WEEK_ARRAY	ARRAY	Array of unique days of week. Parse for weekday patterns	-
MOST_FREQUENT_DAY_OF_WEEK	NUMBER	Most frequent day of week (1-7). Use for weekday preference	-
UNIQUE_FULFILLMENT_METHOD_ARRAY	ARRAY	Array of unique fulfillment methods. Parse for delivery preferences	-
MOST_FREQUENT_FULFILLMENT_METHOD	TEXT	Most frequent fulfillment method. Use for delivery preference	-
UNIQUE_SOURCE_ARRAY	ARRAY	Array of unique order sources. Parse for platform preferences	-
MOST_FREQUENT_SOURCE	TEXT	Most frequent order source. Use for platform preference	-
UNIQUE_ORDER_SOURCE_ARRAY	ARRAY	Array of unique order sources. Parse for source diversity	-
MOST_FREQUENT_ORDER_SOURCE	TEXT	Most frequent order source. Use for source preference	-
UNIQUE_ORDER_LOCATION_ID_ARRAY	ARRAY	Array of unique order locations. Parse for location diversity	-
MOST_FREQUENT_ORDER_LOCATION_ID	NUMBER	Most frequent order location. Use for location preference	-
LIFETIME_VALUE_USD	FLOAT	Pre-aggregated. Total lifetime value in USD. Use `AVG(LIFETIME_VALUE_USD)` for CLV	-
AVERAGE_ORDER_VALUE_USD	FLOAT	Pre-aggregated. Average order value in USD. Use for AOV analysis	-
IS_LOYALTY_MEMBER	NUMBER	Whether person is loyalty member. Use for loyalty program analysis	0, 1
DAYS_BETWEEN_FIRST_AND_SECOND_ORDER	NUMBER	Days between first and second order. Use for early retention	-
AVG_DAYS_BETWEEN_ORDERS	NUMBER	Average days between orders. Use for order frequency	-
AVG_WEEKS_BETWEEN_ORDERS	NUMBER	Average weeks between orders. Use for weekly frequency	-
AVG_MONTHS_BETWEEN_ORDERS	NUMBER	Average months between orders. Use for monthly frequency	-
UNIQUE_LOCATION_ID_COUNT	NUMBER	Count of unique locations visited. Use for location diversity	-
FIRST_LOCATION_ID	NUMBER	First location visited. Use for acquisition analysis	-
SECOND_LOCATION_ID	NUMBER	Second location visited. Use for location expansion	-
PENULTIMATE_LOCATION_ID	NUMBER	Second-to-last location visited. Use for location patterns	-
LAST_LOCATION_ID	NUMBER	Last location visited. Use for recent location	-
UNIQUE_DAYPART_COUNT	NUMBER	Count of unique dayparts. Use for daypart diversity	-
FIRST_DAYPART	TEXT	First daypart ordered. Use for initial preferences	-
SECOND_DAYPART	TEXT	Second daypart ordered. Use for preference evolution	-
PENULTIMATE_DAYPART	TEXT	Second-to-last daypart. Use for recent preferences	-
LAST_DAYPART	TEXT	Last daypart ordered. Use for current preferences	-
UNIQUE_DAY_OF_WEEK_COUNT	NUMBER	Count of unique days of week. Use for weekday diversity	-
FIRST_DAY_OF_WEEK	NUMBER	First day of week ordered (1-7). Use for initial patterns	-
SECOND_DAY_OF_WEEK	NUMBER	Second day of week ordered. Use for pattern evolution	-
PENULTIMATE_DAY_OF_WEEK	NUMBER	Second-to-last day of week. Use for recent patterns	-
LAST_DAY_OF_WEEK	NUMBER	Last day of week ordered. Use for current patterns	-
UNIQUE_FULFILLMENT_METHOD_COUNT	NUMBER	Count of unique fulfillment methods. Use for method diversity	-
FIRST_FULFILLMENT_METHOD	TEXT	First fulfillment method used. Use for initial preferences	-
SECOND_FULFILLMENT_METHOD	TEXT	Second fulfillment method used. Use for preference evolution	-
PENULTIMATE_FULFILLMENT_METHOD	TEXT	Second-to-last fulfillment method. Use for recent preferences	-
LAST_FULFILLMENT_METHOD	TEXT	Last fulfillment method used. Use for current preferences	-
UNIQUE_SOURCE_COUNT	NUMBER	Count of unique sources. Use for source diversity	-
FIRST_SOURCE	TEXT	First order source. Use for acquisition channel	-
SECOND_SOURCE	TEXT	Second order source. Use for channel evolution	-
PENULTIMATE_SOURCE	TEXT	Second-to-last source. Use for recent channels	-
LAST_SOURCE	TEXT	Last order source. Use for current channel	-
UNIQUE_ORDER_SOURCE_COUNT	NUMBER	Count of unique order sources. Use for source diversity	-
FIRST_ORDER_SOURCE	TEXT	First order source. Use for acquisition analysis	-
SECOND_ORDER_SOURCE	TEXT	Second order source. Use for source evolution	-
PENULTIMATE_ORDER_SOURCE	TEXT	Second-to-last order source. Use for recent sources	-
LAST_ORDER_SOURCE	TEXT	Last order source. Use for current source	-
UNIQUE_ORDER_LOCATION_ID_COUNT	NUMBER	Count of unique order locations. Use for location diversity	-
FIRST_ORDER_LOCATION_ID	NUMBER	First order location. Use for acquisition location	-
SECOND_ORDER_LOCATION_ID	NUMBER	Second order location. Use for location expansion	-
PENULTIMATE_ORDER_LOCATION_ID	NUMBER	Second-to-last order location. Use for recent locations	-
LAST_ORDER_LOCATION_ID	NUMBER	Last order location. Use for current location	-
DAYS_SINCE_LAST_ORDER	NUMBER	Days since last order. Use for recency analysis	-
DAYS_SINCE_LAST_EVENT	NUMBER	Days since last event. Use for engagement recency	-
ORDER_LATENCY	TEXT	Order frequency category. Use for frequency segmentation	-
UNIQUE_LIFECYCLE_STAGE_ARRAY	ARRAY	Array of lifecycle stages. Parse for stage progression	-
UNIQUE_LIFECYCLE_STAGE_ID_ARRAY	ARRAY	Array of lifecycle stage IDs. Parse for stage tracking	-
FIRST_LIFECYCLE_STAGE	TEXT	First lifecycle stage. Always lowercase	onboarding, first-time
LAST_LIFECYCLE_STAGE	TEXT	Current/last lifecycle stage. Always lowercase. Use for current segmentation	onboarding, engaged, churned
PENULTIMATE_LIFECYCLE_STAGE	TEXT	Second-to-last lifecycle stage. Use for stage transitions	-
MOST_FREQUENT_LIFECYCLE_STAGE	TEXT	Most frequent lifecycle stage. Use for typical stage	-
FIRST_LIFECYCLE_STAGE_ID	NUMBER	First lifecycle stage ID. Use for stage tracking	-
LAST_LIFECYCLE_STAGE_ID	NUMBER	Last lifecycle stage ID. Use for current stage	-
PENULTIMATE_LIFECYCLE_STAGE_ID	NUMBER	Second-to-last stage ID. Use for transitions	-
MOST_FREQUENT_LIFECYCLE_STAGE_ID	NUMBER	Most frequent stage ID. Use for typical stage	-
UNIQUE_ORDER_FREQUENCY_ARRAY	ARRAY	Array of order frequencies. Parse for frequency patterns	-
UNIQUE_ORDER_FREQUENCY_ID_ARRAY	ARRAY	Array of frequency IDs. Parse for frequency tracking	-
FIRST_ORDER_FREQUENCY	TEXT	First order frequency. Always lowercase	daily, weekly, monthly
LAST_ORDER_FREQUENCY	TEXT	Current order frequency. Always lowercase	daily, weekly, monthly
PENULTIMATE_ORDER_FREQUENCY	TEXT	Second-to-last frequency. Use for frequency changes	-
MOST_FREQUENT_ORDER_FREQUENCY	TEXT	Most frequent order frequency. Use for typical frequency	-
FIRST_ORDER_FREQUENCY_ID	NUMBER	First frequency ID. Use for tracking	-
LAST_ORDER_FREQUENCY_ID	NUMBER	Last frequency ID. Use for current frequency	-
PENULTIMATE_ORDER_FREQUENCY_ID	NUMBER	Second-to-last frequency ID. Use for changes	-
MOST_FREQUENT_ORDER_FREQUENCY_ID	NUMBER	Most frequent frequency ID. Use for typical frequency	-
REPEAT_INDICATOR	NUMBER	Whether customer is repeat (1) or first-time (0). Use for repeat customer analysis	0, 1
LAPSED_INDICATOR	NUMBER	Whether customer is lapsed. Use for churn identification	0, 1
LIFETIME_START_TIME	TIMESTAMP_TZ	When customer lifetime started. Use for cohort analysis	-
LIFETIME_END_TIME	TIMESTAMP_TZ	When customer lifetime ended. Use for churn analysis	-
PII_CLASS	TEXT	PII classification level. Use for privacy compliance	-
IS_TRACKABLE	NUMBER	Whether person can be tracked. Use `WHERE IS_TRACKABLE = 1` for all guest analysis	0, 1
PII_FIELDS	ARRAY	Array of PII fields present. Use for privacy compliance	-
EMAIL_OPT_IN_STATUS	TEXT	Email opt-in status. Use for email marketing	-
SMS_OPT_IN_STATUS	TEXT	SMS opt-in status. Use for SMS marketing	-
ALTERNATE_IDS	ARRAY	Array of alternate identifiers. Parse for ID mapping	-
PREFERRED_LOCATION_ID	NUMBER	Preferred location ID. Use for location preference	-
BIRTHDAYS	ARRAY	Array of birthday dates. Parse for birthday marketing	-

LOCATIONS

Description: Store and location information. Use this table to filter and group orders by location, and to get location names for reporting.

How to use: Join to ORDERS on LOCATION_ID to get location details for orders. Join to LOCATION_ATTRIBUTE_VALUES for additional location characteristics.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
ID	NUMBER	Primary key. Unique location identifier. Same as LOCATION_ID in other tables	6091, 5976, 9659
NAME	TEXT	Location/store name. Use for location labels in reports	-
TIMEZONE	TEXT	Location timezone. Use for timezone-aware analysis	-

LOCATION_ATTRIBUTE_VALUES

Description: Additional attributes and characteristics for locations (e.g., region, market, demographics). Use this table to segment and analyze locations by custom attributes.

How to use: Join to LOCATIONS on LOCATION_ID to get location details. Filter by ATTRIBUTE to focus on specific attribute types. Use DISPLAY_VALUE for human-readable attribute descriptions.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
LOCATION_ID	NUMBER	Foreign key. Unique location identifier. Join to LOCATIONS table	3215, 7041, 6007
ATTRIBUTE	TEXT	Type of location attribute. Use to filter by attribute type	Regional Director, Zip Code Median Age, Zip Code DMA
ATTRIBUTE_TYPE	TEXT	Type classification of attribute. Use for attribute categorization	-
VALUE	TEXT	Location attribute value. Use for attribute-based filtering	-
DISPLAY_VALUE	TEXT	Combined attribute and value display. Use for human-readable labels in reports	Region: NYC, Start Year: 2014, Zip Code Median Age: >50

ISSUED_OFFERS

Description: Individual offer instances issued to specific customers. Use this table to analyze offer distribution, redemption rates, and offer performance by customer segment.

How to use: Join to PERSONS on PERSON_ID for customer details. Join to OFFER_EVENTS on ISSUED_OFFER_ID for event history. Join to ORDERS on redemption order IDs (via OFFER_EVENTS) to analyze revenue impact.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
ISSUED_OFFER_ID	TEXT	Primary key. Unique identifier for the issued offer instance. Join to OFFER_EVENTS	-
ISSUED_OFFER_REFERENCE_ID	TEXT	External reference identifier. Use for source system mapping	-
PERSON_ID	TEXT	Foreign key. Customer who received the offer. Join to PERSONS table	-
MEMBER_ID	TEXT	Loyalty member identifier. Use for loyalty program analysis	-
MEMBER_EXTERNAL_ID	TEXT	External member identifier. Use for ID mapping	-
OFFER_ID	TEXT	Foreign key. Promotional offer identifier. Join to OFFERS table	-
SOURCE	TEXT	Source system for offer. Use for source analysis	-
STATUS	TEXT	Current status of offer. Use for offer status filtering	-
ISSUED_AT	TIMESTAMP_TZ	When the offer was issued to customer. Use for offer timing analysis	-
FIRST_REDEEMED_AT	TIMESTAMP_TZ	When the offer was first redeemed. Use for redemption timing	-
LAST_REDEEMED_AT	TIMESTAMP_TZ	When the offer was last redeemed. Use for most recent redemption	-
EXPIRES_AT	TIMESTAMP_TZ	When the offer expires. Use for expiration analysis	-
EXPIRED_AT	TIMESTAMP_TZ	When the offer expired. Use for expiration tracking	-
VOIDED_AT	TIMESTAMP_TZ	When the offer was voided. Use for voided offer analysis	-
TOTAL_REDEMPTIONS	NUMBER	Number of times the offer was redeemed. Use `SUM(TOTAL_REDEMPTIONS)` for total redemptions	-

OFFER_EVENTS

Description: Events related to offer lifecycle (issued, redeemed, expired). Use this table to track offer performance, redemption patterns, and event timelines.

How to use: Join to ISSUED_OFFERS on ISSUED_OFFER_ID for offer details. Join to ORDERS on REDEMPTION_ORDER_ID to analyze revenue from redemptions. Filter by EVENT_TYPE to focus on specific events.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
OFFER_EVENT_ID	TEXT	Primary key. Unique identifier for the event	-
EVENT_ID	TEXT	Event identifier. Use for event tracking	-
ISSUED_OFFER_ID	TEXT	Foreign key. Issued offer instance identifier. Join to ISSUED_OFFERS table	-
OFFER_ID	TEXT	Foreign key. Promotional offer identifier. Join to OFFERS table	-
PERSON_ID	TEXT	Foreign key. Customer associated with event. Join to PERSONS table	-
LOCATION_ID	NUMBER	Foreign key. Location where event occurred. Join to LOCATIONS table	-
REDEMPTION_ORDER_ID	TEXT	Foreign key. Order where offer was redeemed. Join to ORDERS for revenue analysis	-
EVENT_TYPE	TEXT	Type of offer event. Use to filter by event type	redeemed, issued, expired
EVENT_DATE	DATE	Date when event occurred. Use for date-based analysis	-
EVENT_TIMESTAMP	TIMESTAMP_TZ	Timestamp when event occurred. Use for precise timing	-
SOURCE	TEXT	Source system for event. Use for source analysis	-
INGESTION_TIME	TIMESTAMP_TZ	When data was ingested. Use for data freshness	-

OFFERS

Description: Promotional offer definitions and aggregate statistics. Use this table to analyze offer performance at the campaign level.

How to use: Join to ISSUED_OFFERS on OFFER_ID to get issued instances. Use aggregate counts (COUNT_ISSUED, COUNT_REDEEMED) for quick performance metrics.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
OFFER_ID	TEXT	Primary key. Promotional offer identifier. Join to ISSUED_OFFERS	-
REFERENCE_ID	TEXT	External reference identifier. Use for source system mapping	-
NAME	TEXT	Name of the promotional offer/campaign. Use for offer identification	-
SOURCE	TEXT	Source system for offer. Use for source analysis	-
EXPIRES_AT	TIMESTAMP_TZ	When the offer expires. Use for expiration analysis	-
COUNT_ISSUED	NUMBER	Total number of times offer was issued. Use for distribution analysis	-
COUNT_REDEMED	NUMBER	Total number of times offer was redeemed. Use for redemption analysis	-
COUNT_EXPIRED	NUMBER	Total number of times offer expired. Use for expiration analysis	-
COUNT_VOIDED	NUMBER	Total number of times offer was voided. Use for void analysis	-
INGESTION_TIME	TIMESTAMP_TZ	When data was ingested. Use for data freshness	-

SEGMENT_MEMBERSHIPS

Description: Custom segments that a customer is a member of. Use this table to analyze customer segments and segment-based performance.

How to use: Join to PERSONS on PERSON_ID for customer details. Join to ORDERS on PERSON_ID to analyze segment order behavior.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
SEGMENT_MEMBERSHIP_ID	TEXT	Primary key. Unique identifier for segment membership	-
PERSON_ID	TEXT	Foreign key. Customer who is member of segment. Join to PERSONS table	-
SEGMENT_ID	NUMBER	Segment identifier. Use for segment identification	-

LOYALTY_MEMBERSHIPS

Description: Loyalty program membership information. Use this table to analyze loyalty member details, registration sources, and opt-in preferences.

How to use: Join to PERSONS on PERSON_ID (may need to match on email/phone) for customer details. Filter by EMAIL_OPT_IN_INDICATOR or SMS_OPT_IN_INDICATOR for marketing consent.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
PERSON_ID	TEXT	Foreign key. Customer identifier. Join to PERSONS table (may need to match on email/phone)	-
FIRST_NAME	TEXT	Member first name. Use for personalization	-
LAST_NAME	TEXT	Member last name. Use for identification	-
LAST_INITIAL	TEXT	Member last initial. Use for privacy-preserving displays	-
EMAIL	TEXT	Member email address. Use for email marketing	-
PHONE	TEXT	Member phone number. Use for SMS marketing	-
EMAIL_OPT_IN_INDICATOR	TEXT	Email opt-in status. Use for email consent	-
SMS_OPT_IN_INDICATOR	TEXT	SMS opt-in status. Use for SMS consent	-
REGISTRATION_DATE	DATE	Date when member registered. Use for registration analysis	-
SOURCE	TEXT	Registration source. Use for acquisition channel	-
SOURCE_MEMBER_ID	TEXT	External member identifier. Use for ID mapping	-

MENU_ITEMS

Description: Menu item catalog with aggregate statistics. Use this table to analyze menu item performance, pricing, and availability.

How to use: Join to ORDER_ITEMS on ITEM_NAME or MENU_ITEM_ID to get order details. Use aggregate fields (ORDERS, AVG_PRICE_USD) for quick analysis.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
MENU_ITEM_ID	TEXT	Primary key. Unique menu item identifier. Join to ORDER_ITEMS	-
ITEM_ID	TEXT	Item identifier. May match MENU_ITEM_ID	-
ITEM_NAME	TEXT	Name of menu item. Join to ORDER_ITEMS.ITEM	-
MENU_NAME	TEXT	Name of menu. Use if multiple menus exist	-
MENU_GROUP_ID	TEXT	Menu group identifier. Use for category analysis	-
MENU_GROUP_NAME	TEXT	Name of menu group. Use for category labels	-
ITEM_VERTICAL	TEXT	Item vertical/category. Use for vertical analysis	Entree, Drink, Other
IS_ENTREE	NUMBER	Whether item is an entree. Use for entree filtering	0, 1
IS_DRINK	BOOLEAN	Whether item is a drink. Use for drink filtering	true, false
IS_ON_CANONICAL_MENU	NUMBER	Whether on canonical menu. Use for menu availability	0, 1
AVG_MENU_PRICE	FLOAT	Average menu price. Use for pricing analysis	-
AVG_PRICE_USD	FLOAT	Average price in USD. Use for price analysis	-
AVG_QUANTITY	NUMBER	Average quantity per order. Use for quantity analysis	-
ORDERS	NUMBER	Pre-aggregated. Total orders containing this item. Use for popularity	-
SOURCES	ARRAY	Array of order sources. Parse for source diversity	-
SOURCE_TYPES	ARRAY	Array of source types. Parse for source analysis	-
INGESTION_TIME	TIMESTAMP_TZ	When data was ingested. Use for data freshness	-

EMAIL_EVENTS

Description: Email marketing events (sent, opened, clicked). Use this table to analyze email campaign performance and customer engagement.

How to use: Join to PERSONS on PERSON_ID for customer details. Filter by EVENT_TYPE to analyze specific event types. Use EVENT_TIMESTAMP for timing analysis.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
EVENT_ID	TEXT	Primary key. Unique event identifier	-
PERSON_ID	TEXT	Foreign key. Customer who received email. Join to PERSONS table	-
SOURCE	TEXT	Source system for event. Use for source analysis	-
EVENT_TYPE	TEXT	Type of email event. Use to filter by event type	sent, opened, clicked
EVENT_TIMESTAMP	TIMESTAMP_TZ	When event occurred. Use for timing analysis	-
EMAIL_ADDRESS	TEXT	Email address. Use for email identification	-
FROM_EMAIL	TEXT	Sender email address. Use for sender analysis	-
EMAIL_SUBJECT	TEXT	Email subject line. Use for subject line analysis	-
EMAIL_TITLE	TEXT	Email title. Use for email identification	-
EMAIL_TYPE	TEXT	Type of email. Use for email categorization	-
CLICKED_URL	TEXT	URL that was clicked. Use for click analysis (only for click events)	-

SMS_EVENTS

Description: SMS marketing events (sent, delivered, clicked). Use this table to analyze SMS campaign performance and customer engagement.

How to use: Join to PERSONS on PERSON_ID for customer details. Filter by EVENT_TYPE to analyze specific event types. Use EVENT_TIMESTAMP for timing analysis.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
EVENT_ID	TEXT	Primary key. Unique event identifier	-
PERSON_ID	TEXT	Foreign key. Customer who received SMS. Join to PERSONS table	-
SOURCE	TEXT	Source system for event. Use for source analysis	-
EVENT_TYPE	TEXT	Type of SMS event. Use to filter by event type	sent, delivered, clicked
EVENT_TIMESTAMP	TIMESTAMP_TZ	When event occurred. Use for timing analysis	-
PHONE	TEXT	Phone number. Use for phone identification	-
MESSAGE_ID	TEXT	Message identifier. Use for message tracking	-
MESSAGE_TYPE	TEXT	Type of message. Use for message categorization	-
MESSAGE_TEXT	TEXT	Message content. Use for message analysis	-
CLICKED_URL	TEXT	URL that was clicked. Use for click analysis (only for click events)	-

OPT_IN_OUT_EVENTS

Description: Customer opt-in and opt-out events for email and SMS marketing. Use this table to track consent changes and compliance.

How to use: Join to PERSONS on PERSON_ID for customer details. Filter by CHANNEL and EVENT_TYPE to analyze consent patterns. Use EVENT_TIMESTAMP for timeline analysis.

Columns:

Column Name	Data Type	Description	Sample Values
BUSINESS_ID	NUMBER	Unique identifier for the business/restaurant. This will only have one value, for your business	-
EVENT_ID	TEXT	Primary key. Unique event identifier	-
PERSON_ID	TEXT	Foreign key. Customer associated with event. Join to PERSONS table	-
SOURCE	TEXT	Source system for event. Use for source analysis	-
CHANNEL	TEXT	Marketing channel. Use to filter by channel	email, sms
EVENT_TYPE	TEXT	Type of event. Use to filter by consent action	opt_in, opt_out
EVENT_TIMESTAMP	TIMESTAMP_TZ	When event occurred. Use for timeline analysis	-
EMAIL	TEXT	Email address (if email channel). Use for email identification	-
PHONE	TEXT	Phone number (if SMS channel). Use for phone identification	-

Common Query Patterns

Revenue Analysis

SELECT
    DATE_TRUNC('week', business_date) AS week
    , SUM(sub_total_usd) AS revenue 
FROM 
    orders 
WHERE 
    business_date >= CURRENT_DATE - INTERVAL '12 weeks' 
GROUP BY 
    1 
ORDER BY 
    1 DESC
;

Customer Lifetime Value

SELECT
    AVG(lifetime_value_usd) AS avg_clv
    , SUM(lifetime_value_usd) AS total_clv 
FROM 
    persons 
WHERE 
    is_trackable = 1
;

PMIX Report

SELECT
    item
    , SUM(price) AS revenue
    , SUM(quantity) AS volume
    , SUM(price) / SUM(SUM(price)) OVER() AS percent_of_revenue 
FROM 
    order_items 
WHERE 
    business_date >= CURRENT_DATE - INTERVAL '1 month' 
GROUP BY 
    1 
ORDER BY 
    2 DESC
;

Offer Redemption Analysis

SELECT
    oe.offer_name
    , COUNT(DISTINCT oe.issued_offer_id) AS offers_issued
    , COUNT(DISTINCT CASE WHEN oe.event_type = 'redeemed' THEN oe.issued_offer_id END) AS offers_redeemed
    , SUM(ord.sub_total_usd) AS redemption_revenue 
FROM 
    offer_events oe 
LEFT JOIN 
    orders ord 
    ON oe.redemption_order_id = ord.order_id 
WHERE 
    oe.event_date >= CURRENT_DATE - INTERVAL '1 month' 
GROUP BY 
    1
;