This is the last lesson from my Google Sheets for Marketers mini-course. In this lesson you’ll build an automatically updating reporting dashboard connected to your Google Analytics account.

Importing Google Analytics Data into Sheets will allow you some more flexibility for your data analysis and reporting (compared to viewing the data directly in Google Analytics). E.g. you can query and report data from multiple views. Schedule automated data updates. You’ll be able to manipulate the data with custom calculations. You can create visualizations and embed them on third party website. And last but not least you can control who sees your data and visualizations with Google Sheets’ sharing options.

The later one is especially helpful when you are working with other stakeholders or if you work in an agency and want to share something with your clients. E.g. you could pull only the most important metrics from Google Analytics and share them in a chart or other data visualization.

What you’ll learn:

Connecting Google Sheets to Google Analytics, Building an automatically updating dashboard

Google Analytics Spreadsheet Add-on

As for the other lessons you’ll find a solution for every step in this guide in the Google Sheets for Marketers workbook and I highly recommend to make a copy and to work along. 

Before we start you have to install the Google Analytics Spreadsheet Add-on. This little plugin will let you pull all your Google Analytics data into a Google Sheet. As such you’ll need a Google Analytics account. If you don’t have a Google Analytics account you can just read along for now and work with the sheet Report Raw Data in the above mentioned Google Sheets for Marketers workbook.

After you have installed the add-on open a new spreadsheet (or open above workbook).

In the top menu choose Add-onsGoogle Analytics Create a New Report. This will open a sidebar on the right side which will allow you to configure the data, which you want to pull into your spreadsheet. For our example we’ll choose the following settings:

Name: Report Raw Data

View: Whatever you prefer for your account

Metrics: Users, Goal Completions

Dimensions: Source, Medium, Country, Date

Segments: All Users


Afterwards click on the Create Report button. A new sheet called “Report Configuration” will be created with the details you just entered. You can change those details manually by changing the cells before you run the actual report. E.g write 90daysAgo into cell B4 to get data from the last 90 days. The sidebar assistant we used before is actually quite superficial in what it allows you to configure. You can have a look at the reference to see what else you can configure, but we’ll leave it for now.

If you want to create additional reports, just repeat the steps above. Every additional report configuration will appear in a new column to the right of the previous report configuration.

Before we run our report for the first time, we’ll set it to run automatically so our dashboard gets updated automatically. In the top menu click on Add-onsGoogle Analytics Schedule Reports. Check Enable reports to run automatically and click on Save.

Now it’s finally time to run the report. Click on Add-onsGoogle Analytics Run Reports to pull your Google Analytics Data into the designated sheet.

Preparing the data

The following will describe the basis and first steps on how to build a dashboard. The dashboard itself we are building won’t be too sophisticated. The following guide rather provides basics so you know how to work with the data to build dashboards yourself.

If you don’t know which metrics you should be reporting for your business or clients check out Kaushik’s great DMMM framework. Or if you work in an ecommerce business my guide on ecommerce marketing KPIs.

You have to do some data preparation first to build the dashboard. However it’s nothing too complicated. First open a new sheet. You can’t work directly in the Report Raw Data as it will get updated automatically and everything in it gets overwritten. In above solution workbook we will be using the Solution – Dashboard sheet.

We are building an interactive dashboard, which you can control via dropdown menus. As such we are going to need the options you can choose in each dropdown menu. You will use the UNIQUE formula for this. The UNIQUE formula pulls all uniques values from a specified range.

Type in the following:

Medium in cell N3

Country in cell O3

Month in cell P4

=UNIQUE(‘Report Raw Data’!B16:B) in CellN4

=UNIQUE(‘Report Raw Data’!C16:C) in CellO4

=UNIQUE(‘Report Raw Data’!E16:E) in CellP4

Next we’ll add the drop down menus via data validation. Click on cell D4 and then choose Data Data Validation in the top menu. In the box next to “List from a range”  write Solution – Dashboard’!O4:O. Do the same for cell E4, however write ‘Solution – Dashboard’!N4:N into the box.

This gives us two nice dropdown menus to control the dashboard later on.

The charts of the dashboard won’t be based on the actual data from the Report Raw Data sheet, but on staged data, which is easier to manipulate with the dropdowns. Type =date(,P4,1) into cell R4 and copy it down until R7. The formula pull the months number and converts it into a data format. Format cells R4 to R7 show the month only via FormatNumber More Formats More time and date formats.

Next write =D$4 in S4 and copy it until T7. This will pull the data from the drop down menus. Last we will use SUMIFS formulas to only pull the users, which fit to the month, country and medium in that row. As such write

