dim_semantic_metric_catalog

Every metric SourceMedium computes, with its formula, category, and aliases. It is regenerated whenever a metric definition changes, so it never drifts from the dashboards.

This is the source of truth for metric definitions. The catalog currently contains 180+ metrics. Query this table to discover available metrics, understand their calculations, and find the right metric for your analysis.

Use Cases

Metric Discovery: Find all available metrics and filter by category or type
Understanding Calculations: See exactly how each metric is calculated
Dependency Tracking: Identify which metrics depend on other metrics
Alias Resolution: Map abbreviated metric names (like aov) to their full descriptive names
Documentation: Generate metric documentation for your team

Columns

Column	Type	Description
`abbreviations_list`	STRING	For primary metrics, a comma-separated list of abbreviations that map to this metric. Example: ‘average_order_value_net’ has abbreviations ‘aov, aov_net_revenue’ NULL for abbreviation metrics.
`calculation`	STRING	Readable formula for the metric, for example `SUM(order_net_revenue)`, `total_revenue / order_count`, or a combination of other metrics.
`catalog_updated_at`	TIMESTAMP	Timestamp when this catalog entry was last refreshed. All rows share the same timestamp from the most recent build.
`cumulative_settings`	STRING	Settings for cumulative metrics: - “month”, “quarter”, “year” (grain_to_date settings) - “30 days”, “90 days”, “365 days” (window settings) - “all_time” (unbounded cumulative from beginning of time) - null for non-cumulative metrics
`dependent_metric_count`	INT64	The total number of metrics that this metric depends on. Useful for understanding metric complexity and dependency chains.
`dependent_metrics`	STRING	Comma-separated list of metrics that this metric depends on. Populated for ratio and derived metrics to show dependencies. Example: “total_ad_clicks, total_ad_impressions” for click_through_rate
`filter_condition`	STRING	The metric’s filter as a WHERE-clause predicate over columns of the underlying table, for example `is_order_sm_valid = TRUE`. NULL when the metric has no filter.
`has_filter`	BOOL	Boolean flag indicating whether the metric has a filter condition applied. True if the metric has any filter, false otherwise.
`is_abbreviation`	BOOL	Boolean flag indicating if this metric is an abbreviation/legacy name. True for metrics like ‘aov’, ‘mer’, ‘cac’ that should use their full names.
`metric_category`	STRING	Business category for grouping related metrics: - Revenue: AOV, revenue totals, profit metrics - Orders: Order counts, order-related metrics - Customers: Customer counts, acquisition metrics - Conversion: Conversion rates, funnel metrics - Marketing: Ad spend, efficiency ratios (MER, CAC, CPO) - Products: Product/SKU counts, inventory metrics - Subscription: Subscriber metrics, churn rates - Web Analytics: Sessions, page views, engagement
`metric_description`	STRING	Detailed explanation of what the metric measures and how it should be interpreted.
`metric_label`	STRING	Human-readable display name (e.g., “Order Net Revenue”, “Average Order Value (AOV) - Net”). Use this for dashboard labels and reports.
`metric_name`	STRING	Unique identifier for the metric (e.g., ‘order_net_revenue’, ‘average_order_value_net’). This is the name you use when querying the semantic layer.
`metric_time_expression`	STRING	The SQL that metric_time stands for on underlying_model, ready to GROUP BY or to join on: `date(order_created_at_local_datetime)` for an obt_orders metric, plain `date` for an rpt_ad_performance_daily one. It is an expression, not necessarily a bare column, because four of the six semantic models declare their agg_time_dimension over one. metric_time_grains says which granularities a metric supports; this says which expression carries them. Without it a consumer had to guess the date column on underlying_model. This is what makes a cross-model ratio answerable. mer, roas, cac, cpo, cpa, cost_per_session and revenue_per_session divide a measure on one model by a measure on another, so a consumer aggregates each side to a common grain and joins on it, and the two sides do not share a column name. Those ratio rows carry NULL here, because two models means no single expression: read dependent_metrics, look up each side’s row, and use the metric_time_expression each one publishes. NULL for exactly the metrics that span models (semantic_model_name ‘Multiple’ or ‘Derived’). Every simple and cumulative metric carries one, asserted by tests/semantic/test_catalog_metrics_executable.sql rather than by a column test here, so the failure names the metric.
`metric_time_grains`	STRING	Available time granularities for this metric: Default: “day, week, month, quarter, year”
`metric_type`	STRING	Type of metric calculation. One of: - `simple`: Direct aggregation of a measure (SUM, COUNT) - `ratio`: Division of two metrics (e.g., revenue / orders = AOV) - `derived`: Calculation combining multiple metrics with custom expressions - `cumulative`: Running totals over time (MTD, QTD, YTD, trailing windows)
`preferred_metric_name`	STRING	For abbreviation metrics, this indicates the full descriptive metric name to use instead. Example: ‘aov’ → ‘average_order_value_net’, ‘mer’ → ‘marketing_efficiency_ratio’ NULL for primary metrics.
`semantic_model_name`	STRING	The name of the semantic model that defines this metric’s measures. Values: obt_orders, obt_customers, rpt_ad_performance_daily, obt_funnel_event_history, dim_product_variants, rpt_executive_summary_daily Special values: ‘Multiple’ (uses multiple semantic models), ‘Derived’ (uses other metrics), ‘None’
`underlying_model`	STRING	The table(s) that contain the data for this metric. Common values: obt_orders, obt_customers, rpt_ad_performance_daily. Can list several tables.

