Skip to main content

Conversion metrics

Conversion metrics let you measure how often one event leads to another for a specific entity within a defined time window.

For example, you can track how often a user (entity) who visits your site (base event) makes a purchase (conversion event) within 7 days (time window). To set this up, you’ll specify both the time range and the entity that links/joins the two events.

Conversion metrics are different from ratio metrics because you need to include an entity in the pre-aggregated join.

Parameters​

The specification for conversion metrics is as follows:

(Applies to dbt v1.12 and later)
ParameterDescriptionRequiredType
nameThe name of the metric.RequiredString
descriptionThe description of the metric.OptionalString
typeThe type of metric. Set as conversion for conversion metrics.RequiredString
labelThe display label for the metric. Accepts plain text, spaces, and quotes.OptionalString
configConfiguration settings for the metric.OptionalDict
config.groupThe group the metric belongs to.OptionalString
config.tagsTags associated with the metric.OptionalList
config.metaMetadata for the metric.OptionalDict
entityThe entity for each conversion event.RequiredString
calculationMethod of calculation. Either conversion_rate or conversions. Defaults to conversion_rate.OptionalString
base_metricThe base metric name or configuration for the conversion event. Can be a string (metric name) or a dict (for additional customization).RequiredString or Dict
base_metric.nameThe name of the base metric (when using dict format).RequiredString
base_metric.filterFilter to apply to the base metric (when using dict format).OptionalString
base_metric.aliasAlias for the base metric (when using dict format).OptionalString
conversion_metricThe conversion metric name or configuration. Can be a string (metric name) or a dict (for additional customization).RequiredString or Dict
conversion_metric.nameThe name of the conversion metric (when using dict format).RequiredString
conversion_metric.filterFilter to apply to the conversion metric (when using dict format).OptionalString
conversion_metric.aliasAlias for the conversion metric (when using dict format).OptionalString
windowThe time window for the conversion event (such as 7 days, 1 week, 3 months). Defaults to infinity.OptionalString
constant_propertiesList of properties to hold constant between base and conversion events. Can be a dimension or entity.OptionalList
constant_properties.base_propertyThe dimension or entity of the semantic model linked to the base_metric.RequiredString
constant_properties.conversion_propertyThe dimension or entity of the semantic model linked to the conversion_metric.RequiredString

Refer to additional settings to learn how to customize conversion metrics with settings for null values, calculation type, and constant properties.

The following code example displays the complete specification for conversion metrics and details how they're applied:

(Applies to dbt v1.12 and later)
models/file_name.yml
models:
  - name: your_model_name
    semantic_model:
      enabled: true
.... rest of configs....
    metrics:
      - name: my_conversion_metric
        description: "Tracks how often a base event leads to a conversion event for an entity"
        label: "My conversion metric"
        type: conversion
        entity: my_primary_entity   # Required; the entity the conversion is tracked for
        calculation: conversion_rate       # Optional; conversion_rate | conversions
        base_metric: my_base_event_metric  # Required; metrics defined in another semantic model
        conversion_metric: my_conversion_event_metric  # Required; metrics defined in another semantic model
        window: 7 days   # Optional; defines the time window for conversion
        constant_properties:  # Optional; list of constant properties
          - base_property: my_dimension_or_entity
            conversion_property: my_dimension_or_entity

Conversion metric example​

The following example will measure conversions from website visits (VISITS table) to order completions (BUYS table) and calculate a conversion metric for this scenario step by step.

Suppose you have two semantic models, VISITS and BUYS:

  • The VISITS table represents visits to an e-commerce site.
  • The BUYS table represents someone completing an order on that site.

The underlying tables look like the following:

VISITS
Contains user visits with USER_ID and REFERRER_ID.

DSUSER_IDREFERRER_ID
2020-01-01bobfacebook
2020-01-04bobgoogle
2020-01-07bobamazon

BUYS
Records completed orders with USER_ID and REFERRER_ID.

DSUSER_IDREFERRER_ID
2020-01-02bobfacebook
2020-01-07bobamazon

Next, define a conversion metric as follows:

(Applies to dbt v1.12 and later) 
models:
  - name: your_model_name
    semantic_model:
      enabled: true
.... rest of configs....
    metrics:
      - name: visit_to_buy_conversion_rate_7d
        description: "Conversion rate from visiting to transaction in 7 days"
        type: conversion
        label: Visit to buy conversion rate (7-day window)
        entity: user
        calculation: conversion_rate
        base_metric:
          name: visits
          filter: {{ Dimension('visits__referrer_id') }} = 'facebook'
        conversion_metric: buys
        window: 7 days

To calculate the conversion, link the BUYS event to the nearest VISITS event (or closest base event). The following steps explain this process in more detail:

Step 1: Join VISITS and BUYS​

This step joins the BUYS table to the VISITS table and gets all combinations of visits-buys events that match the join condition where buys occur within 7 days of the visit (any rows that have the same user and a buy happened at most 7 days after the visit).

The SQL generated in these steps looks like the following:

select
  v.ds,
  v.user_id,
  v.referrer_id,
  b.ds,
  b.uuid,
  1 as buys
from visits v
inner join (
    select *, uuid_string() as uuid from buys -- Adds a uuid column to uniquely identify the different rows
) b
on
v.user_id = b.user_id and v.ds <= b.ds and v.ds > b.ds - interval '7 days'

The dataset returns the following (note that there are two potential conversion events for the first visit):

