Criteo.rmd

---
title: "Criteo Interview"
author: "Vladimir Mikheev"
date: "05 07 2021"
output:
  html_document:
    code_download: yes
    fontsize: 8pt
    highlight: textmate
    number_sections: no
    theme: flatly
    toc: yes
    toc_float:
      collapsed: no
---


```{r setup, include=FALSE}
knitr::opts_chunk$set(warning = FALSE, message = FALSE)
library(dplyr)
library(data.table)
library(readr)
library(lme4)
library(broom)
library(ggplot2)
library(tidyr)
library(arm)
library(ggpmisc)
library("ggpubr")
library(formattable)
library(parameters)
library(sjPlot)



remove_outliers <- function(x, na.rm = TRUE, ...) {
  qnt <- quantile(x, probs=c(.25, .75), na.rm = na.rm, ...)
  H <- 1.5 * IQR(x, na.rm = na.rm)
  y <- x
  y[x < (qnt[1] - H)] <- NA
  y[x > (qnt[2] + H)] <- NA
  y
}
#Sys.setlocale("LC_ALL", "English")
Sys.setlocale("LC_ALL", "en_GB.UTF-8")
```



```{r, include=FALSE}
#wf <- 'C:/Users/Vladimir/YandexDisk/Достижения/Criteo/ecommerce_dataset.csv'
wf <- "/home/vovan/Downloads/Criteo_case/ecommerce_dataset.csv"
#df <- fread('unzip -cq C:/Users/Vladimir/YandexDisk/Достижения/Criteo/ecommerce_dataset.rar') 
raw <- fread(wf) 


```

```{r, include=FALSE}
raw %>% 
  filter(Environment == 'app_android') %>% 
  group_by(`Device type`) %>% summarise(n = n())

raw %>% 
  filter(`Device type` == 'Unknown') %>% nrow()

raw %>% 
  filter(`Browser family` == 'other') %>% nrow()

```

```{r, include=FALSE}
df <- raw %>% mutate(
  Datetime =as.POSIXct(as.numeric(as.character(Timestamp)), origin="1970-01-01", tz="GMT"), 
                           `Product Price` = as.numeric(`Product Price`), 
                           `Product quantity` = as.numeric(`Product quantity`),
  Date = as.Date(Date, "%d/%m/%y"), 
  `Device type` = if_else(`Device type` == 'Unknown', "Android app", `Device type`),
  `Browser family` = if_else(`Browser family` == 'other', "Android app", `Browser family`)
  ) %>% 
  mutate(`Device group` = case_when(
    `Device type` %in% c('Android - Smartphone', 'iPhone', 'Mobile - Other') ~ 'Smartphone',
    `Device type` %in% c('Android - Tablet', 'iPad') ~ 'Tablet',
    `Device type` == 'Android app' ~ 'Android app',
    `Device type` == 'Desktop' ~ 'Desktop'
  ))
  
df$`Event type` = factor(df$`Event type`, levels=c('Listing', 'Product', 'Basket', 'Sales'))

```

```{r, include=FALSE}
names(df)
```

```{r, include=FALSE}
head(df)
```

# 1. Revenue change over time

Let's observe annual dynamics of revenue.

```{r, include=FALSE}
df_rev <- df %>% filter(`Event type` == 'Sales') %>% group_by(Date) %>% summarize(Revenue = sum(`Product Price` * `Product quantity`))  
df_rev
```

```{r}
ggplot(df_rev, aes(x = Date, y = Revenue )) + 
  geom_col(alpha = 0.5) + 
  ggtitle('The daily revenue change over the year') + 
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5))
```


```{r, echo=FALSE}
 
ggplot(df_rev, aes(x = Date, y = Revenue )) + 
  geom_col(alpha = 0.5)  + 
  stat_peaks(colour = "#D55E00", ignore_threshold = 0.92) + 
  stat_peaks(geom = "text", colour = "red", 
             vjust = -0.5, x.label.fmt = "%d.%m",  ignore_threshold = 0.92)  +
  stat_peaks(span = 1201, color = "#D55E00", geom = "rug", sides = "b", ignore_threshold = 0.92) +
  stat_valleys(colour = "#0072B2",   ignore_threshold = 0.99)  +
  stat_valleys(geom = "text", colour = "#0072B2",  vjust = 1.5, hjust = 1,  x.label.fmt =  "%d.%m",   ignore_threshold = 0.99) + 
    stat_smooth(
    color = "#FC4E07", fill = "#FC4E07",
    method = "loess"
  ) + 
  ggtitle('The daily revenue change over the year') +  
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5))

```

The main characteristics of revenue over year.