Example Queries

Find All Revenue Metrics

SELECT
    metric_name,
    metric_label,
    metric_type,
    calculation
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE metric_category = 'revenue'
ORDER BY metric_name

Discover Marketing Efficiency Metrics

SELECT
    metric_name,
    metric_description,
    calculation,
    dependent_metrics
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE metric_category = 'marketing'
  AND metric_type = 'ratio'
ORDER BY metric_name

Resolve an Abbreviated Metric Name

-- What is "aov" actually measuring?
SELECT
    metric_name,
    preferred_metric_name,
    metric_description,
    calculation
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE metric_name = 'aov'

Find All Abbreviations for a Metric

-- What abbreviations exist for average_order_value_net?
SELECT
    metric_name,
    abbreviations_list
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE metric_name = 'average_order_value_net'
   OR preferred_metric_name = 'average_order_value_net'

List All Cumulative (MTD/QTD/YTD) Metrics

SELECT
    metric_name,
    metric_label,
    cumulative_settings,
    calculation
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE metric_type = 'cumulative'
ORDER BY cumulative_settings, metric_name

Find Metrics by Underlying Data Source

-- What metrics can I get from the orders data?
SELECT
    metric_name,
    metric_type,
    metric_category
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE semantic_model_name = 'orders'
ORDER BY metric_category, metric_name

Explore Metric Dependencies

-- Which metrics depend on order_net_revenue?
SELECT
    metric_name,
    metric_type,
    dependent_metrics
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE dependent_metrics LIKE '%order_net_revenue%'
ORDER BY metric_name

Get Canonical Metric Names (Resolving Aliases)

-- Always use the preferred (non-abbreviated) metric name
SELECT
    metric_name AS requested_name,
    COALESCE(preferred_metric_name, metric_name) AS canonical_name,
    metric_description
FROM `your_project.sm_metadata.dim_semantic_metric_catalog`
WHERE metric_name IN ('aov', 'mer', 'cac', 'average_order_value_net')

Metric Types Explained

Simple Metrics

Direct aggregations of a single measure. These are the building blocks for other metric types.Examples:

order_net_revenue = SUM(order_net_revenue)
order_count = SUM(valid_order_count)
new_customers = SUM(customer_count) with filter

Calculation column shows: SUM(measure_name) or SUM(measure_name) WHERE [filter applied]

Ratio Metrics

Division of two metrics, typically used for averages and rates.Examples:

average_order_value_net = order_net_revenue / order_count
customer_acquisition_cost = total_ad_spend / new_customer_order_count
click_through_rate = total_ad_clicks / total_ad_impressions

Calculation column shows: numerator_metric / denominator_metric

Derived Metrics

Custom calculations combining multiple metrics with expressions.Examples:

cost_per_thousand_impressions = total_ad_spend * 1000 / total_ad_impressions
gross_margin = gross_profit / order_net_revenue

Calculation pattern: Custom SQL expression

Cumulative Metrics

Running totals that accumulate over time periods.Grain-to-date:

mtd_net_revenue = Month-to-date net revenue (resets each month)
qtd_net_revenue = Quarter-to-date net revenue
ytd_net_revenue = Year-to-date net revenue

Trailing windows:

