Revenue Dashboard - By State

library(flexdashboard)
library(flexdashboard)
library(tidyverse)
library(tidyquant)
library(plotly)
library(odbc)
library(RSQLite)
library(lubridate)
library(DT)

con <- dbConnect(RSQLite::SQLite(),"../Documents/DS4B_102_R_Shiny_Apps_1/00_data/bikes_database.db")
# dbListTables(con)
bikeshops_tbl <- tbl(con, "bikeshops")
bikes_tbl <- tbl(con, "bikes")
orderlines_tbl <- tbl(con, "orderlines")

processed_data_tbl <- orderlines_tbl %>% 
    left_join(bikeshops_tbl, by = c("customer.id" = "bikeshop.id")) %>% 
    left_join(bikes_tbl, by = c("product.id" = "bike.id")) %>%
    mutate(extended_price = quantity * price) %>% 
    collect()

# Performing steps that only be performed once data is in R
processed_data_tbl <- processed_data_tbl %>% 
    mutate(order.date = ymd(order.date)) %>% 
    separate(location, into = c("city", "state"), sep = ", ")

dbDisconnect(con)

---

By State

geo_plot_tbl <- processed_data_tbl %>% 
    group_by(state) %>% 
    summarize(total_revenue = sum(extended_price)) %>% 
    ungroup() %>% 
    mutate(label_text = str_glue("State: {state}
                                 Revenue: {scales::dollar(total_revenue)}"))

## `summarise()` ungrouping output (override with `.groups` argument)

geo_plot_tbl %>% 
    plot_geo(locationmode = "USA-states") %>% 
    add_trace(z         = ~total_revenue, 
              locations = ~state, 
              color     = ~total_revenue,
              text      = ~ label_text,
              colors    = "Blues"
              ) %>%
    layout(
        geo = list(
            scope = "usa",
            projection = list(type = "albers usa"),
            showlakes = TRUE,
            lakecolor = toRGB("white")
        )
    )

## Warning: `arrange_()` is deprecated as of dplyr 0.7.0.
## Please use `arrange()` instead.
## See vignette('programming') for more help
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_warnings()` to see where this warning was generated.