You can combine Cloud Billing data export to BigQuery with Looker Studio to stay up to date on your Google Cloud costs.
This page shows you how to set up the Billing usage and cost insights dashboard with your Cloud Billing data. You can use the dashboard to answer questions about your Google Cloud spend, such as How much am I spending on Compute Engine resources?
You can set up your dashboard by following this tutorial, or watching the following video:
Objectives
This tutorial shows you how to complete these tasks:
Set up your own copy of the Billing usage and cost insights dashboard using Cloud Shell.
Learn about modifying the dashboard's views for advanced cases, such as if you have multiple Cloud Billing accounts, or a Cloud Billing account in a currency other than USD.
Costs
In this document, you use the following billable components of Google Cloud:
The cost of storing your Cloud Billing data in BigQuery is typically free or minimal. Depending on the size of the BigQuery dataset, you might incur costs for querying the data for analysis.
To generate a cost estimate based on your projected usage,
use the pricing calculator.
Before you begin
Set up Cloud Billing data export to BigQuery.
To use the dashboard, you must enable BigQuery exports for the Standard and Detailed Cloud Billing usage data.
After you enable the data export, it takes about a day 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 Cloud Billing BigQuery dataset is hosted.
- The dataset names for your Standard and Detailed cost data exports. Typically, the cost data exports are in the same dataset.
Create your copy of the dashboard
To create your own copy of the dashboard, you first clone the GitHub repository that automates the process. This step uses Cloud Shell, which is an interactive shell environment for Google Cloud that you can use from your browser.
At a high level, the setup script in the repository does these tasks:
- Creates a new BigQuery dataset, with views that fetch data from your Standard and Detailed cost data exports.
- Copies the dashboard template and connects the copy to the BigQuery views of your data.
- Gives you a Looker Studio link to your copy, which you can save to your Looker Studio dashboards.
To create your copy:
Open the GitHub repository in Cloud Shell:
Navigate to the
billboard
directory:cd examples/billboard
Run the following commands to set up the Python environment for the script:
rm -rf bill-env pip install virtualenv virtualenv bill-env source bill-env/bin/activate pip install -r requirements.txt
Run the script that creates your dashboard. You might need to authorize Cloud Shell to make API calls on your behalf:
python billboard.py \ -pr 'PROJECT_ID' \ -se 'STANDARD_BILLING_EXPORT_DATASET' \ -de 'DETAILED_BILLING_EXPORT_DATASET' \ -bb 'BILLBOARD_DATASET'
Where the variables are as follows:
- PROJECT_ID: The project ID that hosts your Cloud Billing datasets.
- STANDARD_BILLING_EXPORT_DATASET: The BigQuery dataset that contains your Standard cost data export.
- DETAILED_BILLING_EXPORT_DATASET: The BigQuery dataset that contains your Detailed usage cost data export.
- BILLBOARD_DATASET: The name for the
BigQuery dataset where the BigQuery views
for the dashboard are created, for example,
example_dashboard_views
. If you don't already have a dataset for the views, the script creates a new one with this name.
When the script finishes running, you get a Looker Studio link to your dashboard.
Click the Looker Studio link to open the dashboard.
In Looker Studio, click Edit and share to save the dashboard. When you're prompted to add data sources to the report, click Add to report.
You can now access your dashboard from your Looker Studio home page.
(Advanced) Modify the data for multiple Cloud Billing accounts
If you want the dashboard to include data from multiple Cloud Billing accounts, you can modify the BigQuery views to include the billing data exports for all the Cloud Billing accounts.
Before you begin
For each Cloud Billing account that you want to include, follow all the steps in the tutorial prerequisites.
Update the BigQuery views to include additional Cloud Billing exports
In the Google Cloud console, open the BigQuery page.
Select the project that contains the dataset that you created for the dashboard.
In the Explorer panel, expand your project, then expand the dataset.
Click the billboard view to see its details. This view queries your Standard cost and usage data export.
Click the Details tab, then click Edit Query. The query for the view looks similar to the following:
SELECT *, COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0) AS credits_sum_amount, COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0) + cost as net_cost, PARSE_DATE("%Y%m", invoice.month) AS Invoice_Month, _PARTITIONDATE AS date from `PROJECT_ID.BILLING_ACCOUNT_EXPORT` s WHERE _PARTITIONDATE > DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH)
Where PROJECT_ID is the project that contains the BigQuery Standard cost data export for the Cloud Billing, and BILLING_ACCOUNT_EXPORT is the name of the BigQuery table with your Standard cost data.
Duplicate the existing query, and in the
FROM
clause, replace the project ID and BigQuery table with information for the additional Cloud Billing account.Add the
UNION ALL
operator between the two queries to combine the data. Your final query looks something like the following example, which combines the data exports for BILLING_ACCOUNT_1 and BILLING_ACCOUNT_2, which are in PROJECT_ID_1 and PROJECT_ID_2 respectively.SELECT *, --query for BILLING_ACCOUNT_1 COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0) AS credits_sum_amount, COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0) + cost as net_cost, EXTRACT(DATE FROM _PARTITIONTIME) AS date FROM `PROJECT_ID_1.BILLING_ACCOUNT_1_EXPORT` s WHERE _PARTITIONTIME >"2021-01-01" UNION ALL SELECT *, --query for BILLING_ACCOUNT_2 COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0) AS credits_sum_amount, COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0) + cost as net_cost, PARSE_DATE("%Y%m", invoice.month) AS Invoice_Month, _PARTITIONDATE AS date from `PROJECT_ID_2.BILLING_ACCOUNT_2_EXPORT` s WHERE _PARTITIONDATE > DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH)
Repeat these steps for the billboard_detail view, which queries the Detailed cost and usage data.
If one of your accounts is in a different currency than USD, repeat these steps, and use the
currency_conversion_rate
column to convert to USD, similar to the following example:select *, (COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0))/currency_conversion_rate AS credits_sum_amount, (COALESCE((SELECT SUM(x.amount) FROM UNNEST(s.credits) x),0))/currency_conversion_rate + cost/currency_conversion_rate as net_cost, 'USD' as net_cost_currency, PARSE_DATE("%Y%m", invoice.month) AS Invoice_Month, _PARTITIONDATE AS date from `PROJECT_ID.BILLING_ACCOUNT_DATASET` s WHERE _PARTITIONDATE > DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH)
Clean up
If you don't want to use the dashboard any more, clone the GitHub repository
and run the dashboard script with the -clean
option. The script deletes the
BigQuery views, but leaves your BigQuery export
dataset intact.
Open the GitHub repository in Cloud Shell:
Navigate to the
billboard
directory:cd examples/billboard
Run the following commands to set up the Python environment for the script:
pip install virtualenv virtualenv bill-env source bill-env/bin/activate pip install -r requirements.txt
Run the cleanup command:
python billboard.py \ -pr 'PROJECT_ID' \ -se 'STANDARD_BILLING_EXPORT_DATASET' \ -de 'DETAILED_BILLING_EXPORT_DATASET' -bb 'BILLBOARD_DATASET' \ -clean yes
Where the variables are as follows:
- PROJECT_ID: The project ID that hosts your Cloud Billing datasets.
- STANDARD_BILLING_EXPORT_DATASET: The BigQuery dataset that contains your Standard usage cost data export.
- DETAILED_BILLING_EXPORT_DATASET: The BigQuery dataset that contains your Detailed usage cost data export.
- BILLBOARD_DATASET: The BigQuery dataset where you created the BigQuery views for the dashboard.
To delete the Looker Studio dashboard, open Looker Studio, locate the dashboard, and from the menu , click Remove.
What's next
Read about implementing FinOps in Google Cloud, and best practices for monitoring your costs.