V.DSV.USER_IDV.REFERRER_IDB.DSUUIDBUYS
2020-01-01bobfacebook2020-01-02uuid11
2020-01-01bobfacebook2020-01-07uuid21
2020-01-04bobgoogle2020-01-07uuid21
2020-01-07bobamazon2020-01-07uuid21

Step 2: Refine with window function​

Instead of returning the raw visit values, use window functions to link conversions to the closest base event. You can partition by the conversion source and get the first_value ordered by visit ds, descending to get the closest base event from the conversion event:

select
  first_value(v.ds) over (partition by b.ds, b.user_id, b.uuid order by v.ds desc) as v_ds,
  first_value(v.user_id) over (partition by b.ds, b.user_id, b.uuid order by v.ds desc) as user_id,
  first_value(v.referrer_id) over (partition by b.ds, b.user_id, b.uuid order by v.ds desc) as referrer_id,
  b.ds,
  b.uuid,
  1 as buys
from visits v
inner join (
    select *, uuid_string() as uuid from buys
) b
on
v.user_id = b.user_id and v.ds <= b.ds and v.ds > b.ds - interval '7 day'

The dataset returns the following:

V.DSV.USER_IDV.REFERRER_IDB.DSUUIDBUYS
2020-01-01bobfacebook2020-01-02uuid11
2020-01-07bobamazon2020-01-07uuid21
2020-01-07bobamazon2020-01-07uuid21
2020-01-07bobamazon2020-01-07uuid21

This workflow links the two conversions to the correct visit events. Due to the join, you end up with multiple combinations, leading to fanout results. After applying the window function, duplicates appear.

To resolve this and eliminate duplicates, use a distinct select. The UUID also helps identify which conversion is unique. The next steps provide more detail on how to do this.

Step 3: Remove duplicates​

Instead of regular select used in the Step 2, use a distinct select to remove the duplicates:

select distinct
  first_value(v.ds) over (partition by b.ds, b.user_id, b.uuid order by v.ds desc) as v_ds,
  first_value(v.user_id) over (partition by b.ds, b.user_id, b.uuid order by v.ds desc) as user_id,
  first_value(v.referrer_id) over (partition by b.ds, b.user_id, b.uuid order by v.ds desc) as referrer_id,
  b.ds,
  b.uuid,
  1 as buys
from visits v
inner join (
    select *, uuid_string() as uuid from buys
) b
on
v.user_id = b.user_id and v.ds <= b.ds and v.ds > b.ds - interval '7 day';

The dataset returns the following:

V.DSV.USER_IDV.REFERRER_IDB.DSUUIDBUYS
2020-01-01bobfacebook2020-01-02uuid11
2020-01-07bobamazon2020-01-07uuid21

You now have a dataset where every conversion is connected to a visit event. To proceed:

  1. Sum up the total conversions in the "conversions" table.
  2. Combine this table with the "opportunities" table, matching them based on group keys.
  3. Calculate the conversion rate.

Step 4: Aggregate and calculate​

Now that you’ve tied each conversion event to a visit, you can calculate the aggregated conversions and opportunities (Applies to dbt v1.12 and later) simple metric. Then, you can join them to calculate the actual conversion rate. The SQL to calculate the conversion rate is as follows:

select
  coalesce(subq_3.metric_time__day, subq_13.metric_time__day) as metric_time__day,
  cast(max(subq_13.buys) as double) / cast(nullif(max(subq_3.visits), 0) as double) as visit_to_buy_conversion_rate_7d
from ( -- base
  select
    metric_time__day,
    sum(visits) as visits
  from (
    select
      date_trunc('day', first_contact_date) as metric_time__day,
      1 as visits
    from visits
  ) subq_2
  group by
    metric_time__day
) subq_3
full outer join ( -- conversion
  select
    metric_time__day,
    sum(buys) as buys
  from (
    -- ...
    -- The output of this subquery is the table produced in Step 3. The SQL is hidden for legibility.
    -- To see the full SQL output, add --explain to your conversion metric query. 
  ) subq_10
  group by
    metric_time__day
) subq_13
on
  subq_3.metric_time__day = subq_13.metric_time__day
group by
  metric_time__day

Additional settings​

Use the following additional settings to customize your conversion metrics:

  • Null conversion values: Set null conversions to zero using fill_nulls_with. Refer to Fill null values for metrics for more info.
  • Calculation type: Choose between showing raw conversions or conversion rate.
  • Constant property: Add conditions for specific scenarios to join conversions on constant properties.

To return zero in the final data set, you can set the value of a null conversion event to zero instead of null. You can add the fill_nulls_with parameter to your conversion metric definition like this:

(Applies to dbt v1.12 and later) 
metrics:
  - name: visits
    type: simple
    agg: count
    expr: visit_id
    fill_nulls_with: 0 # set null conversion values to zero in a simple metric
      
  - name: buys
    type: simple
    agg: count
    expr: purchase_id
    fill_nulls_with: 0
      
  - name: visit_to_buy_conversion_rate_7_day_window
    description: "Conversion rate from viewing a page to making a purchase"
    type: conversion
    label: Visit to buy conversion rate (7 day window)
    entity: user
    calculation: conversions
    base_metric: visits
    conversion_metric: buys
    window: 7 days

This will return the following results:

Conversion metric with fill nulls with parameterConversion metric with fill nulls with parameter

Refer to Fill null values for metrics for more info.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading