---
title: "ANLY 512 Final Project"
subtitle: Financial Dashboard
author: "Mengyuan Huang, Ziyan Zhang"
date: "`r Sys.Date()`"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    social: menu
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
setwd("C:/1D/School/Harrisburg/ANLY512DataVisualization/FinalProject")
set.seed(1)
```

```{r libraries and setup}
# library
library(ggplot2) # ploting
library(plotly)
library(plyr) # data manipulation
library(dplyr)  
library(tidyr)
```

```{r read data}
load("AirbnbData/inquiries.RData")  # inquiry level data
load("AirbnbData/listings.RData")   # listing level data
load("AirbnbData/users.RData")      # user level data

# data manipulation
inquiries$cnt_guests_group = ifelse(
  inquiries$cnt_guests < 10, as.character(inquiries$cnt_guests), "10+"
)
inquiries$cnt_guests_group <- ordered(
  inquiries$cnt_guests_group, 
  levels = c("1","2","3","4","5","6","7","8","9","10+")
)

inquiries$days_inquiry_stay_group = ifelse(
  inquiries$days_inquiry_stay <= 200, paste("~", as.integer(inquiries$days_inquiry_stay/10) * 10, sep=""), "201+"
)
inquiries$days_inquiry_stay_group[inquiries$days_inquiry_stay_group == "~0"] = "~10"
inquiries$days_inquiry_stay_group <- ordered(
  inquiries$days_inquiry_stay_group, 
  levels = c("~10","~20","~30","~40","~50","~60","~70","~80","~90","~100",
             "~110","~120","~130","~140","~150",
             "~160","~170","~180","~190","~200","201+")
)

inquiries$cnt_listing_reviews_group = ifelse(
  inquiries$cnt_listing_reviews <= 150, paste("~", as.integer(inquiries$cnt_listing_reviews/10) * 10, sep=""), "151+"
)
inquiries$cnt_listing_reviews_group[inquiries$cnt_listing_reviews_group == "~0"] = "~10"
inquiries$cnt_listing_reviews_group <- ordered(
  inquiries$cnt_listing_reviews_group, 
  levels = c("~10","~20","~30","~40","~50","~60","~70","~80","~90","~100",
             "~110","~120","~130","~140","~150","151+")
)

listings2 <- inquiries %>%
  group_by(id_listing_anon) %>%
  summarise(
    inquiries = n(),
    inquiried = TRUE,
    books = sum(booked),
    booked = ifelse(sum(booked) == 0, FALSE, TRUE)
  )
listings <- listings %>%
  left_join(listings2, by = "id_listing_anon") %>%
  mutate(
    inquiries = ifelse(is.na(inquiries), 0, inquiries),
    inquiried = ifelse(is.na(inquiried), FALSE, inquiried),
    books = ifelse(is.na(books), 0, books),
    booked = ifelse((is.na(books) | books == 0), "Not Booked", "Booked")
  )

users2 <- inquiries %>%
  group_by(id_guest_anon) %>%
  summarise(
    inquiries = n(),
    inquiried = TRUE,
    books = sum(booked),
    booked = ifelse(sum(booked) == 0, FALSE, TRUE)
  ) %>%
  mutate(
    id_user_anon = id_guest_anon
  )
guests <- users %>%
  inner_join(users2, by = "id_user_anon") %>%
  mutate(
    inquiries = ifelse(is.na(inquiries), 0, inquiries),
    inquiried = ifelse(is.na(inquiried), FALSE, inquiried),
    books = books,
    booked = ifelse(books == 0, "Not Booked", "Booked")
  )