```{r, echo=FALSE}
df_rev %>% ungroup() %>% summarize(mean = mean(Revenue), median = median(Revenue), min = min(Revenue),max = max(Revenue)) %>% formattable()

```


The figures above demonstrate the revenue change over a year. There are:

  1. an increase of revenue in October and December with two peaks above 6000€ per day in 23.11 (6265€) and 11.12;
  2. a huge peak near to 6000€ per day in 30.04;
  3. average sales per day are around 1500€;
  4. a revenue decrease in summer months with a lowest sales 154 in 16.07. 

**Recommendations:**

  1. There is a high demand for product in October, December and in the end of April. Advertising in a mentioned period is more preferable for obtaining a revenue; 
  2. On the other hand, there is a lower demand in summer month, so the company should decrease spendings on campaign in this period.
  
## 2.2 Did problems with android app affect graph over the year?

```{r, echo=FALSE}
df %>% 
  filter(`Device type` == 'Android app') %>% 
  group_by(Date) %>% mutate(Visits = n()) %>%  
  ggplot(aes(x = Date, y = Visits, fill = `Event type`)) + geom_col() + facet_wrap(~`Event type`) +
    theme(plot.title = element_text(hjust = 0.5), axis.text.x  = element_text(angle = 90), axis.title.x = element_text(hjust = 2), axis.ticks = element_line(size = 1)) + scale_y_continuous(labels = function(x) format(x, scientific = FALSE))

```
There is a lack of any information about sales and basketings in summer months for Android apps. 
Also, there is no information on Product and Listing events in application at all. 

```{r, echo=FALSE}
 df_rev1 <- df %>% filter(`Event type` == 'Sales')  %>%  filter(`Device type` != 'Android app') %>% group_by(Date) %>% summarise(Revenue = sum(`Product Price` * `Product quantity`))  

ggplot(df_rev1, aes(x = Date, y = Revenue )) + 
  geom_col(alpha = 0.5)  + 
  stat_peaks(colour = "#D55E00", ignore_threshold = 0.92) + 
  stat_peaks(geom = "text", colour = "red", 
             vjust = -0.5, x.label.fmt = "%d.%m",  ignore_threshold = 0.92)  +
  stat_peaks(span = 1201, color = "#D55E00", geom = "rug", sides = "b", ignore_threshold = 0.92) +
  stat_valleys(colour = "#0072B2",   ignore_threshold = 0.99)  +
  stat_valleys(geom = "text", colour = "#0072B2",  vjust = 1.5, hjust = 1,  x.label.fmt =  "%d.%m",   ignore_threshold = 0.99) + 
    stat_smooth(
    color = "#FC4E07", fill = "#FC4E07",
    method = "loess"
  ) + 
  ggtitle('The daily revenue change over the year (apps excluded)') +  
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5))

```


```{r, echo=FALSE}
df_rev1 %>% ungroup() %>% summarize(mean = mean(Revenue), median = median(Revenue), min = min(Revenue),max = max(Revenue)) %>% formattable()

```

We excluded Android app, however we stil observe summer drop in revenue.


# 2. Device usage change over year

Let's observe annual dynamics of revenue for every type of devices.

```{r, echo=FALSE}
df %>% 
  filter(`Event type` == 'Sales', `Device type` != 'Mobile - Other') %>% group_by(Date, `Device type`)   %>% filter(`Device type` != 'Unknown') %>% summarise(Revenue = sum(`Product Price` * `Product quantity`)) %>% 
  ggplot(aes(x = Date, y = Revenue, fill = `Device type`)) +
    geom_col() + facet_wrap(~`Device type`)+
     stat_smooth(
    color = "#FC4E07", fill = "#FC4E07",
    method = "loess"
  ) + ggtitle('Annual sales via different devices') + theme_minimal() +  theme(plot.title = element_text(hjust = 0.5), , axis.text.x  = element_text(angle = 90), axis.title.x = element_text(hjust = 2), axis.ticks = element_line(size = 1)) 
```


```{r, eval = FALSE,  echo=FALSE}
df %>% 
  filter(`Event type` == 'Product', `Device type` != 'Unknown') %>% 
  group_by(Date) %>% mutate(Visits = n()) %>%  
  ggplot(aes(x = Date, y = Visits, color = `Device type` )) + 
  geom_line() + 
  facet_wrap(~`Device type`) +
  theme_minimal() + stat_smooth(
    color = "#FC4E07", fill = "#FC4E07",
    method = "lm") + ggtitle('Annual sales via different devices')
```

These plots do not provide us a new meaningful information except the necessity to check what happens on summer with Android apps.

