Case_Study(1).Rmd

---
title: "Case Study"
output: 
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: fill
runtime: shiny
---

```{r setup, include=FALSE}
library(flexdashboard)
library(dplyr)
library(ggplot2)
library(plotly)
library(readxl)
library(tidyr)
library(wordcloud)
library(tm)
april_revenue = read_excel('April 2017_Revenue sheet.xlsx')
april_leads = read_excel('April Leads.xlsx', sheet = 2)
march_revenue = read_excel('March 2017_Revenue sheet.xlsx')
march_leads = read_excel('March Leads.xlsx', sheet = 2)
may_revenue = read_excel('May 2017_Revenue sheet.xlsx')
may_leads = read_excel('May Leads.xlsx', sheet = 2)
leads = read.csv('Source_wise_lead.csv')
admissions = read.csv('Source_wise_Addmission_1.csv')
```

```{r}
april_revenue$Month = 'April'
march_revenue$Month = 'March'
may_revenue$Month = 'May'
revenue = rbind(april_revenue, march_revenue, may_revenue)

april_leads$Month = 'April'
march_leads$Month = 'March'
may_leads$Month = 'May'
month_leads = rbind(april_leads, march_leads, may_leads)
month_leads = month_leads[,-1]

rm(april_revenue, march_revenue, may_revenue, april_leads, march_leads, may_leads)

admissions = admissions[!admissions$Source.Name %in% c('Null','Source Not Available','Others'),]
leads = leads[!leads$Source.Bucket %in% c('Null','Source Not Available','Others'),]
source = merge(admissions,leads, by.x = "Source.Name", by.y = "Source.Bucket")
rm(admissions, leads)

revenue = revenue %>% select(-`Unit Price`)
month_leads = month_leads %>% select(-Location)
```



```{r}
clean_str = function(str){
  c = strsplit(str, 'errors=true')[[1]]
  return(paste(c, collapse = ''))
}
source$Source.Name = tolower(source$Source.Name)
month_leads$`UTM Source` = sapply(month_leads$`UTM Source`, clean_str)
month_leads$`UTM Source` = tolower(month_leads$`UTM Source`)
month_leads$`UTM Source` = gsub(' ','',month_leads$`UTM Source`)
month_leads$`UTM Source` = gsub('freshersworld','fresherworld',month_leads$`UTM Source`)
month_leads$`UTM Source` = gsub('fb','facebook',month_leads$`UTM Source`)

for(i in source$Source.Name){
  month_leads$`UTM Source` = gsub(paste0(i,'.*'),i,month_leads$`UTM Source`)
}
```

```{r}
pgd = c('Manipal University - Post Graduate Diploma in Data Science (Full Time)')
re = c('Manipal University- PG Dip. in Data Science (Full Time) - Application Form|Manipal University - Post Graduate Diploma in Data Science (Full Time)','Manipal University- PG Dip. in Data Science (Full Time) - Application Form|Business Analytics','Manipal University- PG Dip. in Data Science (Full Time) - Application Form','Manipal University - Post Graduate Diploma in Data Science (Full Time)|Manipal University- PG Dip. in Data Science (Full Time) - Application Form','Manipal University- PG Dip. in Data Science (Part Time) - Application Form')
pt = c('Manipal University - Post Graduate Diploma in Data Science (Part Time)')
adv = c('Advanced Business Analytics with R Certification Course|Big Data Analytics using Hadoop','Advanced Business Analytics with R Certification Course|Certificate in Advanced MS Excel 2016','Advanced Business Analytics with R Certification Course|Certificate Program in HR Analytics|Financial Management')
revenue = revenue %>% filter(!`Course Title` %in% re)
revenue$`Course Title`[revenue$`Course Title` %in% pgd] = 'PG Diploma in Data Science (Full-time)'
revenue$`Course Title`[revenue$`Course Title` %in% pt] = 'PG Diploma in Data Science (Part-time)'
revenue$`Course Title`[revenue$`Course Title` %in% adv] = 'Advanced Business Analytics with R Certification Course'
```

```{r}
course11 = revenue %>%
  group_by(`Course Title`,Month) %>% filter(`Currency Code` == "USD") %>%
  summarise(course_revenue=sum(Amount)*68)

course21 = revenue %>% 
  group_by(`Course Title`,Month) %>% filter(`Currency Code`=="INR") %>% 
  summarise(course_revenue=sum(Amount))

course_ff = rbind(course11,course21)
```

