GoogleBigQueryの主な機能の概要とマーケティング分析のためのクエリの例

Google BigQueryは、高速で費用対効果が高く、スケーラブルなビッグデータストレージであり、独自のサーバーをホストする能力や希望がない場合に使用できます。その中で、SQLのような構文、標準およびユーザー定義関数を使用してクエリを記述できます。



この記事では、BigQueryの主な機能について説明し、それらの機能を具体的な例とともに示します。基本的なクエリを作成して、デモデータで試すことができます。

SQLとは何ですか？SQLにはどのような方言がありますか

SQL（Structured Query Language）は、データベースを操作するための構造化照会言語です。大量のデータの受信、データベースへの追加、および変更に使用できます。Google BigQueryは、標準SQLとレガシーSQLの2つの方言をサポートしています。



どの方言を選択するかは好みによって異なりますが、いくつかの利点があるため、標準SQLの使用をお勧めします。

• ネストされたフィールドと繰り返しフィールドを操作するときの柔軟性と機能。
• DMLおよびDDL言語のサポート。これにより、テーブル内のデータを変更したり、GBQでテーブルやビューを操作したりできます。
• 大量のデータの処理は、LegasySQLよりも高速です。
• 現在および将来のすべてのBigQueryアップデートのサポート。

方言の違いについて詳しくは、ヘルプをご覧ください。



デフォルトでは、GoogleBigQueryクエリはレガシーSQLに対して実行されます。



標準SQLに切り替える方法はいくつかあります。

1. BigQueryインターフェイスのクエリ編集ウィンドウで[オプションを表示]を選択し、[レガシーSQLを使用する]オプションの横にあるチェックボックスをオフにします
   
   
   
   
2. クエリの前に#standardSQL行を追加し、新しい行でクエリを開始します
   
   

どこから始めるか

記事を読むのと並行してクエリの実行を練習できるように、デモデータを含む表を用意しました。スプレッドシートからGoogleBigQueryプロジェクトにデータを読み込みます。



GBQプロジェクトをまだお持ちでない場合は、作成してください。これを行うには、Google CloudPlatformにアクティブな請求先アカウントが必要です。カードをリンクする必要がありますが、知らないうちにカードからお金が引き落とされることはありません。さらに、登録時に、12か月間300ドルを受け取り、データの保存と処理に費やすことができます。

GoogleBigQueryの機能

クエリを作成するときに最も一般的に使用される関数のグループは、集計関数、日付関数、文字列関数、およびウィンドウ関数です。次に、それぞれについて詳しく説明します。

集計機能

集計関数を使用すると、テーブル全体の要約値を取得できます。たとえば、平均小切手、月間総収入を計算したり、最大購入数を達成したユーザーのセグメントを強調表示したりします。



このセクションで最も人気のある機能は次のとおりです。



すべての関数のリストについては、ヘルプ：レガシーSQLおよび標準SQLを参照してください。



リストされた関数がサンプルデータデモでどのように機能するかを見てみましょう。トランザクションからの平均収入、最高額と最低額の購入、総収入、およびすべてのトランザクションの数を計算してみましょう。購入が重複しているかどうかを確認するために、一意のトランザクションの数も計算します。これを行うには、Google BigQueryプロジェクト、データセット、およびテーブルの名前を示すクエリを作成します。



#legasy SQL

SELECT
  AVG(revenue) as average_revenue,
  MAX(revenue) as max_revenue,
  MIN(revenue) as min_revenue,
  SUM(revenue) as whole_revenue,
  COUNT(transactionId) as transactions,
  EXACT_COUNT_DISTINCT(transactionId) as unique_transactions
FROM
  [owox-analytics:t_kravchenko.Demo_data]

#standard SQL

SELECT
  AVG(revenue) as average_revenue,
  MAX(revenue) as max_revenue,
  MIN(revenue) as min_revenue,
  SUM(revenue) as whole_revenue,
  COUNT(transactionId) as transactions,
  COUNT(DISTINCT(transactionId)) as unique_transactions
FROM
  `owox-analytics.t_kravchenko.Demo_data`

その結果、次のような結果が得られます。







標準のGoogle Sheets関数（SUM、AVGなど）またはピボットテーブルを使用して、デモデータを使用して元のテーブルの計算結果を確認できます。



上のスクリーンショットからわかるように、トランザクションの数と一意のトランザクションは異なります。

これは、transactionIdが重複しているテーブルに2つのトランザクションがある







