Analyzing Funnel Correlation

Analyzing funnel correlation

Correlation compares two groups of people who entered a funnel: people who completed the last step (success) and people who did not (failure). For each candidate event or property value, build a contingency table and compute the odds ratio. An odds ratio above 1 means the signal goes with conversion. Below 1 means it goes with drop-off. The further the ratio is from 1, the stronger the correlation.

Correlation is not causation. Tell the user that, and suggest an experiment to test a strong signal.

Workflow

1. Get the funnel definition: the step events, the date range, and the conversion window. If the user names a saved insight, read it with insight-get. Confirm the step events with read-data-schema.

2. Run the funnel with query-funnel and note the total entrants and converters. The SQL below must give totals in the same range. If not, fix the step filters first.

3. Run the event correlation query below with execute-sql.

4. If the user asks about person attributes, run the property variant.

5. Report the top success signals and the top failure signals with their counts. To show who is behind a signal, use query-funnel-actors or filter the funnel by that event or property.

Event correlation

Replace the step events, the date range, the conversion window (seconds in windowFunnel, days in the INTERVAL), and the step count. Add more event = '...' conditions to windowFunnel for more steps, and set steps = N to the last step.

WITH
    funnel_actors AS (
        SELECT
            person_id AS actor_id,
            windowFunnel(1209600)(toDateTime(timestamp), event = 'step one', event = 'step two') AS steps,
            arraySort(groupArrayIf((toUnixTimestamp(timestamp), event = 'step two'), event IN ('step one', 'step two'))) AS step_events,
            arrayReverseFill(t -> t > 0, arrayMap(x -> if(x.2, x.1, 0), step_events)) AS next_final,
            arrayFirstIndex((x, t) -> NOT x.2 AND t > 0 AND t <= x.1 + 1209600, step_events, next_final) AS attempt,
            toDateTime(if(attempt > 0, step_events[attempt].1, arrayFirst(x -> NOT x.2, step_events).1)) AS first_timestamp,
            toDateTime(next_final[greatest(attempt, 1)]) AS final_timestamp
        FROM events
        WHERE event IN ('step one', 'step two')
            AND timestamp >= now() - INTERVAL 30 DAY
        GROUP BY actor_id
        HAVING steps >= 1
    ),
    totals AS (
        SELECT countIf(steps = 2) AS success_total, countIf(steps < 2) AS failure_total
        FROM funnel_actors
    ),
    per_event AS (
        SELECT
            e.event AS name,
            countDistinctIf(a.actor_id, a.steps = 2) AS success_count,
            countDistinctIf(a.actor_id, a.steps < 2) AS failure_count
        FROM events AS e
        JOIN funnel_actors AS a ON e.person_id = a.actor_id
        WHERE e.timestamp >= now() - INTERVAL 30 DAY
            AND e.timestamp > a.first_timestamp
            AND e.timestamp < if(a.steps = 2, a.final_timestamp, a.first_timestamp + INTERVAL 14 DAY)
            AND e.event NOT IN ('step one', 'step two')
        GROUP BY name
    )
SELECT
    name,
    success_count,
    failure_count,
    success_total,
    failure_total,
    ((success_count + 1) * (failure_total - failure_count + 1))
        / ((success_total - success_count + 1) * (failure_count + 1)) AS odds_ratio
FROM per_event
CROSS JOIN totals
WHERE success_count + failure_count >= least(25, 0.02 * (success_total + failure_total))
ORDER BY abs(log(odds_ratio)) DESC
LIMIT 20

The query only counts events between funnel entry and conversion. step_events holds the first-step and last-step events in time order. next_final gives the next last-step time for each of them in one pass. For people who converted, attempt picks the first entry that has a last step inside the window, so both timestamps come from the same attempt. For more steps, keep only the first and last step events in groupArrayIf. The attempt then ignores the middle steps, so the interval can start at an earlier entry than the one that converted. For people who did not convert, it counts events from their first entry until the conversion window closes. The timestamps use whole seconds because windowFunnel compares whole seconds. Keep them the same, or some converters get no matched attempt. It excludes the funnel step events, because they correlate with conversion by definition.

Property correlation

To compare person property values, group the actors by the property and skip the events join.

WITH
    funnel_actors AS (
        SELECT
            person_id AS actor_id,
            windowFunnel(1209600)(toDateTime(timestamp), event = 'step one', event = 'step two') AS steps,
            any(person.properties.$browser) AS value
        FROM events
        WHERE event IN ('step one', 'step two')
            AND timestamp >= now() - INTERVAL 30 DAY
        GROUP BY actor_id
        HAVING steps >= 1
    ),
    totals AS (
        SELECT countIf(steps = 2) AS success_total, countIf(steps < 2) AS failure_total
        FROM funnel_actors
    ),
    per_value AS (
        SELECT
            toString(value) AS name,
            countIf(steps = 2) AS success_count,
            countIf(steps < 2) AS failure_count
        FROM funnel_actors
        GROUP BY name
    )
SELECT
    name,
    success_count,
    failure_count,
    ((success_count + 1) * (failure_total - failure_count + 1))
        / ((success_total - success_count + 1) * (failure_count + 1)) AS odds_ratio
FROM per_value
CROSS JOIN totals
WHERE success_count + failure_count >= least(25, 0.02 * (success_total + failure_total))
ORDER BY abs(log(odds_ratio)) DESC
LIMIT 20

To look at the properties of one event instead, filter per_event to that event and group by e.properties.<name>.

Read the results with care

• The + 1 in each cell is a prior. It prevents division by zero and damps signals from small counts.

• The WHERE filter drops signals seen by fewer than 25 people or 2% of entrants. Small groups give unstable ratios.

• If one group is more than 10 times bigger than the other, the ratios are less reliable. Tell the user.

• If success_total or failure_total is 0, there is nothing to compare.

• Noise events that everybody sends, such as $pageview, $pageleave, or $feature_flag_called, can fill the list. Exclude them when they hide the useful signals.

• For group-based funnels, replace person_id with $group_N and the person properties with group properties. Also add AND $group_N != '' to the funnel step WHERE clause. Events without that group have an empty key, and without the filter they become one fake actor that can convert.