Marketing Intelligence

1.1. Clean the dataset, and generate new variables

## Convert the Campaign variable to factor

mc_df <- mc_df %>% 
  mutate(Campaign = fct_relevel(Campaign,"Aldebaran","Bartledan","Cottington")) 

## Remove duplicates
mc_df <- mc_df %>% 
  unique()

## Generate a profit variable
mc_df <- mc_df %>% 
  mutate(Profit = Revenue - Cost)

## Weekly_RPV
mc_df <- mc_df %>% 
  mutate(Weekly_RPV = Revenue /Visits)

1.2 Exploring the trend of visits for each of the campaigns

The Aldebaran campaign seems to have done really well in terms of attracting visitors to the site, all through the campaign period. As much as the number of visits was quite low in the beginning (as compared to the other two campaigns), and with very few dips in the number of visits here and there, there was a good increasing trend overall.

The Bartledan campaign started off at a steady rate, until week 14, where the number of visits to the site picked up a bit till the end.

The Cottington campaign maintained a low but steady state in the number of visits all through the campaign period.

graph <- mc_df %>% 
  ggplot(aes(x = as.factor(Week), y=Visits, group = Campaign,color = Campaign))+
  geom_line(size = 1.1)+
  Company_XXX_theme+
  scale_color_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
  labs(title = "Distribution of the Number of Visits",
       x = "Week", y="Number of Visits",color = "Campaign")
graph

1.3 Revenue Per Visitor

RPV is the average revenue per visitor to your website.

Here, we are assuming that a visit represents a unique visitor.

RPV is calculated by dividing the total income by the number of visitors during a specific time period.

We can see that as much the Cottington campaign maintained a low but steady state in the number of visits all through the campaign period (as shown in the previous section), the RPV was the highest, amongst all the three campaigns.

This means that the low number of visitors actually generated higher revenue as opposed to the revene that was generated by the higher number of visitors on the other two campaigns.

graph <- mc_df %>% 
  ggplot(aes(x = as.factor(Week), y=Weekly_RPV, group = Campaign,color = Campaign))+
  geom_line(size = 1.1)+
  Company_XXX_theme+
  scale_color_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
  labs(title = "Weekly Revenue Per Visitor",
       x = "Week", y="Revenue Per Visitor",color = "Campaign")
graph

## Generating ROMI
RPV_df <- mc_df %>% 
  group_by(Campaign) %>% 
  summarise(RPV = round(sum(Revenue)/ sum(Visits),1)) 

## Generate the plot
graph <- ggplot(data = RPV_df,
              mapping = aes(x = Campaign, y = RPV, fill = Campaign))+
              geom_bar(stat = "identity")+
              geom_text(aes(label = RPV),vjust = -0.25, size = 5)+
              Company_XXX_theme+
              scale_fill_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
              labs(title = "Overall Revenue Per Visitor",
              x = "Campaign", y="Revenue Per Visitor",color = "Campaign")
graph

1.4 Assessing profitability of each of the campaigns over the weeks

Here, Profit = Revenue - Cost

In terms of profitability, the Bartledan campaign was the worst performer as it never generated any profit.

The Cottington Campaign was doing well, until Week 20, when it started generating losses.

Coincidentally, Week 20 is the same week that Aldebaran came out of the red, and started generating profits.

graph <- mc_df %>% 
  ggplot(aes(x = as.factor(Week), y=Profit, group = Campaign,color = Campaign))+
  geom_line(size = 1.1)+
  geom_hline(yintercept = 0,color="black", linetype = "dashed")+
  geom_vline(xintercept = 20,color="red", linetype = "dashed")+
  Company_XXX_theme+
  scale_color_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
  labs(title = "Assessing Weekly Campaign Profitability",
       x = "Week", y="Profitability",color = "Campaign")
graph

1.5 Assessing Overall Campaign Profitability

Throughout the campaign period, the Cottington campaign is the only one that made a significant amount of profit.

abs_df <- mc_df %>% 
  select(Week, Campaign, Revenue, Cost) %>% 
  pivot_longer(cols = Revenue:Cost, names_to ="Revenue_Cost" ,values_to = "Value") %>% 
  group_by(Campaign,Revenue_Cost) %>% 
  summarise(Value = round(sum(Value),1))

## Generate the plot
graph <- ggplot(data = abs_df,
              mapping = aes(x = Campaign, y = Value, fill = Revenue_Cost))+
              geom_bar(stat = "identity", position = "dodge")+
            geom_text(aes(label = Value),vjust = -0.25, size = 5, position = position_dodge(width = 1))+
              Company_XXX_theme+
              scale_fill_manual(values = c("#377DA9","#E79435"))+
              labs(title = "Assessing Overall Campaign Profitability",
              x = "Campaign", y="Revenue/Cost",color = "Measure")

