BigQuery について

この機能は、Google マーケティング プラットフォームに含まれるアナリティクス 360 でのみご利用いただけます。
Google マーケティング プラットフォームの詳細

この記事では、BigQuery にエクスポートするアナリティクス データのクエリを作成する方法の例を紹介します。この記事用のサンプル データセットもご用意いたしました。

この記事の内容:

クエリの最適化

クエリは実行するたびに料金が発生するため、月々のデータ処理用の予算に影響します。余分なフィールドを選択した場合、処理する必要のあるデータ量が増え、結果として、必要以上に月々の予算を使い果たしてしまいます。クエリを最適化することにより、月々のデータ処理用の予算を節約できます。

詳しくは、料金に関する記事をご覧ください。

必要なものだけを選択する

クエリを作成する際、SELECT ステートメントで関連するフィールドだけを選択します。余分なフィールドを呼び出さないようにすると、クエリを処理するのに必要なデータや時間を節約できます。

例: ワイルドカード演算子を使用しない

良くないフォーム: ワイルドカード演算子を使用する
SELECT *
FROM [表名];

 

良いフォーム: フィールド名を使用して不必要な処理を省く
SELECT field1, field2
FROM [表名];

キャッシュを許可する

できるだけ、関数をフィールドとして使用することは避けます。関数（NOW() や TODAY() など）は変数の結果を返すので、クエリがキャッシュされず、その結果、返されるのに時間がかかることになります。代わりに、特定の日時を使用することをおすすめします。

現在のところ、ワイルドカードを使用した複数のテーブルに対するクエリでは、キャッシュされた結果はサポートされていません（[キャッシュされた結果を使用] オプションがオンになっている場合も同様です）。同じワイルドカード クエリを複数回実行した場合は、クエリごとに課金されます。詳細

よく使用するサブクエリに中間表を使用する

特定のクエリをサブクエリとして繰り返し使用している場合、クエリ結果の上にある [Save as Table] をクリックして、そのクエリを中間表として保存できます。保存した中間表は、クエリの FROM セクションで参照でき、処理に必要なデータや時間を節約できます。

中間表を使用する
SELECT field1, field2
FROM [データセット名.表名];

クエリをデバッグする

構築したコードは BigQuery によってデバッグされます。作成ウィンドウで、デバックのステータスがクエリの下に示されます。API 経由の場合も dryRun フラグを使用することでデバッグ機能を使用できます。

有効なクエリは緑で示されます。クリックするとクエリで処理されるデータの量が表示されます。この機能を使用すると、クエリを実行する前にデータの最適化を検討できるので、不必要なデータ処理を避けられます。

Query Debugging - Success

 

無効なクエリは赤で示されます。クリックするとエラー情報が表示され、エラーが発生している行と列が示されます。下の例では、GROUP BY ステートメントが空欄になっており、エラーの場所が示されています。

Query Debugging - Error

 

おすすめの方法やお役立ち情報

サンプル データセットの使用

次の例では、Google アナリティクス サンプル データセットを使用しています。

独自のデータでクエリを使用するには、例の中のプロジェクト名とデータセット名を独自のプロジェクト名とデータセット名に置き換えてください。

標準 SQL とレガシー SQL の使用

BigQuery では 2 つの SQL 言語をサポートしています。

2 つの言語の違いについては、標準 SQL への移行で説明しています。

現在、BigQuery に格納されているデータをクエリするための推奨 SQL 言語は、標準 SQL となっています。

BigQuery UI、CLI、API、またはご使用のインターフェースで標準 SQL を有効にする方法について詳しくは、標準 SQL を有効にするを参照してください。

最も簡単な方法は、以下の例に示すように、標準 SQL クエリの先頭にコメント「standardSQL」を含める方法です。

レガシー SQL では、Google アナリティクス 360 データは毎日新しいテーブルに渡されます。一度に複数の表を対象にクエリを作成するには、次の例のように、表名をカンマで区切るか、TABLE_DATE_RANGE テーブル ワイルドカード関数を使うか、複数のカンマで区切った TABLE_DATE_RANGE 関数を使用します。

複数の表を対象にクエリを作成する

次の例は、同じデータに対する標準 SQL クエリとレガシー SQL クエリを示しています。

3 日間

標準 SQL
UNION ALL を使って 3 日間を指定する
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
レガシー SQL
カンマ区切りの表名を使って 3 日間を指定する
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

過去 1095 日間

標準 SQL
_TABLE_SUFFIX を使って過去 1095 日間を指定する
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
レガシー SQL
TABLE_DATE_RANGE を使って過去 1095 日間を指定する
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

過去 36 か月間

標準 SQL
_TABLE_SUFFIX を使って過去 36 か月間を指定する
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
レガシー SQL
TABLE_DATE_RANGE を使って過去 36 か月間を指定する
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

