Skip to main content

​
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

​
How Time to Conversion Is Calculated

​
Conceptually

Time to conversion is computed as the difference between: timestamp_conv - timestamp_events

​
Source Data

This analysis uses the following fields from the Log Level MTA schema:
FieldDescription
timestamp_eventsTimestamp of the marketing touchpoint
timestamp_convTimestamp of the conversion event
first_touchFlag indicating whether the touchpoint was the first in the conversion path
tier_1, tier_2, tier_3Channel hierarchy dimensions
dateEvent date (used for filtering)

​
SQL Operators

The exact timestamp function depends on your data warehouse:
WarehouseFunction
Snowflakedatediff() or timestampdiff()
Redshiftdatediff()
BigQuerydate_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;