こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。
今回は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の分析ワークスペースでいいじゃん、というツッコミはスルーｗ
上位5件のトラッキングコードのCV数の日別推移も見たいので、「Date」と「First Touch Count」を選び、「Cmpid」をピボットに指定して、上位5件のものに絞り込んで積み上げ棒グラフにします。
Stacked Columnタイプのグラフ
同様の手順でラストタッチとパーティシペーションのLookも作成したら、あとはダッシュボードに並べるだけです。
Lookを並べてダッシュボード作成
まとめ
今回はAdobeAnalyticsのコンバージョンデータをLookerの派生テーブルを使って分析する手順について説明しました。
Lookerはここで作成したLookMLをGitで管理するので、データエンジニア同士で共通のデータ定義をもって分析を進められるのが利点です。
つまり、ダッシュボードを利用する人が全員同じデータ定義を共有出来ます。
弊社ではLookerを始め、各BIツールを使ったダッシュボード構築業務を行っております。
Adobe認定エキスパート資格とGoogle認定データエンジニア資格を保持する弊社だからこそ実践可能なベストプラクティスに興味がございましたら、ぜひこちらよりお問合わせ下さい。
ブログへの記事リクエストはこちらまで
