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.
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.
SELECT day, COUNT(1) as installs
FROM install
WHERE month = 201701
AND app_id = 123456
GROUP BY day
This query sums up by day all of your account's validated revenue (all upsight.monetization.validated
messages) from January 2017.
SELECT day, SUM(upsight_data_value_usd) as Revenue
FROM monetization
WHERE month = 201701 AND type = ‘upsight.monetization.validated’
GROUP BY day
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.
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.
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.
SELECT day, COUNT(1) as DAU
FROM
(
SELECT sid, day
FROM monetization
WHERE month = 201701
GROUP BY sid, day
) t1
GROUP BY day
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.
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
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.
SELECT COUNT(1)
FROM
(
SELECT sid
FROM monetization
WHERE month = 201701
GROUP BY sid
) t1
This query outputs the sum of all validated in app purchases by their product type for the month of January 2017.
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