フリルでは定期的に全国でCMを放送しており、各CMの効果計測をする必要が生じています。
今回は、より正確にCMの効果を分析するために、全国放送のCMで都道府県ごとのオーガニックインストール数を集計する方法をお伝えします。
概要
- Firebaseを使ったインストール数集計の問題点
- 広告IDによるFirebaseと広告効果計測サービスのデータとの突き合わせ
- 都道府県ごとのオーガニックインストール数の活用
Firebaseを使ったインストール数集計の問題点
以前、FirebaseとBigQueryを使って、全国放送のCMで都道府県ごとのCPIを推定する方法をお伝えしましたが、この方法では広告経由のインストール数も含むCPIになってしまうという問題がありました。
CM放送期間中には、相乗効果を狙った別のマーケティング施策を行うことがあり、広告経由のインストールが含まれると正しいCPIが出なくなってしまいます。
そのため、今回はより正確な広告効果計測をするため、広告IDベースで広告効果計測サービスのデータと突き合わせ、都道府県別のオーガニックインストール数を集計する方法を考えました。
広告IDによるFirebaseと広告効果計測サービスのデータとの突き合わせ
Firebaseと広告効果計測サービスのデータを広告IDをベースに突き合わせることによって、都道府県別のオーガニックインストール数を集計することができます。
Firebaseにより当道府県別に広告IDをリストアップし、Adjustの生データと突き合わせる方法をご紹介します。
Firebaseで都道府県別に広告IDをリストアップする
Firebaseには、都道府県情報だけでなく、広告IDも自動計測されており、BigQueryを用いることで、都道府県別にインストールされたデバイスの広告IDをリストアップすることができます。
| フィールド名 | データ型 | 説明 |
|---|---|---|
| user_dim.device_info.resettable_device_id | STRING | 広告の ID または IDFA。 |
| user_dim.app_info.app_platform | STRING | アプリを実行しているプラットフォーム。 |
| user_dim.geo_info.continent | STRING | IP アドレスに基づく、イベントのレポート元の大陸。 |
| user_dim.geo_info.country | STRING | IP アドレスに基づく、イベントのレポート元の国。 |
| user_dim.geo_info.region | STRING | IP アドレスに基づく、イベントのレポート元の地域。 |
| user_dim.geo_info.city | STRING | IP アドレスに基づく、イベントのレポート元の市区町村。 |
user_dim.device_info.resettable_device_id には、AndroidであればAAID、iOSであればIDFAが含まれており、ユーザーが取得を許可していない場合には値がnullになります。
AndroidとiOSのどちらであるかは、 user_dim.app_info.app_platform で判定することができます。
CM期間中に初回起動したユーザーを新規インストールユーザーだと仮定すると、実際に都道府県別に広告IDをリストアップするクエリは以下のような形で書けます。
SELECT user_dim.device_info.resettable_device_id as advertisement_id, FIRST(user_dim.app_info.app_platform) as os_type, FIRST(user_dim.geo_info.region) AS regionFROM your_table_nameWHERE user_dim.geo_info.country = 'Japan' AND event_dim.name = 'first_open'GROUP BY advertisement_idORDER BY region ASC
これにより、CM期間中にインストールしたユーザーの広告IDを取得することができます。
Adjustの生データ取得
フリルでは、Adjustを利用して、広告効果を計測しています。
Adjustには自社データとの連携用のコールバック機能がついており、ほぼリアルタイムでインストール経路情報の生データを取得することができます。
具体的には、フリル側にAdjust用のコールバックAPIを用意し、そのAPIをAdjust側に設定することにより、Adjustでインストールのコンバージョンが計測できるたび、そのデータをコールバックAPIに渡してくれるようになります。
具体例な設定例は以下のような形です。*1
https://example.com/callback?tracker={tracker}&os_type={os_name}&idfa={idfa}&aaid={gps_adid}&installed_at={installed_at}Adjustではtrackerというユニークな識別子により、インストール元を特定することができます。
また、パラメータは以下のページにあるものを自由に設定できます。*2
これにより、広告IDと、それに対応するインストール元が取得できるようになります。オーガニックに相当するtrackerで絞り込むことにより、CM期間中にオーガニックインストールをしたユーザーの広告IDを取得することができます。
FirebaseのデータとAdjustのデータを突き合わせる
広告IDをベースに、FirebaseのデータとAdjustのデータを突き合わせることで、都道府県別のオーガニックインストール数を取得できます。
Python DataSourceを用いることにより、Redash上で異なる複数のクエリ結果を組み合わせた集計をすることができます。
import pandas as pdfirebase_result = pd.DataFrame(get_query_result(1)['rows']) # 1: firebaseのクエリIDadjust_result = pd.DataFrame(get_query_result(2)['rows']) # 2: adjustのクエリID# 共通するadvertisement_idで結果をmergemerged_result = pd.merge(firebase_result, adjust_result, on='advertisement_id')install_counts = merged_result[['region', 'advertisement_id']].groupby('region').count()result = {}for index, row in install_counts.iterrows(): add_result_row(result, {'region': index, 'count': row['advertisement_id']})add_result_column(result, 'region', '', 'string')add_result_column(result, 'count', '', 'integer')
このように、Redash上でPythonを使うことにより、異なるデータソースを突き合わせて、集計を行うことができます。
これにより、都道府県ごとのオーガニックインストール数を集計することができます。
都道府県ごとのオーガニックインストール数の活用
都道府県ごとのオーガニックインストール数を活用することにより、より精度の高いCM効果の分析を行うことができます。
フリルでは、都道府県ごとの推定CPIをより高い精度で出すことができるようになりました。また、今後この都道府県別のオーガニックインストール数のデータを元に、今後より詳細な分析をしていく予定です。
まとめ
フリルでは、より詳細なCMの効果計測を行うために、FirebaseとAdjustのデータを広告IDを元に突き合わせ、都道府県ごとのオーガニックインストール数を出すようになりました。
これにより、都道府県ごとの推定CPIをより高い精度で出すことができるようになり、今後の詳細なCM分析に活用できるようになりました。
参考
この記事を書くにあたり、以下の記事を参考にさせていただきました。ありがとうございました。