Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Group engagement time
SELECT
case
when engagement.minutes > 20 then '>20 min'
when engagement.minutes > 10 then '10-20 min'
when engagement.minutes > 5 then '5-10 min'
when engagement.minutes > 2 then '2-5 min'
else '<2 min'
end as segment,
count(*) as users
FROM (
SELECT distinct(user_pseudo_id) as user_pseudo_id FROM `<project>.analytics_205676331.events_*` WHERE event_name = 'view_item'
) as viewers
LEFT JOIN (
SELECT
user_pseudo_id,
sum(params.value.int_value) / 60000 as minutes
FROM `<project>.analytics_205676331.events_*`,
UNNEST(event_params) AS params
WHERE params.key='engagement_time_msec'
group by user_pseudo_id
) as engagement on engagement.user_pseudo_id = viewers.user_pseudo_id
group by segment
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment