Portfolio Analysis Queries

Monetizing Users

The Question


Of users who are installed and have generated activities in more than one property - which users of which applications generate the highest average revenue per user?

Monetizing Users

How To Instrument


For every user make sure to send a unique and consistent UID (User Identifier) across your portfolio of applications. In Upsight Analytics this is the “S” parameter. This could be a User ID, a rewards number or any unique user identification number that is consistent and specific to the user throughout your portfolio of applications.

Parameter Type Description
s unsigned 63-bit init The UID of the user adding the application. Note Upsight Analytics only accepts integers up to 63-bits. For information on converting UIDs greater than 63-bits, click here.

Monetizing Users

How To Query Your Data


Average Revenue Per User With Only One Application

This will return a single number that counts how the average revenue per user (ARPU) if they have installed one of the applications you choose to review but not both.

  1. Application Selection
    • Replace ##APP1_pgr## with the name of the pgr table of the application whose users you would like to review. For example: demogame1_pgr.
    • Replace ##APP2_pgr## with the name of the pgr table of the application whose users you would like to exclude. For example: demogame2_pgr.
  2. Time Period
    • Replace ##date_in_'YYYY-MM-DD'_format## as the initial date of interest using the format 'YYYY-MM-DD', for example '2012-06-01' would mean June 6 2012
    • Replace ##month_in_YYYYMM_format## as the month filter, for example 201206 would mean June 2012.

The above examples would return the ARPU of the users who used demogame1 but not demogame2 in June starting with June 1 2012.

This query is very customizable.

  • If you would like to remove the filter by month you can remove the two lines that start with WHERE to search for results from all time. This is not recommended as it will slow down your query considerably.
  • By default this query will return the users who have installed after the day chosen. By changing the >= to a <= you can return the results for users who have installed before the chosen date.
    SELECT sum(user_revenu.totalrev)/count(user_revenu.install_s) AS ARPU
    FROM (
       SELECT installdata.install_s,revenue.totalrev
       FROM (
          -- select users that have ONLY installed both applications
          SELECT users.s as install_s, collect_set(app) as apps
          FROM (
             SELECT s, "app1" as app
             FROM ##APP1_apa##
             --WHERE month >= ##month_in_YYYYMM_format##
             AND FROM_UNIXTIME(utc_timestamp, 'yyyy-MM-dd') >= ##date_in_'YYYY-MM-DD'_format##
             UNION ALL
             SELECT s, "app2" as app
             FROM ##APP2_apa##
             --WHERE month >= ##month_in_YYYYMM_format##
             AND FROM_UNIXTIME(utc_timestamp, 'yyyy-MM-dd') >= ##date_in_'YYYY-MM-DD'_format##
          )users
          GROUP BY users.s
          HAVING size(apps)=2
       )installdata
       LEFT OUTER JOIN (
          -- Total revenue generated from both applications
          SELECT inner_rev.s, sum(inner_rev.v) AS totalrev
          FROM (
             SELECT s,v
             FROM ##APP1_mtu##
             UNION ALL
             SELECT s, v
             FROM ##APP2_mtu##
          ) inner_rev
          GROUP BY inner_rev.s
       )revenue
       ON installdata.install_s=revenue.s 
    ) user_revenu
    

Average Revenue Per User With Two Applications