graph

1.6 Return on Marketing Investment (ROMI)

ROMI is an indication of return on investment in marketing.

ROMI = [Total sales - marketing campaign costs / marketing campaign costs]

There is a larger ROMI on the Cottington campaign, as compared to the Aldebaran campaign. The Bartledan resulted into a negative ROMI, even though the number of visits to the site kept on increasing, as the weeks flew by.

I would advise the business developer for the market to invest in the Cotington Campaign. This is because as much as the campaign generally attracts a smaller number of visitors, as compared to the other campaigns, the ROMI is high, and the Revenue per Visitor is also high.

## Generating ROMI
ROMI_df <- mc_df %>% 
  group_by(Campaign) %>% 
  summarise(ROMI_abs = (sum(Revenue)-sum(Cost)) / sum(Cost),
            ROMI_perc = round(ROMI_abs * 100,2)) 

## Generate the plot
graph <- ggplot(data = ROMI_df,
              mapping = aes(x = Campaign, y = ROMI_perc, fill = Campaign))+
              geom_bar(stat = "identity")+
              geom_text(aes(label = ROMI_perc),vjust = -0.25, size = 5)+
              Company_XXX_theme+
              scale_fill_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
              labs(title = "Assessing Campaign Profitability \n (ROMI)",
              x = "Campaign", y="ROMI (%)",color = "Campaign")+
  ylim(-15,5)
graph

1.7 Does the type of campaign predict profit?

Company_XXX is likely to obtain a significant profit of 11.2 for an additional investment on the Cottington campaign, as opposed to the investment being made on the Aldebaran campaign.

Company_XXX would make a huge loss (27.6) if it invested cash on the Bartledan campaign.

model1 <- lm(Profit ~ Campaign, data = mc_df)
summary(model1)

Call:
lm(formula = Profit ~ Campaign, data = mc_df)

Residuals:
    Min      1Q  Median      3Q     Max 
-56.473  -9.864   1.269  14.199  46.555 

Coefficients:
                   Estimate Std. Error t value    Pr(>|t|)    
(Intercept)          0.9631     3.6803   0.262      0.7942    
CampaignBartledan  -27.5866     5.2047  -5.300 0.000000864 ***
CampaignCottington  11.2372     5.2047   2.159      0.0336 *  
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Residual standard error: 20.16 on 87 degrees of freedom
Multiple R-squared:  0.4038,    Adjusted R-squared:  0.3901 
F-statistic: 29.47 on 2 and 87 DF,  p-value: 0.0000000001693

## Generating a tidy table
model1_tidy <- broom::tidy(model1)
basevalues <- c("CampaignAldebaran",0,  5.204708,   0,  0)
model1_tidy <- rbind(model1_tidy,basevalues)
model1_tidy$term <- gsub("Campaign","",model1_tidy$term)
model1_tidy <- model1_tidy%>% filter(term !="(Intercept)")
model1_tidy <- model1_tidy%>% mutate(estimate = round(as.numeric(estimate),1))
model1_tidy <- model1_tidy%>% rename(Campaign = term)
#model1_tidy$estimate <- round(model1_tidy$estimate)

## Generate the plot
graph <- ggplot(data = model1_tidy,
              mapping = aes(x = Campaign, y = estimate, fill = Campaign))+
              geom_bar(stat = "identity")+
              geom_text(aes(label = estimate),vjust = -0.25, size = 5)+
              Company_XXX_theme+
              scale_fill_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
              labs(title = "Profit ~ Campaign",
              x = "Campaign", y="Regression Estimate",color = "Campaign")+
  ylim(-30,13)
graph

1.8 Does the type of campaign predict number of visits?

model2 <- lm(Visits ~ Campaign, data = mc_df)
summary(model2)

Call:
lm(formula = Visits ~ Campaign, data = mc_df)

Residuals:
     Min       1Q   Median       3Q      Max 
-293.667  -33.933    0.233   20.067  292.333 

Coefficients:
                   Estimate Std. Error t value             Pr(>|t|)    
(Intercept)          320.67      19.14  16.752 < 0.0000000000000002 ***
CampaignBartledan   -147.73      27.07  -5.457         0.0000004493 ***
CampaignCottington  -169.90      27.07  -6.276         0.0000000131 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

Residual standard error: 104.8 on 87 degrees of freedom
Multiple R-squared:  0.3486,    Adjusted R-squared:  0.3336 
F-statistic: 23.28 on 2 and 87 DF,  p-value: 0.000000007978

