Adobe Analytics

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

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

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

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

  1. eVarでUser-Agentを計測する
  2. eVar x デバイスタイプ x デバイス名のデータをADWで抽出する
  3. 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認定エキスパート資格を保持した技術者達によるデータ分析基盤構築業務を承っております。
お問い合わせはこちらからどうぞ

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Adobe Analytics

    Adobe Analytics:IF関数を使用した計算指標

    Adobe Analyticsで1年以上前についた計算指標に関数が使え…

  2. Adobe Analytics

    Metabase: カスタムマップで日本地図を追加する

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

  3. Adobe Analytics

    Adobe Analytics: BigQueryでパーティシペーション指標を集計するSQL

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

  4. Adobe Analytics

    検索エンジン-自然とは-Adobe Analyticsの指標説明

    こんにちは。インターン生の市川です。今回は検索エンジン-自然について…

  5. Adobe Analytics

    Adobe Analytics 原理①

    近年、Web解析の必要性が広く認知されるようになり多くのWebサイト…

  6. Google BigQuery

    Treasure DataからBigQueryにデータを移動させる方法

    こんにちは、エクスチュアの酒井です。今回は、TreasureDat…

最近の記事

  1. モダンデータスタックなワークフローオーケストレーションツール…
  2. Streamlit in Snowflakeによるダッシュボ…
  3. Streamlit in SnowflakeによるStrea…
  4. Streamlitを使った簡単なデータアプリケーション作成ガ…
  5. 生成AI機能を活かしたデータカタログ製品「Secoda」を試…
  1. Google Cloud Platform

    【GCP】Cloud Workflowsでデータパイプラインの構築を試してみた②…
  2. Adobe Analytics

    Adobe Analytics: カスタムリンク計測を1行で書く
  3. Adobe Analytics

    再来訪頻度とは-Adobe Analyticsの指標説明
  4. Tableau

    Tableau Tips〜INDEX関数〜
  5. IT用語集

    【完全版】Tableau(タブロー)って何?
PAGE TOP