Create Custom Indicator for Each Game

  • Register sample queries for each indicator in the data source and set up the page source to check indicators for specific games in Analytics.
  • You will need authorization for Analytics – Administration, Page Source, and Data Source menus to create customized indicators.
  • Please refer to the basic custom indicator guide here.
  • For the basic guide on using BigQuery, please check here.

Cumulative AU, NU, NU Rate

  • Cumulative AU, NU, and NU to AU rate during the search period.
  • The indicator can also be viewed in BigQuery.
  • Creating indicators involves two steps: defining a data source and defining a page source.

Step 1: Define Data Source

  • Go to the Console > Analytics > Manage Indicator > Data Source, and then click the Register “Data Source” Button.
  • Select and enter the information below and click Next.
    • Select
      • Date Range: Daily
      • Database: BIGQUERY
    • Enter
      • Data Source Name: Cumulative_AU, NU, NU Rate
      • Write SQL Query
        • Paste the sample query below and modify GameID.
          • GameID: The GameID of the game to which the indicator applies in App Center
select appidgroup, yyyymmdd_period, au, nu, nu_rate
from( with login_log as
(
select appIdGroup, ifnull(playerId, vid) as playerId, newuser
from ##companyDataset##.t_hive_login_log
where datetime >= timestamp_sub(timestamp('##FROMDT##'), interval 9 hour)
and datetime < timestamp_add(timestamp_sub(timestamp('##TODT##'), interval 9 hour), INTERVAL 1 day)
and appIdGroup in ("GameID")
and ifnull(playerId, vid) <> 0
qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1)
select appIdGroup, concat('##FROMDT##',' ~ ','##TODT##') as yyyymmdd_period, count(distinct playerId) as au,
count(distinct case when newUser='Y' then playerId end) as nu,
round(safe_divide( count(distinct case when newUser='Y' then playerId end ),
count(distinct playerId))*100,2) as nu_rate
from login_log
group by appIdGroup,yyyymmdd_period
)

  • After specifying the axis and value selections, click Next.
    • Axis Selection: yyyymmdd_period
    • Value Selection: au, nu, nu_rate
    • You can rename each column to your desired names.
    • You can adjust the decimal places for each value from none to three decimal places. For “nu_rate,” it’s recommended to set decimals to see the value in detail since it is a percentage value.

  • Don’t set sorting or filtering; save the data source.

Step 2: Define Page Source

  • Go to Console > Analytics > Management > Page Source and click the “Register Page Source” button.
  • Enter and select the information below and click Next.
    • Enter
      • Page Title: Cumulative_AU, NU, NU Rate
    • Select
      • Include Axis Configuration: Include
      • Data Source Information: Cumulative _AU, NU, NU Rate
    • Use default values for other items.

  • Configure the placement of the axis and values.
    • Left: yyyymmdd_period
    • Value: au, nu, nu_rate
      • You can change the order of values by dragging and dropping. The order determines their position on the left side of the table.
    • ∑ Value Location: Above the horizontal axis

 

  • Click the “Preview” button to ensure that the table displays correctly. Once confirmed, save the page source.

  • If you select a particular game in the game-specific indicator, the indicator is displayed under Game-specific Indicator > Custom Indicators, so you must select the game to which you want to apply the indicator.
    • Access Console > Analytics > Management > Page Source menu and select “Custom Indicator Application Location” for the cumulative_AU, NU, NU rate source as the game to which you want to apply the indicator.

  • Now, you can access the Cumulative AU, NU, NU Rate indicator in Analytics > Game-specific Indicator > Custom Indicator.

Monthly Stickiness

  • Average daily DAU rate to monthly MAU.
  • A detailed description of Stickiness can be found here.
  • This indicator can also be viewed in Big Query.
  • Creating indicators involves two steps: defining a data source and defining a page source.

Step 1: Define Data Source

  • Go to Console > Analytics > Manage Indicator > Data Source, and then click the “Register Data Source” Button.
  • Select and enter the information below and click Next.
    • Select
      • Date Range: Monthly
      • Data Base: BIGQUERY
    • Enter
      • Data Source Name: Monthly_Stickiness
      • Write SQL Query
        • Paste the sample query below and modify GameID.
          • GameID: The GameID of the game to which the indicator applies in App Center
select appidgroup, yyyymm, avg_dau, au, stickiness
from (
with login_log as (
select appIdGroup, substr(cast(timestamp_add(dateTime, interval 9 hour) as string), 1, 10) as yyyymmdd, substr(cast(timestamp_add(dateTime, interval 9 hour) as string), 1, 7) as yyyymm, ifnull(playerId, vid) as playerId
from ##companyDataset##.t_hive_login_log
where datetime >= timestamp_sub(timestamp(concat('##FROMMM##' ,'-01')), interval 9 hour)
and datetime < timestamp(date_add(datetime(timestamp_sub(timestamp(concat('##TOMM##' ,'-01')), interval 9 hour)), interval 1 month))
and appIdGroup in ("GameID")
and ifnull(playerId, vid) <> 0
qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
)select appIdGroup, yyyymm, round(avg_dau,2) as avg_dau, au, ifnull(round(safe_divide(avg_dau, au)*100, 2), 0) as stickiness
from
(
select appIdGroup, yyyymm, au, avg(dau) as avg_dau
from
(
select appIdGroup, yyyymm, count(distinct playerId) over(partition by appIdGroup, yyyymmdd) as dau,count(distinct playerId) over(partition by appIdGroup, yyyymm) as au
from login_log
)
group by appIdGroup, yyyymm, au
)
)

  • After specifying the axis and value selections, click Next.
    • Axis Selection: yyyymm
    • Value Selection: avg_dau, au, stickiness
    • You can rename each column to your desired names.
    • You can adjust the decimal places for each value from none to three decimal places. For “stickiness” it’s recommended to set decimals to see the value in detail since it is a percentage value.

  • After configuring the sorting, save the data source.
    • yyyymm: Descending Order

