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. Adobe Analytics

    AA + GA : SafariのITP2.1に備えてphpでクッキーを永続化する

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

  2. Adobe Analytics

    GTMのdataLayerをAdobeAnalyticsの「s」オブジェクトにコピーする

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

  3. Adobe Analytics

    訪問回数 とは-Adobe Analyticsの指標説明

    これから、この場をお借りして私が学んだことを書いていきます。よろしく…

  4. Adobe Analytics

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

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

  5. Adobe Analytics

    Adobe Analytics 3つの主要指標

    こんにちは!インターンの藤本です。今日はAdobe Analytic…

  6. Adobe Analytics

    ページでの滞在時間とは-Adobe Analyticsの指標説明

    今回はページでの滞在時間について説明いたします。ページでの滞在時間と…

最近の記事

  1. GCSへのSnowflake Open Catalogによる…
  2. VPC Service Controlsで「NO_MATCH…
  3. モダンデータスタックなワークフローオーケストレーションツール…
  4. Streamlit in Snowflakeによるダッシュボ…
  5. Streamlit in SnowflakeによるStrea…
  1. Adobe Analytics

    イベント前の時間とは-Adobe Analyticsの指標説明
  2. Linux

    Linux技術者資格のLPIC-3試験に合格しました
  3. Adobe Analytics

    オリジナルの参照ドメインとは-Adobe Analyticsの指標説明
  4. Mouseflow

    Mouseflowで問題解決② -事例ベースで機能紹介-
  5. Google Cloud Platform

    Google Compute Engine のLinuxVMにVNC接続する
PAGE TOP