```



Inquiries
=======================================================================

Column {.sidebar}  {data-width=200}
-----------------------------------------------------------------------

```{r}
# selectInput('xcol', label = 'X Variable', choices = names(iris))
#     
# selectInput('ycol', label = 'Y Variable', choices = names(iris),
#                 selected = names(iris)[[2]])
#     
# numericInput('clusters', label = 'Cluster count', 3,
#               min = 1, max = 9)
# 
# sliderInput("bw_adjust", label = "Bandwidth adjustment:",
#             min = 0.2, max = 2, value = 1, step = 0.2)
```

In a decade, Airbnb grows to earn a user base of over 150 million covering more than 65000 cities, and a total of 7 million listings in more than 191 countries (Jaleesa, & Bustamante, 2019). This company earns its market share in the hospitality industry by offering opportunities for homeowners (hosts) to make more money from unused spaces, and travelers (guests) to get different types of accommodation wherever they travel to.

The data contains Airbnb inquiries from guest to host, which initialized between Jan 1st 2016 and Jun 30th 2016 (UTC), whose check-in dates were between Jan 1st 2016 and May 1st 2018, and whose hosts' properties were located in Rio de Janeiro, Brazil.

This dashboard shows information around inquiries. There are 15157 inquiries with 7296 successful bookings. 

From the plots we can see that most inquiries were sent to entire home/apartment listings where more guests can be hosted; guests from foreign countries tended to write more in their first message to host; Jan 2016 had slightly more inquiries than other months; most inquiries were asking about accommendation in Aug 2016 (when the summer Olympic was hosted); and most inquiries were from Brazil guest users.


Column {data-width=270}
-----------------------------------------------------------------------

### TOTAL # OF INQUIRIES

```{r}
valueBox(
  value = paste(nrow(inquiries)),
  icon = "fa-area-chart"
)
```

### # Inquiries by Count of Guest and Room Type

```{r}
mu13 <- ddply(inquiries, "room_type", summarise, grp.mean = mean(cnt_guests))
d13 <- inquiries %>%
  group_by(cnt_guests_group, room_type) %>%
  summarise(initialed = n())
ggplotly(
  ggplot(d13, aes(x = cnt_guests_group, y = initialed, fill = room_type)) +
    geom_bar(stat = "identity", alpha = 0.5) +
    facet_grid(room_type ~ .) +
    scale_color_manual(values = c("#999999", "#E69F00", "#56B4E9")) +
    scale_fill_manual(values = c("#999999", "#E69F00", "#56B4E9")) +
    geom_vline(
      data = mu13, linetype = "dashed",
      aes(xintercept = grp.mean, color = room_type)
    ) +
    labs(x = "# Guests in Individual Inquiries", y = "") +
    theme(
      panel.background = element_blank(),
      legend.position = "none"
    )
)
```

### First Message Length by Guest Origin

```{r}
ggplotly(
  ggplot(inquiries, aes(x = guest_origin, y = cnt_first_message_length, fill = guest_origin)) + 
    geom_boxplot() +
    labs(
      x = "Guest Origin",
      y = "# Characaters in 1st Message Guest Sent to Host"
    ) + 
    ylim(c(0, 750)) +
    guides(fill = guide_legend(title = "Guest Origin")) +
    theme(
      panel.background = element_blank(),
      legend.position = "none"
    ) +
    coord_flip()
)
```

Column
-----------------------------------------------------------------------

### # Initialed, Accpted And Booked Inquiries by Inquiry Month

```{r}
d11 <- inquiries %>%
  mutate(
    month = format(ds_interaction,"%Y-%m")
  ) %>%
  group_by(month) %>%
  summarise(
    initialed = n(),
    accepted = sum(accepted),
    booked = sum(booked)
  )
d11 <- gather(d11, inquiry_type, count, initialed:booked, factor_key=TRUE)
ggplotly(
  ggplot(
    data = d11, 
    aes(x = month, y = count, fill = inquiry_type)
  ) +
    geom_bar(
      stat = "identity", color = "black", 
      position = position_dodge()
    ) +  
    labs(
      x = "Month of Inquiry Initialized",
      y = "Count of Inquiries"
    ) +
    scale_fill_brewer(palette = "Blues") +
    guides(fill = guide_legend(title = "")) +
    theme(panel.background = element_blank())
)
```

### # Initialed, Accpted And Booked Inquiries by Check-in Month

```{r}
d15 <- inquiries %>%
  mutate(
    month = format(ds_checkin,"%Y-%m")
  ) %>%
  group_by(month) %>%
  summarise(
    initialed = n(),
    accepted = sum(accepted),
    booked = sum(booked)
  )
