SQL

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 several projects simultaneously.

Query creation

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 app in our database has its own unique identifier which looks like pXXXXX. The name of the app is mentioned in the 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 there are no mistakes, the query will be stored in History which will be available on 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.

Making a chart

Columns in the result of the query will be used for making a chart.

1. You need to select a type of chart. There are eight options are available: line chart, area chart, column and pie charts, area and column stacked charts, combo chart and number.

2. Then you can select a metric from the query result columns which will be used as the X-axis. You can select only one metric.

1. 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 one 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 open the View advanced options section. This menu allows you to:

• add axis name;

• add up to four Y-axes 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-axes;

• 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.).

Axis scale settings

By default the values on the chart are scaled automatically so that the graph is centered.

You can change the axis boundaries by clicking on Axis scale button. Axis scale settings are available for Chart, Area chart, Stacked area chart, Bar chart and Stacked bar chart types.

Set the minimum or maximum value for the axis and click Save to apply the settings. To turn on automatic scaling again, remove the value and save the settings.

When you save a widget to the dashboard or copy report URL, the settings for axes are saved as well.

Managing SQL query variables

Filters are often used in SQL queries, e.g. filters by country, registration date, app version, user ID, payment status, etc. Occasionally, you need to change parameters of these filters to run a query on another user category. Even more, sometimes you need to change them not in the query but directly in the dashboard.

The feature described below allows you to use the interface to change values of query variables without altering the text of the query.

How to create a variable

You can specify variables in the SQL query text, save it as a widget (with defined variables) on a dashboard and later change the variable values if you need.

1. To create a variable, open a menu by clicking the @ icon in the top right corner. Click + Add variable.

2. In the Variable name block, specify the variable name that will be used in the text of the query. This name will also be used when merging variables in a single dashboard.

3. In the Control title block, specify the name of control that will be used in the interface.

4. After that, select the variable type (read more about types below). There are the following types:

• Date

• Timestamp

• Period Interval

• Text

• Number

• List of reference values

• List of numbers

• List of strings

1. Set a default value of each variable. You can change this value later.

1. After adjusting all the settings, click Save in the column with variable names on the left.

Actions available for variables:

• Edit variable settings. Click the @ button (the one you used for variable creation) and select the desired variable from the list on the left. After that, open the list of settings that you can change any time.

• Edit variable values. Open a list of variables in the Defined variables block and click on the variable. In the window that opens you can set or change the value of the variable. You can also do this by clicking the Edit button in the block of variables.

• Set default values of variables by clicking the Reset to default button. This will reset the values of variables to their default values specified at the time those variables were created.

• Delete a variable. Click the @ button and then click the cross next to the variable to delete it.

By clicking the @ button, you can not only create a new variable (Create new variable option), but also select a variable out of the variables previously created in the current project by a single user. Click Select from recently created and get a list of the last 10 variables.

Variable types

Date

This type of variable allows you to set a date in the ISO 8601 format (YYYY-MM-DD) to use it in the query, e.g. 2022-03-15 date specifies that you will factor in all values received from 2022-03-15 00:00:00 to 2022-03-15 23:59:59.

There is a Rolling time frame option available for this type of variable. If you select it, then when opening the report, you will get the defined date with an offset for the number of days passed as the value of the variable. For example, you set 2022-03-15 in the Select default value field, then after five days you run the SQL query and the system will use another date – 2022-03-20.

Timestamp

This type of variable allows you to set a date and time in ISO 8601 format (YYYY-MM-DD hh:mm:ss), e.g. 2022-03-15 14:35:00.

Period

This type of variable allows you to set a date range, e.g. 07.Mar.2022 – 13.Mar.2022 means that you are going to include the entire time period from 07.Mar.2022 00:00:00 to 12.Mar.2022 23:59:59.

To use the Period type variable in a query, write the following:

created >= @variable.from and created < @variable.to

Interval

This type of variable allows you to work with relative time intervals, e.g. 30 days, 4 weeks, 2 months.

You can use the intervals to limit action time or registration dates. Intervals are convenient to work with because they are not fixed but are always changing. For example, a payment made within the past 30 days:

Text

