BigQuery エクスポート クエリの例

このページでは、BigQuery データセットにエクスポートされたチャネル サービス データをクエリする方法の例を示します。

チャネル サービス データ エクスポートの詳細については、BigQuery へのチャネル サービス データのエクスポートを設定する方法をご覧ください。その他のトラブルシューティングのヒントについては、BigQuery エクスポートに関する問題のトラブルシューティングをご覧ください。

BigQuery テーブルの識別とアクセス

チャネル サービス エクスポートは、Partner Sales Console を使用して設定します。

テーブルの名前は PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1 という形式を使用します。

他のユーザーがチャネル サービスのエクスポートを表示できるようにするには、正しい権限を適用する必要があります。たとえば、bigquery.dataViewer ロールをプロジェクト、データセット、テーブル自体の認証情報に割り当てることができます。

クエリの例

以下の例には、サンプルクエリと、結果の解釈方法の基本的な説明が含まれています。結果には、Partner Sales Console で構成されたすべての顧客またはチャネル パートナーの Google Cloud の使用料金が含まれます。

請求先アカウント別のパートナー費用の合計

このクエリを使用して、Google から受け取った請求書を確認できます。値 cost と credit.amount は、すべての行の合計です。これには、使用料金、税金、調整、丸め誤差が含まれます。

SELECT
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.amount) FROM UNNEST(credits) AS c), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
GROUP BY
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  payer_billing_account_id,
  currency,
  invoice.month;

Cloud Billing サブアカウント別の顧客費用の合計

このクエリを使用して、各顧客の課金可能な使用量をまとめることができます。値 customer_cost と credit.customer_amount は、使用料金を表すすべての行の合計を示します。税金、調整、丸めを表す行には、null の値が表示されます。

type が RESELLER_MARGIN に設定されたクレジットの場合、顧客課金にマージンが表示されないよう、その customer_amount がゼロに設定されます。

値 customer_cost と credit.customer_amount は、料金変更の構成を示します。たとえば、特定の顧客の Google Cloud の利用資格の RebillingBasis が Direct Customer Cost minus 5% に設定されている場合、customer_cost 値は 0.95 に cost を掛けたものになります。これは、費用からの構成された減額を表します。

このクエリの結果には、各 Cloud Billing サブアカウント、それに関連する パートナー セールス 顧客リソース名、請求月、合計請求対象使用量が含まれます。

SELECT
  customer_name,
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(customer_cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.customer_amount) FROM UNNEST(credits) AS c), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
GROUP BY
  customer_name,
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  customer_name,
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month;

Cloud Billiing サブアカウント別の顧客費用の合計（サブアカウントのオーナーに表示されます）

このクエリは、販売パートナー経由で購入されたお客様（またはパートナー自身）がサブアカウントに対して Google Cloud コンソールを開いたときに、データ費用の数値がどのようにマッピングされるかを示します。

  1. type が RESELLER_MARGIN に設定されたクレジットの場合、これは SBA ビューでは非表示になります。
  2. cost_type が 税金 である費用の場合、これは SBA ビューには表示されません。販売パートナーは、販売パートナー経由で購入されたお客様の請求書に、該当する税金を個別に追加する必要があります。

一部のレコードで cost_at_list が null になり、Google Cloud コンソールに表示される結果と異なる場合があります。このような場合は、代わりに IFNULL(cost_at_list, cost) を使用してください。詳しくは、正規費用を含まない料金をご覧ください。

SELECT
  customer_name,
  currency,
  SUM(cost_at_list) AS list_cost,
  SUM(cost - cost_at_list) AS negotiated_savings,
  SUM(cost)
    + SUM(
      IFNULL(
        (
          SELECT
            SUM(c.amount)
          FROM
            UNNEST(credits) AS c
          WHERE
            c.type != 'RESELLER_MARGIN'
        ),
        0)) AS total
