BigQuery export query examples

Stay organized with collections Save and categorize content based on your preferences.

This page provides examples of how to query Channel Services data exported to a BigQuery dataset.

For more information about the Channel Services data export, see how to set up Channel Services data export to BigQuery.

Identifying and accessing the BigQuery table

Channel Services exports are set up using the Partner Sales Console.

The table's name uses the format: PROJECT_NAME.DATASET_ID.reseller_billing_detailed_export_v1

To let other users view a Channel Services export, you need to apply the correct permissions. For example, you can assign the bigquery.dataViewer role to your credentials on the project, dataset, or the table itself.

Query examples

The following examples include a sample query and a basic explanation of how to interpret the results. The results contain Google Cloud usage costs for every configured Customer or channel partner in the Partner Sales Console.

Sum of partner costs by billing account

You can use this query to verify an invoice you receive from Google. The values cost and credit.amount are the sums for all rows. These include usage costs, taxes, adjustments, and rounding errors.

SELECT
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.amount) FROM UNNEST(credits) c), 0))
    AS total
FROM PROJECT_NAME.DATASET_ID.reseller_billing_detailed_export_v1
GROUP BY
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  payer_billing_account_id,
  currency,
  invoice.month;

Sum of customer costs by billing subaccount

You can use this query to summarize billable usage for each customer. The values customer_cost and credit.customer_amount show the sums of all rows that represent usage costs. Rows that represent taxes, adjustments, and rounding show a null value.

For credits where type is set to RESELLER_MARGIN, its customer_amount is set to zero to make sure the margin is not visible in customer billing.

The values customer_cost and credit.customer_amount show your Repricing configurations. For example, if the RebillingBasis for a particular customer's Google Cloud entitlement is set to Direct Customer Cost minus 5%, the customer_cost value is 0.95 multiplied by cost. This reflects the configured reduction from your cost.

The results of this query contain each billing subaccount, its associated Partner Sales customer resource name, invoice month, and total billable usage.

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) c), 0))
    AS total
FROM PROJECT_NAME.DATASET_ID.reseller_billing_detailed_export_v1
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;

Sum of channel partner costs by billing account

If you are a distributor, you can use this query to summarize billable usage for each channel partner. The values channel_partner_cost and credit.channel_partner_amount show the sum of all rows that represent usage costs. Rows that represent taxes, adjustments, and rounding show a null value.

For credits where type is set to RESELLER_MARGIN, its channel_partner_amount is set to zero to make sure the margin is not visible in channel partner billing.

The values channel_partner_cost and credit.channel_partner_amount show your Repricing configurations. For example, if the RebillingBasis for a particular channel partner is set to Direct Customer Cost minus 5%, the channel_partner_cost value is 0.95 multiplied by cost. This reflects the configured reduction from your cost.

The results of this query contain each billing account, the channel partner resource_name attached to customers under that billing account, the invoice month, and total billable usage.

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) c), 0))
    AS total
FROM PROJECT_NAME.DATASET_ID.reseller_billing_detailed_export_v1
GROUP BY
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month;

Sum of costs not assigned to a customer

You can use this query to summarize costs not linked to a Channel Services customer, but that may represent usage billable to other customers. The values cost and credit.amount show the sum of all rows where the billing_account_id and payer_billing_account_id are different. The billing_account_id represents the billing subaccount.

For credits where type is set to RESELLER_MARGIN, this query excludes the credit amount. The credit amount is intended for your allocation and is not considered a customer cost. The RESELLER_MARGIN amount is included in your cost and reflected on invoices sent by Google.

SELECT
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.amount) FROM UNNEST(credits) c WHERE c.type != 'RESELLER_MARGIN'), 0))
    AS total
FROM PROJECT_NAME.DATASET_ID.reseller_billing_detailed_export_v1
WHERE 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;

Notes and limitations

Unassigned billing subaccounts and deleted customers

A billing subaccount is assigned to a customer by creating a Google Cloud entitlement linked to the customer. Unassigned billing subaccounts may exist under your billing account if you don't create this entitlement. When you remove a customer from the Partner Sales Console, any previously assigned sub- billing account become unassociated. In both cases, exported data for unassociated billing subaccounts will show null in the following columns.

  • entitlement_name
  • customer_name
  • customer_cost
  • credits.customer_amount
  • channel_partner_name
  • channel_partner_cost
  • credits.channel_partner_amount

Taxes

Taxes are based on your billing location, not the customer's. Rows with cost_type set to 'tax' for each billing subaccount show Google's attribution of your taxes to those accounts, based on their portion of usage and incurred costs. The fields customer_cost and channel_partner_cost show null for these rows.

Make sure you review these rows and verify the correct value before you generate a customer's invoice.

Adjustments and Rounding

Rows that represent taxes, adjustments, and rounding are specific to your invoice reconciliation process and do not contain values for billable costs to customers or channel partners.