Creating a Forecast for Google Shopping Ads using Prophet and R

Introduction

If you are a digital marketer, then you have probably had to do forecasting for the following quarter or year. Typically, the way it is done at agencies is that you grab historical data, see how much it has grown year over year, turn that into a factor, multiply it against the past year, and voila! You have next year’s forecast.

These forecast are usually presented in beginning of the year/QBR and then rarely looked at again. These forecast are subjective and unreliable when comparing against actual.

Seasonality, every digital marketers favorite scapegoat

If you have worked in digital marketing long enough, when performance goes sideway, then I’m sure you’ve heard , "it’s probably just, seasonality. Seasonality is often used as an easy-way-out of explaining bad performance.

As a digital marketer, how do you know if your marketing efforts are performing better than expected if you can not set expectations you are confindent in to compare it against?

What is Time Series Analysis?

Time series analysis is a statistical technique that is used to detect trends in a value that variable takes over a length of time. These trends are then used to project an estimate of what is expected to happen in the future.

How is time-series different from other types of modeling? Time-series analysis only takes trends into consideration whether it is seasonality or year over year growth. Time-series does not take other variables into account. So, questions like, “What if I raise my bids?” or “What if the economy crashes?” aren’t accounted for using time-series modeling. Time-Series modeling only uses two variables, a time variable, ds, and y, the value you are trying to predict.

What is Prophet?

Facebook describes Prophet as: >Prophet is a procedure for forecasting time series data based on an additive model where non-linear trends are fit with yearly, weekly, and daily seasonality, plus holiday effects. It works best with time series that have strong seasonal effects and several seasons of historical data. Prophet is robust to missing data and shifts in the trend, and typically handles outliers well.

If the above does not make complete sense to you, no worries. The same way you do not need to know quantum mechanics to use a cellphone, you do not have to know the details of what Prophet is doing under the hood to make reliable predictions using Time Series Analysis.

What can we use Prophet for in Digital Marketing?

Prophet can be used to predict all types of variables like rise in CPA, revenue, clicks, visits. Anything that has a seasonal trend can be used by Prophet to predict future outcomes. ’

Once you have these predictions, you can then compare to actual outcomes and see if your efforts helped drive performance higher than expectations or lower. This can help answer the question, is it seasonality or is it something else?

Getting Started

In this tutorial, we will use Prophet and R to solve a real-world problem digital marketers often face: forecasting sales for a marketing channel for a year. Wew ill then compare that the forecast we predicted to actual performance.

We will be training our models on data from 2016 to 2019 and then compare it to 2020 to see how accurate how actual performance compares to our expected results. It is advised that we use atleast one years worth of data to make a forecast. We will add special timer perioud into the model to show how we can tune Prophet to produce more accurate forecast.

Hopefully, this sparks some inspiration for you to dive even deeper into the library’s vast functionality.

We will be using R and going over the following major steps: + Setting up our R environment + Getting and preparing the Data + Create a Prophet Model + Forecast and visualize + Comparing predicted to actual results

Setting Up

We will first install and load the neccessary libraries.

Getting the Data Into R

To make this accessible to users who are familiar with R, I downloaded daily performance as a CSV file starting from 2016 until December 20 directly from the Google Ads interface. If you have a database, you can connect R directly to your db.

Prophet requires the following qualities: + No large missing gaps: There should be no periods with large time gaps. If there are some gaps, it’s okay to get the mean of surrounding days and replace those gaps with the mean. + One Entry Per Day: One entry for whatever time granularity you are using. If you have multiple entries per day, make you aggregate your data so that you have only one entry per day. + Column Names: The y variable which is what you’re measuring and the time variable, which Prophet needs it to be labeled as ds.

Make sure that you take note of the file name and that it is in your Working Directory. In the following code chunk, we will enter our csv file into R and then take a quick peek usig the dlookr’s diagnose function to see the following:

• Variables
• Data Type
• Missing Data
• Repeated Data

shopping_raw <- read.csv("day.csv",  ## reads the csv file
                      header = TRUE)

shopping_raw %>% 
  diagnose() %>% 
  kbl() 

Taking a quick glance at the data, we realize the following:

• Unnecessary Data: 13 columns, we only need two.
• Day Format: Day is a factor and not in Date format
• Days are Unique: There are no repeated days, which is exactly how we need it.

Prepare the Data

The Prophet Library requires that you you used two columns labeled ds for time and y for the variable you’re trying to predict. It is also highly recommended that all date entries be even, unique, without any large gaps. The ds time variable has to be in the “yyyy-m-d” time format.

In the next step, We will do the following:

• Remove unnecessary Columns
  • Reformat day to time format
  • Rename columns to ds for time and y for conversions
  • Visualize the data to inspect for abnormalities like missing dates

shopping <- shopping_raw[,c(1,10)] %>% # Selects only necessary columns
  mutate(Date=as.Date(mdy(Day))) %>%   # Changes day to date format in the right order
  rename(ds=Date, y=Conversions) %>%   # renames columns into ds and y
  arrange(ds) %>%  # orders the data by date 
  select(ds, y)  # pulls columns needed

ggplot(shopping,  aes(ds, y)) +  # Creates scatterplot
  geom_point(alpha=.3) +
  ggtitle("Google Shopping Sales Over Time") + 
  theme_minimal()

Looking at the chart above and the following sticks out: + No obvious times gaps + Sales spike during the end-of-the-year Holiday Season + It seems the pandemic had little impact on sales in 2020

Sales peak at the end of every year. It is my suspicioun that these peak days are most likely around the Black Friday weekend. Lets confirm this by looking at the top three sales dates for every year.

shopping %>% 
  mutate(year=year(ds)) %>% # extracts year from ds column
  group_by(year) %>% 
  arrange(desc(y)) %>% # order data by y descending per year
  mutate(rank = row_number()) %>% # assigns row_number per year
  select(-y) %>% 
  filter(rank <= 3) %>% #shows only top three per year
  spread(year, ds) %>%  #formats to wide format
  kbl(caption = "Top 3 Sales Dates Per Year", )  %>% 
  kable_styling() 

Looking at the top three sales dates for the past five years,we see that the all fall into the next three groups: + Black Friday + Cyber Monday + December 18 and the ten day period before the last day one can order and get it before the Christmas

Holidays and Special Dates Custom Dates

Prophet has a feature that automatically adds all national holidays to your data set with one simple function.

shopping <- add_country_holidays(shopping,
                                 country_name = 'US')

black_friday_weekend <- data.table(
  holiday = 'Black Friday', # we name the holiday
  ds=as.Date(c('2016-11-25',# Black Friday dates for the last 5 years
               '2017-11-24',
               '2018-11-23',
               '2019-11-29',
               '2020-11-27' )),
  lower_window = 0,
  upper_window = 4 # Creates a window for the 4 days following Black Friday
)

last_shopping_days <- data.table(
  holiday = 'Before Christmas', 
  ds=as.Date(c('2016-12-18',
               '2017-12-18',
               '2018-12-18',
               '2019-12-18',
               '2020-12-18' )),
  lower_window = -10, # looks back at the ten days before the last day to shop and get it before Christmas
  upper_window = 0
)

# Combines both tables we just created
holiday_season <- rbind(black_friday_weekend,
                        last_shopping_days)

# Creates Training Data
shopping_train <- shopping %>%
  filter(ds <"2020-01-01")

# Creates Test Data 
shopping_test <- shopping %>%
  filter(ds >="2020-01-01")

Creating Our First Model

Now that we added our custom holidays table and split our data, we will use Prophet to create our first model, m and include our custom holidays table, using the prophet function.

m <- prophet(shopping_train,  # Creates our forecast model
             holidays = holiday_season) 

Forecasting 2020 Sales

Now that we have our model, lets create a dataframe for the next 365 days or the amount of days you want to forecast for which we can then use to feed into our predict function.

future <- make_future_dataframe(m, periods = 365)

Now that we have our future dateframe of days we want to forecast for, we we will feed our model, “m” and “future” into the predict function.

forecast <- predict(m, future) # creates our forecast using our model and future dates

We grab our model and our forecast and plot it to visualize what our forecast looks like layered over past data.

plot(m, forecast) + theme_minimal() 

plot(m, forecast) + add_changepoints_to_plot(m)

Overall Trend and Seasonality

Overall, Prophet predicts higher sales in 2020 than it did in 2019. Lets take a look at the different trends by calling the prophet_plot_components function.

prophet_plot_components(m, forecast)

Loooking at the various components, I have the following observations:

• Sales are trending up Sales are trening up year over year.
• Black Friday Weekend Spike There is a big spike on Black Friday following by smaller spike days two weeks before Christmas.
• Sundays are the bestThe best sales day is Sunday and stays strong until Wednesday, when it then tapers off to the worst day, Saturday.
• End-of-Year Bump The best time of year for sales begin in the later half in November then taper off as it January approaches.

Comparing Forecast to Reality

When we split our data, we excluded 2020 data from our model to compare our shopping channels sales performance to what was forecasted. In the following code chunk, we will join our forecast table with our test set containing 2020 data. We will then create a delta column showing the difference between forecasted sales and actual sales. We will visualize what the delta looks like over time.

s <- forecast %>% 
  inner_join(shopping_test, by="ds") %>% 
  mutate(
         delta = (y-yhat), 
         isPositive= delta >= 0)

s %>% 
  ggplot(aes(ds, delta, fill=isPositive)) + 
  geom_col(position = "identity") +
  theme_minimal() + 
  ggtitle("Delta Between Expected Sales & Actual",
          subtitle = "Sales did not meet expectations for most of the year")

From what we see above, we observe the following: + For most of 2020, sales underperformed when compared to the previous trend. + A strong effort prior to Black Friday that exceeded expectations. + The shopping campaign did not have a strong enough follow through during the period following the Black Friday Weekend.

qplot(s$y, s$yhat, log="xy") +
  geom_abline() +
  ggtitle("Comparing the Logs of Expected versus Real Sales") +
  theme_minimal()

When we compare our expected sales, (yhat) against actual sales, (y), we see that predictions were closer to being accurate on high sales days.

Now that we see that our sales were below expectations, we can start digging into why and what happened? Was it Covid? Did we this account as much? Did CPM’s go up? Did the landing page experience change dramatically?

Conclusion

As you can see, thanks to the generosity of the Data Science team at Facebook, we can now create great forecast without being an expert in statitistics or machine learning.

If you have any questions, comments, would like to share your experience using Prophet, please feel free to share!