Looker Studio dashboard – Plan & Fact Spend Tracking
In this article I am going to review my recent Looker Studio dashboard – Plan & Fact spend tracking dashboard. It is really useful for paid traffic managers and digital marketing managers so that they can quickly overview budget KPIs.
Here is the video-tutorial:
Monthly budget and spend forecast
Before the section we have a date range picker. That is what the section looks like:
Total budget is a fixed number that your client/boss has provided for the ads. In the same line we have this sum split by ads platforms – Facebook, Twitter, TikTok and Microsoft Ads.
I have to warn you that the data is random, that is why the performance may be really strange. In the Spend forecast for the end of the month, percentage from the budget section we do have strange numbers. For example, Total spend = 261% means that if we keep spending money like we do now till the end of the month, we will use 261% of the budget that is more than two times more than we have! And in the same row, where spend percentages are split by ads platforms, we can see where we overspend the budget.
Current spend for today
In this section we can see how much money we have already spent compared to our monthly budget. Analyzing the charts, we can see that we overspend the budget on all the ads platforms except Microsoft Ads.
Spending trajectories
The first chart in this section shows us when the budget was overspent (the point of crossing Total Spend and Period limit lines). Total Spend shows us what we’ve really spent. Pink line stands for the perfect, planned total spend, how it was supposed to be. In the best case, pink and Total Spend lines should meet. Trajectory total shows us the way the spends will grow if we keep spending money the same way we do now.
Here are the trajectories for the spends split by ads platforms. We can notice the only Microsoft ads line is close to the Planning spend line.
Paid traffic channels ROAS overview
So, in the very top of the section we have the date filters and Paid Traffic: ROAS filters. In the last-mentioned filter I added a rule that groups rows by categories.
I have added a drill down to each of the tables, so it transforms to date level.
The first small chart shows us the Revenue and ROAS, and the second one shows Paid traffic spend. The third big chart shows us the data from both of the upper-mentioned tables in one. Here we have separate columns for each of the metrics.
Traffic channel comparison
The section consists of two parts – a line chart and a table. Let’s start observing this section with a chart.
As you can see, right after the date range controls, we have a chart where we can see the correlation between a certain ads platform ROAS and time. We have a drill down that lets us move from months to dates or years, for example. I have also added a possibility to enable optional metrics to overview not only ROAS but also revenues and spends split by ads platform.
Here is the table with completely the same data but it contains exact numbers. I have applied some conditional formatting to this table. It has more or less the following sense: if the ROAS is less than some value then highlight the cell in red, if it is greater than some value, then highlight it in green. This function lets us detect the problematic moments immediately.
Backend
All the information I have in the dashboard is pulled up from Google Sheets. Let’s see what we have there.
First of all, there is a Help tab where you can learn how to replace random data with your own one. The next tab is Budget, where I set up months, budget for each month, you can add your ads platforms and percentage for each channel from the total budget. In the same tab I set up certain rules for campaign splitting. One column stands for triggers and the second one – for the values that should be used in the dashboard when it has this trigger. Other part of the tab belongs to the formulas, counting the days of each month.
The next tab contains Google Analytics data (or it could contain CRM data). Here we have dates, source/medium, transactions and transaction revenue, formula column that splits data by platform based on the source/medium and some notes about working with this table.
The following tab is Query Ads. This tab contains all the spend data for all the platforms. Tables for each platform consist of Ads platform, date, amount spent, website conversions and website conversions value.
The last but not the least tab is the one we actually connect to Looker Studio. White part of the tab is constant values like revenues and spends for each ads platform. Purple part of the report contains all the calculated fields that were counted in the Budget tab.
Summary
It took a long time for me to prepare this dashboard. If you found my dashboard useful and you would love to get it, you can buy it on my website! It will definitely save your time. When you buy the template, you will get an email with two links – pre-set up Google Sheets and Looker Studio. All you need to do is just to change all the yellow fields to your real data and Looker Studio dashboard will be updated automatically according to the provided data!
Leave comments if you liked this article!
You can read more articles in my blog or Medium.
If you want to learn how to work in Looker Studio, create stunning reports for your own use and for selling – I am here to invite you to my Data Visualization in Looker Studio course! You can read more about it by the link.