=SUMIFS(‘Report Raw Data’!F$16:F,’Report Raw Data’!C$16:C,S4,’Report Raw Data’!E$16:E,P4,’Report Raw Data’!B$16:B,T4

into cell U4 and copy down until U7. If you need a refresher on SUMIFS, check out the second lesson of the course!

Put =UNIQUE(‘Report Raw Data’!B16:B836) into cell W4 to get all the mediums and =SUMIF(‘Report Raw Data’!B$16:B$836,W4,’Report Raw Data’!F$16:F$836) into X4. Copy it down until you have reached the last row with a medium in it.

Last make the text colour of columns N to X white.

Building an interactive dashboard

This is actually the easy part (have a look at lesson three about data visualization, if you are not sure what to do here). First select cells W4 to X12 and insert a pie chart to show the medium split.

Next insert a line chart in the sheet in cell C13. As X-Axis choose R4:R and as Series U3:U (If you are not sure how that works, check out lesson 3). In cell C8 type =”User from “&D4&” acquired via “&E4 to create an interactive headline for the chart.

Now try playing around with the dropdown menus to see how the headline and the chart changes. Obviously you can make the whole thing a lot prettier by styling it a little bit (for some inspiration you can take a look at what I did in the workbook).

Last you might want to share the dashboard with somebody. You can do that either via the green share part in the upper right corner or you can actually even publish it publicly via File Publish to the web.

Obviously this is a very simple use case on what you can do when pulling Google Analytics Data into Google Sheets. I.e. you could build a lot more advanced dashboards (even though I would recommend Google DataStudio for that) or do more in-depth analysis with the data itself. Either way it is important to know how to pull the data in the first place, which is shown in the above first part.

This is the fourth lesson of my Google Sheets for Marketers mini-course. A typical task every marketer has to face at one point or the other is to forecast things such as sales, revenue, conversions or something similar. Often this comes up when you have to create a robust marketing plan. Or when you have to present forecasts to superiors and stakeholders to secure budgets.
 
One challenge that usually comes up during the forecasting process is how you can predict the numbers as accurate as possible without using too complicated and time intensive methods.
 
This tutorial describes how to use simple linear regression (no worries, no math needed. There is a simple formula in Google Sheets for that) and formulas to forecast marketing channel revenue. As usual I’ll go through everything step-by-step and explain it all. But I recommend having open the mini-course workbook (please make a copy) to work along to really understand everything.

 
What you’ll learn:
The basics of the Google Sheets QUERY function, FORECAST formula.

 

The task

In the past lessons of this course we analyzed the data from three advertising channel and learned that Twitter Ads are performing quite well. You think it would be a good idea to raise the budget of those ads to improve revenue. However you first have to convince your stakeholders and superiors of this to secure the budget. Part of this you have to forecast the potential additional revenue to prove the impact of more Twitter Ads budget. As such the task is the following:
 
If we rise our investments by 20% compared to last year, what will be the revenue from Twitter Ads on a monthly basis for the next year?
 
There are two implications from this question:
 
  • It won’t be enough to give a total estimate for the next year, but we have to give a forecast for each individual month.
  • We have to forecast at least 12 months into the future.

Preparing the data

Again I highly recommend to work along in the workbook, you’ll also find a solution sheet to this lesson there. As a first step we want to get the data we are going to work with into our worksheet. In our case the Twitter Ads data.
 
Of course we could simply copy the data from the Raw Data sheet and delete everything besides the Twitter Ads data. However this approach would get messy very fast, when working with large Data Sets. Instead we are going to work with the Google Sheets Query function. This function is probably one of the most powerful functions overall. It is kind of a pseudo SQL, which let’s you query, manipulate and analyze very large data sets in Google Sheets.
 
We won’t go into depth here and you’ll only learn a simple case on how to use the formula. However believe me, knowing this function will probably make you stand out among 97% of all marketers and it will seem like magic for those who don’t know it.
 
Put the following formula into cell A1 in your new sheet:
 
=QUERY(‘Worksheet – Raw Data’!A1:J118, “SELECT A, B, H, I WHERE A = ‘Twitter Ads'”,1)
The function =QUERY() has three input parameters. The first one ‘Worksheet – Raw Data’!A1:J118 defines the dataset you want to work with and which you want to query. In our case all the raw data. “SELECT A, B, H, I WHERE A = ‘Twitter Ads'” is the actual query. What you are doing here is you are selecting columns A, B,H and I. The WHERE statement introduces a condition and lets you only select those rows, where the cell in column A equals Twitter Ads. The last parameter 1 simply indicates that there is a header row, which we want to display as well.
 
As such we build a simple very fast filter to only select the data we need. Feel free to play around a little bit with the Query function. You can add more conditions with an AND behind the WHAT the statement. E.g. SELECT A, B, H, I WHERE A = ‘Twitter Ads’ AND H >1000 would give you the same as above, however only those rows, which have a revenue higher than $1000.
 
We are almost done with preparing the sheet. Just add Cost Forecast and Revenue Forecast as headers in cell E1 and F1. Write Twitter Ads in A41 and =B40+31 in B41. Copy both down until row 52.
 

The actual forecasting

We will have to do two things here. Plug-in the future potential budgets and forecast the resulting revenues.
 
The first one is quite easy. As the task states we will have a 20% higher budget compared to the last 12 months. So we will simply multiply the costs of each of the last 12 months with 1.2 in order to get the investments for the future 12 months. As such write the following into E41 and drag it down for the other months:
 
=D29*1.2
 
Next we’ll do the revenue forecasting itself with linear regression. It is easier than you might expect as there is a formula in Sheets exactly for that. Write this into F41 and again drag it down for the other future months:
 
=FORECAST(E41,C$2:C$40,D$2:D$40)
 
What the FORECAST formula does is it predicts through linear regression a future value by using existing values.
 
In our case the predicted value is revenue (y-value) for a given cost (x-value). The existing revenue values (y-values) are in cells C2:C40 and existing cost values (y-values ) in cells D2:D40.
 
I added $ dollar signs in above formula in order create an absolute references. So when you drag the formula down it won’t change the referenced existing values.
 

Visualization

You’re done! Or at least almost. Technically you are done already – you predicted revenues for Twitter Ads for the next 12 months. However nobody wants to read a boring table and drawing insights from that table is very difficult. So the last step to take is to build a chart on top of your analysis.
 
Insert a chart from the menu: Insert > Chart and change the chart type to Linear chart (Need a refresher on charts? Check out lesson 3):
 
Add the following two series:
 
Series 1 – C1:C52
 
Series 2 – F1:F52
 
and as X-axis: B1:B52.
 
You’ll probably notice now that there is an ugly and confusing gap between the lines of the historic data and the forecast. In order to get rid of that add the following to cells F40:
 
=C40
 
This will level the start of the forecasts to the actual revenue.
 
That’s it! Obviously you can change the colors of the lines (which I did in above screenshot and the example sheet) to make it look prettier and more organized.
 

Limitations

Certainly above is a very simple solution for forecasting revenue as there are a lot more advanced techniques such as the moving average forecasting method. We are also not counting in any other influencing factors such as seasonality, competition etc..
 
Nonetheless above is a valid demonstration on how a simple data science technique (linear regression in this case) can be applied to real life marketing challenges by everyone. You could use above methods for all kind of marketing forecasting, e.g.:
 
  • How will the the next days outside temperature affect my sales?
  • How will the number of blog posts affect my website traffic?
  • Will conversions go up or down during winter months?
 
In the last lesson of this mini-course we will build a dynamic reporting dashboard, which pulls its data automatically from Google Analytics. So be sure to don’t miss out lesson number 5.

 
This is the third lesson from the Google Sheets for marketers mini course. In this chapter you’ll learn how to implement and customize charts to visualize your insights. As the famous saying goes “a picture is worth a thousand words.” Visualizing data will be useful in itself as it makes finding actionable insights in huge data sets a lot easier. Furthermore it is essential when you want to define and communicate next step recommendations based on the data to other stakeholders or your clients.

 

What you’ll learn:

Regular charts, combo charts with two axis, chart trendlines, SPARKLINE, custom change indicators, interactive charts.

 

 

Visualizing Budget Utilization

As for the other lessons this workbook contains all exercise data (please make a copy to work a long).
As an easy start you are going to visualize the 2018 budgets vs actual costs for the three advertising channels: Facebook Ads, Google Ads and Twitter Ads.
Select cells A1 to C4 in sheet Solution – Charts. Now choose Insert → Chart in the top menu.
 
 
You’ll get a simple column chart comparing budget utilization for the different channels. If you want to you can customize it further by clicking on the tab Customize on the right side in the Chart editor. E.g. click on one of the red bars and choose another color than red in the dropdown menu in the chart editor on the right side.
 
This is basically how it works for all elements of the chart. Click on it and customize the style on the right side. However we won’t focus on these fundamentals in the following, but will rather do some little bit more interesting stuff in this lesson (if you want to learn the fundamentals, check out Google Sheets help).
Btw. if you ever wondered, which chart you should choose in general have a look at this awesome infographic from A. Abela.

 

Average Order Value vs. Conversions

As we learned in the past lessons the Facebook Ads performance has been quite bad due to low Average Order Values and the low number of Conversion. As such we want to visualize this to show the development over time and to potentially communicate this to other stakeholders. You are going to use a so called combination chart for this. In a combinations chart you can display two series using different formats (e.g. bar chart and line) as well as two axis.
 
First insert a pivot table by clicking into any cell in the Worksheet – Raw Data tab and choosing Data → Pivot table from the top menu. Select Existing Sheet and write ‘Solution – Charts’!A21 into the empty box before clicking Create.
 
Choose the following settings in the pivot table editor:
Rows: Month (Deselect Show totals)
Values: Conversions, Average Order Value
Filters: Channel and only select Facebook Ads
 
(If you are not sure what to do here or need a recap check out my first lesson on pivot tables)
 
Select cells A21 to C60 and insert a chart via the top menu (Insert Chart). Now on the right side in the first dropdown menu of the chart editor under Chart type choose Combo chart. It is the third item in the first row.
 
 
Delete Month as a series by clicking on the three dots next to Month on the right side and choosing Remove. Instead choose Month as the X-Axis by clicking in Add X-axis above Series and selecting the range A21:A60. Click OK.
 
 
You have your first combo chart! However Average Order Value is a little bit hard to read as the left axis shows a number (not currency) for both Conversion and Average Order Value.
 
As such click on the red line. On the right side below Axis choose Right axis instead of Left axis. This will add another vertical axis on the right side showing currency values.
 
 
While it’s quite clear to see that there is a strong drop in Average Order Value, it’s unfortunately not as clear for Conversions.
As such click on one of the blue bars add a trendline by checking the box next to Trendline on the right side to visualize it better.
 
 
You now have a ready chart, which you could use to visualize your insight that Facebook Ad performance is going down. As such it is ready to be copied into a slide deck, email or wherever you need it.

 

Comparing Facebook Ads year-over-year performance

 
Sometimes you don’t want to build a full chart, but rather want a quick visualization of the data in Google Sheets itself. E.g. in our case we quickly want to compare the year-over-year performance of the Facebook Ads. Write the following in the respective cells:
 
2016 in cell A14
2017 in cell A15
2018 in cell A16
2019 in cell A17
Seasonality in B13
 
In cells B14 to B17 we will use the SPARKLINE formula to visualize number of Conversions over the months in the respective years. Sparklines are mini-charts which exist in a cell itself. They are perfect to visualize trends or seasonality. Write the following formulas:
 
=SPARKLINE(B22:B32) in cell B14
=SPARKLINE(B33:B44) in cell B15
=SPARKLINE(B45:B56) in cell B16
=SPARKLINE(B57:B60) in cell B17
 
 
We also want to visualize the trend in year over year growth in the most recent month April. As such write the following in the respective cells to calculate year over growth for April of each month:
 
YoY April in C13
=B36/B24-1 in C15
=B48/B36-1 in C16
=B60/B48-1 in C17
 
Select cells C15 to C17 and in the top menu click on FormatNumberMore Formats Custom number formats. In the popup paste the following into the box and click Apply afterwards:
 
[color50]0% ▲;[red]-0% ▼;[color40]0% ▬
 
 
What this does is it will apply the colour 50 (=green) and the symbol ▲ to all cells with a positive value, the colour red and the symbol ▼ to all cells with a negative value and finally the colour 40 (=yellow) and the symbol ▬ to all cells with 0% as value. Those symbols are actually interchangeable with whatever symbols you like. E.g. you could also use ↑ , ↓ and ↔ instead. Same goes for the colours.

 

Building an interactive chart

 
In the last lesson you built a small tool for comparing the performance metrics for two advertising channels. We’ll use that to build a dynamically controllable chart.
 
Write the following formulas in the respective cells:
 
=B8 in cell B10
=C8 in cell C10
=B9 in cell B11
=C9 in cell C11
=A9&” for “&B8&” is “&round((B9/C9),1)&” times the “&A9&” for “&C8 in cell A11
 
 
Even though it looks fancy the last formula does nothing else besides linking different cells and text (everything between two quote marks is considered text) with each other. Select cells A11 to C11 and change the Text color to white.
 
Next select cells A10 to C11 and insert a chart via the top menu. Switch rows / columns by checking the box next to it in on the right side in the chart editor.
 
 
Now go to the Customize tab on the right side of the Chart editor and open Legend. Choose Top in the drop down menu for Position.
 
 
Now try choosing something different in cells A9, B8 and C8. The chart including the legend will update instantly and dynamically! Obviously styling and formatting could be improved. However you get the idea and can imagine how interactive dashboards could be build with these techniques.
 
We will actually use some of these to build an interactive dynamic performance reporting dashboard (which you can share directly with clients or other stakeholders) at the end of this course in lesson 5.
 

Google Sheets formulas for marketing - TRANSPOSE

This is the second lesson from the Google Sheets for Marketers mini course. In this lesson I’ll walk you through the most important Google Sheets formulas for analyzing marketing data as well as some useful functions such as conditional formatting and filters. You’ll even build a small tool for comparing performance metrics for different advertising channels.

What you’ll learn in this lesson:

Conditional Formatting, Filters, Data Validation, AVERAGE, MEDIAN, MODE, MAX, MIN, COUNTIF[S], AVERAGEIF[S], SUMIF[S], TRANSPOSE, VLOOKUP, INDEX, MATCH, calculating ROI.

 

 

Preparing the data

This is the Google sheet with the practice data.

In our last lesson we generated some first insights from the ad channel data set with the help of pivot tables. One of the insights was that Facebook ads were not performing as well, which is why we are going to focus on those for this lesson to analyze them further.

First make a copy of the worksheet (it’s always a good idea to keep a backup of the original data when manipulating it). Next select all data in the new sheet and in the menu click on DataCreate a filter. You can now click on the three bars next to Channel in cell A1. De-select Twitter Ads and Google Ads by clicking on them respectively. This will allow us to focus on the Facebook Ads data only without distraction from the data from the other advertising channels.

Google Sheets formulas for marketing - filter

As you learned in the first lesson revenue and profitability of Facebook Ads are going down. Since costs generally stayed the same you are guessing it might have something to with the Average Order Value. In order to be sure we are going to apply a coloured heatmap to the values to see if there is a downward trend. Select all cells with Average Order Value data and click on Format Conditional Formatting in the menu.

While you could colour the cells depending on if the are not empty or contain a certain value we want to colour them on a scale from min to max value. As such choose Colour Scale on the right site. Under Preview choose the scale from green to yellow to red, which will colour the lowest values green, mid range values yellow and high values red (you could choose different colours here, but we are going to leave them as they are for now).

Google Sheets formulas for marketing - conditional formatting

As you can clearly see, Average Order Values dropped significantly at the end of 2017 and as such we got our first insight here (in a real life scenario I would e.g. talk to one of the Social Media PPC managers now to see what might have happened at the the end of the year., if I don’t know it myself).

 

Calculating the typical value of the data set

When working with marketing data you will often have to deal with large data sets. It’s often difficult to make sense of those data sets due to the sheer amount of metrics. As such it’s helpful to summarize the data by getting the typical values, min/max values and others to get a feel for the data.

There are three different ways you usually use to summarize the typical value for a data set.

The mean or average is simply the sum of the numbers in in the data set divided by the number of values in the data set. Write =AVERAGE(G41:G79) into N41 to get the average number of Conversions per month.

The median is the 50th percentile of the data set. This means one half of the data is below the median and the other half is above the median. Write =MEDIAN(G41:G79) into N42 to get the median for Conversions.

Last is the mode of the data, which is simply the most frequently occurring value in the data set. Write =MODE(G41:G79) into M43 to get the median for Conversions.

As such on average there were 53.2 conversions via Facebook Ads each month, around one-half the time there were fewer than 52 conversions and the most frequently occurring number of conversions per month were 49.

None of the three is the best per se. In most cases you would take either the mean or the median. If you have extreme values they tend to distort the mean and the median is a better choice as a summary of a typical data value. However the median might throw out important important in other situation. So as a general rule of thumb use the mean, if no extreme values are present and the median otherwise. In our case no extreme values are present and we can focus on the mean

Finding the smallest and largest values

This is actually an easy one. Simply type in =MAX(H41:H79) in N46 and =MIN(H41:H79) in N47 to get the largest and smallest values respectively.

Google Sheets formulas for marketing - mean

 

Calculating ROI

First we’ll calculate ROI for each month. As Return on investment = Revenue / Investment you can put =((H41-I41-J41)/(I42+J42)) in K41 and drag it all the way down to K79. You now have the ROI for each month.

In order to get the total ROI for all month write =(SUM(H41:H79)-SUM(I41:J79))/SUM(I41:J79) into Cell N48. This formula summarizes the revenue first and then subtracts the sum of all costs.

Google Sheets formulas for marketing - mean

In order to count the number of times we had a positive ROI you can use the COUNTIF formula. This formula will count values depending on a certain criteria. In our case the criteria will be that the ROI is larger than 0. So write =COUNTIF(K41:K79,”>0″) into N48.

Google Sheets formulas for marketing - ROI total

 

Putting the metrics into context

Above we calculated several performance metrics for Facebook ads. However they are quite useless, if we don’t put them into context (a general rule for marketing analysis: Never just dump metrics out there, always put them into context and make them actionable). In our case the context would be to compare the Facebook ad metrics with Twitter and Google ad metrics. With the exercises we did above we have the tools to do exactly that.

First select the Google ads and Twitter ads from the filter in A1 as well so you can see the data for all advertising channels. Next calculate ROI for those two channel by dragging the ROI formula into the empty cells in column K.

As preparation write the following headlines into the corresponding cells:

Facebook Ads in cell M3

Google Ads in cell  M4

Twitter Ads in cell M5

Average CPC: in cell N2

Max: in cell O2

Number of positive ROI months: in cell P2

Total ROI: in cell Q2

We’ll calculate the average CPC for each channel first. You can use the AVERAGEIF formula for this. The AVERAGEIF formula checks if a cell in the criterion range matches a certain criteria and will only average the values of the row with matching criteria. E.g. put =AVERAGEIF(A2:A118,M3,F2:F118) into N3.  Sheets now checks if the cell in specified range A2 to A118 matches the value of M3 (Facebook Ads) and will only calculate the average of the values in range F2:F118 of the rows with matching criteria.

Put $ in front of the range row specifiers and drag the formula into N4 and N5 to do the same for the other channels (the $ will keep the range the same).

Google Sheets formulas for marketing - AVERAGEIF

Well now do something similar to find each maximum Average Order Value. As such put =MAXIFS(C$2:C$118,A$2:A$118,M3) into O3 and drag it into O4 and O5. Keep in mind that the order inside the formula is different. Unfortunately that’s the case for most *IF formulas. So alway pay attention to the hints in the upper left corner, which give specific instructions here.

Next we’ll use the COUNTIFS formula to calculate the number of positive ROI months. COUNTIFS allows several criteria (as opposed to COUNTIF). Put =COUNTIFS(A$2:A$118,M3,K$2:K$118,”>0″) into P3. This formula will only count rows which match criteria M3 (Facebook Ads) in column A as well as has values >0 in column K.

Drag the formula down to get the counts for the other channels.

Google Sheets formulas for marketing - COUNTIFS

Last we’ll calculate total ROI. For this we’ll use the SUMIF, which works similar to the AVERAGEIF formula. As such it will only sum the values in a range if a certain criteria matches the criteria range in the same row. It is a pretty long formula to calculate the ROIs. However you are basically summing up Revenue and subtracting the sums of Advertising Costs and Other Costs first and then dividing that by the sums of Advertising Costs and Other Costs. Put

=(SUMIF(A$2:A$118,M3,H$2:H$118)-SUMIF(A$2:A$118,M3,I2:I$118)-SUMIF(A$2:A$118,M3,J$2:J$118))/(SUMIF(A$2:A$118,M3,I$2:I$118)+SUMIF(A$2:A$118,M3,J$2:J$118))

into Q3 and drag it down to Q4 and Q5.

Google Sheets formulas for marketing - SUMIF

Your new table comparing performance metrics from the different advertising channels is technically done. However it is kind of hard to read. It would would be better to have the different channels as rows and the metric titles as columns. The can be easily done with TRANSPOSE, which will interchange rows and columns. Put =TRANSPOSE(M2:Q5) into M8 (you could also use the paste function of the same name instead, however that would mess up the formulas).

Even though the format is better now it is still hard to compare the performance metrics on first sight. Some colour coding would be nice… Luckily you already learned how to do heat maps in the beginning. Select cells N9 to P9 and click in the menu on Format Conditional Formatting. Choose the color scale on the right side with green to yellow to red. Do the same for cells N10 to P10, N11 to P11 as well N12 to P12. However for those three interchange green and red as we want green to indicate value where the respective channel is better than the other channels.

Google Sheets formulas for marketing - TRANSPOSE

The final result (and insight) shows us that Twitter Ads are actually comparing quite well in all metrics compared to the others even though the Average Order Value is quite low. Facebook Ads on the other hands perform quite bad in all metrics compared to the other channels. This might indicate that you should shift some budget from Facebook Ads to Twitter Ads.

 

Preparing the data for charts

The last part of this lesson will prepare the data for building some charts (and a simple reporting which you could use to send out to other stakeholders or clients). As such we will work in the sheet Solution – Charts.

There is actually another new sheet called Worksheet – Budgets/Costs, which contains the budgets and actual costs of several 2019 advertising channel.

As we are analyzing Facebook Ads, Twitter Ads and Google Ads more closely you obviously don’t want to have all of the Budget/Costs data in your Solution – Charts sheet.

You could just copy the relevant data from the former sheet to the later one. However in very long list it can be very toilsome to find relevant data. There is a smarter way called VLOOKUP, which will find relevant data for you based on a key.

Prepare your sheet by writing the following in the cells:

Facebook Ads in cell A2

Google Ads in cell  A3

Twitter Ads in cell A4

Budget 2018 in cell B1

Actual Cost in cell C1

Next write

=VLOOKUP($A2,’Worksheet – Budgets/Costs’!$A$2:$C$10,2,FALSE)

in Cell B2.

What this does is that VLOOKUP searches for Key A2 (Facebook Ads) in range A2 to C10 in Worksheet – Budgets/Costs and returns the cell of the 2nd column of the row where it finds the key. FALSE only says that the range is not ordered in any particular way. Past the formula in cell B4, B6, C2,C4 and C6 as well. Since we are looking for the actual costs in column C you have to replace the 2 in the formula of C2,C4 and C6 with a 3 to return a cell from the third column.

Google Sheets formulas for marketing - VLOOKUP

Building a performance metric comparison tool

Two other useful formulas to find data are INDEX and MATCH. Those two combined are a powerful tool to find data in large data set. INDEX gets a value at a specified location in a range of cells based on the  numeric position. E.g. putting =INDEX(A1:C4,2,3) in any cell in the sheet Solution – Charts will get you the cell in the second row and third column of the range A1 to C4 (in this case that would be $24,310).

MATCH will find the numeric position of an item in a list.  E.g. putting=MATCH(“Google Afs”,A2:A4,1) in any cell in the sheet Solution – Charts will get you the position of Google Ads in the list A2 to A4. The last 1 indicates that we are looking for an approximate match (which is why it ignores the typo) rather than an approximate match (in which case we would use  0).

We will use those two formulas two build a small dynamic performance metric comparison tool. First write

Total ROI: in cell A9

Twitter Ads in cell B8

Google Ads in cell C8

Copy this formula into B9 and paste it into C9 as well:

=INDEX(‘Solution – Functions’!$N3:$Q5,MATCH(B8,’Solution – Functions’!$M3:$M5,0),MATCH($A9,’Solution – Functions’!$N2:$Q2,0))

It is actually a simple index function, however row and column indicators are replaced by match functions. So MATCH(B8,’Solution – Functions’!$M3:$M5,0) looks for the value in B8 (=Twitter Ads) in range M3 to M5 of the Solution – Functions sheet and gives back its position (=3) while MATCH($A9,’Solution – Functions’!$N2:$Q2,0) looks for the value in A9 (=Total ROI:) in range N2 to Q2 of the Solution – Functions sheet and gives back that position (=4). The INDEX function takes the positions and uses them as row and column indicators for the specified range respectively.

Google Sheets formulas for marketing - INDEXMATCH

The cool thing is now, that if you would e.g. change Twitter Ads in cell B8 to Facebook Ads it would update the value in C9 automatically!

However every proper tool has some dropdown menus. We can add those with data validation. Data validation tells Sheets that only certain values are allowed in a cell. Select cells B8 and C8 and right click on them. Choose Data validation… In the empty field next to List from range paste this: ‘Solution – Functions’!M3:M5. That is a list of the three advertising channels we are analyzing. Click on Save.

Google Sheets formulas for marketing -  Data Validation

Do the same for cell A9 by right clicking on it, choosing Data validation… and pasting ‘Solution – Functions’!M9:M12 into the empty field. Save.

You can now use the dropdown menus to choose the comparison metric as well as the channels you want to compare. We prepared everything in this sheet for the next charts lesson. Based on the data we will create some charts, modify them to look better and I’ll show you how they can be updated dynamically to build some simple beautiful reports.

 

Google Sheets pivot tables for marketing

Pivot tables are one of the easiest and quickest tools to analyze marketing data and to draw some first actionable insights. As such they shouldn’t be missing in the basic skill set of every marketer. This is an introductory session to pivot tables. 

What you’ll learn in this lesson:

Basics of pivot tables, different aggregation options (SUM, AVERAGE, %, etc.), pivot groupings, calculated fields.

 

 

Examining marketing channel performance with pivot tables

First of all make a copy of this workbook. It contains the raw data the below example is based on as well as the solution sheets. Obviously you can just read through this guide. However I highly recommend to make a copy and work along!

For this case we will use the reported data for three paid advertising channels (Google Ads, Facebook, Twitter) from a t-shirt ecommerce store as a basis for the analysis. Obviously this is only an example and you could use pivot tables for analyzing other data such as sales revenue from different regions, customer orders or cost by location.

The example data contains the last three years and includes: Average Order Value, Impressions, Clicks, CPC, Conversions, Revenue, Advertising Costs and Other Costs segmented by month and advertising channel.

Disclaimer: The values for the advertising channels are completely random and should not be seen as representative for one channel or the other.

In this lesson you’ll do the following things:

  • Examine absolute revenue and revenue share by channel and month
  • Describe the influence of seasonality and overall trend
  • Analyze profitability and order values based advertising channel

 

Analyzing absolute revenue and revenue shares

In this first part you’ll learn how to get some first insights on how each advertising channel is performing during the respective months. We’ll start by looking into absolute revenue numbers and then analyze what months and channels drive most of the revenue.

How much revenue does each channel generate?

Click anywhere in one of the cells containing data in the Worksheet – Raw Data sheet. Afterwards click in the menu on Data and then Pivot table… to prompt the pivot table pop-up. Google Sheets should have correctly guessed the range which contains data. So simply click on create to create the pivot table in a new sheet.

Googel Sheets pivot tables for marketing

In the new sheet you’ll see the pivot table as well as the table editor on the right site, which you can use to build the table. We’ll start by clicking on Add next to Rows and adding Channel there. Now click on Values to add Revenue. We already know now what the lifetime revenue of each channel is:

Google Sheets pivot tables for marketing - sum

However it would also be interesting to know how much revenue on average each channel does each month. For this simply click on the dropdown SUM below Summarize by on the right side and choose AVERAGE instead.

Next we want to know what the total revenue is per months. So set AVERAGE back to SUM, click on Add next to Columns and add Month.

Google Sheets pivot tables for marketing - months

What’s the revenue share of each channel compared with advertising costs?

Even though knowing the absolute revenue of each channel is already helpful to get a general idea of the channel performance, looking at shares or percentages is often more insightful.

So remove Month by clicking on the X next to it. In the Revenue tab click on the Show as dropdown next to the Summarize by dropdown and chose % of column instead of Default. Next add another Value called Advertising Costs and do the same.

Google Sheets pivot table for marketing - % of column

You just unlocked your first small marketing insight!

While Facebook ads account for roughly 30% of advertising costs they only account for 18% of revenue. The other two channel do a lot better here and as such there is definitely room for optimization or even a shift of budget. But we’ll look more into this in the later sessions.

The influence of seasonality and overall time trends

While we looked into channel performance above we’ll now examine the performance of individual years and months more closely. As preparation switch the pivot table back to Month as Columns and Revenue as Values.

Which months are on average the highest grossing?

Right click on any month in the month header and choose Create pivot date group… –> Month. This will group the months of each year (e.g. February ‘19 with  February ‘16, February ‘17 and February ‘18).

ivot table for marketing - month group

Next in the Revenue tab switch Summarize by from SUM to AVERAGE. This gives us the average revenue for each kind of month. Obviously this would already be enough to answer above questions. However it’s a lot easier, if we sort the months by revenue descending. This can easily be done by choosing Descending in the drop down menu below Month and Order as well as AVERAGE of Revenue in the Sort by dropdown.

ivot table for marketing - month desc

This gives us our second little marketing insight: Not surprisingly for a t-shirt retailer, summer months are the strongest revenue wise.

How is the revenue performance year-over-year?

Ungroup the months and create a pivot group by Year instead. Also switch the fields back to default (Columns: sorted by Month, Values: SUM of Revenue):

Since we want do look at year-over-year growth and 2019 is not done yet, we are going to filter it out. Simply click on Add next to Filter, choose Month and un-select all 2019 months (January, February, March, April).

Write =C3/B3-1 in cell C4 and =D3/C3-1 into cell D4 respectively to calculate the growth rates.

ivot table for marketing - YoY

This results into our next insight: revenue growth rates are actually dropping!

Examining profitability and order values

Last thing we want to do is to look at the profits and average order values of each advertising channel.

What is the profit per channel for each year?

First unfilter the 2019 months since we also want to have a look at the most recent months. Also click the X next to Revenue to delete the field. Luckily Google Sheets pivot tables allow us to add calculated fields.  And since profit =revenue-cost we can simply click on the Add button next to Values choose Calculated field and add the following formula (each item in the formula equals the column names of the raw data):

=’Revenue’-‘Advertising Costs’-‘Other Costs’

Google Sheets pivot table for marketing - calculated field

This will give us a profit field and another insight: In addition to the year-over-year drop in revenue, Facebook Ads are dropping in profitability.

How are the average order values per advertising channel distributed?

First of all delete the calculated profit field and add Average Order Value as row as well as Value and Channel as column. For the later one choose % of column as Show as.

Above we already grouped by date, however it is actually also possible to choose custom groupings. Just click on one of the Average Order Value values and click Create pivot group rule… . Set Interval size to $10. This leads to dividing Average Order Value into 10$ buckets. E.g. this means in our case 23% of all months Twitter Ads had an average order value of $40 – $50.

Google Sheets pivot table for marketing - custom bucket

As such our last insight is, that Facebook as well as Twitter Ads have a significantly lower average order value than Google Ads.

That’s it, you are done! You learned all important pivot table functions and how to use them to gain some first insights from marketing raw data. In the next lesson we will further analyze the data with Google Sheets formulas and functions.

 

RFM analysis Header

You are working as a digital marketer or digital marketing analyst in a company. The company has several thousand online customers, but beyond some top level metrics they don’t have any customer-focused insights. As such you might have the typical Google Analytics reports (e.g. which sources are customers coming from), know what products are purchased most often and what the average order value is. But what your stakeholders lack is a better understanding of the customers in order to drive marketing & content decisions and strategies for acquisition, growth and retention. That’s why they come to you asking to share some general “who are the customers?” insights with them.

Even though this probably one of THE typical asks for marketers and analysts it might lead to some inner stress levels rising due its vagueness. Above question can mean everything and anything from rather top level customer personas to in-depths customer and purchasing behavior reports.

So imagine you had an analysis that would not only group all customers into different clusters but also allow you to develop and present to your stakeholders targeted strategies for each cluster based on its characteristics. All leading to an optimized marketing & sales approach and in-depth customer insights for improved conversion rates. And the best of all is all you need is the transactions data of your company.

That’s where the RFM analysis comes into play. It’s a simple to understand and easy to apply data analysis model to segment your customers. The following is a step-by step tutorial on how to create such a model in Google Sheets. Furthermore it shows you specific strategy recommendations for each of the key customer clusters (and if you want to get started quickly you can plug-in your data into the provided workbook to use it as a template in order to segment your clients right away).

Get access to the RFM analysis example & template and work along.

The recency frequency monetary (RFM) analysis

The recency frequency monetary analysis (RFM analysis) is a classic analysis model for behavior based consumer segmentation. It segments customers by scoring them on a 1-5 scale in regards to how recently, how often, and how much they have bought (different scales may be used, however the 1-5 is usually the one used in a commercial context). Those three factors can then be used to predict how likely it is that a customer will purchase (or for some business models engage, e.g. apps) again.

Furthermore those segments can be grouped into clusters allowing you to develop targeted individualized content and promotion strategies which are more likely to convert with the customers in each cluster. Because each cluster is assigned a monetary value high value high-value customers can be identified easily and marketing spend can be allocated accordingly.

This guide gives a RFM Analysis example and shows step-by-step how to conduct a recency frequency monetary analysis with your data in Google Sheets. Afterwards it explains the content and promotion strategies, which can be applied to each individual cluster.

As usual I recommend working along in the above provided Google Sheet in order to understand everything. The workbook contains two sheets: Sample Data includes dummy data representing a transaction list (this could be anything from software sales to ecommerce store orders) and RFM Model which includes the solution to the RFM analysis example this guide is working towards.

If you are in a hurry: It is possible to replace to the dummy with your own data to use the RFM Model as a ready made template.

Creating a RFM analysis example step by step

First step is to prepare the data and to calculate the following metrics for each customer:

  • The most recent transaction
  • The number of transactions per month for each customer
  • The average amount purchased each month by each customer

In the following we will work in the sheet Tutorial of the provided workbook.

We’ll start by finding out the number of transactions each individual customer had. This is easily done by copying the formula =COUNTIF(‘Sample Data’!B:B,A2)from B2 down to B3403. It will count how often the value from the referenced cell occurs in column B from the sheet Sample Data.

Next step is to identify the most recent transaction for each customer. You can do so by copying the following formula from C2 down to C3403:

=MAXIFS(‘Sample Data’!C$2:C,‘Sample Data’!B$2:B,$A2)

The formula uses cell A2 as a reference to filter the corresponding rows in the sheet Sample Data with column B and returns the highest (=most recent) date from column C respectively.

Similar to this we use the formula =MINIFS(‘Sample Data’!C$2:C,‘Sample Data’!B$2:B,$A2) in in cells D2 to C3403 to get the date of the first transactions of each customer.

For the final RFM model you’ll need the amount of time the customer has been with the business. In our example we’ll use months for this. As such put =DATEDIF(D2,now(),“M”)into E2 and drag it down E3403.

Next we want to know how much each customer spends on average each month. Plug in =SUMIF(‘Sample Data’!B:B,A3,‘Sample Data’!D:D)/E3 from F2 to F3403.

RFM Data Prep

The last step is to calculate the average number of transactions per month for each customer. So simply write =B2/E2 into G2 and copy it down.

For all above instead of “M” you could also use “Y” or “D” to set the time unit to years or days respectively. It doesn’t really matter what you choose as we we’ll be coding each data point into a 1-5 scale for the RFM analysis later on anyway. However to make your data more vividly choose a unit, which make sense to your business model, e.g. if you are selling cars it would make sense to choose years, while it might make more sense to choose days when you are selling coffee.

Calculating R,F and M

Two steps are necessary to calculate the R, F and M scores:

  • Determine how each customer ranks for recency, frequency and monetary
  • Assign a score to each recency, frequency and monetary rank

Luckily Google Sheets has a handy formula for returning the rank of a specified value in a dataset.

As such plug in and copy down the following formulas.

For the recency rank: =RANK(C2,C$2:C$4296,1) in H2 to H3403

For the frequency rank: =RANK(G2,G$2:G$4296,1) in I2 to I3403

For the monetary rank: =RANK(F2,F$2:F$4296,1) in J2 to J3403

RFP ranksThe last argument (1) in the formulas ensures that the highest values in the respective dataset gets a higher rank and vice versa. E.g. a customer with an average order value of 10$ would get a higher rank than a customer with an order value of 5$.

Next we’ll create a RFM rank matrix to convert a customer’s ranks on recency, frequency and monetary into the wanted 1-5 rating. For this you will use the formula PERCENTILE to get the minimum rank a customer has to have on each for the three factors to get a certain rating. Since we have ratingd from 1-5 we’ll need five percentiles which equal to 20% percent steps.

Put  =PERCENTILE(H$2:H$4296,0.8) into Q2 to get the lowest possible rank for the highest R score, similar put  =PERCENTILE(H$2:H$4296,0.6) into Q3 to get the lowest possible rank for a R score of 4 and so on. Do the same for the F score in column R and and for the M score in column S.

No we’ll do the actual conversion for each customer. Put the formula =if(H2>=Q$2,$T$2,if(H2>=Q$3,$T$3,if(H2>=Q$4,$T$4,if(H2>=Q$5,$T$5,$T$6)))) in K2 and copy it down to K3403 to determine the R score. The conditional statements will determine if the respektive rank is above one of the thresholds of the RFM rank matrix and then assign an according score. Do the same for the F score by copying =if(I2>=R$2,$T$2,if(I2>=R$3,$T$3,if(I2>=R$4,$T$4,if(I2>=R$5,$T$5,$T$6)))) from L2 to L3404 and for the M score by copying =if(J2>=S$2,$T$2,if(J2>=S$3,$T$3,if(J2>=S$4,$T$4,if(J2>=S$5,$T$5,$T$6)))) from M2 to M3404.

As a last step we want count how often each of the RFM combination (there are 5x5x5 = 125 combinations) occurred. Again we will use the COUNTIF formula for this. Write =COUNTIF(N:N,Q10)into R10 and copy it down to R134.

RFM Scores

You are done! ….at least technically. You gave each of your customers a RFM score based on the recency, frequency and monetary value of past purchases. However in order to make your analysis actionable you should group your 125 segments into more meaningful clusters and define strategies for each of them.

Clustering the RFM segments

RF Matrix

Even though many others suggest having ten or more clusters I would recommend focusing on having only six key clusters (at least in the beginning). As such the implications for each cluster are still manageable and content and promotion strategies for each can be created.

The following describes content strategies for your email list and consequently customer segmentation for each of the defined clusters.

High Value Customers

Segments: 555

Description: These customers are your most valuable customers. They buy frequently, are spending a high amount on each transaction and are still very active (=bought something recently).

Strategy:  Obviously these customers have proven that they are willing to pay and to buy often from your. So don’t use price incentives (e.g. discounts) to generate incremental sales. They love engaging with you so rather reward them by testing new product (or feature) launches with them first. Being your most loyal customers the probability that they give some valuable feedback on the new products and recommend them to others will be highest with them. If possible try implementing loyalty programs (as well as advocacy and review programs) to reward and keep their loyalty. In addition these are the customers you should target your most expensive products at.

Core Customers

Segments: 34X, 35X, 44X, 45X, 54X, 551, 552, 553, 554

Description: Your core group of loyal customers. While they might be spending less frequently or lower amounts than your High Value Customers, they are still very valuable as they are regular and recent purchasers of your products.

Strategy: Probably not as effective as your High Value customers you can still look into upsell opportunities. Aa such if you are selling several products (e.g. as an ecommerce business) you can add value for those clients by recommending products based on previous purchases. Advocacy and review programs can help you spread word of mouth for your business through this cluster.

New Customers

Segments: 51X, 52X

Description: Your newest customers. They recently had their first transaction with you. As such they obviously will have low frequency score. Even though they can have high monetary scores already, if they are high spenders.

Strategy: Most first time buyers will never graduate to promising and finally loyal customers. It is important to have an optimized onboarding with clear strategies in place (such as a triggered welcome email sequence) to encourage repeat purchases.

Promising Customers

Segments: 33X, 43X, 53X

Description: Customers, who finished the onboarding process but aren’t in the Loyal Customer cluster yet. They buy fairly often, but haven’t reached the frequency levels of the Loyal Customers or High Value Customers yet.

Strategy: You have already accomplished an initial relationship with the customers. Now you should focus on increasing monetization and frequency depending on what they are currently lacking. You can test personalized product recommendations based on past purchases and special offers based on spending thresholds. Increase brand awareness to stay top of mind and to increase frequency.

Need Attention Customers

Segments: 24X, 25X

Description: Customer, who once purchased from you with a medium to high frequency but stopped for some reason a while ago.

Strategy: Goal for this customer cluster is to reactivate them before they get lost at all. Part of this can be to try to find out, why they left through analyzing their behavior or surveying them. Try limited-time offers as well as individualized recommendations based on past transactions. Price incentives can also be tested.

Lost Customers

Segments: 1XX

Description: These customers have not purchased from you in a long while. Some of them might have been high frequency and big spenders, but stopped buying at some point.

Strategy: As with the “Need Attention” segment you should try to reconnect with these customers. By having even more aggressive offers and price incentives you can try to reactivate them. However it can make sense here to segment this cluster even further by F and M in order to identify low value customers

Obviously there are still some white spots left for some of the customer segments. Above are only key clusters you should focus on in the beginning. You can cluster all the left out segments into a General Population group sending those more generalized and less segment specific content. Once you have mastered above key groups you can start clustering the rest of your segments.

In addition the thresholds for above clusters aren’t set in stone. As soon as you fully understand the implications of the model and the clusters for you business you can start moving the segments around to form your own clusters, fitting best to your business.

RFM Summary

You can add the following formula to cell O2 and copy to O3403 to group the segments with above clusters in the Google Sheets workbook:

=IF(AND(K2=5,L2=5,M2=5),“High Value”,

IF(AND(K2=1),“Lost”,

IF(AND(K2>2,L2>3),“Core”,

IF(AND(K2>2,L2=3),“Promising”,

IF(AND(K2=5,L2<3),“New”,

IF(AND(K2=2,L2>3),“Need Attention”, “General”))))))

Did You Enjoy This?

Then feel free to sign up for my newsletter. Get my newest articles on career & skill development for marketers and guides on technical marketing every week.

Forecast Template Header

With the new year ahead many marketers and analysts will be tasked with creating a marketing plan for the year. Imagine you are one of those and are asked to present the plan next week. Sooner or later during the preparation you will have to address one major pain point usually every analyst or data driven marketer has to go through: How to realistically forecast sales, revenue, conversions or something similar for the next year?

Obviously during the presentation you would have to defend those numbers and as such you have to have a robust model for predicting them. On the other hand it can’t be too complex as you have to present already in a couple of days.

So what if you had a simple technique which allows you to accurately predict metrics, while it can be implemented very easily.

That’s what this tutorial is about. It teaches you the moving average forecasting method for forecasting future sales, revenue, etc. in Google Sheets. If you are reading this last minute and you need forecasts right away you can just plug-in your numbers in below template, but I highly recommend working through the guide to understand everything.

The Template

 

Get access to the Sales Forecast Template and plug in your numbers or work along.

First of all, for those of you, who only need a sales template for predicting revenue or other metrics, please find it above. Make a copy, open the sheet Data Input and copy your monthly revenue or conversion numbers from the last two years into cells D3:D26 and E3:E26 respectively.

You’ll find the output, i.e. the actual projections in the sheet Forecast.

You can simply change the column headers (D2 + E2), if you want to name your forecasts differently.

However in order to understand the techniques behind it and to potentially tweak and change the forecast I highly recommend working along the following guide. I’ll show how the predictions were modeled, including the ratio to moving average forecasting method.

Template

The Ratio to Moving Average Forecasting Method

In order to work along please also access above Google Sheets Workbook. In addition to the Data Input and Forecast sheets you’ll find two additional Worksheets – the first one containing the example data, which you can use to work along and the second one the solution to the example data. Even though conversions as well as revenue is forecasted in the template we will only work on revenue prediction in the following. However the forecasting technique used is the same for both.

The example data has already been cleaned and prepared, so you can start right away.

The ratio to moving average forecasting method uses trend and seasonal indices to accurately forecast future sales, revenue, conversions or whatever other time series you decide you want to forecast. It is an very easy-to-use four step method. We will use it in our example to forecast sales revenue. As such we’ll have the following four steps:

  1. Estimate the deseasonalized level of sales during each month (using centered moving averages).
  2. Define a trend line to the the deseasonalized estimates.
  3. Determine the seasonal index for each month and estimate the future sales by extrapolating the trend line.
  4. Predict future sales by adding seasonality to the trend line estimate.

Calculating Moving Averages and Centered Moving Averages

First you’ll have to create a full year moving average for each month by averaging the current month, the six prior months and the next five months respectively. By creating a full year average seasonality will be eliminated. To do so copy the following formula in cell F8 and drag it down to cell F20: =AVERAGE(D2:D13).

This means for example that the moving average for month no 13 (January-18) is $18.7k. The moving average for month no 14 averages months no 7 to 18. Adding these up (7+8+9+10… +18) and averaging those month numbers will give you 12.5. As such the moving average for month no 13 is centered at month no 12.5. Similarly the moving average for month no 14 is centered at month no 13.5. Averaging those two moving averages will give you a centered moving average that estimates the actual centered moving average at the end of month no 13. As such to estimate the sales revenue during each month (de-seaonalized), copy the formula =AVERAGE(F8:F9)down from cell G8.

Averages

Defining the Trend Line to Centered Moving Averages

We’ll now use the centered moving averages to define a trend line that can be used to estimate future sales revenue. We’ll need to find an intercept and slope to do this

Luckily there are two functions, which will do exactly this for us. In cell L3 put =SLOPE(G8:G20,A8:A20to find the slope of the trendline and in cell L4 write =INTERCEPT(G8:G20,A8:A20)to find the intercept of the trendline.

Now copy the formula =A26*L$3+L$4 from G26 to G37. This will give you the estimated revenue (without seasonality) for the future months.

Slope

Calculating the Seasonal Indexes

Start by calculating for each past month Sales / Centered Moving Average. So simply put =D8/G8 into H8 and copy it down to H20.

E.g. for July you’ll get 0.9 (2017) and 1.02 (2018) respectively. This means in July 2017 sales have been at 90% of an average month and in July 2018 at 102% of an average month. Averaging those two numbers will give you the seasonal index for July, which is 96%. So July usually generates 96% of the sales a average months would generate. In order to calculate the seasonal index estimates for all month we can work with the AVERAGEIF formula. The AVERAGEIF formula will only average values, which fulfill certain criteria.

Put the numbers 1 to 12 in the cells K7 to K18 respectively. This numbers represent the individual months. The formula =AVERAGEIF(B:B,K7,H:H)in L7 will average all revenue numbers for January. Copy that formula down to L18 to do the same for the remaining months.

We have to ensure that the seasonal indices average exactly to 1 to normalize them. This is actually quite easy. Put =L7/AVERAGE(L$7:L$17) into M7 and copy it down to M18.

Seasonal Indexes

Forecasting future months

In order to forecast the revenue for future months you have to multiply the trend line estimate for each month’s revenue with the the appropriate seasonal index. Copy the formula =VLOOKUP(B26,K$6:M$18,3)*G26 from I26 to I37 to predict revenue for the next 12 months.

That’s it you are done!

The model is adjustable for more recent trends as well, if you believe the recent trend of the series has changed significantly. As such you don’t have to take all Centered Moving Average for calculating the slope but could take only more recent months (e.g. the last half year in our case) in order to calculate the slope with months closer to the current date.

Forecast

Limitations

As usual the disclaimer that this is model won’t predict the future to 100% as it is based on historical data.The model will obviously be more accurate the more past data you have and furthermore the less volatile your time series development is.

Nevertheless above is an very easy to follow accurate method for forecasting sales and other metrics.

Did You Enjoy This?

Then feel free to sign up for my newsletter. Get my newest articles on career & skill development for marketers and guides on technical marketing every week.

Sales season is on. Black Friday and Christmas are traditionally some of the promotion strongest dates during the year. However for many marketing analysts and data driven marketers a lot of uncertainty develops on how to predict the outcome of promotional efforts. Obviously most promotions will probably lead to more orders, but to how much incremental revenue will it lead? Can it even have a negative effect by cannibalizing sales in the time following the promotions? Or is the overall effect positive nonetheless?

So imagine if you had a predictive model with which you can forecast the effects of your planned promotions. Giving you more certainty on how big your sales revenue will be.

This guide provides step-by-step instructions on how develop such a forecasting model for your business based on past promotions. All you need is Google Sheets and a free solver add-on. I recommend working along in the provided sheet to truly understand everything.

 

Download the example workbook to work a long or to have a look at the solution.

 

Defining the task

For this example you are given the historical daily revenue for an online retailer for the last two and a half years up until 2018-11-28 (obviously the techniques in this model can be used for other products such as online courses, software and others as well). In the past years the retailer ran 20% and 30% promotions reducing the price of all sold products respectively. Promotions were run on random days as well as during Black Friday and before Christmas.

We now want to find out a) what the impact of those promotions is, b) if it makes more sense to run a 20% or 30% promotion for overall revenue and c) if a promotion should be run during upcoming Christmas time.

