BigQuery Export 查詢範例

本頁面提供查詢匯出至 BigQuery 資料集的 Channel Services 資料的範例。

如要進一步瞭解 Channel Services 資料匯出作業，請參閱「如何設定將 Channel Services 資料匯出至 BigQuery」。如需其他疑難排解秘訣，請參閱「排解 BigQuery 匯出問題」。

找出並存取 BigQuery 資料表

您可以使用 Partner Sales Console 設定 Channel Services 匯出作業。

資料表名稱的格式為：PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1

如要讓其他使用者查看 Channel Services 匯出內容，您必須套用正確的權限。舉例來說，您可以將 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 Billing 子帳戶顯示，並顯示給子帳戶擁有者)

這項查詢會顯示資料費用數字與轉售客戶 (或合作夥伴) 在開啟子帳戶的 Google Cloud 控制台時看到的內容之間的對應關係。

  1. 如果抵免額的 type 設為 RESELLER_MARGIN，則會在 SBA 檢視畫面中隱藏。
  2. 如果費用的 cost_type 為「稅金」，則會在 SBA 檢視畫面中隱藏。經銷商應在其轉售方的月結單中，分別加入適用的稅金。

有時，cost_at_list 可能會為部分記錄設為空值，導致這項結果與您在 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;

未指派給客戶的費用總和

您可以使用這項查詢，匯總與 Channel Services 客戶無關，但可能代表可向其他客戶計費的用量費用。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,
  SUM(cost_at_list) AS list_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：Channel Services 中授權的資源名稱。
  • customer_correlation_id：您為客戶定義的客戶關係管理 (CRM) ID。
  • usage.amount：如果是 Google Workspace 以座位為準的用量，則表示付費座位。例如，已購買的承諾帳戶名額，或彈性訂閱方案的指定帳戶名額數量。

查詢結果與 CSV 月結單的差異如下：

  • CSV 的「Description」欄使用人類可讀的字串，而 BigQuery 匯出作業則使用列舉值。
  • CSV 的「Start/End date」欄不會填入零 (例如 5 月 1 日)，而 BigQuery 查詢會使用填入值 (例如 5 月 1 日)。您也可以直接使用時間戳記值。usage_start_time 是包含值，而 usage_end_time 是排除值。
  • CSV 檔案結尾會顯示單一「稅金」欄位，而 BigQuery 匯出資料會在訂閱層級顯示稅金欄位，匯總金額與 CSV 的「稅金」欄位相同。

離線訂單帳單資料檢視畫面，類似於月結單 PDF (預覽)

如果您是經銷商，且也下達離線訂單 (未透過合作夥伴銷售控制台下單)，您可以查看帳單資料，其中的欄位與帳單 CSV 檔案相似。

SELECT
  billing_account_id AS customer_name,
  service.description AS sku_name,
  sku.id AS sku_id,
  FORMAT_TIMESTAMP('%b %d', usage_start_time, 'America/Los_Angeles')
    AS start_date,
  FORMAT_TIMESTAMP('%b %d', usage_end_time, 'America/Los_Angeles')
    AS end_date,
  SUM(usage.amount_in_pricing_unit) AS quantity,
  SUM(cost) AS amount,
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_offline_orders_detailed_export_v0`
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
  customer_name,
  sku_name,
  sku_id,
  start_date,
  end_date
HAVING amount != 0
ORDER BY
  customer_name,
  start_date,
  end_date,
  sku_id;

查詢結果與 PDF 月結單的差異如下：

  • 您可以將時間戳記值轉換為「America/Los_Angeles」時區的日期值。usage_start_time 和 usage_end_time 互斥。
  • PDF 檔案會提供詳細的稅金明細，列出地方和州的銷售稅，而 BigQuery 匯出資料會在訂閱層級顯示稅金明細，並將整個訂閱項目的稅金加總。