Enrich a GCP billing export using the API

The Greenpixie API lets you enrich your own Google Cloud billing export in your own environment. You pull the rate card, join it onto your billing data, and multiply, so your data never leaves your control. This is the same enrichment Greenpixie's managed service performs, done on your side.

Before you start, you need an API key (see Getting started with the Greenpixie API) and your BigQuery billing export (see Getting your cloud usage data).

How enrichment works

Pull the rate card at full granularity, where each row is one SKU in one region with its exact per-unit factors. Join those rows onto your billing export on the SKU id and the region, then multiply each factor by the usage amount on the line to get energy, carbon and water for it.

Step 1. Discover what you can read

Call GET /v1/ratecards first. It returns the (provider, granularity) combinations your key can read, the columns each one returns, the grid granularities and date ranges available, and every published version. Build your next request from these values so nothing is hard-coded.

curl "https://api.greenpixie.com/v1/ratecards" \
  -H "Authorization: Bearer $GPX_KEY"

Step 2. Pull the rate card

Use the full granularity. Pull the whole rate card and page through results with the cursor in links.next until it is null. Filter only when you are sure your export needs just that subset: a region filter, for example, drops the region-less rows the join below falls back to. Each page holds up to 1,000 records.

curl -G "https://api.greenpixie.com/v1/ratecards/cloud/gcp/full" \
  --data-urlencode "limit=1000" \
  -H "Authorization: Bearer $GPX_KEY"

Each row carries sku_id, region, pricing_unit, service_name and service_display_name, and the per-unit factors usage_electricity_consumption_kwh_per_pricing_unit, total_tonnes_co2e_per_pricing_unit and total_water_litres_per_pricing_unit.

One row is unique on sku_id and region. Where Google's catalogue leaves the region blank, the rate card serves unspecified; the query below does the same on your side. A SKU has one pricing unit today, so pricing_unit is not needed for uniqueness, but the query below joins on it too as good practice.

Step 3. Prepare your export

You need three columns from your billing export, plus the quantity.

Multiply usage.amount_in_pricing_units, not usage.amount, which is in base units such as byte-seconds.

The rate card has one row per region a SKU is sold in, so the region is part of the join. Its region is Google's region code in lowercase, for example europe-west1, or global where Google lists the SKU as global. location.region is null for multi-region and global usage; location.location then holds US, EU or global. The query below uses it as the region in that case.

Step 4. Join onto your billing export

SELECT
    b.*,
    b.usage.amount_in_pricing_units * a.usage_electricity_consumption_kwh_per_pricing_unit AS usage_electricity_consumption_kwh,
    b.usage.amount_in_pricing_units * a.total_tonnes_co2e_per_pricing_unit                 AS total_tonnes_co2e,
    b.usage.amount_in_pricing_units * a.total_water_litres_per_pricing_unit                AS total_water_litres
FROM gcp_billing_export b
LEFT JOIN api_output a
       ON b.sku.id = a.sku_id
      AND b.usage.pricing_unit = a.pricing_unit
      AND lower(coalesce(b.location.region, b.location.location, 'unspecified')) = a.region;

Here gcp_billing_export is your BigQuery export and api_output is the rate card you pulled in step 2. Lines with no match keep null factors.

Step 5. Check the result

Count the rows. The enriched table must have exactly as many rows as your export. If it has more, a billing line matched more than one rate card row and every total is too high. The usual cause is a join without the region.

If you join several months of usage to several months of rate cards, add the billing period to the join so each month uses the rate card for that month.

Data periods, refresh, and caching

By default, the API enriches using the carbon intensity from the previous full calendar month. This refreshes two days into each new month, once the complete carbon data for the previous month is available.

Whether you can get a past month's rate card back depends on your plan:

• Historic data is in your plan. You can request any past month with the grid_period parameter, at any time. To reproduce an old run exactly, also pin the version and grid_version it used.
• Historic data is not in your plan. Your key only serves the current rate card, built on the last complete month. Requesting an earlier grid_period returns a 403. Once the monthly refresh happens, that month's rate card is gone for you, so save the API output before the refresh and store it alongside your results.

Call GET /v1/ratecards to see which periods your key can read.

If you want to enrich the current month's usage before the month is over, you can use the previous month's rate card as an estimate. Re-enrich once the rate card refreshes on the 3rd of the following month, so the month uses its own completed carbon data.

To pin a run to a specific dataset, set the version, grid_version and grid_period parameters. See the API documentation for the full parameter reference.

Next steps