Three Step Funnel Count

three-step-funnel-counts.sql

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