Skip to content

Instantly share code, notes, and snippets.

@mattm
Last active April 6, 2018 18:14
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mattm/0997b3b587d5ad0783190b3db04283f5 to your computer and use it in GitHub Desktop.
Visitor Path
SELECT
time,
name,
current_url
FROM (
SELECT
distinct_id,
CAST(JSON_EXTRACT_SCALAR(properties, "$.com_id") AS INT64) AS com_id
FROM mp.event
WHERE name = "Signed Up"
) signed_up
JOIN mp.event ON event.distinct_id = signed_up.distinct_id
WHERE signed_up.com_id = 1234
ORDER BY time ASC
+--------------------------------+-------------+--------------------------------------------------------------+
| time | name | current_url |
+--------------------------------+-------------+--------------------------------------------------------------+
| 2018-04-05 15:38:38.000000 UTC | Viewed Page | https://www.helpscout.net/ |
| 2018-04-05 15:38:40.000000 UTC | Viewed Page | https://secure.helpscout.net/members/login/ |
| 2018-04-05 15:38:46.000000 UTC | Viewed Page | https://www.helpscout.net/pricing/ |
| 2018-04-05 15:38:52.000000 UTC | Viewed Page | https://secure.helpscout.net/members/register/19/ |
| 2018-04-05 15:39:43.000000 UTC | Signed Up | https://secure.helpscout.net/welcome/segmentation-questions/ |
+--------------------------------+-------------+--------------------------------------------------------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment