TS Sales Prediction

1.1 Import data

data <- read_csv("Sales Analyst CL Ltd.csv") %>%
  clean_names()
update <- read_csv("clean_update.csv") %>%
  clean_names()
# Check dataset
glimpse(data)

## Rows: 1,593
## Columns: 5
## $ date  <dbl> 20240423, 20240423, 20240424, 20240424, 20240425, 20240427, 2024…
## $ sku   <chr> "B0B1X3N2LX", "UF24917", "B0B1X3N2LX", "B0B1X3N2LX", "B0B1X3N2LX…
## $ qty   <dbl> 1, 2, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ price <dbl> 10, 35, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, …
## $ sale  <dbl> 10, 70, 10, 10, 20, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, …

1.2 Data Cleaning

data <- data %>%
  mutate(date = ymd(date),
         year = year(date),
         month = as.factor(month.abb[month(date)]),
         day = day(date),
         wday = wday(date, label = TRUE)) %>%
  select(sku, qty, month, day, wday, year, date)

update <- update %>%
  mutate(date = ymd(date)) %>%
  select(sku, qty, date)

1.3 Data Manipulation

1.3.1 Historical Data

data_ts <- data %>%
  group_by(date) %>%
  summarise(total_sale_qty = sum(qty))

1.3.2 Updated Data

# Set up date range
start_date <- as.Date("2024-06-15")
end_date <- as.Date("2024-06-17")
num_dates <- 143  # Number of dates to generate

# Generate random dates
random_dates <- sample(seq(start_date, end_date, by = "day"), num_dates, replace = TRUE)
table(random_dates)

## random_dates
## 2024-06-15 2024-06-16 2024-06-17 
##         45         36         62

# Change part of 0615
update_df <- update %>%
  mutate(date_atc = random_dates) %>%
  arrange(date_atc)
update_df[1:19, 4] <- as.Date("2024-06-16")
table(update_df$date_atc)

## 
## 2024-06-15 2024-06-16 2024-06-17 
##         26         55         62

# Change part of 0617
update_df <- update_df %>%
  arrange(date_atc)
update_df[136:143, 4] <- as.Date("2024-06-16")

# Data aggregation
update_df <- update_df %>%
  group_by(date_atc) %>%
  summarise(total_sale_qty = sum(qty))
colnames(update_df)[1] <- c("date")

1.3.3 Merging Data

df_ts <- rbind(data_ts, update_df)

2. Data Visualization

df_ts %>%
  plot_time_series(date, total_sale_qty, .interactive = TRUE)

3. Sale Forecasting

3.1 Split dataset

set.seed(231)

#Split data into test and training set
split <- df_ts %>%
  time_series_split(date_var = date, 
                    assess = "1 months", # use the last month as test set
                    cumulative = TRUE)
#Visualize test train split
split %>%
  tk_time_series_cv_plan() %>%
  plot_time_series_cv_plan(date, total_sale_qty, .interactive = TRUE)

3.2 Model Fit

model_fit_prophet  <- prophet_reg() %>%
    set_engine('prophet') %>% 
  fit(total_sale_qty ~ ., data = training(split))

# Put model into a modeltime table
models_tbl <- modeltime_table(model_fit_prophet)
models_tbl

## # Modeltime Table
## # A tibble: 1 × 3
##   .model_id .model   .model_desc
##       <int> <list>   <chr>      
## 1         1 <fit[+]> PROPHET

# Calibrate model
calibration_tbl <- models_tbl %>% 
  modeltime_calibrate(new_data = testing(split))

# Check model accuracy
(accuracy_table <- calibration_tbl %>%
    modeltime_accuracy())

## # A tibble: 1 × 9
##   .model_id .model_desc .type   mae  mape  mase smape  rmse    rsq
##       <int> <chr>       <chr> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
## 1         1 PROPHET     Test   41.7  138. 0.725  59.6  58.2 0.0647

3.3 Forecast test data

#Step 6: Create future forecast on test data
(forecast_tbl <- calibration_tbl %>%
    modeltime_forecast(
        new_data    = testing(split),
        actual_data = data_ts,
        keep_data = TRUE
    ))

## # A tibble: 68 × 9
##    .model_id .model_desc .key   .index     .value .conf_lo .conf_hi date      
##        <int> <chr>       <fct>  <date>      <dbl>    <dbl>    <dbl> <date>    
##  1        NA ACTUAL      actual 2024-04-23      3       NA       NA 2024-04-23
##  2        NA ACTUAL      actual 2024-04-24      2       NA       NA 2024-04-24
##  3        NA ACTUAL      actual 2024-04-25      2       NA       NA 2024-04-25
##  4        NA ACTUAL      actual 2024-04-27      1       NA       NA 2024-04-27
##  5        NA ACTUAL      actual 2024-04-28      8       NA       NA 2024-04-28
##  6        NA ACTUAL      actual 2024-04-30     12       NA       NA 2024-04-30
##  7        NA ACTUAL      actual 2024-05-01      6       NA       NA 2024-05-01
##  8        NA ACTUAL      actual 2024-05-02      2       NA       NA 2024-05-02
##  9        NA ACTUAL      actual 2024-05-03     19       NA       NA 2024-05-03
## 10        NA ACTUAL      actual 2024-05-04      5       NA       NA 2024-05-04
## # ℹ 58 more rows
## # ℹ 1 more variable: total_sale_qty <dbl>

plot_modeltime_forecast(forecast_tbl)

#Create a tibble of observations with length out being the number of observations we want in reference to our date variable (new_data)
#Create tibble of dates
dates <- df_ts %>% 
  future_frame(date, .length_out = "3 months")

#Put data into dataframe
explanatory_data <- dates

# Refit to the full dataset
refit_tbl <- calibration_tbl %>%
  modeltime_refit(df_ts)

#Forecast on the new tibble dataframe
forecast_tbl_future_data <- refit_tbl %>%
    modeltime_forecast(
        new_data    = dates
    )

#Check results of forecast
head(forecast_tbl_future_data)

## # A tibble: 6 × 7
##   .model_id .model_desc .key       .index     .value .conf_lo .conf_hi
##       <int> <chr>       <fct>      <date>      <dbl>    <dbl>    <dbl>
## 1         1 PROPHET     prediction 2024-06-18  113.     -1.96     229.
## 2         1 PROPHET     prediction 2024-06-19   87.8   -27.6      203.
## 3         1 PROPHET     prediction 2024-06-20   86.3   -29.1      202.
## 4         1 PROPHET     prediction 2024-06-21   76.2   -39.1      192.
## 5         1 PROPHET     prediction 2024-06-22   66.3   -49.1      182.
## 6         1 PROPHET     prediction 2024-06-23  100.    -15.1      216.

plot_modeltime_forecast(forecast_tbl_future_data, .interactive = TRUE)