Skip to main content

Conversion metrics

Conversion metrics allow you to define when a base event and a subsequent conversion event happen for a specific entity within some time range.

For example, using conversion metrics allows you to track how often a user (entity) completes a visit (base event) and then makes a purchase (conversion event) within 7 days (time window). You would need to add a time range and an entity to join.

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:

ParameterDescriptionType
nameThe name of the metric.Required
descriptionThe description of the metric.Optional
typeThe type of metric (such as derived, ratio, and so on.). In this case, set as 'conversion'Required
labelDisplayed value in downstream tools.Required
type_paramsSpecific configurations for each metric type.Required
conversion_type_paramsAdditional configuration specific to conversion metrics.Required
entityThe entity for each conversion event.Required
calculationMethod of calculation. Either conversion_rate or conversions. Defaults to conversion_rate.Optional
base_measureA list of base measure inputsRequired
base_measure:nameThe base conversion event measure.Required
base_measure:fill_nulls_withSet the value in your metric definition instead of null (such as zero).Optional
base_measure:join_to_timespineBoolean that indicates if the aggregated measure should be joined to the time spine table to fill in missing dates. Default false.Optional
conversion_measureA list of conversion measure inputs.Required
conversion_measure:nameThe base conversion event measure.Required
conversion_measure:fill_nulls_withSet the value in your metric definition instead of null (such as zero).Optional
conversion_measure:join_to_timespineBoolean that indicates if the aggregated measure should be joined to the time spine table to fill in missing dates. Default false.Optional
windowThe time window for the conversion event, such as 7 days, 1 week, 3 months. Defaults to infinity.Optional
constant_propertiesList of constant properties.Optional
base_propertyThe property from the base semantic model that you want to hold constant.Optional
conversion_propertyThe property from the conversion semantic model that you want to hold constant.Optional

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:

metrics:
  - name: The metric name # Required
    description: The metric description # Optional
    type: conversion # Required
    label: YOUR_LABEL # Required
    type_params: # Required
      conversion_type_params: # Required
        entity: ENTITY # Required
        calculation: CALCULATION_TYPE # Optional. default: conversion_rate. options: conversions(buys) or conversion_rate (buys/visits), and more to come.
        base_measure: 
          name: The name of the measure # Required
          fill_nulls_with: Set the value in your metric definition instead of null (such as zero) # Optional
          join_to_timespine: true/false # Boolean that indicates if the aggregated measure should be joined to the time spine table to fill in missing dates. Default `false`. # Optional
        conversion_measure:
          name: The name of the measure # Required
          fill_nulls_with: Set the value in your metric definition instead of null (such as zero) # Optional
          join_to_timespine: true/false # Boolean that indicates if the aggregated measure should be joined to the time spine table to fill in missing dates. Default `false`. # Optional
        window: TIME_WINDOW # Optional. default: infinity. window to join the two events. Follows a similar format as time windows elsewhere (such as 7 days)
        constant_properties: # Optional. List of constant properties default: None
          - base_property: DIMENSION or ENTITY # Required. A reference to a dimension/entity of the semantic model linked to the base_measure
            conversion_property: DIMENSION or ENTITY # Same as base above, but to the semantic model of the conversion_measure

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:

- 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)
  type_params:
    conversion_type_params:
      base_measure:
        name: visits
        fill_nulls_with: 0
      conversion_measure: sellers
        name: sellers
      entity: user
      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 measures. 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 measure
  select
    metric_time__day,
    sum(visits) as mqls
  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 measure
  select
    metric_time__day,
    sum(buys) as sellers
  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:

- 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 Seller Conversion Rate (7 day window)
  type_params:
    conversion_type_params:
      calculation: conversions
      base_measure:
        name: visits
      conversion_measure: 
        name: buys
        fill_nulls_with: 0
      entity: user
      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.

0
Edit this page