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