GA 360 Suite

Google Analytics 360: BigQueryを使ってアトリビューション分析で91日以上のルックバック期間を実現

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

今回は、GoogleAnalytics360とBigQueryを使ってアトリビューション分析における91日以上のルックバック期間を実現する方法についてです。

Google Analyticsのアトリビューション分析では、最大で90日間のルックバック期間を設定出来ます。
デフォルトは30日ですね。

アトリビューション分析のルックバック期間

アトリビューション分析を使えば、レポート期間中にコンバージョンしたユーザーがコンバージョンに至るまでにどのようなエンゲージをしたか分析出来ます。

しかし、ユーザーがCVするまでにもっと時間がかかる様なロングテールな商材を扱ってるサイトだともっと長いルックバック期間を設定したい事があります。

こんな時はGA360+BigQueryです。
SQLでササっとデータを抽出できます。

例. コンバージョンまでの日数

では例として「コンバージョンまでの日数」を下記の条件で抽出します。

  • レポート期間: 2017/07/01 から 2017/07/31 までの1ヶ月
  • ルックバック期間: 180日
  • コンバージョン: トランザクションIDが存在するヒットをCVとみなす

書いたSQLはこちら↓
BigQueryパブリックデータセットのbigquery-public-data.google_analytics_sample.ga_sessions を使ってます。

  -- ベースになるデータを取得 7ヶ月分 = 180日 + 1ヶ月
WITH
  base AS (
  SELECT
    fullVisitorId,
    TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)) AS hit_timestamp,
    DATE(TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)), 'Asia/Tokyo') AS hit_timedate,
    hits.TRANSACTION.transactionId AS hit_transactionId,
    hits.isInteraction as hit_isInteraction,
    totals.visits,
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
    UNNEST(hits) AS hits
  WHERE
    _TABLE_SUFFIX BETWEEN "20170101" AND "20170731" )
  -- 先月CVしたユーザーのIDと日付
  ,user_cv_flag_1 AS (
  SELECT
    fullVisitorId,
    ROW_NUMBER() OVER (PARTITION BY fullVisitorId ORDER BY hit_timestamp) AS num,
    FIRST_VALUE(hit_timedate) OVER (PARTITION BY fullVisitorId ORDER BY hit_timestamp) AS date
  FROM
    base
  WHERE
    hit_transactionId IS NOT NULL
    AND hit_timedate BETWEEN '2017-07-01' AND '2017-07-31'
  )
  -- 複数行返ってくるので一番最初の行だけに絞る
  ,user_cv_flag AS (
    SELECT fullVisitorId,
    DATE
    FROM user_cv_flag_1
    WHERE num = 1
  )
  -- CVしたユーザーの最初の訪問日 ルックバック180日
  ,user_session_1 AS (
  SELECT
    base.fullVisitorId AS vid,
    ROW_NUMBER() OVER (PARTITION BY base.fullVisitorId ORDER BY base.hit_timestamp) AS num,
    FIRST_VALUE(base.hit_timedate) OVER(PARTITION BY base.fullVisitorId ORDER BY base.hit_timestamp) AS base_date,
    user_cv_flag.date AS cv_date,
    DATE_DIFF(user_cv_flag.date, base.hit_timedate, day) AS lookback_day
  FROM
    base
  INNER JOIN
    user_cv_flag
  ON
    base.fullVisitorId = user_cv_flag.fullVisitorId
  WHERE
    DATE_DIFF(user_cv_flag.date, base.hit_timedate, day) BETWEEN 0 AND 180
    AND base.visits = 1
    AND base.hit_isInteraction = true
  )
  -- 複数行返ってくるので一番最初の行だけに絞る
  ,user_session AS (
  SELECT
    vid,
    base_date,
    cv_date,
    lookback_day
  FROM
    user_session_1
  WHERE
    num = 1
  )
  -- 最後にまとめる
SELECT
  lookback_day,
  COUNT(1) AS cv,
FROM
  user_session
GROUP BY 1
ORDER BY 1

簡単ですね。
先月CVしたユーザーのIDと、そのユーザーが過去180日間の間で一番最初にやって来た日とCVした日までの経過日数を取り出して最後に日数別に集計してます。

結果はこうなります。

ルックバック180日による「CVまでの日数」

ちょっと長ったらしい書き方なので、「サブクエリ使えよ」とか「WITH句使いすぎワロス」と思った方はご自身でキレイなクエリを書きましょう。

というわけで、GA360ならBigQueryを使ってUI上の制限に影響されないデータを抽出する事が出来ます。

TL;DR

GA360とBigQueryを連携すればGAの機能よりも長いルックバック期間を設定した分析が可能になります。
というかルックバック無視してBigQueryにデータが入ってる全期間の分析が出来ます。

弊社ではGoogleAnalytics/AdobeAnalyticsなどの各Martechツールの導入実装コンサルティングサービスや、GCP/AWSなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。
お問い合わせはこちらからどうぞ

関連記事

  1. Adobe Analytics

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

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

  2. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

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

  3. Adobe Analytics

    Adobe Analytics: BigQuery+Lookerでアトリビューション分析

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

  4. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードす…

    ※2019年9月4日追記この記事は情報が古いので、新しい記事を書き…

  5. Cloud Dataproc

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

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

  6. Google BigQuery

    GCPのBQMLを使ってKaggleコンペに挑んでみた(その1)

    こんにちは。エクスチュアでインターンをさせて頂いている中野智基です。…

最近の記事

  1. KARTE:最低限!KARTEの運用管理のためにやっておくこ…
  2. YOTTAA:新機能「Anomaly AI」のご紹介
  3. Tableau 2021.1 新機能紹介
  4. YOTTAA:ECサイトで見るべき8つのサイトパフォーマンス…
  5. 【初心者向け】AWSを学ぶ前に確認したい用語
  1. Adobe Experience Cloud

    Adobe Summit 2020レポート: Building for a Co…
  2. Cloud Dataproc

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

    Adobe Audience Manager: REST APIを使ってTrai…
  4. Databricks

    ScalaでDatabricksのDataFrameを扱う
  5. Mouseflow

    Mouseflow: MouseflowタグをWebサイトに導入する
PAGE TOP