You can combine Channel Services data export with Looker Studio to create cost dashboards to share with your customer or channel partner (distributor-only), populated with costs configured using repricing configurations.
This page shows you how to repurpose the generic Billing usage and cost insights dashboard with your customer's or channel partner's data, so you can share with them. You may be already familiar with this dashboard if you have previously set up your own cost dashboard following Visualize your costs with Looker Studio.
This tutorial shows you how to complete these tasks:
Set up your own copy of the Billing usage and cost insights dashboard with reseller-configured costs.
Understand how to adapt the BigQuery view to a specific customer or channel partner (distributor-only).
In this document, you use the following billable components of Google Cloud:
- BigQuery: Depending on the size of the BigQuery dataset, you might incur storage costs for the export table in BigQuery and query costs for analysis. Also, if your customer or channel partner views the dashboard, you might also incur query costs.
- BI Engine: If you choose to create BI Engine reservations to improve the performance of your dashboards, you incur costs for the reservation you create in BI Engine.
For more information on BigQuery pricing, see Pricing in the BigQuery documentation.
For more information on BI Engine pricing, see the Pricing page. BI Engine reservations can improve the performance of Looker Studio dashboards. This page does not include instructions on creating BI Engine reservations. Learn about reserving BI Engine capacity.
Before you begin
To use the dashboard, you must enable BigQuery exports for Channel Services data.
After you enable the data export, it takes a few hours for the dataset to be populated with Cloud Billing data. You might see some network egress first, then some VM charges, then Pub/Sub usage, and so on. After you've got data in your dataset, you can run queries against it.
If your organization already has BigQuery exports set up, you must have permissions to create BigQuery views in the project that hosts the datasets.
Optionally, if you want to create a new dataset for the BigQuery views, you must have permissions to create BigQuery datasets.
Get the following information about your Google Cloud environment:
- The project ID where your BigQuery export is hosted.
- The dataset names for your export and for views for customer cost data.
Create BigQuery views of customer costs
Typically, costs shared with customers and channel partners contains only costs for that customer or channel partner and does not contain your own cost. With Channel Services data export, you can create BigQuery views to reduce visibility of the data without using extra storage. Learn more about BigQuery views.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE OR REPLACE VIEW `PROJECT_ID.DATASET_NAME.VIEW_NAME` AS ( SELECT * EXCEPT(cost, credits, customer_name, customer_cost, customer_repricing_config_name, channel_partner_name, channel_partner_cost, channel_partner_repricing_config_name, customer_correlation_id), COALESCE(( SELECT SUM(c.customer_amount) FROM UNNEST(credits) AS c),0) AS credits_sum_amount, COALESCE(( SELECT SUM(c.customer_amount) FROM UNNEST(credits) AS c),0) + customer_cost AS net_cost, PARSE_DATE("%Y%m", invoice.month) AS Invoice_Month, DATE(export_time) AS date, FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1` WHERE DATE(export_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH) -- Adjust this to your use case. AND billing_account_id = 'BILLING_SUBACCOUNT_ID')
Where the variables are as follows:
- PROJECT_ID: The project ID that hosts your export datasets.
- DATASET_NAME: The BigQuery dataset that contains your export. You may choose a different dataset for the BigQuery view.
- VIEW_NAME: The name of the BigQuery view.
- BILLING_SUBACCOUNT_ID: The billing subaccount ID of the resold to restrict cost data to.
For more information about how to run queries, see Running interactive queries.
Adjust the query to your use case
If you are building the dashboard for a channel partner, replace
are no CRM IDs for channel partners.
If you want to customer resource names to filter the view, change the last line
WHERE clause to filter on
Convert non-USD cost amounts to USD
The preceding query does not perform currency conversion. To convert all costs
to USD using the information in the export, first, change the relevant part of
the query (involving
COALESCE(( SELECT SUM(c.customer_amount) FROM UNNEST(credits) AS c),0)/currency_conversion_rate AS credits_sum_amount, COALESCE(( SELECT SUM(c.customer_amount) FROM UNNEST(credits) AS c),0)/currency_conversion_rate + customer_cost/currency_conversion_rate AS net_cost,
currency to the
EXCEPT list and override the
currency column in
'USD' AS currency,
Replace record count with usage amount
In the Overview tab of the dashboard, the table on the right contains record count breakdown by SKU. If you find usage amount is more useful (for example, for Google Workspace Partners this field represents seat counts), you can customize this table to suit your need.
To change "Record Count" to usage amount:
Open the dashboard, and click Edit.
Hold the pointer over the table, then click Show properties () to open the Setup tab.
In the Setup tab, under Metric, click Record Count.
In the list of fields, search select
Hold the pointer over SUM and click the () icon.
In the "Name" box, type a name such as "Usage Amount" or "Seats".
Create and share a copy of the dashboard
To create a copy:
On the top right, click Menu (), then click Make a copy.
Create a data source using the view you just created. Select the view as the data source for the new report.
Click Copy Report.
You can now access the dashboard from your Looker Studio home page.
To share the report with your customer:
Click Edit in the top right.
Change the name of the report. Make any changes as desired.
Click Share in the top right and share the report with your customer. Learn about ways to share a dashboard.
If you don't want to keep the dashboard anymore, delete the view and Looker Studio dashboard.
To delete the BigQuery view, follow the instructions for deleting views.
To delete the Looker Studio dashboard, open Looker Studio, locate the dashboard, and from the menu , click Remove.
If you created BI Engine reservations, you can delete unused ones.
Create repricing configurations to configure cost for your customer.