SQL для начинающих: считаем средний чек, количество платежей и ARPPU

RU
Как узнать основные монетизационные метрики и как определить страны с наибольшим количеством платящих пользователей с помощью одного простого SQL-запроса.
SQL для начинающих: считаем средний чек, количество платежей и ARPPU
Published
02.09.2021
|
devtodev

В предыдущей статье мы рассмотрели команды 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

Результат SQL-запроса
Результат запроса. Скриншот из демо devtodev

Тогда, 

  • 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) – отношение суммы всех платежей к количеству заплативших пользователей.

Так, в простом запросе можно получить основные монетизационные метрики, используя лишь одну группировку и агрегирующие функции.

Результат SQL-запроса на графике
Результат запроса. Скриншот из демо devtodev

Топ стран по 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

Результат SQL-запроса
Результат запроса. Скриншот из демо devtodev
Добавив в самом конце запроса limit 5, мы ограничили количество выводимых строк в результате. Запрос выполняется в полном объёме, считая в select и подготавливая к выводу все данные, сортирует их, и, в самом конце, оставляет только N строк.

Таким образом, если мы сделаем сортировку по возрастанию, то limit 5 выведет список из стран с наименьшим количеством платящих за последний месяц.

Допустим, на одном дашборде отдельно анализируется подробная статистика по первым 5 странам, а на другом необходимо отразить следующие 5 стран (6-10) по количеству платящих пользователей. Для этого в конце запроса необходимо добавить:

limit offset 5

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

Результат запроса мы хотим добавить на дашборд в табличном виде, поэтому сразу же отформатируем эти данные, изменив select:

'$'||round((sum(priceusd)/count(distinct devtodevid))::numeric,2)||'per paying user' as "ARPPU"  

Результат SQL-запроса
Результат запроса. Скриншот из демо devtodev

Первым делом мы добавили знак $ к значению ARPPU. С помощью одинарных кавычек добавляем строку с текстом, который нам нужен, а с помощью оператора || (concatenate) объединяем строковое значение с вычисленным значением ARPPU.

Не стоит форматировать данные, если вы планируете далее их обрабатывать в другом запросе или, например, с помощью электронных таблиц.

Также мы округлили значение ARPPU (ранее оно имело 12 знаков после запятой) с помощью функции round(), первый аргумент которой – округляемое число, приведённое к типу numeric, а второй аргумент – необходимое количество знаков после запятой.

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

Read more