Conditions with dates

1. To set the difference between two dates, it is better to use the following expression: eventtime - created <= interval ‘10 min’ (instead of eventtime - created <= 600000)

2. To set a relative date, instead of created <= to_timestamp('2020-11-01 12:59:59', 'YYYY-MM-DD HH24:MI:SS') it is better to use created < date ‘2020-11-02’ or created <= timestamp ‘2020-11-01 12:59:59’ if you need to add the exact time.

3. To include a specific date to the query result (for example 30th of November 2020) you can apply the following condition: created < date '2020-12-01' or created <= timestamp '2020-11-30 23:59:59' The first option is preferable.

Countries

The country names are stored in two-letter code form, such as ‘CN’, ‘FR’, ‘GB’, etc.Therefore, instead of the full country name (‘China’) you can use a code ('CN') or use the countries dictionary - and p.country <> ANY(select code from countries where name = 'China')

DAU (Daily active users)

Active user is a user who generates any event (basic, custom, Start session event) via SDK or API over a certain period of time, for example, a day for DAU. You can use a more convenient table with users’ activity to calculate DAU: users get added only once per day if they are active.

select eventdate, count(devtodevid)
from p103198.dau
where eventdate >= current_date - interval '1 month'
group by 1
order by 1 asc

It corresponds to the DAU metric in the Basic Metrics report. Users marked as cheater or tester are not included in this table.

User segments:

To see the segments the user belongs to:

SELECT  devtodevid, segmentvalues
FROM p102968.users
WHERE segmentvalues is not null 

To filter users and select those who belong to the 'test_segment' segment:

SELECT  devtodevid
FROM p102968.users
WHERE segmentvalues->'test_segment' is true 
or segmentvalues->'other_segment' is not true

To filter users and select those who belong to the specific A/B test group:

select devtodevid, abtestvalues
from p105424.users u
where u.lasttime >= current_date - interval '1 year' 
and u.abtestvalues->'test_name.A' is not null

Currency conversion

When we receive payment information, we convert it into United States Dollar (USD) using the latest exchange rate. Thus, devtodev stores information about payment amounts in USD.

However, if you want to calculate your financial metrics in Euro (EUR), Japanese Yen (JPY), Russian Ruble (RUB), or any other currency, you can use the currencies_historical table which contains exchange rates of multiple currencies to the U.S. dollar for each day.

select 
  p.date,
  p.usd,
  round(p.usd/eur.rate::numeric, 2) as eur
from (select  
        eventtime::date as date,
        sum(p.priceusd) as usd
      from p102968.payments p
      where p.eventtime >= current_date - interval '30 day' and p.eventtime < current_date
      group by 1) p
  left join public.currencies_historical eur on 
      eur.source = 'EUR' 
      and eur.update_date = p.date
order by p.date

Example 1

The result of this query is a list of user events for the last 2 days sorted by the amount of times the event was performed.

with last_ev as 
    (select distinct devtodevid, max(eventtime) as eventtime
    from p104704.custom_events 
    where created >= current_date - interval '2 days'
    group by 1)
select name, count(distinct devtodevid)
from 
  (select distinct q1.devtodevid, name
  from p104704.custom_events q1 
    join last_ev q2 using (devtodevid, eventtime)) q3
group by 1
order by 2 desc

Example 2

The result of this query is a list of last user events before they churn. The churn period is specified.

with last_ev as
    (select distinct devtodevid, max(eventtime) as eventtime
    from p104704.custom_events ev 
    join p104704.users us using(devtodevid)
    where us.lasttime <= current_date - interval '7 days'  -- churn period
    and ev.created >= current_date - interval '30 days'
    group by 1)
select name, count(distinct devtodevid)
from
  (select distinct q1.devtodevid, name
  from p104704.custom_events q1
    join last_ev q2 using (devtodevid, eventtime)) q3
group by 1
order by 2 desc

Example 3

The result of this query is a timestamp of the last event with numeric value.

