Enrich an AWS cloud usage report using the API

The Greenpixie API lets you enrich your own cloud usage report in your own environment. You pull the rate card, join it onto your usage data, and multiply, so your usage data stays in your own environment. 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 exported usage data (see Getting your cloud usage data).

How enrichment works

Pull the rate card at rate-code granularity, where each row is a single rate code with its exact per-unit factors. Join those rows onto your usage report on the rate code, then multiply each factor by the usage quantity 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

For a rate-code join, use the full granularity, which returns one row per rate code with exact per-rate factors. 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 rows for services AWS prices without a region. Each page holds up to 1,000 records.

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

Each row carries a rate_code, a pricing_unit, and the exact 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 rate_code and pricing_unit. Where AWS's price list leaves a join column blank, the rate card serves unspecified; treat a blank value on your side the same way before joining. A rate code is SKU.OfferTerm.PriceDimension, so one SKU has a rate code for each way of paying (on-demand, each reserved term) and for each price tier. Those rows carry the same factors.

Step 3. Join onto your AWS CUR

Join on the rate code and the pricing unit. Together they identify one rate card row. Then multiply the usage amount by each factor to add your new columns.

SELECT
    b.*,
    b.line_item_usage_amount * a.usage_electricity_consumption_kwh_per_pricing_unit AS usage_electricity_consumption_kwh,
    b.line_item_usage_amount * a.total_tonnes_co2e_per_pricing_unit                 AS total_tonnes_co2e,
    b.line_item_usage_amount * a.total_water_litres_per_pricing_unit                AS total_water_litres
FROM aws_cur b
LEFT JOIN api_output a
       ON b.pricing_rate_code = a.rate_code
      AND b.pricing_unit     = a.pricing_unit;

Here aws_cur is your usage report and api_output is the rate card you pulled in step 2. The rate code is pricing_rate_code in CUR 2.0 and pricing/RateCode in the legacy CUR. Lines with no rate code, such as tax, credits, refunds and fees, keep null factors. The pricing unit is pricing_unit in CUR 2.0 and pricing/unit in the legacy CUR; the rate card holds the CUR's own spelling (Hrs, GB-Mo).

If the rate code is not available to you for any reason, for example after a FinOps tool has dropped it, you have two options:

• You still have the SKU. Join on sku and pricing_unit. One SKU has several rate card rows, so first collapse the rate card to one row per SKU and unit with a GROUP BY and max of each factor, as the Azure guide does. The rows share the same factors, so nothing is averaged.
• You have neither. Pull the usage granularity and join on line_item_product_code, line_item_usage_type and pricing_unit. All three are needed. Each usage row averages the rate codes behind it.

Step 4. Check the result

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

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, applying the right carbon data to the right date.

Data periods, refresh, and caching

By default, the API enriches using the carbon intensity from the previous full calendar month. The rate card 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