4
$\begingroup$

I have the following data for web traffic in Excel

  • June 88,147
  • July 111,839
  • August 93,148
  • September 93,069
  • October 101,881
  • November 97,345

I can do a chart with a trend line and a linear trend shows a slight upward trend, I would like the simplest way (stats is not my field!) to project / guestimate what it will be like in the future

The trend line in the chart gives me y=14.321x - 491537, but I don't know to work that into a formula I tried =14.321*E8-491537 (e8 is November's value) and it gave me 902,545 which does not fit the trend at all!

The goal is a rough estimation of the month by which the figures will be up to 130k+ (presuming my slight upwards trend is correct!)

Improve this question
$\endgroup$

3 Answers 3

Reset to default
4
$\begingroup$

x is not "November's value" it is "time" and its value depends on how you have time coded. I don't know how Excel does this (I wouldn't do statistics in Excel), but in R you could do what you propose like this:

month <- 1: 1:6 traffic <- c(88147, 111839, 93148, 93069, 101881, 97345) m1 <- lm(traffic~month) summary(m1)

which gives an equation of 95.967 + 458.2*month which means that predicted traffic goes up by 458.2 per month.

But doing this (linear regression) on time series data isn't really a great idea.

However, if you plot the data and add the line

plot(month, traffic) abline(m1)

You see that the line fits the data pretty well, except for one month

enter image description here

Improve this answer
$\endgroup$
1
  • $\begingroup$ thanks, that's handy :) I know trying to second guess the future has limited use, but hopefully it will have some motivational benefit, as it does prove things are on the up and not sliding down :) $\endgroup$ Commented Nov 15, 2012 at 13:38
3
$\begingroup$

Following up on Peter's answer, exponential smoothing is a common method for forecasting time series data, and is also very easy to implement in R:

require(forecast) month <- 1: 1:6 traffic <- c(88147, 111839, 93148, 93069, 101881, 97345) m2 <- ets(traffic) plot(forecast(m2))

forecast plot

The blue line is the forecast, and the orange and yellow regions are the confidence intervals.

Improve this answer
$\endgroup$
1
$\begingroup$

Rather than just fitting a trend, think about the variables that are driving traffic to the website. If you are using a web-analytics package you could think about including campaigns as explanatory variables. Think about using Gooogle rankings and content too. This would be more informative and give better predictions based on what activity you plan to undertake in the future.

Here's a step-by-step guide to creating a regression in Excel

guide to Excel Regression

Be sure to check this link too, so you can perform a Breusch Godfrey test for serial correlation. This shows a step by step time series regression.

Time Series & Breusch Godfrey in Excel

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.