Overview & Getting Started

The aim of this script is to:

  • Provide the number of reviews posted to Intuit’s Blind page for the month.
  • Provide a breakdown of the review postings by employment status (current vs. former).
  • Report Intuit’s scores on key categories of employee experience and show trends over time and comparisons to competitors.
  • Show month over month trends in key category scores to compare time trends
knitr::opts_chunk$set(warning=FALSE, message=FALSE)

# Load Libraries
libraries <- c("tidyverse",
               "stringr",
               "lubridate",
               "ggplot2",
               "ggrepel",
               "gt",
               "htmltools", 
               "paletteer", 
               "scales",
               "sparkline"
               
)
lapply(libraries, require, character.only = TRUE)

## Set Paths
wd <- getwd()
output_path <- "/03-2022/"  #you want to write this to the monthly file
dir.create(file.path(wd, output_path))


# Set Key-Parameters
#### Dates ####
## Update the start of month date & start of next month date
month_start_reviews <- as.Date("2022-03-01")
col_month_start <- paste0("T", month_start_reviews)
month_start_next_month <- as.Date("2022-04-01") 
month_previous_for_MOM_trend <- as.Date("2022-02-01") 
col_month_prev <- paste0("T", month_previous_for_MOM_trend)
one_year_ago <- as.Date("2021-03-01")

#### Visual Elements ####
## Set the appropriate months for the line graphs
MoM_ratings_trend_graphs_title <- "Monthly Blind Ratings during the Last Year" 
color_scheme_categories <- c("Career Growth" = "#003f5c", 
     "Company Culture" = "#58508d", 
     "Compensation/Benefits" = "#bc5090", 
     "Management" = "#ff6361", 
     "Work-Life Balance" = "#ffa600", 
     "Overall" = "#686868")

# Set Key Functions
label_score_categories <- function(x) { 
  x %>%
  mutate(Category = case_when(
    Category == "overall_rating" ~ "Overall",
    Category == "career_growth_rating" ~ "Career Growth",
    Category == "wlb_rating" ~ "Work-Life Balance", 
    Category == "compensation_rating" ~ "Compensation/Benefits",
    Category == "company_culture_rating" ~ "Company Culture", 
    Category == "management_rating" ~ "Management")
    )
  }    

compute_MOM_trend_for_table <- function(x) { 
  x %>% 
  mutate(Change = Current - Previous) %>% # current month - last month
  select(Category, Rating = Current, Change) 
}

dev.new(family = "Arial")
## Import Data
temp_rds <- list.files(pattern="*.rds")
list2env(
  lapply(setNames(temp_rds, make.names(gsub("*.rds$", "", temp_rds))), 
         readRDS), envir = .GlobalEnv)

Overview of Data

View the chunk below and make sure that the current month falls in the Date_Obj field.

summary(intuit_reviews)
##      date              Date_obj              Month             Day       
##  Length:1037        Min.   :2020-05-28   Min.   : 1.000   Min.   : 1.00  
##  Class :character   1st Qu.:2021-02-05   1st Qu.: 4.000   1st Qu.: 8.00  
##  Mode  :character   Median :2021-09-17   Median : 6.000   Median :15.00  
##                     Mean   :2021-09-24   Mean   : 6.487   Mean   :15.47  
##                     3rd Qu.:2022-05-03   3rd Qu.: 9.000   3rd Qu.:23.00  
##                     Max.   :2023-05-28   Max.   :12.000   Max.   :31.00  
##       Year       emp_status         job_title         overall_rating 
##  Min.   :2020   Length:1037        Length:1037        Min.   :1.000  
##  1st Qu.:2021   Class :character   Class :character   1st Qu.:3.000  
##  Median :2021   Mode  :character   Mode  :character   Median :4.000  
##  Mean   :2021                                         Mean   :3.857  
##  3rd Qu.:2022                                         3rd Qu.:5.000  
##  Max.   :2023                                         Max.   :5.000  
##  career_growth_rating   wlb_rating    compensation_rating
##  Min.   :1.000        Min.   :1.000   Min.   :1.000      
##  1st Qu.:3.000        1st Qu.:4.000   1st Qu.:3.000      
##  Median :3.000        Median :4.000   Median :4.000      
##  Mean   :3.425        Mean   :4.095   Mean   :3.726      
##  3rd Qu.:4.000        3rd Qu.:5.000   3rd Qu.:4.000      
##  Max.   :5.000        Max.   :5.000   Max.   :5.000      
##  company_culture_rating management_rating    title          
##  Min.   :1.000          Min.   :1.000     Length:1037       
##  1st Qu.:3.000          1st Qu.:3.000     Class :character  
##  Median :4.000          Median :3.000     Mode  :character  
##  Mean   :3.818          Mean   :3.266                       
##  3rd Qu.:5.000          3rd Qu.:4.000                       
##  Max.   :5.000          Max.   :5.000

