SQL для начинающих: как считать DAU, WAU, MAU

RU
Как узнать количество активных пользователей за день, неделю и месяц с помощью SQL и какие команды для этого понадобятся
SQL для начинающих: как считать DAU, WAU, MAU
Published
29.07.2021
|
devtodev

В прошлой статье мы рассказали о том, что такое SQL и каким специалистам он может понадобиться, а также разобрали базовые команды на примере простого запроса. А теперь научимся рассчитывать метрики активности пользователей игры или приложения.

Предположим, вы уже запустили свой проект. Его аудитория будет увеличиваться ежедневно. Впервые зайдя в приложение, кто-то продолжит им пользоваться регулярно, кто-то – время от времени, а некоторые быстро потеряют интерес. Тем не менее эти сегменты пользователей считаются активными, т.к. в определенный промежуток времени у них была хотя бы одна сессия в приложении. 

Чтобы оценить активную аудиторию приложения и проанализировать ее запросы, вам пригодятся такие метрики, как DAU, WAU, MAU. 

  • DAU (daily active users) – число уникальных пользователей в день;
  • WAU (weekly active users) – число уникальных пользователей в неделю;
  • MAU (monthly active users) – число уникальных пользователей в месяц.

Читайте также: Главные метрики. Активные пользователи (DAU, WAU, MAU)

Попробуем посчитать с помощью SQL количество уникальных пользователей за последние три месяца. Мы будем использовать для этого редактор SQL запросов devtodev. Залогиньтесь на сайте и найдите SQL отчет в демо проекте.

В devtodev все метрики приложения считаются автоматически; к ним также можно применять огромное разнообразие фильтров без использования SQL.

DAU

Рассчитаем DAU для приложения 3 in a row. iOS (идентификатор проекта p102968). Для этого используем таблицу sessions

from p102968.sessions – путь к таблице, с которой мы будем работать. Команда where eventtime>=current_date - interval '3 month' определяет временной интервал, из которого мы будем получать данные. 
Время совершения каждого события (eventtime) из этого интервала мы превращаем в строку с помощью функции to_char(eventtime, 'Month - dd'). Сюда вы можете добавить любые разделители внутри даты, писать год полностью ‘YYYY’ или только последние две цифры ‘YY’, месяц с маленькой буквы в сокращенном формате ‘mon’, в виде цифрового обозначения ‘mm’ или в виде полного названия месяца ‘Month’.

select to_char(eventtime, 'Month - dd') as day
, count(distinct devtodevid) as "DAU"   
from p102968.sessions
where eventtime>=current_date - interval '3 month' 
and eventtime < current_date  
and tester is false  
and cheater is false  
group by 1
order by 1 asc

Как посчитать DAU
Результат запроса. Скриншот из ДЕМО devtodev

Чтобы посчитать количество уникальных пользователей для каждого из этих дней, используем count(distinct devtodevid), где devtodevid – уникальный идентификатор пользователя.

MAU

Для расчета MAU используем те же команды, но заменяем eventtime, 'Month - dd' на eventtime, 'yyyy-mm' внутри функции to_char, таким образом, группировка будет происходить сразу же по месяцам.

select to_char(eventtime, 'yyyy-mm') as month
, count(distinct devtodevid) as "MAU"   
from p102968.sessions
where eventtime>=current_date - interval '3 month' 
and eventtime < current_date  
and tester is false  
and cheater is false  
group by 1
order by 1 asc

Как посчитать MAU
Результат запроса. Скриншот из ДЕМО devtodev

При этом вы, скорее всего, получите результат сразу же за 4 месяца, только два из которых будут отражать реальное MAU. Допустим, вы делаете запрос 10 июня. Тогда самый дальний месяц (март), будет показывать пользователей только за последние 20 дней месяца, а текущий месяц – только тех, кто успел совершить сессии за последние 10 дней. Это легко проверить, добавив в select подсчет уникальных дней, попавших в выборку.

count(distinct(to_char(eventtime, 'yyyy-mm-dd'))) as daysInMonth

Давайте это исправим, чтобы получить MAU только за полные месяцы. Для этого изменим условия.

where eventtime>=ut('2021-04-01')and date_trunc('month',current_date)

Первая часть ограничения по времени eventtime>= ut('2021-04-01') задана напрямую. Мы не рекомендуем использовать такие условия в виджетах и на дашбордах. Гораздо лучше использовать плавающие даты, зависящие от текущей, либо от времени совершения ивентов.
Функция date_trunc('month',current_date) округляет current_date до месяца, оставляя в результате ‘2021-06-01 00:00:00.0’ (если запрос совершался в июне 2021 года). Таким образом, добавив эту функцию в where, можно оставить данные только о полных месяцах.

select to_char(eventtime, 'YYYY - mm') as month
, count(distinct devtodevid) as "MAU"   
from p102968.sessions
where eventtime>= date_trunc('month',current_date) - interval '2 month'
and eventtime date_trunc('month',current_date)
and tester is false  
and cheater is false  
group by 1
order by 1 asc

Как считать MAU
Результат запроса. Скриншот из ДЕМО devtodev

Функция date_trunc, наравне с оператором interval, может принимать совершенно разные параметры: это могут быть ‘year’, ’month’, ’day’, ’hour’, ’minute’, ’second’.

WAU

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

select to_char(date_trunc('week',eventtime), 'YYYY Mon dd')
, count(distinct devtodevid) as "WAU"   
from p102968.sessions
where eventtime>= date_trunc('week',current_date) - interval '10 week'
and eventtime date_trunc('week',current_date
and (eventtime-created) interval '7 day'
and tester is false  
and cheater is false  
group by 1
order by 1 asc

Как считать WAU
Результат запроса. Скриншот из ДЕМО devtodev

Для этого нужно отфильтровать результат, получаемый из таблицы с сессиями. Добавим условие (eventtime-created) > interval '7 day'. Таким образом, для каждой строки в таблице вычисляется разница между событием сессии eventtime и датой установки приложения пользователем created. Если разница будет больше 7 дней, то данные о такой сессии попадают в нашу выборку.

В select запроса можно добавить to_char(date_trunc('week',eventtime), 'YYYY Mon dd') – функция date_trunc округлит дату до недели, а функция to_char приведет данные к красивому виду.

Мы рассмотрели способы вычисления метрик активности пользователей. В следующей статье расскажем о том, как с помощью редактора SQL запросов получить данные о монетизации этой аудитории.

Read more