API tools faq
paste
Guest User

Untitled

a guest
Dec 13th, 2017
114
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 45.49 KB | None | 0 0
raw download clone embed print report
  1. #Version 2
  2. library(devtools)
  3. #install_github("jcheng5/bubbles",force = TRUE)
  4. library(bubbles)
  5. library(lubridate)
  6. library(tidyverse)
  7. library(fasttime)
  8. library(shiny)
  9. library(shinydashboard)
  10. library(RPostgres)
  11. library(DBI)
  12. library(scales)
  13. library(dygraphs)
  14. library(xts)
  15. guaranteesquery <- "select vehicles.id, vehicles.licence_plate, vehicles.guarantee_start_date, vehicles.guarantee_end_date,vehicles.guarantee_amount from vehicles where vehicles.state = 'enabled'"
  16. bookingsquery <- "select bookings.id as booking_id,bookings.driver_id, driver_plans.start_time, case when driver_plans.end_time = 'infinity' then NULL else driver_plans.end_time end, driver_plans.created_at, driver_plans.booking_amount_before_discount_pence as b, driver_plans.insurance_pence as i, driver_plans.vehicle_discount_percent as d, driver_plans.owner_price_before_discount_pence as o from driver_plans left join bookings on driver_plans.booking_id = bookings.id where driver_plans.state = 'booked' and bookings.fleet_organisation_id is null"
  17. con <- dbConnect(RPostgres::Postgres(),dbname = 'd6fpj3q9fd7ir0', host = 'ec2-34-201-185-191.compute-1.amazonaws.com', port = 5432, user = 'uas5mog87kfvp1', password = 'phajrgnbciabq1l0kqv45kohq')
  18. bookings <- dbGetQuery(con, bookingsquery) %>% as.tibble()
  19. bookings$b <- bookings$b/100
  20. bookings$i <- bookings$i/100
  21. bookings$o <- bookings$o/100
  22. bookings$d <- (100-bookings$d)/100
  23. vehicles_on_hire <- dbGetQuery(con,
  24. "select vehicles.id as vehicle_id, vehicle_make, year, sub_type, driver_plans.booking_amount_before_discount_pence/100 as list_price,
  25. case when companies.name is null then concat(partners.first_name,' ',partners.last_name,'*') else companies.name end as Supplier,
  26. bookings.owner_id,bookings.id as booking_id, bookings.start_time, bookings.end_time
  27.  
  28. from vehicles
  29. left join bookings on bookings.vehicle_id = vehicles.id
  30. left join driver_plans on driver_plans.booking_id = bookings.id
  31. left join companies on companies.partner_id = vehicles.owner_id
  32. left join partners on bookings.owner_id = partners.id
  33.  
  34. where vehicles.state = 'enabled' and
  35. (driver_plans.end_time>=now() or driver_plans.end_time = 'infinity') and bookings.state = 'booked'
  36. and bookings.fleet_organisation_id is null") %>% as.tibble()
  37. vehicles_available <- dbGetQuery(con,
  38. "select vehicles.owner_id,
  39. case when companies.name is null then concat(partners.first_name,' ',partners.last_name,'*') else companies.name end as Supplier,
  40. vehicles.id as vehicle_id,
  41. vehicles.vehicle_make, vehicles.vehicle_model, vehicles.year, vehicles.licence_plate from vehicles
  42. left join companies on companies.partner_id = vehicles.owner_id
  43. left join partners on partners.id = vehicles.owner_id where vehicles.state = 'enabled'") %>% as.tibble()
  44. vehicles_enabled <- full_join(vehicles_available,vehicles_on_hire,by = c("vehicle_id","supplier","year","vehicle_make"))
  45. cohorts <- bookings %>% group_by(driver_id) %>% summarise(time_of_conception = min(created_at),cohort = min(start_time) %>% format("%Y %m"), cohort_year = min(start_time) %>% format("%Y"), cohort_start = min(start_time) %>% format("%Y-%m-01") %>% as.Date("%Y-%m-%d"))
  46. chris <- function(date1,date2) {
  47. #date1 <- Sys.Date()+1
  48. #date2 <- Sys.Date() %>% format("%Y-%m-01") %>% as.Date()
  49. bookings$start_time <- as.Date(bookings$start_time)
  50. bookings$end_time <- as.Date(bookings$end_time)
  51. bookings$d <- (100-bookings$d)/100
  52. bookings <- bookings %>% mutate(GMV = ((b-i)*d+i),
  53. GMV_pre_insurance = (b-i)*d,
  54. Commission = (b-i-o)*d,
  55. Payout = o*d, Insurance = i)
  56. bookings <- bookings %>% filter((start_time<date2 & end_time>=date1) | (start_time>=date2 & end_time<date1) | (start_time<date2 & end_time<date1 & end_time>=date2) | (start_time>=date2 & start_time<date1 & end_time>=date1) |(is.na(end_time) & start_time<date1) ) %>%
  57. mutate(duration_in_given_interval = ifelse(!is.na(end_time),
  58. ifelse(start_time<date2 & end_time>=date1,(date1-date2) %>% as.numeric(),
  59. ifelse(start_time<date1 & start_time>=date2 & end_time>=date2 & end_time<date1,end_time-start_time,
  60. ifelse(end_time>=date1,date1-start_time-1,end_time-date2+1))),
  61. ifelse(start_time<date2,date1-date2,date1-start_time-1)),
  62. dp_gmv = duration_in_given_interval*GMV/7, dp_gmv_pre_insurance = duration_in_given_interval*GMV_pre_insurance/7, dp_booking_commission = duration_in_given_interval*Commission/7,dp_insurance = duration_in_given_interval*Insurance/7,dp_payout = duration_in_given_interval*Payout/7)
  63.  
  64. KPIS <- bookings %>% summarise(Commission = sum(dp_booking_commission),Insurance = sum(dp_insurance),Payout = sum(dp_payout),GMV = sum(dp_gmv),date = date2 %>% format("%b %y"), date2 = date2 %>% as.Date())
  65. print(KPIS)}
  66. x1 <- seq(as.Date("2016-05-01"), as.Date("2017-11-01"), "months")
  67. x2 <- seq(as.Date("2016-06-01"), as.Date("2017-12-01"), "months")
  68. x <- map2(x2,x1,chris) %>% bind_rows %>% select(GMV,Commission,Payout,date,date2) %>% gather(key = "Metric",Amount,-c(date,date2))
  69. ts1 <- map2(x2,x1,chris) %>% bind_rows %>% select(GMV,Commission,Payout,date,date2) %>% gather(key = "Metric",Amount,-c(date,date2))
  70. current_bookings <- bookings %>% filter(start_time<=Sys.time(),is.na(end_time) | end_time>=Sys.time()) %>% mutate(duration_to_date = ifelse(is.na(end_time),difftime(Sys.Date(),start_time,units = "days"),difftime(end_time,start_time,units = "days")), type = ifelse(is.na(end_time),"Rolling","Fixed")) %>%
  71. group_by(type) %>% summarise(count = n(), avg_duration = mean(duration_to_date), posy = cumsum(count)-(count/2)) %>% mutate(posx = ifelse(type=="Rolling",0.75,0.25))
  72.  
  73. #Monthly KPIS per second
  74. insert_date_monthly2 <- Sys.Date()+1
  75. prev_date_monthly2 <- Sys.Date() %>% format("%Y-%m-01") %>% as.Date()
  76. bookings_monthly2 <- bookings
  77. bookings_monthly2$d <- (100-bookings_monthly2$d)/100
  78. bookings_monthly2 <- bookings_monthly2 %>% mutate(GMV = ((b-i)*d+i),
  79. GMV_pre_insurance = (b-i)*d,
  80. Commission = (b-i-o)*d,
  81. Payout = o*d, Insurance = i, start_time2 = start_time)
  82.  
  83. bookings_monthly2$start_time <- fastPOSIXct(bookings_monthly2$start_time) %>% format("%Y-%m-%d") %>% as.Date()
  84. bookings_monthly2$end_time <- fastPOSIXct(bookings_monthly2$end_time) %>% format("%Y-%m-%d") %>% as.Date()
  85.  
  86. #driver_cohorts <- bookings_monthly2 %>% group_by(driver_id) %>% summarise(cohort = min(start_time) %>% format("%Y %m"), cohort_year = min(start_time) %>% format("%Y"), cohort_start = min(start_time) %>% format("%Y-%m-01") %>% as.Date("%Y-%m-%d"))
  87.  
  88. bookings_monthly2 <- bookings_monthly2 %>% filter((start_time<prev_date_monthly2 & end_time>=insert_date_monthly2) | (start_time>=prev_date_monthly2 & end_time<insert_date_monthly2) | (start_time<prev_date_monthly2 & end_time<insert_date_monthly2 & end_time>=prev_date_monthly2) | (start_time>=prev_date_monthly2 & start_time<insert_date_monthly2 & end_time>=insert_date_monthly2) |(is.na(end_time) & start_time<insert_date_monthly2) ) %>%
  89. mutate(duration_in_given_month = ifelse(!is.na(end_time),
  90. ifelse(start_time<prev_date_monthly2 & end_time>=insert_date_monthly2,(insert_date_monthly2-prev_date_monthly2) %>% as.numeric(), ifelse(start_time<insert_date_monthly2 & start_time>=prev_date_monthly2 & end_time>=prev_date_monthly2 & end_time<insert_date_monthly2,end_time-start_time,ifelse(end_time>=insert_date_monthly2,insert_date_monthly2-start_time-1,end_time-prev_date_monthly2+1))), ifelse(start_time<prev_date_monthly2,insert_date_monthly2-prev_date_monthly2,insert_date_monthly2-start_time-1)),
  91. dp_gmv = duration_in_given_month*GMV/7, dp_gmv_pre_insurance = duration_in_given_month*GMV_pre_insurance/7, dp_booking_commission = duration_in_given_month*Commission/7,dp_insurance = duration_in_given_month*Insurance/7,dp_payout = duration_in_given_month*Payout/7)
  92.  
  93. x <- bookings_monthly2 %>% select(start_time2,end_time,GMV,GMV_pre_insurance,Commission,Payout,Insurance,duration_in_given_month)
  94.  
  95. #Monthly KPIS
  96. {insert_date_monthly <- Sys.Date()+1
  97. prev_date_monthly <- Sys.Date() %>% format("%Y-%m-01") %>% as.Date()
  98. bookings_monthly <- bookings
  99. bookings_monthly$d <- (100-bookings_monthly$d)/100
  100. bookings_monthly <- bookings_monthly %>% mutate(GMV = ((b-i)*d+i),
  101. GMV_pre_insurance = (b-i)*d,
  102. Commission = (b-i-o)*d,
  103. Payout = o*d, Insurance = i)
  104. bookings_monthly$start_time <- fastPOSIXct(bookings_monthly$start_time) %>% format("%Y-%m-%d") %>% as.Date()
  105. bookings_monthly$end_time <- fastPOSIXct(bookings_monthly$end_time) %>% format("%Y-%m-%d") %>% as.Date()
  106.  
  107. driver_cohorts <- bookings_monthly %>% group_by(driver_id) %>% summarise(cohort = min(start_time) %>% format("%Y %m"), cohort_year = min(start_time) %>% format("%Y"), cohort_start = min(start_time) %>% format("%Y-%m-01") %>% as.Date("%Y-%m-%d"))
  108.  
  109. bookings_monthly <- bookings_monthly %>% filter((start_time<prev_date_monthly & end_time>=insert_date_monthly) | (start_time>=prev_date_monthly & end_time<insert_date_monthly) | (start_time<prev_date_monthly & end_time<insert_date_monthly & end_time>=prev_date_monthly) | (start_time>=prev_date_monthly & start_time<insert_date_monthly & end_time>=insert_date_monthly) |(is.na(end_time) & start_time<insert_date_monthly) ) %>%
  110. mutate(duration_of_driver_plan = ifelse(!is.na(end_time),(end_time-start_time),(insert_date_monthly-start_time)),
  111. duration_to_date = ifelse((start_time>insert_date_monthly) | is.na(end_time), insert_date_monthly-start_time, end_time-start_time),
  112. duration_in_given_month = ifelse(!is.na(end_time),
  113. ifelse(start_time<prev_date_monthly & end_time>=insert_date_monthly,(insert_date_monthly-prev_date_monthly) %>% as.numeric(), ifelse(start_time<insert_date_monthly & start_time>=prev_date_monthly & end_time>=prev_date_monthly & end_time<insert_date_monthly,end_time-start_time,ifelse(end_time>=insert_date_monthly,insert_date_monthly-start_time-1,end_time-prev_date_monthly+1))), ifelse(start_time<prev_date_monthly,insert_date_monthly-prev_date_monthly,insert_date_monthly-start_time-1)),
  114. duration_projection = ifelse(!is.na(end_time),duration_of_driver_plan,ifelse(duration_of_driver_plan<90,90,duration_of_driver_plan)),
  115. case = ifelse(!is.na(end_time),ifelse(start_time<prev_date_monthly & end_time>=insert_date_monthly,1,ifelse(start_time<insert_date_monthly & start_time>=prev_date_monthly & end_time>=prev_date_monthly & end_time<insert_date_monthly,2,ifelse(end_time>=insert_date_monthly,3,4))),ifelse(start_time<prev_date_monthly,5,6)),
  116. dp_gmv = duration_in_given_month*GMV/7, dp_gmv_pre_insurance = duration_in_given_month*GMV_pre_insurance/7, dp_booking_commission = duration_in_given_month*Commission/7,dp_insurance = duration_in_given_month*Insurance/7,dp_payout = duration_in_given_month*Payout/7)
  117.  
  118. KPIS_monthly <- bookings_monthly %>% summarise(GMV_less_insurance = sum(dp_gmv_pre_insurance),booking_commission = sum(dp_booking_commission),Insurance = sum(dp_insurance),Payout = sum(dp_payout),GMV = sum(dp_gmv),date = prev_date_monthly %>% format("%b %y"))}
  119.  
  120. #Weekly KPIS
  121. {insert_date_weekly <- Sys.Date()+1
  122. prev_date_weekly <- (Sys.Date()-days((Sys.Date() %>% format("%u")))) %>% format("%Y-%m-%d") %>% as.Date()
  123. bookings_weekly <- bookings
  124. bookings_weekly$d <- (100-bookings_weekly$d)/100
  125. bookings_weekly <- bookings_weekly %>% mutate(GMV = ((b-i)*d+i),
  126. GMV_pre_insurance = (b-i)*d,
  127. Commission = (b-i-o)*d,
  128. Payout = o*d, Insurance = i)
  129. bookings_weekly$start_time <- fastPOSIXct(bookings_weekly$start_time) %>% format("%Y-%m-%d") %>% as.Date()
  130. bookings_weekly$end_time <- fastPOSIXct(bookings_weekly$end_time) %>% format("%Y-%m-%d") %>% as.Date()
  131.  
  132. driver_cohorts <- bookings_weekly %>% group_by(driver_id) %>% summarise(cohort = min(start_time) %>% format("%Y %m"), cohort_year = min(start_time) %>% format("%Y"), cohort_start = min(start_time) %>% format("%Y-%m-01") %>% as.Date("%Y-%m-%d"))
  133.  
  134. bookings_weekly <- bookings_weekly %>% filter((start_time<prev_date_weekly & end_time>=insert_date_weekly) | (start_time>=prev_date_weekly & end_time<insert_date_weekly) | (start_time<prev_date_weekly & end_time<insert_date_weekly & end_time>=prev_date_weekly) | (start_time>=prev_date_weekly & start_time<insert_date_weekly & end_time>=insert_date_weekly) |(is.na(end_time) & start_time<insert_date_weekly) ) %>%
  135. mutate(duration_of_driver_plan = ifelse(!is.na(end_time),(end_time-start_time),(insert_date_weekly-start_time)),
  136. duration_to_date = ifelse((start_time>insert_date_weekly) | is.na(end_time), insert_date_weekly-start_time, end_time-start_time),
  137. duration_in_given_month = ifelse(!is.na(end_time), ifelse(start_time<prev_date_weekly & end_time>=insert_date_weekly,(insert_date_weekly-prev_date_weekly) %>% as.numeric(),ifelse(start_time<insert_date_weekly & start_time>=prev_date_weekly & end_time>=prev_date_weekly & end_time<insert_date_weekly,end_time-start_time, ifelse(end_time>=insert_date_weekly,insert_date_weekly-start_time-1,end_time-prev_date_weekly+1))), ifelse(start_time<prev_date_weekly,insert_date_weekly-prev_date_weekly,insert_date_weekly-start_time-1)),
  138. duration_projection = ifelse(!is.na(end_time),duration_of_driver_plan,ifelse(duration_of_driver_plan<90,90,duration_of_driver_plan)),
  139. case = ifelse(!is.na(end_time),ifelse(start_time<prev_date_weekly & end_time>=insert_date_weekly,1,ifelse(start_time<insert_date_weekly & start_time>=prev_date_weekly & end_time>=prev_date_weekly & end_time<insert_date_weekly,2,ifelse(end_time>=insert_date_weekly,3,4))),ifelse(start_time<prev_date_weekly,5,6)),
  140. dp_gmv = duration_in_given_month*GMV/7, dp_gmv_pre_insurance = duration_in_given_month*GMV_pre_insurance/7, dp_booking_commission = duration_in_given_month*Commission/7,dp_insurance = duration_in_given_month*Insurance/7,dp_payout = duration_in_given_month*Payout/7)
  141.  
  142. KPIS_weekly <- bookings_weekly %>% summarise(GMV_less_insurance = sum(dp_gmv_pre_insurance),booking_commission = sum(dp_booking_commission),Insurance = sum(dp_insurance),Payout = sum(dp_payout),GMV = sum(dp_gmv),date = prev_date_weekly %>% format("%b %y"))}
  143.  
  144.  
  145. #Live weekly KPIS
  146. insert_date_weekly <- Sys.Date()+1
  147. prev_date_weekly <- (Sys.Date()-days((Sys.Date() %>% format("%u")))) %>% format("%Y-%m-%d") %>% as.Date()
  148. bookings_weekly <- bookings
  149. bookings_weekly$d <- (100-bookings_weekly$d)/100
  150. bookings_weekly <- bookings_weekly %>% mutate(GMV = ((b-i)*d+i),
  151. GMV_pre_insurance = (b-i)*d,
  152. Commission = (b-i-o)*d,
  153. Payout = o*d, Insurance = i, start_time2 = start_time)
  154. bookings_weekly$start_time <- fastPOSIXct(bookings_weekly$start_time) %>% format("%Y-%m-%d") %>% as.Date()
  155. bookings_weekly$end_time <- fastPOSIXct(bookings_weekly$end_time) %>% format("%Y-%m-%d") %>% as.Date()
  156.  
  157. driver_cohorts <- bookings_weekly %>% group_by(driver_id) %>% summarise(cohort = min(start_time) %>% format("%Y %m"), cohort_year = min(start_time) %>% format("%Y"), cohort_start = min(start_time) %>% format("%Y-%m-01") %>% as.Date("%Y-%m-%d"))
  158.  
  159. bookings_weekly <- bookings_weekly %>% filter((start_time<prev_date_weekly & end_time>=insert_date_weekly) | (start_time>=prev_date_weekly & end_time<insert_date_weekly) | (start_time<prev_date_weekly & end_time<insert_date_weekly & end_time>=prev_date_weekly) | (start_time>=prev_date_weekly & start_time<insert_date_weekly & end_time>=insert_date_weekly) |(is.na(end_time) & start_time<insert_date_weekly) ) %>%
  160. mutate(duration_of_driver_plan = ifelse(!is.na(end_time),(end_time-start_time),(insert_date_weekly-start_time)),
  161. duration_to_date = ifelse((start_time>insert_date_weekly) | is.na(end_time), insert_date_weekly-start_time, end_time-start_time),
  162. duration_in_given_month = ifelse(!is.na(end_time), ifelse(start_time<prev_date_weekly & end_time>=insert_date_weekly,(insert_date_weekly-prev_date_weekly) %>% as.numeric(),ifelse(start_time<insert_date_weekly & start_time>=prev_date_weekly & end_time>=prev_date_weekly & end_time<insert_date_weekly,end_time-start_time, ifelse(end_time>=insert_date_weekly,insert_date_weekly-start_time-1,end_time-prev_date_weekly+1))), ifelse(start_time<prev_date_weekly,insert_date_weekly-prev_date_weekly,insert_date_weekly-start_time-1)),
  163. duration_projection = ifelse(!is.na(end_time),duration_of_driver_plan,ifelse(duration_of_driver_plan<90,90,duration_of_driver_plan)),
  164. case = ifelse(!is.na(end_time),ifelse(start_time<prev_date_weekly & end_time>=insert_date_weekly,1,ifelse(start_time<insert_date_weekly & start_time>=prev_date_weekly & end_time>=prev_date_weekly & end_time<insert_date_weekly,2,ifelse(end_time>=insert_date_weekly,3,4))),ifelse(start_time<prev_date_weekly,5,6)),
  165. dp_gmv = duration_in_given_month*GMV/7, dp_gmv_pre_insurance = duration_in_given_month*GMV_pre_insurance/7, dp_booking_commission = duration_in_given_month*Commission/7,dp_insurance = duration_in_given_month*Insurance/7,dp_payout = duration_in_given_month*Payout/7)
  166.  
  167. KPIS_weekly <- bookings_weekly %>% summarise(GMV_less_insurance = sum(dp_gmv_pre_insurance),booking_commission = sum(dp_booking_commission),Insurance = sum(dp_insurance),Payout = sum(dp_payout),GMV = sum(dp_gmv),date = prev_date_weekly %>% format("%b %y"))
  168.  
  169. x2 <- bookings_monthly2 %>% select(start_time2,end_time,GMV,GMV_pre_insurance,Commission,Payout,Insurance,duration_in_given_month)
  170.  
  171. agent <- dbGetQuery(con, "
  172. select bookings.id as booking_id,bookings.booked_by_id,partners.first_name,partners.last_name,bookings.driver_id, driver_plans.start_time,
  173. case when driver_plans.end_time = 'infinity' then NULL else driver_plans.end_time end, driver_plans.created_at from driver_plans
  174. left join bookings on driver_plans.booking_id = bookings.id
  175. left join partners on bookings.booked_by_id = partners.id
  176. where driver_plans.state = 'booked' and bookings.fleet_organisation_id is null") %>% as.tibble() %>% filter(as.Date(created_at)==Sys.Date()) %>% arrange(desc(created_at)) %>% mutate('Booked by' = ifelse(is.na(first_name),"?",paste(first_name,last_name)))
  177.  
  178.  
  179. #install.packages("remotes")
  180. #remotes::install_github("rstats-db/RPostgres")
  181.  
  182.  
  183. ui <- dashboardPage(skin = "green",
  184. dashboardHeader(title = "Drover Dashboard"),
  185.  
  186. #Dashboard Sidebar
  187. dashboardSidebar(
  188. sidebarMenu(
  189. menuItem("Dashboard", tabName = "dashboard", icon = icon("dashboard")),
  190. menuItem("Demand", tabName = "demand", icon = icon("address-card-o")),
  191. menuItem("Supply", tabName = "supply"),
  192. #menuItem("New Bookings Today", tabName = "newbookingstoday"),
  193. menuItem("Cohort Analysis", tabName = "cohorts"),
  194. menuItem("Vehicles Analysis", tabName = "vehicles"))
  195.  
  196. ),
  197.  
  198. dashboardBody
  199. (
  200. tabItems(
  201. #Dashboard
  202. tabItem(tabName = "dashboard",
  203. fluidRow(box(h1("Current time:",textOutput("currentTime", container = span))),box(infoBoxOutput("gmv_runrate_per_second"))),
  204. #fluidRow(box(h2("Live GMV Numbers")),box(h2("Live Supply and Demand numbers"))),
  205. fluidRow(box(valueBoxOutput("gmv_per_second",width=3),valueBoxOutput("GMV_this_month",width=3),valueBoxOutput("GMV_this_week",width=3), #valueBoxOutput("commission_this_month"),
  206. valueBoxOutput("bookingsrightnow",width=3),width=12)),
  207. fluidRow(box(radioButtons("metric_choice","Select a KPI",choices = list("GMV"=1,"Insurance"=2),selected=1,inline=TRUE),
  208. dygraphOutput("time_series1"),width=12)),
  209. #valueBoxOutput("utilisation_right_now",width=3),width=12),
  210. fluidRow(
  211. box(radioButtons("cbb","Select a metric",choices = list("Count" = 1,"Average duration (to date)" = 2),selected = 1,inline = TRUE),
  212. plotOutput("current_bookings_breakdown1")),#box(plotOutput("current_bookings_breakdown2")),
  213. box(h2("New bookings today"),dataTableOutput("bookingstoday"))),
  214. #bubblesOutput("bubbles"))),
  215.  
  216. #box(valueBoxOutput("gmv_per_second_weekly")),
  217. fluidRow(
  218. box(h2("Breakdown of current bookings:"),
  219. radioButtons("dog","Group by",choices = list("All" = 5, "Manufacturer" = 1, "Year" = 2, "Uber Type" = 3, "Manufacturer and Uber type" = 4), selected = 5, inline = TRUE),
  220. dataTableOutput("vehiclesbreakdown")),
  221. fluidRow(box(bubblesOutput("trybubbles")))
  222.  
  223.  
  224. )),
  225.  
  226. tabItem(tabName = "demand",
  227. fluidRow(box(h1("Demand driven data"),valueBoxOutput("new_drivers_this_month"),valueBoxOutput("new_drivers_today")),
  228. box(h2("Drivers at risk of churning"),h4("Drivers whose current booking will end before or after n days and are not down to renew"),
  229. dataTableOutput("drivers_at_risk_of_churning"))),
  230. fluidRow(box(h2("New drivers acquired today"),dataTableOutput("new_drivers_today_table")),
  231. fluidRow(box(h2("Driver acquisition historically"),
  232. #dateInput("date_conceived","How many days back would you like to look at?",min=as.Date("2016-01-01"),max = Sys.Date(),value=28,format = "%b %y"),
  233. radioButtons("date_conceived","Time period",choices = list("Last Month" = 28,"Last 3 months" = 84, "Last Year" = 340),selected = 28,inline = TRUE),
  234. plotOutput("drivers_conception"))),
  235. box(h2("Bookings which are earning us negative commission"),dataTableOutput("dodgybookings"),width=12))),
  236.  
  237.  
  238. tabItem(tabName = "supply",
  239. fluidRow(box(h1("Supply driven data"),valueBoxOutput("excess_vehicles"))),
  240. fluidRow(box(h2("Breakdown of suppliers"),dataTableOutput("owner_breakdown")),
  241. box(h2("The following vehicles are not on hire and are not booked onto any future bookings"),
  242. radioButtons("group1","What would you like to group by?",choices = list("All"=5,"Supplier"=1,"Vehicle Make"=2,"Vehicle Model"=3,"Year"=4),selected=3,inline=TRUE),
  243. bubblesOutput("excess_vehicles_table")))
  244. ),
  245.  
  246. tabItem(tabName = "cohorts",
  247. fluidRow(box(plotOutput("cohorts_LTV")),box(plotOutput("cohorts_LTV2"))),
  248. fluidRow(box(h2("From which cohorts are our current active drivers from?"),plotOutput("active_cohorts")),
  249. box(h2("Number of drivers that joined us in each month"),plotOutput("cohorts")))
  250. ),
  251.  
  252. tabItem(tabName = "vehicles",
  253. fluidRow(box(dataTableOutput("vehicle_kpis"), radioButtons("com_or_gmv","Select Commission or GMV",choices = list("GMV" = 1, "Commission" = 2), selected = 1, inline = TRUE),
  254. bubblesOutput("vehicle_kpis_bubble")),
  255. box(dataTableOutput("vehicle_kpis2"), radioButtons("com_or_gmv2","Select Commission or GMV",choices = list("GMV" = 1, "Commission" = 2), selected = 1, inline = TRUE),
  256. bubblesOutput("vehicle_kpis_bubble2"))))
  257. )
  258.  
  259.  
  260.  
  261. #box(valueBoxOutput("excess_vehicles"),valueBoxOutput("bookingsrightnow")),
  262. )
  263. )
  264.  
  265.  
  266. server <- function(input, output, session) {
  267.  
  268.  
  269. #Dashboard tab
  270. output$currentTime <- renderText({
  271. invalidateLater(1000, session)
  272. format(Sys.time() %>% format("%A %d %B %H:%M:%S"))
  273. })
  274. output$commission_this_month <- renderValueBox({
  275. invalidateLater(10000)
  276. valueBox((dollar_format("£")(KPIS_monthly$booking_commission)),"Commission this month (to date)", icon = icon("thumbs-up"), color = "green")
  277. })
  278. output$GMV_this_month <- renderValueBox({
  279. invalidateLater(10000)
  280. valueBox((dollar_format("£")(KPIS_monthly$GMV)),"GMV so far this month", icon = icon("thumbs-up"), color = "green")
  281. })
  282. output$GMV_this_week <- renderValueBox({
  283. invalidateLater(10000)
  284. valueBox((dollar_format("£")(KPIS_weekly$GMV)),"GMV so far this week", icon = icon("thumbs-up"), color = "green")
  285. })
  286.  
  287. output$gmv_per_second <- renderValueBox({
  288. invalidateLater(500)
  289. y <- x %>% mutate(seconds = ((duration_in_given_month-1)*24*60*60)+(Sys.time() %>% format("%H") %>% as.numeric())*3600+(Sys.time() %>% format("%M") %>% as.numeric())*60+(Sys.time() %>% format("%S") %>% as.numeric()), x1 = (GMV*seconds/(24*7*60*60)) %>%round(2)) %>% mutate(seconds = ((duration_in_given_month-1)*24*60*60)+(Sys.time() %>% format("%H") %>% as.numeric())*3600+(Sys.time() %>% format("%M") %>% as.numeric())*60+(Sys.time() %>% format("%S") %>% as.numeric()), x1 = GMV*seconds/(24*7*60*60)) %>% summarise(sum(as.numeric(x1)))
  290. y <- dollar_format("£")(y[[1]])
  291. valueBox(y,"GMV this month to the nearest second")
  292. })
  293.  
  294.  
  295. output$gmv_per_second_weekly <- renderValueBox({
  296. invalidateLater(500)
  297. y1 <- x1 %>% mutate(seconds = ((duration_in_given_month-1)*24*60*60)+(Sys.time() %>% format("%H") %>% as.numeric())*3600+(Sys.time() %>% format("%M") %>% as.numeric())*60+(Sys.time() %>% format("%S") %>% as.numeric()), x1 = (GMV*seconds/(24*7*60*60)) %>%round(2)) %>% mutate(seconds = ((duration_in_given_month-1)*24*60*60)+(Sys.time() %>% format("%H") %>% as.numeric())*3600+(Sys.time() %>% format("%M") %>% as.numeric())*60+(Sys.time() %>% format("%S") %>% as.numeric()), x1 = GMV*seconds/(24*7*60*60)) %>% summarise(sum(as.numeric(x1)))
  298. y1 <- dollar_format("£")(y1[[1]])
  299. valueBox(y,"GMV this month to the nearest second")
  300. })
  301.  
  302.  
  303.  
  304. output$gmv_runrate_per_second <- renderInfoBox({
  305. invalidateLater(500)
  306. y <- x %>% mutate(seconds = ((duration_in_given_month-1)*24*60*60)+(Sys.time() %>% format("%H") %>% as.numeric())*3600+(Sys.time() %>% format("%M") %>% as.numeric())*60+(Sys.time() %>% format("%S") %>% as.numeric()), x1 = (GMV*seconds/(24*7*60*60)) %>%round(2)) %>% mutate(seconds = ((duration_in_given_month-1)*24*60*60)+(Sys.time() %>% format("%H") %>% as.numeric())*3600+(Sys.time() %>% format("%M") %>% as.numeric())*60+(Sys.time() %>% format("%S") %>% as.numeric()), x1 = GMV*seconds/(24*7*60*60)) %>% summarise(sum(as.numeric(x1)))
  307. y <- dollar_format("£")(y[[1]]*12)
  308. infoBox(h1(y),"Annual GMV")
  309. })
  310.  
  311.  
  312.  
  313.  
  314. output$bookingsrightnow <- renderValueBox({
  315. invalidateLater(1000)
  316. valueBox(bookings %>% filter((end_time>=Sys.time() | is.na(end_time)) & start_time<=Sys.time()) %>% nrow(),"Bookings occurring right now",icon = icon("thumbs-up"),color = "green")})
  317. output$utilisation_right_now <- renderValueBox({
  318. valueBox((nrow(vehicles_on_hire) / nrow(vehicles_available)) %>% percent(), "Utilisation", color = "red")
  319. })
  320.  
  321.  
  322. output$excess_vehicles <- renderValueBox({valueBox(vehicles_enabled %>% filter(is.na(booking_id)) %>% nrow(),"Vehicles available to be booked")})
  323.  
  324. output$excess_vehicles_table <- renderBubbles({
  325. v <- vehicles_enabled %>% filter(is.na(booking_id)) %>% select(vehicle_id,supplier,vehicle_make,vehicle_model,year,licence_plate)
  326. if (input$group1==1) {
  327. a <- v %>% group_by(supplier) %>% summarise(count = n()) %>% arrange(desc(count))
  328. plot <- bubbles(a$count,a$supplier)
  329. }
  330. if (input$group1==2) {
  331. a <- v %>% group_by(vehicle_make) %>% summarise(count = n()) %>% arrange(desc(count))
  332. plot <- bubbles(a$count,a$vehicle_make)
  333. }
  334. if (input$group1==3) {
  335. a <- v %>% group_by(vehicle_model) %>% summarise(count = n()) %>% arrange(desc(count))
  336. plot <- bubbles(a$count,a$vehicle_model)
  337. }
  338. if (input$group1==4) {
  339. a <- v %>% group_by(year) %>% summarise(count = n()) %>% arrange(desc(count))
  340. plot <- bubbles(a$count,a$year)
  341. }
  342. #if (input$group1==2) {v <- v %>% group_by(vehicle_make) %>% summarise(count = n()) %>% arrange(desc(count))}
  343. #if (input$group1==3) {v <- v %>% group_by(vehicle_model) %>% summarise(count = n()) %>% arrange(desc(count))}
  344. #if (input$group1==4) {v <- v %>% group_by(year) %>% summarise(count = n()) %>% arrange(desc(count))}
  345. plot
  346. })
  347.  
  348. output$bookingstoday <- renderDataTable({
  349. invalidateLater(60000)
  350. x <- agent
  351. x$created_at <- as.character(x$created_at)
  352. x$start_time <- as.character(x$start_time)
  353. x$end_time <- ifelse(is.na(x$end_time),"Rolling",as.character(x$end_time))
  354. x <- x[,c(1,5:9)]
  355. colnames(x) <- c("Booking Id","Driver Id","Start Time","End Time","Booked at","Booked by")
  356. print(x)})
  357.  
  358. output$vehiclesbreakdown <- renderDataTable({
  359. invalidateLater(1000000)
  360.  
  361. if (input$dog==1) {
  362. x <- vehicles_on_hire[,-1] %>% group_by(Manufacturer = vehicle_make) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  363. colnames(x) <- c("Manufacturer","Count","Average List Price")
  364. }
  365. if (input$dog==2) {
  366. x <- vehicles_on_hire[,-1] %>% group_by(Year = year) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  367. colnames(x) <- c("Year","Count","Average List Price")
  368. }
  369. if (input$dog==3) {
  370. x <- vehicles_on_hire[,-1] %>% group_by('Uber Type' = sub_type) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  371. colnames(x) <- c("Uber Type","Count","Average List Price")
  372. }
  373. if (input$dog==4) {
  374. x <- vehicles_on_hire[,-1] %>% group_by(Manufacturer = vehicle_make,'Uber Type' = sub_type) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  375. colnames(x) <- c("Manufacturer","Uber Type","Count","Average List Price")
  376. }
  377. if (input$dog==5) {
  378. x <- vehicles_on_hire[,-1] %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  379. colnames(x) <- c("Count","Average List Price")
  380. }
  381. x$'Average List Price' <- dollar_format("£")(x$'Average List Price')
  382. print(x)
  383. })
  384.  
  385. # output$bubbles <- renderBubbles({
  386. # if (input$dog==1) {
  387. # x <- vehicles_on_hire[,-1] %>% group_by(Manufacturer = vehicle_make) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  388. # bubbles(x$Count,x$Manufacturer)
  389. # }
  390. # if (input$dog==2) {
  391. # x <- vehicles_on_hire[,-1] %>% group_by(Year = year) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  392. # bubbles(x$Count,x$Year)
  393. # }
  394. # if (input$dog==3) {
  395. # x <- vehicles_on_hire[,-1] %>% group_by('Uber Type' = sub_type) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))
  396. # bubbles(x$Count,x$'Uber Type')
  397. # }
  398.  
  399. #if (input$dog==4) {x <- vehicles_on_hire[,-1] %>% group_by(Manufacturer = vehicle_make,'Uber Type' = sub_type) %>% summarise(Count = n(), Average_List_Price = mean(list_price,na.rm=T))}
  400. # })
  401.  
  402.  
  403.  
  404.  
  405. output$new_drivers_this_month <- renderValueBox({
  406. valueBox(cohorts %>% filter(cohort==Sys.Date() %>% format("%Y %m")) %>% nrow(),"New drivers this month")
  407. })
  408.  
  409. output$new_drivers_today <- renderValueBox({
  410. valueBox(cohorts %>% filter(time_of_conception %>% as.Date()==Sys.Date()) %>% nrow(),"New drivers today")
  411. })
  412.  
  413.  
  414. output$cohorts <- renderPlot({
  415. c <- cohorts %>% group_by(cohort,cohort_start) %>% summarise(count = n()) %>% mutate(Cohort = format(cohort_start,"%b %y"))
  416. ggplot(c, aes(reorder(Cohort,cohort_start),count))+geom_bar(stat = 'identity', fill = "green4")+theme_classic()+geom_text(aes(label = count),vjust=-0.5)+labs(x = "Cohort")+theme(axis.text.y = element_blank(),axis.line = element_blank(), axis.ticks.x = element_blank(),axis.title.y = element_blank(), axis.ticks.y = element_blank())
  417. })
  418.  
  419. output$new_drivers_today_table <- renderDataTable({
  420. a <- cohorts %>% filter(time_of_conception %>% as.Date()==Sys.Date())
  421. a <- left_join(a,agent,by="driver_id") %>% arrange(desc(time_of_conception)) %>% select('Booked by',driver_id,time_of_conception,start_time,end_time,booking_id)
  422. a$time_of_conception <- as.character(a$time_of_conception)
  423. a$start_time <- as.character(a$start_time)
  424. a$end_time <- as.character(a$end_time)
  425. a
  426. })
  427.  
  428. output$dodgybookings <- renderDataTable({
  429. invalidateLater(20000)
  430. d <- bookings
  431. d <- d %>% mutate(GMV = ((b-i)*d+i),
  432. GMV_pre_insurance = (b-i)*d,
  433. Commission = (b-i-o)*d,
  434. Payout = o*d, Insurance = i)
  435. #d$end_time <- ifelse(d$end_time>'2050-01-01',"Rolling",d$end_time %>% as.Date())
  436. d <- d %>% filter(start_time<=Sys.time(),is.na(end_time) | end_time>=Sys.time(),end_time<'2050-01-01') %>% filter(Commission<0)
  437. d$end_time <- ifelse(is.na(d$end_time),"Rolling",d$end_time %>% as.character())
  438. d$d <- (d$d) %>% percent()
  439. d$created_at <- as.character(d$created_at)
  440. d$start_time <- as.character(d$start_time)
  441. d$b <- dollar_format("£")(d$b)
  442. d$i <- dollar_format("£")(d$i)
  443. d$o <- dollar_format("£")(d$o)
  444. d$GMV <- dollar_format("£")(d$GMV)
  445. #d$Commission <- dollar_format("£")(d$Commission)
  446. d$Payout <- dollar_format("£")(d$Payout)
  447. colnames(d) <- c("Driver Id","Start Time","End Time","Booked at","Booking Amount","Insurance","Discount","Owner Payout","GMV","GMV Pre Insurance","Commission","Payout","Insurance")
  448. print(d[,-c(13,10)])
  449. })
  450.  
  451.  
  452. output$owner_breakdown <- renderDataTable({
  453. vehicles_enabled %>% group_by(supplier) %>% summarise('Vehicles on Platform' = n(),'Vehicles in Bookings' = sum(!is.na(booking_id)),Utilisation = (sum(!is.na(booking_id))/n()) %>% percent()) %>% arrange(desc(`Vehicles in Bookings`))
  454. })
  455.  
  456. output$drivers_at_risk_of_churning <- renderDataTable({
  457. renew <- bookings %>% filter(end_time>Sys.time() | is.na(end_time)) %>% group_by(driver_id) %>% summarise(count = n(), end = min(end_time)) %>% select(c(1,3))
  458. renew <- renew %>% arrange(end)
  459. renew$end <- as.character(renew$end)
  460. colnames(renew) <- c("Driver Id","Current booking will end on:")
  461. print(renew)
  462. })
  463.  
  464. output$drivers_conception <- renderPlot({
  465. cohorts %>% filter(time_of_conception>as.Date('2017-01-01')) %>% group_by(week_of_conception = format(time_of_conception,"%U")) %>% summarise(count = n(), week_starting = min(format(time_of_conception,"%Y-%m-%d")) %>% as.Date()) %>% filter(week_starting>=Sys.Date()-days(input$date_conceived)) %>% ggplot(aes(reorder(week_of_conception,week_starting),count))+geom_bar(stat = 'identity', fill = "green4")+theme_minimal()+labs(x = "Week number", y = "Count")
  466. })
  467.  
  468. #output$dodgybookingsloss <- renderValueBox(valueBox(sum(d$'Commission'),"Weekly amount lost due to the following bookings:", color = "red"))
  469.  
  470. output$cohorts_LTV <- renderPlot({
  471. vehicles <- dbGetQuery(con,"select vehicles.id as vehicle_id, vehicle_make, year, sub_type,
  472. case when companies.name is null then concat(partners.first_name,' ',partners.last_name,'*') else companies.name end as Supplier
  473.  
  474.  
  475. from vehicles
  476. left join companies on companies.partner_id = vehicles.owner_id
  477. left join partners on vehicles.owner_id = partners.id") %>% as.tibble()
  478.  
  479. #bookings <- dbGetQuery(con, bookingsquery) %>% as.tibble()
  480.  
  481. bookings$d <- (100-bookings$d)/100
  482. bookings <- bookings %>% mutate(GMV = ((b-i)*d+i)/100,
  483. GMV_pre_insurance = (b-i)*d/100,
  484. Commission = (b-i-o)*d/100,
  485. Payout = o*d/100, Insurance = i/100)
  486.  
  487. bookings <- bookings %>%
  488.  
  489. mutate(duration_to_date = ifelse((end_time>Sys.Date()) | is.na(end_time), Sys.Date()-as.Date(start_time), as.Date(end_time)-as.Date(start_time)),
  490. dp_gmv = duration_to_date*GMV/7,
  491. dp_gmv_pre_insurance = duration_to_date*GMV_pre_insurance/7, dp_booking_commission = duration_to_date*Commission/7,dp_insurance = duration_to_date*Insurance/7,dp_payout = duration_to_date*Payout/7)
  492.  
  493. #KPIS <- bookings %>% summarise(GMV_less_insurance = sum(dp_gmv_pre_insurance)/100,booking_commission = sum(dp_booking_commission)/100,Insurance = sum(dp_insurance)/100,Payout = sum(dp_payout)/100,GMV = sum(dp_gmv)/100,date = prev_date %>% format("%b %y")) %>% print()
  494. drivers <- bookings %>% group_by(driver_id) %>% summarise(cohort = format(min(as.Date(start_time)),"%Y %m"),cohort_start = format(min(as.Date(start_time)),"%Y-%m-01") %>% as.Date(),driver_plan_count = n(),driver_lifetime_duration = sum(duration_to_date), gmv_to_date = sum(dp_gmv), booking_commission_to_date = sum(dp_booking_commission) %>% round(2))
  495. drivers_kpis <- drivers %>% group_by(cohort,cohort_start) %>%
  496. summarise(size = n(),gmv = sum(gmv_to_date),commission = sum(booking_commission_to_date),
  497. duration = mean(driver_lifetime_duration), LTV_gmv = gmv/size, LTV_commission = commission/size) %>% as.data.frame()
  498.  
  499. vehicle_kpis <- left_join(bookings %>% filter(start_time>as.Date('2016-07-01')),vehicles,by="vehicle_id") %>% group_by(vehicle_id,vehicle_make,year,sub_type,supplier) %>%
  500. summarise(bookings = n(),gmv = sum(dp_gmv),commission = sum(dp_booking_commission), commission_rate = percent(commission/gmv), days_hired = sum(duration_to_date))
  501.  
  502.  
  503. ggplot(drivers_kpis, aes(reorder(cohort,cohort_start),LTV_commission))+geom_bar(stat = 'identity',fill = "green4")+theme_classic()+geom_text(aes(label=dollar_format("£")(LTV_commission)),vjust=-0.5)+
  504. theme(axis.text.y = element_blank(),axis.line = element_blank(), axis.ticks.x = element_blank(),axis.title.y = element_blank(), axis.ticks.y = element_blank())+labs(x = "Cohort",title = "LTV Commission")
  505.  
  506. #
  507. })
  508.  
  509. output$cohorts_LTV2 <- renderPlot({
  510. ggplot(drivers_kpis, aes(reorder(cohort,cohort_start),LTV_gmv))+geom_bar(stat = 'identity',fill = "green4")+theme_classic()+geom_text(aes(label=dollar_format("£")(LTV_gmv)),vjust=-0.5)+
  511. theme(axis.text.y = element_blank(),axis.line = element_blank(), axis.ticks.x = element_blank(),axis.title.y = element_blank(), axis.ticks.y = element_blank())+labs(x = "Cohort",title = "LTV GMV")
  512. })
  513.  
  514. output$vehicle_kpis <- renderDataTable({
  515. vehicle_kpis %>% group_by(sub_type) %>% summarise('Avg Booking Duration' = paste(mean(days_hired) %>% round(),"days"),'# Bookings' = n(), GMV_per_day_on_hire = dollar_format("£")(sum(gmv)/sum(days_hired)), 'Commission/day' = dollar_format("£")(sum(commission)/sum(days_hired)), 'Commission %' = ((sum(commission)/sum(days_hired))/(sum(gmv)/sum(days_hired))) %>% percent())
  516. })
  517.  
  518. output$vehicle_kpis2 <- renderDataTable({
  519. vehicle_kpis %>% group_by(year) %>% summarise('Avg Booking Duration' = paste(mean(days_hired) %>% round(),"days"),'# Bookings' = n(), GMV_per_day_on_hire = dollar_format("£")(sum(gmv)/sum(days_hired)), 'Commission/day' = dollar_format("£")(sum(commission)/sum(days_hired)), 'Commission %' = ((sum(commission)/sum(days_hired))/(sum(gmv)/sum(days_hired))) %>% percent())
  520. })
  521.  
  522.  
  523. output$vehicle_kpis_bubble <- renderBubbles({
  524. f <- vehicle_kpis %>% group_by(sub_type) %>% summarise('Avg Booking Duration' = paste(mean(days_hired) %>% round(),"days"),'# Bookings' = n(), GMV_per_day_on_hire = (sum(gmv)/sum(days_hired)), 'Commission/day' = (sum(commission)/sum(days_hired)), 'Commission %' = ((sum(commission)/sum(days_hired))/(sum(gmv)/sum(days_hired))) %>% percent())
  525. if (input$com_or_gmv==1) {sub_plot <- bubbles(f$GMV_per_day_on_hire,paste(f$sub_type,':',dollar_format("£")(f$GMV_per_day_on_hire)))}
  526. if (input$com_or_gmv==2) {sub_plot <- bubbles(f$`Commission/day`,paste(f$sub_type,':',dollar_format("£")(f$`Commission/day`)))}
  527. sub_plot
  528. })
  529.  
  530. output$vehicle_kpis_bubble2 <- renderBubbles({
  531. g <- vehicle_kpis %>% group_by(sub_type) %>% summarise('Avg Booking Duration' = paste(mean(days_hired) %>% round(),"days"),'# Bookings' = n(), GMV_per_day_on_hire = (sum(gmv)/sum(days_hired)), 'Commission/day' = (sum(commission)/sum(days_hired)), 'Commission %' = ((sum(commission)/sum(days_hired))/(sum(gmv)/sum(days_hired))) %>% percent())
  532. if (input$com_or_gmv2==1) {sub_plot2 <- bubbles(g$GMV_per_day_on_hire,paste(g$sub_type,':',dollar_format("£")(g$GMV_per_day_on_hire)))}
  533. if (input$com_or_gmv2==2) {sub_plot2 <- bubbles(g$`Commission/day`,paste(g$sub_type,':',dollar_format("£")(g$`Commission/day`)))}
  534. sub_plot2
  535. })
  536.  
  537. output$active_cohorts <- renderPlot({
  538. bookings <- dbGetQuery(con, bookingsquery) %>% as.tibble()
  539. active_drivers <- bookings %>% filter(start_time<=Sys.time(),is.na(end_time) | end_time>Sys.time()) %>% select(3) %>%
  540. left_join(drivers,by="driver_id") %>% mutate(cohort_bucket = ifelse(format(cohort_start,"%Y") %>% as.numeric()==2016,2016,
  541. ifelse(format(cohort_start,"%Y")==2017 & cohort_start<'2017-09-01',"Jan-Aug 17",format(cohort_start,"%b %y"))),
  542.  
  543. cohort_bucket_start = ifelse(cohort_bucket==2016,as.Date('2016-01-01'),
  544. ifelse(cohort_bucket=="Jan-Aug 17",as.Date('2017-08-01'),cohort_start))) %>%
  545.  
  546. group_by(cohort_bucket,cohort_bucket_start) %>% summarise(count = n(), avg_lifetime = mean(driver_lifetime_duration))
  547. ggplot(active_drivers, aes(x = reorder(cohort_bucket,cohort_bucket_start),y = count))+geom_bar(stat = 'identity', fill = "green4")+theme_classic()+geom_text(aes(label=count),vjust=-0.5, size= 6)+
  548. theme(axis.text.y = element_blank(),axis.line = element_blank(), axis.ticks.x = element_blank(),axis.title.y = element_blank(), axis.ticks.y = element_blank(), axis.text.x = element_text(size = 12), axis.title.x = element_text(size = 14))+labs(x = "Cohort Group")
  549. })
  550.  
  551. output$current_bookings_breakdown1 <- renderPlot({
  552. if (input$cbb==1) {cbbplot <- ggplot(current_bookings, aes(x = factor(1), y = count, fill = type, group = type))+geom_bar(stat = 'identity')+
  553. geom_text(aes(label = paste(type,':',count),y = posy, x = 1.85, size = 6))+scale_fill_manual(values = c("green4","green1"))+
  554. theme_void()+coord_polar(theta = "y")+theme(legend.position = "none")}
  555. if (input$cbb==2) {cbbplot <- ggplot(current_bookings, aes(type,avg_duration))+geom_bar(stat = 'identity',fill="green3",width = 0.35)+geom_text(aes(label=paste(round(avg_duration),"days")),vjust=-0.5)+theme_classic()+
  556. theme(axis.text.y = element_blank(),axis.ticks.x = element_blank(),axis.title.y = element_blank(), axis.ticks.y = element_blank(), axis.text.x = element_text(size = 12), axis.title.x = element_blank())}
  557. cbbplot
  558. })
  559.  
  560. output$trybubbles <- renderBubbles({
  561. drivers <- bookings %>% group_by(driver_id) %>% summarise(cohort = format(min(as.Date(start_time)),"%Y %m"),cohort_start = format(min(as.Date(start_time)),"%Y-%m-01") %>% as.Date())
  562. b <- drivers %>% group_by(cohort) %>% summarise(count = n())
  563. bubbles(b$count,b$cohort)
  564. })
  565.  
  566. # output$current_bookings_breakdown2 <- renderPlot({
  567. # ggplot(current_bookings, aes(type,avg_duration))+geom_bar(stat = 'identity',fill="green3",width = 0.35)+geom_text(aes(label=paste(round(avg_duration),"days")),vjust=-0.5)+theme_classic()+
  568. # theme(axis.text.y = element_blank(),axis.ticks.x = element_blank(),axis.title.y = element_blank(), axis.ticks.y = element_blank(), axis.text.x = element_text(size = 12), axis.title.x = element_blank())
  569. #})
  570. output$time_series1 <- renderDygraph({
  571. if (input$metric_choice==1) {y <- ts1 %>% filter(Metric=="GMV") %>% select(date2,Amount) %>% filter(date2>=as.Date('2017-01-01'))}
  572. if (input$metric_choice==2) {y <- ts1 %>% filter(Metric=="Insurance") %>% select(date2,Amount) %>% filter(date2>=as.Date('2017-01-01'))}
  573. dygraph(as.xts(y$Amount,order.by = y$date2))
  574. })
  575. }
  576.  
  577. # Run the application
  578. shinyApp(ui = ui, server = server)
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!