Time to Conversion

This guide explains how to compute time to conversion using the Log Level MTA dataset.

When to Use This Analysis

Time to conversion analysis is particularly useful for:

• Setting retargeting windows
• Determining attribution lookback windows
• Evaluating upper-funnel vs lower-funnel performance
• Understanding purchase latency

Source Data

This analysis uses the following fields from the Log Level MTA schema:

Field	Description
timestamp_events	Timestamp of the marketing touchpoint
timestamp_conv	Timestamp of the conversion event
first_touch	Flag indicating whether the touchpoint was the first in the conversion path
tier_1, tier_2, tier_3	Channel hierarchy dimensions
date	Event date (used for filtering)

Time to conversion is computed as the difference between:

timestamp_conv - timestamp_events

The exact timestamp function depends on your data warehouse:

Warehouse	Function
Snowflake	datediff() or timestampdiff()
Redshift	datediff()
BigQuery	date_diff() or timestamp_diff()

---

Example 1: Average Time to Conversion

First Touch vs Any Touch

This query computes:

• Average time to convert from the first touch
• Average time to convert from any touchpoint
• Grouped by channel (tier_1)

💡 Note: Time to convert is typically expressed in days, but can be calculated in hours or minutes if desired.

Snowflake Example

select
    -- Grouping dimension
    tier_1,

    -- Average time to conversion (First Touch only)
    avg(
        case 
            when first_touch = 1 
            then datediff(day, timestamp_events, timestamp_conv)
        end
    ) as first_touch_time_to_convert_days,

    -- Average time to conversion (Any Touch)
    avg(
        datediff(day, timestamp_events, timestamp_conv)
    ) as any_touch_time_to_convert_days

from <database>.<schema>.<log_level_mta_table>
where 
    date >= dateadd('day', -30, current_date)
group by 1
order by 1;

How to customize

• Add tier_2, tier_3, campaign, or other dimensions to increase granularity
• Change day to hour if you want higher precision
• Expand the date filter to analyze longer time windows

---

Example 2: Time to Convert Bins

This query counts the number of touchpoints that fall into predefined time-to-conversion bins.

Typical reporting buckets:

• 0–7 days
• 8–14 days
• 15–30 days
• 31–60 days
• 61–90 days
• Greater than 90 days

Snowflake Example

select
    tier_1,

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 0 and 7 
        then 1 else 0 
    end) as "0_7D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 8 and 14 
        then 1 else 0 
    end) as "8_14D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 15 and 30 
        then 1 else 0 
    end) as "15_30D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 31 and 60 
        then 1 else 0 
    end) as "31_60D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) between 61 and 90 
        then 1 else 0 
    end) as "61_90D",

    sum(case 
        when datediff(day, timestamp_events, timestamp_conv) > 90 
        then 1 else 0 
    end) as ">90D"

from <database>.<schema>.<log_level_mta_table>
where date >= dateadd('day', -30, current_date)
group by 1
order by 1;