trailing_30d_revenue = Revenue for the last 30 days
trailing_90d_revenue = Revenue for the last 90 days

Calculation column shows: CUMULATIVE_SUM(measure_name)

Metric Categories

Category	Description	Example Metrics
`revenue`	Revenue, profit, and average order value metrics	`order_net_revenue`, `average_order_value_net`, `gross_profit`
`customer`	Customer counts and acquisition metrics	`new_customers`, `new_customer_order_count`, `total_customers`
`conversion`	Conversion rates and funnel metrics	`conversion_rate`, `add_to_cart_rate`, `checkout_completion_rate`
`marketing`	Ad spend and efficiency metrics	`total_ad_spend`, `marketing_efficiency_ratio`, `cost_per_click`
`product`	Product and inventory metrics	`total_products`, `total_skus`, `product_page_views`
`cumulative`	Time-based running totals	`mtd_net_revenue`, `ytd_order_count`, `trailing_30d_revenue`
`period_comparison`	Period-over-period comparisons	`order_count_mom_change`, `order_count_yoy_change`

Common Aliases Reference

The semantic layer supports abbreviated metric names for convenience. Always use the full descriptive name for new implementations.

Alias	Preferred Metric	Description
`aov`	`average_order_value_net`	Average Order Value
`mer`	`marketing_efficiency_ratio`	Marketing Efficiency Ratio (blended ROAS)
`cac`	`customer_acquisition_cost`	Customer Acquisition Cost
`cpc`	`cost_per_click`	Cost Per Click
`cpm`	`cost_per_thousand_impressions`	Cost Per Mille (thousand impressions)
`cpo`	`cost_per_order`	Cost Per Order
`roas`	`return_on_ad_spend`	Return on Ad Spend
`ctr`	`click_through_rate`	Click-Through Rate
`cvr`	`conversion_rate`	Conversion Rate
`cpa`	`customer_acquisition_cost`	Cost Per Acquisition (same as CAC)

Query the catalog with WHERE is_abbreviation = true to see all available aliases and their preferred metric names.

Best Practices

Use Full Metric Names

For new dashboards and queries, always use the full descriptive metric name (e.g., average_order_value_net instead of aov). This improves readability and maintainability.

Check Dependencies Before Filtering

When filtering metrics, check the dependent_metrics column to understand what underlying data will be affected. Ratio and derived metrics may behave unexpectedly with certain dimension filters.

Match Semantic Models for Joins

When combining metrics in a query, prefer metrics from the same semantic_model_name for consistent dimension availability. Mixing metrics from different semantic models may limit available dimensions.

Use Cumulative Metrics for Period Totals

For MTD, QTD, or YTD reporting, use the pre-built cumulative metrics instead of writing custom window functions. They handle edge cases like partial periods correctly.

Metric Definitions - Curated list of key metrics with descriptions
dim_data_dictionary - Table availability and column metadata

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

dim_semantic_metric_catalog

Use Cases

Columns

Example Queries

Find All Revenue Metrics

Discover Marketing Efficiency Metrics

Resolve an Abbreviated Metric Name

Find All Abbreviations for a Metric

List All Cumulative (MTD/QTD/YTD) Metrics

Find Metrics by Underlying Data Source

Explore Metric Dependencies

Get Canonical Metric Names (Resolving Aliases)

Metric Types Explained

Metric Categories

Common Aliases Reference

Best Practices

​Use Cases

​Columns

​Example Queries

​Find All Revenue Metrics

​Discover Marketing Efficiency Metrics

​Resolve an Abbreviated Metric Name

​Find All Abbreviations for a Metric

​List All Cumulative (MTD/QTD/YTD) Metrics

​Find Metrics by Underlying Data Source

​Explore Metric Dependencies

​Get Canonical Metric Names (Resolving Aliases)

​Metric Types Explained

​Metric Categories

​Common Aliases Reference

​Best Practices

​Related Resources

Use Cases

Columns

Example Queries

Find All Revenue Metrics

Discover Marketing Efficiency Metrics

Resolve an Abbreviated Metric Name

Find All Abbreviations for a Metric

List All Cumulative (MTD/QTD/YTD) Metrics

Find Metrics by Underlying Data Source

Explore Metric Dependencies

Get Canonical Metric Names (Resolving Aliases)

Metric Types Explained

Metric Categories

Common Aliases Reference

Best Practices

Related Resources