ことを示しています。したがって、一意のトランザクションに関心がある場合は、一意の行をカウントする関数を使用してください。または、GROUP BY句を使用してデータをグループ化し、集計機能を使用する前に重複を取り除くこともできます。

日付を操作するための関数（日付関数）

これらの機能を使用すると、日付を処理できます。形式の変更、必要な部分（日、月、または年）の選択、日付を特定の間隔でシフトします。



これらは、次の場合に役立ちます。

• エンドツーエンドの分析を設定する場合-さまざまなソースからの日付と時刻を単一の形式に変換します。
• 自動的に更新されたレポートまたはトリガーされたメーリングを作成する場合。たとえば、過去2時間、1週間、または1か月のデータが必要な場合です。
• 日、週、月のコンテキストでデータを取得する必要があるコホートレポートを作成する場合。

最も一般的に使用される日付関数：





すべての機能のリストについては、レガシーSQLおよび標準SQLのヘルプを参照してください。



上記の各関数がどのように機能するか、データのデモを見てみましょう。たとえば、現在の日付を取得し、元のテーブルの日付を％YYYY-％MM-％DDの形式に変換し、1日を減算して加算します。次に、現在の日付と元のテーブルの日付との差を計算し、現在の日付を年、月、日に別々に分割します。これを行うには、以下のクエリ例をコピーして、プロジェクト、データセット、およびデータテーブルの名前を独自のものに置き換えることができます。



#legasy SQL

SELECT
    CURRENT_DATE() AS today,
    DATE( date_UTC ) AS date_UTC_in_YYYYMMDD,
    DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day,
    DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day,
    DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date,
    DAY( CURRENT_DATE() ) AS the_day,
    MONTH( CURRENT_DATE()) AS the_month,
    YEAR( CURRENT_DATE()) AS the_year
  FROM
    [owox-analytics:t_kravchenko.Demo_data]

#standard SQL

SELECT
  today,
  date_UTC_in_YYYYMMDD,
  DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day,
  DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day,
  DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date,
  EXTRACT(DAY FROM today ) AS the_day,
  EXTRACT(MONTH FROM today ) AS the_month,
  EXTRACT(YEAR FROM today ) AS the_year
FROM (
  SELECT
    CURRENT_DATE() AS today,
    DATE( date_UTC ) AS date_UTC_in_YYYYMMDD
  FROM
    `owox-analytics.t_kravchenko.Demo_data`)

リクエストを適用すると、次のレポートが届きます。

文字列を操作するための関数（文字列関数）

文字列関数を使用すると、文字列の形成、サブ文字列の強調表示と置換、文字列の長さの計算、および元の文字列のサブ文字列の順序インデックスを計算できます。



たとえば、彼らの助けを借りて、次のことができます。

• ページURLに渡されるUTMタグによってレポートにフィルターを作成します。
• ソースとキャンペーンの名前が異なるレジスタに書き込まれている場合は、データを統一された形式にします。
• キャンペーン名がタイプミスで送信された場合など、レポート内の誤ったデータを置き換えます。

文字列を操作するための最も一般的な関数：



詳細-ヘルプ：レガシーSQLおよび標準SQL。



説明した関数の使用方法のデモデータの例を見てみましょう。日、月、年の値を含む3つの別々の列がある



とします。この形式で日付を操作するのはあまり便利ではないので、1つの列にまとめましょう。これを行うには、以下のSQLクエリを使用し、プロジェクト、データセット、およびGoogleBigQueryテーブルの名前を含めることを忘れないでください。



#legasy SQL

SELECT
  CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1,
  the_day+'-'+the_month+'-'+the_year AS mix_string2
FROM (
  SELECT
    '31' AS the_day,
    '12' AS the_month,
    '2018' AS the_year
  FROM
    [owox-analytics:t_kravchenko.Demo_data])
GROUP BY
  mix_string1,
  mix_string2

#standard SQL

SELECT
  CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1
FROM (
  SELECT
    '31' AS the_day,
    '12' AS the_month,
    '2018' AS the_year
  FROM
    `owox-analytics.t_kravchenko.Demo_data`)
GROUP BY
  mix_string1

リクエストを実行した後、1つの列に日付が表示されます：







多くの場合、サイトの特定のページを読み込むときに、URLにはユーザーが選択した変数の値が含まれています。これは、支払いまたは配送の方法、トランザクション番号、顧客がアイテムを受け取りたい実店舗のインデックスなどです。SQLクエリを使用して、ページアドレスからこれらのパラメータを抽出できます。



