How to Create Your eCommerce Forecast in 3 Steps

Forecasting your eCommerce traffic and sales is something many businesses and brands must do

“Give me six hours to chop down a tree, and I will spend the first four sharpening the axe”

–Abraham Lincoln.

I created this eCommerce forecasting spreadsheet when I was managing a team of digital marketers managing campaigns for 35+ retail brands.

Unfortunately, there is no crystal ball to tell us how much our eCommerce businesses will earn next week, next month, or next year. However, we do have the next best thing: eCommerce business forecasting.

This was the document that guided brand’s advertising budget allocation, anticipated ROI, conversion rate optimization and more.

Over the years, I have continued to use this forecasting template to help our clients increase traffic and revenue year over year.

And I am sharing it with you, for FREE, today. Not gated. No signups. No gimmicks! FREE! 🙂 Just thought I emphasis that it’s FREE!!

One of  the challenges as P&L owners of eCommerce marketing is forecasting. Now more than ever, you’re facing pressure from the “boss” to show attribution and/or bring in more revenue. Tracking attribution, marketing spend, and ROI is not easy, but it is extremely important in order to be able to reach those revenue goals.

It’s tricky.

“But what other choice do you have — with great power comes great responsibility.” Uncle Ben was right!

What other options do you have?

You must use your math and excel formula prowess (or software) to provide guidance on where marketing budget should be spent, what return is expected by channel, how ad budget is allocated, and most importantly, how to account for seasonality in your calculations.

More on that later.

I faced this conundrum at every company I’ve worked at — from LegalZoom and Yahoo! to PriceGrabber and Organic.

Truthfully, the ecommerce forecast got old really quick (maybe after 2 rounds). It was anyone’s best guess. That’s the reason why I decided to spend 3 months creating this template to help my team and the brands with their eCommerce forecast.

What I got frustrated with was the lack of data points and the abundance of opinions.

Forecasting sales or revenue projection is the foundation and compass for your eCommerce businesses.

Keep in mind, it’s a forecast. So by nature, it’s not an exact science, in fact I would venture to say it’s part art part science.

For most eCommerce businesses this forecast can provide clarity on each channel’s performance in your digital marketing mix.

Let’s dive in.

What is eCommerce forecasting?

It’s the calculated process of anticipating how much advertising dollars will be spent (for the most part) – on what tactic and strategy – and calculating will the return on that investment looks like.

The cost of getting the traffic and return on advertising spend is what it comes down to.

In a nutshell, it’s a working spreadsheet to help answer questions like where you are spending, how you’re managing budget, and what returns you can anticipate from your advertising and marketing campaigns.

The one thing business look for is clarity or an informed approach to forecast e-commerce performance for the next 12 to 18 months.

Forecasting should happen in tandem with figuring out what your customers are looking for, lining up your content strategy, setting up your Facebook advertising campaigns, and other digital tactics.

Speed and efficiency in everything you do is key — especially when it comes to your digital strategies.

You cannot afford to spend two months on your forecast before you start planning out your campaigns.

Here’s where you give me the “But, Derek, my boss wants a 3 to 5 year forecast.” I’m sure they do. But it’s useless. Yes. I’m saying what many marketers are thinking.

Unless you’re one of the Fortune 500 companies that have deep pockets to hire an entire accounting team, I am willing to bet that you are struggling to produce your e-commerce forecast for even the next year.

Don’t worry. By the end of this article, you will have everything you need to create your e-commerce forecast. And hopefully you will see why I said a 5-year forecast is useless.

Firstly, I would like to confess a misguided statement. Previously, I said it takes 3 steps to create your forecast. That is true.

But there are many sub-steps and data gathering required to make my eCommerce forecast work.

Yes, it’s going to be a lot of details. After going through this spreadsheet, you will have a much better understanding of your business.

Secondly, the good news is, over the years, I have distilled and improved this to a process you can readily use without having to deal with the dirty work.

Thirdly, you can download the e-commerce marketing forecast spreadsheet for FREE.

Last but not least, you can reach out to me if you have any questions about this forecasting document.

How did this e-commerce marketing forecast spreadsheet came about?

Prior to launching search marketing agency, Fullmoon Digital, I worked in-house at an agency, managing a team of marketers and analyst serving over 30+ retail brands.

