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

This is fantastic. I’ve implemented for my sheet, and love it. Thank you so much for sharing. Great work.

1 Like

Welcome @jpack :wave:!

1 Like

Hi @mar5,
Great work building that Google Data Studio dashboard and showing how well Tiller can integrate it.

It is impressive that you just used the existing Transactions and Categories sheets, with some minor modifications, to create all those charts.

Adding a Group and Type auto-generated column to the Transactions sheet makes it more powerful. Plus, it’s easy to generate the multiple types of views that you discuss above.

While your formulas for generating those columns work, they can be simplified.

For example, you used this in the top row to generate the Group column to lookup up the Group names when a Category is assigned:

=ARRAYFORMULA(IF($D:$D = "Category", "Group", IFERROR(VLOOKUP($D:$D,Categories!$A$2:$B,2, TRUE),"")))

Since the first row in the Categories report has both Category and Group columns, you don’t need to use the IF() statement. Using VLOOKUP of Category with get the result Group, due to the first row.

Also, you use TRUE as the last variable in the VLOOKUP. That’s for lookups when the first column (Category in this case) is sorted. But category is not sort, so FALSE should be used.

The dollar sign references are probably not needed either. I also prefer using IFERROR(1/0) which generates empty instead of the empty double-quotes “”.

Here’s how I would revise the formula for the top row:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))

Your Dashboard looks great on a web browser. Unfortunately, Google Data Studio reports are not (yet?) responsive, so the don’t re-size on mobile devices. Hopefully Google will fix that.

Jon

3 Likes

Hey Thanks @jonorlin! I haven’t gone through everything you mentioned as I’m on mobile but I did want to say that the reason for the IF is because the way Tiller works is it creates new rows from row 2 onwards so you need to add a formula to the first row and the IF formula allows to add the title. I referenced a doc that mentioned this. Let me know if I didn’t understand what you were saying though.

2 Likes

Yes, @mar5.
Tiller does create new rows.

But if you put the formula I proposed in the top row of your Group column, it will accurately calculate the 1st row and all the additional and new rows in the column.

When it does the VLOOKUP for the word “Category” in cell D1 of the Transactions sheet, it will see that the word “Category” in A1 of the Categories sheet and will lookup row 1 column B. In Categories sheet B1 is the word “Group”.

Technically, Category is not a Category but it since it is in the first row of the Category column, so it will work. Try it. :slight_smile:

3 Likes

Sweet! Ok now I get it what you mean! I’ll try it.

1 Like

Appreciate you publishing this @mar5 and @jonorlin for the VLOOKUP wizardry on the Transactions tab :metal:

1 Like

Just want to clarify that we actually add new rows to the bottom of the sheet and then sort by the date column.

You can toggle on the Auto Sort Override via Settings section in Tiller Money Feeds.

2 Likes

Oh sweet! Thank you for pointing that out!

But my point was that adding a formula to row 2 doesn’t work because Tiller sorts however I re-read the comment by @jonorlin and he was not saying to add it to row 2 but rather the logic could be simplified so I was not understanding. Either case thank you for clearing that up!

1 Like