過去 3 年間

標準 SQL
_TABLE_SUFFIX を使って過去 3 年間を指定する
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
レガシー SQL
TABLE_DATE_RANGE を使って過去 3 年間を指定する
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

特定の期間

標準 SQL
_TABLE_SUFFIX を使って特定の期間を指定する
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
レガシー SQL
TABLE_DATE_RANGE を使って特定の期間を指定する
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

過去 3 年間と今日のデータ（当日）

標準 SQL
UNION ALL と _TABLE_SUFFIX を使って過去 3 年間と今日のデータ（当日）を指定する
注: このクエリの例は、現在当日表がないため、Google アナリティクスの一般公開データセットでは機能しません。
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

レガシー SQL

複数の TABLE_DATE_RANGE を使って過去 3 年間と今日のデータ（当日）を指定する
注: このクエリの例は、現在当日表がないため、Google アナリティクスの一般公開データセットでは機能しません。
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

基本的なクエリの例

ここでは、アナリティクス データの例に含まれる指標とディメンションを使って、基本的なクエリを作成する方法を説明します。

[ディメンション] あたりの合計 [指標]

「2017 年 7 月のデバイスのブラウザあたりのトランザクションの合計数はいくらですか？」の質問に対するスクリプトの例は次のとおりです。

標準 SQL

2017 年 7 月のデバイスのブラウザあたりのトランザクションの合計数
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

レガシー SQL

2017 年 7 月のデバイスのブラウザあたりのトランザクションの合計数
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

[ディメンション] あたりの平均直帰率

実際の直帰率は、「ページビューが 1 つだけの訪問の割合」と定義できます。「トラフィック ソースあたりの実質直帰率はいくらでしたか？」の質問に対するスクリプトの例は次のとおりです。

標準 SQL

2017 年 7 月のトラフィック ソースごとの直帰率
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

レガシー SQL

2017 年 7 月のトラフィック ソースごとの直帰率
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

購入ユーザーのタイプ別の平均商品ページビュー数（購入ユーザーと非購入ユーザー）

「2017 年 7 月に購入したユーザーの商品ページビューの平均数はいくらですか？」の質問に対するクエリの例は次のとおりです。

標準 SQL

2017 年 7 月に購入したユーザーの商品ページビューの平均数
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

レガシー SQL

2017 年 7 月に購入したユーザーの商品ページビューの平均数
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

「2017 年 7 月に購入しなかったユーザーの商品ページビューの平均数はいくらですか？」の質問に対するスクリプトの例は次のとおりです。

標準 SQL

2017 年 7 月に購入しなかったユーザーの商品ページビューの平均数
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

レガシー SQL

2017 年 7 月に購入しなかったユーザーの商品ページビューの平均数
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

購入ユーザーあたりの平均トランザクション数

「2017 年 7 月の購入ユーザーあたりのトランザクションの平均数はいくらですか？」の質問に対するスクリプトの例は次のとおりです。

標準 SQL

2017 年 7 月の購入ユーザーあたりのトランザクションの平均数
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

レガシー SQL

2017 年 7 月の購入ユーザーあたりのトランザクションの平均数
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

セッションあたりの平均消費金額

「2017 年 7 月のセッションあたりの平均消費金額は？」の質問に対するスクリプトの例は次のとおりです。

標準 SQL

2017 年 7 月のセッションあたりの平均消費金額
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

レガシー SQL

2017 年 7 月のセッションあたりの平均消費金額
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

ヒットの順序

「閲覧されたページの順序は？」の質問に対するスクリプトの例は次のとおりです。

標準 SQL

2017 年 7 月にユーザーが閲覧したページの順序
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

レガシー SQL

2017 年 7 月にユーザーが閲覧したページの順序
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

このクエリでは、ヒットの種類を PAGES に制限して、イベントやトランザクションの接点を取得しないようにします。出力の各行はページビューを表しており、SELECT ステートメントのフィールドの既定の順序で出力されます。

 

 

ヒットやセッション単位での複数のカスタム ディメンション

ヒット単位のカスタム ディメンション
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

セッション単位のカスタム ディメンション
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

各クエリでの処理:

SELECT ステートメントによって関係するディメンションと指標フィールドに対してクエリされます。

MAX 関数による処理:

  • カスタム ディメンションを新しい列として返します。この関数を繰り返し使用して複数のカスタム ディメンションを新しい列として返すことができます。
  • WITHIN hits と WITHIN RECORD によって、BigQuery で繰り返されているフィールド内の条件が評価されます。
  • MAX 内の条件は、各カスタム ディメンションに対して評価され、index=1（ヒット）や index=2（セッション）でないディメンションに対しては NULL を返します。
  • 最大値（ヒットに対してはカスタム ディメンション 1 の値、セッションに対してはカスタム ディメンション 2 の値）が返されますが、その他の値すべてに対しては NULL が返されます。

