inFablic | Fablic, inc. Developer's Blog.

フリマアプリ フリル (FRIL) を運営する Fablic の公式開発者ブログです。Fablic のデザイナー・エンジニア・ディレクターが情報発信していきます。

全国放送のCMで都道府県ごとのオーガニックインストール数を集計する(Firebase + Adjust + Redash)

こんにちは。shobyです。

フリルでは定期的に全国でCMを放送しており、各CMの効果計測をする必要が生じています。

今回は、より正確にCMの効果を分析するために、全国放送のCMで都道府県ごとのオーガニックインストール数を集計する方法をお伝えします。

概要

  • Firebaseを使ったインストール数集計の問題点
  • 広告IDによるFirebaseと広告効果計測サービスのデータとの突き合わせ
  • 都道府県ごとのオーガニックインストール数の活用

Firebaseを使ったインストール数集計の問題点

以前、FirebaseとBigQueryを使って、全国放送のCMで都道府県ごとのCPIを推定する方法をお伝えしましたが、この方法では広告経由のインストール数も含むCPIになってしまうという問題がありました。

in.fablic.co.jp

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 アドレスに基づく、イベントのレポート元の市区町村。

BigQuery Export のスキーマより一部抜粋

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 region
FROM
  your_table_name
WHERE
  user_dim.geo_info.country = 'Japan'
  AND event_dim.name = 'first_open'
GROUP BY advertisement_id
ORDER 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

adjust placeholder list

これにより、広告IDと、それに対応するインストール元が取得できるようになります。 オーガニックに相当するtrackerで絞り込むことにより、CM期間中にオーガニックインストールをしたユーザーの広告IDを取得することができます。

FirebaseのデータとAdjustのデータを突き合わせる

広告IDをベースに、FirebaseのデータとAdjustのデータを突き合わせることで、都道府県別のオーガニックインストール数を取得できます。

FirebaseのデータとAdjustのデータを突き合わせるには、Redash上でPython DataSourceを用いました。

Python DataSourceを用いることにより、Redash上で異なる複数のクエリ結果を組み合わせた集計をすることができます。

今回は、pandasを用いたデータ集計を行いました。

import pandas as pd

firebase_result = pd.DataFrame(get_query_result(1)['rows']) # 1: firebaseのクエリID
adjust_result = pd.DataFrame(get_query_result(2)['rows']) # 2: adjustのクエリID

# 共通するadvertisement_idで結果をmerge
merged_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分析に活用できるようになりました。

参考

この記事を書くにあたり、以下の記事を参考にさせていただきました。ありがとうございました。

Redashで複数データソースの結果をpandasを使って可視化する

*1:コールバックAPIはGETで叩かれる仕様です。

*2:country_subdivisionなどの位置情報に相当する部分は、2017/7/25現在、日本では動いていないようです。そのためFirebaseなどでインストール元地域を集計する必要がありました。