Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Three Step Funnel Count
SELECT
COUNT(viewed_pricing_at) AS viewed_pricing,
COUNT(viewed_sign_up_at) AS viewed_sign_up,
COUNT(signed_up_at) AS signed_up
FROM (
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
) visitor_data
+----------------+----------------+-----------+
| viewed_pricing | viewed_sign_up | signed_up |
+----------------+----------------+-----------+
| 1234 | 567 | 89 |
+----------------+----------------+-----------+
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.