高度なクエリの例

クエリの基本的な使用方法を理解したら、BigQuery で使用できる高度な関数と機能を使ってクエリを作成できます。

商品 A を購入したユーザーによって購入された商品（従来の e コマース）

質問「商品 A を購入したユーザーによって購入された別の商品はなんですか？」のスケルトン スクリプトは次のとおりです。

商品 A を購入したユーザーによって購入された商品（従来の e コマース）
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘データセット名’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘データセット名’ ]
  WHERE hits.item.productName CONTAINS '商品アイテム名 A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != '商品アイテム名 A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. 1 行目でユーザーによって購入された他のすべての商品が選択され、集計関数 COUNT() を使って購入された他の商品ごとの数量が計算されます。その結果が quantity というラベルでフィールドに表示されます。また、その関連商品も other_purchased_products というラベルで商品フィールドに表示されます。
  2. 灰色部分のサブクエリで、トランザクションを実行し（totals.transactions>=1）、トランザクション中に商品 A を購入した（WHERE hits.item.productName CONTAINS '商品アイテム名 A'）ユニーク ユーザー（fullVisitorId)）のみが選択されます。

トップレベル（緑色）クエリのルール（WHERE と AND ステートメント）は、hits.item.productName の値が NULL で商品 A を含む場合、その値を考慮しません。

例: Brighton Metallic Pens - Set of 4 を購入したユーザーが購入した他の商品はなんですか？

2013 年 6 月 24 日に 'Brighton Metallic Pens - Set of 4' を購入したユーザーが購入した商品
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Dremel / BigQuery では、WHERE expr IN を使用すると JOIN がトリガーされ、サイズ制限が適用されます。特に、JOIN の右側のサイズ（この場合、ユーザー数）は 8 MB を超えることはできません。Dremel では、broadcast JOIN と呼ばれます。サイズが 8 MB を超える場合、JOIN EACH 構文を使って shuffled JOIN をトリガーする必要があります。これには IN を使用することはできませんが、JOIN を使って書きかえることができます。

商品 A を購入したユーザーによって購入された商品（拡張 e コマース）

これは上で説明したスケルトン クエリに似ていますが、拡張 e コマース向けです。また、複数の日付のデータを対象にクエリを作成するため、TABLE_DATE_RANGE を使用しています。

商品 A を購入したユーザーによって購入された商品（拡張 e コマース）
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

購入前のユーザー インタラクションの平均数

JOIN() [...] ON コマンドを使用したクエリの例です。ここでは、Google アナリティクス データだけを使用しています。

質問「購入前のユーザー インタラクションの平均数は？」のスケルトン スクリプトは次のとおりです。

購入前のユーザー インタラクション数
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘Google アナリティクス データセット名’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘エイリアス名 1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘Google アナリティクス データセット名’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘エイリアス名 2’
ON エイリアス名 1.hits.item.productSku = エイリアス名 2.hits.item.productSku;
  1. 1 行目では、商品あたりのユーザー インタラクションの平均数を計算する主な数値演算を実行します。このクエリでは、‘Alias_Name_1’ と ‘Alias_Name_2’ と呼ばれる 2 つのサブクエリの結合を作成します。
  2. ‘Alias_Name_1’ は、集計関数 SUM() を使用するフィールドを作成するのに使用され、商品に対して記録されたすべてのヒット数が合計されます。
  3. ‘Alias_Name_2’ は、COUNT() 関数を使用して商品あたりのユーザーヒット数をカウントするのに使用されます。
  4. 最後の行には、結合された 2 つのデータセット間で共有された共通フィールド（hits.item.productSku）が出力されています。

例: 2013 年 9 月 10 日における購入前のユーザー インタラクションの平均数はいくらですか？

2013 年 9 月 10 日における購入前のユーザー インタラクションの数
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

商品あたりの販売された在庫の割合

このクエリの例では、アナリティクス データだけでなくそれ以外のデータも使用しています。両方のデータセットを組み合わせることで、ユーザーの行動をさらに細かく分析できますアナリティクス以外のデータを BigQuery にインポートすることは可能ですが、月々のデータ保存料金に影響することにご注意ください。

質問「商品あたりの販売された在庫の割合は？」のスケルトン スクリプトは次のとおりです。