select tm(eventtime) as "event timestamp", data
from p104704.user_property_updates 
where data::text SIMILAR TO '%customUserProperyName":\d%' 
order by eventtime desc 
limit 1

The result of this query is a timestamp of the last event with text (string) value.

select tm(eventtime) as "event timestamp", data
from p104704.user_property_updates
where data::text SIMILAR TO '%customUserProperyName":"%' 
order by eventtime desc 
limit 1

Example 4

Use this query to find the users with decreasing player level (events with decreasing player level alert). The result of this query is a list of users and their previous and current levels.

select devtodevid, prev_event_lvl, cur_event_lvl
from(
  select devtodevid
  , lag(eventlevel,1) over (partition by devtodevid order by eventtime) as prev_event_lvl
  , eventlevel as cur_event_lvl
  from p104704.levelups
  where created >= current_date - interval '7 day' and eventtime < current_date
) t
where prev_event_lvl > cur_event_lvl
order by devtodevid

Example 5

Use this query to calculate user retention. The result of this query is a table that contains the number of new users per day and the number of returning users on the 0th, 1st, 2nd, 3rd, 7th, and 14th day after installation.

with return as (
  select created::date as date_, 
    count(devtodevid) filter (where retentionday = 0) as R0,
    count(devtodevid) filter (where retentionday = 1) as R1,
    count(devtodevid) filter (where retentionday = 2) as R2,
    count(devtodevid) filter (where retentionday = 3) as R3,
    count(devtodevid) filter (where retentionday = 7) as R7,
    count(devtodevid) filter (where retentionday = 14) as R14
  from p102968.returns_by_24h 
  where created::date >= current_date - interval '1 month'
  and eventtime >= current_date - interval '1 month'
  group by 1
  )
, us as (
  select created::date as date_, count(1) AS regs
  from p102968.users
  where created::date >= current_date - interval '1 month'
  group by 1
  )
select us.date_, regs, r0, r1, r2, r3, r7, r14
from us 
  left join return r on us.date_=r.date_
order by 1

Here are the articles in this section:

​This dashboard contains the overview table of key metrics for all projects in the space: Gross, New users, Active users, ARPU, Paying share.

At the top of the page, there are several settings:

• Calendar. This feature allows you to adjust the reporting period. It offers a dropdown list with predefined time intervals for quick selection:

  • Today

  • Yesterday

  • Last 7 days

  • Last 30 days

  • Last 90 day

• Revenue sources. This selector impacts the calculation of monetization metrics by selecting the ones you need from the list: SUB, IA, AD.

• Search. This functionality allows filtering the displayed apps in the table and the charts. For example, you can filter the table to show all projects on the Android platform or projects with the same name:

The table provides key metrics for all projects in the Space organized by apps. To the right of each metric value, a percentage is displayed, comparing the current period with the previous period of equal length. If you want to analyze the data of one of the applications, you can click on its name in the table.

Below the table, you can view the metrics values on the chart: New users, Active users, Day 1 retention by calendar, Gross, ARPU, and Paying share. A checkbox in the table marks the projects whose metrics will be displayed on the widgets.

At the bottom of the page, you can find the most important devtodev news.

Reports

The Reports section allows you to build custom reports for several projects at the same time.

To build a report click the "Add new report" button.

You can choose a report template with set metrics or build a custom report:

While building a new report, you can define the necessary metrics. You can select up to 10 metrics for one report.

Then select the applications you want to have in the report. You can set an unlimited number of applications. But beware: sometimes the reports on too many applications are really hard to read and understand.

You can group metrics by different parameters:

• applications. The data will be grouped by application. If you need to compare the performance of different applications, use this grouping method. Recommendation: use basic metrics such as ARPU, retention, lifetime value, or total revenue;

• platforms. If you have the applications working for different platforms and you need to compare platform performance, use this grouping. Recommendation: use monetization metrics such as ARPU, ARPPU, and Gross;