```{r}
total_calls_assinged <- na.omit(month_leads %>% group_by(`Assigned To`) %>% summarise(total_calls_made=n()) %>% arrange(-total_calls_made)) 

#For coun
#total_admission_by_couns <- revenue %>% filter(`Payment Status`=="success",`Course Title` %in% c(pgd,pt,adv)) %>% group_by(`Counselor Name`) %>% summarise(Total_revenue_generated=sum(Amount),success_transaction=n()) 


total_success_assinged<- na.omit(month_leads %>% group_by(`Assigned To`,Status) %>% filter(Status=="Interested/Follow Up") %>% summarise(total_responce=n()) %>% arrange(-total_responce)) 

total_success_rate_assinged <- merge(total_success_assinged,total_calls_assinged) %>% mutate(success_rate=total_responce*100/total_calls_made) %>% arrange(-success_rate) %>% head(10)


#In ggplot
#success_rate_bar<- total_success_rate_assinged %>% ggplot(aes(x=reorder(`Assigned To`,-success_rate),y=success_rate))+geom_bar(stat = "identity")+labs(x="Counselor",y="Success Rate",title="Top ten counselor for highest success rate") 
#In Plotly 
```

```{r}
total_admission_by_counseler_coursewise <- revenue %>% 
  filter(`Payment Status`=="success",`Course Title` %in% c(pgd,pt,adv)) %>% 
  group_by(`Counselor Name`,`Course Title`) %>%
  summarise(Total_revenue_generated=sum(Amount),success_transaction=n()) 
total_success_assinged_coursewise<- na.omit(month_leads %>% group_by(`Assigned To`,Course,Status) %>%
                                              filter(Status=="Interested/Follow Up") %>%
                                              summarise(tot_responce=n()) %>% arrange(`Assigned To`))

total_calls_assinged_coursewise <- na.omit(
  month_leads %>% group_by(`Assigned To`,Course) %>%
    summarise(total_calls_made=n()) %>% arrange(-total_calls_made)) 
total_success_rate_assinged_coursewise<-merge(total_success_assinged_coursewise,total_calls_assinged_coursewise,by=c("Assigned To","Course")) %>%
  mutate(success_rate=tot_responce*100/total_calls_made) %>% arrange(Course,-success_rate)
#In ggplot 
#success_rate_bar_coursewise<- total_success_rate_assinged_coursewise %>% arrange(-success_rate) %>% ggplot(aes(x=reorder(`Assigned To`,-success_rate),y=Course,fill=-success_rate,text=paste("success/call:",paste(tot_responce,total_calls_made,sep = "/"))))+geom_tile()+theme_bw()+ labs(x="Counselor",y="")+ # theme(axis.text.x = element_text(angle = -45,hjust = 0))+labs(x="Assigned to",y="Course",title="Courseswise success rate for Counselors")
# # # ggplotly(success_rate_bar_coursewise,tooltip = c("x","y","text","fill")) 

```


```{r}
#write.csv(month_leads,'final_months.csv')
#write.csv(revenue,'final_revenue.csv')
#write.csv(source,'final_source.csv')
```



Revenue {.sidebar}
===================================

```{r}
selectInput(inputId = 'course',label = 'Pick a course',
            choices = c('All',unique(month_leads$Course)), selected = 'All')
#selectInput("course_for_source",label = '',choices = unique(month_leads$Course)) 
```

Best source
===================================

Row
-----------------------------------------------------------------------

### Highest leads

```{r}
renderValueBox({
  if(!input$course == 'All')
    month_leads = month_leads %>% filter(Course == input$course)
  month_leads$`UTM Source`[!month_leads$`UTM Source` %in% source$Source.Name] = 'others'
  mid = merge(revenue,month_leads %>% select(Email, `UTM Source`), by.x = 'Order Email', by.y = 'Email')
  mid = mid %>% group_by(`UTM Source`) %>% summarise(Revenue = sum(`Order Total`))
  source = merge(source, mid, by.x = 'Source.Name', by.y = 'UTM Source')
  
  value = max(source$Lead.count)
  cap = source$Source.Name[source$Lead.count == value]
  valueBox(value, caption = paste0(cap,' (Highest leads)'), color = 'Red', icon = 'fa-tasks')
})
```

