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.

 

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.

Ecommerce KPI Hero

Imagine you just started a new job and are leading marketing for a small or newer ecommerce business (or alternatively you are the marketing analyst of the department). Before you start your marketing efforts ƒyou are tasked with defining a few ecommerce KPIs for tracking the success of your marketing campaigns. The only tracking tool you are using is Google Analytics.

Sounds scary right? With no starting points to begin with this sounds like a huge and complex venture as there are so many potential KPIs to choose. In addition you are probably scared that as you don’t know an immediate answer, people will soon start asking how somebody like you was hired to lead marketing (or be an analyst respectively). And as such not long until you get fired or replaced by somebody else.

So what if you had a concrete KPIs framework to follow for ecommerce marketing. You would know exactly which marketing efforts work and what to focus on.  All leading to skyrocketing sales.

That’s what this guide is for. Step-by-step it will show you how to systematically develop a ecommerce KPIs framework directly derived from your business objectives. No other tool besides Google Analytics will be necessary for this. At the end of this article you’ll also find a done-for-you custom report template, which you can instantly add to Google Analytics in order to use your new KPI framework.

In case you can’t wait and already want to work along. Get instant access to the custom report here.

Getting started

This guide and framework relies heavily on the work from Avinash Kaushik and is adjusted for an ecommerce context. Be sure to check out his blog, if you haven’t.

One of the reasons why setting up a marketing ecommerce KPIs framework can be so overwhelming is that there are hundreds of metrics to choose from. E.g. should you focus on bounce rate or clicks? Are conversions the only metric to look at? Or are others equally important? In this guide you will reduce the large number of metrics you could look at, while focusing only on those, which are really important for your ecommerce business.

In general when setting up a marketing KPIs framework you should always make sure to track and cover all of the following areas of the consumer journey with your KPIs:

Acquisition: How will users be acquired?

Behavior: What should the users do on the website?

Outcome: What’s the final desired result you want to achieve?

These are the guiding questions you should alway keep in mind when designing your new ecommerce KPIs framework. The setup of the framework itself then follows five steps:

  • Step one is to identify the business objectives of your company. Why does it actually exist?
  • Step two is to identify goals for each business objective. Think of goals as steps to take to reach the objectives.
  • Step three is to define the key performance indicators. So basically metrics that help you understand how you are doing.
  • Step four is to identify targets for each KPI, indicating, if you are succeeding or failing.
  • Step five is to identify segments of people / behavior / outcomes that can be analyzed further.

Step 1 – Define the business objectives.

Most ecommerce businesses have three objectives in common: They want to grow and as such increase their sales revenue. They want their brand to become more well known and doing so build brand awareness. And last but not least they to want build an audience or more specifically an email list, which they can nurture.

Obviously not all of those objectives must be relevant for you and you can decide to use only part of below metrics. However those objectives are usually the common denominator for the majority of smaller or newer ecommerce businesses.

ecommerce kpis - business objectives

Step 2 – Identify goals for each objective

Goals are basically the specific levers you have to pull to accomplish your business objectives.

Business Objective 1: Increase sales revenue

As you want to increase the absolute number of sales as well as the efficiency of your sales process we will work with three different goals here.

As a first goal we want to increase the number of orders, second we want to improve your conversion rate in order to convert the website traffic more efficiently and as a last we want to increase your average order value in order to rise customer value.

Business Objective 2: Create brand awareness

We want to get your brand more well known and more people visiting your store. As such we will set two goals here. First we want to get more visitors overall to your website and second more specifically we want to increase the number of new website visitors.

Business Objective 3: Generate leads

Last but not least we want to build an engaged audience with an email list that you can nurture. Therefore your last goal here is to increase newsletter sign-ups through the website blog.

ecommerce kpi goals

Step 3 – Define ecommerce KPIs for marketing

For each objective we will need metrics, which tell us how you are doing against each objective and goals respectively. Those metrics are called key performance indicators (KPIs).

Business Objective 1: Increase sales revenue

