Metrics Catalog & Glossary
This page serves as a quick-lookup appendix for key business metrics, their definitions, and guidance on when to use each one.
For more context on data consistency and contracts, see the Start Here guide. For detailed table structures, see the Data Dictionary and Data Contracts pages.
Metrics Catalog
| Metric (Canonical) | Definition & Formula | Grain & Dimensionality | Owner Fact / Rollup & Lineage |
|---|---|---|---|
| Reviews Count | Count of latest review rows; in daily reporting use SUM(reviews_count) | By Location, Account, Brand, Platform, Date | fact_reviews -> fact_location_daily_stats |
| Avg Review Rating (Last Month) | Weighted cumulative avg rating over last 30 days, combining pre-Edge baseline + post-Edge reviews | By Location, Account, Brand, Date | fact_location_daily_stats |
| Avg Review Rating (Last Quarter) | Weighted cumulative avg rating over last 90 days, combining pre-Edge baseline + post-Edge reviews | By Location, Account, Brand, Date | fact_location_daily_stats |
| Avg Review Rating (Last Year) | Weighted cumulative avg rating over last 365 days, combining pre-Edge baseline + post-Edge reviews | By Location, Account, Brand, Date | fact_location_daily_stats |
| Avg Review Rating (All Time) | Weighted cumulative avg rating over all time, combining pre-Edge baseline + post-Edge reviews | By Location, Account, Brand, Date | fact_location_daily_stats |
| Review Versions Count | Count of observed review versions or edits | By Location, Account, Brand, Platform, Date | fact_review_versions -> fact_location_daily_stats |
| Avg Review Version Rating | Average rating across observed review versions | By Location, Account, Brand, Platform, Date | fact_review_versions -> fact_location_daily_stats |
| Review Gate Count | Count of review-gate interactions | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Avg Review Gate Rating | Average rating on review-gate interactions | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Mentions Count | Count of employee mentions | By Location, Employee, Account, Brand, Date | fact_employee_mentions -> fact_location_daily_stats |
| Manual Review Requested Count | Count of employee mentions where manual_review_requested = TRUE | By Location, Employee, Account, Brand, Date | fact_employee_mentions |
| Interactions Count | Count of guest-business interactions | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Chat Interactions Count | Count of interactions shown in chat | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Resolved Chat Interactions Count | Count of resolved interactions shown in chat | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Positive Interactions Count | Count of interactions with positive review-gate sentiment | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Neutral Interactions Count | Count of interactions with neutral review-gate sentiment | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Negative Interactions Count | Count of interactions with negative review-gate sentiment | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Resolved Negative Interactions | Count of resolved negative interactions | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Outbound Messages | Count of location-sent messages | By Location, Account, Brand, Date | fact_messages -> fact_location_daily_stats |
| Inbound Messages | Count of customer-sent messages | By Location, Account, Brand, Date | fact_messages -> fact_location_daily_stats |
| Avg First Response Minutes | Average minutes from first customer message to first location response | By Location, Account, Brand, Date | fact_interactions -> fact_location_daily_stats |
| Saved Revenue | Sum of saved_revenue | By Location, Account, Brand, Date | fact_customers_saved |
| Saved Visits | Sum of saved_visits | By Location, Account, Brand, Date | fact_customers_saved |
| Transactions Count | Count of POS transactions | By Location, Account, Brand, Date | fact_transactions -> fact_location_daily_stats |
| Transaction Items Count | Count of transaction line items | By Location, Account, Brand, Date | fact_transaction_items -> fact_location_daily_stats |
| Transaction Subtotal Amount | Sum of transaction subtotal amount | By Location, Account, Brand, Date | fact_transactions -> fact_location_daily_stats |
| Customer Count | Count of transaction customer IDs | By Location, Account, Brand, Date | fact_transactions -> fact_location_daily_stats |
| Unique Customers Count | Count of distinct transaction customer IDs | By Location, Account, Brand, Date | fact_transactions -> fact_location_daily_stats |
| Most Popular Item ID | Most frequent transaction item ID for the location/date | By Location, Account, Brand, Date | fact_transaction_items -> fact_location_daily_stats |
Note: If you need the latest review snapshot, use fact_reviews. If you need to count all review edits observed, use fact_review_versions.
Cheatsheet
| Goal (Plain English) | Pick This Metric | Grain You'll Query | Common Mistakes to Avoid |
|---|---|---|---|
| "How many reviews do we have today?" | Reviews Count (latest) | Latest snapshot | Using fact_review_versions and counting reviews more than once |
| "How often do people edit reviews?" | Review Versions Count | Version (review_id x version) | |
| "Show weekly review trend by brand" | Daily Review Metrics (rollups) | date x brand (then week) | Recomputing from events when rollups exist (slower) |
| "Average rating last month vs this month" | avg_review_rating_last_month from fact_location_daily_stats | date x location/brand | Use cumulative columns; compare values at month boundaries |
| "Median first response time by location" | Response Metrics | date x location | Joining raw facts before aggregating to the same grain |
| "How much revenue came through POS?" | Transaction Subtotal Amount | date x location/brand | Mixing transaction rows with transaction-item rows before aggregating |
| "How many saved visits did outreach drive?" | Saved Visits | saved customer event | Treating saved customer rows as unique people across all systems |
Decision Map
This decision map highlights how to pick the right review-related metric before you write a query. It helps prevent the most common "my numbers don’t match" issues.
flowchart TD
%% Define classes first
classDef default fill:#fbff37,stroke:#333333,stroke-width:3px,color:#162863
classDef question fill:#0b5ed7,stroke:#0b5ed7,stroke-width:2px,color:#ffffff
classDef snap fill:#ffffff,stroke:#333333,stroke-width:2px,color:#162863
A["What do you want to answer?"]:::question --> B{"Do you need the current total as of now?"}:::question
B -->|Yes| M1["Reviews Count (latest)"]:::snap
B -->|No| C{"Do you need to see every edit/version over time?"}:::question
C -->|Yes| M2["Review Versions Count (historical)"]
C -->|No| D{"Are you trending by day/week/month?"}:::question
D -->|Yes| M3["Daily Review Metrics (rollups)"]
D -->|No| F{"Do you need ratings aggregates (avg)?"}:::question
F -->|Yes| M5["Cumulative Rating Columns (avg_review_rating_*)"]:::question
F -->|No| M6["Use the owner fact for the subject area"]
%% Caveats inline
M1 --> M1C["Snapshot: one row per active review (latest state)"]
M2 --> M2C["History: multiple rows per review across edits"]
M3 --> M3C["Rollup: pre-aggregated at date x location (faster)"]