Visualize your costs with Looker Studio

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?

Example visualization in Looker Studio

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:

BigQuery

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. New Google Cloud users might be eligible for a free trial.

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.

Caution: The Cloud Billing data exports only reflect Google Cloud usage and cost data incurred from the date you enable Cloud Billing export, and after. Google Cloud billing data is not added retroactively, so you won't see Cloud Billing data from before you enable export. We recommend that you enable Cloud Billing export to BigQuery at the same time that you create a Cloud Billing account, so you have access to a more comprehensive set of Google Cloud billing data for your analysis needs.
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.

Go to BigQuery
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

Understand the Cloud Billing data tables in BigQuery.
Review example queries for your Cloud Billing data export.
Read about implementing FinOps in Google Cloud, and best practices for monitoring your costs.
Review the code that creates the dashboard.