Preparing the data

Formatting and cleaning data is usually the first step you have to take for every data analysis. As we are dealing with quite a simple data set here (revenue and dates) and the data has been prepared upfront there is not much to do in terms of cleaning and formatting.

However we still have to do some preparations for our upcoming analysis. As we want to investigate the impact of promotions we have to indicate the dates on which promotions happened. Furthermore we need to  indicate if the promotions had any visible impact on the days after the promotions, i.e. negatively due to the cannibalization of revenue.

Both has been done already in the provided sheet. In column E – H a “1” indicates that a event has occurred respectively. E.g. a “1” In cell E2 would mean a 30% promotion has happened on 2016-01-01, while a 1 in cells E3 – E5 means a negative after-promotion effect was observed on those days.

Days of the week and seasonality usually have an impact on sales as well (e.g. weekdays vs. weekends, summer month vs December), which is why we want to factor in those as well.

There are two simple formulas we can use here: MONTH() and WEEKDAY() which will give you month and the day of the week for a reference cell respectively. Those formulas have been used in  column B and C.

That’s it for preparation. Obviously even though everything above is already provided in our example data you would still have to do it for your own datasets.

 

Building a basic model

What we are going to do is build is a model, which forecasts sales based on the impact of seasonality and promotions. We will have the following five factors influencing our sales prediction:

  1. A constant as a basis
  2. Influencing effects for the day of the week
  3. Influencing effects for the month
  4. Influencing effects for a promotion day
  5. Influencing effects for the days after a promotion