This type of variable allows you to use text values, e.g. names of game characters or items:

Number

The Number type of variable allows you to input a number as a variable value, e.g. 2 or 2.6.

Use it in a query to filter values of custom event parameters or number of payments:

List of reference values

Here you can select available values from the lists, e.g. certain countries, campaigns, item names, etc. It works similar to filters in the interface of our reports:

To use the variable in a query, write the following:

country = any(@variable)

List of numbers

This type of variable allows you to specify a list of numbers that can be further filtered using a query, e.g. values of a custom event parameter or tutorial step numbers:

To use the list, write:

step = any(@variable) 

Use commas to separate the numbers in the list.

List of strings

You can use several string values in this variable, e.g. version numbers, list of items or countries, string parameters values.

To use the list, write:

appversion = any(@variable)

Use commas to separate the values in the list.

How to use variables in queries

After you have created the variables, they will show up in the Defined variables block above the query text.

This block works similar to creating variables using the DECLARE function. In this case, however, you don't need to duplicate them in the text of the query. In the query, you can use the variables described in the Defined variables list.

To add a variable to a query, click on its name in the Defined variables block and it will appear where the cursor is positioned. Or you can type in the name of the variable after @ in the text of the query.

select country, sum(priceusd) as gross 
from p102968.payments p
where eventtime>=current_date - @rel_dates      -- Data type: Interval
and paymentcount = @paym_cnt                    -- Data type: Number
and p.product like @item_name                   -- Data type: Text
and appversion = any(@ver)                      -- Data type: List of strings
and country = any(@countries)                   -- Data type: List of reference values
and created::date = @inst_date                  -- Data type: Date
and lastpaymentdate::date >= @last_paym.from and lastpaymentdate::date < @last_paym.to  -- Data type: Period
group by 1

Change widget’s variable values on a dashboard

After you’ve added a query with created variables to a dashboard as a widget, you can change the values of the variables by using the filter icon in the top right corner of the widget.

If the variable value in the filter is different from a default value, then the filter icon turns darker.

Create common dashboard controls

If a dashboard contains several widgets with similar variables, e.g. country or creation date, then you can choose to edit the values of the variables simultaneously in all the widgets that contain them.

To create a dashboard control that will be common for all the widgets, click Manage controls in the dashboard menu.

In this section you can create a control (e.g. country) and assign it to respective variables from the dashboard widgets. Also, in this section you can view, edit and delete created dashboard controls.

To create a control:

1. Enter the name of the control.

2. Select the variable type (date, text, list of numbers etc.).

3. In the drop-down menu, select the widgets (out of those containing the variable of the selected type) to which the common filter will be applied.

4. In the next drop-down menu, select a specific variable of the selected type in the selected widget. It may be that there are two variables of the same type in the widget (e.g. installation date – created and date of event – eventtime) then when creating a general control you need to select the one that you need.

Let’s look at an example. A dashboard has several SQL widgets that contain variables of the List of reference – Country type. You want the values of these variables to change simultaneously in all dashboard widgets that contain it. Create a common dashboard control for the three widgets. Default values are pulled up from the first variable in the list and become default for the entire control. It means that all three widgets will be built for the same countries.

Another example. You want all dashboard widgets to be built over the same period. In this case we need to create a Period type of variable in each SQL widget that will set the date range for the report (the period will be pulled up automatically from widgets created in other reports). After that, you need to create a common dashboard control and unite the Period variables from all the widgets.

Change the values of widget variables

After you create a common dashboard control in the Manage controls section, it will appear as a block in the upper part of the dashboard.

To change control values, click these values and then click Edit in the top right corner of the Variables block. After that, the variable values will be updated in all the widgets that contain the variables.

If a variable of a widget is taken out of control on the dashboard level, then changing its values in the widget filter is impossible.

Saving and downloading the data

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 chart settings will be saved as well.

This saved report will be available in the Reports -> Saved reports section.

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.

SQL functions description

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 currently not available:

• temporary table creation;

• tables creation and adding data to tables;

• creation of indexes to the table;

• stored procedures;

Working with the date and time

You can easily select the time zone for queries using the dropdown menu in the top right corner of the query editor.

