SQL для начинающих: структура покупок пользователей

RU
Узнайте, как использовать SQL для определения структуры покупок пользователей и того, какие продукты побуждают их платить
SQL для начинающих: структура покупок пользователей
Published
08.10.2021
|
devtodev

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

В этом запросе для каждого product мы считаем количество покупок за месяц count() и сумму дохода, полученного от продажи каждого товара sum(priceusd).

select product, count(devtodevid) as "Transactions"
, sum(priceusd) as "Gross" 
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 3 desc

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

Мы видим интересную картину – offer3 и offer4 пользователи покупают меньше всего, но при этом всего два этих товара приносят большую часть дохода от приложения. В то же время наш starterpack покупают чаще всего, но он генерирует только малую часть дохода.

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

Какой товар конвертирует пользователей в платящих?

С помощью SQL мы также можем узнать, какой товар становится первым приобретением в приложении для пользователей. Для этого воспользуемся стандартным параметром событий paymentcount, добавив его в условие where предыдущего запроса. 

and paymentcount = 1

Таким образом мы выберем только те платежи, которые стали первыми для пользователей.

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

Параметр paymentscount автоматически добавляется к каждому событию, отправляемому в devtodev. С помощью него можно очень быстро отфильтровать действия не платящих пользователей (paymentscount = 0), платящих (paymentscount > 0) или тех, кто сделал конкретное количество платежей.

Если мы хотим посмотреть платежи, совершенные только в первый день жизни в приложении, дополним условие where:

and eventtime - created <=interval '1 day'

А если необходимо выяснить, как пользователи из США и Канады приобретают офферы, можно добавить к where следующее:
and product like '%offer%'
and (country 'US' or country 'CN')

Оператор like сравнивает значение параметра product с шаблоном, который задан в текстовом виде ‘%offer%’. Знаки % позволяют выбрать нам названия, которые содержат внутри себя текст offer. Вы можете дописать что угодно как перед ним (special offer), так и после него (offer1). Страны записываются в виде двухбуквенного кода. Выбираем только те строки данных, которые содержат US или CN, используя оператор or. Обратите внимание на приоритет выполнения операций. У or он ниже. 

Если мы не объединим условие country = 'US' or country = 'CN' в скобки, то в результате запроса мы получим строки, удовлетворяющие всем вышеперечисленным условиям – не тестеры, не читеры, совершившие покупку offer в течение последнего месяца и из страны US. Либо покупки, совершенные из CN – без фильтрации читеров, тестеров, дат покупок и наименования продукта.

Теперь давайте соберём все фильтры в один запрос и дадим внятные названиям нашим офферам, используя конструкцию case.

select case when product 'offer1' then 'Bombs pack offer'
when product 'offer2' then 'Gloves and shovel offer'
when product 'offer3' then 'Additional steps offer'
when product 'offer4' then 'Additional lives offer'
else 'Other offers'
,end as "Offer name"
,count() as "Transactions", sum(priceusd) as "Gross"
from p102968.payments
where eventtime>=current_date - interval '1 month'
and eventtime < current_date 
and tester is false
and cheater is false

and paymentcount = 1
and eventtime - created <=interval '1 day'
and product like '%offer%'
and (country 'US' or country 'CN')
group by 1
order by 2 desc
Результат запроса SQL
Результат запроса. Скриншот из демо devtodev

Конструкция case позволяет задать определенное значение в столбце при выполнении заданного условия. Если значение в столбце product ='offer1', то тогда вместо него запишется значение 'Bombs pack offer'. Также и с остальными – если значение не подошло ни под одно из условий, то запишется значение, указанное в else

При этом условие для проверки внутри конструкции case может быть сколько угодно сложным, как и в where.

Структура первых покупок в динамике

Для того чтобы посчитать, сколько покупок совершалось в каждый день, добавим eventtime::date в select запроса и сгруппируем по этому полю (group by 1). Чтобы для каждого дня получить список купленных товаров, в select и group by добавляем поле product. В итоге для каждого дня и каждого товара считаем количество транзакций. Первые покупки фильтруются условием paymentcount = 1.

select eventtime::date
, product
count() as "Transactions"
from p102968.payments
where eventtime>=current_date - interval '1 month'and eventtime < current_date 
and tester is false

and cheater is false
and paymentcount = 1
group by 12
order by 13 desc

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

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

select eventtime::date
count() filter (where product like 'starterpack') as "Starterpack"
count() filter (where product like 'special offer') as "Special offer"
count() filter (where product like 'offer%') as "Other offers"
from p102968.payments
where eventtime>=current_date - interval '1 month'and eventtime < current_date
and tester is false
and 
cheater is false
and paymentcount = 1
group by 1
order by 1

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

И это можно сделать с помощью конструкции filter. В этом запросе осталась только одна группировка – группировка по дням. Для каждого дня в select считается количество строк count(), которые соответствуют условию фильтра (where …). Вместо count() можно использовать любую другую агрегирующую функцию и любые логические операцие для условия внутри фильтра where.

Таким образом, каждому дню соответствует только одна строка и несколько столбцов с фактами (метриками). Такой результат гораздо проще визуализировать с помощью диаграмм.

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

Read more