Basic Queries

Overview

In this section, we go over some simple queries that can answer many questions you may have about your applications. One thing to note is that the queries here may not match metrics on your dashboard as your dashboard metrics may have specific definitions for your applications. In most circumstances, your metrics should be the most correct source of information, though these examples should produce results that are close unless your specific metric differs significantly.

Note Some of these examples include app_id in the WHERE clause and some do not. In general, you will likely want to limit your queries to a specific application or add app_id to the GROUP BY to get per-app information.

Number of install events each a day in a month

Basic Query

Overview

This query counts all of the install messages by day (all rows in the install table are Upsight-generated install messages) that have occurred in January 2017 for a specific application.

Query

SELECT day, COUNT(1) as installs 
FROM install 
WHERE month = 201701 
AND app_id = 123456
GROUP BY day

Total revenue each day in a month

Basic Query

Overview

This query sums up by day all of your account's validated revenue (all upsight.monetization.validated messages) from January 2017.

Query

SELECT day, SUM(upsight_data_value_usd) as Revenue 
FROM monetization 
WHERE month = 201701 AND type = ‘upsight.monetization.validated’ 
GROUP BY day

Number of users active each day in a month

Number Of Users Active Each Day In A Month

Overview

This query counts all unique users by day for the month of January 2017 without deduplicating users per application.

This could be done without a subquery by using count(distinct sid). If done without the subquery, all of the data would have to go through one CPU, which would be a bottleneck. It's often faster to achieve the same outcome in two steps to take advantage of the parallelism of DataMine.

Query

SELECT day, COUNT(1) as DAU 
FROM 
( 
    SELECT sid, day 
    FROM session 
    WHERE month = 201701 
    GROUP BY sid, day 
) t1 
GROUP BY day

Note Because sid is the device identifier assigned by Upsight upon install, two or more players using the same device will be counted as one user. If your app tracks players using your own internal ID, you may send that ID as a custom user_attribute to count users by user ID.

Total number of spenders each day on a month

Basic Query

Overview

This query outputs the total number of unique spenders ("spenders" in this case means users who have sent any kind of monetization message) per each day for all of your applications.

In this query, we use a subquery that outputs a unique row per user and every day on which they spent in a month. From there, we do a count of each row by day.

Query

SELECT day, COUNT(1) as DAU 
FROM 
( 
    SELECT sid, day 
    FROM monetization 
    WHERE month = 201701 
    GROUP BY sid, day 
) t1 
GROUP BY day

Monthly Active Users

Basic Query

Overview

This query outputs the total monthly active users per application for the month of January 2017. MAU is not just a sum of the DAU for the month. If a user came back on two separate days we want to only count them once.

Query

SELECT app_id,COUNT(1) 
FROM 
( 
    SELECT app_id,sid
    FROM session 
    WHERE month = 201701 
    GROUP BY app_id,sid
) t1
GROUP BY app_id

Monthly Unique Spenders

Basic Query

Overview

This query outputs the total number of users that have sent a monetization event in the month of January 2017. You can further refine this by looking only for validated messages by adding AND type = ‘upsight.monetization.validated’ in the WHERE clause.

Query

SELECT COUNT(1) 
FROM 
( 
    SELECT sid 
    FROM monetization 
    WHERE month = 201701 
    GROUP BY sid 
) t1

Revenue Per Virtual Good Item

Basic Query

Overview

This query outputs the sum of all validated in app purchases by their product type for the month of January 2017.

Query

SELECT upsight_data_product, SUM(upsight_data_value_usd) as Revenue 
FROM monetization 
WHERE month = 201701 AND type = ‘upsight.monetization.validated’ 
GROUP BY upsight_data_product