Adobe Analytics

Adobe AnaltyicsとGoogle Analytics の「生Webビーコン」をBigQueryに保存してLookerで分析 (その2)

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

前回の続きです。

今回は、BigQueryにストリーミングインサートされてるAdobe AnalyticsとGoogle AnalyticsのWebビーコンログを抽出して、Lookerでグラフ化します。

1. BigQueryでAA/GAログを抽出する

ビーコンのログそのものは見づらいので、加工必須です。
あと、ビーコンにはセッションの情報がありません。
という訳で、各ヒット間のタイムスタンプが30分以上開いていたら、新しいセッションとしてカウントします。
そのうえで、PV、セッション数、UU数をビーコンログからカウントします。

AAビーコンの場合

使ってるs_codeのバージョンに依存するのですが、弊社はAppMeasurement1.7+VisitorAPIなので、midというパラメータに訪問者IDが入ります。
それを訪問者IDとして使います。
また、URLにブログタイトルが入る事があるので、それを見やすくするために2回デコードします。
あとはページビューだけ集計したいので、&pevパラメータ(クリックイベント)も除外します。

CREATE TEMPORARY FUNCTION decodeTwice(str String) RETURNS STRING LANGUAGE js AS """
  var ret = '';
  if (str) { try { ret = decodeURIComponent(decodeURIComponent(str)); } catch(e) { ret = str; } }
  return ret;
""";

WITH base AS (
  SELECT hit_time,  DATE(hit_time, 'Asia/Tokyo') AS date,  REGEXP_EXTRACT(log, r"&mid=(\d+)&") AS vid,
    decodeTwice(REGEXP_EXTRACT(log, r"&g=([^&]+)")) AS page_url
  FROM  `beacon.hit_data`
  WHERE REGEXP_CONTAINS(log, r"&mid=(\d+)&")
    AND NOT REGEXP_CONTAINS(log, r"&pev")
    AND log_type = 'aa' ),

history AS (
  SELECT hit_time, LAG(hit_time, 1) OVER (PARTITION BY vid ORDER BY hit_time) AS last_hit,
    date, vid, page_url
  FROM base),

aalog AS (
SELECT *, CASE WHEN TIMESTAMP_DIFF(hit_time, last_hit, second) > 1800 THEN 1
      WHEN last_hit IS NULL THEN 1 ELSE 0 END AS session_start
  FROM history )
  
SELECT hit_time, date, vid,
  SUM(session_start) OVER (PARTITION BY vid ORDER BY hit_time) AS session_num, page_url
FROM aalog

GAビーコンの場合

これもAAと同じ要領で、パースします。
GAの場合はcidというパラメータに訪問者IDが入ります。
また、URLにブログタイトルが入る事があるので、それを見やすくするために2回デコードします。
あとはページビューだけ集計したいので、&t=pageviewの行だけ抽出します。

CREATE TEMPORARY FUNCTION decodeTwice(str String) RETURNS STRING LANGUAGE js AS """
  var ret = '';
  if (str) { try { ret = decodeURIComponent(decodeURIComponent(str)); } catch(e) { ret = str; } }
  return ret;
""";

WITH base AS (
  SELECT hit_time,  DATE(hit_time, 'Asia/Tokyo') AS date,  REGEXP_EXTRACT(log, r"&cid=(\d+\.\d+)&") AS vid,
    decodeTwice(REGEXP_EXTRACT(log, r"&dl=([^&]+)")) AS page_url
  FROM  `beacon.hit_data`
  WHERE REGEXP_CONTAINS(log, r"&cid=(\d+\.\d+)&")
    AND REGEXP_CONTAINS(log, r"&t=pageview")
    AND log_type = 'ga' ),

history AS (
  SELECT hit_time, LAG(hit_time, 1) OVER (PARTITION BY vid ORDER BY hit_time) AS last_hit,
    date, vid, page_url
  FROM base),

galog AS (
SELECT *, CASE WHEN TIMESTAMP_DIFF(hit_time, last_hit, second) > 1800 THEN 1
      WHEN last_hit IS NULL THEN 1 ELSE 0 END AS session_start
  FROM history )
  
SELECT hit_time, date, vid,
  SUM(session_start) OVER (PARTITION BY vid ORDER BY hit_time) AS session_num, page_url
FROM galog

SQLを実行すると、このような結果になります。

AAの場合

AAビーコンの結果

session_numの列に各訪問者ID別に訪問回数が入ってます。

GAの場合

GAビーコンの結果

こちらも同様にsession_numに訪問回数が入ります。
あとはこのデータを使って、COUNTでページビュー数、vid+session_numのCOUNT DISTINCTでセッション数、vid単体のCOUNT DISTINCTでユーザー数が出せるようになります。

2. Lookerでログデータをグラフ化する

ここからLookerの出番です。
上記のSQLを使って、Derived Table(派生テーブル)を作ります。

LookerのDerived TableでBigQueryのUDFを使う時の注意点