• app versions;

• channels. The data is grouped by incoming traffic channels, so the report is useful for finding a favorable traffic structure. Recommendation: use traffic metrics such as ARPU, ARPPU, and paying conversion;

• countries. If you need to compare one country with another, use this option. Recommendation: usually the difference between countries can be found in monetization metrics such as Average Check, ARPU, and ARPPU;

• languages. If you need to compare either different markets or different localizations of your product, use this grouping method. Recommendation: use metrics such as ARPU and retention;

• devices, paying capacity, etc.

Finally, to set a filter on the application set click the "Filters" button. You can add one or more filters to the application set using for the report.

Applications can be filtered by install date, ad network, paying status, devices, etc.

In the report menu, you can choose one or some of the following actions:

• Reset settings - for fast reset of the selected filters, segments, apps, and other settings applied to the report.

• Save report - save the report and get fast access to all the saved reports. You can find the reports in the “Reports” list

• Share the report with teammates - you can also share the report with your colleagues.

• Share the report in a Slack channel

• Save to dashboard - save as a widget for a dashboard:

With Basic Metrics, you can check the metrics of all of your projects on one screen, see their individual and aggregated metrics, group metrics, and audiences by custom characteristics. Seeing the metrics of your apps in one place helps you quickly check the status of all your projects and make informed decisions later on. For example, with Basic Metrics you can compare the financial metrics of your projects to find which of the games brings more money to the studio and therefore invest more resources into its development. Also, if you compare projects’ behavior metrics and ARPPU of the paying users, you’ll be able to see which of the mechanics are more successful and use them when developing your other games. Using devtodev’s updated Basic Metrics, you can compare the revenue of your projects by country, and choose the best locations for each project. Knowing that, you can work on their positioning in their most profitable markets.

Filter your saved reports

You can filter out reports by type, e.g. display only saved Conversion funnels or reports built using SQL. To do that, select the desired type from the ‘Report type’ drop-down list:

Use filter by tags to filter saved reports not only by type but also by their content. For example, you can split reports by the topic, by country, by goals of their creation:

To add one or more tags to a report, select the report by checking the box on the left of the report name, and click the # (Add tag to report) above the list of reports.

Then add one or more tags and click ‘Ok’ to save the changes:

To filter the results, open the «Tag filter» drop-down list and select one or more tags that you want to filter on.

If you select several tags, you will see reports that have ALL the selected tags. To delete a tag for one or more reports, select them by checking the box and click the # button. In the window that opens, click the cross mark next to the tags you want to delete and save the changes:

Custom dashboards

Dashboards allow you to see data on several projects in one place. It gives you an opportunity to react to changes in user behavior decisively and saves time because you won’t need to open all the reports for analysis.

To the dashboard, you can add several custom reports that will be presented as one page. To grasp the whole picture, you can also use one widget to analyze different platforms’ project metrics. When you need to, for example, routinely track monetization metrics, you can create a dashboard with monetization metrics for the selected apps.

You can add to the dashboard a widget:

• a diagram that shows Gross performance per app.

• a diagram that shows an app-specific revenue structure.

• a table with more detailed information for the past month: number of new users, active users, revenue, conversion to paying user, ARPU, ARPPU, and other app-specific metrics.

If you want to build a report that shows the performance of apps’ audience acquisition channels, you can add some widgets to one dashboard and they will show the current traffic status. For example:

• a table for analyzing the most important metrics by traffic sources: new users, gross, paying conversion, ARPU, Day 1 retention, day 7 retention, and so on.

• a diagram that shows new user acquisition performance by app.

• and other widgets that help you analyze channel performance.

You can find all the necessary information on one page. You can respond to trends fast and improve your ad strategies, reassess your budget, and so on.

Creating a dashboard

To start working with dashboards, select the "Dashboard" section on the top of the form. To create a new dashboard, enter its name and click “Save”:

