Sunburst chart
November 3, 2014 · Refreshed over 5 years ago
Description
For more information on how to build a sunburst chart, visit https://community.modeanalytics.com/gallery/sunburst-chart .
Collaborators
Benn Stancil
Run History
-- For details on how to use this report, visit
-- https://modeanalytics.zendesk.com/hc/en-us/articles/203501570
WITH
events AS (
SELECT user_id,
event_name,
occurred_at
FROM tutorial.playbook_events
WHERE event_type = 'engagement'
)
SELECT e1,
e2,
e3,
e4,
e5,
COUNT(*) AS occurrances
FROM (
SELECT user_id,
session_number,
MAX(CASE WHEN event_number = 1 THEN event_name ELSE NULL END) AS e1,
MAX(CASE WHEN event_number = 2 THEN event_name ELSE NULL END) AS e2,
MAX(CASE WHEN event_number = 3 THEN event_name ELSE NULL END) AS e3,
MAX(CASE WHEN event_number = 4 THEN event_name ELSE NULL END) AS e4,
MAX(CASE WHEN event_number = 5 THEN event_name ELSE NULL END) AS e5
FROM (
SELECT e.user_id,
e.occurred_at,
s.session_number,
e.event_name,
ROW_NUMBER() OVER (PARTITION BY e.user_id, s.session_number ORDER BY e.occurred_at) AS event_number
FROM (
SELECT user_id,
occurred_at AS session_start,
COALESCE(LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at),'2020-01-01') AS session_end,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at) AS session_number
FROM (
SELECT user_id,
occurred_at,
EXTRACT(
'EPOCH' FROM occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at)
)/60 AS time_to_last_event
FROM events
) bounds
WHERE time_to_last_event > 30
OR time_to_last_event IS NULL
) s
JOIN events e
ON e.user_id = s.user_id
AND e.occurred_at >= s.session_start
AND e.occurred_at < s.session_end
) x
GROUP BY 1,2
) z
GROUP BY 1,2,3,4,5
ORDER BY 6 DESC
LIMIT 100
-- For details on how to use this report, visit
-- https://modeanalytics.zendesk.com/hc/en-us/articles/203501570
WITH
events AS (
SELECT user_id,
event_name,
occurred_at
FROM tutorial.playbook_events
WHERE event_type = 'engagement'
)
SELECT e1,
e2,
e3,
e4,
e5,
COUNT(*) AS occurrances
FROM (
SELECT user_id,
session_number,
MAX(CASE WHEN event_number = 1 THEN event_name ELSE NULL END) AS e1,
MAX(CASE WHEN event_number = 2 THEN event_name ELSE NULL END) AS e2,
MAX(CASE WHEN event_number = 3 THEN event_name ELSE NULL END) AS e3,
MAX(CASE WHEN event_number = 4 THEN event_name ELSE NULL END) AS e4,
MAX(CASE WHEN event_number = 5 THEN event_name ELSE NULL END) AS e5
FROM (
SELECT e.user_id,
e.occurred_at,
s.session_number,
e.event_name,
ROW_NUMBER() OVER (PARTITION BY e.user_id, s.session_number ORDER BY e.occurred_at) AS event_number
FROM (
SELECT user_id,
occurred_at AS session_start,
COALESCE(LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at),'2020-01-01') AS session_end,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at) AS session_number
FROM (
SELECT user_id,
occurred_at,
EXTRACT(
'EPOCH' FROM occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at)
)/60 AS time_to_last_event
FROM events
) bounds
WHERE time_to_last_event > 30
OR time_to_last_event IS NULL
) s
JOIN events e
ON e.user_id = s.user_id
AND e.occurred_at >= s.session_start
AND e.occurred_at < s.session_end
) x
GROUP BY 1,2
) z
GROUP BY 1,2,3,4,5
ORDER BY 6 DESC
<link rel="stylesheet" href="https://mode.github.io/alamode/alamode.min.css">
<script src="https://mode.github.io/alamode/alamode.min.js"></script>
<div class="mode-header embed-hidden">
<h1>{{ title }}</h1>
<p>{{ description }}</p>
</div>
<script>
alamode.sunburstChart({
query_name: "Query 1",
title: "How people move through your product",
event_columns: ["e1", "e2", "e3", "e4", "e5"],
event_counts: "occurrances"
})
</script>
<div class="mode-grid container">
</div>
The dataset is too large to view in browser
Export
Looks like something went wrong with your query.
{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled