dim_semantic_metric_catalog

Published SourceMedium metric definitions, formulas, categories, and aliases. The catalog is regenerated by its producer. Consumer dashboards can use older copies, so compare definitions before assuming equivalence.

This is the source of truth for metric definitions. 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	Published predicate over the underlying model. Check has_filter as well: NULL with has_filter = true means the filter is not statically available, not that the metric is unfiltered.
`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 published date expression for the underlying model. Valid-order metrics on obt_orders use date(coalesce(order_processed_at_local_datetime, order_created_at_local_datetime)); ad metrics use date. Metrics spanning models can publish NULL: inspect each exact dependent metric and its supported time grains. A report using created-only or processed-only dates has a different date basis.
`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.
`value_format`	STRING	Display unit: currency, percent (a fraction, 1 = 100%), ratio, count, number, or identifier. Older publications may omit this field. It does not establish reporting currency or authorize currency conversion; count formatting must retain fractional attributed conversions.

Using definitions in a dashboard

The catalog has no store column. Apply authorized store scope to the business relations, and compare each published definition with the report’s actual population, filters, date basis and grain. A similar label is not a match. Executive Summary orders sum the published summary count. They are not the valid-order count on obt_orders. Revenue per summary order is a report-specific ratio, not valid-order AOV. Summary marketing efficiency and platform-reported ROAS use different inputs and attribution bases. Calculate each ratio from aggregated components, not an average of daily ratios. Resolve dependent_metrics as exact comma-separated metric identifiers. A missing dependency, duplicate definition, cycle, changed filter or changed date expression needs review. Do not silently replace a saved calculation. Custom reports can remain explicitly report-specific. value_format is optional for consumers of older publications. Validate that it agrees with the reviewed measure; an inaccessible field is different from an absent field. Keep exact values for arithmetic and exports, and round only for display. catalog_updated_at records a catalog build, not business-data freshness or numerical reconciliation. The dashboard template displays calculation and filter text as documentation. Its warehouse queries remain developer-authored and parameterized; it does not execute metadata strings as SQL.

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), over valid orders
order_count = COUNT(sm_order_key), over valid orders
new_customers = COUNT(DISTINCT sm_customer_key), over customers whose first valid order falls in the period, on Online DTC, Amazon and TikTok Shop

Calculation column shows: the aggregation, such as SUM(column), COUNT(column) or COUNT(DISTINCT column), followed by WHERE [filter applied] when the metric is filtered. The filter itself is in filter_condition.

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

Using definitions in a dashboard

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

​Using definitions in a dashboard

​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

Using definitions in a dashboard

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