I was frustrated that each time our clients wanted a projection of e-commerce performance, the team would scramble to come up an answer — and with the added pressure from management to “make things look good,” the forecast frequently shows positive trends.

My friends, here’s the misconception with forecasting. It’s supposed to provide guidance — a hypothesis — not the absolutely truth, and definitely not guarantees.

As marketers, we have a commitment to the legitimacy of our forecasts.

We must show both sides of the coin. If the future looks bleak, let the numbers guide you so you can pivot your strategy and turn things around. What’s the point of forcing the numbers and placate the situation?

Form a hypothesis: Forecasting Is An Transparent Process

This is a repeatable eCommerce marketing forecasting process. As I’ve mentioned many times, this template was used by over 35+ brands to supercharge their e-commerce marketing strategy. And today, you can to by learning how I did it. 

If you want to grow your eCommerce business then use this template to:

• pivot quickly if the numbers are trending downwards in your forecast
• increase, shift budgets, and adjust marketing campaigns to scale and grow their business
• get a clearer picture of marketing budget allocation
• coordinate marketing team around standard success KPIs

This is important. I continue to improve and produced a newer version of the spreadsheet each year.

But It’s Like Predicting the Weather

eCommerce forecast can feel like this. It might rain, or it could be sunny, or it’s anyone best guess.

Everything can change quickly. That’s the blessing and curse of doing business online. If you’ve been running an eCommerce business for a while, you will witness the ebb and flow — and you’ll either love it or hate it. 

Fortunately for me, I fell in love with eCommerce. (whew)

E-commerce marketing is complex. And forecasting requires many skills.

You need to be proficient in Excel formulas, analytics, advertising platforms, data extraction, transformation, and manipulation, and much more.

At first, I knew very little about Excel formulas or data extraction, which is required if you want to handle tons of raw data.

I ran into roadblocks (a lot of them). VLOOKUP and pivots sounded like names of alien spaceships to me.

Update: With Microsoft’s introduction of XLOOKUP, forecasting will become much easier in this document when we update it to the latest version.

Without a doubt, this article proves that I’ve come a long way from the younger years.

The final spreadsheet is a culmination of a lot of sleepless nights, hundreds of hours of research, and gallons of coffee (A LOT!).

And I empathize fellow marketers go through this pain more than ever — so hopefully this will help you with your e-commerce marketing forecast project.

It’s important to use the right data (ingredients)

You must approach forecasting methodically, consider many internal and external variables like traffic sources, performance metrics, seasonality, shopper behavior, and other data points, then applying the proper inputs.

It’s like baking a cake. Everyone cares about what the cake taste like, but no one realizes how many ingredients is required, with precise measurements, going in the recipe to make the cake.

At the end of the day, if we really want to sum it up, it’s as simple as…

…For every $1 spent, how much do I get in return. But the calculus to arrive at the conclusion can be tedious. But we need to put in the work and correct ingredients…after all, no one likes a cardboard-cake right?

This screenshot is straight from the spreadsheet. — it’s not fancy, but it gives you the executive level summary you can share with your boss.

So, think of forecasting as your e-commerce “cake recipe.” Let’s get baking!

3 Top Reasons You Are Forecasting

The ability to forecast your e-commerce performance can provide guidance to marketing ad spend allocation across a spectrum of digital channels like pay-per-click marketing, SEO, email marketing, social media marketing, content creation, PR, affiliate marketing, and others.

This forecast document will also make you the BFF of your finance team.

Trust me, I made a lot of friends with when I shared this across the agency I was working in.

But to know how much to spend on each marketing channel of the business, and the kind of site traffic and sales you’ll bring, you need to know what channels are best suited for your objectives.

In most cases, you’ll probably divide your forecast into customer acquisition and retention.

But then again, it depends on what your company’s objectives are.

Some businesses are really focused on acquiring new customers and breaking even (1-to-1 ROI) is good enough.

I’ve worked with some brands that are willing to operate at a negative ROI to reach a broader audience to get more web traffic. 

Others need to increase profitability. Yet some are able to increase both gross revenue and profit at the same time. But what about the ones that are focused on brand awareness.

There’s a laundry list of scenarios that will influence the direction and outcome of your forecast. 

