Аналитика сессий в BigQuery 101

Опубликовано: 15 мар 2019

Ниже описан пример построения алгоритма анализа сессий из сырых данных, сохраненных в BigQuery.

Первым делом подключим в наш Jupyter Notebook библиотеки BigQuery:

In [1]:

from google.cloud import bigquery
import os
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'datawhbq.json'
client = bigquery.Client()
%load_ext google.cloud.bigquery

Создадим таблицу данных в качестве примера на котором мы будем строить алгоритм. В этом же певом SQL запросе с помощью функцией LAG мы для каждой строчки добавляем предыдущие значением поля timestamp, которое будет имитировать таймстемп хита:

In [2]:

%%bigquery
SELECT chunk.user, chunk.timestamp, LAG(chunk.timestamp,1) OVER(PARTITION BY chunk.user ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<user STRING, timestamp INT64>('user1',5),('user2',9),('user1',15),('user1',51),
  ('user2',12),('user1',60),('user2',92),('user2',82),('user1',90),('user2',192)]) AS chunk
ORDER BY timestamp

Out[2]:

	user	timestamp	previos
0	user1	5	NaN
1	user2	9	NaN
2	user2	12	9.0
3	user1	15	5.0
4	user1	51	15.0
5	user1	60	51.0
6	user2	82	12.0
7	user1	90	60.0
8	user2	92	82.0
9	user2	192	92.0

Взяв данный запрос за основу, постепенно начнем добавлять расчет нужных нам данных. Вначале добавим колонку со значением разницы между текущим и предыдущим данными timestamp и колонку в которой определим начало условной сессии на основе данного посчитанного интервала. Пусть началом сессии будет каждая строка в которой интервал между значениями timestamp больше 15-и.

In [3]:

%%bigquery
WITH a AS (
SELECT chunk.user, chunk.timestamp, LAG(chunk.timestamp,1) OVER(PARTITION BY chunk.user ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<user STRING, timestamp INT64>('user1',5),('user2',9),('user1',15),('user1',51),
  ('user2',12),('user1',60),('user2',92),('user2',82),('user1',90),('user2',192)]) AS chunk
  ORDER BY timestamp)
SELECT *, timestamp-previos dif,
          CASE WHEN timestamp-previos>15 OR previos IS NULL THEN true
          ELSE false END AS newsess
FROM a

Out[3]:

	user	timestamp	previos	dif	newsess
0	user1	5	NaN	NaN	True
1	user2	9	NaN	NaN	True
2	user2	12	9.0	3.0	False
3	user1	15	5.0	10.0	False
4	user1	51	15.0	36.0	True
5	user1	60	51.0	9.0	False
6	user2	82	12.0	70.0	True
7	user1	90	60.0	30.0	True
8	user2	92	82.0	10.0	False
9	user2	192	92.0	100.0	True

С посощью функции COUNTIF пронумеруем сесии для каждого пользователя:

In [4]:

%%bigquery
WITH a AS (
SELECT chunk.user, chunk.timestamp, LAG(chunk.timestamp,1) OVER(PARTITION BY chunk.user ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<user STRING, timestamp INT64>('user1',5),('user2',9),('user1',15),('user1',51),
  ('user2',12),('user1',60),('user2',92),('user2',82),('user1',90),('user2',192)]) AS chunk
  ORDER BY timestamp)
SELECT *, COUNTIF(sess) OVER(PARTITION BY user ORDER BY timestamp) AS sessnum  #number of session for a particular user
FROM (
  SELECT *, timestamp-previos dif,
          CASE WHEN timestamp-previos>15 OR previos IS NULL THEN true
          ELSE false END AS sess
  FROM a
)

Out[4]:

	user	timestamp	previos	dif	sess	sessnum
0	user1	5	NaN	NaN	True	1
1	user1	15	5.0	10.0	False	1
2	user1	51	15.0	36.0	True	2
3	user1	60	51.0	9.0	False	2
4	user1	90	60.0	30.0	True	3
5	user2	9	NaN	NaN	True	1
6	user2	12	9.0	3.0	False	1
7	user2	82	12.0	70.0	True	2
8	user2	92	82.0	10.0	False	2
9	user2	192	92.0	100.0	True	3

Расширим алгоритм подсчета на случай с разбивкой по датам:

In [5]:

