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 Contracts page.
Metrics Catalog
| Metric (Canonical) | Definition & Formula | Grain & Dimensionality | Owner Fact / Rollup & Lineage |
|---|---|---|---|
| Reviews Count | Count of rows in fact_reviews | By Location, Account, Brand, Platform, Date | fact_reviews → fact_location_daily_stats |
| Avg Review Rating | AVG(rating) on fact_reviews | By Location, Account, Brand, Platform, Date | fact_reviews → fact_location_daily_stats |
| Review Versions Count | Count of rows in fact_review_versions | By Location, Account, Brand, Platform, Date | fact_review_versions |
| Mentions Count | Count of rows in fact_employee_mentions | By Location, Employee, Account, Brand, Date | fact_employee_mentions → fact_location_daily_stats |
| Outbound Messages | COUNT_IF(sender_type='location') on fact_messages | By Location, Date | fact_messages → fact_location_daily_stats |
| Inbound Messages | COUNT_IF(sender_type='customer') on fact_messages | By Location, Date | fact_messages → fact_location_daily_stats |
| Interactions Count | Count of rows in fact_interactions | By Location, Date | fact_interactions → fact_location_daily_stats |
| Avg First Response Minutes | AVG(first_response_minutes) where not null | By Location, Date | fact_interactions → 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 × version) | |
| "Show weekly review trend by brand" | Daily Review Metrics (rollups) | date × brand (then week) | Recomputing from events when rollups exist (slower) |
| "Average rating last month vs this month" | Ratings Aggregates from rollups | date × location/brand | |
| "Median first response time by location" | Response Metrics (messages) | date × location | Joining raw facts before aggregating to the same grain |
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
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| E{"Do you need reviewer-level uniqueness?"}:::question
E -->|Yes| M4["Distinct Reviewers / First-Time Reviewers"]:::question
E -->|No| F{"Do you need ratings aggregates (avg)?"}:::question
F -->|Yes| M5["Ratings Aggregates"]:::question
%% 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)"]