Data Dictionary

This dictionary covers the public ANALYTICS tables used for reporting and customer exports. Types are Snowflake-style categories and are intended for query planning, not strict DDL reproduction. Internal helper tables are intentionally omitted.

---

Shared Columns

Column	Type	Definition
location_id	STRING	Unique location identifier. Primary join key to DIM_LOCATIONS.
account_id	STRING	Account identifier associated with the location.
brand	STRING	Normalized brand name.
customer_id	STRING	Opaque customer identifier. Customer names and contact details are not exposed. Values originate in the source system, so the same person across different POS or platform sources is not deduplicated.
created_at_datetime	TIMESTAMP	UTC timestamp for when the row's underlying entity or event was created.
created_at_date	DATE	UTC date derived from created_at_datetime.
ingested_at_datetime	TIMESTAMP	UTC timestamp for when the row landed in the warehouse layer.

Dimensions

DIM_DATES

One row per calendar date.

Column	Type	Definition
date_id	DATE	Primary key and canonical reporting date.
year	NUMBER	Calendar year.
quarter	NUMBER	Calendar quarter.
month	NUMBER	Calendar month number.
day	NUMBER	Day of month.
week	NUMBER	ISO week number.
day_name_short	STRING	Abbreviated day name.
day_name	STRING	Full day name.
month_name_short	STRING	Abbreviated month name.
month_name	STRING	Full month name.
is_weekend	BOOLEAN	True for Saturday and Sunday.

DIM_LOCATIONS

One row per location. Use this table for brand, geography, status, and location-level filtering.

Column	Type	Definition
location_id	STRING	Primary key.
account_id	STRING	Account identifier.
brand	STRING	Normalized brand name.
name	STRING	Location display name.
active	BOOLEAN	True when the location is active.
industry	STRING	Normalized industry.
internal	BOOLEAN	True for internal, demo, or test locations.
us_region_4	STRING	Four-region US grouping.
us_region_9	STRING	Nine-region US grouping.
state	STRING	Normalized state or province code.
country	STRING	Normalized country code.
lat	NUMBER	Latitude, with fallbacks for missing values.
lon	NUMBER	Longitude, with fallbacks for missing values.
timezone	STRING	Location time zone.
pos_name	STRING	POS system name when available.
employee_count	NUMBER	Count of employees linked to the location.
first_msg	DATE	First message date observed for the location.
last_msg	DATE	Last message date observed for the location.
first_review	DATE	First review date observed for the location.
last_review	DATE	Last review date observed for the location.
review_count_before_edge	NUMBER	Review count before Edge activity began, using configured Google baseline when available.
review_count_after_edge	NUMBER	Unique review count after Edge activity began.
review_version_count_after_edge	NUMBER	Review version count after Edge activity began.
endorser_program	BOOLEAN	Whether the endorser program is enabled.
eligible	BOOLEAN	Whether the location is eligible for the configured program.
payback	NUMBER	Program payback value when configured.
threshold	NUMBER	Program threshold value when configured.
cap	NUMBER	Program cap value when configured.
shared_cap	BOOLEAN	Whether the program cap is shared.
split_payment	BOOLEAN	Whether split payment is enabled.
payment_program	BOOLEAN	Whether a payment program is enabled.
digital_checks	BOOLEAN	Whether digital checks are enabled.
go_live_datetime	TIMESTAMP	Location go-live timestamp.
go_live_date	DATE	Location go-live date.
created_at_datetime	TIMESTAMP	Location creation timestamp.
created_at_date	DATE	Location creation date.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.
google_reviews_pre_go_live	NUMBER	Raw Google review count before go-live when provided by source data.
google_rating_pre_go_live	NUMBER	Raw Google rating before go-live when provided by source data.

DIM_EMPLOYEES

One row per employee.

Column	Type	Definition
employee_id	STRING	Primary key.
email	STRING	Employee email when available.
first_name	STRING	Employee first name when available.
last_name	STRING	Employee last name when available.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

Facts

FACT_REVIEWS

Latest state of each review.

