SQL for Beginners: In-App Purchase Structure

EN
Learn how to use SQL to determine the structure of users purchases and what offers encourage them to pay
SQL for Beginners: In-App Purchase Structure
Published
08.10.2021
|
devtodev

In the last article, we calculated the main monetization metrics of our demo project using SQL tools. Now let's figure out what they are formed from. First of all, we’ll find out what in-game purchases users make for real money and how our income is generated.

In this query, we’ll calculate the number of purchases for each offer per month count() and the amount of income received from the sale of each offer 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

Query result on the chart
Query result on the chart. Screenshot from devtodev DEMO

We found that offer3 and offer4 are the least bought, but these two products generate the most app revenue. At the same time, our starterpack is the most purchased, but it generates only a small part of the income. Cheap offers are also necessary. They allow you to convert users into paying ones because after the first payment it is much easier to make the second one.

What offer encourages users to pay?

We can also use SQL to find out which offer becomes the first in-app purchase. Let’s use the standard paymentcount event parameter by adding it to the where clause of the previous request.

and paymentcount = 1

This way we will get only the very first of the user's payments.

Query result on the chart
Query result on the chart. Screenshot from devtodev DEMO

The paymentscount parameter is automatically added to every event sent to devtodev. You can quickly filter out the actions of non-paying (paymentscount = 0) and paying users (paymentscount> 0), or those who made a certain number of payments using it. 

And if you only need to see payments made on the first day of life in the app, add this to the where:

and eventtime - created <=interval '1 day'

You can add the following condition to the where to find out how users from the USA and Canada purchase offers:

and product like '%offer%'
and (country 'US' or country 'CN')

The like operator compares the value of the product parameter with the template, which is given in the text part of ‘%offer%’. The % signs allow us to choose names that contain the ‘offer’  text inside them. You can add anything you want either before it (special offer) or after it (offer1). Countries are written as a two-letter code. Select only those data lines that contain US or CN using the or operator.

If we do not combine the condition country = 'US' or country = 'CN' in brackets, then as a result of the request we will receive lines that satisfy all of the above conditions – not testers, not cheaters, not those who made an offer purchase within the last month and from US. Or purchases made from CN – without filtering cheaters, testers, purchase dates and offer names.

Now let's collect all the filters into one request and give clear names to our offers using the case construction.

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

Query result
Query result. Screenshot from devtodev DEMO

The case construct allows us to set a specific value in a column when a given condition is met. If product = 'offer1', then the value 'Bombs pack offer' will be written instead. The same applies to the rest – if the value does not match any of the conditions, then the value specified in else will be written.

In this case, the condition for checking inside the case structure can be as complex as we want, as in the where.

The structure of the first purchases in dynamics

In order to calculate how many purchases were made on each day, add eventtime::date to the select and group by this field (group by 1). To get a list of purchased items for each day, add the product field to select and group by. As a result, we count the number of transactions for each day and each item. The first purchases are filtered by the condition 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 1, 2
order by 1, 3 desc

Query result
Query result. Screenshot from devtodev DEMO

Such a query produces the required result, but it's so inconvenient to work with – several offers with the number of purchases correspond to each day. We need to make sure that only one line corresponds to each day, with the number of each purchased item in separate columns.

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

Query result
Query result on the chart. Screenshot from devtodev DEMO

This can be done using the filter construct. There is only one grouping left in this query – grouping by day. For each day, select counts the number of rows count() that match the filter condition (where...). Instead of count(), you can use any other aggregate function and any boolean operator for the condition inside the where filter.

Thus, each day corresponds to only one row and several columns with facts (metrics). This result is much easier to visualize with charts.

In this article, we explored the SQL commands required to define the structure of user purchases and learned how to use them correctly. Next time we'll talk about table joins and funnels.

Read more