Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Three Step Funnel v2
SELECT
pricing.distinct_id,
MIN(viewed_pricing_at) AS viewed_pricing_at,
MIN(viewed_sign_up_at) AS viewed_sign_up_at,
MIN(signed_up_at) AS signed_up_at
FROM (
SELECT
distinct_id,
time AS viewed_pricing_at
FROM mp.event
WHERE
name = "Viewed Page" AND
current_url LIKE "https://www.helpscout.net/pricing/%"
) pricing
LEFT JOIN (
SELECT
distinct_id,
time AS viewed_sign_up_at
FROM mp.event
WHERE
name = "Viewed Page" AND
current_url LIKE "https://secure.helpscout.net/members/register/%"
) sign_up_page ON sign_up_page.distinct_id = pricing.distinct_id AND sign_up_page.viewed_sign_up_at > viewed_pricing_at
LEFT JOIN (
SELECT
distinct_id,
time AS signed_up_at
FROM mp.event
WHERE
name = "Signed Up"
) signed_up ON signed_up.distinct_id = pricing.distinct_id AND signed_up.signed_up_at > viewed_sign_up_at
GROUP BY 1
+--------------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
| distinct_id | viewed_pricing_at | viewed_sign_up_at | signed_up_at |
+--------------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
| 162931933729b2-0b3ba365e6d865-336c7b05-384000-1629319337333b | 2018-04-04 16:58:45.000000 UTC | 2018-04-11 20:50:13.000000 UTC | 2018-04-11 20:51:46.000000 UTC |
| 162b770945247a-0145ce1cbf076d8-495860-1fa400-162b77094541286 | 2018-04-11 19:56:18.000000 UTC | 2018-04-11 20:02:54.000000 UTC | |
| 162899a5ec5895-0fc657f6c6ebde-33697b07-384000-162899a5ec68c6 | 2018-04-09 19:20:01.000000 UTC | 2018-04-11 19:38:59.000000 UTC | |
| 162b73858df1f8-0625fc7a476d418-76313118-3d10d-162b73858e2237 | 2018-04-11 17:19:53.000000 UTC | 2018-04-11 17:23:22.000000 UTC | |
| 162b6d32dd11a5-0bbf34346ce939-3553629-231015-162b6d32dd2646 | 2018-04-11 15:30:09.000000 UTC | 2018-04-11 16:40:08.000000 UTC | |
| 162875b83f63e2-0870a441baadb6-33697b07-384000-162875b83f714f | 2018-04-02 10:15:44.000000 UTC | 2018-04-11 14:56:37.000000 UTC | 2018-04-11 14:57:27.000000 UTC |
+--------------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.