Get monthly GCP costs for FY2023 by project in BigQuery

get_monthly_gcp_costs_for_fy2023_by_project_in_bigquery.py

import pandas as pd

# Query of BigQuery
bq_query = """
SELECT
    project.id
    , SUM(COALESCE((SELECT SUM(x.amount) FROM UNNEST(credits) x), 0) + cost) as cost
    , SUM(cost) as raw_cost
    , SUM(COALESCE((SELECT SUM(x.amount) FROM UNNEST(credits) x), 0)) AS credits_amount
FROM
    `__GCP_PROJECT_ID__.billing_management.gcp_billing_export_v1_000000_000000_000000`
WHERE
        usage_start_time > TIMESTAMP('2023-04-01 09:00:00')
    AND usage_start_time < TIMESTAMP('2024-04-01 09:00:00')
GROUP BY 1
ORDER BY 1
"""

def main():
    data_frame = pd.read_gbq(bq_query, "__GCP_PROJECT_ID__")
    print(data_frame)
    print("Sum: " + str(sum(data_frame["cost"])))  # Sum of cost columns
    print(data_frame.to_csv().encode("utf-8"))  # Print in CSV


if __name__ == "__main__":
    main()