LookerStudio用BigQueryサンプル ページの価値算出、購入者の動線を算出

以下、コード内の

`ga40000.analytics_00000.events_*`

部分をBigQueryのデータセット IDに置き換え

ページの価値算出_eコマース用

WITH
  page AS (
  SELECT
    ga_session_id,
    page_title,
    page_path
  FROM (
    SELECT
      (
      SELECT
        value.int_value
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'ga_session_id') AS ga_session_id,
      (
      SELECT
        value.string_value
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'page_title') AS page_title,
      REGEXP_EXTRACT((
        SELECT
          value.string_value
        FROM
          UNNEST(event_params)
        WHERE
          KEY = 'page_location'), r'^(?:https?:\/\/)?[^\/]+(\/[^?]*)') AS page_path
    FROM
    　--ここと、64目を別のテーブルに替えれば流用可能です
      `ga40000.analytics_00000.events_*`
    WHERE
      -- 指定された日付範囲内のデータを選択
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    ORDER BY
      1 )
  WHERE
    page_title NOT IN ('Shopping Cart',
      'Checkout Your Information',
      'Payment Method',
      'Checkout Confirmation',
      'Checkout Review')
  GROUP BY
    ga_session_id,
    page_title,
    page_path ),
  revenue AS (
  SELECT
    ga_session_id,
    SUM(revenue) AS revenue
  FROM (
    SELECT
      (
      SELECT
        value.int_value
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'ga_session_id') AS ga_session_id,
      ecommerce.purchase_revenue AS revenue
    FROM
       --ここと、32を別のテーブルに替えれば流用可能です
    `ga40000.analytics_00000.events_*`
    WHERE
      -- 指定された日付範囲内のデータを選択
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  )
  GROUP BY
    ga_session_id )
SELECT
  *,
  revenue / unique_sessions_per_page AS page_value
FROM (
  SELECT
    page_title,
    page_path,
    COUNT(DISTINCT ga_session_id) AS unique_sessions_per_page,
    SUM(revenue) AS revenue
  FROM (
    SELECT
      p.ga_session_id,
      p.page_title,
      p.page_path,
      COALESCE(r.revenue, 0) AS revenue
    FROM
      page AS p
    LEFT JOIN
      revenue AS r
    ON
      p.ga_session_id = r.ga_session_id )
  GROUP BY
    page_title,
    page_path )
ORDER BY
  unique_sessions_per_page DESC;

ページの価値算出_リード獲得用

WITH
  page AS (
  SELECT
    ga_session_id,
    page_title,
    page_path
  FROM (
    SELECT
      (
      SELECT
        value.int_value 
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'ga_session_id') AS ga_session_id,
      (
      SELECT
        value.string_value
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'page_title') AS page_title,
      REGEXP_EXTRACT((
        SELECT
          value.string_value
        FROM
          UNNEST(event_params)
        WHERE
          KEY = 'page_location'), r'^(?:https?:\/\/)?[^\/]+(\/[^?]*)') AS page_path
    FROM
   `ga40000.analytics_00000.events_*`
    WHERE
      -- 指定された日付範囲内のデータを選択
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
    ORDER BY
      1 )
  WHERE
    page_title NOT IN ('Shopping Cart',
      'Checkout Your Information',
      'Payment Method',
      'Checkout Confirmation',
      'Checkout Review')
  GROUP BY
    ga_session_id,
    page_title,
    page_path ),
  revenue AS (
  SELECT
    ga_session_id,
    SUM(revenue) AS revenue
  FROM (
    SELECT
      (
      SELECT
        value.int_value
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'ga_session_id') AS ga_session_id,

      (
      SELECT
        value.int_value
      FROM
        UNNEST(event_params)
      WHERE
        KEY = 'value') AS revenue

    FROM
   `ga40000.analytics_00000.events_*`
    WHERE
      -- 指定された日付範囲内のデータを選択
      _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
)
  GROUP BY
    ga_session_id )
SELECT
  *,
  revenue / unique_sessions_per_page AS page_value
FROM (
  SELECT
    page_title,
    page_path,
    COUNT(DISTINCT ga_session_id) AS unique_sessions_per_page,
    SUM(revenue) AS revenue
  FROM (
    SELECT
      p.ga_session_id,
      p.page_title,
      p.page_path,
      COALESCE(r.revenue, 0) AS revenue
    FROM
      page AS p
    LEFT JOIN
      revenue AS r
    ON
      p.ga_session_id = r.ga_session_id )
  GROUP BY
    page_title,
    page_path )
ORDER BY
  unique_sessions_per_page DESC;

購入者の動線を出力する_eコマース

WITH PurchaseEvents AS (
  SELECT 
    user_pseudo_id,
    MAX(event_timestamp) AS purchase_time
  FROM 
    `ga40000.analytics_00000.events_*`
  WHERE 
    event_name = 'purchase' AND
    -- 指定された日付範囲内のデータを選択
  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE

  GROUP BY 
    user_pseudo_id
)

SELECT 
  e.event_name,
  e.user_pseudo_id,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(e.event_timestamp), SECOND), "Asia/Tokyo") AS event_time,
  (
    SELECT 
      ep.value.string_value
    FROM 
      UNNEST(e.event_params) AS ep
    WHERE 
      ep.key = 'page_location'
  ) AS page_location,
  (
    SELECT 
      ep.value.string_value
    FROM 
      UNNEST(e.event_params) AS ep
    WHERE 
      ep.key = 'page_title'
  ) AS page_title,
  (
    SELECT 
      ep.value.string_value
    FROM 
      UNNEST(e.event_params) AS ep
    WHERE 
      ep.key = 'transaction_id'
  ) AS transaction_id,
  (
    SELECT 
      ep.value.string_value
    FROM 
      UNNEST(e.event_params) AS ep
    WHERE 
      ep.key = 'source'
  ) AS source,
  (
    SELECT 
      ep.value.string_value
    FROM 
      UNNEST(e.event_params) AS ep
    WHERE 
      ep.key = 'medium'
  ) AS medium,
  ARRAY(
    SELECT 
      item.item_name
    FROM 
      UNNEST(e.items) AS item
  ) AS item_names
FROM 
    `ga40000.analytics_00000.events_*` AS e
JOIN 
  PurchaseEvents AS p
ON 
  e.user_pseudo_id = p.user_pseudo_id
WHERE 
  e.event_name IN ('page_view', 'purchase')
  AND e.event_timestamp <= p.purchase_time　AND
  -- 指定された日付範囲内のデータを選択
  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE

ORDER BY 
  e.user_pseudo_id, event_time DESC;

Tweet