WARNING: Most importantly, your team must agree on a set of objectives otherwise your forecast will cause more harm than good.

I hope this spreadsheet can help you navigate the ocean of never-ending data.

There are 3 common reasons for building a forecast:

I’m sure there are more, but these are the ones I came across the most during my in-house years.

Scenario #1:

Your company is launching a new e-commerce site

Now that you’ve launched your e-commerce site, you need to drive traffic and generate sales.

Before you spend a single dollar on advertising, you need a plan. You need to go to your boss and present a case for advertising budget.

Because it’s a new website, you are not going to know how things will turn out.

But luckily, there are many research tools and e-commerce benchmarks available for free that you can refer to to create a hypothesis.

(RELATED: Is your business ready for e-commerce transformation? 10 reasons it may not be.)

Scenario #2:

Your are planning for the next calendar year and beyond

Your e-commerce site has been in business for a few years; growing steadily, and now you need to plan for the coming years.

With a plethora of e-commerce data recorded in Google Analytics (or other analytics software), you get to work on forecasting numbers.

You come up with growth numbers, plug in your target annual growth rate into excel.

As a result, you probably use excel’s in-built FORECAST or TREND functions.

Scenario #3:

You have been hired and placed in charge to overhaul the e-commerce department

You are now leading the e-commerce department and need to fine tune the P&L.

But if you are like most executives in new roles, you’re already overwhelmed with 50 other objectives and issues to deal with on day one.

You might already have a budget at this point. Or not, depending on the situation.

But forecasting still needs to get done because you are accountable for the e-commerce business now.

The objective of this planning process is to make sure that your business will invest in the right areas and making necessary adjustments – and pivots –  to drive more traffic, customers, and sales for the business.

Okay, that gives you the background into why e-commerce forecasting is important – how it plays a role in your business. 

Now, let’s dig into the methods of creating a e-commerce forecast.

I’ll walk you through the steps and you can download your free copy below. But…don’t skip the explanations, or you’ll miss out on a lot 🙂

But first, let’s look at some of the pitfalls of e-commerce forecasting, so that you can avoid it:

• Taking averages and projecting annual growth. Monthly performance metrics are never consistent. There’s just too much fluctuation and external influence.
• Not including any agency or management fees into your cost. Frequently, businesses forget to add in the monthly 3rd party fees and come short of their ROI.
• Using 100% of the data from research tools blindly. This can be dangerous especially if you are working with PPC search volume. Not all traffic is created equal.

Averages ruin everything

There’s just too many internal and external variables that can affect your e-commerce performance.

If you plot your website’s conversion rate for the past 12 months, it’s rare that see a consistent trend. And when you go deeper you will see even more fluctuations. Some are more obvious than others.

A few notable seasonal shopping seasons include:

• Christmas
• Black Friday
• Cyber Monday
• Memorial Day
• Labor Day

Projections that use averages are generally used during sale pitches – it’s a common practice.

But that type approach to forecasting never give you any indication of trajectory or trend.

Worse of all, it doesn’t come close to considering seasonality of your business, holiday spikes, or slower months.

Free lunches will never exist

Many e-commerce budgets leave out agency and 3rd party fees. The fees should be included to give you a clear understanding of total marketing spend across your digital channels. It’s an honest view of what your business is spending on. 

Fees is something that must be accounted for throughout the e-commerce planning process. It’s not something you can afford to leave out.

It’s not a buffet, don’t treat it like one

As I mentioned before, e-commerce forecasting is not just about sales.

You need to also estimate visitor volume that the site will generate from all the traffic sources. There are plenty of tools that can help with this; from Google Keyword Planner, Ahrefs.com, and SEMRush are great examples. 

Traffic estimating tools return just that; estimates of potential volume available for specific search keywords.

Your job is to figure out how much volume can you truly afford to pay for and what can you rank for organically.

If you have historical data, then you’ll have a good idea. But if you are launching a brand new e-commerce site, you need to be realistic about what you can afford and how long before you can drive organic traffic. 

Today I will focus on how you can start building on an e-commerce marketing forecast. Additionally, I’ll show you step-by-step how we do it for our clients at Fullmoon Digital.

The Forecasting Process

The forecast will help create a honed in marketing planning document, so you can stop banging your head against the wall.

It’s important to keep in mind that you’re going to revise the forecasting process quarterly and annually.