Once you name it, you'll be able to fill it out with your report widgets and start monitoring metrics. You can always rename your dashboard later.

Add widgets to a dashboard

After you click the “Add widget” button, you will be redirected to the Basic Metrics for building a report. After you complete it, you can add it as a widget to the dashboard. Also, you can create widgets from any saved reports in the Basic Metrics.

To do it, you need to:

1. Name the widget.

2. Choose the dashboard where you want to save the widget to.

3. Set the report date range: fixed, moving.

If a preset time frame is set (i.e. last 7 days) then this time frame will be used further as to the actual date.

After that your widget appears on the dashboard where you can quickly analyze the necessary metrics for the selected apps:

Text widget that you can use to add comments to dashboards and these comments will be visible to your coworkers. You can highlight the main points, key analysis takeaways, remind your colleagues that they can open the report from any widget, and go on with the research by changing the report, its rate range, applying filters to the data, etc. You can add links or other data sources to the text, for example, a link to a storyboard of screens or a screenshot of features in question.

To add a text widget to a dashboard, click ‘Add widget’ in the drop-down menu and then select the ‘Text widget (for notes and descriptions)’ type.

Name your text widget and then add notes or takeaways to the text body while formatting it as you like (alignment, styles, colors, bulleted lists, etc.)

A total of 20 widgets per dashboard are available, of which up to 10 can be widgets based on SQL queries. Additionaly, 20 text widgets allow users to freely include information in text format. For example, you can customize your dashboard by adding: 8 SQL widgets, 12 widgets from other reports (Basic metrics report, Custom events, Retention report, Funnels), and a few more text widgets to explain graphs or describe conclusions.

Widget customization

By clicking the widget menu, you can open the report in the Basic Metrics and edit it, add an additional filter, group, apply a segment, select additional metrics or apps for a more detailed analysis.

You also have an opportunity to change the widget’s name and description. Hover over the title to change it. A pencil icon will appear next to the title, click it to edit. The widget description may be useful, for example, if you give your coworkers access to the dashboard. You can add comments, conclusions, and other useful information to your widgets.

To read the widget’s description, you need to choose “i” icon to the right of the widget’s name.

Table widget settings

Sometimes the text in tables may be difficult to read. You can use Table settings to adjust the column width and text content. Only users with Editor access can apply these settings.

Autofit column width option sets the best column width automatically based on the text length in the header and column values. If this option is not selected, you can change the column width manually.

Wrap text option wraps text in the heading and table rows. The text is wraped up to 3 lines, however, if it is too long, the text will be cut.

You can use both options simultaneously. These settings are saved on the dashboard for all users.

Dashboard management

If you want to switch between dashboards, click the drop-down menu near the dashboard’s name.

There are two types of access to the dashboards: for view and for edit. The access level of a user (owner, viever, editor) can be found next to the dashboard name.

The user who gained permission to edit, can:

• Edit created widgets (change report settings, rename, switch widgets, add/edit the description of the widget).

• Add and delete widgets (also, by using the ‘Move to another dashboard’ option on another widget).

• Grant access to view the dashboard to other team members.

The user who gained permission to edit, CAN NOT:

• Delete the dashboard.

• Give edit permission to another team member. Only the dashboard’s owner can grant permission to edit.

• Remove the access that is not provided by them.

You can also use the menu to save the selected widget to your dashboard:

To change the order of widgets, simply drag any widget to your desired position, via the icon at the top right of the report.

The icon in the lower right of the report is used to resize the widgets.

Adding comments to a dashboard

Add a comment to a dashboard if you want to discuss widget data with your colleagues, ask a question or share your conclusions.

To do this, click the ‘Comments’ icon to the right of the dashboard’s name or next to the widget’s name if you want to comment on a specific widget.

To see all comments, click ‘Comments’ next to the Refresh button:

Mentions of colleagues To mention a colleague, type the @ symbol followed by the user name. Choose one of the options. If you’ve mentioned a user in a comment, they will receive a notification via email and an on-site (bell) notification.

