Monthly Budget Dashboard using Google Data Studio

What is the goal of your workflow? What problem does it solve, or how does it help you?
The goal of my workflow was to be able to have an aesthetically pleasing Dashboard that can easily be sent to me via email, and that I don’t have to spend too long making it. I can’t stress it enough that I am notorious for over complicating simple decisions. When it came to creating my own Tiller Sheet, this was the same case. Now you may ask why not use one of the gorgeous templates available, well for me I learn better by doing it on my own, and so that is what I decided to do.

How did you come up with the idea for your workflow?
I am a Product Manager at a Software SaaS company, and when it came to creating a dashboard for our mixpanel user data my manager introduced me to Google Data Studio, and after redoing my dashboard on google sheets more often than I could count, I decided to try Google Data Studio.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
My workflow is pretty simple. It uses 2 sheets from the Tiller template, transactions and categories. In the transactions sheet I use a formula towards the right that pulls in the groups and type mapped to each transaction this way in Data Studio you can have higher hierarchy Type > Group > Category. You don’t have to do it this way this is just how I do it. You can easily modify this to use all Categories. However, I will say that pulling in Type is important this way you can easily generate your Net since you only have to apply 1 filter based on Type.

Workflow steps are basically going to Transaction sheet on a daily basis and categorizing each transaction then going to Data Studio and clicking refresh on the top right.

In Data Studio, I have created just 1 more custom field out of the Amount this way all my expenses can be in positive amounts. The top two quadrants are for a quick monthly summary of the current month and the last month. Each scorecard (which is the box with numbers on it) has, currently, “No Data” where there will be a percentage with an arrow pointing up or down for YoY trend. You can customize this to be MoM if you’d like. The table in the bottom of each box will include totals for each group, and also gives you YoY for each group.

The next 2 quadrants are MoM and YoY Cash Flow. The MoM was a graph i use to have at Personal Capital that I liked since it told me which days i spend the most money, the YoY tells me the cash flow for the current year which is great to know my savings rate.

The last graph is the bar chart that has the current year stacked by group for just expenses.

You can setup an email digest of this report at any frequency you’d like. I currently have it for the beginning of the month even though i will check it daily. The reason for this is because i don’t expect the data to change all that frequently. Every beginning of the month my fiance and I meet up to discuss where we left off in the previous month so this is perfect to just tell her the Net.

Oh one last thing I forgot to mention is that I currently have in my dashboard another custom formula that this sample data doesn’t have. That column is to divide transactions in half if the account has a suffix that end with A&M which is the initials of my finace and I as that is the credit card we split. This way my finances include half of those transactions and all of my transactions. I use to maintain a separate spreadsheet for just our expenses together, but realized it was best to just keep it in one spreadsheet.

Anything else you’d like people to know?
Data Studio is pretty easy to change which columns you are wanting to use, so keep that in mind as in the beginning you will have to map it to the columns you use. The good thing is that the transactions sheet is pretty standard, except for the custom formula I created, of course.

Is it ok for others to copy, use, and modify your workflow?
Certainly! This has sample data from Tiller, that I modified to be shareable.

If you said yes above, please make a copy of your workflow and share the copy’s URL:

Data Studio Template
Tiller Sheet

Thanks you for sharing @mar5!! This is awesome!

1 Like

@mar5

Thanks for sharing this idea. I’ll give it a run and see how it works.

This is amazing. Thanks so much for sharing and explaining it.

1 Like