Overview

What is the semantic layer

The semantic layer is a governed set of analytical models. It sits between your raw data and your BI tools and standardizes metric definitions. It keeps dashboards consistent across teams and tools.

Key concepts

Models

Each model is queryable like a table. Each row represents one “entity” in Second Door. Models expose:

• Dimensions: fields you can SELECT, WHERE, and GROUP BY.

• Measures: pre-built aggregations like counts, rates, and averages.

• Segments: pre-built filters you enable with a boolean predicate.

Dimensions

Dimensions are attributes like source, location_name, or created_at. Use them to slice results and filter the dataset.

Measures

Measures are curated calculations. They hide joins, filters, and aggregation logic. Examples: total lead count, conversion rate, average time-to-first-touch.

Segments

Segments are common filters shipped with the model. You apply them in WHERE, like lead.active_sql_leads = true.

How to query

Use MEASURE() for measures. Group by the dimensions you select.

SELECT
  lead.source,
  MEASURE(lead.count),
  MEASURE(lead.percentage_closed_won)
FROM lead
WHERE lead.created_at >= '2024-01-01'
GROUP BY 1;

Segments work like boolean dimensions:

SELECT
  lead.location_name,
  MEASURE(lead.avg_time_to_first_touch),
  MEASURE(lead.count)
FROM lead
WHERE lead.opened_at >= '2024-01-01'
  AND lead.active_sql_leads = true
GROUP BY 1;

Measures must be wrapped in an aggregate function.

Use MEASURE() as a universal option.

Most BI tools will also work if they generate the matching aggregate (SUM(), COUNT(), etc).

If the aggregation type doesn’t match the measure, you’ll get: Measure aggregation type doesn't match.

When to use it

Use the semantic layer when you want consistent metrics. It’s the best option for dashboards and shared reporting. Use Export Views when you want flat, row-level data. Export views are better for ingestion and warehouse syncs.

Available models

• lead

• nps_response

• nps_response_trend

• More Soon