Let's group different devices on four types: smart phones, tablets, Desktop and, special case, Android app.


```{r, echo=FALSE}
df %>% 
  filter(`Event type` == 'Sales') %>% 
  group_by(Date, `Device group`) %>% summarise(Visits = n()) %>%  
  ggplot(aes(x = Date, y = Visits, fill = `Device group`)) + 
  geom_col(axis.text.x=element_text(angle=45,vjust=0.5)) +  
  facet_wrap(~`Device group`) +
  theme_minimal() + ggtitle('Annual sales via different groups of devices over year') + 
  theme(plot.title = element_text(hjust = 0.5), axis.text.x  = element_text(angle = 90), axis.title.x = element_text(hjust = 2), axis.ticks = element_line(size = 1))  
```

```{r, echo=FALSE}
df %>% 
  filter(`Event type` == 'Sales') %>% 
 group_by(`Device group`) %>%  summarise(Visits = n()) %>%    arrange(desc(Visits)) %>%
  ggplot(aes(x = reorder(`Device group`, Visits), y = Visits, label = Visits, fill = reorder(`Device group`, desc(Visits)))) + 
  geom_col() + geom_text( position = position_dodge(width = 0.9), vjust = -0.5) +
  theme_minimal() + ggtitle('Annual number of sales via different groups of devices') + labs(x = "Device groups", fill = "Device groups") +
  theme(plot.title = element_text(hjust = 0.5))
```

```{r, echo=FALSE}
df %>% 
  filter(`Event type` == 'Sales') %>% 
 group_by(`Device group`) %>%   summarise(Revenue = sum(`Product Price` * `Product quantity`)) %>%     arrange(desc(Revenue)) %>%
  ggplot(aes(x = reorder(`Device group`, Revenue), y = Revenue, label = Revenue, fill = reorder(`Device group`, desc(Revenue)))) + 
  geom_col() + geom_text( position = position_dodge(width = 0.9), vjust = -0.5) +
  theme_minimal() + ggtitle('Annual sales in € via different groups of devices') + 
  labs(x = "Device groups", fill = "Device groups") + 
  theme(plot.title = element_text(hjust = 0.5))
```

Let's discover is this difference is significant?


```{r, echo=FALSE}
a <- df %>% 
  filter(`Event type` == 'Sales') %>%  group_by(Date, `Device group`) %>% 
  mutate(Revenue = `Product Price` * `Product quantity`) %>% 
  dplyr::select(`Device group`, Revenue, Date)
a$`Device group` <- factor(a$`Device group` , levels = c('Desktop', 'Tablet', 'Smartphone', 'Android app'))
tab_model(lm(data = a, Revenue ~ `Device group`))
```

```{r, echo=FALSE}

my_comparisons <- list( c("Smartphone", "Tablet"),c("Smartphone", "Android app"), 
                        c("Desktop", "Android app"),c("Tablet", "Android app") , c("Desktop", "Smartphone"),  c("Desktop", "Tablet"))
ggboxplot(a, x = "Device group", y = "Revenue", color = "Device group", palette = "jco")+ 
  stat_compare_means(comparisons = my_comparisons, label = "p.signif", ref.group = "0.5")+ # Add pairwise comparisons p-value
  stat_compare_means( label.y = 800) +   ggtitle('Annual mean sales in € via different groups of devices') + theme(plot.title = element_text(hjust = 0.5))           # Pairwise comparison against reference
```


From the plot above, we can conclude that daily revenue of all Device types significantly differ from each other and majority of sales happens via Desktop and, four time less, via Android App.

**Recommendations:**

  1. Make Desktop advertising the main target of sdvertising campaigns.
  2. In smartphones more spensings occur via applications than via mobile web.
  3. A few number of users buy products through tablets and advertising via tablets is irrelevant. 

# 3. Apps VS Mobile Web

## 3.1. App vs web


```{r, echo=FALSE}
df %>%  filter(`Event type` == 'Sales') %>% 
  group_by(Date, Environment) %>% summarise(Visits = n()) %>%  
  ggplot(aes(x = Date, y = Visits, fill = `Environment`)) + 
  geom_col() + 
  facet_wrap(~`Environment`) + stat_smooth(
    color = "#FC4E07", fill = "#FC4E07",
    method = "loess")
```

```{r, echo=FALSE}
df %>%  filter(`Event type` == 'Sales') %>% 
  group_by(Environment) %>% summarise(Visits = n()) %>%  
  ggplot(aes(x = Environment, y = Visits, fill = `Environment`, label = Visits)) + 
  geom_col() + geom_text( position = position_dodge(width = 0.9), vjust = -0.5) +
  theme_minimal() + ggtitle('Annual sales in € via different environments') + 
  labs(x = "Environments", fill = "Environments") + 
  theme(plot.title = element_text(hjust = 0.5))

```

