Skip to main content
SourceMedium supports multiple “levels” of querying, depending on what you’re trying to do:
  • Report tables (rpt_*): fastest for common dashboard-style questions
  • One Big Tables (obt_*): flexible analysis with pre-modeled joins and business logic
  • Semantic metrics: a single “metric definition layer” so the same metrics power dashboards and saved queries
This page focuses on the semantic metric layer and how it connects to:

​
Metric definitions (single source of truth)

Metric definitions are documented in: In the dbt project, semantic layer definitions live under models/semantic/ (semantic models + metrics + saved queries).

​
When to use semantic metrics vs raw tables

Use semantic metrics when you need:
  • Consistent metric logic across tools (dashboards, ad-hoc analysis, exports)
  • Standardized naming (no “two versions” of CAC/ROAS/net revenue)
  • The ability to group metrics by dimensions without re-writing business logic
Use raw tables (OBT/RPT) when you need:
  • Custom fields not covered by metrics
  • Deep row-level inspection and debugging
The semantic layer includes a set of saved queries designed to match common “dashboard tile” questions. If you’re using the Query Library, these saved queries also map cleanly to sections/recipes.
Saved queryBest forCommonly maps toQuery Library section
executive_metrics_dailyCore order KPIs by dayExecutive SummaryOrders & Revenue
simple_executive_metrics_dailyFast channel comparisons (pre-aggregated)Executive Summary / Marketing OverviewMarketing & Ads / Orders & Revenue
marketing_performance_dailyAd spend + platform-reported performanceMarketing Overview / platform modulesMarketing & Ads
revenue_breakdown_dailyFull daily revenue breakdown (incl. discounts/refunds)Executive SummaryOrders & Revenue
revenue_cumulative_dailyMTD/QTD/YTD + trailing periodsExecutive SummaryOrders & Revenue
customer_metrics_dailyNew vs repeat behavior and revenueLTV & Retention / New Customer AnalysisCustomers & Retention / LTV & Retention
product_performance_dailyProduct catalog & mix monitoringProduct PerformanceProducts
simple_conversion_metrics_dailyHigh-level conversion rates by channelTraffic Deep Dive / ExecutiveFunnel
channel_conversion_metrics_dailyConversion + MER by channelMarketing Overview / ExecutiveFunnel / Marketing & Ads
detailed_conversion_metrics_dailyUTM-level conversion analysisTraffic Deep DiveFunnel
conversion_metrics_detailed_dailyLanding page + UTM conversion deep diveTraffic Deep DiveFunnel
“Dashboard tile” mappings above are intended as practical starting points, not hard guarantees. Not every brand has every integration, and coverage can vary by source system.

​
How this relates to the SQL Query Library

If you prefer writing SQL directly in BigQuery, the Query Library recipes are pre-built around the same core concepts:
  • Valid order filtering (is_order_sm_valid = TRUE)
  • Stable “analysis tables” (obt_* and rpt_*)
  • Lowercased categorical groupings for safe aggregation
Start here:

​
(Optional) Querying via dbt Semantic Layer

If you have dbt Semantic Layer tooling enabled (e.g., MetricFlow), you can query metrics using a CLI interface instead of writing SQL. 
# Example (dbt CLI / Semantic Layer):
dbt sl query order_net_revenue --group-by "TimeDimension('metric_time', 'day')" --limit 30
If you don’t have Semantic Layer tooling enabled, use the SQL Query Library + OBT/RPT tables instead.