### Highest admissions
```{r}
renderValueBox({
  if(!input$course == 'All')
    month_leads = month_leads %>% filter(Course == input$course)
  month_leads$`UTM Source`[!month_leads$`UTM Source` %in% source$Source.Name] = 'others'
  mid = merge(revenue,month_leads %>% select(Email, `UTM Source`), by.x = 'Order Email', by.y = 'Email')
  mid = mid %>% group_by(`UTM Source`) %>% summarise(Revenue = sum(`Order Total`))
  source = merge(source, mid, by.x = 'Source.Name', by.y = 'UTM Source')
  
  value = max(source$Enroll_count)
  cap = source$Source.Name[source$Enroll_count == value]
  valueBox(value, caption = paste0(cap,' (Highest admissions)'), color = 'Blue', icon = 'fa-check-circle')
})
```

### Highest Revenue

```{r}
renderValueBox({
  if(!input$course == 'All')
    month_leads = month_leads %>% filter(Course == input$course)
  month_leads$`UTM Source`[!month_leads$`UTM Source` %in% source$Source.Name] = 'others'
  mid = merge(revenue,month_leads %>% select(Email, `UTM Source`), by.x = 'Order Email', by.y = 'Email')
  mid = mid %>% group_by(`UTM Source`) %>% summarise(Revenue = sum(`Order Total`))
  source = merge(source, mid, by.x = 'Source.Name', by.y = 'UTM Source')
  
  value = max(source$Revenue)
  cap = source$Source.Name[source$Revenue == value]
  valueBox(value, caption = paste0(cap,' (Highest revenue)'), color = 'Green', icon = 'fa-money')
})
```


Row
-----------------------------------------------------------------------

### Marketing source giving us the best result:

```{r}
renderPlotly({
  if(!input$course == 'All')
    month_leads = month_leads %>% filter(Course == input$course)
  month_leads$`UTM Source`[!month_leads$`UTM Source` %in% source$Source.Name] = 'others'
  mid = merge(revenue,month_leads %>% select(Email, `UTM Source`), by.x = 'Order Email', by.y = 'Email')
  mid = mid %>% group_by(`UTM Source`) %>% summarise(Revenue = sum(`Order Total`))
  source = merge(source, mid, by.x = 'Source.Name', by.y = 'UTM Source')

  p = source %>% ggplot(aes(x = Lead.count, y = Enroll_count)) + 
    geom_point(aes(col = Source.Name, size = Revenue)) +
    xlab('Lead count') + ylab('Admission count') + theme_bw()
  ggplotly(p)
})
```

```{r}
#source %>%
#  ggplot(aes(x = Lead.count, y = Enroll_count)) + 
#  geom_point(aes(col = Source.Name, size = Lead.count))
```

Focus products
========================================

Row {.tabset}
-----------------------------------------------

### Product performance

```{r}
renderPlotly({
  course_ff$Month = factor(course_ff$Month,levels = c("March","April","May"),ordered = T) 
  chart4=course_ff %>%
    arrange(-course_revenue) %>% head(25) %>% 
    ggplot(aes(x = reorder(`Course Title`,course_revenue), y = course_revenue,fill=`Course Title`)) +
    geom_bar(stat="Identity") + theme_bw() + facet_grid(.~Month) + 
    theme(axis.text.x = element_blank()) + xlab("Course Title") + ylab("Revenue") 
  ggplotly(chart4)
})
```

### Hottest course

```{r}
renderPlotly({
  enr = revenue %>% group_by(`Course Title`) %>% summarise(enroll_count=n()) %>% 
    arrange(-enroll_count)%>%head(10) %>%
    ggplot(aes(x=reorder(`Course Title`,enroll_count),y=enroll_count,fill=`Course Title`)) + 
    geom_bar(stat = "Identity")+theme(axis.text.x = element_blank()) + 
    xlab('Top Courses') + ylab('Enrollment count')
  ggplotly(enr)
})
```


Profit strategy
=========================================

Row
-----------------------------------------------

### Profitable course

```{r}
renderValueBox({
  temp = revenue %>% 
  group_by(`Course Title`) %>% 
  summarise(Revenue_per_student = sum(`Order Total`)/length(unique(`Student Name`)),
            students_count = length(unique(`Student Name`)),
            Counselors_count = length(unique(`Counselor Name`)))
  value = temp$Revenue_per_student[temp$Revenue_per_student == max(temp$Revenue_per_student)]
  title = temp$`Course Title`[temp$Revenue_per_student == max(temp$Revenue_per_student)]
  valueBox(round(value), caption = paste0(title,' (Profitable course)'),icon = 'fa-money', color = 'Green')
})
```