FROM
  `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  cost_type != 'tax'
  AND billing_account_id = 'BILLING_SUBACCOUNT_ID'
  AND invoice.month = 'YYYYMM'
GROUP BY
  customer_name,
  currency
ORDER BY
  customer_name,
  currency;

請求先アカウントごとのチャネル パートナーの費用の合計

ディストリビュータの場合は、このクエリを使用して、各チャネル パートナーに対する課金対象の使用量を要約できます。値 channel_partner_cost と credit.channel_partner_amount は、使用料金を表すすべての行の合計を示します。税金、調整、丸めを表す行には、null の値が表示されます。

type が RESELLER_MARGIN に設定されたクレジットの場合、チャネル パートナー課金にマージンが表示されないよう、その channel_partner_amount がゼロに設定されます。

値 channel_partner_cost と credit.channel_partner_amount は、料金変更の構成を示します。たとえば、特定のチャネル パートナー用の RebillingBasis が Direct Customer Cost minus 5% に設定されている場合、channel_partner_cost 値は 0.95 に cost を掛けたものになります。これは、費用からの構成された減額を表します。

このクエリの結果には、各請求先アカウント、その請求先アカウントの下にある顧客に関連付けられたチャネル パートナー resource_name、請求月、請求対象使用量の合計が含まれます。

SELECT
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(channel_partner_cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.channel_partner_amount) FROM UNNEST(credits) AS c), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
GROUP BY
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month;

顧客に割り当てられていない費用の合計

このクエリを使用して、チャネル サービスの顧客にリンクされていないが、他の顧客に請求可能な使用量をまとめることができます。値 cost と credit.amount は、billing_account_id と payer_billing_account_id が異なるすべての行の合計を示します。billing_account_id は、Cloud Billing サブアカウントを表します。

type が RESELLER_MARGIN に設定されたクレジットの場合、このクエリではクレジット金額が除外されます。クレジット金額は割り当てを目的としたものであり、顧客費用とは見なされません。RESELLER_MARGIN の金額は費用に含まれ、Google から送信される請求書に反映されます。

SELECT
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.amount) FROM UNNEST(credits) AS c WHERE c.type != 'RESELLER_MARGIN'), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
  AND customer_name IS NULL
GROUP BY
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month;

Google Workspace の請求書 CSV と同様のデータビュー

Google Workspace 販売パートナーの場合、Google Workspace の課金データが、請求書 CSV ファイルと同様の列で表示されます。

SELECT
  (
    SELECT
      ws_labels.value
    FROM
      UNNEST(system_labels) AS ws_labels
    WHERE
      ws_labels.key = 'workspace.googleapis.com/domain_name'
  ) AS domain_name,
  billing_account_id AS customer_id,
  sku.description AS sku_name,
  sku.id AS sku_id,
  (
    SELECT
      ws_labels.value
    FROM
      UNNEST(system_labels) AS ws_labels
    WHERE
      ws_labels.key = 'workspace.googleapis.com/usage_type'
  ) AS description,
  (
    SELECT
      ws_labels.value
    FROM
      UNNEST(system_labels) AS ws_labels
    WHERE
      ws_labels.key = 'workspace.googleapis.com/order_id'
  ) AS order_name,
  FORMAT_TIMESTAMP('%b %d ', usage_start_time, 'America/Los_Angeles')
    AS start_date,
  FORMAT_TIMESTAMP(
    '%b %d',
    TIMESTAMP_SUB(usage_end_time, INTERVAL 1 MINUTE),
    'America/Los_Angeles')
    AS end_date,
  SUM(usage.amount_in_pricing_unit) AS quantity,
  (
    SELECT ws_labels.value
    FROM UNNEST(system_labels) AS ws_labels
    WHERE ws_labels.key = 'workspace.googleapis.com/purchase_order_id'
  ) AS po_number,
  SUM(cost) AS amount,
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
  AND payer_billing_account_id = 'EXTERNAL_BILLING_ACCOUNT_ID'
  AND invoice.month = 'INVOICE_MONTH'
GROUP BY
  domain_name,
  customer_id,
  sku_name,
  sku_id,
  description,
  order_name,
  start_date,
  end_date,
  po_number
HAVING amount != 0
ORDER BY
  domain_name,
  order_name,
  start_date,
  end_date;

SELECT 句と GROUP BY 句に追加するその他の列:

  • billing_account_id: 顧客の Cloud Identity ID を表します。
  • customer_name: API 呼び出しで使用できる顧客のリソースを表します。
  • channel_partner_name: （ディストリビュータの場合）チャネル パートナーを表します。
  • entitlement_name: チャネル サービス内の利用資格のリソース名。
  • customer_correlation_id: 顧客に対して定義できる顧客管理（CRM）ID。
  • usage.amount: Google Workspace のシートベースの使用量の場合は、有料のシート数を示します。たとえば、購入したコミットメント シートの数や、フレキシブル プランのサブスクリプションに割り当てられたシート数などです。

クエリ結果は、CSV 請求書と次の点で異なります。

  • CSV の「説明」列は人が読める文字列を使用しますが、BigQuery エクスポートでは列挙値を使用します。
  • CSV の「開始日 / 終了日」列はゼロパディング（例: May 1）されていませんが、BigQuery クエリではパディングされた値（例: May 01）が使用されます。タイムスタンプの値を直接使用することもできます。usage_start_time はその数値を含み、usage_end_time はその数値を含みません。
  • CSV の末尾には 1 つの「税金」行がありますが、BigQuery エクスポートにはサブスクリプション レベルの税金行があり、CSV の「税金」行と同じ金額に合計されます。