This means for each day the forecast of revenue will be calculated by the following equation:

Predicted revenue =  Constant + [day of the week effect] +  [month effect] + [20% promotion effect] + [days after 20% promotion effect] +[30% promotion effect] + [days after 30% promotion effect]

Put those individual parameters in cell Q2:Q27 and a 100 in the respective cells in column R. Those are only trial values and will act as coefficients.

We will use the solver to estimate the coefficients, which will make the model fit best to our data, i.e. minimizing the (squared) error between our forecasts and the historical data. But more on that later on .

Next step is to write the actual formula, which corresponds to above equation. In cell M2 put the following formula and drag it down the whole column:

=$R$2 + VLOOKUP(B2,$Q$4:$R$15,2,FALSE) + VLOOKUP(C2,$Q$17:$R$23,2,FALSE) + E2*$R$24 + F2*$R$26 + G2*$R$25 + H2*$R$27

R2 represents the constant. VLOOKUP(B2,$Q$4:$R$15,2,FALSE) picks up the coefficient for each month while VLOOKUP(C2,$Q$17:$R$23,2,FALSE) picks up the coefficient for the day of the week. If a promotion occurred on a given day E2*$R$24 and G2*$R$25 will pick up the effects of a 20% and 30% promotion respectively, while F2*$R$26 and H2*$R$27 will do the same for after promotion day effects.

