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 Billing page.
2. Select Billing export.
3. Enter the fully-qualified dataset ID in the Rebilling dataset field.
4. Select the Data sources to 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 export on the Customer rebilling page. 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;