Skip to content

Instantly share code, notes, and snippets.

@mattm

mattm/visitor-flow.sql

Last active Apr 6, 2018
Embed
What would you like to do?
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
You can’t perform that action at this time.