d15 <- gather(d15, inquiry_type, count, initialed:booked, factor_key=TRUE)
ggplotly(
  ggplot(
    data = d15, 
    aes(x = month, y = count, fill = inquiry_type)
  ) +
    geom_bar(stat = "identity", color = "black", position = position_dodge()) +  
    labs(
      x = "Month of Check-in",
      y = "Count of Inquiries"
    ) +
    scale_fill_brewer(palette = "Reds") +
    guides(fill = guide_legend(title = "")) +
    theme(
      axis.text.x = element_text(angle = 40),
      panel.background = element_blank()
    )
)
```

### # Initialed, Accpted And Booked Inquiries by Guest Origin

```{r}
d12 <- inquiries %>%
  group_by(guest_country) %>%
  summarise(
    initialed = n(),
    accepted = sum(accepted),
    booked = sum(booked)
  ) %>%
  arrange(desc(initialed)) %>%
  top_n(15)
lvl = d12$guest_country
d12 <- gather(d12, inquiry_type, count, initialed:booked, factor_key=TRUE)
ggplotly(
  ggplot(
    data = d12, 
    aes(x = factor(guest_country, levels = lvl), y = count, fill = inquiry_type)
  ) +
    geom_bar(stat = "identity", color = "black", position = position_dodge()) +  
    labs(
      x = "Guest Origin",
      y = "Count of Inquiries"
    ) +
    scale_fill_brewer(palette = "Greens") +
    guides(fill = guide_legend(title = "")) +
    theme(panel.background = element_blank()) 
)
```


Inquiry Effectiveness
=======================================================================

Column {.sidebar}  {data-width=260}
-----------------------------------------------------------------------

Airbnb earns its market share by offering opportunities for homeowners (hosts) to make more money from unused spaces, and travelers (guests) to get different types of accommodation wherever they travel to.

A guest can choose to book the listing directly if the host accepts instant booking ('instant book'), or request a booking and awaits approval from the host ('book it'), or contact the host to inquire about the listing ('contact me').  Efficiency of the match between hosts and guests is critical to the profitability of Airbnb due to its commission-based business model.

The data contains Airbnb inquiries from guest to host, which initialized between Jan 1st 2016 and Jun 30th 2016 (UTC), whose check-in dates were between Jan 1st 2016 and May 1st 2018, and whose hosts' properties were located in Rio de Janeiro, Brazil.

This dashboard shows information around inquiry effectiveness, i.e., how effectively did inquiries turn into final bookings, what might had affected inquiry effectiveness. Of all 15157 inquiries, 9964 were accepted by hosts and 7296 were booked by guests, which resulted in an overall 65.7% accept rate and a 48.1% book rate.

Accept Rate = # accepted inquiries / # initialized inquiries

Book Rate = # booked inquiries / # initialized inquiries

From the plots we can see that booked inquiries had much shorter time between inquiry and acceptance, as well as more communication between guests and hosts; contact me channel had a very low book rate despite having similar accept rate than book it channel; inquiries from foreign guests were more likely to be accepted and booked than those from domestic guest users; the more guests an inquiry was for, the less probability to be acceptd and booked; the longer days prior to check-in date, the less likely the inquiry would be accepted and booked.

Column {data-width=300}
-----------------------------------------------------------------------

### Inquires accepted by hosts, with a acceptance rate of 65.7%

```{r}
valueBox(
  value = paste(sum(inquiries$accepted)),
  icon = "fa-area-chart"
)
```

### Inquiried booked by guests, with a book rate of 48.1%

```{r}
valueBox(
  value = paste(sum(inquiries$booked)),
  icon = "fa-area-chart"
)
```

### Minutes between Inquiry and Acceptance by Final Booking Status (Accepted Inquiries Only)

```{r}
ggplotly(
  ggplot(inquiries[inquiries$accepted == 1,], aes(x = factor(booked), y = mins_inquiry_accept, fill = factor(booked))) + 
    geom_boxplot() +
    ylim(c(0, 500)) +
    scale_fill_manual(values = c("#999999", "#E69F00")) +
    labs(
      x = "Booking Status",
      y = "Minutes between Inquiry and Acceptance"
    ) +
    scale_x_discrete(
      breaks = c("1", "0"),
      labels = c("Booked", "Not Booked")
    ) +
    theme(
      panel.background = element_blank(),
      legend.position = "none"
    ) 
)
```

### # Messages between Guest And Host by Final Booking Status

```{r}
ggplotly(
  ggplot(inquiries, aes(x = factor(booked), y = cnt_messages, fill = factor(booked))) + 
    geom_boxplot() +
    ylim(c(0, 50)) +
    scale_fill_manual(values = c("#999999", "#E69F00")) +
    labs(
      x = "Booking Status",
      y = "Message Count between Guest and Host"
    ) +
    scale_x_discrete(
      breaks = c("1", "0"),
      labels = c("Booked", "Not Booked")
    ) +
    theme(
      panel.background = element_blank(),
      legend.position = "none"
    )
)
```


Column
-------------------------------------------------------

### Inquiry Effectiveness by Contact Channel And Guest Origin

```{r}
d21 <- inquiries %>%
  group_by(contact_channel, guest_origin) %>%
  summarise(
    accept_rate = round(sum(accepted) / n(), 2),
    book_rate = round(sum(booked) / n(), 2)
  ) 