Now you’ll calculate squared error for each day. This can be done simply by putting =(D2M2)^2 into N2 and dragging it down the column. Sum all squared errors up in cell Y4.

One last thing you have to do before using the solver is to average all values of the day of the week and months values. You can do that by putting =AVERAGE(R17:R23) in into V4 and =AVERAGE(R4:R15) into V5. We are doing this as we will add constraints to the Solver model, which constrain the average day of the week and the average month to equal 0. We are doing this as those constraints make sure that a day of the week or month with a positive effect has a higher than average revenue and a day of the week or month with a negative effect has a lower than average revenue.

If you haven’t so far: No it’s time to download the solver. After doing so click on Add-ons -> Solver -> Start to set it up:

We want to minimize objective cell Y4 by changing values R2:R27, while constraining V4:V5 = 0.

So basically what we are doing is we are minimizing the squared error between the historical revenue data and our fictional forecast by changing the coefficients for each of our above defined five factors. When you are done setting up the Solver click on “solve”.

The outcome tells you a couple of things. E.g. a 30% promotion day raises (all else being equal) the revenue by $6116. Weekends are revenue wise a lot better than other week days and December accounts for a major sales raise as well. Days after a promotion usually lead to a drop in revenue.

Evaluating the model

You can evaluate the model by calculating the R2 value between the forecasts and the actual historical revenue. Plug =RSQ(D2:D1064,M2:M1064) into cell Y5 for this. The formula calculates the percentage of the variation in revenue, which is explained by the forecasting model. In our case we should get a 0.59, which means that the independent variables of the five factors explain 59% of the daily variation of the revenue. Obviously as such the model has some room for improvement.

