Today we’ll talk about a dashboard for the marketing department: that displays the most important numbers on a single screen. This is the actual data that shows the forecast of the plan for leads fulfillment, traffic to the website and blog, publications. The document automatically drives all the necessary information. Read below and find out how to build such a dashboard: we describe all formulas and share the template. All you have to do is to make a copy and configure the dashboard to your needs.
Marketers usually use several systems at once: Google Analytics, CRM, Excel or Google Sheets, etc. That’s why they have to spend time on data consolidating, to get a full picture. Therefore, it is better to devote several hours to create an automated dashboard that will do everything for you.
Dashboard advantages
- All KPI on a single screen. What is the actual situation with the plan fulfillment and what is the forecast for the current month? How many potential customers came for the past day? How much traffic do we have now and what should we expect at the end of the month? Thanks to this dashboard, every morning in Ringostat marketing team starts with the answers to these questions 🙂
- Data that don’t need to be consolidated manually. Everything is automated, so the probability of errors and inaccuracies is reduced. Every day you see an actual picture and can quickly notice if something goes wrong.
- Control of work by each direction. The head of the department doesn’t need to open the blog each time he wants to find out when the last article was published. Or there is no need to ask an event marketer how many people have registered for the webinar. All data is clear on the sheet with all data brought together.
In this article, we analyze a report created for a department with content marketers who write to a blog and external media resources, as well as an event marketer. But this dashboard can be easily rebuilt under the characteristics of your business processes.
Structure
We’re building a report in Google Sheets for several reasons:
- it’s free;
- the service has flexible settings;
- data from other Google products can be easily transferred to Sheets;
- the network is full of manuals and communities that teach how to work in it.
Link to the dashboard template — open and copy this dashboard. Formulas are already written in the template as well as example numbers are given to make it more clear.
IMPORTANT: Data in the example report was relevant up to March 2019. Thus, some sections like Blog and Site won’t contain actual numbers when you open it. But you can simply upload data from your databases, and everything will be displayed correctly, as it is described in the article.
A dashboard can be divided into two main parts: databases and a sheet with consolidated information, where the data from these databases are driven.
The document consists of the following tabs:
- Dashboard — a sheet with consolidated information for the main metrics;
- Conditions — a description of the stages of the sales funnel and the rules for qualifying leads;
- Events Dashboard — webinars, conferences, and other events;
- Blog Article EN — articles published on the blog;
- Blog Article UA — articles published on the blog for the Ukrainian-speaking audience;
- Outbound — articles published on external media resources;
- Report Configuration — parameters used to drive data from Google Analytics;
- Blog DB — uploading blog traffic data from Google Analytics;
- Site DB — uploading site traffic data from Google Analytics;
- CRM DB — uploading data on leads from CRM;
- Registration DB — data on registration for events
Sheet with consolidated data
This is the first tab that is divided into several segments. We can call it “the starting point” from which the marketer monitors the situation by activities. The data in this sheet is collected for the current month, the forecast for all parameters is calculated up to the last day of the month.
The dashboard is divided into three blocks:
- L&R — leads and registrations;
- C&P — content & production, activities for the content creation as well as customers attraction using this content;
- Users — the ones who visit the blog and website.
The visualization that we see on this sheet is only part of the dashboard. If you click on any of the graphs and move it, you will find parameters placed under charts. They contain formulas that calculate the indicators for a particular block. We will describe them below.
Each of them drives information from a particular sheet. To do the same thing, you need to transfer data from the necessary systems:
- traffic — from Google Analytics;
- leads — from CRM, in our case, the analyst set the automatic transfer of leads from the Pipedrive system using Google App Script;
- data on the number of registrations for the webinar, articles — manually entered by employees, we will describe this below.
IMPORTANT: the dashboard will be updated automatically if you use the Run Reports option.
Number of leads and a forecast
This block contains information on the actual and forecasted number of leads. The data is driven here from the database that you can find in the CRM DB, so most formulas from here refer to this tab.
A fragment of the sheet with the raw lead database from the CRM
Just a few words about leads classification:
- MQL — these are all incoming leads and there are no open transactions with them;
- SQL — leads that the sales department considered as the high-quality: the visitor made a relevant request, it’s not spam, the sales rep could contact the person, etc.;
- Trial — users who connected a free trial period;
- Won — users who paid for the product.
We recommend you to read the article “How and why you need to classify leads” as you will discover more information about leads and their classification there.
If you move the graphs from this block, then you’ll see metrics and formulas that are required for the calculation.
We will give examples of formulas for specific cells below, but they are already written in this dashboard. To better understand how this works, we recommend reading the article “Basic Tips for Convenient Work in Google Spreadsheets”.
The actual number of leads is taken from the corresponding rows of the CRM DB tab. Leads qualifications are already written there. Here is used an array formula. It puts 1 if the lead, for example, MQL. 0 — if it’s not, and so on for each condition. Therefore, in the formula below, we are looking for “1” in the following range. The number of ones “1” will correspond to the MQL number.
To do so, you need to use a formula, for example:
=COUNTIFS(
'CRM DB'!$P$2:$P,1,
'CRM DB'!$H$2:$H,MONTH(today()))
The lead forecast is calculated by dividing the number of past days by the actual number of leads. We multiply the received average amount per day by the number of all business days in the month. To do so, you need to separately and manually enter the holidays.
The formula for the Forecast metric “considers” the column with holidays. If the holiday is on the weekend, it won’t take it into account, as the day off is not a business day. You can also write the same thing in the formula using a text. But it’s inconvenient, especially if there are a lot of holidays.
Here’s an example of calculation by MQL, SQL, Trial, and Won. The first symbol corresponds to Monday, the last one to Sunday; 0 — business day, 1 — day off. So, in the example below, the line “0000011” describes the week, where Saturday and Sunday are days off:
=C4/
NETWORKDAYS.INTL(date(year(today()),month(today()),1),today(),"0000011",$F$3:$F$6)*
NETWORKDAYS.INTL(date(year(today()),month(today()),1),EOMONTH(TODAY(),0),"0000011",$F$3:$F$6)
H-J cells are dedicated to the next calculation: the rate of implementation of the plan for leads. The actual number of leads that we previously calculated in a numerical value is divided by the plan for a month. The formula here is simple. The percentage of the plan implementation, for example:
=C3/270
The forecast for the rate of the plan completion, for example:
=D3/50
Data for the last day “Last day”. This block is needed to quickly notice if something goes wrong. For example, when you launched a dozen ad campaigns, and there are no leads.
The formula “understands” today’s date, subtracts 1 to get the previous day, and “looks” in the database if there were any leads for this date. Formula example:
=ifs(
day(today())>1,
COUNTIFS(
'CRM DB'!$S$2:$S,1,
'CRM DB'!$I$2:$I,(day(today()-1)),
'CRM DB'!$G$2:$G,year(today()),
'CRM DB'!$H$2:$H,MONTH(today())),
day(today())=1,
COUNTIFS(
'CRM DB'!$S$2:$S,1,
'CRM DB'!$I$2:$I,(day(today()-1)),
'CRM DB'!$G$2:$G,year(today()),
'CRM DB'!$H$2:$H,MONTH(today()-1)))
Visualization
The “filling” described above is usually not visible, as it hides under the visualization. If you want to make such graphs, go to Insert — Chart. After that, the chart editor will open in the right corner. It contains two sections Setup and Customize. You can set there cell ranges, chart type, and many more.
In our template, everything is already configured. But if you want, you can change the appearance of the dashboard. We also recommend you to read the Google support article “Add & edit a chart or graph.”
Webinars
Here we see the following information:
- date of the upcoming webinar;
- how many days left;
- how many registrations we had for the last event “Last”;
- and how many registrations we have for the future one “New”.
This information is driven from the Events Dashboard tab. The event marketer enters the information about events there. An example of a similar tab with data given to set an example:
How these data are counted on the sheet with consolidated information? The Query function takes the required range and leaves only the Webinar values. It also sorts the range from the largest to smallest and displays the first two dates as they have the largest value in the range.
=QUERY('Events Dashboard'!A2:D,
"select C,D where A = 'Webinar'
order by C desc
limit 2")
Date — the formula takes into account the last date that is entered in the table. The number of days before the webinar — considers according to a formula that subtracts the number of days remaining until the date of the event from the current day:
=IFERROR(DATEDIF(TODAY(),M4,"d"),0)
We recommend using IFERROR in all formulas. It helps to customize the value in case of an error. This is mainly done “for beauty”. Otherwise, a cell with an N/A value appears.
Content
In this tab we see the information divided by three types of content:
- last publication date;
- the number of days that have passed since the article was published;
- total number of publications;
- N% of a monthly plan on the number of published articles is implemented.
As KPI, you can specify, for example, the number of views, likes, etc.
Date — the formula determines the last completed row and transfers the date from it:
=INDEX('Blog Article UA'!B1:B,COUNTA('Blog Article UA'!A1:A))
The number of days since the last article was published is calculated the same as the date of the last webinar:
=DATEDIF(B15,TODAY(),"d")
If you move the chart with the number of publications and the rate of the plan completion, you’ll see the calculation itself.
For example, the number of articles for a blog is driven via the formula:
=COUNTIFS(
arrayformula(MONTH('Blog Article UA'!$B$2:$B)),month(today()))
The percentage of the plan completion we receive the following way. We divide the number of articles from the cell described above by the number of articles in the plan. In our example, the blog editor should publish 10 articles monthly. Therefore, the formula looks like this — the format is written there as a percentage:
=L14/10
If the last article was written long enough, first of all, the color of the cell becomes orange and only then red. So a manager can immediately notice the fact that the content is not updating regularly. You can do the same thing in the Format — Conditional formatting.
Traffic volume
Data from traffic databases to the blog and website are transferred here. The information gets there from Google Analytics. If you move the chart with the blog, you’ll see a table with the formulas:
The forecast and fact are the same for the past months as they are already finished. We enter these data here to track the dynamics of the visits. An example of a formula to get the actual number of visitors:
=IFERROR(INDEX('Blog DB'!$C$16:$C,MATCH(D22&D23,'Blog DB'!$A$16:$A&'Blog DB'!$B$16:$B,0)),0)
The forecast is calculated in the last column for the current month. The principle is similar to the lead forecast described above. We divide the average traffic by the number of working days and multiply by the number of all working days:
=INT(F27/
NETWORKDAYS.INTL(date(year(today()),month(today()),1),today(),"0000011",$F$3:$F$6)*
NETWORKDAYS.INTL(date(year(today()),month(today()),1),EOMONTH(TODAY(),0),"0000011",$F$3:$F$6))
Data on the actual number of leads are transferred by the following formula:
=IFERROR(INDEX('Blog DB'!$C$16:$C,MATCH(F22&F23,'Blog DB'!$A$16:$A&'Blog DB'!$B$16:$B,0)),0)
The number that indicates the traffic forecast is written twice in the last column. This is necessary as the graph becomes easier to read because you can see the “seek point”. As a result, you receive three numbers:
- a point that indicates a plan — it’s the same as the metric located in the cell below;
- the area that displays the forecast — in our example, it’s a light blue one;
- a purple area that displays the fact of the traffic volume.
The graph with the website visits creates the same way. But data drives from another sheet.
The described dashboard covers only part of the information that relates to marketing activities, leads, and traffic. So you don’t need to stop there. The next step is to build end-to-end analytics to understand the cost recovery of investments in ads.
