PostHog / PostHog/posthog

Feature Request: Time to value historical trend

Open
#41,124 1 comment 0 reactions 1 assignee View on GitHub

@ordehi is already working on this.

Since Nov 11, 2025.

enhancement feature/funnels team/product-analytics
Dominant language
Python
Stars
39.9k
Forks
3.4k
Avg merge
7h 27m
Merged PRs (30d)
222

Description

Feature request

Is your feature request related to a problem?

Funnels support Time to convert and historical trends, but not historical time to convert.

Image

Describe the solution you'd like

Support historical time to value/convert so users can see how the average/etc time to convert evolves, not just the conversion rates.

Describe alternatives you've considered

  • Use SQL, you can use the Funnel's historical trends and time to convert queries as starting points, here's a version that seems to work, but may need some adjustments, feel free to comment below with yours. Make sure to change the repeated pageview events to the ones you want to track.
Time to convert between two events last 180 days
SELECT
    concat(toString(aggregation_target), '_', toString(entrance_period_start)) AS id,
    entrance_period_start AS timestamp,
    aggregation_target AS distinct_id,
    entrance_period_start,
    conversion_time AS conversion_time_seconds,
    conversion_time / 3600 AS conversion_time_hours,
    conversion_time / 86400 AS conversion_time_days
FROM
    (SELECT
        toStartOfDay(min_timestamp) AS entrance_period_start,
        breakdown,
        aggregation_target,
        arraySum(timings) AS conversion_time
    FROM
        (SELECT
            arraySort(t -> t.1, groupArray(tuple(toFloat(timestamp), uuid, '', arrayFilter(x -> notEquals(x, 0), [multiply(1, step_0), multiply(2, step_1)])))) AS events_array,
            [''] AS prop,
            min(timestamp) AS min_timestamp,
            arrayJoin(aggregate_funnel(2, 259200, 'first_touch', 'ordered', prop, [], arrayFilter((x, x_before, x_after) -> not(and(lessOrEquals(length(x.4), 1), equals(x.4, x_before.4), equals(x.4, x_after.4), equals(x.3, x_before.3), equals(x.3, x_after.3), greater(x.1, x_before.1), less(x.1, x_after.1))), events_array, arrayRotateRight(events_array, 1), arrayRotateLeft(events_array, 1)))) AS af_tuple,
            af_tuple.1 AS step_reached,
            plus(af_tuple.1, 1) AS steps,
            af_tuple.2 AS breakdown,
            af_tuple.3 AS timings,
            aggregation_target
        FROM
            (SELECT
                e.timestamp AS timestamp,
                person_id AS aggregation_target,
                e.uuid AS uuid,
                if(equals(event, '$pageview'), 1, 0) AS step_0,
                if(equals(event, '$pageview'), 1, 0) AS step_1
            FROM
                events AS e
            WHERE
                and(and(greaterOrEquals(e.timestamp, now() - INTERVAL 180 DAY), lessOrEquals(e.timestamp, now())), and(in(event, tuple('$pageview', '$pageview')), or(equals(step_0, 1), equals(step_1, 1)))))
        GROUP BY
            aggregation_target
        HAVING
            greaterOrEquals(step_reached, 0))
    WHERE
        greaterOrEquals(step_reached, 1)
        AND notEmpty(timings))
WHERE
    conversion_time >= 0
ORDER BY
    entrance_period_start ASC

Additional context

From: https://posthoghelp.zendesk.com/agent/tickets/41839 (moved to PostHog: https://us.posthog.com/project/2/support/tickets/44139)

Debug info
null

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.