BigQueryでの広告ビューコンバージョン率の計算

広告ビューコンバージョン率¶

• 広告ビューコンバージョン率は、各ステージでのコンバージョン率を指します。広告の読み込み – 視聴開始 – 報酬の完了です。一般的に、コンバージョン率が100%に近いほど、よく設計された広告を示します。
• Hive Adkitを通じて、3つの広告視聴イベントのタイミングを収集できます。
  • 広告の読み込み (load): 広告がリクエストされたとき、またはリクエストが完了したとき。
  • 視聴開始 (open): 広告がデバイスに表示されたとき。
  • 報酬の完了 (reward): ユーザーが報酬付き広告の視聴を完了し、報酬を受け取ったとき。

開封率¶

• オープンレートは、報酬広告とインタースティシャル広告によってリクエストされた広告の表示割合を測定します。これは、広告がユーザーに効果的に配信されているかどうかを判断するのに役立ちます。
  • オープンレート = (開始されたビュー数 / 広告ロード数) *100
• 特定のセグメントで指標が減少した場合、広告が意図した通りに設計されているかを確認できます。
  • アプリが起動したときに広告が事前にリクエストされると、広告ロード数が増加する可能性があります。
  • 広告ビューの数に制限がある場合、ユーザーは広告の視聴を開始できないかもしれません。

報酬率¶

• 報酬率は、報酬付き広告で広告の視聴を開始したユーザーの中で、広告全体を視聴したユーザーの割合です。これは、広告コンポーネントに対するユーザーのエンゲージメントを評価するのに役立ちます。
  • 報酬率 = (完了した報酬数 / 開始した視聴数) *100
• 特定のセグメントで目標値が達成されない場合、広告視聴時間が長すぎるか、不適切なコンテンツが原因で、広告視聴中にユーザーが離脱している可能性があります。

BigQueryで広告ビューコンバージョン率を計算する¶

• BigQueryを使用して、広告視聴ファunnelの指標を分析し、ユーザーがどこで離脱しているかを特定します。クエリ結果をスプレッドシートとリンクさせることで、指標を視覚化できます。
  • 広告モジュールの初期実装中に、指標の傾向を観察し、各セグメントの目標値を設定できます。
  • 指標の最近の傾向を調べることで、意図した目標が適切に達成されているかどうかを判断できます。
  • 新しい広告配置を導入したり、広告計画で報酬アイテムの数量を調整したりする場合は、変更前後の指標を比較できます。

BigQueryクエリを入力¶

• 下記のサンプルクエリをクエリエディタにコピー＆ペーストしてください。セットに対応する5つのパラメータを手動で入力し、次に上部の「実行」ボタンをクリックするか（またはCtrl + Enterを押してください）。

  • yyyymmdd_1 : クエリの開始日
  • yyyymmdd_2 : クエリの最終日
  • timezone_offset : タイムゾーンを設定します（KSTの場合は9）
  • appidGroup_1 : アプリセンターゲームID
    • 複数の入力の場合、appidGroup_1 = ' "game1","game2" ' に設定します。
  • company_index : 会社番号
    • これは、BigQuery > エクスプローラーの左上 > データセット（analytics_company number_live）でfluted-airline-109810の下にあります。
  • サンプルクエリ

    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;
    

BigQueryクエリ結果を確認する¶

• クエリ結果ウィンドウの下部にある「結果を表示」オプションをクリックして*
  • クエリ結果を確認します。
  • ppidGroup : アプリセンターゲームID
  • yyyymmdd: 問い合わせ日
  • open_rate : 開封率
  • reward_rate : 報酬率
  • load_cnt : 広告ロード数
  • open_cnt : 開始ビュー数
  • reward_cnt : 完了した報酬数

スプレッドシートとのリンク¶

• クエリ結果ウィンドウの右上にあるデータを探索メニューで、「スプレッドシートで探索」をクリックします。
• チャート機能を通じて指標を視覚化できます。