SQL Runnerの実行結果からDerived Table用のLookMLを簡単に作る事が出来るのですが、UDFが入ったままのDerived Tableを作るとエラーが発生します。

Syntax error: Expected “(” or keyword SELECT or keyword WITH but got keyword CREATE

回避策はここらへんに書いてありますが、UDFだけ別のexploreセクションに分けてあげて、Derived Tableのビューからはextends句で参照するとエラーを回避出来ます。

custom_functionをextendsで参照

dimension定義でUDFを使う

ビューのLookMLはこうなりました。

view: aa_log {
  derived_table: {
    sql:
      WITH
        base AS (
        SELECT
          hit_time,
          REGEXP_EXTRACT(log, r"&mid=(\d+)&") AS vid,
          REGEXP_EXTRACT(log, r"&g=([^&]+)") AS page_url
        FROM
          `beacon.hit_data`
        WHERE
          REGEXP_CONTAINS(log, r"&mid=\d+&")
          AND NOT REGEXP_CONTAINS(log, r"&pev")
          AND log_type = 'aa' ),
        history AS (
        SELECT
          hit_time,
          LAG(hit_time, 1) OVER (PARTITION BY vid ORDER BY hit_time) AS last_hit,
          vid,
          page_url
        FROM
          base),
        aalog AS (
        SELECT
          *,
          CASE
            WHEN TIMESTAMP_DIFF(hit_time, last_hit, second) > 1800 THEN 1
            WHEN last_hit IS NULL THEN 1
            ELSE 0
          END AS session_start
        FROM
          history )
      SELECT
        hit_time,
        vid,
        SUM(session_start) OVER (PARTITION BY vid ORDER BY hit_time) AS session_num,
        page_url
      FROM
        aalog
       ;;
  }

  measure: pageview {
    type: count
    drill_fields: [detail*]
  }

  measure: uu {
    type: count_distinct
    sql:  ${vid} ;;
    drill_fields: [detail*]
  }

  measure: session {
    type: count_distinct
    sql:  concat(${vid}, safe_cast(${session_num} as string)) ;;
    drill_fields: [detail*]
  }

  dimension_group: hit_time {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.hit_time ;;
  }

  dimension: vid {
    type: string
    sql: ${TABLE}.vid ;;
  }

  dimension: session_num {
    type: number
    sql: ${TABLE}.session_num ;;
  }

  dimension: page_url {
    type: string
    sql: decodeTwice(${TABLE}.page_url) ;;
  }

  set: detail {
    fields: [hit_time_time, vid, session_num, page_url]
  }
}

これだけクリアすれば、あとは普通のLookerの使い方です。

Exploreでデータを抽出して、任意のVisulizationを選んでグラフ化します。

Exploreでデータ抽出

日別のページビュー・セッション数・ユーザー数と、ページ別の数字をササっとグラフ化して、ダッシュボードに並べるだけでログデータの可視化が出来ました。

ログデータからダッシュボードを作成

今回はAdobeAnalyticsとGoogleAnalyticsのビーコンデータを横取りして、それをダッシュボード化しましたが、ワザワザAA/GAを使わなくても自分でビーコン計測タグを作れば、あとはGCPとLookerを使って即席Web解析ツールの出来上がり!という訳です。

弊社では各種マーテックツールの導入支援と、Google Cloud Platformを使ったデータ分析基盤構築を主に行っております。
お問い合わせはこちらからどうぞ

関連記事

  1. Adobe Analytics

    Adobe Analyticsと「どこどこJP」のAPIを連携する

    こんにちは、エクスチュアCTOの権です。弊社Webサイトでは「どこ…

  2. Google Analytics

    Google Analytics StandardのデータをBigQueryで分析するための力技

    こんにちは、エクスチュアの権です。先月「Google Clou…

  3. Cloud Dataproc

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

    こんにちは、エクスチュアCTOの権です。今日も普段あまり使わな…

  4. Firebase Analytics

    Firebase Analyticsの新しいBigQueryスキーマを試す

    こんにちは、エクチュアの権泳東(コン・ヨンドン)です。「お名前なん…

  5. Adobe Analytics

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

    こんにちは、エクスチュアCTOの権です。前回のブログで、Big…

  6. GA 360 Suite

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

    こんにちは、エクスチュアCTOの権です。今回はGA360のBi…

最近の記事

  1. データアナリスト/データエンジニア向けNext19まとめ
  2. BigQuery BI Engine解説
  3. GoogleNext 2019レポート:3日目
  4. Adobe Summit 2019 レポート
  5. GoogleNext 2019レポート:2日目
  1. Adobe Analytics

    Adobe Analytics:ワークスペース:セグメント比較機能の紹介
  2. Adobe Analytics

    Adobe AnaltyicsとGoogle Analytics の「生Webビ…
  3. Tableau

    Tableau:累計ユニークカウント数を時系列グラフで表現する
  4. Mouseflow

    Mouseflow: MouseflowタグをWebサイトに導入する
  5. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント…
PAGE TOP