Reconcile Your Monthly GCP Invoice with BigQuery Billing Export
If you are involved with the Google Cloud Platform billing for your organization, you may have noticed that there is currently no programmatic access to the details of your monthly invoice.
Read on to learn why this is frustrating and how you can ultimately solve the problem thanks to recent updates to the BigQuery Billing Export.
Invoice Notification & Access
Whether you are setup with online billing (credit card or direct debit) or offline billing (monthly invoice), you can receive an email notification from Google when your invoice is ready.
If you pay automatically with a credit card or direct debit, your invoice notifications will come from payments-noreply@google.comwith a subject such as Google Cloud Platform & APIs: Your invoice is available for XXXXX-XXXXX-XXXXX. If you are setup for monthly invoicing, it will come with the subject Your Google Cloud Platform & APIs documents are ready.
In either case, the email notification will include the PDF invoice as an attachment. This PDF is exactly the same content you may physically receive in the mail if you are also setup for postal invoices. It represents exactly the amount that Google will charge your card, debit your account, or expect you to pay within 30 days.
You can also download a CSV invoice with very same information by visiting the Billing Console. For billing accounts with automated billing, you will go to “Transactions” and then scroll down to the recently completed month where you should find “Documents.” For billing accounts with invoiced billing, you can go to “Invoices” and click on the download icon to select CSV or PDF, or the invoice number where you should find “Documents.”
You may notice, if you are too quick to visit the console after receiving the invoice notification, that the CSV invoice is not yet available for download. It can take another day or two before the CSV invoice shows up, but once it does you can download it with your browser and then store it somewhere useful, such as in a Google Cloud Storage bucket.
At that point, you can access your invoice data from other applications, such as Google Sheets or Python.
Why Automate?
In my organization, we started using GCP 2012 during the “Compute Engine Limited Preview” and moved to Invoiced billing a couple of years later. As the Billing Account Administrator in the organization, I’ve gone through the manual process described above, to download the CSV invoice and upload it to a bucket, every month for the last 50+ months. Needless to say, this is something I’d like to automate.
Why Not CSV Billing Export?
It is possible to automatically export your billing data to a CSV or JSON file in a GCS bucket. This seems like the obvious solution to the problem. If you take the 28 to 31 individual CSV exports from each day in a given month, the total of those should add up to the same amount as the monthly invoice, right?
No.
The CSV exports actually contain “daily usage and cost estimates” rather than the exact same information that is included in the invoice. If you add up the CSV exports, what you get will be a close approximation to the amount you ultimately pay on your invoice, but it will miss some information, such as sustained usage discounts, credits, taxes, adjustments, and other differences related to the timing of when certain GCP services report their usage information or to which sort of service is being billed.
For example, a service like Compute Engine might report instance usage (measured in seconds) quite frequently, while a service such a Cloud Storage might report storage usage (measured in byte-seconds or bytes per second) less frequently due to the nature of what is has to do to add up the usage of every object in every bucket. (!)
As such, it’s possible that some of the items included on the monthly invoice did not, in fact, make it onto the final CSV for the month because they hadn’t fully reported before the CSV was produced. Conversely, some of the charges from the previous month may show up the first or second daily CSV exports from the following month.
The point is, the CSV export is not a reliable way to get the information that is available in the CSV or PDF invoice.
What About the Big Query Billing Export?
It is also possible to export your billing data directly to BigQuery. We began testing this feature during the beta in late 2016 and it became generally available a year later. You may be asking, didn’t having all the billing data automatically available in BigQuery solve the problem with programmatic access to the billing data?
Not exactly.
The BigQuery billing export data contains the usage_start_time and the usage_end_time. So, you would think that you could take run a SQL query for a total all of charges with a usage_end_time in a given month and that would be the same as your invoice total, right?
No.
Based on an analysis that compared more than a year of invoices against the following Standard SQL query (which captures both cost and credits), we found the query result was between -0.30% and +2.6% off from the actual invoice. Depending on how large your invoice is, this difference could represent just a few pennies, or it could represent tens of thousand so dollars!
SELECT
FORMAT_DATE('%Y%m', DATE(usage_end_time)) AS year_month,
(SUM(CAST(cost * 1000000 AS int64)) + SUM(IFNULL((
SELECT
SUM(CAST(c.amount * 1000000 AS int64))
FROM
UNNEST(credits) c),
0)))/ 1000000 AS month_total
FROM
`PROJECT.DATASET.gcp_billing_export_v1_XXXXX_XXXXX_XXXXX`
GROUP BY
year_month
ORDER BY
year_monthAgain, this is due to the same issues that affect the CSV export. Just because some usage occurred during a certain month doesn’t mean for certain that it appears on the invoice for that month rather than the previous of the following month.
What About Using the Invoice Month?
In May of 2018, Cloud Billing introduced invoice.month as a new field to help support our need for programmatic access to the invoice information. The first full month that included this field was June of 2018. The new field allows you to rely on the invoice.month in your SQL queries instead of the usage_end_time.
This is a huge improvement! Using the modified query that follows, the data is much more accurate. For the six months of data we analyzed, we found the query total was between +0.00009% and +0.00026% off from the actual invoice, except for one month that was off by +1.31860 which was most likely caused by a manual adjustment.
SELECT
invoice.month,
(SUM(CAST(cost * 1000000 AS int64)) + SUM(IFNULL((
SELECT
SUM(CAST(c.amount * 1000000 AS int64))
FROM
UNNEST(credits) c),
0)))/ 1000000 AS month_total
FROM
`PROJECT.DATASET.gcp_billing_export_v1_XXXXX_XXXXX_XXXXX`
GROUP BY
invoice.month
ORDER BY
invoice.monthAnother win with this update is the fact that you no longer need to calculate the year_month because invoice.month comes as a String in a useful format (YYYYMM or %Y%m).
Is That Everything?
No! We have not yet reached our goal of being able to automatically and reliably obtain the exact total (to the penny) of a monthly Google Cloud Platform invoice without having to manually download the CSV invoice.
In December of 2018, Cloud Billing introduced cost_type as a new field to identify the type of cost, including taxes, adjustments and rounding errors in addition to regular service costs. With this exciting update, you can obtain a complete billing record from BigQuery making it possible to reconcile your BigQuery billing export with your invoice, to the penny!
Starting with December of 2018, and every month since, the query shown above now exactly matches the total that appears on the CSV and PDF invoices.
Hooray! Problem solved!
None of this would have been possible without the tireless efforts of the dedicated members of Google’s Cloud Billing team, who have been listening to customer feedback and building features that directly address our biggest concerns.
If you are attending Google Cloud Next ’19 in San Francisco this year, and you want to learn more about how a handful of large GCP customers deal with some billing concerns, come check out our panel on Cost Control and Financial Governance Best Practices on April 9th, 2019 at 5pm.
For more information about working with BigQuery Billing Exports data, see the docs, example queries, and release notes.
