Export Channel Services data to BigQuery

This page shows you how to configure a Channel Services export, and also describes the available schema for an export.

Using BigQuery , you can export Channel Services billing data to a specified BigQuery dataset . This data can help you perform a detailed analysis of how your customers or channel partners are using the services you provide.

Exports from Channel Services contain reseller-specific data not otherwise included in Cloud Billing exports. This includes costs, credits, and usage data with your configured repricing rules. These exports also aggregate all information about your customers' parent billing accounts into a single dataset.

However, data from a Channel Services export is compatible with Cloud Billing exports, and you can use queries to join both datasets for analysis.

For information about non-reseller Cloud Billing exports, see setting up Cloud Billing exports to BigQuery .

Before you begin

Before you start exporting Channel Services billing data, you must:

• Set up a project to host your BigQuery dataset. Make sure that billing is enabled on the project.
• Enable the BigQuery Data Transfer Service API on the project.
• Create a dataset for your exported data.

If you already have a project and BigQuery dataset for Cloud Billing data exports , you can reuse that project and dataset for the Channel Services export.

For detailed information on setting up a project and dataset, see Set up Cloud Billing data export to BigQuery .

Permissions required for exports

To enable and configure Channel Services exports, you need the following roles:

• For BigQuery, if you created the target dataset, you need BigQuery User for the project that contains the dataset. If the dataset was created by someone else, you need the BigQuery Data Owner role.

• To enable Google Cloud exports, in the Partner Sales Console, you must have the Google Cloud Reseller administrator role.

  Learn about assigning Google Cloud roles in the Partner Sales Console

• To enable Google Workspace exports, you must be a Reseller Admin or have the Google Workspace Billing privileges.

  Learn about assigning Google Workspace roles in the Partner Sales Console

• (Optional) Project Viewer for the project that contains the target BigQuery dataset, to automatically check if billing is enabled on the project .

Select a project and dataset

In the Partner Sales Console, select a project and dataset in BigQuery to store the Channel Services data. Enable billing on the project if not already enabled.

When you select or create a BigQuery dataset , you can choose one of the supported BigQuery locations for the dataset. When you enable the Channel Services data export for the first time per data source (Google Cloud or Google Workspace):

• If you configure your BigQuery dataset to use a multi-region location , BigQuery export data is available for each selected data source from the start of the previous month. For example, if you enable BigQuery export on September 23rd, your export will include data beginning August 1st.

• If you configure your BigQuery dataset to use a region location (other than multi-region US or EU), your BigQuery export data is available for each selected data source from the date you enable the export, and after. That is, BigQuery export data isn't added retroactively for non-multi-region dataset locations, so you won't see BigQuery export data from before you enable export.

To export billing data for multiple Partner Sales Console accounts, follow these steps:

1. On the BigQuery dataset that you want to export your data to, make sure the Billing Administrators for each Partner Sales Console account have permissions to view the dataset.
2. When you set up the billing data export in each Partner Sales Console account, select the unified dataset.

Supported BigQuery locations

There are two types of regions BigQuery supports.

Multi-regions

The following table lists the multi-regions where Channel Services BigQuery data export is supported.

Regions

The following table lists the regions in the Americas where Channel Services BigQuery data export is supported.

The following table lists the regions in Asia Pacific where Channel Services BigQuery data export is supported.

The following table lists the regions in Europe where Channel Services BigQuery data export is supported.

Enable export

Before you enable the data export, note your fully-qualified dataset ID, which is in the format PROJECT_ID : DATASET_NAME .

To enable the Channel Services export:

1. In the Partner Sales Console, open the Billingpage .
2. Select Billing export.
3. Enter the fully-qualified dataset ID in the Rebilling datasetfield.
4. Select the Data sourcesto export data for.
5. Click Update. If you see a prompt to update dataset permissions, click Update.

After it's set up, the billing export delivers incremental usage data daily. The export table won't show up until the billing export runs for your account for the first time. The dataset contains data from when it was first configured to retain data.

