Запрос показывает количество событий по дням в процентах по сравнению с предыдущим днем. Одним из важных процессов аналитика, это контроль, что никакое из важных событий не отвалилось. Давайте представим, что у вас таких событий 100 или больше. Долго ли вы будете проверять? Как быть?
Мы написали такой запрос, в котором происходит расчет событий в периоде, а также сравнение количества событий одного дня с предыдущего. Каждого дня с предыдущим, выраженным коэффициентом разницы или другими словами процентом одного числа от другого. Результаты этого запроса лучше вывести в BI где вы сможете построить нужное представление, чтобы сразу выявить такие события.
Также можно пойти еще глубже и поставить некие оповещения из Google BigQuery на базе возможностей Google Cloud Platform, но об этом уже возможно позже.
Результат в BigQuery
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
