Collection of SQL scripts developed for the Snowflake Data Cloud to analyze Google Analytics events, user sequences, and session engagement for data-driven product improvements.
Comprehensive metrics on user engagement, with segmentation by factors like device type, geography, and browser.
Analysis of user journey paths, measuring precise timing between steps and identifying workflow bottlenecks.
Granular tracking of individual user interactions, capturing click details and mapping to a business taxonomy.
Google Analytics events and custom tracking solutions providing detailed user interaction data with millisecond precision.
Snowflake SQL environment with comprehensive ETL processes to transform raw events into business-meaningful metrics.
Interactive visualization dashboards connecting directly to the analytics database, providing real-time insights for product decisions.
Provides a 360-degree view of user engagement across different segments, helping identify both power users and those at risk of churning. Enables product teams to understand how factors like geography, device type, and browser affect usage patterns and satisfaction.
GA Data project for creating user segmentation models and predicting product stickiness based on measured engagement patterns
Comprehensive session-level metrics and engagement scoring of user interaction patterns
These SQL scripts transform complex digital footprints into actionable business insights. They answer questions like "Which features are most engaging?", "Where do users get stuck in workflows?", and "How do different user segments interact with our product?". The analysis helps prioritize improvements based on real user behavior rather than assumptions.
This analytical framework uses Common Table Expressions (CTEs) for modular processing, with window functions for temporal and state analysis. It implements sophisticated data transformations including sequence detection algorithms, engagement scoring formulas, and hierarchical categorization systems—all optimized for performance with large datasets.
1-- ***************************************************************
2-- Session Event & Engagement Analysis
3-- This script demonstrates how to:
4-- 1. Identify the first event per session.
5-- 2. Filter sessions that include at least one "designated" page view.
6-- 3. Capture detailed event-level data for these sessions.
7-- 4. Derive session attributes and user engagement metrics.
8-- ***************************************************************
9
10-- Step 1: Identify the first event per session using ROW_NUMBER()
11WITH FirstEventPerSession AS (
12 SELECT
13 "EVENT_PARAMS_GA_SESSION_ID", -- Session identifier
14 "TENANTID",
15 "USER_ID" AS "FIRST_USER_ID", -- The first user ID in the session
16 CONCAT("TENANTID", "USER_ID") AS "TENANT_USER_ID", -- Combined tenant & user ID for uniqueness
17 "EVENT_TIMESTAMP" AS "FIRST_EVENT_TIMESTAMP" -- Timestamp of the first event
18 FROM (
19 SELECT
20 "EVENT_PARAMS_GA_SESSION_ID",
21 "TENANTID",
22 "USER_ID",
23 "EVENT_TIMESTAMP",
24 ROW_NUMBER() OVER (
25 PARTITION BY "EVENT_PARAMS_GA_SESSION_ID", "TENANTID"
26 ORDER BY "EVENT_TIMESTAMP"
27 ) AS rn -- Rank events within each session by timestamp
28 FROM "YOUR_SCHEMA"."YOUR_TABLE" -- Replace with your actual schema/table
29 WHERE "EVENT_TIMESTAMP" >= DATEADD(month, -3, CURRENT_DATE)
30 ) t
31 WHERE rn = 1 -- Keep only the first event per session
32)
33-- Explanation: This CTE extracts the earliest event per session for each tenant.
34
35-- Step 2: Identify sessions with at least one designated URL event
36, AllSessions AS (
37 SELECT
38 F."EVENT_PARAMS_GA_SESSION_ID",
39 F."TENANTID",
40 F."FIRST_USER_ID",
41 F."TENANT_USER_ID",
42 -- Create a unique session identifier by combining session and tenant-user info
43 TO_NUMBER(CONCAT(F."EVENT_PARAMS_GA_SESSION_ID", REPLACE(F."TENANT_USER_ID", '-', ''))) AS "SESSION_ID_USER_ID",
44 COUNT(DISTINCT CASE
45 -- Designated URL condition: using a generic example link pattern
46 WHEN E."EVENT_PARAMS_PAGE_LOCATION" ILIKE 'https://example.com/ExampleLink%' THEN 1
47 ELSE NULL
48 END) AS "DESIGNATED_URL_COUNT"
49 FROM "YOUR_SCHEMA"."YOUR_TABLE" E
50 INNER JOIN FirstEventPerSession F
51 ON E."EVENT_PARAMS_GA_SESSION_ID" = F."EVENT_PARAMS_GA_SESSION_ID"
52 AND E."TENANTID" = F."TENANTID"
53 AND E."USER_ID" = F."FIRST_USER_ID" -- Only consider events from the first user in the session
54 WHERE E."EVENT_TIMESTAMP" >= DATEADD(month, -3, CURRENT_DATE)
55 GROUP BY
56 F."EVENT_PARAMS_GA_SESSION_ID",
57 F."TENANTID",
58 F."FIRST_USER_ID",
59 F."TENANT_USER_ID"
60 HAVING "DESIGNATED_URL_COUNT" > 0 -- Only include sessions with at least one designated URL event
61)
62-- Explanation: This CTE filters sessions to those where the user visited at least one "designated" page (using an example link).
63
64-- Step 3: Capture all events from the filtered sessions for the first USER_ID only
65, AllSessionEvents AS (
66 SELECT
67 S."SESSION_ID_USER_ID",
68 E."EVENT_PARAMS_GA_SESSION_ID",
69 E."EVENT_NAME",
70 E."EVENT_TIMESTAMP",
71 DATE(E."EVENT_TIMESTAMP") AS "SESSION_DATE",
72 E."EVENT_PARAMS_PAGE_LOCATION",
73 E."TENANTID",
74 S."TENANT_USER_ID",
75 E."USER_ID",
76 E."GEO_CITY",
77 E."GEO_COUNTRY",
78 E."GEO_METRO",
79 E."GEO_REGION",
80 E."DEVICE_CATEGORY",
81 E."DEVICE_OPERATING_SYSTEM",
82 E."DEVICE_WEB_INFO_BROWSER",
83 E."EVENT_PARAMS_VIEWPORT",
84 E."EVENT_PARAMS_CUSTOM_ZOOM_PERCENTAGE",
85 E."EVENT_PARAMS_CUSTOM_ZOOM_PERCENTAGE_PIXELRATIO",
86 -- Assign event priority for later ordering: session_start first, then page_view, then others.
87 CASE
88 WHEN E."EVENT_NAME" = 'session_start' THEN 1
89 WHEN E."EVENT_NAME" = 'page_view' THEN 2
90 ELSE 3
91 END AS "EVENT_PRIORITY",
92 -- Clean the page URL: remove the base URL if it matches our example domain.
93 CASE
94 WHEN E."EVENT_PARAMS_PAGE_LOCATION" ILIKE 'https://example.com/%'
95 THEN SPLIT_PART(REPLACE(E."EVENT_PARAMS_PAGE_LOCATION", 'https://example.com/', ''), '?', 1)
96 ELSE SPLIT_PART(E."EVENT_PARAMS_PAGE_LOCATION", '?', 1)
97 END AS "CLEANED_PAGE_LOCATION",
98 -- Flag if this event's page is a designated URL (using the generic example link)
99 CASE
100 WHEN E."EVENT_PARAMS_PAGE_LOCATION" ILIKE 'https://example.com/ExampleLink%' THEN 1
101 ELSE 0
102 END AS "IS_DESIGNATED_URL",
103 -- Capture the first browser used in the session
104 FIRST_VALUE(E."DEVICE_WEB_INFO_BROWSER") OVER (
105 PARTITION BY S."SESSION_ID_USER_ID"
106 ORDER BY E."EVENT_TIMESTAMP"
107 ) AS "INITIAL_BROWSER",
108 -- Capture the last browser used in the session
109 LAST_VALUE(E."DEVICE_WEB_INFO_BROWSER") OVER (
110 PARTITION BY S."SESSION_ID_USER_ID"
111 ORDER BY E."EVENT_TIMESTAMP"
112 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
113 ) AS "LAST_BROWSER",
114 -- Calculate timestamps to help identify new vs. returning users and churn
115 MIN(E."EVENT_TIMESTAMP") OVER (PARTITION BY S."TENANT_USER_ID") AS "FIRST_USER_EVENT_TIMESTAMP",
116 MIN(E."EVENT_TIMESTAMP") OVER (PARTITION BY S."SESSION_ID_USER_ID") AS "FIRST_SESSION_EVENT_TIMESTAMP",
117 MAX(E."EVENT_TIMESTAMP") OVER (PARTITION BY S."TENANT_USER_ID") AS "LAST_USER_EVENT_TIMESTAMP"
118 FROM "YOUR_SCHEMA"."YOUR_TABLE" E
119 INNER JOIN AllSessions S
120 ON E."EVENT_PARAMS_GA_SESSION_ID" = S."EVENT_PARAMS_GA_SESSION_ID"
121 AND E."TENANTID" = S."TENANTID"
122 AND E."USER_ID" = S."FIRST_USER_ID" -- Limit to events from the first user of each session
123 WHERE E."EVENT_TIMESTAMP" >= DATEADD(month, -3, CURRENT_DATE)
124)
125-- Explanation: This CTE gathers detailed event data for sessions that passed the filter, cleans the URL, and flags designated URL events.
126
127-- Step 4: Get session-level attributes from the first event in each session
128, SessionAttributes AS (
129 SELECT DISTINCT
130 "SESSION_ID_USER_ID",
131 FIRST_VALUE("TENANTID") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "TENANTID",
132 FIRST_VALUE("TENANT_USER_ID") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "TENANT_USER_ID",
133 FIRST_VALUE("USER_ID") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "USER_ID",
134 FIRST_VALUE("SESSION_DATE") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "SESSION_DATE",
135 FIRST_VALUE("GEO_CITY") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "GEO_CITY",
136 FIRST_VALUE("GEO_COUNTRY") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "GEO_COUNTRY",
137 FIRST_VALUE("GEO_METRO") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "GEO_METRO",
138 FIRST_VALUE("GEO_REGION") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "GEO_REGION",
139 FIRST_VALUE("DEVICE_CATEGORY") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "DEVICE_CATEGORY",
140 FIRST_VALUE("DEVICE_OPERATING_SYSTEM") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "DEVICE_OPERATING_SYSTEM",
141 FIRST_VALUE("DEVICE_WEB_INFO_BROWSER") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "DEVICE_WEB_INFO_BROWSER",
142 FIRST_VALUE("EVENT_PARAMS_VIEWPORT") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "MAX_VIEWPORT",
143 FIRST_VALUE("EVENT_PARAMS_CUSTOM_ZOOM_PERCENTAGE") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "MAX_ZOOM_PERCENTAGE",
144 FIRST_VALUE("EVENT_PARAMS_CUSTOM_ZOOM_PERCENTAGE_PIXELRATIO") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY") AS "MAX_ZOOM_PERCENTAGE_PIXELRATIO",
145 -- Establish first and last event timestamps for additional metrics
146 MIN("FIRST_USER_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID") AS "FIRST_USER_EVENT_TIMESTAMP",
147 MIN("FIRST_SESSION_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID") AS "FIRST_SESSION_EVENT_TIMESTAMP",
148 MAX("LAST_USER_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID") AS "LAST_USER_EVENT_TIMESTAMP",
149 -- Determine if the user is new or returning based on first event timestamps
150 CASE
151 WHEN MIN("FIRST_USER_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID") =
152 MIN("FIRST_SESSION_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID")
153 THEN 'New User'
154 ELSE 'Returning User'
155 END AS "USER_TYPE",
156 -- Assess churn probability based on days since the last event
157 CASE
158 WHEN DATEDIFF('day', MAX("LAST_USER_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID"), CURRENT_DATE) > 30 THEN 'High'
159 WHEN DATEDIFF('day', MAX("LAST_USER_EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID"), CURRENT_DATE) BETWEEN 10 AND 30 THEN 'Medium'
160 ELSE 'Low'
161 END AS "CHURN_PROBABILITY"
162 FROM AllSessionEvents
163)
164-- Explanation: This CTE summarizes session-level attributes using window functions on the event data.
165
166-- Step 5: Calculate session order, gaps, and counts per tenant user
167, UserSessionOrder AS (
168 SELECT
169 SA."TENANT_USER_ID",
170 SA."SESSION_ID_USER_ID",
171 SA."SESSION_DATE",
172 ROW_NUMBER() OVER (PARTITION BY SA."TENANT_USER_ID" ORDER BY SA."SESSION_DATE") AS "SESSION_ORDER",
173 LAG(SA."SESSION_DATE") OVER (PARTITION BY SA."TENANT_USER_ID" ORDER BY SA."SESSION_DATE") AS "PREVIOUS_SESSION_DATE",
174 DATEDIFF('day', LAG(SA."SESSION_DATE") OVER (PARTITION BY SA."TENANT_USER_ID" ORDER BY SA."SESSION_DATE"), SA."SESSION_DATE") AS "DAYS_BETWEEN_SESSIONS"
175 FROM SessionAttributes SA
176)
177-- Explanation: This CTE orders sessions per tenant user and calculates the days between sessions.
178
179-- Step 6: Count the number of sessions in the last 60 days per tenant user
180, UserSessionCounts AS (
181 SELECT
182 "TENANT_USER_ID",
183 COUNT(*) AS "SESSIONS_LAST_60_DAYS"
184 FROM UserSessionOrder
185 WHERE "SESSION_DATE" >= DATEADD('day', -60, CURRENT_DATE)
186 GROUP BY "TENANT_USER_ID"
187)
188-- Explanation: This CTE computes how many sessions a user had in the previous 60 days.
189
190-- Step 7: Aggregate events to generate session-level statistics
191, AggregatedSessionEvents AS (
192 SELECT
193 ASE."SESSION_ID_USER_ID",
194 SA."TENANTID",
195 SA."TENANT_USER_ID",
196 SA."SESSION_DATE",
197 SA."USER_ID",
198 SA."GEO_CITY",
199 SA."GEO_COUNTRY",
200 SA."GEO_METRO",
201 SA."GEO_REGION",
202 SA."DEVICE_CATEGORY",
203 SA."DEVICE_OPERATING_SYSTEM",
204 SA."DEVICE_WEB_INFO_BROWSER",
205 SA."MAX_VIEWPORT",
206 SA."MAX_ZOOM_PERCENTAGE",
207 SA."MAX_ZOOM_PERCENTAGE_PIXELRATIO",
208 SA."USER_TYPE",
209 SA."CHURN_PROBABILITY",
210 -- Concatenate event names and cleaned page URLs in order of occurrence
211 LISTAGG(ASE."EVENT_NAME", ' > ') WITHIN GROUP (ORDER BY ASE."EVENT_TIMESTAMP", ASE."EVENT_PRIORITY") AS "ALL_SESSION_EVENT_NAMES",
212 LISTAGG(ASE."CLEANED_PAGE_LOCATION", ' > ') WITHIN GROUP (ORDER BY ASE."EVENT_TIMESTAMP", ASE."EVENT_PRIORITY") AS "ALL_SESSION_EVENT_URLS",
213 COUNT(ASE."EVENT_NAME") AS "TOTAL_EVENTS_IN_SESSION",
214 COUNT(DISTINCT ASE."CLEANED_PAGE_LOCATION") AS "TOTAL_UNIQUE_PAGES_IN_SESSION",
215 -- Capture the initial and final browsers used during the session
216 MIN(ASE."INITIAL_BROWSER") AS "STARTING_BROWSER",
217 MIN(ASE."LAST_BROWSER") AS "ENDING_BROWSER",
218 -- Determine if the browser changed during the session
219 CASE
220 WHEN MIN(ASE."INITIAL_BROWSER") != MIN(ASE."LAST_BROWSER") THEN MIN(ASE."LAST_BROWSER")
221 ELSE NULL
222 END AS "SWITCHED_BROWSER"
223 FROM AllSessionEvents ASE
224 INNER JOIN SessionAttributes SA ON ASE."SESSION_ID_USER_ID" = SA."SESSION_ID_USER_ID"
225 GROUP BY
226 ASE."SESSION_ID_USER_ID",
227 SA."TENANTID",
228 SA."TENANT_USER_ID",
229 SA."SESSION_DATE",
230 SA."USER_ID",
231 SA."GEO_CITY",
232 SA."GEO_COUNTRY",
233 SA."GEO_METRO",
234 SA."GEO_REGION",
235 SA."DEVICE_CATEGORY",
236 SA."DEVICE_OPERATING_SYSTEM",
237 SA."DEVICE_WEB_INFO_BROWSER",
238 SA."MAX_VIEWPORT",
239 SA."MAX_ZOOM_PERCENTAGE",
240 SA."MAX_ZOOM_PERCENTAGE_PIXELRATIO",
241 SA."USER_TYPE",
242 SA."CHURN_PROBABILITY"
243)
244-- Explanation: This CTE aggregates event data into session-level metrics (e.g., total events, unique pages, browser details).
245
246-- Step 8: Calculate time differences between consecutive events within each session
247, TimeBetweenEvents AS (
248 SELECT
249 "SESSION_ID_USER_ID",
250 "EVENT_PARAMS_GA_SESSION_ID",
251 "CLEANED_PAGE_LOCATION",
252 "IS_DESIGNATED_URL",
253 "EVENT_TIMESTAMP",
254 DATEDIFF(
255 'second',
256 LAG("EVENT_TIMESTAMP") OVER (PARTITION BY "SESSION_ID_USER_ID" ORDER BY "EVENT_TIMESTAMP", "EVENT_PRIORITY"),
257 "EVENT_TIMESTAMP"
258 ) AS "TIME_BETWEEN_EVENTS_SECONDS"
259 FROM AllSessionEvents
260)
261-- Explanation: This CTE computes the elapsed time between events in seconds.
262
263-- Step 9: Summarize time and counts for events occurring on designated URLs
264, TimeOnDesignatedURLs AS (
265 SELECT
266 "SESSION_ID_USER_ID",
267 SUM(CASE
268 WHEN "IS_DESIGNATED_URL" = 1 THEN COALESCE("TIME_BETWEEN_EVENTS_SECONDS", 0)
269 ELSE 0
270 END) AS "TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS",
271 COUNT(CASE
272 WHEN "IS_DESIGNATED_URL" = 1 THEN 1
273 ELSE NULL
274 END) AS "EVENTS_ON_DESIGNATED_URLS",
275 COUNT(DISTINCT CASE
276 WHEN "IS_DESIGNATED_URL" = 1 THEN "CLEANED_PAGE_LOCATION"
277 ELSE NULL
278 END) AS "UNIQUE_PAGES_ON_DESIGNATED_URLS"
279 FROM TimeBetweenEvents
280 GROUP BY "SESSION_ID_USER_ID"
281)
282-- Explanation: This CTE aggregates the total time (and counts) spent on designated (example) URLs per session.
283
284-- Step 10: Compute overall session statistics such as duration and bounce rate
285, AllSessionStats AS (
286 SELECT
287 "SESSION_ID_USER_ID",
288 COUNT("EVENT_NAME") AS "ALL_EVENT_COUNT",
289 COUNT(DISTINCT "EVENT_PARAMS_PAGE_LOCATION") AS "ALL_PAGE_COUNT",
290 MIN("EVENT_TIMESTAMP") AS "FIRST_EVENT_TIMESTAMP",
291 MAX("EVENT_TIMESTAMP") AS "LAST_EVENT_TIMESTAMP",
292 DATEDIFF('second', MIN("EVENT_TIMESTAMP"), MAX("EVENT_TIMESTAMP")) / 60.0 AS "ALL_SESSION_DURATION_MINUTES",
293 -- Define a bounce: a single event on one page in under 10 seconds
294 CASE
295 WHEN COUNT(DISTINCT "EVENT_PARAMS_PAGE_LOCATION") = 1
296 AND COUNT("EVENT_NAME") = 1
297 AND DATEDIFF('second', MIN("EVENT_TIMESTAMP"), MAX("EVENT_TIMESTAMP")) < 10 THEN 'Yes'
298 ELSE 'No'
299 END AS "BOUNCE_INDICATOR",
300 -- Categorize session length based on duration
301 CASE
302 WHEN DATEDIFF('second', MIN("EVENT_TIMESTAMP"), MAX("EVENT_TIMESTAMP")) < 300 THEN 'Short'
303 WHEN DATEDIFF('second', MIN("EVENT_TIMESTAMP"), MAX("EVENT_TIMESTAMP")) < 900 THEN 'Medium'
304 ELSE 'Long'
305 END AS "SESSION_LENGTH_CATEGORY"
306 FROM AllSessionEvents
307 GROUP BY "SESSION_ID_USER_ID"
308)
309-- Explanation: This CTE computes overall metrics per session including total events, duration (in minutes), bounce rate, and length category.
310
311-- Final Output: Join all aggregated metrics to provide a comprehensive session-level report
312SELECT
313 ASE."SESSION_ID_USER_ID",
314 ASE."TENANTID",
315 ASE."TENANT_USER_ID",
316 ASE."USER_ID",
317 ASE."SESSION_DATE",
318 USO."DAYS_BETWEEN_SESSIONS",
319 USC."SESSIONS_LAST_60_DAYS",
320 ASE."GEO_CITY",
321 ASE."GEO_COUNTRY",
322 ASE."GEO_METRO",
323 ASE."GEO_REGION",
324 ASE."DEVICE_CATEGORY",
325 ASE."DEVICE_OPERATING_SYSTEM",
326 ASE."DEVICE_WEB_INFO_BROWSER",
327 ASE."STARTING_BROWSER",
328 ASE."ENDING_BROWSER",
329 ASE."SWITCHED_BROWSER",
330 ASE."MAX_VIEWPORT",
331 ASE."MAX_ZOOM_PERCENTAGE",
332 ASE."MAX_ZOOM_PERCENTAGE_PIXELRATIO",
333 ASE."USER_TYPE",
334 ASE."CHURN_PROBABILITY",
335 ASE."ALL_SESSION_EVENT_NAMES",
336 ASE."ALL_SESSION_EVENT_URLS",
337 TD."EVENTS_ON_DESIGNATED_URLS" AS "TOTAL_EVENTS_ON_DESIGNATED_URLS",
338 ASE."TOTAL_EVENTS_IN_SESSION",
339 ASE."TOTAL_UNIQUE_PAGES_IN_SESSION",
340 TD."UNIQUE_PAGES_ON_DESIGNATED_URLS" AS "TOTAL_UNIQUE_PAGES_ON_DESIGNATED_URLS",
341 ASSTATS."ALL_SESSION_DURATION_MINUTES",
342 ASSTATS."BOUNCE_INDICATOR",
343 ASSTATS."SESSION_LENGTH_CATEGORY",
344 CASE
345 WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" > 0
346 THEN ROUND(ASSTATS."ALL_EVENT_COUNT" / ASSTATS."ALL_SESSION_DURATION_MINUTES", 2)
347 ELSE 0
348 END AS "CLICKS_PER_MINUTE_ALL_SESSION",
349 CASE
350 WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" > 0
351 THEN ROUND(ASSTATS."ALL_PAGE_COUNT" / ASSTATS."ALL_SESSION_DURATION_MINUTES", 2)
352 ELSE 0
353 END AS "PAGES_PER_MINUTE_ALL_SESSION",
354 -- Percentage of designated URL events relative to all session events
355 CASE
356 WHEN ASSTATS."ALL_EVENT_COUNT" > 0
357 THEN ROUND((TD."EVENTS_ON_DESIGNATED_URLS"::FLOAT / ASSTATS."ALL_EVENT_COUNT") * 100, 2)
358 ELSE 0
359 END AS "PERCENTAGE_DESIGNATED_IN_ALL",
360 -- Convert total time on designated URLs from seconds to minutes
361 COALESCE(TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS", 0) / 60.0 AS "TOTAL_TIME_ON_DESIGNATED_URLS_MINUTES",
362 CASE
363 WHEN COALESCE(TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS", 0) > 0
364 THEN ROUND(TD."EVENTS_ON_DESIGNATED_URLS" / (TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS" / 60.0), 2)
365 ELSE 0
366 END AS "EVENTS_PER_MINUTE_DESIGNATED_URLS",
367 CASE
368 WHEN COALESCE(TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS", 0) > 0
369 THEN ROUND(TD."UNIQUE_PAGES_ON_DESIGNATED_URLS" / (TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS" / 60.0), 2)
370 ELSE 0
371 END AS "UNIQUE_PAGES_PER_MINUTE_DESIGNATED_URLS",
372 -- Engagement Score for the overall session: a weighted combination of events, unique pages, and duration factors
373 CASE
374 WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" > 0
375 THEN (ASE."TOTAL_EVENTS_IN_SESSION" * 0.4
376 + ASE."TOTAL_UNIQUE_PAGES_IN_SESSION" * 0.4
377 + (CASE WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" < 10 THEN 0.5 ELSE 1 END) * 0.2)
378 ELSE 0
379 END AS "ENGAGEMENT_SCORE_ALL_SESSION",
380 -- Engagement Score for designated URL events
381 CASE
382 WHEN COALESCE(TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS", 0) > 0
383 THEN (TD."EVENTS_ON_DESIGNATED_URLS" * 0.4
384 + TD."UNIQUE_PAGES_ON_DESIGNATED_URLS" * 0.4
385 + (CASE WHEN (TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS" / 60.0) < 10 THEN 0.5 ELSE 1 END) * 0.2)
386 ELSE 0
387 END AS "ENGAGEMENT_SCORE_DESIGNATED_URLS",
388 -- New: Session Engagement Category for overall session
389 CASE
390 WHEN
391 (CASE
392 WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" > 0
393 THEN (ASE."TOTAL_EVENTS_IN_SESSION" * 0.4
394 + ASE."TOTAL_UNIQUE_PAGES_IN_SESSION" * 0.4
395 + (CASE WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" < 10 THEN 0.5 ELSE 1 END) * 0.2)
396 ELSE 0
397 END) < 15 THEN 'Low'
398 WHEN
399 (CASE
400 WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" > 0
401 THEN (ASE."TOTAL_EVENTS_IN_SESSION" * 0.4
402 + ASE."TOTAL_UNIQUE_PAGES_IN_SESSION" * 0.4
403 + (CASE WHEN ASSTATS."ALL_SESSION_DURATION_MINUTES" < 10 THEN 0.5 ELSE 1 END) * 0.2)
404 ELSE 0
405 END) BETWEEN 15 AND 30 THEN 'Medium'
406 ELSE 'High'
407 END AS "SESSION_ENGAGEMENT_CATEGORY_ALL",
408 -- New: Session Engagement Category for designated URL events
409 CASE
410 WHEN
411 (CASE
412 WHEN COALESCE(TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS", 0) > 0
413 THEN (TD."EVENTS_ON_DESIGNATED_URLS" * 0.4
414 + TD."UNIQUE_PAGES_ON_DESIGNATED_URLS" * 0.4
415 + (CASE WHEN (TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS" / 60.0) < 10 THEN 0.5 ELSE 1 END) * 0.2)
416 ELSE 0
417 END) < 5 THEN 'Low'
418 WHEN
419 (CASE
420 WHEN COALESCE(TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS", 0) > 0
421 THEN (TD."EVENTS_ON_DESIGNATED_URLS" * 0.4
422 + TD."UNIQUE_PAGES_ON_DESIGNATED_URLS" * 0.4
423 + (CASE WHEN (TD."TOTAL_TIME_ON_DESIGNATED_URLS_SECONDS" / 60.0) < 10 THEN 0.5 ELSE 1 END) * 0.2)
424 ELSE 0
425 END) BETWEEN 5 AND 10 THEN 'Medium'
426 ELSE 'High'
427 END AS "SESSION_ENGAGEMENT_CATEGORY_DESIGNATED"
428FROM AggregatedSessionEvents ASE
429LEFT JOIN TimeOnDesignatedURLs TD ON ASE."SESSION_ID_USER_ID" = TD."SESSION_ID_USER_ID"
430LEFT JOIN AllSessionStats ASSTATS ON ASE."SESSION_ID_USER_ID" = ASSTATS."SESSION_ID_USER_ID"
431LEFT JOIN UserSessionOrder USO ON ASE."SESSION_ID_USER_ID" = USO."SESSION_ID_USER_ID"
432LEFT JOIN UserSessionCounts USC ON ASE."TENANT_USER_ID" = USC."TENANT_USER_ID"
433ORDER BY ASE."SESSION_ID_USER_ID";
434Check out the GA Data project where these SQL scripts drive interactive dashboards that helped improve product features and user satisfaction.
View the GA Data Project