Mobile/Metrics/Redash: Difference between revisions

Mobile/Metrics/Redash (view source)

Revision as of 14:07, 5 April 2016

1,587 bytes added , 5 April 2016

m

→‎Useful Queries

MarkFinkle

canmove, Confirmed users, Bureaucrats and Sysops emeriti

2,798

edits

@@ Line 135: / Line 135: @@
     submissiondate > current_date - interval '7' day and action = 'loadurl.1'
   group by 1
+=== Finding User Sessions ===
+We don't have an explicit way to determine individual session of user activity in the application. It's pretty hard to have explicit "markers" because the real world tries to corrupt single events like a marker. Other [https://blog.modeanalytics.com/finding-user-sessions-sql/ systems] use [https://segment.com/blog/using-sql-to-define-measure-and-analyze-user-sessions/ gaps] in the flow of events to indicate breaks in user sessions. Google Analytics uses a 30 minute gap, but it really depends on the expected use of the application. Here is how we'd use our events to find a stream of user sessions:
+  select
+    submissiondate,
+    global_session_id,
+    max(ts) - min(ts) as session_length,
+    count(ts) as session_events
+  from (
+    select
+      clientid,
+      submissiondate,
+      ts,
+      sum(is_new_session) over (order by clientid, submissiondate, ts) as global_session_id,
+      sum(is_new_session) over (partition by clientid order by submissiondate, ts) as user_session_id
+    from (
+      select
+        *,
+        -- let's use 5 minute gaps as an indicator of a session break
+        case when (ts - last_ts) >= (1000 * 60 * 5) or last_ts is null then 1 else 0 end as is_new_session
+      from (
+        select
+          clientid,
+          submissiondate,
+          ts,
+          lag(ts, 1) over (partition by clientid, submissiondate order by ts) as last_ts
+        from android_events_v1
+        where
+          submissiondate > current_date - interval '7' day
+          and channel = 'beta'
+      ) t
+    ) f
+  ) s
+  group by 1, 2
 == Resources ==