After selecting the time zone, it will be applied automatically to all dates used in the query. The editor uses the ISO 8601 format for dates and time.

Getting the date

To get the date you can apply the following functions:

Modifying date and time

To modify date and time, use the following functions:

Time intervals

Use intervals to move dates:

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 week 02:03:04 (means 1 week 2 hours 3 minutes 4 seconds);

• 1 year 2 month 3 week 4 day 17 min

Round dates

For rounding dates use this function:

The following values can be used as the ‘field’:

• Microsecond

• Millisecond

• Second

• Minute

• Hour

• Day

• Week

• Month

• Quarter

• Year

• Decade

• Century

• Millennium

Date formatting

For formatting dates use this function:

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.

Data structure

Let's take a look at the database structure.

The root folder of the tree contains all projects in the space. Each project contains a list of tables. Some of them are related to basic events, others – to custom events (their names start with the underscore sign _).

Common table fields

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.

• eventtime – time in unix timestamp format when the specific event was performed.

• abtestvalues – collection of user's groups in the A/B test.

• appversion – current version of the app for specific user.

• appversionid – ID of the current app version.

• cheater – whether the user has cheated or not (true/false).

• country – user’s country.

• created – when the user opened the app for the first time.

• device – name of the device model (IPHONE 6S PLUS, GALAXY NOTE 10+, etc.).

• deviceid – identifier of the device.

• eventlevel – on which level user performed the specific event.

• firstappversion – the first version installed by the user.

• firstappversionid – ID of the first installation version.

• firstpaymentdate – unix timestamp of the first user payment.

• lastpaymentdate – unix timestamp of the last user payment.

• level – current user’s level which couldn’t be decreased.

• locale – user’s language.

• location – current location of the user. Also the location where the event was made.

• locationid – ID of the location.

• osversion – user device's operating system version.

• osversionid – ID of the device OS.

• payingstatus – shows if the user is Paying or Non-Paying.

• paymentcount – number of payments that the user have made in the app.

• paymentsum – total sum of payments in USD.

• sbsfirstpaymentdate – unix timestamp of the first subscription payment date.

• sbspaymentcount – the number of subscription payments made by the user in the app.

• sbspaymentsum – total amount of subscription payments in USD.

• sdkversion – version of the devtodev SDK on the device.

• sdkversionid – ID of the SDK version.

• segmentvalues – whether or not the user belongs to a segment. For more information, see the User segments section below.

• sessionid – ID of the session when the event was made.

• tester – whether the user is a tester or not (true/false).

Levelups table specific fields

Fields in levelups table:

• leveluprecordid – identifier which can be used for connection with levelup_currencies table.

Levelup_currencies table specific fields

Fields in levelup_currencies table:

• leveluprecordid – identifier which can be used for connection with levelups table.

• type – how the resource was changed. It could be: 'BALANCE', 'SPENT', 'EARNED', 'BOUGHT'.

• _{currency_name} – such 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.

Tutorials table specific fields

Fields in tutorials 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.

Payments table specific fields

Fields in payments table (this table contains information about real payments):

• currency – shows the currency of the payment.

• paymentid – identifier of the payment.

• price – price of the product in the user's currency.

• priceusd – price of the product in USD.

• revenueusd – price of the product in USD after the store comission rate. The revenue rate is set up in Settings -> SDK -> Payments.

• product – name of the purchased product.

• valid – shows if the payment is valid or not (True/False).

Subscriptions table specific fields

Fields in subscriptions table:

• action – what happened to the subscription. Example values: purchased, expired, revoked, canceled, renewed.

• bundle – name of the bundle.

• period – subscription period. Example values: daily, weekly, monthly, yearly.

• state – subscription state. Example values: Upgraded, Downgraded, None, Trial, Expired, Renewed, New, Refunded, Reactivated.

• sequencenumber – number of times the user renewed the subscription.

• currency – shows the currency of the payment.

• paymentid – identifier of the payment.

• price – price of the product in the user's currency.

• priceusd – price of the product in USD.

• valid – shows if the payment is valid or not (True/False).

Virtual payments table specific fields