To stop exporting rebilling cost data to the dataset, click Disable billing data exporton the Customer rebillingpage. To re-enable exporting, follow the previous steps to specify a dataset ID.

Rebilling cost data may be unavailable for the duration it was disabled. If you deleted any exported data during that time, we cannot backfill the deleted records.

Required service account permissions

The Channel Services data export uses a service account that is owned and managed by Google, and has permission to write billing records to a table. The service account is:

 cloud-channel-billing-reporting-rebilling@system.gserviceaccount.com 

This service account requires the predefined role: roles/bigquery.dataEditor to allow exports. You can configure the permissions by following the steps to enable export .

Query data efficiently

The BigQuery table is partitioned by export_time and clustered by payer_billing_account_id by default. This means that the table is divided into smaller partitions, each of which is further divided into smaller clusters. Using the partition and cluster keys in a query will help BigQuery to quickly locate the relevant data, which can significantly reduce the cost of the query.

For example, this query is efficient compared to a query without partitioning and clustering keys:

  SELECT 
  
 billing_account_id 
  
 currency 
 , 
  
 invoice 
 . 
 month 
 , 
  
 -- Divide by currency_conversion_rate if converting non-USD to USD. 
  
 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 
 
 ) 
  
 AND 
  
 payer_billing_account_id 
  
 IN 
  
 ( 
  PAYER_BILLING_ACCOUNT_IDS 
 
 ) 
 GROUP 
  
 BY 
  
 billing_account_id 
 , 
  
 currency 
 , 
  
 invoice 
 . 
 month 
 ORDER 
  
 BY 
  
 billing_account_id 
 , 
  
 currency 
 , 
  
 invoice 
 . 
 month 
 ; 
 

For more information on partitioning, clustering, and best practices, see the following links:

1. Query Partitioned tables
2. Query Clustered tables
3. Optimize query computation
4. Best Practices to control costs in BigQuery

Finding payer's billing account ID

To find the payer's billing account ID, open the billing accounts page of the Partner Sales Console and click the Partner billingtab.

You can see all the billing accounts and the payer billing account ids.

Setting up access controls for other users on the export dataset

There are multiple ways to set up access controls on the data in BigQuery export. For more information, see the BigQuery access controls .

To make sure that users only have access to data that they're authorized to view, we recommend setting up Authorized views to share data from your exported dataset.

For continuous data export, when you set up permissions for users, make sure that the service account cloud-channel-billing-reporting-rebilling@system.gserviceaccount.com always has full editor access on the export dataset.

Table schema

The following schema describes the data in the BigQuery table.

In your BigQuery dataset, the detailed rebilling export is loaded into a data table named reseller_billing_detailed_export_v1 .

Additional notes on the schema

There are a few additional notes on the schema.

Columns not applicable to Google Workspace

When a column or field doesn't apply to a Product Family, the exported data for this product family contains null in this field. The following fields don't apply to Google Workspace exports:

• project
• labels
• resource
• credits
• adjustmentsInfo
• customer_repricing_config_name
• channel_partner_repricing_config_name
• tags
• price.effective_price
• price.tier_start_amount
• price.list_price
• price.effective_price_default
• price.list_price_consumption_model
• transaction_type
• seller_name
• consumption_model

Columns not applicable to Google Workspace Consumptive SKUs

When a column or field doesn't apply to a Product Family, the exported data for this product family contains null in this field. The following fields don't apply to Google Workspace exports:

• service.id
• sku.id
• project
• labels
• resource
• customer_repricing_config_name
• channel_partner_repricing_config_name
• tags
• price.effective_price
• price.tier_start_amount
• transaction_type
• seller_name
• subscription

Columns not applicable to offline orders (preview)

When a column or field doesn't apply to a Product Family, the exported data for this product family contains null in this column or field. The following columns or fields don't apply to offline order billing exports:

