Please note that only a beta version of SQL is available at the moment. Feel free to share your feedback and comments with our managers at info@devtodev.com.
This tool allows you to get access to any data stored in devtodev (basic and custom events) via SQL-based queries. It is available for the whole Space, which means that you can build a query for sever
Let’s look at how it works. First, explore the interface. There is a list of the projects in Space represented by the tables on the left side of the screen. Each product in our database has its own unique identifier which looks like pXXXXX. The name of the product is mentioned in brackets.
You can expand this project to see its table and column structure. On the right side of the screen, there is a field where you can write a query (Query Editor tab).
To do it faster you can use the following methods:
One click on the scheme, table, or column in the list will put their names to the text of the query
A combination of ‘Ctrl + Enter’ will run the query.
You can also change the time zone for the queries by choosing the right one in the top right corner. If there is a mistake in the query, you will see a notification. If not this query will be stored in History which will be available in the next tab. After you run the correct query, you will see the results at the bottom of the screen, where you can export them as a CSV file (up to 10M rows) or create a chart.
Columns in the result of the query will be used for making a chart. First of all, you need to select a type of chart. 7 options are available: line chart, area chart, column and pie charts, area and column stacked charts, and combo chart. Then you can select a metric from the columns which were received in the query results which will be used as X-axis. It could be only 1 metric.
Then you can select one or several сolumns which will be displayed on the Y-axis. The number of these metrics depends on the selected chart type. For the pie chart, you can choose only 1 metric, for the rest of the chart types you can select up to 10 metrics.
It is enough to create a chart but you can also customize it if you tap “View advanced options”. This menu allows you to:
add axis name
add up to 4 Y-axises which will be useful when you create a chart for the metrics with completely different units or values (e.g. ARPU, Gross, Paying share, Number of purchases)
change the axis type to linear, logarithmic or categorical
distribute metrics by different Y-axises
set a metric type when you use the combo chart
set a specific rounding of values
select a color for the chart
write down the units that will be used to label the values on the chart and will be displayed in hints (%, pcs. $, etc.)
After you get the results of the query you can save the report by clicking on the Settings button in the top right corner. The chat settings will be saved as well.
This saved report will be available in the table in the SQL section of the top menu.
You can also download the results of the query to a CSV file by clicking on buttons above the table with the results or use the Settings button on the top right corner.
Now let’s explore the details of writing SQL-queries.
The major PostgreSQL functions are available in this tool for writing a query such as:
All types of Joins and Union
Subqueries and aliases
Operators for comparison values (!=, <, >, between …)
Logic operators (and, not, or)
Mathematical operators (+, -, abs, log, …)
Working with strings (substring, concat, length, ...)
Conditions (case, coalesce, least, …)
Grouping, ordering, limit, having
etc.
To learn more about SQL queries functions visit this page
Functions that are not available currently:
temporary table creation
tables creation and adding data to tables
creation of indexes to the table
stored procedures
First of all, you can easily select the time zone for making queries by choosing the right one in the dropdown menu of the top right corner of the query field.
After doing this, the time zone will be automatically applied to all dates used in the query. For working with dates and time ISO 8601 format is used. To get the date you can apply the following functions:
Function | Description |
CURRENT_DATE | Current date (YYYY-MM-DD) in selected time zone |
CURRENT_TIME | Current time (HH-MM-SS) in UTC |
CURRENT_TIMESTAMP | Current date and time in selected time zone |
LOCALTIME | Current time (HH-MM-SS) in selected time zone |
LOCALTIMESTAMP | Current date and time in selected time zone |
To modify date and time, use the following functions:
Function | Input | Result |
::<type_name> | '2020-12-14 09:46:47.744594'::date '2020-11-01 18:00+3'::timestamptz | 2020-12-14 2020-11-01 15:00:00.0 |
to_date(<text>, <mask>) | to_date('2020-11-01 18:00', 'yyyy-mm-dd') | 2020-11-01 |
to_timestamp(<text>, <mask>) | to_timestamp('2020-12-01 9:30:20', 'YYYY-MM-DD HH:MI:SS') | 2020-12-01 09:30:20.0 |
date | date '2020-11-01 18:00' | 2020-11-01 |
Use intervals to move dates
Function | Input | Result |
interval | date('2020-11-01 18:00+3') + interval '3 week 2 day 6 hour' current_date - interval '1 month' | 2020-11-24 06:00:00.0 |
Here are several examples of how you can set the intervals:
‘1-2’ means 1 year and 2 months
‘0-1 2’ means 1 month and 2 days
‘1 02:03’ means 1 day 2 hours and 3 minutes
1 year 2 month 3 week 4 day 17 min
1 week 02:03:04 means 1 week 2 hours 3 minutes 4 seconds
For rounding dates use this function:
Function | Input | Result |
date_trunc(‘field’, ‘value’) | date_trunc('hour', timestamp '2020-12-01 19:05:45') | 2020-12-01 19:00:00.0 |
The following values can be used as the ‘field’:
Microsecond
Millisecond
Second
Minute
Hour
Day
Week
Month
Quarter
Year
Decade
Century
Millennium
For formatting dates use this function:
Function | Input | Result |
to_char(<value>, <mask>) | to_char(eventtime, 'yyyy-mm') | |
Here are several mask examples:
HH12 - hour of day (01-12)
HH24 - hour of day (00-23)
MI - minute (00-59)
MS - millisecond (000-999)
AM, am, PM or pm - meridiem indicator (without periods)
YYYY - year (4 or more digits)
Y - last digit of year
IYYY - week-numbering year (4 or more digits)
MONTH - full upper case month name (blank-padded to 9 chars)
MM - month number (01-12)
DAY - full upper case day name (blank-padded to 9 chars)
DD - day of month (01-31)
WW - week number of year (1-53) (the first week starts on the first day of the year)
TZ - upper case time-zone name
etc.
Let's take a look at the database structure. The highest level in the tree contains all projects of the space. Each project contains a list of tables. Some of them are related to basic events, others - to сustom events (their names start with the underscore sign). Most of the tables include the following fields:
devtodevid - unique identifier of the user which is used in devtodev and assigned to the user when he launches the app for the first time. It could be different for one user in different apps.
firstpaymentdate - unix timestamp of the first user payment
lastpaymentdate - unix timestamp of the last user payment
created - when the user opened the app for the first time
paymentcount - number of payments that the user have made in the app
paymentsum - total sum of payments in USD
level - current user’s level which couldn’t be decreased
deviceid - identifier of the device
country - user’s country
locale - user’s language
device - name of the device model (IPHONE 6S PLUS, GALAXY NOTE 10+, etc.)
eventtime - time in unix timestamp format when the specific event was performed
eventlevel - on which level user performed the specific event
appversion - current version of the app for specific user
cheater - whether the user has cheated or not (true/false)
tester - whether the user is a tester or not (true/false)
Fields in levelups table:
leveluprecordid - identifier which can be used for connection with Levelup_currencies table
Fields in levelup_currencies table:
type - how the resource was changed. It could be: 'BALANCE', 'SPENT', 'EARNED', 'BOUGHT'
leveluprecordid - identifier which can be used for connection with Levelup_currencies table
_{currency_name} - such a column will be created for each currency of the project. For example, "_Coins". The name of the column will be the same as it was received from the app.
Fields in the tutorial table:
step - if the value is more than 0, then it is the number of the tutorial step, step = 0 means that tutorial was skipped, step = -1 means that tutorial was started, step = -2 means that tutorial was finished.
Fields in payments table (this table contains information about real payments):
currency - shows the currency of the payment
price - price of the product in the user's currency
priceusd - price of the product in USD
product - name of the purchased product
paymentid - identifier of the payment
Fields in purchases table (this table contains information about in-game payments):
amount - amount of the purchased product
price - price of the purchased product
currency - currency of the purchased product
itemtype - group of the purchased product
item - name of the purchased product
_{currency_name} - such a column will be created for each currency of the project. For example, "_Coins". The name of the column will be the same as it was received from the app.
Fields in sessions table:
activityduration - duration of the session in seconds
sessionstarts - number of sessions
eventtype - type of the event: "gs" - contains duration and amount, "ss" - contains only amount, "ue" - contains only duration
Fields in progressions table (this table contains information about changes of locations):
duration - duration of completing the location in seconds
progressionrecordid - identifier of the event. Can be used for connection with the progression_currencies table
locationsourceid - identifier of the location
difficulty - difficulty of the location
success - whether the location was passed successfully or not
locationsource - name of the location
Fields in progression_currencies table (this table contains information about resources which was sent with the progression events):
type - how the resource was changed. It could be: 'SPENT', 'EARNED'
_{currency_name} - such s column will be created for each currency of the project. For example, "_Coins". The name of the column will be the same as it was received from the app.
Fields in push_clicked, push-sent tables:
pushcampaignid - identifier of the push campaign
name - campaign name
success - whether it was sent successfully or not
Tables with custom events have the same fields as tables with basic events. If there are any parameters in these tables, their names also start with the underscore sign.
Other fields that custom event tables contain:
customeventid - identifier of the custom event
name - custom event name
_{custom_param_name} - each event parameter has a column with its name which contains the parameter value (for example, "_bonus").
If you want to combine users from different projects then use the following user identifiers:
If these apps are on different platforms (for example, iOS and Android), you can merge them only if your project uses a custom identifier that is sent to devtodev (https://docs.devtodev.com/faq/identification#identification-by-user-id-set-by-developer ). Then you can use ‘customuid’ field.
If the apps are on the same platform, then you can use ‘idfa’ field for iOS apps or ‘advertisingid’ for Android apps.
Information about Users and Payments is available for the whole time, the rest of the data is available for the last 90 days.
Let’s explore several SQL query examples. Number of purchases of different product grouped by months:
select to_char(eventtime, 'yyyy-mm') as month, count(eventtime) filter (where product = 'offer4') as offer4, count(eventtime) filter (where product = 'starterpack') as starterpack, count(eventtime) filter (where product = 'offer1') as offer1, count(eventtime) filter (where product = 'special offer') as special_offer, count(eventtime) filter (where product = 'offer2') as offer2, count(eventtime) filter (where product = 'offer3') as offer3from p102968.payments pwhere eventtime >=current_date - interval '6 month' and eventtime < current_dateand tester is falsegroup by 1order by 1 asc
On the next tab, we can build a chart using data from the results of the query:
The number of users who installed the app 1 month ago, passed at least 3 levels, and haven't added a friend:
select count(distinct l.devtodevid) filter (where level >=3)from p102968.levelups lleft join p102968."_add friend" fon l.devtodevid=f.devtodevid and f.eventtime >= date_trunc('month', current_date) - interval '1 month'where l.created >=current_date - interval '1 month' and l.created < current_dateand l.eventtime >=current_date - interval '1 month' and l.eventtime < current_dateand f.devtodevid is null