Fields in virtual_payments table (this table contains information about in-game payments):

• amount – amount of the purchased product.

• currency – currency of the purchased product.

• item – name of the purchased product.

• itemtype – group of the purchased product.

• price – price of the purchased product.

• _{currency_name} – such 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.

Sessions table specific fields

Fields in sessions table:

• activityduration – duration of the session in seconds.

• sessionstarts – indicates a start of a session.

• eventtype – type of the event: ss – indicates a start of a session, ue – contains activity duration.

Progression table specific fields

Fields in progression table (this table contains information about changes of locations):

• difficulty – difficulty of the location.

• duration – duration of completing the location in seconds.

• locationsource – name of the location.

• locationsourceid – identifier of the location.

• progressionrecordid – identifier of the event. Can be used for connection with the progression_currencies table.

• success – whether the location was passed successfully or not.

Progression currencies specific table

Fields in progression_currencies table (this table contains information about resources which was sent with progression events):

• difficulty – difficulty of the location.

• duration – duration of completing the location in seconds.

• locationsource – name of the location.

• locationsourceid – identifier of the location.

• progressionrecordid – identifier of the event. Can be used for connection with the progression table.

• success – whether the location was passed successfully or not.

• type – how the resource was changed. It could be: 'SPENT', 'EARNED'.

• _{currency_name} – such 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.

Push clicked table specific fields

Fields in push_clicked table:

• pushcampaign – campaign name.

• pushcampaigntag – identifier of the campaign sent via Push API.

• pushcampaignid – push campaign identifier.

Push sent table specific fields

Fields in push_sent table:

• pushcampaign – campaign name.

• pushcampaigntag – identifier of the campaign sent via Push API.

• status – delivery status. Example values: Control group, Token rejected, Sent.

• pushcampaignid – push campaign identifier.

Custom events table specific fields

Fields in custom_events table:

• customeventid - identifier of the custom event.

• name – custom event name.

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.

• _{custom_param_name} – each event parameter has a column with its name which contains the parameter value (for example, "_bonus").

Ad impressions table specific fields

Fields in ad_impressions table:

• ad_network – name of the ad network responsible for the impression.

• ad_unit – banner name.

• ad_placement – banner placement.

• ad_source – ad impression data provider.

• revenue – reward for banner display in USD.

Ad revenue aggregates table specific fields

Fields in ad_revenue_aggregates table:

• ad_source – ad revenue data provider.

• revenue – reward for banner display in USD.

• clicks – number of clicks.

• impressions – number of impressions.

External acquisition cost table specific fields

Fields in external_acquisition_cost table:

• publisher – name of the publisher.

• campaign – ad campaign name.

• adgroup – name of the ad group.

• cost – ad cost in USD.

• clicks – number of clicks.

• impressions – number of impressions.

Retention tables specific fields

Fields in returns_by_24h and returns_by_calendar tables:

• retentionday – day on which the user returned to the app.

User property updates table specific fields

Fields in user_property_updates table:

• data – JSON that contains all of the custom user properties.

• _{user_property_name} – each custom user property has a column with its name which contains the property value (for example, "_Difficulty").

Users table specific fields

Fields in users table:

• lasttime – unix timestamp of the last time the user was active.

• pushavailable – shows whether devtodev received the user's push-token and the user can receive notifications (true/false).

• publisherid – ID of the publisher

• publisher – name of the publisher.

• campaign – name of the campaign to which the user is attributed.

• placement – ad banner placement.

• ad – name of the ad.

• mainid – users's main identifier. By default the same as devices' advertising id.

• customuid – optional custom user identifier.

• idfa – Identifier for Advertisers (Apple).

• idfv – Identifier for Vendors (Apple).

• advertisingid – advertising identifier of the device.

• timezoneoffset – user's offset from the space timezone.

• inactivitydays – number of days the user is inactive.

• adrevenue – advertisement revenue from the user.

• adimpressions – number of ad impressions.

• mmpid – user identifier for MMP matching. Sent manually as ad_tracker_id property.

• predicted_payingstatus – devtodev's prediction if the user will become a paying one.

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. 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.

User segments

