在 bigQuery 中计算广告查看转化率

廣告觀看轉換率¶

• 廣告觀看轉換率是指每個階段的轉換率，廣告加載 – 開始觀看 – 獎勵完成。通常，轉換率越接近100%表示廣告設計良好。
• 您可以通過 Hive Adkit 收集三個廣告觀看事件的時間。
  • 廣告加載 (load)：當廣告被請求或請求完成時。
  • 開始觀看 (open)：當廣告在設備上顯示時。
  • 獎勵完成 (reward)：當用戶完成觀看獎勵廣告並獲得獎勵時。

開啟率¶

• 開放率衡量被獎勵廣告和插播廣告要求顯示的廣告百分比。它有助於確定廣告是否有效地傳遞給用戶。
  • 開放率 = (開始觀看次數 / 廣告加載次數) *100
• 如果某個特定區段的指標下降，您可以檢查廣告是否按預期設計。
  • 如果在應用啟動時預先請求廣告，廣告加載次數可能會增加。
  • 如果廣告觀看次數有限制，用戶可能無法開始觀看廣告。

獎勵率¶

• 獎勵率是指在獎勵廣告中，觀看完整廣告的用戶百分比，這些用戶是從開始觀看廣告的用戶中計算得出的。它有助於評估用戶對廣告組件的參與度。
  • 獎勵率 = (完成獎勵數 / 開始觀看數) *100
• 如果在特定區段未達到目標值，可能是由於廣告觀看時間過長或內容不當，導致用戶在觀看廣告時流失。

在 BigQuery 中計算廣告查看轉換率¶

• 使用 BigQuery 來分析廣告觀看漏斗的指標，以識別用戶可能流失的地方。您可以通過將查詢結果與電子表格鏈接來可視化指標。
  • 在廣告模塊的初始實施階段，您可以觀察指標的趨勢並為每個細分設置目標值。
  • 通過檢查指標的近期趨勢，您可以確定是否適當地達成了預期目標。
  • 如果您在廣告規劃中引入新的廣告位置或調整獎勵項目的數量，您可以比較變更前後的指標。

輸入 BigQuery 查詢¶

• 將下面的範例查詢複製並粘貼到查詢編輯器中。手動輸入對應於該集合的五個參數，然後單擊頂部的“運行”按鈕（或按 Ctrl + Enter）。

  • yyyymmdd_1 : 查詢的開始日期
  • yyyymmdd_2 : 查詢的最後日期
  • timezone_offset : 設定時區 (9 代表 KST)
  • appidGroup_1 : App Center 遊戲 ID
    • 對於多個輸入，設定 appidGroup_1 = ' "game1","game2" ';
  • company_index : 公司編號
    • 您可以在 BigQuery > Explorer 的左上角 > dataset (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 : 完成獎勵次數

與電子表格的連結¶

• 在查詢結果窗口右上角的探索數據菜單中，點擊使用電子表格進行探索。
• 您可以通過圖表功能可視化指標。