これを行う方法と理由の2つの例を見てみましょう。



例1。ユーザーが実店舗から商品を受け取る購入数を知りたいとします。これを行うには、URLにサブストリングshop_id（実店舗のインデックス）が含まれているページから送信されたトランザクションの数をカウントする必要があります。これは、次のクエリを使用して行います。



＃legasy SQL

SELECT
  COUNT(transactionId) AS transactions,
  check
FROM (
  SELECT
    transactionId,
    page CONTAINS 'shop_id' AS check
  FROM
    [owox-analytics:t_kravchenko.Demo_data])
GROUP BY
  check

#standard SQL

SELECT
  COUNT(transactionId) AS transactions,
  check1,
  check2
FROM (
  SELECT
    transactionId,
    REGEXP_CONTAINS( page, 'shop_id') AS check1,
    page LIKE '%shop_id%' AS check2
  FROM
    `owox-analytics.t_kravchenko.Demo_data`)
GROUP BY
  check1,
  check2

結果のテーブルから、shop_id（check = true）を含むページから5502トランザクションが送信されたことがわかります。







例2。delivery_idを各配信方法に割り当て、このパラメーターの値をページのURLに書き込んだとします。ユーザーが選択した配信方法を確認するには、別の列でdelivery_idを選択します。

これには次のクエリを使用します：



#legasy SQL

SELECT
  page_lower_case,
  page_length,
  index_of_delivery_id,
  selected_delivery_id,
  REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id
FROM (
  SELECT
    page_lower_case,
    page_length,
    index_of_delivery_id,
    SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id
  FROM (
    SELECT
      page_lower_case,
      LENGTH(page_lower_case) AS page_length,
      INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id
    FROM (
      SELECT
        LOWER( page) AS page_lower_case,
        UPPER( page) AS page_upper_case
      FROM
        [owox-analytics:t_kravchenko.Demo_data])))
ORDER BY
  page_lower_case ASC

#standard SQL

SELECT
  page_lower_case,
  page_length,
  index_of_delivery_id,
  selected_delivery_id,
  REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id
FROM (
  SELECT
    page_lower_case,
    page_length,
    index_of_delivery_id,
    SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id
  FROM (
    SELECT
      page_lower_case,
      CHAR_LENGTH(page_lower_case) AS page_length,
      STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id
    FROM (
      SELECT
        LOWER( page) AS page_lower_case,
        UPPER( page) AS page_upper_case
      FROM
        `owox-analytics.t_kravchenko.Demo_data`)))
ORDER BY
  page_lower_case ASC

その結果、GoogleBigQueryに次のテーブルが表示されます。

データのサブセットまたはウィンドウ関数を操作するための関数（ウィンドウ関数）

これらの関数は、前述の集計関数に似ています。それらの主な違いは、クエリを使用して選択されたデータのセット全体ではなく、その一部（サブセットまたはウィンドウ）で計算が実行されることです。



ウィンドウ関数を使用すると、JOIN演算子を使用して複数のクエリを結合しなくても、グループごとにデータを集約できます。たとえば、広告キャンペーンごとの平均収益、デバイスごとのトランザクション数を計算します。レポートに別のフィールドを追加することで、たとえば、ブラックフライデーの広告キャンペーンからの収益のシェアやモバイルアプリケーションから行われたトランザクションのシェアを簡単に見つけることができます。



各関数とともに、ウィンドウの境界を定義するOVER式をリクエストに書き込む必要があります。OVERには、操作できる3つのコンポーネントが含まれています。

• PARTITION BY-ソースデータをサブセットに分割するための属性を定義します（例：PARTITION BY clientId、DayTime）。
• ORDER BY-サブセット内の行の順序を定義します（例：ORDER BY時間DESC）。
• WINDOWFRAME-特定の特性に従ってサブセット内の行を処理できます。たとえば、ウィンドウ内のすべての行ではなく、現在の行の前の最初の5行のみの合計を計算できます。

この表は、最も一般的に使用されるウィンドウ機能をまとめたものです。



すべての関数のリストは、レガシーSQLおよび標準SQLのヘルプで確認できます：集計分析関数、ナビゲーション関数。



例1.就業時間と非就業時間中のバイヤーの活動を分析したいとします。これを行うには、トランザクションを2つのグループに分割し、関心のあるメトリックを計算する必要があります。

• グループ1-営業時間の9:00から18:00までの購入。
• グループ2-営業時間外の00:00から9:00および18:00から00:00までの購入。

