Definitions

The definitions live in metrics/leads.yml (MetricFlow style): one semantic model, entity lead (key lead_id) in table leads, months cut at midnight Europe/Zurich.

Time dimensions: created_at, appointment_at, contract_signed_at, invoiced_at. Dimensions: channel, canton, product, broker, appointment_status. Measures: lead_count = count_distinct(lead_id), revenue_chf = sum(invoice_amount_chf).

When the app starts it checks every name against these lists and against ^[a-z_][a-z0-9_]*$. Filters are structured (dimension, operator, values), never raw SQL. If anything is wrong, the app refuses to start and lists the problems.

# Metric layer for BI cockpit v1.
#
# MetricFlow-style: one semantic model (entity, table, columns, dimensions, measures)
# and named metrics on top. Every number in the cockpit is defined here, once.
#
# Rules the app checks when it loads this file (it refuses to start otherwise):
# - every name matches ^[a-z_][a-z0-9_]*$ and is declared before it is used;
# - dimensions, time dimensions and measures may only use the columns listed under `columns`;
# - filters are structured ({dimension, op, values}), never raw SQL; op is in, eq or not_in;
# - a filter on a dimension with a `values` list may only use those values. The list also lets the
#   referee say what a filter leaves out ("appointment cancelled or no-show").
#
# `noun` names one row of the metric ("leads", "appointments"). Each metric's `time_basis` says
# which date puts a lead into a month. That is the main
# reason the departments' numbers differ, and what the referee explains.

semantic_model:
  name: leads
  description: Insurance leads of a Swiss lead business, April to October 2026.
  table: leads
  timezone: Europe/Zurich
  entity:
    name: lead
    key: lead_id

  columns:
    - { name: lead_id, type: integer, label: Lead }
    - { name: created_at, type: timestamptz, label: Created }
    - { name: channel, type: text, label: Channel }
    - { name: canton, type: text, label: Canton }
    - { name: product, type: text, label: Product }
    - { name: broker, type: text, label: Broker }
    - { name: appointment_at, type: timestamptz, label: Appointment }
    - { name: appointment_status, type: text, label: Status }
    - { name: contract_signed_at, type: timestamptz, label: Contract }
    - { name: invoiced_at, type: timestamptz, label: Invoiced }
    - { name: invoice_amount_chf, type: numeric, label: Amount (CHF) }

  # `missing`, `before` and `after` are the plain words the referee uses for a row whose date is
  # empty, or falls before / after the month ({month} is filled in).
  time_dimensions:
    - name: created_at
      label: creation date
      missing: no creation date
      before: created before {month}
      after: created after {month}
    - name: appointment_at
      label: appointment date
      missing: no appointment yet
      before: appointment before {month}
      after: appointment after {month}
    - name: contract_signed_at
      label: contract date
      missing: no contract yet
      before: signed before {month}
      after: signed after {month}
    - name: invoiced_at
      label: invoice date
      missing: not invoiced yet
      before: invoiced before {month}
      after: invoiced after {month}

  dimensions:
    - { name: channel, label: Channel }
    - { name: canton, label: Canton }
    - { name: product, label: Product }
    - { name: broker, label: Broker }
    - name: appointment_status
      label: Appointment status
      values: [scheduled, held, cancelled, no_show]
      value_labels: { no_show: no-show }
      blank: no appointment   # shown for an empty status
      phrase: "appointment {values}"   # e.g. "appointment cancelled or no-show"

  measures:
    - name: lead_count
      label: Leads
      agg: count_distinct
      expr: lead_id
      unit: count
    - name: revenue_chf
      label: Revenue
      agg: sum
      expr: invoice_amount_chf
      unit: chf

owners:
  - { name: marketing, label: Marketing }
  - { name: operations, label: Operations }
  - { name: sales, label: Sales }
  - { name: finance, label: Finance }

metrics:
  - name: leads_marketing
    label: Leads
    noun: leads
    description: Every lead, counted on the day it arrived.
    measure: lead_count
    time_basis: created_at
    owner: marketing
    certified: true

  - name: appointments_operations
    label: Appointments
    noun: appointments
    description: Every broker appointment that is scheduled or held, counted on the appointment day.
    measure: lead_count
    time_basis: appointment_at
    filter:
      dimension: appointment_status
      op: in
      values: [scheduled, held]
    owner: operations
    certified: true

  - name: contracts_sales
    label: Contracts signed
    noun: contracts
    description: Every signed contract, counted on the day it was signed.
    measure: lead_count
    time_basis: contract_signed_at
    owner: sales
    certified: false

  - name: leads_invoiced_finance
    label: Leads invoiced
    noun: invoiced leads
    description: Every lead billed to a broker, counted on the invoice day.
    measure: lead_count
    time_basis: invoiced_at
    owner: finance
    certified: true

  - name: revenue_finance
    label: Revenue
    noun: invoiced leads
    description: The amount billed to brokers in CHF, counted on the invoice day.
    measure: revenue_chf
    time_basis: invoiced_at
    owner: finance
    certified: true