Adobe Analytics: Datafeedにデバイスデータがないけど慌てない

こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。

Adobe Datafeedはヒットデータを丸ごとdumpしてくれるのですが、なんとデバイスのデータが含まれてません。
Adobe AnalyticsのデバイスレポートはDevice Atlas社のOEMデータのため含める事が出来ないのです。

しかし、Adobe DataWarehouse(以下ADW)にはデバイスタイプもデバイス名も入ってます。
ということは、これらのデバイスデータとDatafeedを掛け合わせれば良いのです。

eVarでUser-Agentを計測する
eVar x デバイスタイプ x デバイス名のデータをADWで抽出する
BigQueryにDatafeedとADWのデータを両方入れて結合する

では一つずつ詳細を説明します。

eVarでUser-Agentを計測する

DatafeedにはUser-Agentが含まれてますが、AdobeAnalyticsのワークスペースやADWでは生のUser-Agentのレポートはデフォルトで存在しません。
そのため、eVarにUser-Agentを入れておけば、そのeVarをキーにしてDatafeedとADWのデータを結合する事が出来ます。

eVarにUser-Agentをセットする方法は複数ありますが、「処理ルール」を使えばWebもアプリも両方対応出来るのでオススメです。

「処理ルール」でeVar10にUser−Agentをセット

eVar x デバイスタイプ x デバイス名のデータをADWで抽出する

eVarにUser-Agentを格納したら、ADWで「前日のデータ」をSFTPで毎日配信するようにします。

使用するディメンションは UAを入れたeVar・デバイスタイプ・デバイス名の3つだけです。

ADWでeVar・デバイス名・デバイスタイプを配信

ADWをSFTPで配信する方法は以前の記事に書いてありますのでそちらをご覧ください。

また、配信するファイル名には日付を入れるようにしましょう。
ファイル名に「%R」を入れると、その部分にレポートの日付がYYYYMMDDで入ります。

BigQueryにDatafeedとADWのデータを両方入れて結合する

ADWのデバイスデータがSFTPでCompute Engineに配信されたら、BigQueryにロードしましょう。

BigQueryにdeviceテーブルを作成

user_agent, device_type, device_name の3つのカラムを持つdeviceテーブルを作成しました。
このテーブルに毎日ADWから配信されるデータをロードします。

私はこんなスクリプトで毎日ロードしてます。

#!/bin/bash

set -eu
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin

project=my-project
bucket=my-bucket
dataset=exture

cd ${HOME}/workspace

#ADWファイル名は前日の日付をYYYYMMDDが入るようにする。
TDATE=$(date &quot;+%Y%m%d&quot; -d &quot;1 day ago&quot;)
FILE=${HOME}/adw/device_${TDATE}.zip

cp ${FILE} . 
unzip device_${TDATE}.zip

bq load --source_format=CSV --skip_leading_rows=1 --max_bad_records=10 ${project}:${dataset}.device device_${TDATE}.csv

rm -f device_${TDATE}.zip
rm -f device_${TDATE}.csv

#ADWの元ファイルをGCSにバックアップ
gsutil mv ${FILE} gs://${bucket}/bkup/

#distinctの結果で重複除去
bq query --replace=true --use_legacy_sql=false --destination_table=${project}:${dataset}.device &quot;SELECT distinct * FROM exture.device&quot;

exit 0

何も考えずにbq loadコマンドでアップロードしていくとappendされて重複データがどんどん増えてしまうため、bq loadしたあとにselect distinctした結果で上書きしてます。

実際にクエリを実行する

ではデバイスタイプ別のデータを抽出して数値を確認します。

デバイスタイプ別に、ページビュー数・訪問回数・訪問者数を出します。

SELECT
  d.device_type AS device_type,
  COUNT(1) AS pv,
  COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, CAST(visit_num AS string))) AS visits,
  COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low)) AS visitors
FROM
  exture.hit_data AS h
LEFT JOIN
  exture.device AS d
ON
  h.post_evar10 = d.user_agent
WHERE
  DATE(h.hit_time_gmt, 'Asia/Tokyo') &gt;= '2019-10-31'
  AND DATE(h.hit_time_gmt, 'Asia/Tokyo') &lt; '2019-11-01'
  AND h.exclude_hit = 0
  AND post_page_event = 0
GROUP BY 1
ORDER BY 2 DESC

結果はこうなりました。