Column	Type	Definition
review_id	STRING	Primary key.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
first_version_datetime	TIMESTAMP	Timestamp when the review first appeared.
first_version_date	DATE	Date when the review first appeared.
last_version_datetime	TIMESTAMP	Timestamp for the current/latest review version.
last_version_date	DATE	Date for the current/latest review version.
platform	STRING	Review platform.
rating	NUMBER	Review rating.
author	STRING	Review author display value when available.
endorsed	BOOLEAN	Whether the review is endorsed.
endorsed_datetime	TIMESTAMP	Endorsement timestamp.
endorsed_date	DATE	Endorsement date.
endorser	STRING	Normalized endorser category.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_REVIEW_VERSIONS

Observed review version history.

Column	Type	Definition
review_version_id	STRING	Primary key.
location_id	STRING	Location key.
account_id	STRING	Account key.
review_id	STRING	Parent review key.
brand	STRING	Brand name.
version_datetime	TIMESTAMP	Timestamp for this observed review version.
version_date	DATE	Date for this observed review version.
platform	STRING	Review platform.
rating	NUMBER	Rating on this version.
author	STRING	Review author display value when available.
endorsed	BOOLEAN	Whether this version is endorsed.
endorsed_datetime	TIMESTAMP	Endorsement timestamp.
endorsed_date	DATE	Endorsement date.
endorser	STRING	Normalized endorser category.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_EMPLOYEE_MENTIONS

Employee mentions detected in reviews.

Column	Type	Definition
mention_id	STRING	Primary key.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
employee_id	STRING	Mentioned employee key when matched.
review_id	STRING	Review version key associated with the mention.
platform	STRING	Review platform.
rating	NUMBER	Review rating associated with the mention.
manual_review_requested	BOOLEAN	True when the mention has been routed for manual review.
created_at_datetime	TIMESTAMP	Mention timestamp.
created_at_date	DATE	Mention date.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_CUSTOMERS_SAVED

Saved customer outcomes attributed to outreach.

Column	Type	Definition
customer_id	STRING	Opaque saved customer identifier.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
saved_revenue	NUMBER	Saved revenue amount.
saved_visits	NUMBER	Saved visit count.
created_at_datetime	TIMESTAMP	Saved customer event timestamp.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_INTERACTIONS

Latest state of guest-business interactions.

Column	Type	Definition
interaction_id	STRING	Primary key.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
customer_id	STRING	Opaque customer identifier.
resolved	BOOLEAN	True when the interaction has a resolved timestamp.
resolved_datetime	TIMESTAMP	Resolved timestamp.
resolved_date	DATE	Resolved date.
interaction_type	STRING	Normalized interaction type.
associated_review_gate	BOOLEAN	True when associated with a review gate.
review_gate_rating	NUMBER	Review gate rating when available.
review_gate_sentiment	STRING	Normalized sentiment: positive, neutral, or negative.
shown_in_chat	BOOLEAN	True when shown in chat.
messages_inbound_count	NUMBER	Count of customer messages in the interaction.
messages_outbound_count	NUMBER	Count of location messages in the interaction.
initiator	STRING	Sender type for the first message.
first_customer_msg_seen_datetime	TIMESTAMP	First customer message timestamp.
first_customer_msg_seen_date	DATE	First customer message date.
first_response_datetime	TIMESTAMP	First location response after first customer message.
first_response_date	DATE	First response date.
first_response_minutes	NUMBER	Minutes from first customer message to first location response.
created_at_datetime	TIMESTAMP	Interaction start timestamp.
created_at_date	DATE	Interaction start date.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_MESSAGES

One row per message with parent interaction context.

Column	Type	Definition
message_id	STRING	Primary key.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
interaction_id	STRING	Parent interaction key.
customer_id	STRING	Opaque customer identifier.
message_type	STRING	Normalized message type.
sender_type	STRING	Sender category, such as customer or location.
sender_id	STRING	Sender identifier.
message_origin	STRING	Message origin.
message_read	BOOLEAN	True when marked read.
interaction_resolved	BOOLEAN	Parent interaction resolved flag.
interaction_last_updated_at_datetime	TIMESTAMP	Parent interaction last-message timestamp.
interaction_last_updated_at_date	DATE	Parent interaction last-message date.
interaction_resolved_at_datetime	TIMESTAMP	Parent interaction resolved timestamp.
interaction_resolved_at_date	DATE	Parent interaction resolved date.
interaction_type	STRING	Parent interaction type.
associated_review_gate	BOOLEAN	Parent review gate flag.
review_gate_rating	NUMBER	Parent review gate rating.
review_gate_sentiment	STRING	Parent review gate sentiment.
shown_in_chat	BOOLEAN	Parent chat visibility flag.
created_at_datetime	TIMESTAMP	Message timestamp.
created_at_date	DATE	Message date.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_TRANSACTIONS

