BigQuery について

この機能は、Google アナリティクス 360 スイートに含まれる Google アナリティクス 360 でのみ使用できます。
詳しくは、Google アナリティクス 360 スイートをご参照ください。

この記事では、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 アナリティクス 360 のデータは、毎日新しい表に渡されます。一度に複数の表を対象にクエリを作成するには、次の例のように、表名をカンマで区切るか、TABLE_DATE_RANGE表のワイルドカード関数)を使うか、複数のカンマで区切った TABLE_DATE_RANGE 関数を使います。

カンマ区切りの表名を使って 3 日間を指定する
SELECT
date,
SUM (totals.visits) visits,
SUM (totals.pageviews) pageviews,
SUM (totals.transactions) transactions,
SUM (totals.transactionRevenue)/1000000 revenue
FROM [73156703.ga_sessions_20160327],[73156703.ga_sessions_20160328],[73156703.ga_sessions_20160329]
GROUP BY
date
ORDER BY
date ASC

 

TABLE_DATE_RANGE を使って過去 7 日間を指定する
SELECT
date,
SUM (totals.visits) visits,
SUM (totals.pageviews) pageviews,
SUM (totals.transactions) transactions,
SUM (totals.transactionRevenue)/1000000 revenue
FROM (TABLE_DATE_RANGE([73156703.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

TABLE_DATE_RANGE を使って過去 3 か月間を指定する
SELECT
date,
SUM(totals.visits) visits,
SUM(totals.pageviews) pageviews,
SUM(totals.transactions) transactions,
SUM(totals.transactionRevenue)/1000000 revenue
FROM (TABLE_DATE_RANGE([73156703.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -3, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

TABLE_DATE_RANGE を使って過去 1 年間を指定する
SELECT
date,
SUM(totals.visits) visits,
SUM(totals.pageviews) pageviews,
SUM(totals.transactions) transactions,
SUM(totals.transactionRevenue)/1000000 revenue
FROM (TABLE_DATE_RANGE([73156703.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

特定の期間を指定する
SELECT
date,
SUM(totals.visits) visits,
SUM(totals.pageviews) pageviews,
SUM(totals.transactions) transactions,
SUM(totals.transactionRevenue)/1000000 revenue
FROM (TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2016-01-01'), TIMESTAMP('2016-01-31')))
GROUP BY
date
ORDER BY
date ASC

 

複数の TABLE_DATE_RANGE を使って過去 6 日間と当日のデータ(日中)を指定する
SELECT
date,
SUM(totals.visits) visits,
SUM(totals.pageviews) pageviews,
SUM(totals.transactions) transactions,
SUM(totals.transactionRevenue)/1000000 revenue
FROM (TABLE_DATE_RANGE([73156703.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -6, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))), (TABLE_DATE_RANGE([73156703.ga_sessions_intraday_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

基本的なクエリの例

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

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

質問「[ディメンションを挿入] あたりの合計 [指標を挿入] はいくらですか?」のスケルトン スクリプトは次のとおりです。

[ディメンション] あたりの合計 [指標]
SELECT SUM ( '指標フィールド名を挿入' ) AS ‘エイリアス名’, [ ‘ディメンション フィールド名を挿入’ ]
FROM [ 'データセット名' ]
GROUP BY [ ‘ディメンション フィールド名を挿入’ ]
ORDER BY [ ‘フィールド名を挿入’ ] DESC;
  1. 最初の行で関係するすべての指標とディメンション フィールドを選択しています。集計関数 SUM()、続いて GROUP BY コマンドを使用します。これで、ディメンション フィールド内のディメンション値ごとに指標フィールドのすべての数値が集計されます。
  2. キーワード AS は、フィールドに名前を割り当てるために使用されます。1 行目にあるとおり、キーワード ASSUM ( '指標フィールド名を挿入' ) フィールドに名前 (‘エイリアス名’) を割り当てるのに使用されています。この方法は、集計関数に名前を付けたり、複雑なフィールド名を変更したりするのに便利です。これによりクエリがよりわかりやすくなります。
  3. 4 行目のコマンド ORDER BY [フィールド名を挿入] DESC により、値の大きさの降順でフィールドを並べます。これはすべてのクエリに適用できます。ORDER BY [フィールド名を挿入] ASC コマンドを使用すると、値の大きさの昇順でフィールドを並べることができます。これはどのクエリにも適用できますが、順序を並び替えることができるのは 1 つのフィールド(指標の場合は値順、ディメンションの場合はアルファベット順)でのみです。

    例: 2013 年 9 月 10 日の端末のブラウザあたりのトランザクションの合計数はいくらですか?
2013 年 9 月 10 日のブラウザあたりのトランザクションの合計数
SELECT device.browser, SUM ( totals.transactions ) AS total_transactions
FROM LondonCycleHelmet.ga_sessions_20130910
GROUP BY device.browser
ORDER BY total_transactions;

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

実際の直帰率は、「ページビューが 1 つだけの訪問の割合」と定義できます。質問「[ディメンションを挿入] あたりの平均直帰率はいくらですか?」のスケルトン スクリプトは次のとおりです。

[ディメンション] あたりの平均直帰率
SELECT [ ‘ディメンション フィールド名を挿入’ ] , ( ( total_no_of_bounces / ‘エイリアス名’ ) * 100 ) AS bounce_rate
FROM (
  SELECT [ ‘ディメンション フィールド名を挿入’ ], COUNT ( [ ‘ディメンション フィールド名を挿入’ ]) AS ‘エイリアス名’, COUNT ( totals.bounces ) AS total_no_of_bounces
  FROM [ 'データセット名' ]
GROUP BY [ ‘ディメンション フィールド名を挿入’ ]
)
GROUP BY [ ‘ディメンション フィールド名を挿入’ ] , bounce_rate ;
  1. 1 行目で、分析するディメンションを選択し、同時に数値演算も実行します。数値演算により直帰率指標フィールド(ディメンション フィールドの固有の値ごとのページビューが 1 つだけのセッションの割合)が計算されます。
  2. 灰色部分の行は、1 行目のフィールドに必要なデータセットを作成するコマンドです。このコマンドは、選択されたディメンション フィールド値あたりの合計直帰数を示すフィールドを出力する働きをします。
  3. 灰色部分の SELECT ステートメントの最初の集計関数(COUNT())により、ディメンション フィールドに特定の値が表示される回数と、それに関連付けられる直帰数の合計数がカウントされます。
  4. 灰色部分のサブクエリの WHERE 行により、ユーザーのページビューが 1 つだけのディメンション フィールドの値が選択されます。
  5. 最後の行の GROUP BY ステートメントによって、それぞれの直帰率でディメンション フィールドの値がグループ分けされます。

例: トラフィックの参照元あたりの実質直帰率はいくらでしたか?

2013 年 9 月 10 日のトラフィック参照元あたりの平均直帰率
SELECT trafficSource.source , ( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
  SELECT trafficSource.source , COUNT ( trafficSource.source ) AS total_visits, COUNT ( totals.bounces ) AS total_no_of_bounces
  FROM LondonCycleHelmet.ga_sessions_20130910
  GROUP BY trafficSource.source
)
GROUP BY trafficSource.source, bounce_rate;

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

質問「購入ユーザーの商品ページビューの平均数はいくらですか?」のスケルトン スクリプトは次のとおりです。

購入ユーザーの商品ページビューの平均数
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 [ 'データセット名' ]
  WHERE totals.transactions >=1
  GROUP BY users
) ;

質問「非ず購入ユーザーの商品ページビューの平均数はいくらですか?」のスケルトン スクリプトは次のとおりです。

非購入ユーザーの商品ページビューの平均数
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 [ 'データセット名' ]
  WHERE totals.transactions IS NULL
  GROUP BY users
);
  1. 1 行目の数値演算により、ユーザーあたりの平均ページビュー数(avg_pageviews_per_user)が計算されます。つまり、集計関数 SUM() を使って合計ページビュー数を取得し、集計関数 COUNT() を使って計算したユーザー(購入ユーザーか非購入ユーザーのいずれか)の合計数で割ることで計算されます。
  2. データセットは灰色部分のサブクエリで作成されます(フィールドを 2 つ返す)。
    1. 1 つ目のフィールドは、最後の行の GROUP BY fullVisitorId ステートメントで作成されるユニーク ユーザー フィールドです。
    2. もう 1 つのフィールドは、灰色部分の最初の行の SUM(totals.pageviews) 関数を使って作成されるユーザーあたりのページビューの合計数です。

灰色部分のサブクエリの WHERE ステートメントに適用されるルールは、購入ユーザーの数を知りたいか非購入ユーザーの数を知りたいかによって変わります。購入ユーザーの数を取得するには、WHERE totals.transactions >=1 を使用します。非購入ユーザーの数を取得するには、WHERE totals.transactions IS NULL を使用します。

例: 2013 年 9 月 10 日に購入したユーザーの商品ページビューの平均数はいくらですか?

2013 年 9 月 10 日に購入したユーザーの商品ページビューの平均数
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 LondonCycleHelmet.ga_sessions_20130910
  WHERE totals.transactions >=1
  GROUP BY users
);

例: 2013 年 9 月 10 日に購入しなかったユーザーの商品ページビューの平均数はいくらですか?

2013 年 9 月 10 日に購入しなかったユーザーの商品ページビューの平均数
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 LondonCycleHelmet.ga_sessions_20130910
  WHERE totals.transactions IS NULL
  GROUP BY users
);

表が大きくなる場合、ユーザーの数が多い users 列で GROUP EACH BY を使用してグループ分けできます。

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

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

購入ユーザーあたりの平均トランザクション数
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 [‘Dataset Name’ ]
  WHERE totals.transactions IS NOT NULL
  GROUP BY fullVisitorId
);
  1. 1 行目の数値演算により、ユーザーあたりの平均トランザクション数(avg_total_transactions_per_user)が計算されます。つまり、実行されたトランザクションの合計数を関数 SUM() で取得し、トランザクションを実行したユーザーの合計数(関数 COUNT() で計算)で割ることで計算されます。
  2. データセットはサブクエリで作成され、2 つのフィールドで構成されます。
    1. 1 つ目のフィールドは、最後の行の GROUP BY fullVisitorId ステートメントで作成されるユニーク ユーザー フィールドです。
    2. もう 1 つのフィールドは、灰色部分の最初の行の SUM(totals.transactions) 関数を使って作成される、各ユーザーによって実行されるトランザクションの合計数です。

灰色部分のサブクエリの WHERE ステートメントには、WHERE totals.transactions IS NOT NULL を適用して、トランザクションを実行したユーザーだけが選択されるようにしてください。

例: 2013 年 9 月 10 日の購入ユーザーあたりのトランザクションの平均数はいくらですか?

2013 年 9 月 10 日の購入ユーザーあたりのトランザクションの平均数
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 LondonCycleHelmet.ga_sessions_20130910
  WHERE totals.transactions IS NOT NULL
GROUP BY fullVisitorId
);

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

質問「訪問あたりの平均消費金額は?」のスケルトン スクリプトは次のとおりです。

セッションあたりの平均消費金額
SELECT ( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS avg_revenueSpend_by_user_per_visit
FROM (
  SELECT SUM(totals.visits) AS total_visits_per_user, SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user, fullVisitorId
  FROM ['データセット名' ]
  WHERE totals.visits>0
   AND totals.transactions>=1
   AND totals.transactionRevenue IS NOT NULL
  GROUP BY fullVisitorId
) ;
  1. 灰色部分のサブクエリにより 1 行目の数値演算に必要な 3 つのデータ フィールドが作成され、ユーザーごとにセッションあたりの平均消費金額が計算されます。
  2. ユーザーあたりの合計消費金額(total_transactionrevenue_per_user)、ユーザーあたりのアクセス合計数(total_visits_per_user)、ユーザーに関連づけられた ID フィールド(fullVisitorId)の 3 つのフィールドをサブクエリで作成します。
  3. 該当するユーザーは、灰色部分のサブクエリの WHERE ステートメントにある 3 つのルール(AND ステートメントで区切られている)を満たしている必要があります。つまり、各ユーザーは、セッションを開始したことがあり(WHERE totals.visits>0)、1 回以上のトランザクションを実行し(AND totals.transactions>=1)、支払いが発生している(AND totals.transactionRevenue IS NOT NULL)必要があります。

例: 2013 年 9 月 10 日のセッションあたりの平均消費金額はいくらですか?

2013 年 9 月 10 日のセッションあたりの平均消費金額
SELECT ( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS avg_revenue_by_user_per_visit
FROM (
  SELECT SUM(totals.visits) AS total_visits_per_user, SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user, fullVisitorId
  FROM LondonCycleHelmet.ga_sessions_20130910
  WHERE totals.visits>0
   AND totals.transactions>=1
   AND totals.transactionRevenue IS NOT NULL
  GROUP BY fullVisitorId
) ;

ヒットの順序

ページの閲覧経路分析のスケルトン スクリプトは次のとおりです。

閲覧されたページの順序
SELECT fullVisitorId, visitId, visitNumber, hits.hitNumber, hits.page.pagePath
FROM [ 'データセット名' ]
WHERE hits.type=[ ‘ヒットの種類’ ];
  1. 1 行目でユーザー ID と訪問 ID の固有の組み合わせを特定します。すべてのヒットがこの 2 つのディメンションの固有の組み合わせに属することになります。
  2. 1 行目にページの経路の要素も含めます。
  3. FROM ステートメントで、データソースを指定します。
  4. WHERE ステートメントで、クエリを特定の種類のヒットに制限して、取得する接点の種類を制御します。

例: 閲覧されたページの順序(経路の分析方法)は?

2013 年 9 月 10 日にユーザーが閲覧したページの順序
SELECT fullVisitorId, visitId, visitNumber, hits.hitNumber, hits.page.pagePath
FROM LondonCycleHelmet.ga_sessions_20130910
WHERE hits.type="PAGE";

このクエリでは、ヒットの種類を 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 hitsWITHIN 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))のみが選択されます。

トップレベル(緑色)クエリのルール(WHEREAND ステートメント)は、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 コマンドを使用したクエリの例です。ここでは、アナリティクス データのみを使用しています。

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

購入前のユーザー インタラクション数
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 ‘Alias_Name_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 ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_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 ‘Alias_Name’
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 日の商品あたりの販売された在庫の割合はいくらですか?

商品あたりの販売された在庫の割合
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 ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.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 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.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 )
);

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

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

この記事は役に立ちましたか?
改善できる点がありましたらお聞かせください。