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 コンソールを開いたときに、データ費用の数値がどのようにマッピングされるかを示します。

type が RESELLER_MARGIN に設定されたクレジットの場合、これは SBA ビューでは非表示になります。
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;

請求書の CSV のような Google Workspace データビュー

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 の「税金」行と同じ金額に合計されます。