How to define a conversion metric in dbt (funnel - signup to purchase)

A conversion metric measures how often one event leads to another for the same entity within a time window: a signup followed by an order within 7 days, a trial followed by a subscription. Unlike a ratio, it links each conversion to the base event of the same customer that came before it, so the two events must share an entity.

Copy and paste

The base and conversion events live in different semantic models (signups on customers, the order metrics on orders), so the metric goes under a top-level metrics: key:

# models/metrics/conversion.yml
metrics:
  - name: signup_to_order_7d
    label: Signup to order within 7 days
    type: conversion
    entity: customer                    # the entity both events share
    calculation: conversion_rate        # write it, even though it is the default
    base_metric: signups                # count of customers by signup_date
    conversion_metric: ordering_customers   # count_distinct of customer_id on orders
    window: 7 days
  - name: signup_to_order_30d
    type: conversion
    entity: customer
    calculation: conversion_rate
    base_metric: signups
    conversion_metric: ordering_customers
    window: 30 days
  - name: signups_converted_7d
    type: conversion
    entity: customer
    calculation: conversions            # the number, not the rate
    base_metric: signups
    conversion_metric: ordering_customers
    window: 7 days

mf query --metrics signups,signups_converted_7d,signup_to_order_7d,signup_to_order_30d \
  --group-by customer__segment

customer__segment      signups    signups_converted_7d    signup_to_order_7d    signup_to_order_30d
-------------------  ---------  ----------------------  --------------------  ---------------------
Enterprise                   2                       2                     1                      1
SMB                          4                       1                  0.25                   0.75

Six customers signed up. Ben and Dario (Enterprise) ordered within a week. Among SMB customers, Anna ordered after five days, Chiara and Eva after two weeks, and Fabio never did.

What conversion_rate counts

The rate is conversions divided by base events, and a conversion is a row of the conversion metric linked to an earlier base event of the same customer. If the conversion metric counts orders, every order counts:

metric_time__year      signups    with order_count    with ordering_customers
-------------------  ---------  ------------------  -------------------------
2025-01-01T00:00:00          4                4.25                          1
2026-01-01T00:00:00          2                   1                        0.5

Without a window, the four 2025 customers placed 17 orders between them, and the “conversion rate” based on order_count is 4.25. In 2026 it shows 100% although only Eva of the two new customers ordered: she placed two orders. For “share of customers who converted”, make the conversion metric count customers, as ordering_customers does with agg: count_distinct on customer_id, or count only first orders.

Two things that broke in testing

• Leaving out calculation. The documentation says it defaults to conversion_rate, but on dbt Core 1.12.6 a conversion metric without it crashed the parse with AttributeError: 'NoneType' object has no attribute 'lower'. Writing calculation: conversion_rate explicitly fixes it.
• Querying without a group-by. mf query --metrics signup_to_order_7d with no --group-by produced invalid SQL on DuckDB (Parser Error: syntax error at or near ","). Grouping by anything, such as metric_time__year, worked. Check the ungrouped case on your warehouse before a BI tool tries it.

Window and timing

• window accepts values such as 7 days, 1 week, 3 months. Without it, any later conversion counts, however late.
• The conversion is credited to the base event’s date: a signup in December converted in January counts in December. Recent periods keep growing until their window has passed, so the last 7 days always look worse than they will be.
• Groups with no conversions return NULL rather than 0: in 2026 nobody ordered within 7 days, and signup_to_order_7d is empty. fill_nulls_with: 0 on the conversion metric was accepted but did not change that: neither dbt Core 1.12 nor dbt v2 kept it in the semantic manifest. A derived metric does:

  - name: signup_to_order_7d_or_zero
    type: derived
    expr: "coalesce(signup_to_order_7d, 0)"
    input_metrics:
      - name: signup_to_order_7d

Filters and constant properties

base_metric and conversion_metric also accept a dict with name and filter, to restrict either event, for example signups from one campaign. constant_properties requires a dimension to match between the two events, such as the same product or plan: a trial of plan A converts only with a subscription to plan A.

Tested with dbt Core 1.12.6 and MetricFlow 0.213 on DuckDB.