就業時間と非就業時間に加えて、ウィンドウを形成するための別の記号はclientIdです。つまり、ユーザーごとに2つのウィンドウがあります。



デモデータで、平均、最大、最小、および総収入、トランザクション数、および各ユーザーの就業時間と非就業時間中の一意のトランザクションの数を計算してみましょう。以下のクエリは、これを行うのに役立ちます。



#legasy SQL

SELECT
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue,
    MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue,
    MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue,
    SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue,
    COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions,
    COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN ' '
        ELSE ' '
      END AS DayTime
    FROM
      [owox-analytics:t_kravchenko.Demo_data]))
GROUP BY
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
ORDER BY
  transactions DESC

#standard SQL

SELECT
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue,
    MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue,
    MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue,
    SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue,
    COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions,
    COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN ' '
        ELSE ' '
      END AS DayTime
    FROM
      `owox-analytics.t_kravchenko.Demo_data`))
GROUP BY
  date,
  clientId,
  DayTime,
  avg_revenue,
  max_revenue,
  min_revenue,
  sum_revenue,
  transactions,
  unique_transactions
ORDER BY
  transactions DESC

clientId = '102041117.1428132012'のユーザーの1人の例を使用して、結果として何が起こったかを見てみましょう。このユーザーの元のテーブルには、次のデータが







あります。クエリを適用することにより、このユーザーからの平均、最小、最大、合計の収入とトランザクション数を含むレポートを受け取りました。以下のスクリーンショットでわかるように、ユーザーは営業時間中に両方のトランザクションを実行しました。





例2。それでは、タスクを少し複雑にしましょう。

• 実行時間に応じて、ウィンドウ内のすべてのトランザクションのシーケンス番号を書き留めましょう。ユーザーと稼働時間/非稼働時間によってウィンドウを定義することを思い出してください。
• ウィンドウ内に次/前のトランザクションの収益（現在のトランザクションと比較して）を表示してみましょう。
• 最初と最後のトランザクションの収益をウィンドウに表示してみましょう。

このために、次のクエリを使用します。



＃legasy SQL

SELECT
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    hour,
    DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank,
    revenue,
    LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue,
    LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour,
    LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN ' '
        ELSE ' '
      END AS DayTime
    FROM
      [owox-analytics:t_kravchenko.Demo_data]))
GROUP BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
ORDER BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour

#standard SQL

SELECT
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
FROM (
  SELECT
    date,
    clientId,
    DayTime,
    hour,
    DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank,
    revenue,
    LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue,
    LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour,
    LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour
  FROM (
    SELECT
      date,
      date_UTC,
      clientId,
      transactionId,
      revenue,
      page,
      hour,
      CASE
        WHEN hour>=9 AND hour<=18 THEN ' '
        ELSE ' '
      END AS DayTime
    FROM
      `owox-analytics.t_kravchenko.Demo_data`))
GROUP BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour
ORDER BY
  date,
  clientId,
  DayTime,
  hour,
  rank,
  revenue,
  lead_revenue,
  lag_revenue,
  first_revenue_by_hour,
  last_revenue_by_hour

clientId = '102041117.1428132012'に既に精通しているユーザーの例を使用して、計算結果を確認してみましょう。







上のスクリーンショットから、次のことがわかります。

• 最初のトランザクションは15:00で、2番目のトランザクションは16:00でした。
• 15:00の現在のトランザクションの後、16：00にトランザクションがあり、その収益は25066（列lead_revenue）です。
• 午後4時の現在のトランザクションの前に、午後3時にトランザクションがあり、収益は3699でした（列lag_revenue）。
• ウィンドウ内の最初のトランザクションは15:00のトランザクションで、その収益は3699（列first_revenue_by_hour）です。
• リクエストはデータを1行ずつ処理するため、検討中のトランザクションの場合、ウィンドウの最後になり、last_revenue_by_hour列とrevenue列の値は同じになります。

結論

この記事では、Aggregate関数、Date関数、String関数、Window関数のセクションから最も人気のある関数について説明しました。ただし、Google BigQueryには、次のような多くの便利な機能があります。

• キャスト関数-データを特定の形式にキャストできます。
• テーブルワイルドカード関数-データセットから複数のテーブルにアクセスできます。
• 通常の式関数-正確な値ではなく、検索クエリのモデルを記述できます。

コメントについて同じ詳細を書くことが理にかなっている場合は、コメントに書き込んでください。