d21 <- gather(d21, metrics, rate, accept_rate:book_rate, factor_key=TRUE)

ggplotly(
  ggplot(d21, aes(x = guest_origin, y = rate, fill = metrics)) +
    geom_bar(stat = "identity", position = position_dodge()) +
    facet_wrap(contact_channel ~ .) +
    labs(x = "", y = "Accept & Book Rates") +
    guides(fill = guide_legend(title = "")) +
    theme(panel.background = element_blank())
)

```

### Inquiry Effectiveness by Number of Guests of An Inquiry

```{r}
d22 <- inquiries %>%
  group_by(cnt_guests_group) %>%
  summarise(
    accept_rate = round(sum(accepted) / n(), 2),
    book_rate = round(sum(booked) / n(), 2)
  ) 
d22 <- gather(d22, metrics, rate, accept_rate:book_rate, factor_key=TRUE)
ggplotly(
  ggplot(d22, aes(x = factor(cnt_guests_group), y = rate, group = metrics)) + 
    geom_line(aes(linetype = metrics, color = metrics)) +
    geom_point(aes(shape = metrics, color = metrics)) +
    labs(
      x = "Number of Guests In Individual Inquiries",
      y = "Accept & Book Rates"
    ) +
    guides(fill = guide_legend(title = "")) +
    theme(panel.background = element_blank())
)
```

### Inquiry Effectiveness by Number of Days of Inquiry Prior to Check-in

```{r}
d24 <- inquiries %>%
  group_by(days_inquiry_stay_group) %>%
  summarise(
    accept_rate = round(sum(accepted) / n(), 2),
    book_rate = round(sum(booked) / n(), 2)
  ) 
