[SQL] 어떻게 중포 기지 분석 원시 데이터에 세션 및 세션 기간을 계산?

어떻게 중포 기지 분석 원시 데이터에 세션 및 세션 기간을 계산?

어떻게 BigQuery에 연결되어 중포 기지 분석 원시 데이터에서 세션 시간을 계산?

나는 각 레코드에 중첩 된 이벤트에 대한 패턴 화 된 명령을 사용하여 사용자를 계산하려면 다음 블로그를 사용했다,하지만 난 국가 및 시간을 기준으로 세션 및 세션 기간을 계산 진행하는 방법을 알고 싶습니다.

(나는 많은 응용 프로그램이 구성되어 있지만, 세션 시간과 세션을 계산하는 SQL 쿼리를 도와 줄 수 있다면, 그것은 엄청난 도움이 될 것입니다)

중포 기지와 큰 쿼리를 사용하는 방법에 대한 Google 블로그

해결법

1. ==============================

   1.나는 사용자가 20 분 이상 비활성 상태 일 때마다 세션을 깰거야 다음 쿼리에서 - 먼저 세션을 정의 할 필요가있다.

   나는 사용자가 20 분 이상 비활성 상태 일 때마다 세션을 깰거야 다음 쿼리에서 - 먼저 세션을 정의 할 필요가있다.

   이제, 당신이 트릭 https://blog.modeanalytics.com/finding-user-sessions-sql/에 설명 사용할 수있는 SQL 모든 세션을 찾을 수 있습니다.

   다음 쿼리는 모든 세션과 길이를 찾습니다

   #standardSQL
   
   SELECT app_instance_id, sess_id, MIN(min_time) sess_start, MAX(max_time) sess_end, COUNT(*) records, MAX(sess_id) OVER(PARTITION BY app_instance_id) total_sessions,
      (ROUND((MAX(max_time)-MIN(min_time))/(1000*1000),1)) sess_length_seconds
   FROM (
     SELECT *, SUM(session_start) OVER(PARTITION BY app_instance_id ORDER BY min_time) sess_id
     FROM (
       SELECT *, IF(
                   previous IS null 
                   OR (min_time-previous)>(20*60*1000*1000),  # sessions broken by this inactivity 
                   1, 0) session_start 
                   #https://blog.modeanalytics.com/finding-user-sessions-sql/
       FROM (
         SELECT *, LAG(max_time, 1) OVER(PARTITION BY app_instance_id ORDER BY max_time) previous
         FROM (
           SELECT user_dim.app_info.app_instance_id
             , (SELECT MIN(timestamp_micros) FROM UNNEST(event_dim)) min_time
             , (SELECT MAX(timestamp_micros) FROM UNNEST(event_dim)) max_time
           FROM `firebase-analytics-sample-data.ios_dataset.app_events_20160601`
         )
       )
     )
   )
   GROUP BY 1, 2
   ORDER BY 1, 2
   

2. ==============================

   2.BigQuery에에서 중포 기지의 새 스키마로, 나는 @Maziar에 의한 대답은 나를 위해 작동하지 않은 것을 발견,하지만 확실 왜 안입니다. 대신에 나는 세션이 10 초 최소 경우 사용자가 30 분 동안 응용 프로그램에 참여하지 않으면 세션이 중지에 대한 귀하의 응용 프로그램과 함께 참여 사용자로 정의되는 경우,이를 계산하려면 다음을 사용했다. 그것은 총 세션 수 분에서 세션 길이를 제공하며,이 쿼리를 기반으로 : https://modeanalytics.com/modeanalytics/reports/5e7d902f82de/queries/2cf4af47dba4

   BigQuery에에서 중포 기지의 새 스키마로, 나는 @Maziar에 의한 대답은 나를 위해 작동하지 않은 것을 발견,하지만 확실 왜 안입니다. 대신에 나는 세션이 10 초 최소 경우 사용자가 30 분 동안 응용 프로그램에 참여하지 않으면 세션이 중지에 대한 귀하의 응용 프로그램과 함께 참여 사용자로 정의되는 경우,이를 계산하려면 다음을 사용했다. 그것은 총 세션 수 분에서 세션 길이를 제공하며,이 쿼리를 기반으로 : https://modeanalytics.com/modeanalytics/reports/5e7d902f82de/queries/2cf4af47dba4

   세션 AS SELECT COUNT (*),        AVG (길이)의 AS average_session_length   FROM (    SELECT는 global_session_id        (MAX (event_timestamp) - MIN (event_timestamp)) / (60 * 1000 * 1000)의 길이 AS   FROM ( SELECT는 user_pseudo_id        event_timestamp,        AS SUM (is_new_session) OVER (user_pseudo_id, event_timestamp BY ORDER) global_session_id,        SUM (is_new_session) OVER (event_timestamp BY user_pseudo_id ORDER BY PARTITION) user_session_id AS   FROM (        고르다 *,               CASE WHEN event_timestamp - last_event> = (30 * 60 * 1000 * 1000)                      OR last_event는 NULL은 IS                    is_new_session AS THEN 1 ELSE 0 END          FROM (               SELECT는 user_pseudo_id                      event_timestamp,                      last_event AS LAG (event_timestamp 1) OVER (event_timestamp BY user_pseudo_id ORDER BY PARTITION)                 `dataset.events_2019 *`FROM               ) 마지막        ) 최종        ) 세션  GROUP BY 1                ) AGG WHERE 길이> = (60분의 10)

3. ==============================

   3.아시다시피, 구글 BigQuery에서의 중포 기지 데이터베이스의 스키마를 변경 : https://support.google.com/analytics/answer/7029846

   아시다시피, 구글 BigQuery에서의 중포 기지 데이터베이스의 스키마를 변경 : https://support.google.com/analytics/answer/7029846

   @Felipe 대답 덕분에, 새로운 포맷은 다음과 같이 변경됩니다 :

   SELECT SUM(total_sessions) AS Total_Sessions, AVG(sess_length_seconds) AS Average_Session_Duration
   FROM (
     SELECT user_pseudo_id, sess_id, MIN(min_time) sess_start, MAX(max_time) sess_end, COUNT(*) records, 
       MAX(sess_id) OVER(PARTITION BY user_pseudo_id) total_sessions,
       (ROUND((MAX(max_time)-MIN(min_time))/(1000*1000),1)) sess_length_seconds
     FROM (
       SELECT *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY min_time) sess_id
       FROM (
         SELECT *, IF(previous IS null OR (min_time-previous) > (20*60*1000*1000), 1, 0) session_start 
         FROM (
           SELECT *, LAG(max_time, 1) OVER(PARTITION BY user_pseudo_id ORDER BY max_time) previous
           FROM (SELECT user_pseudo_id, MIN(event_timestamp) AS min_time, MAX(event_timestamp) AS max_time
             FROM `dataset_name.table_name` GROUP BY user_pseudo_id)
         )
       )
     )
     GROUP BY 1, 2
     ORDER BY 1, 2
   )
   

   참고 : 프로젝트 정보에 따라 변경 dataset_name 및 TABLE_NAME

   샘플 결과 :

from https://stackoverflow.com/questions/42546815/how-to-calculate-session-and-session-duration-in-firebase-analytics-raw-data by cc-by-sa and MIT license