LookerStudio101: TheSagaBegins

Hey, hey! Alright, let’s get into this, shall we? So, you’re a cloud engineer who’s a whiz with GCP, but Looker’s studio got you scratching your head? No worries, mate! 🤓

First things first — make sure you’ve got a billing account. Yup, you heard it, no freebies here.

Billing, Baby!: Head on over to billing → export billing and switch that bad boy on.

Two Flavors of Tables: You get to enable “Standard” or “Detailed.”

• Standard: Just the basics, chief. It’s got a catchy name like gcp_billing_export_v1_<BILLING_ACCOUNT_ID>.
• Detailed: The whole nine yards! Think of it as the finer cut for billing data. It goes by gcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID>.

BigQuery Setup: Don’t forget to create a BigQuery dataset where these tables are gonna live.

SQL Magic: Now let’s whip up a SQL view in Query field to find that ever-elusive “Net Cost.”

select *,
    COALESCE((SELECT SUM(x.amount)
    FROM UNNEST(s.credits) x),0) AS credits_sum_amount,
    COALESCE((SELECT SUM(x.amount)
    FROM UNNEST(s.credits) x),0) + cost as net_cost,
    PARSE_DATE("%Y%m", invoice.month) AS Invoice_Month,
    _PARTITIONDATE AS date
    from `{project_id}.{dataset_name}.gcp_billing_export_v1_{Billing_account_id}` s
    WHERE _PARTITIONDATE > DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH)

This fancy SQL does a bunch of stuff, like summing up credits and whatnot. It’s all there, down to the last 13 months!

More serious 🚨

1. Selects all columns (*) from the original table.
2. Calculates the sum of the amount field in the credits array, replacing NULL with 0 via COALESCE.
3. Calculates the net_cost by adding cost to the sum of credits (again replacing NULL with 0).
4. Parses the invoice.month string to a DATE type under the column name Invoice_Month.
5. Retrieves _PARTITIONDATE.
6. Filters records for a date range of the last 13 months from the current date.

Okay, now that we got our SQL game on point, let’s make Looker Studio our playground.

1- Into the Looker-Verse: Open Looker Studio and go for the blank report. Oooh, mysterious!

2- BigQuery Love: Click on Add Data and choose BigQuery. Then find the project where you've been hoarding all that billing data and pick your view. Notice that shiny net_cost field? Yeah, we did that!

Let’s Get Filtering: Before we move on, let’s set up some filters. These will let you zoom in on specific environments, projects, or labels…

3- Visualization Baby : Now create a visualization of your cost preview. Told you we’d get there!

4- Service Breakdown: Wanna get a closer look at costs per service? You got it!

5- VAT or Not VAT: If you’re not into including VAT in your net cost, no biggie, we can filter that out.

6- Trendsetter: Wanna be the Nostradamus of your billing data? Let’s compare costs to last month and see those deltas!

Boom! 🎉 You’ve just become a Looker Studio pro. Or at least, less of a Looker Studio newbie like me 😄 but i bet you design better than me 😎

Haha, alright, so you’re done with the “now” and want to play Fortune Teller for your future GCP bills? I like your style! 🌟 Let’s do some SQL divination.(Cost prediction next article based on some cool math)

Feel free to hop back in if you’ve got more Q’s. Happy cloud engineering! 🚀

PS: No worries , all data here it is just for testing purpose , nothing is real

Peace From Tunisia 💌