Time to Conversion Analysis from MTA
This page provides sample queries that enable you to compute time to conversion across varying levels of granularity.
Timestamp Comparison SQL Operator
You can compute the time to conversion between a marketing touchpoint and the conversion event by comparing the difference between the timestamp_events and the timestamp_conv fields in the log level MTA schema. You can then aggregate this metric at varying levels of granularity (e.g., channel, tactic, campaign level), over different time horizons, and based on the position in the user's path to conversion.
The exact timestamp comparison operator syntax will vary depending on your DWH provider:
- Snowflake: supports datediff or timestampdiff
- Redshift: supports datediff
- BigQuery: supports date_diff or timestmap_diff
Example 1: Time to convert for first touch vs any touch in the path to conversion
Compute time to conversion depending on the position in the path to conversion -- first touchpoint vs. any touchpoint.
select
--group by dimensions: add additional dimensions to increase granularity -- tier_2, tier_3, tier_4, tier_5
tier_1,
--time to conversion metrics
avg(case when first_touch = 1 then timestampdiff(day, timestamp_events, timestamp_conv) end) as first_touch_ttc_days,
avg(datediff(day, timestamp_events, timestamp_conv)) as any_touch_ttc_days
from {log_level_mta_table} --insert your qualified database table name
where
date >= current_date - INTERVAL '30 DAY'
group by 1
order by 1
;Example 2: Count of marketing touchpoints that fall within time to conversion bins
--count of marketing touchpoints within time to conversion bins
select
--group by dimensions: add additional dimensions to increase granularity -- tier_2, tier_3, tier_4, tier_5
tier_1,
--time to conversion metrics
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 1 then 1 else 0 end) as "1D",
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 7 then 1 else 0 end) as "7D",
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 14 then 1 else 0 end) as "14D",
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 21 then 1 else 0 end) as "21D",
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 30 then 1 else 0 end) as "30D",
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 60 then 1 else 0 end) as "60D",
sum(case when datediff(day, timestamp_events, timestamp_conv) between 0 and 90 then 1 else 0 end) as "90D",
count(*) as ">90D",
from {log_level_mta_table} --insert your qualified database table name
where
date >= current_date - INTERVAL '30 DAY'
group by 1
;