## Generating a tidy table
model2_tidy <- broom::tidy(model2)
basevalues <- c("CampaignAldebaran",0,  5.204708,   0,  0)
model2_tidy <- rbind(model2_tidy,basevalues)
model2_tidy$term <- gsub("Campaign","",model2_tidy$term)
model2_tidy <- model2_tidy%>% filter(term !="(Intercept)")
model2_tidy <- model2_tidy%>% mutate(estimate = round(as.numeric(estimate),1))
model2_tidy <- model2_tidy%>% rename(Campaign = term)
#model2_tidy$estimate <- round(model2_tidy$estimate)

## Generate the plot
graph <- ggplot(data = model2_tidy,
              mapping = aes(x = Campaign, y = estimate, fill = Campaign))+
              geom_bar(stat = "identity")+
              geom_text(aes(label = estimate),vjust = -0.25, size = 5)+
              Company_XXX_theme+
              scale_fill_manual(values = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue))+
              labs(title = "Number of Visits ~ Campaign",
              x = "Campaign", y="Regression Estimate",color = "Campaign")
graph

2.1 Create additional variables

duration: the length of time taken on the session

start_hour: the hour when the session started.

time_of_day: the time of day i.e Early Morning, Morning, Afternoon, Evening

## Session duration

sessions_df <- sessions_df%>% 
  mutate(duration = difftime(session_end_text, session_start_text, units = "secs",tz = "EAT"),
         duration = ifelse(duration <0, (24*60*60)+duration, duration))

## Start hour 
sessions_df <- sessions_df%>% 
  mutate(start_hour = hour(session_start_text)) 


## hour_of_day
sessions_df <- sessions_df %>% 
  mutate(start_hour = as.numeric(start_hour)) %>% 
  mutate(time_of_day = ifelse(start_hour >=0 & start_hour <=5,"Early Morning",
                        ifelse(start_hour >=6 & start_hour <=11,"Morning",
                        ifelse(start_hour >=12 & start_hour <=18,"Afternoon",
                        ifelse(start_hour >=19 & start_hour <=23,"Evening","")))))

2.2 Is there a difference in means of booking, between the different times of day?

H0: The mean of the booking variable, for all the different times = 0

Ha: At least one of the means is not 0

The P-value is very large (>0.05) meaning that the means are not really different from each other, and that this variable is not predictive of the instance of booking.

## Generate anova results
anova_test <- aov(booking ~ time_of_day, data = sessions_df)
summary(anova_test)
              Df Sum Sq Mean Sq F value Pr(>F)
time_of_day    3    0.1 0.03623   0.415  0.742
Residuals   9996  873.4 0.08737               
TukeyHSD(anova_test)
  Tukey multiple comparisons of means
    95% family-wise confidence level

Fit: aov(formula = booking ~ time_of_day, data = sessions_df)

$time_of_day
                                diff         lwr        upr     p adj
Early Morning-Afternoon  0.001126106 -0.01967051 0.02192273 0.9990396
Evening-Afternoon        0.007688625 -0.01407098 0.02944823 0.8006404
Morning-Afternoon        0.006476426 -0.01419107 0.02714393 0.8520366
Evening-Early Morning    0.006562519 -0.01597362 0.02909866 0.8774553
Morning-Early Morning    0.005350320 -0.01613322 0.02683386 0.9190753
Morning-Evening         -0.001212199 -0.02362924 0.02120484 0.9990434

2.3 What is the correlation between the continuous variables?

There is no correlation between the ‘booking variable’ and any othe variables. Meaning none of the variables can predict booking.

## Generate the correlation matrix
corr_mat <- cor(sessions_df %>% select(booking, clickouts, duration, start_hour))
corr_mat
                 booking    clickouts   duration    start_hour
booking     1.0000000000 -0.049811677 0.01044032  0.0009995598
clickouts  -0.0498116772  1.000000000 0.03979617 -0.0077694714
duration    0.0104403163  0.039796170 1.00000000  0.0154922952
start_hour  0.0009995598 -0.007769471 0.01549230  1.0000000000

## Generate the p-values of this correlation matrix
p.mat <- cor_pmat(corr_mat)
p.mat
             booking clickouts  duration start_hour
booking    0.0000000 0.5628733 0.6811493  0.6836681
clickouts  0.5628733 0.0000000 0.7530925  0.6518106
duration   0.6811493 0.7530925 0.0000000  0.6746880
start_hour 0.6836681 0.6518106 0.6746880  0.0000000

## Generate the correlation plot.
ggcorrplot(corr_mat,
   outline.col = "white",lab = TRUE,
   ggtheme = Company_XXX_theme,
   colors = c(Company_XXX_maroon, Company_XXX_yellow, Company_XXX_blue),
   title = "Correlation Plot")