빅쿼리에서 지표 조회해보기

빅쿼리에서 지표 조회해보기¶

기본적인 빅쿼리 사용 가이드는 여기를 확인하세요.
각 지표에 대응되는 샘플 쿼리에 사용자 입력 파라미터를 입력한 후, 쿼리를 실행하면 지표의 수치를 확인할 수 있습니다.
쿼리 결과를 스프레드시트와 연동하면, 차트 기능을 통해 지표를 시각화할 수 있습니다.

누적 AU, NU, NU 비율¶

조회 기간 누적 AU, NU, AU 대비 NU 비율입니다.
set에 대응되는 다섯 개의 사용자 입력 파라미터 입력한 후, 쿼리를 실행하면 수치를 확인할 수 있습니다.
- 사용자 입력 파라미터
  - 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: 앱센터 등록 GameID
    - 두 개 이상 입력 시, set appIdGroup_1 = '"game1","game2"';
- 쿼리 결과
  - appIdGroup : 앱센터 등록 GameID
  - yyyymmdd_period : 조회 기간
  - au : 활성 유저 수
  - nu : 신규 유저 수
  - nu_rate : AU 대비 NU 비율
    - ( 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;

고착도(Stickiness)¶

조회 기간 AU 대비 일평균 DAU 비율입니다.
고착도(Stickiness)에 대한 자세한 설명은 여기서 확인할 수 있습니다.
set에 대응되는 다섯 개의 사용자 입력 파라미터 입력한 후, 쿼리를 실행하면 수치를 확인할 수 있습니다.
- 사용자 입력 파라미터
  - 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 : 앱센터 등록 GameID
    - 두 개 이상 입력 시, set appIdGroup_1 = '"game1","game2"';
쿼리 결과
- appIdGroup : 앱센터 등록 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;