One row per POS transaction.

Column	Type	Definition
transaction_id	STRING	Primary key.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
customer_id	STRING	Opaque customer identifier.
pos_transaction_id	STRING	POS-provided transaction identifier.
transaction_datetime	TIMESTAMP	Transaction timestamp.
transaction_date	DATE	Transaction date.
pos	STRING	POS system.
transaction_item_count	NUMBER	Number of line items or source item count.
transaction_subtotal	NUMBER	Transaction subtotal amount.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_TRANSACTION_ITEMS

One row per transaction line item.

Column	Type	Definition
transaction_item_id	STRING	Primary key.
transaction_id	STRING	Parent transaction key.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
customer_id	STRING	Opaque customer identifier.
pos_transaction_id	STRING	POS-provided transaction identifier.
transaction_datetime	TIMESTAMP	Transaction timestamp.
transaction_date	DATE	Transaction date.
pos	STRING	POS system.
transaction_item_count	NUMBER	Transaction-level item count.
transaction_subtotal	NUMBER	Transaction-level subtotal.
item_id	STRING	Item identifier.
item_product_type	STRING	Item product type.
item_price	NUMBER	Item price.
item_quantity	NUMBER	Item quantity.
item_discount	NUMBER	Item discount amount.
item_subtotal	NUMBER	Item subtotal amount.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.

FACT_LOCATION_DAILY_STATS

One row per location and date. Use this table for dashboards and cross-domain daily reporting.

Column	Type	Definition
id	STRING	Primary key, built from location and date.
location_id	STRING	Location key.
account_id	STRING	Account key.
brand	STRING	Brand name.
date_id	DATE	Reporting date.
review_gate_count	NUMBER	Count of review gate interactions.
avg_review_gate_rating	NUMBER	Average review gate rating.
reviews_count	NUMBER	Count of unique reviews for the date.
avg_review_rating_last_month	NUMBER	Weighted cumulative average rating using the last 30 days plus pre-Edge baseline.
avg_review_rating_last_quarter	NUMBER	Weighted cumulative average rating using the last 90 days plus pre-Edge baseline.
avg_review_rating_last_year	NUMBER	Weighted cumulative average rating using the last 365 days plus pre-Edge baseline.
avg_review_rating_all_time	NUMBER	Weighted cumulative average rating over all time plus pre-Edge baseline.
review_versions_count	NUMBER	Count of review versions for the date.
avg_review_version_rating	NUMBER	Average rating across review versions for the date.
interactions_count	NUMBER	Count of interactions.
chat_interactions_count	NUMBER	Count of interactions shown in chat.
resolved_chat_interactions_count	NUMBER	Count of resolved chat interactions.
positive_interactions_count	NUMBER	Count of positive interactions.
neutral_interactions_count	NUMBER	Count of neutral interactions.
negative_interactions_count	NUMBER	Count of negative interactions.
resolved_negative_interactions_count	NUMBER	Count of resolved negative interactions.
outbound_messages_count	NUMBER	Count of outbound location messages.
inbound_messages_count	NUMBER	Count of inbound customer messages.
avg_first_response_minutes	NUMBER	Average first response time in minutes.
employee_mentions_count	NUMBER	Count of employee mentions.
transactions_count	NUMBER	Count of transactions.
transaction_items_count	NUMBER	Count of transaction items.
transaction_subtotal_amt	NUMBER	Sum of transaction subtotal amount.
customer_count	NUMBER	Count of customer IDs on transactions.
unique_customers_count	NUMBER	Count of distinct customer IDs on transactions.
most_popular_item_id	STRING	Most frequent item ID for the location/date.
ingested_at_datetime	TIMESTAMP	Last warehouse ingestion timestamp.