• project
• labels
• system_labels
• location
• resource
• credits
• entitlement_name
• channel_partner_name
• channel_partner_cost
• customer_name
• customer_cost
• cost_at_list
• customer_repricing_config_name
• channel_partner_repricing_config_name
• tags
• price.effective_price
• price.tier_start_amount
• price.list_price
• price.effective_price_default
• price.list_price_consumption_model
• transaction_type
• seller_name
• consumption_model

Other notes about Google Workspace export

There are several special behaviors with Google Workspace exports:

• Google Workspace data doesn't contain the reseller's own usage.
• When cost_type is tax and adjustment , usage.amount_in_pricing_units is always 1.
• On Google Workspace invoices, taxes are shown as one line-item. In the export, taxes are broken down by customer. While the total tax should match the invoice, individual taxes might contain minor rounding inaccuracies.

billing_account_id

• For Google Cloud, this is the Cloud Billing account that the usage is associated with.
• For Google Workspace, this is the Cloud Identity of the customer .
• For offline orders, this is name of the customer.

service and sku

For Google Workspace, these columns represent the "Product" and "SKU" associated with the usage. Learn more about Product & SKU IDs .

For Google Workspace consumptive SKUs (Google Voice and Drive), the sku.id and service.id columns contain null .

usage_start_time and usage_end_time

In the Google Workspace invoice CSV, this is represented as dates. In the exports, these are represented as timestamps:

• For usage_start_time , this is the start time (inclusive) of the subscription, at 0:00 PST on the start date.
• For usage_end_time , this is the end time (exclusive) of the subscription, at 0:00 PST on the day after the end date.

project.labels

For Google Cloud, these are the key:value pairs on the Google Cloud project where the usage occurred. For more information, see Using Labels .

labels

For Google Cloud, these are the key:value pairs on the Google Cloud resource where the usage occurred. For more information, see Using Labels .

system_labels

• For Google Cloud, these are the key:value pairs on the Google Cloud resource where the usage occurred. For more information, see Available system labels .

• For Google Workspace, these are additional metadata regarding the cost, which includes:

  • workspace.googleapis.com/domain_name : The domain name of the customer.
  • workspace.googleapis.com/order_id : The order ID.
  • workspace.googleapis.com/purchase_order_id : The purchase order (PO) ID.
  • workspace.googleapis.com/subscription_id : The subscription ID.
  • workspace.googleapis.com/usage_type : The type of usage. Not applicable if cost_type isn't regular .

The Google Workspace usage types include:

• COMMITMENT_TERM_START_ITEM : Beginning of a subscription. Typically associated with a partial month for a monthly billed commitment subscription and for the billing or contract period for a prepaid subscription.
• COMMITMENT_TERM_END_ITEM : End of a subscription. Typically associated with the last month charges of a monthly billed commitment subscription.
• COMMITMENT_TERM_RENEWAL_ITEM : Start of a follow-on subscription period.
• COMMITMENT_MONTHLY_ITEM : Usage recorded for commitments existing at the start of the month. In most cases, the charge will extend throughout the entire month.
• COMMITMENT_SEATS_CHANGE_ITEM : Recorded when the commitment level changes (either increase or decrease). Often starts mid-month and extends to the end of the month for a monthly billed commitment subscription or until the end of the billing or contract period for a prepaid subscription.
• COMMITMENT_CANCEL_ITEM : Recorded when a subscription is cancelled. Includes charges associated with early termination. Represents either a charge for the remainder of the contract period when contract is cancelled mid-way or a credit issued for the remainder of the billing period when a contracted is changed or cancelled mid-way.
• FLEXIBLE_SEATS_ITEM : Records usage by for some number of users for a contiguous block of some number of days. Only intended for users without a commitment.
• COUPON_DISCOUNT_ITEM : Discount amount associated with a coupon.
• MIGRATED_CREDIT_ITEM : Credit from postini migration.
• COMMITMENT_PREPAY_BILLING_PERIOD_ITEM : Represents a usage info that is created at the start of the payment cycle for the entire cycle for a commitment prepay subscription.

usage.amount

For Google Workspace seat-based usage, this indicates paid seats. For example, committed seats for commitments or number of seats paid for flex.

