{"cells":[{"metadata":{"_uuid":"bbe01d7d084df181304e91f88cd9c7e3abe1eb35"},"cell_type":"markdown","source":"# Data Import and unrolling of json columns\n\n* Code to unroll the json columns credits: ML's early R script.\n\n**Additional metrics:** \n\nI. <u>Session level Conversion Rate per group</u> = (# sessions having transactionRevenue > 0)/(# of sessions in a group)\n\n\nII. <u># sessions</u>\n\n\n---\n\n**Contents:**\n\n1) [Daily mean transaction revenue time series](#optparam1)\n\n2) [Daily conversion rate time series](#optparam2)\n\n3) [Both Metrics w.r.t day of week](#optparam3)\n\n4) [Both metrics w.r.t months](#optparam4)\n\n5) [Channel - Device groups for both metrics and an additional traffic metrics - number of sessions](#optparam5)"},{"metadata":{"_uuid":"d3c4b966c4f09951fd9aa7126ae3cb75a7c98ce8","_execution_state":"idle","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"suppressPackageStartupMessages(library(tidyverse))\nsuppressPackageStartupMessages(library(jsonlite))\nsuppressPackageStartupMessages(library(scales))\nsuppressPackageStartupMessages(library(lubridate))\nsuppressPackageStartupMessages(library(repr))\nsuppressPackageStartupMessages(library(ggrepel))\nsuppressPackageStartupMessages(library(data.table))\n\ndtrain <- read_csv(\"../input/train.csv\")\n#dtest <- read_csv(\"C:/Kaggle/googlemerchandise/data/test.csv\")\n\n\ntr_device <- paste(\"[\", paste(dtrain$device, collapse = \",\"), \"]\") %>% fromJSON(flatten = T)\ntr_geoNetwork <- paste(\"[\", paste(dtrain$geoNetwork, collapse = \",\"), \"]\") %>% fromJSON(flatten = T)\ntr_totals <- paste(\"[\", paste(dtrain$totals, collapse = \",\"), \"]\") %>% fromJSON(flatten = T)\ntr_trafficSource <- paste(\"[\", paste(dtrain$trafficSource, collapse = \",\"), \"]\") %>% fromJSON(flatten = T)\n\ndtrain <- cbind(dtrain, tr_device, tr_geoNetwork, tr_totals, tr_trafficSource) %>%\n  as.data.table()\n\n# drop the old json columns\ndtrain[, c('device', 'geoNetwork', 'totals', 'trafficSource') := NULL]\n\n#----------------------------------------\n# dropping useless columns\n\ncolsToDel = c('socialEngagementType', 'browserSize','browserVersion',\n              'flashVersion', 'language', 'mobileDeviceInfo','mobileDeviceBranding',\n              'mobileDeviceMarketingName', 'mobileInputSelector', 'operatingSystemVersion',\n              'screenColors','screenResolution', 'cityId', 'campaignCode',\n              'referralPath', 'latitude', 'longitude', 'metro', 'networkLocation',\n              'region', 'subContinent', 'visits', 'adwordsClickInfo.criteriaParameters',\n              'adwordsClickInfo.gclId')\n\n\ndtrain = as.data.frame(dtrain)\ndtrain = dtrain[, -match(colsToDel, names(dtrain))]\n\n#---- Visualizing the TransactionRevenue w.r.t various dimensions ----#\n\ndtrain$date <- ymd(dtrain$date)\ndtrain$transactionRevenue <- as.numeric(dtrain$transactionRevenue)\ndtrain$transactionRevenue[is.na(dtrain$transactionRevenue)] <- 0","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"6154be2e219875c3fcb69f4a2e89f4c1a1107207"},"cell_type":"code","source":"# --- MULTIPLOT HELPER FUNCTION DEFINITION --- #\n\nmultiplot <- function(..., plotlist=NULL, file, cols=1, layout=NULL) {\n  require(grid)\n  \n  # Make a list from the ... arguments and plotlist\n  plots <- c(list(...), plotlist)\n  \n  numPlots = length(plots)\n  \n  # If layout is NULL, then use 'cols' to determine layout\n  if (is.null(layout)) {\n    # Make the panel\n    # ncol: Number of columns of plots\n    # nrow: Number of rows needed, calculated from # of cols\n    layout <- matrix(seq(1, cols * ceiling(numPlots/cols)),\n                     ncol = cols, nrow = ceiling(numPlots/cols))\n  }\n  \n  if (numPlots==1) {\n    print(plots[[1]])\n    \n  } else {\n    # Set up the page\n    grid.newpage()\n    pushViewport(viewport(layout = grid.layout(nrow(layout), ncol(layout))))\n    \n    # Make each plot, in the correct location\n    for (i in 1:numPlots) {\n      # Get the i,j matrix positions of the regions that contain this subplot\n      matchidx <- as.data.frame(which(layout == i, arr.ind = TRUE))\n      \n      print(plots[[i]], vp = viewport(layout.pos.row = matchidx$row,\n                                      layout.pos.col = matchidx$col))\n    }\n  }\n}","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d461aed20f59ecdba22a147dc64304718ed7d6b7"},"cell_type":"markdown","source":"<a id=\"optparam1\"></a>\n\n## 1. Mean Transaction Revenue Time Series (Daily)"},{"metadata":{"trusted":true,"_uuid":"d89bef2b3df90bbd69fcae53628299a37595401c","_kg_hide-input":true},"cell_type":"code","source":"p1 <- dtrain %>%\n  mutate(transactionRevenue = transactionRevenue/(10^6)) %>%\n  group_by(date) %>%\n  summarise(meanTransactionRevenue = mean(transactionRevenue)) %>%\n  ggplot(aes(date,meanTransactionRevenue)) +\n  geom_line(col = \"red\", size=0.7) +\n  labs(y = \"Mean TransactionRevenue\", x = \"Date\", title = \"Mean Transaction Revenue Time Series\") +\n  theme_bw()\n\n\n\n# 2. Session level conversion rate over time\n\np2 <- dtrain %>% group_by(date) %>% \n  summarise(convertedSessionCount = sum(transactionRevenue>0), \n            numsessions = n()) %>%\n  mutate(sessLevelCR = convertedSessionCount/numsessions) %>% \n  select(date, sessLevelCR) %>%\n  ggplot(aes(date, sessLevelCR)) +\n  geom_line(col = \"blue\", size=0.7) +\n  labs(y = \"Conversion Rate\", x = \"Date\", \n        title = \"Conversion Rate Time Series\") +\n  theme_bw()\n\n\n# 3. Mean Transaction Revenue per day of week\n\np3 <- dtrain %>%\n  mutate(transactionRevenue = transactionRevenue/(10^6)) %>%\n  mutate(wday = lubridate::wday(date, label=TRUE)) %>%\n  group_by(wday) %>%\n  summarise(meanTransactionRevenue = mean(transactionRevenue)) %>%\n  ggplot(aes(wday, meanTransactionRevenue, fill = wday)) +\n  geom_col() +\n  theme(legend.position = \"none\", axis.text.x  = element_text(angle=45, hjust=1, vjust=0.9)) +\n  labs(x = \"Day of the week\", y = \"Mean Transaction Revenue\") +\n  theme_bw()\n\n\n# 4. Session level CR per day of week\n\np4 <- dtrain %>% \n  mutate(wday = lubridate::wday(date, label=TRUE)) %>%\n  group_by(wday) %>%\n  summarise(convertedSessionCount = sum(transactionRevenue>0), \n            numsessions = n()) %>%\n  mutate(sessLevelCR = convertedSessionCount/numsessions) %>% \n  select(wday, sessLevelCR) %>%\n  ggplot(aes(wday, sessLevelCR, fill = wday)) +\n  geom_col() +\n  theme(legend.position = \"none\", axis.text.x  = element_text(angle=45, hjust=1, vjust=0.9)) +\n  labs(x = \"Day of the week\", y = \"Conversion Rate\") +\n  theme_bw()\n\n\n\n# 5. Mean Transaction Revenue per month\n\np5 <- dtrain %>%\n  mutate(transactionRevenue = transactionRevenue/(10^6)) %>%\n  mutate(month = lubridate::month(date, label=TRUE)) %>%\n  group_by(month) %>%\n  summarise(meanTransactionRevenue = mean(transactionRevenue)) %>%\n  ggplot(aes(month, meanTransactionRevenue, fill = month)) +\n  geom_col() +\n  theme(legend.position = \"none\", axis.text.x  = element_text(angle=45, hjust=1, vjust=0.9)) +\n  labs(x = \"Month\", y = \"Mean Transaction Revenue\") +\n  theme_bw()\n\n\n# 6. Session level CR per day of week\n\np6 <- dtrain %>% \n  mutate(month = lubridate::month(date, label=TRUE)) %>%\n  group_by(month) %>%\n  summarise(convertedSessionCount = sum(transactionRevenue>0), \n            numsessions = n()) %>%\n  mutate(sessLevelCR = convertedSessionCount/numsessions) %>% \n  select(month, sessLevelCR) %>%\n  ggplot(aes(month, sessLevelCR, fill = month)) +\n  geom_col() +\n  theme(legend.position = \"none\", axis.text.x  = element_text(angle=45, hjust=1, vjust=0.9)) +\n  labs(x = \"Month\", y = \"Conversion Rate\") +\n  theme_bw()\n\n\nlibrary(repr)\noptions(repr.plot.width=12, repr.plot.height=4)\n\np1","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1f82cf2b571935286801670599a3066bc8cf1d54"},"cell_type":"markdown","source":"Observations:\n\n* Sign of existence of a weekly seasonality.\n* There is no trend. Mean transaction revenue is pretty constant over time.\n* Lets see if there is seasonality at weekly level by using ets() function\n* If ets() function selects a seasonal component, then we have a strong evidence that there is some kind of a weekly pattern"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"113a8d833ccc9183aee5ae957824802369ac12e0"},"cell_type":"code","source":"library(forecast)\ntrain_ts <- ts(\n      dtrain %>%\n      mutate(transactionRevenue = transactionRevenue/(10^6)) %>%\n      group_by(date) %>%\n      summarise(meanTransactionRevenue = mean(transactionRevenue)) %>%\n      select(meanTransactionRevenue) %>% unlist() %>% as.numeric(),\n    \n      start = dtrain %>% arrange(date) %>% select(date) %>% head(1) %>% unlist(),\n      end = dtrain %>% arrange(date) %>% select(date) %>% tail(1) %>% unlist(),\n      frequency = 7)\n\nets(train_ts)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"010434f7509032e36603c96cee0e8a07783c781a"},"cell_type":"markdown","source":"* ets() selected an additive errors components and additive seasonal component.\n* No trend component selected\n\n<a id=\"optparam2\"></a>\n\n\n## 2. Time series of daily session level conversion rate"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"008c40b524a41c57758c167ea38b8e25b01ceec5"},"cell_type":"code","source":"options(repr.plot.width=12, repr.plot.height=4)\np2","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c83a69b45bb8c3c8a33ff336f5f10919722bcbd6"},"cell_type":"markdown","source":"Observations:\n\n* December 2017 sees increase in the daily conversion rate\n* Conversion rates also have a seasonal pattern possibly at a weekly level\n* Apart from seasonal component, it is still a fairly stationary time series\n* Lets confirm that using ets()"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"9ed934eb15e04f5033b8b2c2fcec3492b3051bfb"},"cell_type":"code","source":"train_ts_cr <- ts(\n      dtrain %>%\n      group_by(date) %>% \n      summarise(convertedSessionCount = sum(transactionRevenue>0), \n            numsessions = n()) %>%\n      mutate(sessLevelCR = convertedSessionCount/numsessions) %>% \n      select(sessLevelCR) %>% unlist() %>% as.numeric(),\n      \n      start = dtrain %>% arrange(date) %>% select(date) %>% head(1) %>% unlist(),\n      end = dtrain %>% arrange(date) %>% select(date) %>% tail(1) %>% unlist(),\n      frequency = 7)\n\nets(train_ts_cr)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"417c60909b65a9c624e6277a6e1662b63d230211"},"cell_type":"markdown","source":"* Same combination selected, so seasonal pattern exists w.r.t conversion too at a weekly level\n\n<a id=\"optparam3\"></a>\n\n## 3. Both metrics w.r.t day of the week"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"efbcd5d86a8995842971a5d4a75856fd2edfad02"},"cell_type":"code","source":"options(repr.plot.width=12, repr.plot.height=6)\nmultiplot(p3, p4, cols = 1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8b7ae72faaeff5a567a88649ca0a839519c67436"},"cell_type":"markdown","source":"Observations:\n\n* Both conversion rate and mean transaction revenue reduces over the Saturday and Sunday\n\n<a id=\"optparam4\"></a>\n\n\n## 4. Both metrics w.r.t months"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"484f7be78bb22f2ce22a032f18207c57821ee4f3"},"cell_type":"code","source":"options(repr.plot.width=12, repr.plot.height=8)\nmultiplot(p5, p6, cols = 1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f83c04a1d013d9984bb63ecf4bfc5cbfb6c6a28f"},"cell_type":"markdown","source":"Observations:\n\n* December has high mean TR as well as CR.\n* January to November forms a kind of middle 68% of some normal curve with a little low mean TR and CRs at the tail and a slightly increasing trend towards the middle around April and May\n\n<a id=\"optparam5\"></a>\n\n\n## 5. <u>*Marketing Channels*</u>  and <u>*Device*</u> w.r.t both metrics and a traffic metric <u># sessions</u> as well"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"be3980b131eae316a76329c974328911f6e22ae0"},"cell_type":"code","source":"options(repr.plot.width=12, repr.plot.height=8)\np7 <- dtrain %>%\n  mutate(transactionRevenue = transactionRevenue/(10^6)) %>%\n  group_by(channelGrouping, deviceCategory) %>%\n  summarise(meanTransactionRevenue = mean(transactionRevenue)) %>%\n  ggplot(aes(x = channelGrouping, y = meanTransactionRevenue, fill = deviceCategory)) + \n  geom_bar(colour = \"black\", \n           width = 0.8, position = \"dodge\", stat=\"identity\") +\n  labs(title = \"Mean Transaction Revenue per Device per Channel\",\n       x=\"Marketing Channel\", y=\"Mean Transaction Revenue\") +\n  theme_bw() +\n  theme(axis.text.x  = element_text(angle=45, hjust=1, vjust=0.9))\n\n\n# Number of sessions per device per channel\n\np8 <- dtrain %>%\n  group_by(channelGrouping, deviceCategory) %>%\n  summarise(numSessions = n()) %>%\n  ggplot(aes(x = channelGrouping, y = numSessions, fill = deviceCategory)) + \n  geom_bar(colour = \"black\", \n           width = 0.8, position = \"dodge\", stat=\"identity\") +\n  labs(title = \"Number of Sessions per Device per Channel\",\n       x=\"Marketing Channel\", y=\"Sessions\") +\n  theme_bw() +\n  theme(axis.text.x  = element_text(angle=45, hjust=1, vjust=0.9))\n  \n\n# Conversion Rate per device per channel\n\np9 <- dtrain %>%\n  group_by(channelGrouping, deviceCategory) %>%\n  summarise(convertedSessionCount = sum(transactionRevenue>0), \n            numsessions = n()) %>%\n  mutate(sessLevelCR = convertedSessionCount/numsessions) %>% \n  ggplot(aes(x = channelGrouping, y = sessLevelCR, fill = deviceCategory)) + \n  geom_bar(colour = \"black\", \n           width = 0.8, position = \"dodge\", stat=\"identity\") +\n  labs(title = \"Conversion Rate per Device per Channel\",\n       x=\"Marketing Channel\", y=\"Conversion Rate\") +\n  theme_bw()\n\n\nlayout_3 = matrix(c(1,1,2,2,3,3,3,3),2,4,byrow = TRUE)\nmultiplot(p7, p8, p9, layout = layout_3)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"61da42c2c71dc8e3e7bcceaf15f9aa800b317399"},"cell_type":"markdown","source":"Observations:\n\n* High amount of traffic from Social + Desktop but very low conversion rate.\n* Decent Direct + Mobile traffic but less conversion rate.\n* Referral CR for Desktop is excellent > 4% but very poor for Mobile ~ 0.2%\n\n## To be continued ..."}],"metadata":{"kernelspec":{"display_name":"R","language":"R","name":"ir"},"language_info":{"mimetype":"text/x-r-source","name":"R","pygments_lexer":"r","version":"3.4.2","file_extension":".r","codemirror_mode":"r"}},"nbformat":4,"nbformat_minor":1}