빅쿼리에서 광고 시청 전환율 구해보기

광고 시청 전환율

• 광고 시청 전환율은 광고 로드 – 시청 시작 – 보상 완료까지 각 단계별 전환율을 의미하며, 일반적으로 전환율이 100%에 가까울수록 광고가 적절히 설계된 것으로 해석할 수 있습니다.
• Hive Adkit을 통해 세 가지 광고 시청 이벤트 시점을 수집할 수 있습니다.
  • 광고 로드(load) : 광고가 요청되거나 요청이 완료된 시점
  • 시청 시작(open) : 기기에 광고가 노출되는 시점
  • 보상 완료(reward) : 보상형 광고 시청을 완료하고 보상을 지급받는 시점

• 오픈율

  • 오픈율은 보상형 광고와 전면 광고에서 요청된 광고 중 노출로 전환된 비율로, 광고가 유저에게 잘 제공되었는지 확인할 수 있습니다.
    • 오픈율 = ( 시청 시작 수 / 광고 로드 수 ) * 100
  • 해당 구간에서 지표가 감소한 경우, 광고가 의도에 맞게 설계되었는지 확인할 수 있습니다.
    • 앱 실행 시 광고가 미리 요청된다면, 광고 로드 수가 증가할 수 있습니다. 
    • 광고 시청 횟수 제한이 있다면, 유저가 광고 시청을 시작하지 못할 수 있습니다.

• 보상률

  • 보상률은 보상형 광고에서 광고를 보기 시작한 유저 중 광고를 끝까지 시청한 유저 비율로, 광고 구성 요소에 대한 유저 반응을 확인할 수 있습니다.
    • 보상률 = ( 보상 완료 수 / 시청 시작 수 ) * 100
  • 해당 구간에서 목표한 수치를 달성하지 못하였다면, 광고 시청 시간이 길거나 콘텐츠가 유저에게 적합하지 않은 이유로 광고 시청 중 이탈하는 유저가 발생할 수 있습니다.

빅쿼리에서 광고 시청 전환율 구해보기

•  빅쿼리를 통해 광고 시청 퍼널 지표의 각 수치를 확인하고, 어느 구간에서 유저가 이탈하는지 있는지 확인해 보세요. 쿼리 결과를 스프레드시트와 연동한다면 지표를 시각화할 수 있습니다.
  • 광고 모듈 도입 초기의 경우, 지표의 추세를 확인하고 각 구간의 목표 수치를 설정할 수 있습니다.
  • 최근 지표의 추세를 확인하여 목표한 수치를 적절하게 달성하고 있는지 확인할 수 있습니다.
  • 새로운 광고 구좌를 추가하거나 보상 아이템 수량을 조정하는 등의 광고 기획을 변경한 경우, 변경 전/후 기간의 지표를 비교해 볼 수 있습니다.

• 빅쿼리 쿼리 입력

  • 쿼리 편집기에 아래 샘플 쿼리를 붙여 넣고, set에 대응되는 5개의 파라미터를 수동으로 입력한 후 상단의 실행 버튼(혹은 Ctrl + Enter)을 클릭합니다.
    • yyyymmdd_1 : 조회 시작 날짜
    • yyyymmdd_2 : 조회 마지막 날짜
    • timezone_offset : 타임존 설정(KST의 경우 9)
    • appidGroup_1: 앱센터 GameId
      • 두 개 이상 입력 시, set appidGroup_1 = ' "game1","game2" ';
    • company_index : 회사 번호
      • 빅쿼리 > 왼쪽 상단의 탐색기 > fluted-airline-109810 하위의 데이터셋(analytics_회사번호_live)에서 확인 가능
  • 샘플 쿼리
    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-07';
set timezone_offset = 9 ;
set company_index = 5 ;
set appidGroup_1 = '"com.sample.samplegametest"' ;
set dataset = 'analytics_' || company_index || '_live';

set query = format("""
select appidGroup,yyyymmdd,
case when open_rate > 100 then 100 else open_rate end as open_rate,
case when reward_rate > 100 then 100 else reward_rate end as reward_rate,
load_cnt,
case when open_cnt > load_cnt then load_cnt else open_cnt end as open_cnt,
case when reward_cnt > open_cnt then open_cnt else reward_cnt end as reward_cnt
from
(
select appidGroup,yyyymmdd,
ifnull(round(((safe_divide(count(ad_open), count(ad_load))) * 100),2),0) as open_rate,
ifnull(round(((safe_divide(count(ad_reward), count(ad_open))) * 100),2),0) as reward_rate,
count(ad_load) as load_cnt,
count(ad_open) as open_cnt,
count(ad_reward) as reward_cnt
from
(
select appidGroup,yyyymmdd,
case when eventType ='load' then checksum end as ad_load,
case when eventType='open' then checksum end as ad_open,
case when eventType='reward' then checksum end as ad_reward
from
(
select appidGroup, checksum, eventType,
substr(cast(timestamp_add(dateTime, interval %d hour) as string), 1,10) as yyyymmdd
from fluted-airline-109810.%s.t_hive_ad_watch_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 eventType in ('load','open','reward')
qualify row_number() over(PARTITION BY checksum ORDER BY bigqueryRegistTimestamp) =1 )
)
group by appidGroup,yyyymmdd
)
order by appidGroup,yyyymmdd
""",
timezone_offset, dataset, yyyymmdd_1, timezone_offset, yyyymmdd_2, timezone_offset,appidGroup_1);
EXECUTE IMMEDIATE query;

• 빅쿼리 쿼리 결과 확인

  • 쿼리 결과창 맨 하단 작업(프로시져)의 결과 보기를 클릭하면, 쿼리 결과를 확인할 수 있습니다.
    • appidGroup : 앱센터 Game Id
    • yyyymmdd : 조회일
    • open_rate : 오픈율
    • reward_rate : 보상률
    • load_cnt : 광고 로드 수
    • open_cnt : 시청 시작 수
    • reward_cnt : 보상 완료 수

• 스프레드시트와 연동해보기

  • 쿼리 결과창 오른쪽 상단의 데이터 탐색 메뉴에서 스프레드시트로 탐색을 클릭합니다.
  • 차트 기능을 통해 지표를 시각화할 수 있습니다.