В предыдущей статье мы рассмотрели команды SQL, необходимые для расчета DAU, WAU и MAU. А теперь расскажем, как посчитать монетизационные метрики этой аудитории при помощи SQL.
Вспомним, какие метрики могут понадобиться для определения уровня дохода от игры или приложения.
Gross – сумма платежей за выбранный период.
Paying Users (платящие пользователи) – число всех уникальных пользователей, активных за определенный период, которые совершили как минимум один платёж.
Average Check – сумма среднего платежа.
Transactions – общее количество транзакций за определённый период.
ARPPU (Average Revenue Per Paying User) – средний доход, полученный от платящего пользователя и одна из самых важных монетизационных метрик. Необходима для того, чтобы понять ценность проекта с точки зрения платящих пользователей. Чтобы узнать ARPPU, необходимо разделить доход за выбранный период на количество пользователей, совершивших хотя бы один платёж.
Читайте также: Главные метрики. ARPPU
Монетизационные метрики в дневной динамике
Посчитаем основные монетизационные метрики с помощью SQL. Для этого используем редактор SQL запросов devtodev. Залогиньтесь на сайте, зайдите в демо и найдите SQL отчёт во вкладке Reports.
В devtodev все метрики приложения считаются автоматически; к ним также можно применять огромное разнообразие фильтров без использования SQL.
Обратимся к таблице payments, в которой содержатся все данные о платежах, совершённых пользователями за реальную валюту. Значение параметра priceusd равно стоимости купленного товара, переведённой из локальной валюты в USD в момент совершения платежа, а параметр devtodevid – пользователь, совершивший событие платежа.
select to_char(eventtime, 'YYYY-mm-dd') as day
, sum(priceusd) as "Gross"
, count(devtodevid) as "Transactions"
,count(distinct devtodevid) as "Paying users"
,avg(priceusd) as "Average check"
,sum(priceusd)/count(distinct devtodevid)) as "ARPPU"
from p102968.payments
where eventtime>=current_date - interval '1 month'
and eventtime < current_date
and tester is false
and cheater is false
group by 1
order by 1 asc
Тогда,
-
Gross – sum(priceusd) – сумма параметра priceusd;
-
Transactions – count(devtodevid) – количество событий (платежей);
-
Paying users – count(distinct devtodevid) – количество уникальных пользователей, совершивших платёж;
-
Average check – avg(priceusd) – среднее значение параметра priceusd (можно посчитать как sum(priceusd)/count(devtodevid));
-
ARPPU – sum(priceusd)/count(distinct devtodevid) – отношение суммы всех платежей к количеству заплативших пользователей.
Так, в простом запросе можно получить основные монетизационные метрики, используя лишь одну группировку и агрегирующие функции.
Топ стран по Paying Users и их ARPPU
Теперь мы хотим узнать, из каких стран приходит больше всего платящих пользователей, а также сколько в среднем приносит каждый такой пользователь. Для этого нужно сгруппировать наши данные из payments по параметру country, который также имеется в таблице.
select country
, count(distinct devtodevid) as "Paying users"
, sum(priceusd)/(distinct devtodevid) as "ARPPU"
from p102968.payments
where eventtime>=current_date - interval '1 month'
and eventtime < current_date
and tester is false
and cheater is false
group by 1
order by 1 asc
group by country
order by "Paying users" desc
limit 5
Таким образом, если мы сделаем сортировку по возрастанию, то limit 5 выведет список из стран с наименьшим количеством платящих за последний месяц.
Допустим, на одном дашборде отдельно анализируется подробная статистика по первым 5 странам, а на другом необходимо отразить следующие 5 стран (6-10) по количеству платящих пользователей. Для этого в конце запроса необходимо добавить:
limit 5 offset 5
Если необходимо оставить все страны за исключением первых пяти, команду limit можно не использовать.
Результат запроса мы хотим добавить на дашборд в табличном виде, поэтому сразу же отформатируем эти данные, изменив select:
'$'||round((sum(priceusd)/count(distinct devtodevid))::numeric,2)||'per paying user' as "ARPPU"
Первым делом мы добавили знак $ к значению ARPPU. С помощью одинарных кавычек добавляем строку с текстом, который нам нужен, а с помощью оператора || (concatenate) объединяем строковое значение с вычисленным значением ARPPU.
Не стоит форматировать данные, если вы планируете далее их обрабатывать в другом запросе или, например, с помощью электронных таблиц.
Также мы округлили значение ARPPU (ранее оно имело 12 знаков после запятой) с помощью функции round(), первый аргумент которой – округляемое число, приведённое к типу numeric, а второй аргумент – необходимое количество знаков после запятой.
Таким образом, мы узнали, как с помощью одного простого запроса SQL можно получить основные метрики монетизации продукта, а также научились группировать пользователей из разных стран по полученному доходу. В следующей статье разберём, как с помощью SQL узнать структуру покупок пользователей.