商品あたりの販売された在庫の割合
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘インポートしたデータセット’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘Google アナリティクス データセット’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘エイリアス名’
ON インポートしたデータセット.’商品 ID フィールド’ = Alias_Name.hits.item.productSku;
  1. このクエリの 1 行目では、2 つのフィールドが出力されています。1 つ目のフィールドにはすべての商品 ID が含まれています。2 つ目のフィールドには数値演算が含まれていて、商品 ID に対して販売された在庫の割合が出力されます。
  2. 2 つのデータセットを使用するクエリを実行する際には、JOIN() ... ON 関数を使用する必要があります。このコマンドにより、共通のフィールドに基づいて 2 つのデータセットの行が結合されます。この場合、2 つのデータセットは [ ‘インポートしたデータセット’ ] と ‘エイリアス名’ です。
  3. [ ‘インポートしたデータセット’ ] は、アナリティクス以外のデータです。このデータセットには、残り在庫数の指標フィールド（インポートしたデータセット.’残り在庫数フィールド’）と商品 ID ディメンション フィールド（インポートしたデータセット.’商品 ID フィールド’）が含まれています。
  4. ‘エイリアス名’ は、灰色部分のサブクエリによって返されたデータに割り当てられた名前です。このサブクエリでは、アナリティクスのデータを使用して商品あたりの販売されたアイテム合計数が取得されます。
  5. 最後の行では、ON ステートメントを使用して、2 つのデータセットの共通フィールドとデータセットが結合される場所が示されています。

このクエリの変数の多くには、データセットの名前に接頭辞が追加されています（インポートしたデータセット.’商品 ID フィールド’、エイリアス名.quantity_sold など）。これにより、選択しているフィールドやそのフィールドが属しているデータセットが明らかになります。

例: 2013 年 7 月 28 日の商品あたりの販売された在庫の割合はいくらですか？

2013 年 7 月 28 日の商品あたりに販売された在庫の割合
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

各商品の収益

質問「各商品の収益はいくらですか？」のスケルトン スクリプトは次のとおりです。

各商品の収益
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘インポートしたデータセット’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘Google アナリティクス データセット名’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘エイリアス名’
  ON インポートしたデータセット.productId = エイリアス名.hits.item.productSku );
  1. 1 行目には、各商品における合計収益を計算するのに必要な数値演算が含まれています。
  2. 灰色部分のサブクエリでは、アナリティクス以外のデータが使用され、商品販売時の収益金額に関するデータが収集されます。
  3. 赤色部分のサブクエリでは、アナリティクス データが使用され、アナリティクス以外のデータと結合されます。商品あたりの販売されたアイテム合計数がカウントされます。
  4. 最後の行では、ON ステートメントを使用して、2 つのデータセットが共有する共通フィールドを明らかにします。この場合は、商品 ID 番号になります。

例: 2013 年 7 月 28 日の商品あたりの収益はいくらですか？

2013 年 7 月 28 日の商品あたりの収益
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

収益は、商品の販売価格と製造コストとの差から計算されます。この情報は、アナリティクス以外のデータセットに保存されます。

各商品の実質収益（払い戻しを考慮に入れる）

質問「各商品の実質収益は？」のスケルトン スクリプトは次のとおりです。

各商品の実質収益
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘インポートしたデータセット名’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘Google アナリティクス データセット名’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'エイリアス名'
    ON インポートしたデータセット.productId = エイリアス名.hits.item.productSku ) );
  1. このクエリは「各商品の収益はいくらですか？」に似ています。相違点は、灰色部分のサブクエリに含まれるアナリティクス以外のデータセットと、1 行目で実質収益を計算している数値演算の部分のみです。
  2. アナリティクス以外のデータセットでは、払い戻しに支払われた合計金額も計算されます（赤色部分のサブクエリの SELECT ステートメント）。
  3. 次に 1 行目で、払い戻しで支払われた収益を粗利益から差し引く数値演算が行われ、実質収益が取得されています。

このクエリについて詳しくは、各商品の収益のセクションをご覧ください。

例: 2013 年 7 月 28 日の商品あたり収益はいくらですか？

2013 年 7 月 28 日の商品あたり実質収益
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

実質収益は、払い戻しされた商品を考慮に入れた後の商品の収益です。商品の合計払い戻し収益を計算する方法は次のとおりです。

商品の合計払い戻し収益 =（商品価格 + 払い戻し商品の送料）* 払い戻しをした商品数

この情報は役に立ちましたか？

改善できる点がありましたらお聞かせください。

さらにサポートが必要な場合

次の手順をお試しください。

ヘルプ コミュニティに投稿する コミュニティ メンバーから回答を得る
お問い合わせ 詳しい情報をお知らせください。解決に向けてサポートいたします
true
learning path
独自の学習プログラムを選択する

Google アナリティクス 4 を最大限に活用するための新しいリソースをご確認ください（google.com/analytics/learn）。新しいウェブサイトには、動画、記事、ガイド付きフローが含まれ、Google アナリティクスの Discord、ブログ、YouTube チャンネル、GitHub リポジトリへのリンクも記載されています。

今すぐ学習を始める

検索
検索をクリア
検索を終了
メインメニュー
11018051649795944138
true
ヘルプセンターを検索
true
true
true
true
true
69256
false
false