boggs
June 13, 2016, 6:35pm
1
Hey all,
What’s the reasoning behind calculating time_engaged_with_minutes here:
SELECT
blended_user_id, -- Placeholder (the domain_userid)
MIN(collector_tstamp) AS first_touch_tstamp,
MAX(collector_tstamp) AS last_touch_tstamp,
MIN(dvce_created_tstamp) AS dvce_min_tstamp, -- Used to replace SQL window functions
MAX(dvce_created_tstamp) AS dvce_max_tstamp, -- Used to replace SQL window functions
MAX(etl_tstamp) AS max_etl_tstamp, -- Used for debugging
COUNT(*) AS event_count,
MAX(domain_sessionidx) AS session_count,
SUM(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) AS page_view_count,
COUNT(DISTINCT(FLOOR(EXTRACT (EPOCH FROM dvce_created_tstamp)/30)))/2::FLOAT AS time_engaged_with_minutes
FROM snowplow_intermediary.events_enriched_final
GROUP BY 1
);
? Just wondering the whys of calculating it in that way.
Thanks.
1 Like
Hi @boggs ,
This specific implementation assumes a page ping is sent every 30 seconds. It converts the timestamp to a Unix timestamp, then essentially rounds it to the nearest 30, and counts the number of distinct occurrences. So it counts the number of 30-second intervals with at least one event.
Hope this makes sense.
2 Likes