In order to do so we’ll try to spot any outliers in the daily forecast accuracy. As a first step calculate the error between the forecast and the historical daily revenue. Do so by putting =D2M2 into cell O2 and copying it down the column. Calculate a close approximation to the standard error of the forecast with the standard deviation of the errors with the formula =STDEV(O2:O1064) in cell Y6.

We define outliers if the absolute value of the forecast error exceeds two times the standard error. So as a next step we want to identify the outliers fitting into this definition. For this we will use conditional formatting. Select range O2:O1064 and click on Format -> Conditional Formatting -> Add new rule. In the “Format if…” dropdown menu choose “Custom formula is” and put =abs(O2)>=2*$Y$6 in the box.

This will highlight all outliers within above definition.

And, surprise surprise we will find clusters of outliers around Black Friday and before Christmas, both we did not include in our original model. However, obviously people are shopping a lot more during these dates (e.g. 12/01 – 12/17) and less after (e.g. 12/18 – 12/31).

Improving the model

Now that we know what caused the outliers and thus a lower forecasting accuracy we can improve our model by adding those factors to our original five factors.

As such we’ll add Black Friday, After Black Friday, Christmas sales, After Christmas into the headers of the columns I:L in order to be able to mark the occurrence of those events for each day similar to what we did before for the promotions. As such we’ll add the coefficients for those new factors in Q28:R31 as well.