Row {.tabset}
---------------------------------------------------

### Most Revenue generating courses

```{r}
renderPlotly({
r = revenue %>% 
  group_by(`Course Title`) %>% 
  summarise(Revenue_per_student = sum(`Order Total`)/length(unique(`Student Name`)),
            students_count = length(unique(`Student Name`)),
            Counselors_count = length(unique(`Counselor Name`))) %>% 
  arrange(-Revenue_per_student) %>% head(10) %>%  
  ggplot(aes(x = reorder(`Course Title`, -Revenue_per_student), y = Revenue_per_student)) + 
  geom_bar(stat = 'identity', aes(fill = `Course Title`)) + 
  xlab('Course title') + ylab('Revenue per student') + theme_bw() + 
  theme(axis.text.x = element_blank(), legend.position = 'none') +
  geom_text(aes(label = Counselors_count), position = position_stack(vjust = 0.5))
ggplotly(r)
})
```


### Best performing counselors

```{r}
renderPlotly({
  plotly_success_rate= total_success_rate_assinged %>% arrange(-success_rate) %>% plot_ly(x= ~reorder(`Assigned To`,-success_rate), y=~success_rate) %>% layout(xaxis = list(title="Counselor"), yaxis =list(title="Success Rate") ) 
plotly_success_rate
})
```

### Course wise counselors performance

```{r}
renderPlotly({
  plotly_coursewise <- total_success_rate_assinged_coursewise %>%
    plot_ly(x=~reorder(`Assigned To`,-success_rate),y=~Course,z=~success_rate,type="heatmap") %>%
    layout(xaxis=list(title="",showline=T, autorange=TRUE),
           yaxis=list(title="", autorange=TRUE),
           margin = list(l = 300, r = 50, b = 150, t = 50, pad = 4)) 
  plotly_coursewise
  })
```

### Source

```{r}
renderPlotly({
  course_by_source <- na.omit(month_leads %>% filter(Status=="Interested/Follow Up") %>%
                                group_by(Course,`UTM Source`) %>% summarise(total_lead_by_source=n()) %>%
                                arrange(Course,-total_lead_by_source))
  if(!input$course == 'All') course_by_source = course_by_source %>% filter(Course==input$course)
  else  course_by_source = course_by_source %>% arrange(-total_lead_by_source) %>% head(10)
  c = course_by_source %>% 
    ggplot(aes(x = reorder(`UTM Source`,-total_lead_by_source),y = total_lead_by_source)) +
    geom_bar(aes(fill = `UTM Source`), stat = "identity") + xlab('Source') + ylab('Total revenue') +
    theme(axis.text.x = element_blank()) 
  ggplotly(c)
})
```



```{r}
#r = revenue %>% group_by(`Counselor Name`) %>% summarise(student_count = length(unique(`Student Name`))) 
#library(wordcloud)
#wordcloud(words = r$`Counselor Name`, freq = r$Student_count, min.freq = 1,
#          max.words=100, 
#          colors=brewer.pal(8, "Dark2"))
```

Call center strategy
=========================================
Row {.tabset}
-------------------------------------------

### Call center performance

```{r}
calls = month_leads %>% filter(Status %in% c("Call Back","Interested/Follow Up")) %>% 
  group_by(`Assigned To`) %>% 
  summarise(Fail_to_callback = sum(is.na(`Callback/Followup Date`)),
            Called_back = sum(!is.na(`Callback/Followup Date`))) %>% arrange(-Fail_to_callback) %>% 
  mutate(Fail_to_callback = -Fail_to_callback)
calls %>% gather('Status','count',2:3) %>% 
  filter(!count == 0) %>% 
  ggplot(aes(x = `Assigned To`, y = count, fill = Status)) + geom_bar(stat = 'identity') + 
  geom_hline(yintercept = 0, color =c("white")) + 
  coord_flip() + 
  theme(axis.text.y = element_text(hjust=0))

```

### The cheating one

```{r, fig.width=35}
renderPlot({
  assign = merge(month_leads %>% filter(Status == 'Interested/Follow Up') %>% select(Name,Email,`Assigned To`),
      revenue %>% filter(`Payment Status` %in% c('failure','canceled')) %>% 
        select(`Order Email`), by.x = 'Email', by.y = 'Order Email')
  wordcloud(assign$`Assigned To`, min.freq = 1, max.words=100, 
          random.order=FALSE, rot.per=0.35, colors=brewer.pal(8, "Dark2"))
})
```