Adobe Analytics

Adobe Analytics: BigQuery+Lookerでアトリビューション分析

こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。
今回はAdobeAnalyticsのデータをBigQueryとLookerを使ってアトリビューション分析をする方法についてです。

Adobe分析ワークスペースでは、計算指標を使ってファーストタッチ・ラストタッチ・パーティシペーションというアトリビューションモデルを使った指標を作成できます。
しかし、Adobe分析ワークスペースは一度に最大400行までのデータしか出力できない控えめ仕様。

というわけで、BigQueryを使って一気にデータを抽出します。
そして、これまた私の中で一番アツいBIツール「Looker」を使って可視化します。

例として「訪問をまたいだキャンペーン流入(s.campaign)において、どのキャンペーンでコンバージョンしたのか」を分析しましょう。

前提条件:

・すでにBigQueryにAdobeのhit_dataがロードされている
・Lookerにはhit_dataテーブルがViewとして作成されている
・訪問セッションをまたいだ訪問者レベル(Cross-Visit)でのファーストタッチ・ラストタッチ・パーティシペーションを算出する
・以下のカスタム変数で計測をしている
  s.campaign … トラッキングコード 配分:最後の値 / 有効期限:訪問
  event2 … 資料請求完了

1.LookerのSQLRunnerを使ってクエリ結果を確認

まずはhit_dataから「トラッキングコード」と「資料請求完了数」のファーストタッチ・ラストタッチ・パーティシペーションでのコンバージョンデータを抽出します。
LookerをDevelopment modeにして、「SQL Runner」を開きます。

そして、訪問者ID、タイムスタンプ、トラッキングコードID、ファーストタッチCV、ラストタッチCV、パーティシペーションを抽出するためのSQLを実行します。
実際に使ったSQLはGithubに載せておきます。

なお、SQLについてちょっとだけ補足します。
まずはWITH区でCTEを作ってますが、ここではコンバージョンが発生した訪問者IDと、コンバージョンが発生するよりも前にクリックしたトラッキングコードを抽出してます。
そのCTEに対して、FIRST_VALUE関数でファーストタッチを取得し、LAST_VALUE関数でラストタッチを取得して、最後にLAG関数で過去のヒットに遡って重複しないトラッキングコードならばパーティシペーションとして取得してます。

SQLを実行すると、結果を確認できます。

SQL Runnerで実行結果を確認

そして、このクエリを元に派生テーブルを作成するためのLookMLも自動生成されるので、これを保存しておきます。

LookMLが自動生成される

2. LookerにDerived Table(派生テーブル)を作る

続いて、上記1で作成したLookMLを使ってDerived Tableを作成します。
BigQuery上にビューを作るようなイメージです。

LookMLプロジェクトを開き、「Add…」> 「Create View」を開き、1.で生成したLookMLを貼り付けます。
Derived Tableと言っても結局はViewな訳ですが、derived_tableというブロックが使われています。
これによって、派生テーブルが出来上がります。

View名はデフォルトのsql_runner_queryではなく、適当な名前に、例えばmydata_viewにします。

SQLで抽出された列はデフォルトではただのディメンションなので、ファーストタッチ/ラストタッチ/パーティシペーションのカラムを指標として使えるように「measure」ブロックも作成します。
SUM関数で各列の数字を合算した指標です。

指標を定義

派生テーブルを作ったらExplore機能でアクセス出来るように、Modelに追加しておきます。

派生テーブルをモデルに追加

3. Explore機能を使って可視化する

上記2.で作成したViewをExploreして行きます。

まずはトラッキングコードとファーストタッチCV数のLookを作成します。
左側のナビゲーションメニューから、「Cmpid」と「First Touch Count」を選択してLookを作成します。

Exploreでディメンションと指標を選んでパイチャートを作成

デフォルトでは500行まで抽出するのですが、パイチャートで作りたかったので10行に制限しました。
→ 10行ならAdobeの分析ワークスペースでいいじゃん、というツッコミはスルーw

上位5件のトラッキングコードのCV数の日別推移も見たいので、「Date」と「First Touch Count」を選び、「Cmpid」をピボットに指定して、上位5件のものに絞り込んで積み上げ棒グラフにします。

Stacked Columnタイプのグラフ

同様の手順でラストタッチとパーティシペーションのLookも作成したら、あとはダッシュボードに並べるだけです。

Lookを並べてダッシュボード作成

まとめ

今回はAdobeAnalyticsのコンバージョンデータをLookerの派生テーブルを使って分析する手順について説明しました。
Lookerはここで作成したLookMLをGitで管理するので、データエンジニア同士で共通のデータ定義をもって分析を進められるのが利点です。
つまり、ダッシュボードを利用する人が全員同じデータ定義を共有出来ます。

弊社ではLookerを始め、各BIツールを使ったダッシュボード構築業務を行っております。
Adobe認定エキスパート資格とGoogle認定データエンジニア資格を保持する弊社だからこそ実践可能なベストプラクティスに興味がございましたら、ぜひこちらよりお問合わせ下さい。

関連記事

  1. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードす…

    こんにちは、エクスチュアCTOの権です。前回のブログの続きです…

  2. Adobe Analytics

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

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

  3. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

    こんにちは、エクスチュアCTOの権です。今回は、AdobeAn…

  4. Adobe Analytics

    Adobe Analytics: データフィードをBigQueryで集計する

    こんにちは、エクスチュアCTOの権です。前回のブログで、Ado…

  5. Adobe Analytics

    Looker: エンジニアがBIで分析ダッシュボードを作る

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

  6. Adobe Analytics

    Adobe Analytics: RSIDを間違えてしまったデータを正しいRSに入れ直す

    こんにちは、エクスチュアCTOの権です。今回はAdobe An…

最近の記事

  1. AdobeAnalyticsでReactNativeアプリを…
  2. Adobe Analytics: BigQuery+Look…
  3. Looker: エンジニアがBIで分析ダッシュボードを作る
  4. Adobe Analytics: DWHレポートをAWSのS…
  5. Adobe Analytics: レガシーs_code.js…
  1. Adobe Analytics

    AdobeAnalytics:WordPressやHTML5内の動画を計測する
  2. Google BigQuery

    GCP: 今月のGCP課金額をslackに自動的に書き込む
  3. Mouseflow

    Mouseflow:検索の仕様
  4. Adobe Analytics

    Adobe Analytics:セグメントの落とし穴:滞在時間がおかしくなる
  5. Google BigQuery

    Tableau : BigQueryでLOD計算が使えない場合の対処法
PAGE TOP