Количество событий в динамике по дням

Запрос показывает количество событий по дням в процентах по сравнению с предыдущим днем.

Одним из важных процессов аналитика, это контроль, что никакое из важных событий не отвалилось. Давайте представим, что у вас таких событий 100 или больше. Долго ли вы будете проверять? Как быть?

Мы написали такой запрос, в котором происходит расчет событий в периоде, а также сравнение количества событий одного дня с предыдущего. Каждого дня с предыдущим, выраженным коэффициентом разницы или другими словами процентом одного числа от другого.

Результаты этого запроса лучше вывести в BI где вы сможете построить нужное представление, чтобы сразу выявить такие события. Также можно пойти еще глубже и поставить некие оповещения из Google BigQuery на базе возможностей Google Cloud Platform, но об этом уже возможно позже.


WITH
  date_tab AS (
  SELECT
    MY_DATE,
--- 3. Извлекаем день недели
    EXTRACT (DAYOFWEEK FROM MY_DATE) AS DAY_OF_WEEK
  FROM (
    SELECT
--- 2. Определяем границу начала календаря дат
      DATE_ADD ('2020-01-01', INTERVAL param DAY) AS MY_DATE
    FROM
--- 1. Создаем массив значений от нуля до тысячи с интервалом в единицу
      UNNEST (GENERATE_ARRAY (0, 1000, 1)) AS param ))
 
--- Если предыдущее значение не равно нулю,и если количество записей больше количества предыдущих записей, то делим количество всех записей на количество предыдущих записей, а если количество записей не больше количества предыдущих, то делим количество предыдущих записей на количество всех записей по конкретному событию
SELECT
  *,ROUND(IF(prev_cnt IS NOT NULL,IF(cnt > prev_cnt, ((cnt / prev_cnt)*100)-100, 100-((prev_cnt / cnt) *100)),0),1) AS diff_prev_day
FROM (
  SELECT
    ev.date,
    DAY_OF_WEEK AS week_day,
    event_name,
    cnt,
--- Получаем предыдущее значение рассчитанного количества записей отсортированные по дате
    LAG(cnt) OVER (PARTITION BY event_name ORDER BY date ASC) AS prev_cnt
  FROM (
    SELECT
--- Приводим дату в читаемый формат
      PARSE_DATE("%Y%m%d", event_date) AS date,
      event_name,
--- Подсчитываем количество записей в таблице по конкретному событию
      COUNT(*) cnt
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
    GROUP BY
      event_date, event_name
--- Сортируем дату в читаемом формате по возрастанию
    ORDER BY
      PARSE_DATE("%Y%m%d", event_date) ASC) ev
--- Объединяем данные из временной таблицы
  JOIN
    date_tab
  ON
    date_tab.MY_DATE = ev.date
  ORDER BY
    event_name, ev.date ASC) lag_tab

Результат в BigQuery