Retrieve indicators in bigQuery
Retrieve indicators in BigQuery¶
- You can check the basic BigQuery usage guide here.
- After entering user input parameters and executing the sample query corresponding to each indicator, you can examine the indicator’s numerical value.
- By connecting query results to a spreadsheet, the chart function can be used to visualize indicators.
Cumulative AU, NU, NU ratio¶
- Cumulative AU, NU, and NU to AU ratio during the search period.
- After inputting the five user input parameters corresponding to the set, the query can be executed to verify the numbers.
- User input parameters
- yyyymmdd_1 : Search start date (YYYY-MM-DD)
- yyyymmdd_2 : Search last date (YYYY-MM-DD)
- timezone_offset : Time zone setting (time offset based on UTC – 9 for KST)
- comapny_index : Company number
- Bigquery > explorer at the top left > dataset (analytics_company number_live) under fluted-airline-109810
- appIdGroup_1: App Center registered GameID
- When entering two or more,
set appidGroup_1 = '"game1","game2"';
- When entering two or more,
- Query result
- appIdGroup : App Center registered GameID
- yyyymmdd_period : Search period
- au : Number of active users
- nu : Number of new users
- nu_rate : NU to AU ratio
- ( nu / au ) * 100
- Sample
- User input parameters
declare yyyymmdd_1 string ;
declare yyyymmdd_2 string ;
declare timezone_offset int64;
declare appIdGroup_1 string;
declare company_index int64;
declare dataset string default '';
declare query string default '';
set yyyymmdd_1 = '2023-07-01';
set yyyymmdd_2 = '2023-07-08';
set timezone_offset = 9 ;
set company_index = 5 ;
set appIdGroup_1 = '"com.sample.samplegametest"' ;
set dataset = 'analytics_' || company_index || '_live';
set query = format("""
with login_log as (
select appIdGroup, ifnull(playerId, vid) as playerId, newuser
from `fluted-airline-109810.%s.t_hive_login_log`
where datetime >= timestamp_sub(timestamp('%s'), interval %d hour)
and datetime < timestamp_add(timestamp_sub(timestamp('%s'), interval %d hour), INTERVAL 1 day)
and appIdGroup in (%s)
and ifnull(playerId, vid) <> 0
qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
)
select appIdGroup, concat('%s',' ~ ','%s') 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
order by appIdGroup
""",
dataset, yyyymmdd_1, timezone_offset, yyyymmdd_2, timezone_offset, appIdGroup_1, yyyymmdd_1, yyyymmdd_2);
EXECUTE IMMEDIATE query;
Stickiness¶
- The ratio of average daily DAU to average AU during the search period.
- This page contains a comprehensive explanation of adhesion.
-
After inputting the five user input parameters corresponding to the set, the query can be executed to verify the numbers. _ User input parameters _ yyyymmdd*1 : Search start date (YYYY-MM-DD)
- yyyymmdd*2 : Search last date (YYYY-MM-DD)
- timezone*offset : Time zone setting (time offset based on UTC – 9 for KST)
- comapny*index : Company number
- Bigquery > explorer at the top left > dataset (analytics*company number_live) under fluted-airline-109810
- appIdGroup*1: App Center registered GameID
- When entering two or more,
set appidGroup_1 = '"game1","game2"';
_ Query result _ appIdGroup : App Center registered GameID _ yyyymmdd_period : Search period _ avg*dau : Daily average DAU - au : Number of active users _ stickiness : stickiness _ ( avg*dau / au ) * 100
-
Sample query
declare yyyymmdd_1 string ;
declare yyyymmdd_2 string ;
declare timezone_offset int64;
declare appIdGroup_1 string;
declare company_index int64;
declare dataset string default '';
declare query string default '';
set yyyymmdd_1 = '2023-07-01';
set yyyymmdd_2 = '2023-07-31';
set timezone_offset = 9 ;
set company_index = 5 ;
set appIdGroup_1 = '"com.sample.samplegametest"' ;
set dataset = 'analytics_' || company_index || '_live';
set query = format("""
with login_log as (
select appIdGroup, datetime, substr(cast(timestamp_add(dateTime, interval %d hour) as string), 1,10) as yyyymmdd, ifnull(playerId, vid) as playerId
from `fluted-airline-109810.%s.t_hive_login_log`
where datetime >= timestamp_sub(timestamp('%s'), interval %d hour)
and datetime < timestamp_add(timestamp_sub(timestamp('%s'), interval %d hour), INTERVAL 1 day)
and appIdGroup in (%s)
and ifnull(playerId, vid) <> 0
qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
)
select appIdGroup, concat('%s', ' ~ ', '%s') as 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, au, avg(dau) as avg_dau
from
(
select appIdGroup, yyyymmdd, count(distinct playerId) over(partition by appIdGroup, yyyymmdd ) as dau,count(distinct playerId) over(partition by appIdGroup) as au
from login_log
)
group by appIdGroup, au
)
""",
timezone_offset, dataset, yyyymmdd_1, timezone_offset, yyyymmdd_2, timezone_offset, appIdGroup_1, yyyymmdd_1, yyyymmdd_2);
EXECUTE IMMEDIATE query;