This forecast allows you to assess the performance of your current plan and adjust accordingly. It gives you an actual “projections” and reveals opportunities for improvements where you could generate more, traffic, and/or make more money from your digital efforts.

(WARNING: The spreadsheet is focused on functionality, not aesthetics.)

The E-commerce Marketing Forecasting Steps

Let’s get into the steps so you can start building this for your e-commerce business. 

(NOTE: We will focus on the 3rd scenario — where you just inherited the e-commerce team and need to come up with a forecast.) 

Numbers used in this illustration are semi-fictitious. 

Forecasting Step 1: Collect Historical Data (For Google Analytics)

There are 6 data points you need to complete:

• Traffic
• E-commerce Revenue
• Transactions
• Conversion Rate (automatically calculated)
• Average Order Value (automatically calculated)
• Ad spend

The easiest way to gather this data is to create a custom report in Google Analytics. Go to your Google Analytics > Custom Reports

Then create a Flat Table with the following Dimensions and Metrics. You will need this to export the historical data you will use in the forecasting process.

After saving your custom report, select the Date Range. Pick data from the past two years, the export the data into excel.

Traffic

After exporting your raw data, fill in the traffic section of the spreadsheet.

Next you enter the sales date for each month for the past two years.

Enter the total transactions from your historical data.

Once you have completed the traffic, sales and transactions, the AOV and Conversion Rate will be automatically calculated for you.

And finally, enter your previous years’ ad spend for the respective mediums. This will be summed up and used in the executive summary to compare your year-over-year ROI.

Forecasting Step 2: Adjusting Input Variables

Your e-commerce business volume is affected by seasonality, site traffic, conversion rates, and many other factors. And depending on what you’re selling, you might even be affected by customer abandonment. 

If you’re selling winter jackets, then the summer months could be slow.

Mother’s Day, Graduation, and Thanksgiving will be huge for you if own an online flower site.

If you are selling custom hardware, then you are probably more consistent. You get the point. Seasonality needs to be adjusted for.

Let’s start with that.

In the Input Variables tab, we have entered major holidays into the calculations of the forecast. 

We have 10 top shopping holidays in the U.S. market. Next to each, you can adjust the percentages to suit your business. So if you are selling flowers, for example, Valentine’s Day adjustments might be 50%. That just means you expect 50% more business than usual for this period.

There are other input variables you can adjust like:

• conversion rate improvements
• average order value increases
• overall site growth expectation
• natural attrition rate
• average CPC
• and many more.

There are 28 input variables that you can adjust in total. Custom it to your e-commerce business.

Each of these inputs is 100% dependent on your e-commerce business. There is no such thing as the perfect inputs. There are benchmarks, but at the end of the day it’s really up to you to look at your data and make the most educated assumptions on what metric you can affect the most.

Forecasting Step 3: Reviewing Executive Summary

If everything was entered and adjusted correctly, your Executive Dashboard Summary should look something like this. 

One caveat: this forecast planning tool is not a guarantee. It is designed to provide guidance and 

If you are in charge of the e-commerce P&L, this document could come in handy as you plan your next e-commerce budgets.

Before you decide if you should invest another $5,000 or $500,000 in marketing dollars, you want to know where it should be invested, where to double-down, and where to avoid (potentially).

The Forecasting Template will help you in 2 ways:

• Having a planning process in place that can provide guidance. After looking at your current e-commerce plan, your first instinct is to propose a budget plan, naturally, so you can scale. But, the forecast process allows for scenario planning…adjusting several input variables and applying it to as many situations as possible.
• Scaling out to a traffic source. Building out a forecast and provide a methodology – a guidance where you and your team can approach digital channel ad dollar allocation. This breaks up the process of forecasting into straight forward steps to help you look at your plan holistically.

Putting It All Together

This planning document should be used as a guide. If you got this far, I want to remind you that a forecast is only as good as the input, assumptions, and reasonable expectations you get out of it.

There are obviously other considerations to account for when it comes to forecasting. Every business will have exceptions to consider — but that’s when you start to adapt this e-commerce forecast worksheet to your own needs.

Even so, many businesses have a difficult time even getting to this stage. So I thought this might help get you started.

I hope this document helps one e-commerce business transform their forecasting mindset.