Need a dashboard to calculate top marketing metrics? We've created this Marketing Dashboard spreadsheet template in Excel and Google Sheets to track the budget spent against ROI gained. Enter the top metrics to track to customize the spreadsheet to the desired specifications, and the template will create dashboards to compare budgeted spent against your actual spent, as well as revenues gained, to find the return on investment (ROI) of all dollars spent. With formulas on how to calculate budget vs. actual spent and return on investment across multiple dropdown fields, this Marketing Dashboard spreadsheet template provides all the visualizations needed to track trends over time. When trends are visible, tactics and budget allocation can be changed accordingly.
But best of all - this isn't just limited to marketing. You can customize this Marketing Dashboard spreadsheet to track whatever budget and revenue data that's most important to you, then download it in Google Sheets or Excel. Below, we review how to use this spreadsheet.
Customize your dashboard
First, go to the Fields tab and enter your static inputs like the name of the report and what event you plan to track, such as marketing spend. Below, you can customize two dynamic dropdown fields and two nested dropdown fields. In our example, we chose to track regions with specific countries nested inside those regions. But this could be cities and states, states and countries, or something entirely unrelated to location, such as customer demographics or ad types.
Last, select a noun to be tracked. This could be customers gained from this marketing spend, products sold, new subscribers added, or whatever metric is most important to determine your ROI. For instance, if you're working with Google Analytics, this could be the most important event that you're tracking as your North-Star KPI.
Below the dropdowns, list all of the inputs that will appear in your dataset. In our example, media refers to the platform where we spent our budget, and channel refers to the types of marketing effort. You can list up to 20 individual inputs here to track, but you may want to stick to about 5 to 10 so the dashboard is easier to interpret. If you do use all 20, then the filter inputs on the dashboard will be your best friend.
Enter your data
Now that the inputs have been customized to your specifications, enter your data here. Make sure to include the date of the purchase, the budgeted amount for that date, the actual spent, and the revenue and customers (or whatever your "noun" metric is) gained from that period. It's easiest to assess this data on a monthly basis, but you can get as granular as you want with the date range. Once all your data has been entered, you can now use the budget versus actual and spend versus revenue dashboard to compare and analyze it across time.
It's important to turn this data into dashboards - for one, the Wharton School of Business found the use of data visualization could shorten meetings by 24%. And Bain and Company found that companies who use data visualizations are 5x as likely to make faster decisions than their peers and are 3x as likely to execute decisions as intended. With dashboards to visualize your marketing budget versus actual and spend versus revenue, you're more likely to make more robust decisions. And according to Bain, you'll be 2x as likely to be in the top quartile of your industry's financial performance. Not bad at all!
Find your budget versus actual
Start with the "Budget versus Actual" dashboard. At the top, you can filter the dashboard based on the date range and dropdowns you want to prioritize. Remember to clear all the inputs to see all your data. Below the filter inputs, the Budget versus actual overview gives a quick glance at the data from the last three months of the latest date in your date range. Next to it, the "Budget versus actual by month" section calculates how much you spent against how much you budgeted for the given search filter.
The next section details the budget used as a percentage broken out across each of your nested dropdowns. So in our example, we can see the amount of budget spent across each region and each country. Below the nested section, the dashboard compares the percent used across your other dropdowns.
Below that, the budget versus the actual spent is shown compared against one another. In this example, it's by media and by marketing channels. Remember, you can download and customize this spreadsheet to whatever dropdown fields best fit your comparison parameters.
Compare your spend versus revenues
Now, the "Spend versus Revenues" dashboard compares the actual amount spent across all your various channels against the revenue returned from that spend. The filter inputs at the top allow you to filter based on a given range or dropdown field. Remember that the filter inputs on both dashboards are unique to that dashboard, so make sure they match one another if you are comparing data across both dashboards at the same time. The actual versus revenues overview gives the same quick glance at the last three months of the latest date in the query, and the "Revenues versus actual by month" section compares the data over the entire period.
Below that, you can see the number of customers per month as well as the average revenue per customer (or whatever noun you decided to track) as well as the ROI per month over the given period.
The dashboard then provides bar charts comparing the revenue versus actual per-all four dropdowns and line charts to find the average revenue per noun per dropdown.
Below that, bar charts show the noun gained per each dropdown, and the noun, in our case customers, gained per month by each dropdown.
The next section finds the ROI, revenue, and customers by nested dropdown, followed by the ROI, revenues, and customers per the other dropdown fields below that. The percent of ROI gained per each dropdown is shown as bar charts along with line charts.
These charts are so the user can visualize changes in ROI over time to discover trends in the data that wouldn't otherwise be visible. Now, if your dashboard is tracking up to 20 inputs, these charts could become a bit of a spaghetti mess, so make good use of the filter inputs above to break down your analysis across each input.
And that's everything you need to know to use this Marketing Dashboard template to calculate your budget versus actual spend, return on investment, and track your top metrics and KPIs. Remember, you can download and customize this Marketing Dashboard spreadsheet to use it for whatever you want to track and save time and hours of work creating your own dashboard.
Now, go check out our KPI Sales Dashboard to learn about how you can use a dashboard we created specifically for sales teams to track your top sales metrics, or check out our KPI Board spreadsheet for a generic KPI dashboard with further customization elements.