Как рассчитать время сеанса и сеанса в необработанных данных Firebase Analytics?

Question

Как рассчитать длительность сеанса в исходных данных аналитики Firebase, связанных с BigQuery?

Я использовал следующий блог для вычисления пользователей с помощью команды flatten для событий, которые вложены в каждую запись, но я хотел бы знать, как действовать при расчете продолжительности сеанса и сеанса по стране и времени.

(У меня настроено много приложений, но если бы вы могли помочь мне с запросом SQL для расчета продолжительности сеанса и сеанса, это было бы очень полезно)

Блог Google об использовании Firebase и больших запросов

Answer 1

Сначала вам нужно определить сессию - в следующем запросе я собираюсь разбить сессию, когда пользователь неактивен более 20 минут.

Теперь, чтобы найти все сеансы с SQL, вы можете воспользоваться трюком, описанным по адресу https://blog.modeanalytics.com/finding-user-sessions-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

Answer 2

С новой схемой Firebase в BigQuery я обнаружил, что ответ @Maziar не работает для меня, но я не уверен, почему. Вместо этого я использовал следующее, чтобы вычислить его, где сеанс определяется как пользователь, взаимодействующий с вашим приложением в течение минимум 10 секунд, и где сеанс останавливается, если пользователь не взаимодействует с приложением в течение 30 минут. Он предоставляет общее количество сеансов и продолжительность сеанса в минутах и ​​основан на этом запросе: https://modeanalytics.com/modeanalytics/reports/5e7d902f82de/queries/2cf4af47dba4

SELECT COUNT(*) AS sessions,
       AVG(length) AS average_session_length
  FROM (
  
SELECT global_session_id,
       (MAX(event_timestamp) - MIN(event_timestamp))/(60 * 1000 * 1000) AS length
  FROM (
SELECT user_pseudo_id,
       event_timestamp,
       SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) AS global_session_id,
       SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS user_session_id
  FROM (
       SELECT *,
              CASE WHEN event_timestamp - last_event >= (30*60*1000*1000) 
                     OR last_event IS NULL 
                   THEN 1 ELSE 0 END AS is_new_session
         FROM (
              SELECT user_pseudo_id,
                     event_timestamp,
                     LAG(event_timestamp,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_event
                FROM `dataset.events_2019*`
              ) last
       ) final
       ) session
 GROUP BY 1
       
       ) agg
WHERE length >= (10/60)

Answer 3

Как вы знаете, Google изменил схему баз данных BigQuery Firebase: 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
)

Примечание: измените имя набора данных и имя_таблицы на основе информации о вашем проекте

Пример результата:

Answer 4

С недавним изменением, внесенным в каждую строку событий в таблице BigQuery, вы можете гораздо легче рассчитать количество сеансов и среднюю длину сеанса.

Ценность останется неизменным для всего сеанса, поэтому вам не нужно определять сеанс отдельно.

Вы берете минимальное и максимальное значение столбец, сгруппировав результат по user_pseudo_id, ga_session_id и event_date, чтобы вы могли получить продолжительность сеанса конкретного сеанса любого пользователя в любую заданную дату.

      WITH 
UserSessions as (
SELECT
        user_pseudo_id,
        event_timestamp,
        event_date,
        (Select value.int_value from UNNEST(event_params) where key = "ga_session_id") as session_id,
        event_name
      FROM `projectname.dataset_name.events_*`
),
SessionDuration as (
SELECT
user_pseudo_id,
session_id,
COUNT(*) AS events,
TIMESTAMP_DIFF(MAX(TIMESTAMP_MICROS(event_timestamp)), MIN(TIMESTAMP_MICROS(event_timestamp)), SECOND) AS session_duration
,event_date
FROM
UserSessions
WHERE session_id is not null
GROUP BY
user_pseudo_id,
session_id
,event_date
)
Select count(session_id) as NumofSessions,avg(session_duration) as AverageSessionLength from SessionDuration 

Наконец, вы просто подсчитываете session_id, чтобы получить общее количество сеансов, и вычисляете среднее значение продолжительности сеанса, чтобы получить значение средней продолжительности сеанса.