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を選んでグラフ化します。