Three Step Funnel v2

funnel-first-events.sql

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 |
+--------------------------------------------------------------+--------------------------------+--------------------------------+--------------------------------+