在 bigQuery 中计算广告浏览转化率

广告查看转化率¶

• 广告观看转化率是指每个阶段的转化率，即广告加载 – 开始观看 – 奖励完成。通常，转化率越接近100%表示广告设计良好。
• 您可以通过 Hive Adkit 收集三个广告观看事件的时间。
  • 广告加载 (load): 当请求广告时，或请求完成时。
  • 开始观看 (open): 当广告在设备上显示时。
  • 奖励完成 (reward): 当用户完成观看奖励广告并获得奖励时。

打开率¶

• 打开率衡量请求的广告中被奖励广告和插页广告转换为显示的百分比。它有助于确定广告是否有效地传递给用户。
  • 打开率 = (开始查看次数 / 广告加载次数) *100
• 如果某个特定细分市场的指标下降，您可以检查广告是否按预期设计。
  • 如果在应用启动时预请求广告，广告加载次数可能会增加。
  • 如果广告查看次数有限制，用户可能无法开始查看广告。

奖励率¶

• 奖励率是指在观看奖励广告的用户中，观看完整广告的用户所占的百分比。它有助于评估用户对广告组件的参与度。
  • 奖励率 = (完成奖励计数 / 开始观看计数) *100
• 如果在特定细分市场中未达到目标值，可能是由于广告观看时间过长或内容不当，导致用户在观看广告时流失。

在 BigQuery 中计算广告查看转化率¶

• 使用BigQuery分析广告观看漏斗的指标，识别用户可能流失的地方。您可以通过将查询结果与电子表格链接来可视化指标。
  • 在广告模块的初始实施过程中，您可以观察指标的趋势并为每个细分设置目标值。
  • 通过检查指标的近期趋势，您可以确定预期目标是否得到了适当的实现。
  • 如果您在广告规划中引入新的广告位置或调整奖励物品数量，您可以比较变更前后的指标。

输入 BigQuery 查询¶

• 将下面的示例查询复制并粘贴到查询编辑器中。手动输入与该集合对应的五个参数，然后点击顶部的“运行”按钮（或按 Ctrl + Enter）。

  • yyyymmdd_1 : 查询的开始日期
  • yyyymmdd_2 : 查询的结束日期
  • timezone_offset : 设置时区（9表示韩国标准时间）
  • appidGroup_1 : 应用中心游戏ID
    • 对于多个输入，设置 appidGroup_1 = ' "game1","game2" ';
  • company_index : 公司编号
    • 您可以在 BigQuery > Explorer 左上角 > 数据集 (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 : 完成奖励次数

与电子表格的链接¶

• 在查询结果窗口右上角的“探索数据”菜单中，点击“使用电子表格进行探索”。
• 您可以通过图表功能可视化指标。