For Google Workspace consumptive SKUs (Google Voice and Drive), this is the number of minutes used.

usage.pricing_unit

• For Google Cloud, learn more about Cloud Billing Catalog API {: track-type="tasks" class="external" track-name="externalLink" track-metadata-position="body"}
• For Google Workspace, this is count .

credits.id

For Google Cloud, this is either an alphanumeric unique identifier ( 12-b34-c56-d78 ), or a description of the credit type ( Committed Usage Discount: CPU ). If the credits.id field is empty, the product SKU isn't associated with a credit.

credits.full_name

For Google Cloud, this is a human-readable description of an alphanumeric credits.id . Examples include Free trial credit or Spend-based committed use discount . credits.full_name values are only populated for SKUs with an alphanumeric credits.id . If the value of credits.id is a description of the credit type (such as Committed Usage Discount: CPU ), then the credits.full_name field will be empty.

credits.type

For Google Cloud, credit types include:

• COMMITTED_USAGE_DISCOUNT : Resource-based committed use contracts purchased for Compute Engine in return for deeply discounted prices for VM usage.
• COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE : Spend-based committed use contracts purchased for services in exchange for your commitment to spend a minimum amount.
• DISCOUNT : The discount credit type is used for credits earned after a contractual spending threshold is reached. Note that in the Cloud Billing reports available in the Partner Sales Console, the discount credit type is listed as Spending based discounts (contractual) .
• FREE_TIER : Some services offer free resource usage up to specified limits For these services, credits are applied to implement the free tier usage.
• PROMOTION : The promotion credit type includes Google Cloud Free Trial and marketing campaign credits, or other grants to use Google Cloud. When available, promotional credits are considered a form of payment and are automatically applied to reduce your total bill.
• RESELLER_MARGIN : If you are a reseller, the reseller margin credit type indicates the Reseller Program Discounts earned on every eligible line item.
• SUBSCRIPTION_BENEFIT : Credits earned by purchasing long-term subscriptions to services in exchange for discounts.
• SUSTAINED_USAGE_DISCOUNT : The sustained use discounts credit type is an automatic discount that you earn for running specific Compute Engine resources for a significant portion of the billing month.

adjustmentsInfo

For Google Cloud, these values are only populated if the cost line item was generated for a Cloud Billing modification. The adjustmentsInfo type contains details about the adjustment, whether it was issued for correcting an error or other reasons.

adjustmentsInfo.adjustment_type

For Google Cloud, this includes:

• USAGE_CORRECTION : A correction due to incorrect reported usage.
• PRICE_CORRECTION : A correction due to incorrect pricing rules.
• METADATA_CORRECTION : A correction to fix metadata without changing the cost.
• GOODWILL : A credit issued to the customer for goodwill.
• SALES_BASED_GOODWILL : A credit issued to the customer for goodwill, as part of a contract.
• SLA_VIOLATION : A credit issued to the customer due to a service-level objective (SLO) violation.
• BALANCE_TRANSFER : An adjustment to transfer funds from one payment account to another.
• ACCOUNT_CLOSURE : An adjustment to bring a closed account to a zero balance.
• GENERAL_ADJUSTMENT : A general Cloud Billing account modification.

adjustmentsInfo.adjustment_mode

For Google Cloud, this includes:

• PARTIAL_CORRECTION : The correction partially negates the original usage and cost.
• COMPLETE_NEGATION_WITH_REMONETIZATION : The correction fully negates the original usage and cost, and issues corrected line item(s) with updated usage and cost.
• COMPLETE_NEGATION : The correction fully negates the original usage and cost, and no further usage is remonetized.
• MANUAL_ADJUSTMENT : The adjustment is allocated to cost and usage manually.

price.effective_price

For Google Cloud, this is the price charged for usage of the Google Cloud or Google Maps Platform SKUs and SKU pricing tiers . If your Cloud Billing account has custom contract pricing, this price is specific to your Cloud Billing. Otherwise, this is the list price of the SKU or SKU tier.