We may observe that web purchasing is 4 times higher than application sales and especially in the end of the year. 

## 3.2 Application vs Mobile web

However, the main question is concerned on comparison of applications with mobile web, not web as it is.

```{r, echo=FALSE}
df %>%  filter(`Event type` == 'Sales') %>% 
  filter(`Device group` %in% c('Smartphone', 'Android app')) %>%  
  group_by(`Device group`, Date) %>%   
  summarise(Revenue = sum(`Product Price` * `Product quantity`, na.rm = TRUE)) %>% 
  ggplot(aes(x = Date, y = Revenue, fill = `Device group`)) + 
  geom_col() + 
  facet_wrap(~`Device group`)  + theme(plot.title = element_text(hjust = 0.5), axis.text.x  = element_text(angle = 90), axis.title.x = element_text(hjust = 2), axis.ticks = element_line(size = 1)) 
```

```{r, echo=FALSE}
df %>%  filter(`Event type` == 'Sales') %>% 
  filter(`Device group` %in% c('Smartphone', 'Android app')) %>%
  group_by(`Device group`) %>% 
  summarise(Revenue = sum(`Product Price` * `Product quantity`, na.rm = TRUE)) %>%  
  ggplot(aes(x = `Device group`, y = Revenue, fill = `Device group`, label = Revenue)) + 
  geom_col() + geom_text( position = position_dodge(width = 0.9), vjust = -0.5) +
  theme_minimal() + ggtitle('Annual sales in € via different environments') + 
  labs(x = "Environments", fill = "Environments") + 
  theme(plot.title = element_text(hjust = 0.5))

```

```{r, echo=FALSE}
df2 <- df %>%  filter(`Event type` == 'Sales') %>% 
  filter(`Device group` %in% c('Smartphone', 'Android app')) %>%  
  group_by(`Device group`, Date) %>%   
  mutate(Revenue = sum(`Product Price` * `Product quantity`, na.rm = TRUE)) 

tab_model(lm(data = df2, Revenue ~ `Device group`))
```


The mean daily revenue via app is by 391 times higher than via mobile web. 

```{r, echo=FALSE}
df  %>% filter(`Event type` %in% c('Sales', 'Product')) %>% 
  filter(`Device group` %in% c('Smartphone', 'Android app')) %>%
  group_by(`Device group`, `Event type`) %>% 
  summarise(Usage = n ()) %>% 
  ggplot(aes(x = `Device group`, y = Usage, fill = `Device group`, label = Usage))+ 
  geom_text( position = position_dodge(width = 0.9), vjust = -0.3)  + 
  geom_col() +         
  facet_wrap(~`Event type`)  + 
  theme_minimal() + ggtitle('Annual number of actions via different environments') + 
  theme(plot.title = element_text(hjust = 0.5))
```

The company obtains by 4.9 times higher revenue through android app than through mobile web.


```{r, echo=FALSE}
df %>%  filter(`Event type` == 'Sales') %>% 
  filter(`Device group` %in% c('Smartphone', 'Android app')) %>%
  group_by(`Device group`) %>% 
  mutate(Revenue = `Product Price` * `Product quantity`) %>%  
  ggplot(aes(x = `Device group`, y = Revenue, fill = `Device group`)) + 
  geom_boxplot() + 
  theme_minimal() + ggtitle('Annual sales in € via different environments') + 
  labs(x = "Environments", fill = "Environments") + 
  theme(plot.title = element_text(hjust = 0.5))
```


```{r, echo=FALSE}
df2 <- df %>%  filter(`Event type` == 'Sales') %>% 
  filter(`Device group` %in% c('Smartphone', 'Android app')) %>%  mutate(Revenue = `Product Price` * `Product quantity`) 
tab_model(lm(data = df2, Revenue ~ `Device group` ))
```


A mean revenue through Android app is by 1.05 higher than revenue through mobile web.


**Recommendations:** 

  1. There were a technical problems with Android applications in summer months there is no information about sale and product events. Company should solve this technical problem.
  2. Company will obtain more revenue if will focus on promotion of its application as soon as (although there were no data in summer on application usage) it generates:
    - almost 5 times more total revenue than mobile web;
    - 391 more mean daily revenue than mobile web;
    - 5%  more mean revenue than mobile web.
  3. There was a short peak of mobile web usage in summer when there was no data about application usage. 
  4. However there is no listing and basketing options in application. There is a need in additional investigation about their usefulness for revenue increasing.