Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/two-step-funnel.sql

Last active Apr 12, 2018
Embed
What would you like to do?
Two Step SQL Funnel
SELECT
pricing.distinct_id,
viewed_pricing_at,
viewed_sign_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
+-----------------------------------------------------------+--------------------------------+--------------------------------+
| distinct_id | viewed_pricing_at | viewed_sign_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 | |
+-----------------------------------------------------------+--------------------------------+--------------------------------+
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.