library(openxlsx)
library(dplyr)
library(tidyr)
library(lubridate)
library(ggplot2)
library(ggpubr)
library(tools)
library(reshape2)
library(zoo)

sessionCounts <- read.csv("C:/Users/Saeah Go/OneDrive/Desktop/IXIS/R Assessment/Dataset/DataAnalyst_Ecom_data_sessionCounts.csv")

addsToCart <- read.csv("C:/Users/Saeah Go/OneDrive/Desktop/IXIS/R Assessment/Dataset/DataAnalyst_Ecom_data_addsToCart.csv")

# sessionCounts table
str(sessionCounts)

sessionCounts$dim_date=mdy(sessionCounts$dim_date)

# addsToCart table
str(addsToCart)

addsToCart<- addsToCart %>% unite(dim_year, dim_month, col = "Date", sep="-")
addsToCart$Date = ymd(paste0(addsToCart$Date, "-01"))

firstSheet <- sessionCounts

# drop dim_browser
firstSheet <- subset(firstSheet, select = -dim_browser)

firstSheet <- firstSheet %>% 
  group_by(Date=lubridate::floor_date(dim_date, 'month'), dim_deviceCategory) %>% 
  summarise(
    Sessions = sum(sessions), 
    Transactions = sum(transactions), 
    ECR = (Transactions/Sessions), .groups = 'drop')

# rename column name
colnames(firstSheet)[colnames(firstSheet) == "dim_deviceCategory"] <- "Device"

secondSheet <- firstSheet %>% 
  arrange(desc(ymd(firstSheet$Date))) %>% 
  slice(1:6) %>% 
  pivot_wider(names_from = Device, values_from = c(Sessions, Transactions, ECR))

# join two tables
secondSheet <- secondSheet %>% left_join(addsToCart, by = "Date")

secondSheet_t <- data.frame(t(secondSheet))
colnames(secondSheet_t) = secondSheet_t[1,] # date row will be the header
secondSheet_t = secondSheet_t[-1,] # remove the date row

# convert values from char to numeric 
secondSheet_t <- mutate_all(secondSheet_t, function(x) as.numeric(x))

# month over month comparison
secondSheet_t <- secondSheet_t %>% 
  mutate(
    mom_var = `2013-06-01`-`2013-05-01`, # MOM variance
    mom_perc = mom_var - `2013-05-01`, # MOM percentage (growth or shrinking)
    abs_diff = abs(`2013-06-01`-`2013-05-01`), # absolute difference
    rel_diff = (`2013-06-01`-`2013-05-01`)/`2013-05-01` * 100 # relative difference 
  )

secondSheet_t_vis <- cbind(Devices = rownames(secondSheet_t), secondSheet_t)
rownames(secondSheet_t_vis) <- 1:nrow(secondSheet_t_vis)

vis_data_ECR <- secondSheet_t_vis %>% 
  filter(grepl('ECR', Devices)) %>% 
  select(c(1,2,3)) %>% 
  melt(id.vars = 'Devices', variable.name = 'Month', value.name = 'ECR') 

# change the Month column from mdy format to my format
vis_data_ECR$Month <- as.Date(vis_data_ECR$Month)
vis_data_ECR$Month <- format(vis_data_ECR$Month,"%Y-%m")

# change the device column values
vis_data_ECR$Devices <- gsub("ECR_", "", vis_data_ECR$Devices) # remove ECR_ part 
vis_data_ECR$Devices <- toTitleCase(vis_data_ECR$Devices) # capitalize

ggplot(vis_data_ECR, aes(x = Devices, y = ECR, shape = Month, color = Month)) +
  geom_point(size = 6) +
  theme_bw() +
  scale_color_manual(values = c('2013-06' = "red", '2013-05' = "blue")) +
  scale_shape_manual(values = c('2013-06' = 16, '2013-05' = 16)) +
  ggtitle("Month over Month Summary") +
  labs(subtitle = "May 2013, June 2013 ECR Summary by Devices") +
  xlab("Devices") + ylab("ECR") +
  theme(legend.position = "right",
        plot.title = element_text(size = 15))

ggsave("ECR Summary.jpeg", width = 5, height = 6)

# change the device column values
vis_data_abs_diff <- secondSheet_t_vis %>% 
  filter(!grepl('ECR', Devices))
vis_data_abs_diff$Devices <- gsub("_", " - ", vis_data_abs_diff$Devices) 
vis_data_abs_diff$Devices <- toTitleCase(vis_data_abs_diff$Devices) # capitalize

ggplot(vis_data_abs_diff, aes(Devices, mom_var, fill = Devices)) +
  geom_bar(stat = "identity", position = "dodge") +
  xlab("Measure") + ylab("Variance") +
  ggtitle("Variance Summary") +
  labs(subtitle = "Sessions, Transactions, and AddsToCart") +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "right",
        plot.title = element_text(size = 15)) 

ggsave("Variance Summary.jpeg", width = 8, height = 6)

# change the device column values
vis_data_rel_diff <- secondSheet_t_vis
vis_data_rel_diff$Devices <- gsub("_", " - ", vis_data_rel_diff$Devices) 
vis_data_rel_diff$Devices <- toTitleCase(vis_data_rel_diff$Devices) # capitalize

ggplot(vis_data_rel_diff, aes(Devices, rel_diff, fill = Devices)) +
  geom_bar(stat = "identity", position = "dodge") +
  xlab("Measure") + ylab("Relative Differences (%)") +
  ggtitle("Relative Difference Summary") +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "right",
        plot.title = element_text(size = 15)) 

ggsave("Rel Diff Summary.jpeg", width = 8, height = 6)

str(firstSheet)

# convert from mmddyyyy to mmyyyy format for the first sheet
firstSheet$Date <- format(firstSheet$Date, "%m-%Y")

str(secondSheet_t)

# rename column name
colnames(secondSheet_t)[colnames(secondSheet_t) == "2013-06-01"] <- "06-2013"
colnames(secondSheet_t)[colnames(secondSheet_t) == "2013-05-01"] <- "05-2013"

excel_sheet <- list('Sheet1' = firstSheet, 'Sheet2' = secondSheet_t)
write.xlsx(excel_sheet, file = 'OnlineRetailerPerformanceAnalysis.xlsx')