If you’ve mentioned a user who does not have access to the dashboard, then they will be granted access to it and receive an email notification about the mention.

Edit or delete comments You can edit or delete your comments. Open the drop-down list (the three dots menu) next to the comment and select the action you want to perform.

When your problem is resolved, you can close it by clicking the check mark or open the three dots menu and select ‘Close thread’.

gives you information about audience overlap (of a set of advertising IDs) in applications of the same platform within the space. This report becomes available when you have at least two applications on the same platform within the space, but it is not available for web applications. To match users during report building, we use a set of advertising IDs, that is, for an advertising ID used in one project, we look for similar IDs in another project. For iOS applications comparison we use IDFA, and for Android and Windows applications comparison we use advertising ID.

Some users don’t have an advertising ID parameter in their card and these users are not taken into account while comparing apps’ audiences.

The report has two tabs:

• General report

• Advanced report

The “General report” tab contains general information about the number of overlaps of active and paying audiences. In order to build a report you need to select the application platform, the overlap period, and select the applications that you want to compare. The overlap period is the time period during which users were active in at least one of the comparison projects. Then click the "View result" button to see the report results.

The Active users chart shows the overlap of active users in the selected projects (users who had at least one session in the selected period).

The Paying users chart shows the overlap of paying users (users who made at least one payment) in the selected projects.

The "Advanced report" tab allows you to compare users by custom parameters. To perform a comparison, you need to select the Main project and the Donor projects with which the comparison is to be made. You also have to choose the criteria of overlapping. You can overlap the audience by:

• channels

• countries

• amount of payments

• number of payments

• date of the first payment

• date of the last payment

• date of last activity

To add several overlaps with different criteria press +Counter. After specifying the settings necessary for comparison, click the "View result" button to see a report results table. You can change the display of audience overlap results from a table to a chart.

After receiving the results of the comparison you will be able to:

• create a user segment and study the behavior of the users later on. For example, you can save a segment of users who make payments in Application A, but don’t pay in Application B. After that, using the created segment, you can study user behavior in detail: what offers motivate users to buy in Application A, what goods they buy there, what amount of money they spend. Perhaps the prices in Application A are lower than in Application B, or in Application A you can buy goods for customizing your game character, while in Application B there are no such goods. After analyzing the paying audience of your application you can develop hypotheses for A/B testing, as well as start sending push notifications with appealing offers to potential paying audience.

• select a potential paying audience for behavior research and Gross increase. For example, by studying a segment composed of users who have not paid in Application B for a long time or users who pay more in Application A. Thus, you can analyze what exactly users buy in Application A, at what stage they make payments, what amount of money they spend, what offers motivate users to buy. With this information at hand, you can test various hypotheses (price changes, offer content, etc.) that can increase Gross.

• export users’ devtodevId to a CSV file. This can for example save cross-promotion costs by excluding users who are already using the promoted application. Or you can upload the IDs of the users who are not yet using your second application in order to promote it later on:

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

Making 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. 8 options are available: line chart, area chart, column and pie charts, area and column stacked charts, combo chart and number. 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-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.

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

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.

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

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

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

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, as the value of the variable you will get the defined date with an offset for the number of days passed. 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 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 variable’ block above the query text.

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.

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

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.

Functions that are not available currently:

• temporary table creation

• tables creation and adding data to tables

• creation of indexes to the table

• stored procedures

Working with the date and time

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:

To modify date and time, use the following functions:

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

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:

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

• firstappversion - the first version installed by the user

• firstappversionid - ID of the first installation version

• 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

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

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

• 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

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

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:

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, the View consists of a set of named columns and rows of data. After you create the 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 loading because they allow 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

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.

Usage examples

Create a projection (DAU, in this case)

Usage:

Create dictionaries:

SQL query examples

Let’s explore several SQL query examples. Number of purchases of different product grouped by months:

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: