Analysing Google Cloud Platform Billing Data

No matter if you are a large enterprise or small startup, or even individual developers, you probably need to to know what you are paying for when it comes to cloud bill. in GCP you can export your billing data to Google BigQuery and then run queries on it to know where you are spending more which can help you control the cost you are incurring to run the infrastructure and app.

Daily spend


SELECT

India_date,

ROUND(SUM(cost)) cost

FROM (

SELECT

DATE(DATETIME(usage_end_time,"Asia/Kolkata")) India_date,

cost

FROM

`YOUR_BILLING_EXPORT_TABLE`

WHERE

project.id = 'YOUR_PROJECT_ID'

AND _PARTITIONTIME >= TIMESTAMP("2019-01-16")

AND _PARTITIONTIME <= TIMESTAMP("2019-01-20") )

GROUP BY

India_date

Order by India_date

Result
GCP Cost per day, taking timezone into account.


 Cost By Services
    SELECT
      item,
      cost
    FROM (
          SELECT
            service.description item,
            ROUND(SUM(cost)) cost
          FROM
            `YOUR_BILLING_EXPORT_TABLE`
          WHERE
               project.id = 'YOUR_PROJECT_ID'
            AND _PARTITIONTIME >= TIMESTAMP("2018-11-01")
            AND _PARTITIONTIME <= TIMESTAMP("2018-11-30")
          GROUP BY
            service.description )
    ORDER BY
      cost desc;
Result

Cost by GCP Service


Similarly, other queries can be executed for drilling further down within a service. i.e. the App Engine cost includes Cloud Datastore, networking, disks etc. One can find the breakdown with the following query.

 Cost by SKU for a specific service 
 SELECT
      item,
      cost
    FROM (
          SELECT
            sku.description item,
            ROUND(SUM(cost)) cost
          FROM
            `YOUR_BILING_EXPORT_TABLE`
          WHERE
             project.id = 'YOUR_PROJECT_ID'
            AND _PARTITIONTIME >= TIMESTAMP("2018-11-01")
            AND _PARTITIONTIME <= TIMESTAMP("2018-11-30")
            AND  service.description = 'App Engine'
          GROUP BY
            sku.description  )
    ORDER BY
      cost desc;
Result