Adobe Analytics

Adobe Analytics: BigQueryでパーティシペーション指標を集計するSQL

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

今回はBigQueryでAdobeAnalyticsのパーティシペーション指標を集計するSQLについてです。

Adobe Analyticsにおけるパーティシペーション(貢献度)指標とは、コンバージョンに至るまでに通過したページや、途中で発生したeVar値の履歴に対して、貢献度を割り当てるための指標です。

これで関節効果・アシスト効果が見えてくる訳です。

コンバージョンをアシストした貢献数(パーティシペーション)

では、AdobeAnalyticsのDatafeedをBigQueryに入れて分析する際に、パーティシペーションはどうやって集計すれば良いのか?

例えば各ページ別にpurchaseイベント(Datafeed上のEventID:1)のパーティシペーションを出すなら、こう書きます。

WITH t1 AS (
  SELECT
    CONCAT(post_visid_high, post_visid_low, CAST(visit_num AS string)) AS session,
    hit_time_gmt,
    post_pagename AS pagename,
    SIGN(SUM(CASE WHEN CONCAT(',', post_event_list, ',') LIKE '%,1,%' THEN 1 ELSE 0 END) 
         OVER(PARTITION BY CONCAT(post_visid_high, post_visid_low, CAST(visit_num AS string))
              ORDER BY hit_time_gmt DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS cvflag
  FROM `exture.hit_data`
  WHERE
    DATE(hit_time_gmt, 'Asia/Tokyo') >= '2019-09-01'
    AND DATE(hit_time_gmt, 'Asia/Tokyo') < '2019-10-01'
    AND post_pagename IS NOT NULL
    AND exclude_hit = 0
    AND duplicate_purchase = 0
),
    
t2 AS (
  SELECT
    session,
    hit_time_gmt,
    pagename,
    ROW_NUMBER() OVER(PARTITION BY session, pagename ORDER BY hit_time_gmt) AS rownum,
    cvflag
  FROM t1
  WHERE cvflag = 1 
)

SELECT
  pagename,
  SUM(cvflag) AS participations
FROM t2
WHERE
  rownum = 1
  AND pagename NOT LIKE '%thanks%'
  AND pagename NOT LIKE '%contact%'
GROUP BY 1
ORDER BY 2 DESC

結果はこうなりました。

BigQueryでパーティシペーションを算出

Adobe Workspacesの数値と比べます。完全に一致です。

Adobe Workspacesのパーティシペーション数と一致

解説

まず最初のCTE(WITH句) t1では、同一セッションにおいて各ページを通過したあとにコンバージョンが発生したかどうかをチェックしてます。
post_visid_high, post_visid_low, visit_num の3つを組み合わせてセッションIDを作ってます。
→ もし訪問者レベルのパーティシペーションを分析したい場合はpost_visid_highとpost_visid_lowだけ使えばOKです。

次のCTE t2では、セッション内で同じページを複数回通過した場合に重複を除去するために「セッション+ページ別」にROW_NUMBER関数で番号を振ってます。
この番号が1のページだけを使えば同じページをセッション内に何度通過しても「1」だけカウントします。

そして最後のクエリで、ページ別にパーティシペーション数を出すのですが、フォーム近辺のページは当然のように数値が大きくなるので除外します。

以上で、AdobeのUIを使わなくてもパーティシペーション分析がBigQueryで出来るようになりました。
BigQueryが大好きなMartech系データエンジニアはこれで益々仕事が楽しくなります。(当社比)

なお、注意点がありまして、BigQueryでeVar変数のパーティシペーション分析をする場合は、post列ではなくpre列で行うと良いです。
→ eVar配分設定が「元の値」になってる場合にpost列だと最初の値しか入らないので、パーティシペーション分析出来ない。

 

今回はBigQueryを使ってAdobeAnalyticsのパーティシペーション指標を再現するためのSQLについて紹介しました。
これを応用すれば、AdobeAnalyticsではなくてGoogleAnalytis360 / Firebase / GA App+WebプロパティのBQスキーマに対してもパーティシペーション分析が出来ますね。

弊社では、Google Cloud認定データエンジニア資格とAdobe認定エキスパート資格を保持した技術者達によるデータ分析基盤構築業務を承っております。
お問い合わせはこちらからどうぞ

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Adobe Analytics

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

    こんにちは。インターン生の市川です。今回はイベント前の時間を説明いた…

  2. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い① セグメントのコンテナの違い…

    こんにちは。CEOの原田です。今回は良く話題に出ながらも情報がまと…

  3. Adobe Analytics

    Adobe Analytics: ランドスケイプの「企業ログ解析データベースAPI」と連携する

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

  4. Cloud Dataproc

    BigQueryテーブルをAVRO形式でエクスポートしてHiveで扱う

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

  5. Adobe Analytics

    Adobe AEP SDKをTypeScriptで開発したReactNativeアプリに実装する

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

  6. Adobe Analytics

    購入とは-Adobe Analyticsの指標説明

    今回は購入について説明します。「購入」の下には「購入回数」「購入点数…

最近の記事

  1. LangChainのソースコードから実装を見てみる(Chat…
  2. Tableau×Teams連携
  3. AIを使ったマーケティングゲームを作ってみた
  4. Snowflakeや最新データ基盤が広義のマーケティングにも…
  5. 回帰分析はかく語りき Part3 ロジスティック回帰
  1. Google Analytics

    Google Analytics: ユニバーサルアナリティクスの拡張Eコマース用…
  2. Adobe Dynamic Tag Manager

    Adobe DTMからAdobe Launchに一発アップグレード
  3. ブログ

    競合調査
  4. ブログ

    ダッシュボードに使うべき10のグラフ③
  5. ChatGPT

    LangChainって何?: 次世代AIアプリケーション構築 その3
PAGE TOP