Last adjustment we have to do now is to add the factors in our forecasting calculation. Put the following formula into Cell M2 and drag it to M1064 (we added the Black Friday, Christmas and the days after those events respectively as factors):

=$R$2 + VLOOKUP(B2,$Q$4:$R$15,2,FALSE) + VLOOKUP(C2,$Q$17:$R$23,2,FALSE) + E2*$R$24 F2*$R$26 G2*$R$25 H2*$R$27 I2*$R$28 J2*$R$29 K2*$R$30 L2*$R$31

Now run the solver again with the added variables:

Adding those variables improved R2 drastically to 0.88, which means the variables explain 88% of the daily variation of the revenue

The actual forecasting

So far we have only worked on the prediction model. Next step is obviously to forecast sales revenue with this model until the end of the year and to examine what impact a 20% or 30% promotion has. This actually the easy part.

First copy the formula from cell M1064 to all cells M1065:M1097. Next mark the Christmas sales time and days after the Christmas sales in column K and L (this is quite easy here as the change in sales revenue is quite obvious. However it might be more difficult to spot in real life data.)

This will give you a prediction on what revenue to expect for December without any promotions: An overall revenue of $175,734.

Let’s try now what will happen, if we have a 20% promotion on 2018-12-01. Plug in the indicators into G1067 and for the after promotion effects into H1068:H10670 respectively. The overall revenue for December would be $175,082 now.