%%bigquery
WITH a AS (
SELECT chunk.date, chunk.user, chunk.timestamp, LAG(chunk.timestamp,1) 
      OVER(PARTITION BY chunk.user,chunk.date ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<date STRING, user STRING, timestamp INT64>('day1', 'user1',5),
      ('day1','user2',9),('day1','user1',15),('day1','user1',51),
      ('day1','user2',12),('day1','user2',15),('day2','user1',65),
      ('day2','user1',60),('day2','user2',92),('day2','user2',82),
      ('day2','user1',90),('day2','user2',192)]) AS chunk
  ORDER BY timestamp)
SELECT *, COUNTIF(sess) OVER(PARTITION BY user,date ORDER BY timestamp) AS sessnum
FROM (
  SELECT *, timestamp-previos dif,
          CASE WHEN timestamp-previos>15 OR previos IS NULL THEN true
          ELSE false END AS sess
  FROM a
)

Out[5]:

	date	user	timestamp	previos	dif	sess	sessnum
0	day1	user1	5	NaN	NaN	True	1
1	day1	user1	15	5.0	10.0	False	1
2	day1	user1	51	15.0	36.0	True	2
3	day2	user1	60	NaN	NaN	True	1
4	day2	user1	65	60.0	5.0	False	1
5	day2	user1	90	65.0	25.0	True	2
6	day1	user2	9	NaN	NaN	True	1
7	day1	user2	12	9.0	3.0	False	1
8	day1	user2	15	12.0	3.0	False	1
9	day2	user2	82	NaN	NaN	True	1
10	day2	user2	92	82.0	10.0	False	1
11	day2	user2	192	92.0	100.0	True	2

Расширим алгоритм дальше и пронумеруем хиты внутри каждой сессии:

In [6]:

%%bigquery
WITH a AS (
SELECT chunk.date, chunk.user, chunk.timestamp, LAG(chunk.timestamp,1)
    OVER(PARTITION BY chunk.user,chunk.date ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<date STRING, user STRING, timestamp INT64>('day1', 'user1',5),
       ('day1','user2',9),('day1','user1',15),('day1','user1',16),
       ('day1','user1',49),('day1','user1',51),('day1','user2',12),
       ('day1','user2',16),('day1','user2',15),('day2','user1',65),
       ('day2','user1',60),('day2','user1',92),('day2','user1',91),
       ('day2','user2',92),('day2','user2',82), ('day2','user1',90),
       ('day2','user2',192),('day2','user2',193),('day2','user2',194)]) AS chunk
  ORDER BY timestamp)
SELECT *, DENSE_RANK() OVER(PARTITION BY sessnum,user,date ORDER BY timestamp) AS hitnumper
FROM (
  SELECT *, COUNTIF(sess) OVER(PARTITION BY user,date ORDER BY timestamp) AS sessnum
  FROM (
    SELECT *, timestamp-previos dif,
            CASE WHEN timestamp-previos>15 OR previos IS NULL THEN true
            ELSE false END AS sess
    FROM a
  )
)
ORDER BY user,timestamp

Out[6]:

	date	user	timestamp	previos	dif	sess	sessnum	hitnumper
0	day1	user1	5	NaN	NaN	True	1	1
1	day1	user1	15	5.0	10.0	False	1	2
2	day1	user1	16	15.0	1.0	False	1	3
3	day1	user1	49	16.0	33.0	True	2	1
4	day1	user1	51	49.0	2.0	False	2	2
5	day2	user1	60	NaN	NaN	True	1	1
6	day2	user1	65	60.0	5.0	False	1	2
7	day2	user1	90	65.0	25.0	True	2	1
8	day2	user1	91	90.0	1.0	False	2	2
9	day2	user1	92	91.0	1.0	False	2	3
10	day1	user2	9	NaN	NaN	True	1	1
11	day1	user2	12	9.0	3.0	False	1	2
12	day1	user2	15	12.0	3.0	False	1	3
13	day1	user2	16	15.0	1.0	False	1	4
14	day2	user2	82	NaN	NaN	True	1	1
15	day2	user2	92	82.0	10.0	False	1	2
16	day2	user2	192	92.0	100.0	True	2	1
17	day2	user2	193	192.0	1.0	False	2	2
18	day2	user2	194	193.0	1.0	False	2	3