Step 2: Define Page Source

  • Go to Console > Analytics > Management > Page Source and click the “Register Page Source” button.
  • Enter and select the information below and click Next.
    • Enter
      • Page Title: Monthly_Stickiness
    • Select
      • Include Axis Configuration: Include
      • Data Source Information: Monthly_Stickiness
    • Use default values for other items.

  • Configure the placement of the axis and values.
    • Left: yyyymm
    • Value: avg_dau, au, stickiness
      • You can change the order of values by dragging and dropping. The order determines their position on the left side of the table.
    • ∑ Value Location: Above the horizontal axis

  • Click the “Preview” button to ensure that the table displays correctly. Once confirmed, save the page source.

  • If you select a particular game in the game-specific indicator, the indicator is displayed under Game-specific Indicator > Custom Indicators, so you must select the game to which you want to apply the indicator.
    • Access Console > Analytics > Management > Page Source menu and select “Custom Indicator Application Location” for the Monthly_Stickiness as the game to which you want to apply the indicator.

  • Now, you can access the Monthly Stickiness indicator in Analytics > Game-specific Indicator > Custom Indicator.

 

Weekly Stickiness

  • Average daily DAU rate to weekly WAU.
  • A detailed description of Stickiness can be found here.
  • This indicator can also be viewed in Big Query.
  • Creating indicators involves two steps: defining a data source and defining a page source.

Step 1: Define Data Source

  • Go to Console > Analytics > Manage Indicator > Data Source, and then click the “Register Data Source” Button.
  • Select and enter the information below and click Next.
    • Select
      • Date Range: Daily
      • Data Base: BIGQUERY
    • Enter
      • Data Source Name: Weekly_Stickiness
      • Write SQL Query
        • Paste the sample query below and modify GameID.
          • GameID: The GameID of the game to which the indicator applies in App Center
select appidgroup, yyyymmdd_period, avg_dau, au, stickiness
from
(
with login_log as (
select appidgroup, yyyymmdd, monday, sunday,concat(monday,' ~ ',sunday) as yyyymmdd_period, playerId
from
(
select appIdgroup, yyyymmdd
, date_sub(date(yyyymmdd), interval if(extract(dayofweek from yyyymmdd)=1,7,extract(dayofweek from yyyymmdd)-1)-1 day) as monday
, date_sub(date(yyyymmdd), interval if(extract(dayofweek from yyyymmdd)=1,7,extract(dayofweek from yyyymmdd)-1)-7 day) as sunday
, playerId
from
(
select appIdGroup, date(substr(cast(timestamp_add(dateTime, interval 9 hour) as string), 1, 10)) as yyyymmdd, ifnull(playerId, vid) as playerId
from ##companyDataset##.t_hive_login_log
where datetime >= timestamp_sub(timestamp('##FROMDT##'), interval 9 hour)
and datetime < timestamp_add(timestamp_sub(timestamp('##TODT##'), interval 9 hour), INTERVAL 1 day)
and appIdGroup in ("GameID")
and ifnull(playerId, vid) <> 0
qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
)
)
where monday >= date('##FROMDT##')
and sunday <= date('##TODT##')
)
select appIdGroup, yyyymmdd_period, round(avg_dau,2) as avg_dau, au, ifnull(round(safe_divide(avg_dau, au)*100, 2), 0) as stickiness
from
(
select appIdGroup, yyyymmdd_period, au, avg(dau) as avg_dau
from
(
select appIdGroup, yyyymmdd_period, count(distinct playerId) over(partition by appIdGroup, yyyymmdd) as dau,count(distinct playerId) over(partition by appIdGroup, yyyymmdd_period) as au
from login_log
)
group by appIdGroup, yyyymmdd_period, au
)
)

  • After specifying the axis and value selections, click Next.
    • Axis Selection: yyyymmdd_period
    • Value Selection: avg_dau, au, stickiness
    • You can rename each column to your desired names.
    • You can adjust the decimal places for each value from none to three decimal places. For “stickiness” it’s recommended to set decimals to see the value in detail since it is a percentage value.

  • After configuring the sorting, save the data source.
    • yyyymmdd_period : Descending Order

Step 2: Define Page Source

  • Go to Console > Analytics > Management > Page Source and click the “Register Page Source” button.
  • Enter and select the information below and click Next.
    • Enter
      • Page Title: Weeky_Stickiness
    • Select
      • Include Axis Configuration: Include
      • Data Source Information: Weeky_Stickiness
    • Use default values for other items.

  • Configure the placement of the axis and values.
    • Left: yyyymm
    • Value: avg_dau, au, stickiness
      • You can change the order of values by dragging and dropping. The order determines their position on the left side of the table.
    • ∑ Value Location: Above the horizontal axis

  • Click the “Preview” button to ensure that the table displays correctly. Once confirmed, save the page source.

  • If you select a particular game in the game-specific indicator, the indicator is displayed under Game-specific Indicator > Custom Indicators, so you must select the game to which you want to apply the indicator.
    • Access Console > Analytics > Management > Page Source menu and select “Custom Indicator Application Location” for the Weekly_Stickiness as the game to which you want to apply the indicator.

  • Now, you can access the Weekly Stickiness indicator in Analytics > Game-specific Indicator > Custom Indicator.