This will return a single number that counts how the average revenue per user (ARPU) if they have installed both of the applications you choose to review.

  • Application Selection
    • Replace ##APP1_apa##, ##APP1_mtu## with the name of tables of the first application you are interested in reviewing, for example: demogame1_apa and demogame1_mtu.
  • Replace ##APP2_apa##, ##APP2_mtu## with the name of tables of the first application you are interested in reviewing, for example: demogame2_apa and demogame2_mtu.
  • Time Period
    • Replace ##date_in_'YYYY-MM-DD'_format## as the initial date of interest using the format 'YYYY-MM-DD', for example '2012-06-01' would mean June 1 2012.
    • Replace ##month_in_YYYYMM_format## as the initial date of interest using the format 'YYYY-MM-DD', for example 201206 would mean June 2012.

      SELECT sum(user_revenu.totalrev)/count(user_revenu.install_s) AS ARPU FROM ( SELECT installdata.install_s,revenue.totalrev FROM ( -- select users that have ONLY installed both applications SELECT users.s as install_s, collect_set(app) as apps FROM ( SELECT s, "app1" as app FROM ##APP1_apa## --WHERE month >= ##month_in_YYYYMM_format## AND FROM_UNIXTIME(utc_timestamp, 'yyyy-MM-dd') >= ##date_in_'YYYY-MM-DD'format## UNION ALL SELECT s, "app2" as app FROM ##APP2_apa## --WHERE month >= ##month_in_YYYYMM_format## AND FROM_UNIXTIME(utc_timestamp, 'yyyy-MM-dd') >= ##date_in'YYYY-MM-DD'_format## )users GROUP BY users.s HAVING size(apps)=2 )installdata LEFT OUTER JOIN ( -- Total revenue generated from both applications SELECT inner_rev.s, sum(inner_rev.v) AS totalrev FROM ( SELECT s,v FROM ##APP1_mtu## UNION ALL SELECT s, v FROM ##APP2_mtu## ) inner_rev GROUP BY inner_rev.s )revenue ON installdata.install_s=revenue.s ) user_revenu

Monetizing Users

How To Visualize The Results


A simple bar chart can give simple at-a-glance insight into where your users are in your ecosystem.

The first query will yield the value in the first bar. If you swap the table you use in app1 with the table you use in app2, the query will return the value in the second bar. The second query will return the value in the third bar.

Application(s)ARPU
Application 1$15
Application 2$10
Application 1 + Application 2$28

User Installs

The Question


How many unique users have installed two different applications in my portfolio last month? We are looking to understand how many users have installed each application within a particular timeframe, and how many users have installed both applications. We are looking to create a simple Venn Diagram that shows overlap in users between the two applications.

Understanding which properties (applications) users are engaged with in your portfolio of different applications is a more powerful way to understand the health of your portfolio.

User Installs

How To Instrument


For every user make sure to send a unique and consistent UID (User Identifier) across your portfolio of applications. In Upsight Analytics this is the S parameter. This could be a User ID, a rewards number or any unique user identification number that is consistent and specific to the user throughout your portfolio of applications.

Parameter Type Description
s unisigned 63-bit int The UID of the user adding the application. Note Upsight Analytics only accepts integers up to 63-bits. For information on converting UIDs greater than 63-bits, click here.

User Installs

How To Query Your Data


Number of Users With Only One Application Installed

This will return a single number that counts the number of users that have downloaded one of the applications you choose to review but not both.

  1. Application Selection

    • Replace ##APA1_apa## with the name of the apa table of the application whose users you would like to review. For example demogame1_apa.
    • Replace ##APA2_apa## with the name of the apa table of the application whose users you would like to exclude. For example demogame2_apa.
  2. Time Period

    • Replace ##lower_date_in"YYY-MM-DD"_format## with the lower bound of interest using the format "YYYY-MM-DD", for example "2013-04-01".
    • Replace ##upper_date_in"YYY-MM-DD"_format## with the upper bound of interest using the format "YYYY-MM-DD", for example "2013-04-30".

The above examples would return the number of users who used demogame1 but not demogame2 in April 2013.

SELECT COUNT(*) 
FROM
    (SELECT s AS app1s, MIN(utc_timestamp) AS app1utc
    FROM ##APP1_apa##
    GROUP BY s) app1

LEFT OUTER JOIN

    (SELECT s AS app2s, MIN(utc_timestamp) AS app2utc
    FROM ##APP2_apa##
    GROUP BY s) app2

ON app1.app1s = app2.app2s
WHERE app2.app2s IS NULL
AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##

Number of Users With Two Applications Installed

This will return a single number that counts the number of users that have downloaded both of the applications you choose to review.

  1. Application Selection
    • Replace ##APA1_apa## with the name of the apa table of the first application you are interested in reviewing. For example demogame1_apa.
    • Replace ##APA2_apa## with the name of the apa table of the second application you are interested in reviewing. For example demogame2_apa.
  2. Time Period
    • Replace ##lower_date_in"YYY-MM-DD"_format## with the lower bound of interest using the format "YYYY-MM-DD", for example "2013-04-01".
    • Replace ##upper_date_in"YYY-MM-DD"_format## with the upper bound of interest using the format "YYYY-MM-DD", for example "2013-04-30".

The above examples would return the number of users who used both demogame1 and demogame2 in April 2013.

SELECT COUNT(*) 
FROM (
   SELECT s AS app1s, min(utc_timestamp) AS app1utc
    FROM ##APP1_apa##
    GROUP BY s) app1
LEFT OUTER JOIN (
   SELECT s AS app2s, min(utc_timestamp) AS app2utc
    FROM ##APP2_apa##
    GROUP BY s) app2
ON app1.app1s = app2.app2s
WHERE app2.app2s IS NOT NULL
AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##
AND FROM_UNIXTIME(app2.app2utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
AND FROM_UNIXTIME(app2.app2utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##

User Installs

How To Visualize The Results


A simple Venn Diagram can give simple at-a-glance insight into where your users are in your ecosystem. Additionally, sizing the circles by the number of users in each application gives an easy visual queue to relative user base and overlap in user base.

The first query will yield the number of users in the red circle but not in the intersecting purple area. If you swap the table you use in app1 with the table you use in app2, the query will return the number of users in the blue circle but not the intersecting purple area. The second query will return the number of users in the intersecting purple area.

  # of users who installed % of users installed
Just Application 1 1000 50%
Just Application 2 800 40%
Application 1 + Application 2 200 10%
Unique User Installs2000100%

User Flow

The Question


Of users who are active or have installed more than one application, in what order did they flow through the portfolio?

Understanding which properties (applications) users are engaged with in your portfolio of different applications is a more powerful way to understand the health of your portfolio.

User Flow

How To Instrument


For every user make sure to send a unique and consistent UID (User Identifier) across your portfolio of applications. In Upsight Analytics this is the s parameter. This could be a User ID, a rewards number or any unique user identification number that is consistent and specific to the user throughout your portfolio of applications.

Parameter Type Description
s unsigned 63-bit int The UID of the user adding the application. Note Upsight Analytics only accepts integers up to 63-bits. For information on converting UIDs greater than 63-bits, click here.

User Flow

How To Query Your Data


Number of Users With Two Applications Installed

  • Application Selection
    • Replace ##APP1_apa## and ##APP2_apa## with the name of both applications you are interested in reviewing, for example: demogame1_apa, demogame2_apa.
  • Time Period
    • Replace ##lower_date_in_"YYYY-MM-DD"_format## as the lower bound of interest using the format "YYYY-MM-DD", for example "2013-04-01" would mean April 1 2013.
    • Replace ##upper_date_in_"YYYY-MM-DD"_format## as the upper bound of interest using the format "YYYY-MM-DD", for example "2013-04-30" would mean April 30 2013.
        SELECT COUNT(*) 
        FROM (
           SELECT s AS app1s, MIN(utc_timestamp) AS app1utc
           FROM ##APP1_apa##
           GROUP BY s
        ) app1
        LEFT OUTER JOIN (
           SELECT s AS app2s, MIN(utc_timestamp) AS app2utc
           FROM ##APP2_apa##
           GROUP BY s
        ) app2
        ON app1.app1s = app2.app2s
        WHERE app2.app2s IS NOT NULL
        AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
        AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##
        AND FROM_UNIXTIME(app2.app2utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
        AND FROM_UNIXTIME(app2.app2utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##

Number of Users Who Installed app1 followed by app2

  • Application Selection
    • Replace ##APP1_apa## with the name of the table of the application you are interested in reviewing first. For example: demogame1_apa.
    • Replace ##APP2_apa## with the name of the table of the application you are interested in reviewing second. For example: demogame2_apa.
  • Time Period
    • Replace ##lower_date_in_"YYYY-MM-DD"_format## as the lower bound of interest using the format "YYYY-MM-DD", for example "2013-04-01" would mean April 1 2013.
    • Replace ##upper_date_in_"YYYY-MM-DD"_format## as the upper bound of interest using the format "YYYY-MM-DD", for example "2013-04-30" would mean April 30 2013.
        SELECT COUNT(*)
        FROM (
           SELECT s AS app1s, MIN(utc_timestamp) AS app1utc
           FROM ##APP1_apa##
           GROUP BY s
        )app1

        JOIN (
           SELECT s AS app2s, MIN(utc_timestamp) AS app2utc
           FROM ##APP2_apa##
           GROUP BY s
        )app2
        ON app1.app1s = app2.app2s
        WHERE app1.app1utc <= app2.app2utc
        AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
        AND FROM_UNIXTIME(app1.app1utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##
        AND FROM_UNIXTIME(app2.app2utc, 'yyyy-MM-dd')>= ##lower_date_in_"YYYY-MM-DD"_format##
        AND FROM_UNIXTIME(app2.app2utc, 'yyyy-MM-dd')<= ##upper_date_in_"YYYY-MM-DD"_format##

User Flow

How To Visualize The Results


Visually representing the area of the circle relative to the % of users who moved from one application and in which direction gives a clear picture of user flow between applications.

The first query will return the value in the third row. The second query will yield the value in the first row. If you swap the table you use in app1 with the table you use in app2, the query will return the value in the second row.

  Number of Users Flowed Percentage of Users Installed
Application 1 first then Application 2 460 46%
Application 2 first then Application 1 540 54%
Application 1 + Application 21000