Delete the indicators for the 20% promotion and try the same for a 30% promotion by writing a “1” into E1067 and F1068:F10670. Overall revenue for December would be now $177,291. Not much of a difference but still it is better to go with a 30% promotion.

That’s it, we are done! We are now able to answer all of our initial questions: We know what revenue impact the promotions have respectively and which promotion we should run during Christmas time

Limitations

You might wonder, why we did not solve the problem with a regression analysis tool. The answer to this is actually quite simple:

For a regression analysis with a typical spreadsheet analysis add-on the dependent variable would have been revenue. In addition we would have needed 25 independent variables to account for the different factors (months, day of the week, promotions, Black Friday, Christmas, after event days).

Most analysis add-ons simply can’t handle that many independent variables (including the Excel Analysis ToolPak, which can handle up to 15 independent variables). That’s why we use the Solver, which allows a lot more changing cells) to estimate the coefficients of the independent variables in order to minimize the sum of squared errors.

In addition as with all models above created model is only a simplification of reality and can never give a 100% accurate forecast. Furthermore in this case the revenue numbers were simplified as well. As such revenue growth wasn’t included. In addition events and after event days were very easily identifiable and could be clearly distinguished from normal days. As mentioned this might be more difficult for a real life data sets.

Nevertheless the above guide provides a good basis to develop your own forecasting models and to fit a model to your revenue data and events. So next step for you is to fit it to your own data set and to predict if promotions for Christmas will make sense for you!

Did You Enjoy This?

Then feel free to sign up for my newsletter. Get my newest articles on career & skill development for marketers and guides on technical marketing every week.