d24 <- gather(d24, metrics, rate, accept_rate:book_rate, factor_key=TRUE)
ggplotly(
  ggplot(d24, aes(x = factor(days_inquiry_stay_group), y = rate, group = metrics)) + 
    geom_line(aes(linetype = metrics, color = metrics)) +
    geom_point(aes(shape = metrics, color = metrics)) +
    labs(
      x = "Number of Days of Inquiry Prior to Check-in",
      y = "Accept & Book Rates"
    ) +
    guides(fill = guide_legend(title = "")) +
    theme(
      axis.text.x = element_text(angle = 40),
      panel.background = element_blank()
    ) 
)
```


Hosts & Listings
=======================================================================

Column {.sidebar}  {data-width=200}
-----------------------------------------------------------------------

In a decade, Airbnb grows to earn a user base of over 150 million covering more than 65000 cities, and a total of 7 million listings in more than 191 countries (Jaleesa, & Bustamante, 2019). This company earns its market share in the hospitality industry by offering opportunities for homeowners (hosts) to make more money from unused spaces, and travelers (guests) to get different types of accommodation wherever they travel to.

The data contains Airbnb listing information locating in Rio de Janeiro, that were active between Jan 1st 2016 and Jun 30th 2016, as well as inquiries from guest to the listing owners.

This dashboard shows information around listings. There were 13038 listings, 6315 of them had been inquiried about by potential guests and 3248 of these listings were successfully booked during the 1st half of 2016.

From the plots we can see that most active listings in Rio were entire home/apartment; a large number of listings did not even get a single inquiry while a few reveiced more than 10 inquiries; most active listings located in Copacabana neighborhood; the more reviews a listing had, the more likely an inquiry to that listing would be accepted and booked.

Column {data-width=200}
-----------------------------------------------------------------------

### TOTAL # OF LISTINGS

```{r}
valueBox(
  value = paste(nrow(listings)),
  icon = "fa-area-chart"
)
```

### TOTAL # OF INQUIRIED LISTINGS

```{r}
valueBox(
  value = paste(sum(listings$inquiried)),
  icon = "fa-area-chart"
)
```

### TOTAL # OF BOOKED LISTINGS

```{r}
valueBox(
  value = paste(nrow(listings[listings$booked == "Booked",])),
  icon = "fa-area-chart"
)
```

### # Active Listings by Room Type

```{r}
d31 <- listings %>%
  group_by(room_type) %>%
  summarise(
    n_listings = n(),
    inquiried = sum(inquiried),
    booked = sum(ifelse(booked == "Booked", 1, 0))
  )
d31 <- gather(d31, book_status, count, n_listings:booked, factor_key=TRUE)
ggplotly(
  ggplot(d31, aes(x = book_status, y = count, fill = book_status)) +
    geom_bar(stat = "identity") +
    facet_grid(room_type ~ .) +
    scale_fill_brewer(palette = "Dark2") +
    labs(x = "", y = "# Active Listings") +
    theme(
      panel.background = element_blank(),
      legend.position = "none"
    ) +
    coord_flip()
)
```

Column
-----------------------------------------------------------------------

### # Inquiries Received by Each Listing
```{r}
ggplotly(
  ggplot(listings, aes(x = inquiries, fill = room_type)) +
    geom_histogram(stat = "count") +
    scale_fill_manual(values = c("#999999", "#E69F00", "#56B4E9")) +
    labs(x = "# Inquiries", y = "# Listings") +
    guides(fill = guide_legend(title = "Room Type")) +
    theme(panel.background = element_blank())
)
```

### # Initialized, Accepted and Booked Inquiries by Listing Neighborhood

```{r}
d33 <- inquiries %>%
  group_by(listing_neighborhood) %>%
  summarise(
    initialed = n(),
    accepted = sum(accepted),
    booked = sum(booked)
  ) %>%
  arrange(desc(initialed))
lvl = d33$listing_neighborhood
d33 <- gather(d33, inquiry_type, count, initialed:booked, factor_key=TRUE)
ggplotly(
  ggplot(data = d33, aes(x = factor(listing_neighborhood, levels = lvl), y = count, fill = inquiry_type)) +
    geom_bar(stat = "identity", color = "black", position = position_dodge()) +
    labs(x = "Listing Neighborhood", y = "# Inquiries") +
    scale_fill_brewer(palette = "Greens") +
    guides(fill = guide_legend(title = "")) +
    theme(
      axis.text.x = element_text(angle = 30),
      panel.background = element_blank()
    ) 
)
```

### Inquiry Effectiveness by Number of Reviews of Inquiried Listing

```{r}
d34 <- inquiries %>%
  group_by(cnt_listing_reviews_group) %>%
  summarise(
    accept_rate = round(sum(accepted) / n(), 2),
    book_rate = round(sum(booked) / n(), 2)
  ) 