Базовый алгоритм готов. Теперь на основе него мы можем проводить анализ основных показателей для сессий. Например, посчитаем количество хитов вначале за каждую сессиию, а затем среднее на всем интервале:

In [7]:

%%bigquery
WITH a AS (
SELECT chunk.date, chunk.user, chunk.timestamp, LAG(chunk.timestamp,1)
    OVER(PARTITION BY chunk.user,chunk.date ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<date STRING, user STRING, timestamp INT64>('day1', 'user1',5),
       ('day1','user2',9),('day1','user1',15),('day1','user1',16),
       ('day1','user1',49),('day1','user1',51),('day1','user2',12),
       ('day1','user2',16),('day1','user2',15),('day2','user1',65),
       ('day2','user1',60),('day2','user1',92),('day2','user1',91),
       ('day2','user2',92),('day2','user2',82), ('day2','user1',90),
       ('day2','user2',192),('day2','user2',193),('day2','user2',194)]) AS chunk
  ORDER BY timestamp)
SELECT user,date,sessnum,MAX(hitnumper) sesshits FROM (
  SELECT *, DENSE_RANK() OVER(PARTITION BY sessnum,user,date ORDER BY timestamp) AS hitnumper
  FROM (
    SELECT *, COUNTIF(sess) OVER(PARTITION BY user,date ORDER BY timestamp) AS sessnum
    FROM (
      SELECT *, timestamp-previos dif,
              CASE WHEN timestamp-previos>15 OR previos IS NULL THEN true
              ELSE false END AS sess
      FROM a
    )
  )
  ORDER BY user,timestamp
)
GROUP BY user,date,sessnum
ORDER BY date,user,sessnum

Out[7]:

	user	date	sessnum	sesshits
0	user1	day1	1	3
1	user1	day1	2	2
2	user2	day1	1	4
3	user1	day2	1	2
4	user1	day2	2	3
5	user2	day2	1	2
6	user2	day2	2	3

In [8]:

%%bigquery
WITH a AS (
SELECT chunk.date, chunk.user, chunk.timestamp, LAG(chunk.timestamp,1)
    OVER(PARTITION BY chunk.user,chunk.date ORDER BY chunk.timestamp) previos
  FROM UNNEST([STRUCT<date STRING, user STRING, timestamp INT64>('day1', 'user1',5),
       ('day1','user2',9),('day1','user1',15),('day1','user1',16),
       ('day1','user1',49),('day1','user1',51),('day1','user2',12),
       ('day1','user2',16),('day1','user2',15),('day2','user1',65),
       ('day2','user1',60),('day2','user1',92),('day2','user1',91),
       ('day2','user2',92),('day2','user2',82), ('day2','user1',90),
       ('day2','user2',192),('day2','user2',193),('day2','user2',194)]) AS chunk
  ORDER BY timestamp)
SELECT CAST(ROUND(AVG(sesshits)) AS INT64) avghits FROM (
    SELECT user,date,sessnum,MAX(hitnumper) sesshits FROM (
      SELECT *, DENSE_RANK() OVER(PARTITION BY sessnum,user,date ORDER BY timestamp) AS hitnumper
      FROM (
        SELECT *, COUNTIF(sess) OVER(PARTITION BY user,date ORDER BY timestamp) AS sessnum
        FROM (
          SELECT *, timestamp-previos dif,
                  CASE WHEN timestamp-previos>15 OR previos IS NULL THEN true
                  ELSE false END AS sess
          FROM a
        )
      )
      ORDER BY user,timestamp
    )
    GROUP BY user,date,sessnum
    ORDER BY date,user,sessnum
)

Out[8]:

	avghits
0	3

Вопрос целесообразности применения сессии, как аналитической единицы обсуждается давно и долго. Разные аналитические системы могут использовать не одинаковые алгоритмы для подсчета сессий, или же иметь разные установочные настройки, например временной интервал бездействия после которого начинается новая сессия. Сессия — это действительно скорее некий индивидуальный параметр, который должен соответствовать объективным особенностям конкретного проекта. И в этом свете как раз сырые неагрегированные данные позволяют вам при необходимости подсчитывать сессии очень гибко, опираясь на заданные вами параметры, которые формируются, исходя из особенностей вашего онлайн проекта.

← ClickHouse на Windows 10 & Jupyter Notebook для анализа данных Logs API

Автоматическая загрузка json данных из файла с помощью Cloud Functions в BigQuery →