> ## Documentation Index
> Fetch the complete documentation index at: https://data-foundation.rockerbox.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```sql theme={null}
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

```sql theme={null}
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;
```