こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。
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 "+%Y%m%d" -d "1 day ago")
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 "SELECT distinct * FROM exture.device"
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') >= '2019-10-31' AND DATE(h.hit_time_gmt, 'Asia/Tokyo') < '2019-11-01' AND h.exclude_hit = 0 AND post_page_event = 0 GROUP BY 1 ORDER BY 2 DESC
結果はこうなりました。
クエリ結果
Adobe Workspacesのデバイスタイプレポートの数値と比較しましょう。完全に一致します。
Adobe Workspacesの数値と完全一致
これでAdobeAnalyticsのデバイスデータをBigQueryに連携出来るようになったので、各BIツールでデバイス別にさらなる分析が出来ますね。
弊社では、Google Cloud認定データエンジニア資格とAdobe認定エキスパート資格を保持した技術者達によるデータ分析基盤構築業務を承っております。
お問い合わせはこちらからどうぞ。
