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. For additional troubleshooting tips, see Troubleshoot issues with BigQuery exports.
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_ID.DATASET_NAME.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) 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;
Sum of customer costs by Cloud 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 Cloud 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) 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;
Sum of customer costs by Cloud Billing subaccount, as shown to the subaccount owner
This query shows you how the data cost numbers map with what a resold customer (or you as the partner) sees when they open the Google Cloud console for their subaccount.
- For credits where
type
is set to RESELLER_MARGIN, this is hidden in SBA view. - For costs where
cost_type
is tax, this is hidden in SBA view. The Reseller is supposed to add applicable Taxes separately to their Resold's invoices.
Occasionally, cost_at_list
could be null for some records causing this result
to differ from what you see in the Google Cloud console. If this happens, use
IFNULL(cost_at_list, cost)
instead. For more information, see Charges without
list
costs.
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;
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) 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;
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 Cloud 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) 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 data view similar to invoice CSV
If you are a Google Workspace reseller, you may get a view of the Google Workspace billing data with similar columns as the invoice CSV files.
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;
Other columns of interest to add to SELECT
and GROUP BY
clauses:
billing_account_id
: Represents the Customer Cloud Identity ID.customer_name
: Represents the customer resource which can be used in API calls.channel_partner_name
: (For distributors) Represents the channel partner.entitlement_name
: Resource name of the Entitlement in Channel Services.customer_correlation_id
: A Customer Relationship Management (CRM) ID that you can define for a customer.usage.amount
: For Google Workspace seat-based usage, this indicates paid seats. For example, purchased seats for commitments or number of assigned seats for flex subscriptions.
The query result differs from the CSV invoice in the following ways:
- The CSV "Description" column uses a human-readable string, whereas the BigQuery export uses enum values.
- The CSV "Start/End date" columns are not zero-padded (e.g. May 1), whereas
the BigQuery query uses padded values (e.g. May 01). You may
also use the timestamp values directly. The
usage_start_time
is inclusive, but theusage_end_time
is exclusive. - The CSV has a single "Tax" line at the end, whereas the BigQuery export has tax lines on the subscription-level that sum up to the same amount as the CSV "Tax" line.