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. Google BigQuery

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

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

  2. Adobe Analytics

    AdobeAnalytics: GTMのdataLayerをAdobeAnalyticsの処理ルール…

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

  3. Adobe Analytics

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

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

  4. Google BigQuery

    BigQuery BI Engine解説

    こんにちは、エクスチュア渡部です。2019/4/9-4/11に行わ…

  5. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで…

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

  6. Adobe Analytics

    【実験してみた】CNAMEを使わずにAdobeAnalyticsを1stパーティクッキーで計測する

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

最近の記事

  1. KARTE「オフラインデータをオンライン接客に活用する」
  2. ELB (ALB・NLB・CLB) をサクッと学ぶ
  3. Direct Connect vs Site to Site…
  4. パブリックサブネット vs プライベートサブネット
  5. セキュリティグループ vs ネットワークACL
  1. Google Cloud Platform

    Google Compute EngineのUbuntu VMにスワップ領域を作…
  2. KARTE

    KARTE Blocks データプランナーの強い味方!!
  3. Adobe Analytics

    Adobe Summit 2020レポート: Customer Journey …
  4. Segment

    Segmentでデータ管理を一元化する
  5. Salesforce

    Zapier(ザピエル)経由でSansanで取り込んだ名刺をSalesforce…
PAGE TOP