d34 <- gather(d34, metrics, rate, accept_rate:book_rate, factor_key=TRUE)
ggplotly(
  ggplot(d34, aes(x = factor(cnt_listing_reviews_group), y = rate, group = metrics)) +
    geom_line(aes(linetype = metrics, color = metrics)) +
    geom_point(aes(shape = metrics, color = metrics)) +
    labs(
      x = "Number of Days of Inquiry Prior to Check-in",
      y = "Accept & Book Rates"
    ) +
    guides(color = guide_legend(title = "")) +
    theme(
      axis.text.x = element_text(angle = 30),
      panel.background = element_blank()
    ) 
)
```

Guests
=======================================================================

Column {.sidebar}  {data-width=200}
-----------------------------------------------------------------------

In a decade, Airbnb grows to earn a user base of over 150 million covering more than 65000 cities, and a total of 7 million listings in more than 191 countries (Jaleesa, & Bustamante, 2019). This company earns its market share in the hospitality industry by offering opportunities for homeowners (hosts) to make more money from unused spaces, and travelers (guests) to get different types of accommodation wherever they travel to.

The data contains Airbnb inquiries from guest to host, which initialized between Jan 1st 2016 and Jun 30th 2016 (UTC), whose check-in dates were between Jan 1st 2016 and May 1st 2018, and whose hosts' properties were located in Rio de Janeiro, Brazil.

This dashboard shows information around guest users. There were 13285 guest uesrs who initialized inquiries and 6920 of them successfully booked listing(s).

From the plots we can see that there were slightly more new guest users than past bookers; a large number of guest users came from Brazil; most guest user only initialized one inquiry.

Column {data-width=200}
-----------------------------------------------------------------------

### TOTAL # OF GUEST USERS

```{r}
valueBox(
  value = paste(nrow(guests)),
  icon = "fa-area-chart"
)
```

### TOTAL # OF GUEST USERS WHO BOOKED

```{r}
valueBox(
  value = paste(nrow(guests[guests$booked == "Booked",])),
  icon = "fa-area-chart"
)
```

### # Past Guest Users vs. New Guest Users

```{r}
d44 <- inquiries %>%
  select(id_guest_anon, guest_user_stage) %>%
  filter(guest_user_stage == "new") %>%
  distinct_all()
d44 <- guests %>%
  select(id_guest_anon) %>%
  left_join(d44, by = "id_guest_anon") %>%
  mutate(guest_user_stage = ifelse(is.na(guest_user_stage), "Past", "New"))
ggplotly(
  ggplot(d44, aes(x = guest_user_stage)) +
    geom_histogram(stat = "count", fill = "indianred3") +
    labs(x = "Guest User Stage", y = "# Guest Users")
)
```

Column 
-----------------------------------------------------------------------

### # Guest Users by Country

```{r}
d41 <- guests %>%
  group_by(country) %>%
  summarise(cnt = n()) %>%
  arrange(desc(cnt)) %>%
  top_n(20)
lvl <- as.character(d41$country)
d41 <- guests %>%
  mutate(country = ifelse(as.character(country) %in% lvl, as.character(country), 'Other')) %>%
  group_by(country, booked) %>%
  summarise(cnt = n())
ggplotly(
  ggplot(d41, aes(x = factor(country, levels = c(lvl, 'Other')), y = cnt, fill = booked)) +
    geom_bar(stat = "identity") +
    labs(x = "Guest User Origin", y = "# Guest Users") +
    guides(fill = guide_legend(title = "Book Status")) +
    theme(panel.background = element_blank()) 
)
```

### # Inquiries vs Books of Guest Users

```{r}
d43 <- guests %>%
  group_by(inquiries, books) %>%
  summarise(guest_user_count = n())
ggplotly(
  ggplot(guests, aes(x = factor(inquiries), fill = booked)) +
    geom_histogram(stat = "count") +
    guides(fill = guide_legend(title = "Book Status")) +
    labs(x = "# Inquiries", y = "# Guest Users") +
    theme(panel.background = element_blank()) 
)
```