segmentvalues field in the user card (the Users table) contains information about the current user segment with a true/false value. In event tables there is also a segmentvalues field which contains segment values at the time of the user performing it.

A special data type has been created for segments that is converted to an array of segment names while being displayed.

Usage example:

SELECT  devtodevid, segmentvalues, segmentvalues->'example'
FROM p102968.users
WHERE segmentvalues is not null

Views in SQL

In devtodev analytics, you can create materialized, automatically updated as well as normal (with no physical materialization) query Views.

A View is a virtual table with a content determined by a query. Like a table, a View consists of a set of named columns and rows of data. After you create a View, it appears in the list of tables in the views directory on the left and can be used for writing SQL queries.

The use of materialized Views can drastically speed up dashboard load time. It allows you to execute a query against a data sample prepared in the View instead of running it against a larger dataset.

In addition, you can collect the necessary data from several tables in a single View and thus speed up query execution because it allows you to avoid joining entire tables.

How to create a View

CREATE [ OR REPLACE ] [MATERIALIZED [DAILY|WEEKLY|NEVER]] VIEW 
name [ ( column_name [, ...] ) ] AS query

OR REPLACE – if a View with a specified name already exists, it gets replaced with a new one. This works only with the Views that are visible during query execution. If you use an SQL project to create the View and it is conflicting with a View from another project, then OR REPLACE won’t function.

MATERIALIZED – the View result is saved as a table.

DAILY|WEEKLY|NEVER – the View is automatically updated with the specified frequency (calendar day or week). If not specified, NEVER is used – with no auto update.

name – View name. When creating the View, it is prohibited to specify a schema. All Views are stored in a dedicated views schema.

( column_name [, ...] ) – a list of fields. If specified, columns are named this way. The number of columns must be equal to the number of columns in the data sample. They are matched with columns in the data sample by index.

query – content of the View. SELECT or VALUES are available.

Types of Views

You can set up both materialized and normal Views.

Normal Views are basically named queries. They can be used to avoid repeat statements in the queries.

Materialized Views are tables that are created based on a specified query and get updated on a schedule (DAILY, WEEKLY). If NEVER is used, or the update rate is not specified, the View "freezes" the data sample.

Materialized Views can drastically speed up dashboard loading. Instead of running a query against a large data sample, you “freeze” the required part of it for a certain period of time (most often one calendar day) as a materialized View and then your dashboards get built much quicker. When using materialized Views, it’s a good idea to create intermediate data sets that can be used in a large number of dashboards, rather than setting up a separate View for each graph.

Some specific features of working with Views

1. View dependencies are supported – that is, you can create a View that uses other Views.

2. Important! Views can NOT contain more than 10 mln rows!

3. Important! When using a materialized View, it’s very important to use the same time zone that was set when the View was created.

4. If you want to revise or modify the View, open the list of all views (VIEWS) and change the code that was used to create it.

5. To delete views, use the DROP VIEW command.

DROP VIEW name

Usage examples

• Create a projection (DAU, in this case):

CREATE OR REPLACE MATERIALIZED DAILY VIEW p2_dau(dau, eventtime, level, country) AS 
SELECT 
  hyperloglog_accum(devtodevid), 
  eventtime::date, 
  eventlevel,
  country
FROM p2.sessions s
WHERE 
	eventtime >=  current_date - INTERVAL '30 days'
	and eventtime < current_date  
GROUP BY 2,3,4;

• Use view in a query:

select round(hyperloglog_get_estimate(hyperloglog_merge(dau))) dau, eventtime
from p2_dau
group by 2
order by 2

• Create dictionaries:

create view custom_dict (id, name) as values(1, 'name_1'), (2, 'name_2')

SQL query examples

Let’s explore several SQL query examples.

1. 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 offer3
from p102968.payments p
where eventtime >=current_date - interval '6 month' and eventtime < current_date
and tester is false
group by 1
order by 1 asc

On the next tab, we can build a chart using data from the results of the query:

1. 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 l
left join p102968."_add friend" f
on 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_date
and l.eventtime >=current_date - interval '1 month' and l.eventtime < current_date
and f.devtodevid is null

You can find more examples in the next article: