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. 最速で理解したい人のためのIT用語集

関連記事

  1. GA 360 Suite

    BigQuery: Google Analytics 360のネストされたデータをフラット変換するS…

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

  2. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント設定を作成・更新する…

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

  3. Adobe Analytics

    Adobe Analytics Business Practitionerが変わりました。

    はじめにAdobe Analyticsの試験であるBusiness …

  4. Adobe Analytics

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

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

  5. Adobe Analytics

    AA(Adobe Analytics)を導入しよう!

    はじめにこんにちは!インターンの與那覇(ヨナハ)です今回はAA(A…

  6. Google Analytics

    Google Analytics4 を知る。

    Google Analyticsを知る。 -エクスチュアのツール紹介-…

最近の記事

  1. Pythonを用いたAmazon S3の署名付きURLの発行…
  2. Snowflakeとは?Data Cloud World T…
  3. SnowflakeのData Clean Roomを基礎から…
  4. SnowflakeのData Clean Roomを基礎から…
  5. SnowflakeのData Clean Roomを基礎から…
  1. Data Clean Room

    SnowflakeのData Clean Roomを基礎から一番詳しく解説(2回…
  2. Adobe Analytics

    はじめてのAdobe Analytics実装②
  3. IT用語集

    オプティマイズ(Optimize)って何?
  4. Python

    わかりやすいPyTorch入門④(CNN:畳み込みニューラルネットワーク)
  5. Mouseflow

    mouseflow vs Microsoft Clarity
PAGE TOP