Former vs Current Employees

Check to see what is the breakdown for employment status in this month’s reviews. You will need to specify this on the slides (e.g., reviews from # of current employees).

knitr::kable(intuit_reviews %>% 
  filter(Month == month(month_start_reviews) & Year == year(month_start_reviews)) %>%
  group_by(emp_status) %>% 
  count() %>% 
  ungroup() %>% 
  mutate(`%` = round((n/sum(n)) * 100, digits =0)) %>% 
    rename(`Employment Status` = "emp_status")
)
Employment Status n %
Current Employee 34 100

Build Ratings Table

Compute Last Month’s Ratings for Competitors

capture_competitors_monthly_ratings <- function(x, company) { 
  x %>% 
  filter(Date_obj >= month_start_reviews &
         Date_obj < month_start_next_month
         ) %>%
  summarize(across(ends_with("_rating"), ~mean(.x, na.rm=TRUE))) %>%
   pivot_longer(cols = ends_with("_rating"),
               names_to = "Category",
               values_to = company
               )
  }

amazon_ratings_last_month <- capture_competitors_monthly_ratings(
  amazon, "Amazon") 

salesforce_ratings_last_month <- capture_competitors_monthly_ratings(
  salesforce, "Salesforce") 

google_ratings_last_month <- capture_competitors_monthly_ratings(
  google, "Google")
  
facebook_ratings_last_month <- capture_competitors_monthly_ratings(
  facebook, "Facebook")

microsoft_ratings_last_month <- capture_competitors_monthly_ratings(
  microsoft, "Microsoft")

competitors_list <- list(google_ratings_last_month, 
                         facebook_ratings_last_month,
                         amazon_ratings_last_month,
                         microsoft_ratings_last_month,
                         salesforce_ratings_last_month
                         )
  
competitors_ratings <- competitors_list %>% 
  reduce(inner_join, by= "Category")  %>%
  mutate(across(where(is.numeric), ~round(.x, digits = 1))) %>% 
  label_score_categories()

knitr::kable(competitors_ratings,
             caption = "Summary of Last Month's Ratings for Competitors")
Summary of Last Month’s Ratings for Competitors
Category Google Facebook Amazon Microsoft Salesforce
Overall 4.3 3.8 3.5 4.0 3.9
Career Growth 3.5 4.0 3.6 3.5 3.5
Work-Life Balance 4.5 3.3 2.9 4.2 4.4
Compensation/Benefits 4.0 4.1 3.1 3.0 3.6
Company Culture 4.3 3.5 3.0 4.1 4.2
Management 3.8 3.3 3.1 3.7 3.5

Pairwise T-Tests to compare Intuit’s scores to Competitors

Compute t-test comparisons for context. We are still working out whether to use hypothesis tests on these data.

  • If Intuit reviews are low for month then do not include in deck.
  • If Intuit reviews > 30 then consider using, but include note on review counts in deck.
ratings_ttest %>% 
  filter(p.adj.signif != "ns") %>% 
  group_by(Category) %>% 
  summarize(sig_competitors = paste0(group2, collapse = ", ")
            ) %>% 
  ungroup()
## # A tibble: 5 × 2
##   Category              sig_competitors            
##   <chr>                 <chr>                      
## 1 Career Growth         Facebook, Microsoft        
## 2 Company Culture       Amazon                     
## 3 Compensation/Benefits Facebook, Google, Microsoft
## 4 Overall               Google                     
## 5 Work-Life Balance     Amazon, Facebook, Microsoft
# Identify Significant differences in table
extract_competitors_sig_diff <- function(category) { 
  ratings_ttest %>% 
  filter(p.adj.signif != "ns" & Category == category) %>% 
  mutate(group2 = paste0("mean_", group2)) %>%
  pull(group2)
  }
career_growth_sig <- extract_competitors_sig_diff("Career Growth")

company_culture_sig <- extract_competitors_sig_diff("Company Culture")

compensation_sig <- extract_competitors_sig_diff("Compensation/Benefits")

work_life_balance_sig <- extract_competitors_sig_diff("Work-Life Balance")

overall_sig <- extract_competitors_sig_diff("Overall")

# Label Table with Company Name & Sample Size
intuit_title <- ratings_ttest %>% 
  distinct(n1) %>% 
  mutate(n1 = paste0("Intuit (N = ", n1, ")")) %>% 
  pull()

title_naming_competitors <- function(company) { 
  ratings_ttest %>% 
  filter(group2 == company) %>%
  distinct(n2) %>% 
  mutate(n2 = paste0(company, " (N = ", n2, ")")) %>% 
  pull()
}
amazon_title <- title_naming_competitors("Amazon")
facebook_title <- title_naming_competitors("Facebook")
google_title <- title_naming_competitors("Google")
microsoft_title <- title_naming_competitors("Microsoft")
salesforce_title <- title_naming_competitors("Salesforce")


summary_stats_by_category_company %>% 
  pivot_wider(names_from = "Company", 
              values_from = c(n, mean, sd)
              ) %>% 
  relocate(ends_with("_Intuit"), .after = "Category") %>% 
  gt() %>% 
  tab_style(style = list(
            cell_text(weight = "bold")
            
          ),
          locations = cells_body(
            columns = one_of(career_growth_sig),
            
            rows = Category == "Career Growth"
          )
        ) %>%
   tab_style(style = list(
            cell_text(weight = "bold")
            
          ),
          locations = cells_body(
            columns = one_of(company_culture_sig),
            
            rows = Category == "Company Culture"
          )
        ) %>% 
   tab_style(style = list(
            cell_text(weight = "bold")
            
          ),
          locations = cells_body(
            columns = one_of(compensation_sig),
            
            rows = Category == "Compensation/Benefits"
          )
        ) %>% 
  tab_style(style = list(
            cell_text(weight = "bold")
            
          ),
          locations = cells_body(
            columns = one_of(work_life_balance_sig),
            
            rows = Category == "Work-Life Balance"
          )
        ) %>% 
  tab_style(style = list(
            cell_text(weight = "bold")
            
          ),
          locations = cells_body(
            columns = one_of(overall_sig),
            
            rows = Category == "Overall"
          )
        ) %>%  
  tab_style(style = list(
            cell_fill(color = "#D3D3D3")
            
          ),
          locations = cells_body(
            columns = ends_with("_Intuit"),
            rows = everything()
          )
        ) %>%  
  tab_style(
    style = cell_borders(
      sides = "right",
      color = "black",
      weight = px(1.5),
      style = "solid"
    ),
    locations = cells_body(
      columns = ends_with("SD_Intuit"),
      rows = everything()
    )
  ) %>% 
  tab_style(
    style = cell_borders(
      sides = "left",
      color = "black",
      weight = px(1.5),
      style = "solid"
      ),
    locations = cells_body(
    columns = ends_with("Mean_Intuit"),
    rows = everything()
  )) %>%
  tab_spanner(ends_with("Intuit"), label = intuit_title) %>%
  tab_spanner(ends_with("Amazon"), label = amazon_title) %>% 
  tab_spanner(ends_with("Facebook"), label = facebook_title) %>% 
  tab_spanner(ends_with("Google"), label = google_title) %>% 
  tab_spanner(ends_with("Microsoft"), label = microsoft_title) %>% 
  tab_spanner(ends_with("Salesforce"), label = salesforce_title) %>% 
  cols_hide(starts_with("n_")) %>%
  cols_label(
    starts_with("n_") ~ "N",
    starts_with("mean_") ~ "Mean",
    starts_with("sd_") ~ "SD"
  )
Category Intuit (N = 34) Amazon (N = 560) Facebook (N = 187) Google (N = 166) Microsoft (N = 187) Salesforce (N = 57)
Mean SD Mean SD Mean SD Mean SD Mean SD Mean SD
Career Growth 3.2 1.1 3.6 1.1 4.0 1.0 3.5 1.0 4.0 1.0 3.5 1.0
Company Culture 3.9 0.8 3.0 1.2 3.5 1.2 4.3 0.8 3.5 1.2 4.2 0.9
Compensation/Benefits 3.4 1.0 3.1 1.0 4.1 0.9 4.0 0.9 4.1 0.9 3.6 0.9
Management 3.4 1.0 3.1 1.2 3.3 1.2 3.8 1.0 3.3 1.2 3.5 1.1
Overall 3.8 0.8 3.5 1.0 3.8 0.9 4.3 0.7 3.8 0.9 3.9 0.9
Work-Life Balance 4.1 1.0 2.9 1.2 3.3 1.2 4.5 0.8 3.3 1.2 4.4 0.8

Export pairwise comparison t-test as csv files

write_csv(ratings_ttest, 
          paste0(wd, output_path, "ratings_ttest.csv"))

write_csv(summary_stats_by_category_company, 
          paste0(wd, output_path, "ratings_summary_stats.csv"))

Compute Intuit’s MoM Trend by Rating Category

vs_last_month_by_category <- ratings_over_time %>% 
         filter(Time >= month_previous_for_MOM_trend) %>% 
  select(Time, 
         overall_rating,
         career_growth_rating,
         wlb_rating, 
         compensation_rating,
         company_culture_rating, 
         management_rating) %>% 
  pivot_longer(cols = ends_with("_rating"), 
               names_to = "Category",
               values_to = "Average Rating"
               ) %>% 
  mutate(Time = paste0("T", Time), 
         Time = ifelse(Time == col_month_start, "Current", "Previous")
         ) %>% 
  pivot_wider(id_cols = Category, 
              names_from = Time, 
              values_from = `Average Rating`) %>% 
  compute_MOM_trend_for_table() %>% 
  label_score_categories() %>% 
  select(Category, Change)

data_for_table <- intuit_reviews %>% 
  filter(Date_obj >= month_start_reviews & 
           Date_obj < month_start_next_month) %>% 
  select(
         overall_rating,
         career_growth_rating,
         wlb_rating, 
         compensation_rating,
         company_culture_rating, 
         management_rating) %>% 
  summarize(across(ends_with("_rating"), ~mean(.x, na.rm = TRUE))
            ) %>% 
  ungroup() %>% 
  mutate(across(ends_with("_rating"), ~round(.x, digits = 1))) %>%
  pivot_longer(cols = ends_with("_rating"), 
               names_to = "Category",
               values_to = "Rating"
               ) %>% 
  label_score_categories() %>% 
  left_join(., vs_last_month_by_category, by = "Category") %>% 
  left_join(., competitors_ratings, by = "Category") %>% 
  mutate(Google = Rating - Google,
         Facebook = Rating - Facebook, 
         Amazon = Rating - Amazon,
         Microsoft = Rating - Microsoft,
         Salesforce = Rating - Salesforce) %>% 
  mutate(across(c(Google:Salesforce), ~round(.x, digits = 1)))

Create Sparkline One Year Trend

## Create Sparkline Data for Table: 
last_year_month_month_ratings <- ratings_over_time %>% 
  select(Time, 
         overall_rating,
         career_growth_rating,
         wlb_rating, 
         compensation_rating,
         company_culture_rating, 
         management_rating) %>% 
  pivot_longer(cols = ends_with("_rating"), 
               names_to = "Category",
               values_to = "Average Rating"
               ) %>%
  filter(Time >= one_year_ago & 
                  Time < month_start_next_month) %>% 
  label_score_categories() %>%
    arrange(Time) %>% 
    rename(Rating = `Average Rating`)

plot_spark <- function(data){
  data %>% 
    ggplot(aes(x = Time, y = Rating), color = "grey") +
    geom_line(size = 15) +
    theme_void() +
    scale_color_identity() +
    theme(legend.position = "none")
}

# Add SPARKLINE column to data
Ratings_plots <- last_year_month_month_ratings %>% 
  nest(Ratings = c(Time, Rating)) %>% 
  mutate(plot = map(Ratings, plot_spark)) %>% 
  select(-Ratings)

data_for_table <- data_for_table %>% 
  left_join(., Ratings_plots, by = "Category") %>% 
  relocate(plot, .after = "Change")

gt_spark <- function(table_data, plot_col, data_col){
  # save the data extract ahead of time 
  # to be used in our anonymous function below
  data_in = pluck(table_data, "_data", data_col)
  
  text_transform(
    table_data,
    # note the use of {{}} here - this is tidy eval
    # that allows you to indicate specific columns
    locations = cells_body(columns = vars({{plot_col}})),
    fn = function(x){
      sparkline_plot <- map(
        data_in, 
        ~spk_chr(values = .x, chartRangeMin = 0)
        )
      
      map(sparkline_plot, gt::html)
    }
  )
}

Create competitor buttons

## Color & Style buttons based on Intuit's scores relative to competitors
add_button_color_style <- function(company){
      add_color <- if (company == 0) {
        "background: hsl(90, 0%, 81%); color: hsl(180, 0%, 25%);"
      } else if (company > 0) {
        "background: hsl(230, 70%, 90%); color: hsl(230, 45%, 30%);"
      } else if (company < 0) {
        "background: hsl(350, 70%, 90%); color: hsl(350, 45%, 30%);"
      }
      div_out <- htmltools::div(
        style = paste(
          "display: inline-block; padding: 2px 12px; border-radius: 15px; font-weight: 600; font-size: 12px;",
          add_color
          ),
        paste(company)
      )
      
      as.character(div_out) %>% 
        gt::html()
}
# Build and Export Table
data_for_table_styled <- data_for_table %>% 
  mutate(Google = map(Google, add_button_color_style),
         Facebook = map(Facebook, add_button_color_style),
         Amazon = map(Amazon, add_button_color_style),
         Microsoft = map(Microsoft, add_button_color_style),
         Salesforce = map(Salesforce, add_button_color_style)
         )
  
gt(data_for_table_styled) %>% 
    tab_header(
    title = md("Monthly Report on Blind Scalar Reviews")
  ) %>%
    tab_spanner(Change:plot, label = "Trends") %>%
    tab_spanner(Google:Salesforce, label = "Intuit Score vs Competitors") %>% 
  text_transform(
    locations = cells_body(columns = c(Change)),
    fn = function(x){
      
      Change <- as.numeric(x)
      
      choose_logo <-function(x){
if (x >= 0.1){
          gt::html(fontawesome::fa("long-arrow-alt-up", fill = "#2a366f", height = "3em"),
                                   glue::glue("<span style='color:#2a366f;font-face:bold;font-size:12px;text-align:center;'>{x}</span>"))
        } else if (x <= -0.1){
          gt::html(fontawesome::fa("long-arrow-alt-down", fill = "#6f2a36", height = "3em"), glue::glue("<span style='color:#6f2a36;font-face:bold;font-size:12px;text-align:center;'>{x}</span>"))
        } else {
          gt::html(fontawesome::fa("arrows-alt-h", fill = "#404040", height = "3em"), glue::glue("<span style='color:#404040;font-face:bold;font-size:12px;text-align:center;'>{x}</span>"))
        }
        }
      
      map(Change, choose_logo)
    
    }
  ) %>% 
  text_transform(
    locations = cells_body(c(plot)),
    fn = function(x){
      map(Ratings_plots$plot, ggplot_image, height = px(15), aspect_ratio = 4)
    }
  ) %>%
  cols_label(
    Change = "MoM Chg",
    plot = "One Year"
             ) %>%
  cols_align(
    align = c("center"),
    columns = c(Rating, Change, Google, Facebook, Amazon, Microsoft, Salesforce)
  ) %>% 
  tab_options(
    table.font.names = "Arial",
    heading.title.font.size = 24,
    heading.subtitle.font.size = 14,
    column_labels.font.weight = 'bold',
    table.font.size = 12,
    data_row.padding = px(1),
    table.border.bottom.color = "white"
  ) %>%
  gtsave(filename = "blind table.png",
         path = paste0(wd, output_path))