Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Three Step Funnel
SELECT
pricing.distinct_id,
viewed_pricing_at,
viewed_sign_up_at,
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
+-----------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
| distinct_id | viewed_pricing_at | viewed_sign_up_at | signed_up_at |
+-----------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:31:26.000000 UTC | 2018-04-09 06:08:08.000000 UTC | |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-05 12:48:37.000000 UTC | 2018-04-09 06:08:08.000000 UTC | |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:28:42.000000 UTC | 2018-04-09 06:08:08.000000 UTC | |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:27:49.000000 UTC | 2018-04-09 06:08:08.000000 UTC | |
| 1628be3f8ab208-0678714f9f0ec-b34356b-1fa400-1628be3f8b825 | 2018-04-03 07:35:51.000000 UTC | 2018-04-09 06:08:08.000000 UTC | |
| 162914788c5fb-09337b4c437dc4-b34356b-144000-162914788c628 | 2018-04-04 17:30:18.000000 UTC | 2018-04-04 17:31:13.000000 UTC | 2018-04-04 17:34:51.000000 UTC |
| 162914788c5fb-09337b4c437dc4-b34356b-144000-162914788c628 | 2018-04-04 08:31:16.000000 UTC | 2018-04-04 17:24:52.000000 UTC | 2018-04-04 17:34:51.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.