BigQueryでの指標の取得

BigQueryで指標を取得する¶

基本的なBigQueryの使用ガイドはこちらで確認できます。
ユーザー入力パラメータを入力し、各指標に対応するサンプルクエリを実行した後、指標の数値を確認できます。
クエリ結果をスプレッドシートに接続することで、チャート機能を使用して指標を視覚化できます。

累積AU、NU、NU比¶

検索期間中の累積AU、NU、およびNUからAUの比率。
セットに対応する5つのユーザー入力パラメータを入力した後、クエリを実行して数値を確認できます。
- ユーザー入力パラメータ
  - yyyymmdd_1 : 検索開始日 (YYYY-MM-DD)
  - yyyymmdd_2 : 検索最終日 (YYYY-MM-DD)
  - timezone_offset : タイムゾーン設定 (UTCに基づく時間オフセット – KSTの場合は-9)
  - comapny_index : 会社番号
    - Bigquery > 左上のエクスプローラー > fluted-airline-109810の下のデータセット (analytics_company number_live)
  - appIdGroup_1: App Centerに登録されたGameID
    - 2つ以上入力する場合、set appidGroup_1 = '"game1","game2"';
- クエリ結果
  - appIdGroup : App Centerに登録されたGameID
  - yyyymmdd_period : 検索期間
  - au : アクティブユーザー数
  - nu : 新規ユーザー数
  - nu_rate : NUからAUの比率
    - ( nu / au ) * 100
- サンプル

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 &gt;= timestamp_sub(timestamp('%s'), interval %d hour)
and datetime &lt; timestamp_add(timestamp_sub(timestamp('%s'), interval %d hour), INTERVAL 1 day)
and appIdGroup in (%s)
and ifnull(playerId, vid) &lt;&gt; 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;

粘着性¶

検索期間中の平均日次DAUと平均AUの比率。
このページには、粘着性に関する包括的な説明が含まれています。
設定に対応する5つのユーザー入力パラメータを入力した後、数値を確認するためにクエリを実行できます。 _ ユーザー入力パラメータ _ yyyymmdd*1 : 検索開始日 (YYYY-MM-DD)
- yyyymmdd*2 : 検索終了日 (YYYY-MM-DD)
- timezone*offset : タイムゾーン設定 (UTCに基づく時間オフセット – KSTの場合は-9)
- comapny*index : 会社番号
- Bigquery > 左上のエクスプローラー > fluted-airline-109810の下にあるデータセット (analytics*会社番号_live)
- appIdGroup*1: App Centerに登録されたGameID
- 2つ以上を入力する場合、set appidGroup_1 = '"game1","game2"'; _ クエリ結果 _ appIdGroup : App Centerに登録されたGameID _ yyyymmdd_period : 検索期間 _ avg*dau : 日次平均DAU
- au : アクティブユーザー数 _ stickiness : 粘着性 _ ( avg*dau / au ) * 100
サンプルクエリ

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 &gt;= timestamp_sub(timestamp('%s'), interval %d hour)
and datetime &lt; timestamp_add(timestamp_sub(timestamp('%s'), interval %d hour), INTERVAL 1 day)
and appIdGroup in (%s)
and ifnull(playerId, vid) &lt;&gt; 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;