As we want to increase number of sales, sale efficiency and average order value we will also define your KPI’s accordingly. Therefore the metrics to focus on here are Transactions, Avg. Order Value and Ecommerce Conversion Rate.

Business Objective 2: Create brand awareness

Not only do we want more visitors coming to your website, which is why we track number of total Users as one KPI but we specifically want new visitors. Ergo the second KPI is number of New Users.

Business Objective 3: Generate leads

As your goal is to build an audience or email list, we will be tracking the number of newsletter Sign-Ups as a KPI here.

ecommerce kpi kpi

Step 4 – Set targets

It is important to set targets for each KPIs upfront in order to have indicators of success or failure. Failing to set those will make it impossible to determine, if your marketing campaign was successful or not. It is not possible to generalize what the targets should be for you business specifically. E.g. increasing sales by 100 orders could be huge marketing success for an online store just starting out but a marketing campaign failure for a store, which makes millions in revenue already.

There are three main options to determine targets. Either you look at historical performance, e.g. “we had 100 orders at this point last year, which is why we want to have 120 this year”.

Or, if you don’t have historical data you can look at ways to substitute that data. E.g. is there a competitor, which sales you can estimate? Or numbers from a similar product you have been selling before?

Last option is to ask internal or external people. E.g. internally you could ask people, who have been working there before you such as management or the founders. If those don’t exist or aren’t available, try to find external sources as for example your clients (for validation), consultants or other ecommerce people to approximate sales and other metrics.

I know this step might seem intimidating. But setting any target is better than no target at all. If you still don’t have a clue how to set your targets, try to set a number, that if when reached won’t make you feel too bad. The more experience you get the better your targets well be. As such just set a number to start with and revise it as time goes by and you get more data to base it on.

ecommerce kpi target

Step 5 – Create segments to analyze

There are probably a few people, who will stop thinking about their marketing analytics framework after step 3 and as such not set any targets. But I bet that the majority will wrap it up after step 4 the latest and will not define any meaningful segments.

Segments can be anything from the traffic sources, your user behavior onsite, the kind of users attracted. Basically anything you can segment your KPI metric reports with and which will tell you more on how to achieve your business objectives.

Segmentation is the only way to give your KPIs a meaningful sense and make them actionable. E.g. it is nice to know that 30% of your visitors are new users, however this insight becomes truly actionable, when you know that 100% of those come from PPC advertising.

We will define the following segments for your ecommerce store:

Source – Where did the users come from? Social Media, referral, organic, etc.? This segment is relevant for all your KPIs. As such you could ask, which sources drive the most sales or where most new users come from. As somebody working in marketing this will probably one of the most important segmentations for you in order to decide, which marketing channels to focus on.

User Type – Should you focus on new or existing users? E.g. you can track which group accounts for most revenue or which source refers the most new users. You can easily focus your marketing efforts accordingly on either group by looking at this segmentation.

Page Title – Segmenting by pages allows you to see which content performs best. As such you can answer questions, as for example which blog post had the most newsletter sign-ups or which article had the best performance in terms of user acquisition.

As you can see segmentation is the final step to make your KPIs actionable. You will know exactly what type of traffic source, content, etc performs the best and where you should focus. Feel free to add your own segments, depending on what makes most sense for you.

That’s it! We went through a structured approach to determine which KPIs to use, derived directly from the business objectives. We kept in mind the acquisition of customers (user metrics), the behavior they should show on the site (newsletter signups, conversion rate) as well as the outcome we want (conversions and transaction value), while having a relatively low overall number of metrics to track.

ecommerce kpi target

Resources

As promised this is a custom report template, which you can add directly to your Google Analytics account in order to track above six metrics. Please keep in mind that you have to set up ecommerce tracking correctly to use it and make sure you are tracking newsletter sign-ups via event tracking (I used Goal 3 in the template).

Furthermore this is a Google Sheets template, which you can use to track actuals vs. target numbers for your KPIs for the next months.

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.