perfect-margin / server.R
phantomas34
feat: Revenue Intelligence tab β€” Phase 2 executed contract value by PM, GC concentration, project pipeline
d1fce9e
Raw History Blame Contribute Delete
92.1 kB
# --- server.R --- function(input, output, session) { data_trigger <- reactiveVal(0) # ── 1. FAST DATA FETCHING (VIA POOL) ────────────────────────────────────── get_data <- reactive({ input$refresh data_trigger() if (!dbExistsTable(db_pool, "monthly_sales")) return(data.frame()) df <- dbGetQuery(db_pool, " SELECT e.primary_name, s.*, EXTRACT(YEAR FROM s.report_date) AS year_num FROM monthly_sales s JOIN estimators e ON s.estimator_id = e.id ") df }) # ── 2. UPLOAD ───────────────────────────────────────────────────────────── observeEvent(input$process_upload, { req(input$file1) prog <- shiny::Progress$new(message = "Processing...", value = 0.2) on.exit(prog$close()) tryCatch({ raw <- read_csv(input$file1$datapath, show_col_types = FALSE) clean <- raw %>% mutate(report_date = ym(MoYear), across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .))), clean_name = case_when( Estimator == "Scott W. Hutchings" ~ "Scott Hutchings", Estimator %in% c("SW", "SH") ~ "Scott Hutchings", TRUE ~ Estimator)) dbExecute(db_pool, "CREATE TABLE IF NOT EXISTS estimators (id SERIAL PRIMARY KEY, primary_name VARCHAR(100) UNIQUE NOT NULL, active BOOLEAN DEFAULT TRUE);") dbExecute(db_pool, "CREATE TABLE IF NOT EXISTS monthly_sales (id SERIAL PRIMARY KEY, estimator_id INTEGER REFERENCES estimators(id), report_date DATE NOT NULL, amt_bid NUMERIC(15,2), qty_bid INTEGER, amt_booked NUMERIC(15,2), qty_booked INTEGER, UNIQUE(estimator_id, report_date));") for (nm in unique(clean$clean_name)) tryCatch(dbExecute(db_pool, "INSERT INTO estimators (primary_name) VALUES ($1) ON CONFLICT DO NOTHING", params = list(nm)), error = function(e) NULL) est_map <- dbGetQuery(db_pool, "SELECT id, primary_name FROM estimators") upload <- clean %>% left_join(est_map, by = c("clean_name" = "primary_name")) %>% select(estimator_id = id, report_date, amt_bid = AmtBid, qty_bid = QtyBid, amt_booked = AmtBooked, qty_booked = QtyBooked) dbWriteTable(db_pool, "staging_sales", upload, overwrite = TRUE, temporary = TRUE) n <- dbExecute(db_pool, " INSERT INTO monthly_sales (estimator_id,report_date,amt_bid,qty_bid,amt_booked,qty_booked) SELECT estimator_id,report_date,amt_bid,qty_bid,amt_booked,qty_booked FROM staging_sales ON CONFLICT (estimator_id, report_date) DO NOTHING;") dbExecute(db_pool, "DROP TABLE IF EXISTS staging_sales") showNotification(paste("Success! Added/Merged", n, "records."), type = "message") data_trigger(data_trigger() + 1) }, error = function(e) showNotification(paste("Error:", e$message), type = "error")) }) # ── FILTERING ───────────────────────────────────────────────────────────── filtered_data <- reactive({ req(input$dateRange) df <- get_data() if (nrow(df) == 0) return(df[0, ]) df <- df %>% filter(report_date >= input$dateRange[1], report_date <= input$dateRange[2]) sel <- input$estimatorSelect if (!is.null(sel) && length(sel) > 0) df <- df %>% filter(primary_name %in% sel) df }) lag_win_rate <- reactive({ df <- filtered_data() lag <- input$win_lag if (nrow(df) == 0 || lag == 0) return(NULL) all_data <- get_data() max_data_date <- max(all_data$report_date, na.rm = TRUE) cutoff_date <- max_data_date %m-% months(lag) bookings_lookup <- df %>% select(primary_name, report_date, amt_booked, qty_booked) %>% rename(book_date = report_date) bid_months <- df %>% select(primary_name, report_date, amt_bid, qty_bid) %>% distinct() %>% filter(report_date <= cutoff_date) if (nrow(bid_months) == 0) return(NULL) result <- bid_months %>% rowwise() %>% mutate( lag_booked_amt = sum(bookings_lookup$amt_booked[bookings_lookup$primary_name == .data$primary_name & bookings_lookup$book_date > report_date & bookings_lookup$book_date <= report_date %m+% months(lag)], na.rm = TRUE), lag_booked_qty = sum(bookings_lookup$qty_booked[bookings_lookup$primary_name == .data$primary_name & bookings_lookup$book_date > report_date & bookings_lookup$book_date <= report_date %m+% months(lag)], na.rm = TRUE) ) %>% ungroup() result }) ceo_scorecard <- reactive({ d <- filtered_data() validate(need(nrow(d) > 0, "No data in selected range.")) monthly <- d %>% mutate(period = floor_date(report_date, "month")) %>% group_by(primary_name, period) %>% summarise( amt_bid = sum(amt_bid, na.rm = TRUE), amt_booked = sum(amt_booked, na.rm = TRUE), qty_bid = sum(qty_bid, na.rm = TRUE), qty_booked = sum(qty_booked, na.rm = TRUE), .groups = "drop" ) base <- monthly %>% group_by(primary_name) %>% summarise( total_bid = sum(amt_bid, na.rm = TRUE), total_booked = sum(amt_booked, na.rm = TRUE), jobs_bid = sum(qty_bid, na.rm = TRUE), jobs_won = sum(qty_booked, na.rm = TRUE), same_month_win_rate = ifelse(total_bid > 0, total_booked / total_bid, NA_real_), avg_job_size = ifelse(jobs_bid > 0, total_bid / jobs_bid, NA_real_), months_active = n(), booked_cv = ifelse(mean(amt_booked, na.rm = TRUE) > 0, sd(amt_booked, na.rm = TRUE) / mean(amt_booked, na.rm = TRUE), NA_real_), .groups = "drop" ) lwr <- lag_win_rate() if (!is.null(lwr) && nrow(lwr) > 0) { lag_summary <- lwr %>% group_by(primary_name) %>% summarise(lag_win_rate = sum(lag_booked_amt, na.rm = TRUE) / sum(amt_bid, na.rm = TRUE), .groups = "drop") base <- base %>% left_join(lag_summary, by = "primary_name") %>% mutate(win_rate_used = coalesce(lag_win_rate, same_month_win_rate)) } else { base <- base %>% mutate(lag_win_rate = NA_real_, win_rate_used = same_month_win_rate) } safe_rescale <- function(x) { if (all(is.na(x))) return(rep(0.5, length(x))) rng <- range(x, na.rm = TRUE) if (isTRUE(all.equal(rng[1], rng[2]))) return(rep(0.5, length(x))) (x - rng[1]) / (rng[2] - rng[1]) } w_raw <- c( win = input$ceo_w_win %||% 35, eff = input$ceo_w_eff %||% 20, stab = input$ceo_w_stab %||% 20, out = input$ceo_w_out %||% 25 ) w_sum <- sum(w_raw, na.rm = TRUE) if (is.na(w_sum) || w_sum <= 0) { w <- c(win = 0.35, eff = 0.20, stab = 0.20, out = 0.25) } else { w <- w_raw / w_sum } risk_win_cut <- (input$ceo_risk_win %||% 12) / 100 risk_cv_cut <- input$ceo_risk_cv %||% 1.25 base %>% mutate( s_win = safe_rescale(win_rate_used), s_efficiency = safe_rescale(ifelse(total_bid > 0, total_booked / total_bid, NA_real_)), s_stability = 1 - safe_rescale(booked_cv), s_output = safe_rescale(total_booked), composite_score = 100 * (w["win"] * s_win + w["eff"] * s_efficiency + w["stab"] * s_stability + w["out"] * s_output), risk_flag = case_when( is.na(win_rate_used) ~ "Watch", win_rate_used < risk_win_cut | booked_cv > risk_cv_cut ~ "At Risk", win_rate_used < (risk_win_cut + 0.06) | booked_cv > (risk_cv_cut * 0.8) ~ "Watch", TRUE ~ "Healthy" ) ) %>% arrange(desc(composite_score)) }) ceo_forecast <- reactive({ d <- filtered_data() validate(need(nrow(d) > 0, "No data in selected range.")) monthly <- d %>% mutate(period = floor_date(report_date, "month")) %>% group_by(primary_name, period) %>% summarise( amt_bid = sum(amt_bid, na.rm = TRUE), amt_booked = sum(amt_booked, na.rm = TRUE), .groups = "drop" ) estimators <- sort(unique(monthly$primary_name)) out <- vector("list", length(estimators)) for (i in seq_along(estimators)) { est <- estimators[i] e <- monthly %>% filter(primary_name == est) %>% arrange(period) if (nrow(e) < 10) next e <- e %>% mutate( t = row_number(), month_num = month(period), lag_booked = lag(amt_booked, 1), lag_bid = lag(amt_bid, 1) ) %>% drop_na(lag_booked, lag_bid) if (nrow(e) < 8) next fit <- tryCatch( lm(log1p(amt_booked) ~ log1p(amt_bid) + log1p(lag_booked) + month_num + t, data = e), error = function(err) NULL ) if (is.null(fit)) next fit_resid <- residuals(fit) resid_sd <- sd(fit_resid, na.rm = TRUE) if (is.na(resid_sd) || resid_sd == 0) resid_sd <- 0.10 hist <- monthly %>% filter(primary_name == est) %>% arrange(period) next_periods <- seq(max(hist$period) %m+% months(1), by = "month", length.out = 3) last_booked <- tail(hist$amt_booked, 1) last_bid <- tail(hist$amt_bid, 1) avg_recent_bid <- mean(tail(hist$amt_bid, 3), na.rm = TRUE) if (is.na(avg_recent_bid) || avg_recent_bid <= 0) avg_recent_bid <- max(last_bid, 1, na.rm = TRUE) if (is.na(last_booked)) last_booked <- 0 if (is.na(last_bid)) last_bid <- avg_recent_bid pred_rows <- vector("list", length(next_periods)) current_t <- max(e$t, na.rm = TRUE) current_lag_booked <- tail(hist$amt_booked, 1) current_lag_bid <- tail(hist$amt_bid, 1) if (is.na(current_lag_booked)) current_lag_booked <- 0 if (is.na(current_lag_bid) || current_lag_bid <= 0) current_lag_bid <- avg_recent_bid for (j in seq_along(next_periods)) { prd <- next_periods[j] current_t <- current_t + 1 new_x <- tibble( amt_bid = avg_recent_bid, lag_booked = current_lag_booked, lag_bid = current_lag_bid, month_num = month(prd), t = current_t ) pred_log <- as.numeric(predict(fit, newdata = new_x)) pred_amt <- pmax(0, expm1(pred_log)) lo <- pmax(0, expm1(pred_log - 1.28 * resid_sd)) hi <- pmax(0, expm1(pred_log + 1.28 * resid_sd)) pred_rows[[j]] <- tibble( primary_name = est, period = prd, predicted_booked = pred_amt, lo80 = lo, hi80 = hi, model_mae = mean(abs(expm1(fitted(fit)) - e$amt_booked), na.rm = TRUE) ) current_lag_booked <- pred_amt current_lag_bid <- avg_recent_bid } out[[i]] <- bind_rows(pred_rows) } bind_rows(out) }) # ── MAIN DROPDOWN UPDATERS ──────────────────────────────────────────────── observe({ df <- get_data() if (nrow(df) == 0) return() # Update Main Sidebar Estimators est_list <- sort(unique(df$primary_name)) updatePickerInput(session, "estimatorSelect", choices = est_list, selected = isolate(input$estimatorSelect)) # ── BUG FIX 1: its_estimator auto-selection ─────────────────────────── eligible <- df %>% group_by(primary_name) %>% summarise(n = n(), .groups = "drop") %>% filter(n >= 18) %>% pull(primary_name) %>% sort() cur_its <- isolate(input$its_estimator) selected_its <- if (length(cur_its) > 0 && cur_its %in% eligible) cur_its else eligible[1] updatePickerInput(session, "its_estimator", choices = eligible, selected = selected_its) # ── END BUG FIX 1 ──────────────────────────────────────────────────── yrs <- sort(unique(df$year_num), decreasing = TRUE) cur_yr <- isolate(input$quarter_year_select) updateSelectInput(session, "quarter_year_select", choices = yrs, selected = if (!is.null(cur_yr) && cur_yr %in% yrs) cur_yr else yrs[1]) max_dt <- max(df$report_date, na.rm = TRUE) updateDateRangeInput(session, "dateRange", start = max_dt %m-% months(24), end = max_dt) updateDateRangeInput(session, "baselineRange", start = max_dt %m-% months(60), end = max_dt %m-% months(25)) }) observeEvent(input$reset_filters, { df <- get_data() if (nrow(df) == 0) return() max_dt <- max(df$report_date, na.rm = TRUE) updateDateRangeInput(session, "dateRange", start = max_dt %m-% months(24), end = max_dt) updateDateRangeInput(session, "baselineRange", start = max_dt %m-% months(60), end = max_dt %m-% months(25)) updatePickerInput(session, "estimatorSelect", selected = character(0)) updateRadioGroupButtons(session, "view_mode", selected = "Aggregate") updateRadioGroupButtons(session, "time_granularity", selected = "Monthly") updateRadioGroupButtons(session, "annual_granularity", selected = "year") updateRadioGroupButtons(session, "yoy_position", selected = "dodge") updateRadioGroupButtons(session, "trend_series", selected = "booked") updateMaterialSwitch(session, "show_trendline", value = FALSE) updateMaterialSwitch(session, "show_trendline_qty", value = FALSE) updateSliderInput(session, "win_lag", value = 3) }) output$date_verification_table <- renderDataTable({ d <- get_data() if (nrow(d) == 0) return(NULL) d %>% arrange(report_date) %>% mutate(`Raw Format (MoYear)` = format(report_date, "%y-%m"), `Parsed Date` = format(report_date, "%B %Y"), `Full Date Stored` = as.character(report_date)) %>% select(Estimator = primary_name, `Raw Format (MoYear)`, `Parsed Date`, `Full Date Stored`) %>% distinct() %>% head(50) }, options = list(pageLength = 10, scrollX = TRUE, dom = "ftp")) output$trend_box_title <- renderText({ paste("Statistical Process Control β€”", input$time_granularity, "View") }) # ── KPIs ────────────────────────────────────────────────────────────────── output$kpi_total_bid <- renderValueBox(valueBox(dollar(sum(filtered_data()$amt_bid, na.rm = TRUE), accuracy = 1), "Total Bid Volume", icon = icon("file-invoice-dollar"), color = "blue")) output$kpi_total_booked <- renderValueBox(valueBox(dollar(sum(filtered_data()$amt_booked, na.rm = TRUE), accuracy = 1), "Total Booked Revenue", icon = icon("money-bill-wave"), color = "green")) output$kpi_win_rate_amt <- renderValueBox({ d <- filtered_data() r <- sum(d$amt_booked, na.rm = TRUE) / sum(d$amt_bid, na.rm = TRUE) valueBox(percent(r, 0.1), HTML("Same-Month Win Rate<br><small style='font-size:10px;opacity:0.75;'>Bids & bookings matched to same month</small>"), icon = icon("chart-line"), color = "yellow") }) output$kpi_lag_win_rate <- renderValueBox({ lag <- input$win_lag if (lag == 0) return(valueBox("N/A", HTML("Lag-Adjusted Win Rate<br><small style='font-size:10px;opacity:0.75;'>Set lag > 0 in sidebar</small>"), icon = icon("clock"), color = "purple")) lwr <- lag_win_rate() if (is.null(lwr) || nrow(lwr) == 0) return(valueBox("β€”", HTML("Lag-Adjusted Win Rate<br><small style='font-size:10px;opacity:0.75;'>Insufficient complete windows in range</small>"), icon = icon("clock"), color = "purple")) r <- sum(lwr$lag_booked_amt, na.rm = TRUE) / sum(lwr$amt_bid, na.rm = TRUE) max_dt <- max(get_data()$report_date, na.rm = TRUE) cutoff_dt <- max_dt %m-% months(lag) excl_label <- paste0("Bids after ", format(cutoff_dt, "%b %Y"), " excluded (incomplete window)") valueBox(percent(r, 0.1), HTML(paste0("Lag-Adjusted Win Rate<br><small style='font-size:10px;opacity:0.75;'>", excl_label, "</small>")), icon = icon("clock"), color = "purple") }) # ── CORRELATION INSIGHT ─────────────────────────────────────────────────── output$correlation_insight <- renderUI({ d <- filtered_data() if (nrow(d) < 10) return(tags$p("Waiting for sufficient data points...", class = "text-muted")) if (sd(d$qty_bid, na.rm = TRUE) == 0 || sd(d$amt_booked, na.rm = TRUE) == 0) return(tags$p("Insufficient variance for correlation.", class = "text-muted")) r <- cor(d$qty_bid, d$amt_booked, use = "complete.obs") txt <- if (is.na(r)) { "<span class='insight-text'>⚠️ Data insufficient for Pearson correlation.</span>" } else if (r < 0) { paste0("<span class='insight-text'>πŸ”΄ <b>Negative Correlation (r=", round(r,2), "):</b> More bids β†’ lower revenue. Signals Estimator Fatigue β€” bid fewer, higher-probability jobs.</span>") } else if (r < 0.3) { paste0("<span class='insight-text'>⚠️ <b>Weak Correlation (r=", round(r,2), "):</b> Bid quantity has minimal impact on revenue. Quality > quantity.</span>") } else { paste0("<span class='insight-text'>🟒 <b>Positive Correlation (r=", round(r,2), "):</b> Higher bid volume is translating to revenue. Pipeline mechanics are healthy.</span>") } HTML(txt) }) # ── SPC CHARTS ─────────────────────────────────────────────────────────── output$trend_dollars <- renderPlotly({ d <- filtered_data() req(input$time_granularity, input$view_mode, input$baselineRange) validate(need(nrow(d) > 0, "No data.")) is_q <- input$time_granularity == "Quarterly" by_person <- input$view_mode == "Individual" show_both <- isTRUE(input$trend_series == "both") if (by_person) { n <- length(unique(d$primary_name)) validate(need(n <= 4, paste0("⚠️ Select 4 or fewer estimators. Currently: ", n))) } ctrl <- spc_limits(d, "amt_booked", is_q, by_person, input$baselineRange[1], input$baselineRange[2]) if (show_both) { grp_date <- function(dt) if (is_q) floor_date(dt, "quarter") else floor_date(dt, "month") if (by_person) { bid_trend <- d %>% mutate(date_group = grp_date(report_date)) %>% group_by(date_group, primary_name) %>% summarise(Bid = sum(amt_bid, na.rm = TRUE), .groups = "drop") ctrl <- ctrl %>% left_join(bid_trend, by = c("date_group", "primary_name")) } else { bid_trend <- d %>% mutate(date_group = grp_date(report_date)) %>% group_by(date_group) %>% summarise(Bid = sum(amt_bid, na.rm = TRUE), .groups = "drop") ctrl <- ctrl %>% left_join(bid_trend, by = "date_group") } } if (by_person) { show_trend <- isTRUE(input$show_trendline) p <- ggplot(ctrl, aes(x = date_group, y = Val, group = primary_name, text = paste0("<b>", format(date_group, "%b %Y"), "</b><br>", primary_name, "<br>Booked: ", dollar(round(Val), 1), "<br>Baseline Mean: ", dollar(round(BL_Mean), 1)))) + geom_line(aes(color = primary_name), linewidth = 0.9) + geom_point(aes(shape = Anomaly), size = 2) + { if (show_both) geom_line(aes(y = Bid, group = primary_name), linetype = "dotted", color = "#95a5a6", linewidth = 0.8) } + { if (show_trend) geom_smooth(aes(group = primary_name, color = primary_name), method = "lm", se = TRUE, linewidth = 0.7, linetype = "solid", alpha = 0.12) } + geom_hline(aes(yintercept = BL_Mean), color = "#2980b9", linetype = "solid", alpha = 0.5) + geom_hline(aes(yintercept = UCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + geom_hline(aes(yintercept = LCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + scale_shape_manual(values = c("Normal" = 16, "Anomaly" = 8)) + scale_y_continuous(labels = dollar_format(accuracy = 1)) + facet_wrap(~primary_name, ncol = 2, scales = "free_y") + theme_minimal() + labs(x = "", y = "") + theme(legend.position = "none", strip.background = element_rect(fill = "#f4f6f9"), strip.text = element_text(face = "bold")) } else { p <- ggplot(ctrl, aes(x = date_group, y = Val, group = 1, text = paste0("<b>", format(date_group, "%b %Y"), "</b><br>", "Booked: ", dollar(round(Val), 1), "<br>Baseline Mean: ", dollar(round(BL_Mean), 1), if (show_both) paste0("<br>Bid: ", dollar(round(Bid), 1)) else ""))) + geom_line(color = "#2c3e50", linewidth = 0.9) + geom_point(aes(color = Anomaly), size = 2) + { if (show_both) geom_line(aes(y = Bid, group = 1), color = "#95a5a6", linetype = "dotted", linewidth = 1) } + geom_line(aes(y = BL_Mean), color = "#2980b9", linetype = "solid", alpha = 0.5) + geom_line(aes(y = UCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + geom_line(aes(y = LCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + scale_color_manual(values = c("Normal" = "#2c3e50", "Anomaly" = "#e74c3c")) + scale_y_continuous(labels = dollar_format(accuracy = 1)) + theme_minimal() + labs(x = "", y = "") + theme(legend.position = "none") } ggplotly(p, tooltip = "text") }) output$trend_qty <- renderPlotly({ d <- filtered_data() req(input$time_granularity, input$view_mode, input$baselineRange) validate(need(nrow(d) > 0, "No data.")) is_q <- input$time_granularity == "Quarterly" by_person <- input$view_mode == "Individual" if (by_person) { n <- length(unique(d$primary_name)) validate(need(n <= 4, paste0("⚠️ Select 4 or fewer estimators. Currently: ", n))) } ctrl <- spc_limits(d, "qty_booked", is_q, by_person, input$baselineRange[1], input$baselineRange[2]) if (by_person) { show_trend_qty <- isTRUE(input$show_trendline_qty) p <- ggplot(ctrl, aes(x = date_group, y = Val, group = primary_name, text = paste0("<b>", format(date_group, "%b %Y"), "</b><br>", primary_name, "<br>Jobs: ", comma(Val, 1), "<br>Baseline Mean: ", comma(BL_Mean, 1)))) + geom_line(color = "#27ae60", linewidth = 0.9) + geom_point(aes(color = Anomaly), size = 2) + { if (show_trend_qty) geom_smooth(aes(group = primary_name), method = "lm", se = TRUE, color = "#27ae60", fill = "#27ae60", linewidth = 0.7, alpha = 0.12) } + geom_hline(aes(yintercept = BL_Mean), color = "#2980b9", linetype = "solid", alpha = 0.5) + geom_hline(aes(yintercept = UCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + geom_hline(aes(yintercept = LCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + scale_color_manual(values = c("Normal" = "#27ae60", "Anomaly" = "#e74c3c")) + scale_y_continuous(labels = comma_format(accuracy = 1)) + facet_wrap(~primary_name, ncol = 2, scales = "free_y") + theme_minimal() + labs(x = "", y = "") + theme(legend.position = "none", strip.background = element_rect(fill = "#f4f6f9"), strip.text = element_text(face = "bold")) } else { p <- ggplot(ctrl, aes(x = date_group, y = Val, group = 1, text = paste0("<b>", format(date_group, "%b %Y"), "</b><br>", "Jobs Booked: ", comma(Val, 1), "<br>Baseline Mean: ", comma(BL_Mean, 1)))) + geom_line(color = "#27ae60", linewidth = 0.9) + geom_point(aes(color = Anomaly), size = 2) + geom_line(aes(y = BL_Mean), color = "#2980b9", linetype = "solid", alpha = 0.5) + geom_line(aes(y = UCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + geom_line(aes(y = LCL), color = "#c0392b", linetype = "dashed", alpha = 0.5) + scale_color_manual(values = c("Normal" = "#27ae60", "Anomaly" = "#e74c3c")) + scale_y_continuous(labels = comma_format(accuracy = 1)) + theme_minimal() + labs(x = "", y = "") + theme(legend.position = "none") } ggplotly(p, tooltip = "text") }) # ── INTERVENTION ANALYSIS (ITS) ENGINE ──────────────────────────────────── its_series <- reactive({ df <- get_data() metric <- input$its_metric req(nrow(df) > 0, input$its_estimator, metric) series <- df %>% filter(primary_name == input$its_estimator) %>% arrange(report_date) %>% mutate(date_group = floor_date(report_date, "month")) %>% group_by(date_group) %>% summarise( amt_bid = sum(amt_bid, na.rm = TRUE), amt_booked = sum(amt_booked, na.rm = TRUE), qty_booked = sum(qty_booked, na.rm = TRUE), qty_bid = sum(qty_bid, na.rm = TRUE), .groups = "drop" ) %>% mutate( win_rate = ifelse(amt_bid > 0, amt_booked / amt_bid, NA_real_), value = .data[[metric]], t = row_number() ) %>% filter(!is.na(value)) series }) its_result <- reactiveVal(NULL) its_selected_bp <- reactiveVal(NULL) observeEvent(input$its_breakpoints_table_rows_selected, { bps <- its_result()$breakpoints idx <- input$its_breakpoints_table_rows_selected if (!is.null(bps) && !is.null(idx) && idx <= nrow(bps)) { sel_date <- bps$Date[idx] updateDateInput(session, "its_manual_date", value = sel_date) its_selected_bp(sel_date) } }) observeEvent(input$its_run, { # ── BUG FIX 2: tryCatch around its_series() call ────────────────────── series <- tryCatch(its_series(), error = function(e) NULL) if (is.null(series)) { its_result(list(error = "Please select an estimator from the dropdown, then click Run Analysis.")) return() } # ── END BUG FIX 2 ──────────────────────────────────────────────────── validate_msg <- NULL if (nrow(series) < 18) validate_msg <- "Need at least 18 monthly observations." if (!is.null(validate_msg)) { its_result(list(error = validate_msg)); return() } metric <- input$its_metric manual_dt <- input$its_manual_date autodetect <- isTRUE(input$its_autodetect) bp_table <- NULL bp_dates <- c() if (autodetect) { tryCatch({ bp_fit <- breakpoints(value ~ t, data = series, h = 6) bp_idx <- bp_fit$breakpoints if (!any(is.na(bp_idx)) && length(bp_idx) > 0) { bp_dates <- series$date_group[bp_idx] # ── BUG FIX 3: Fstats time-series subscripting ────────────────── fs <- Fstats(value ~ t, data = series) fs_times <- as.integer(time(fs$Fstats)) fs_vals <- as.numeric(fs$Fstats) fstat_at_bp <- sapply(bp_idx, function(i) { pos <- which(fs_times == i) if (length(pos) > 0) round(fs_vals[pos], 2) else NA_real_ }) bp_table <- tibble( Date = bp_dates, `Time Index` = bp_idx, `F-Statistic` = fstat_at_bp, `Months Before` = bp_idx, `Months After` = nrow(series) - bp_idx ) # ── END BUG FIX 3 ────────────────────────────────────────────── } }, error = function(e) NULL) } active_date <- NULL if (!is.null(manual_dt) && !is.na(manual_dt) && manual_dt >= min(series$date_group) && manual_dt <= max(series$date_group)) { active_date <- floor_date(as.Date(manual_dt), "month") } else if (length(bp_dates) > 0) { active_date <- bp_dates[1] } else { active_date <- series$date_group[round(nrow(series) / 2)] } t0 <- which(series$date_group == active_date) if (length(t0) == 0) t0 <- which.min(abs(series$date_group - active_date)) model_df <- series %>% mutate(D = as.integer(t >= t0), t_post = (t - t0) * D) fit <- tryCatch(lm(value ~ t + D + t_post, data = model_df), error = function(e) NULL) if (is.null(fit)) { its_result(list(error = "Model could not be fitted. Try a different date.")); return() } cf <- coeftest(fit) cf_df <- data.frame( Term = recode(rownames(cf), `(Intercept)` = "Intercept (baseline level)", `t` = "Pre-intervention trend", `D` = "Level change", `t_post` = "Slope change"), Estimate = round(cf[, "Estimate"], 2), `Std Error` = round(cf[, "Std. Error"], 2), `t value` = round(cf[, "t value"], 3), `p value` = ifelse(cf[, "Pr(>|t|)"] < 0.001, "< 0.001", as.character(round(cf[, "Pr(>|t|)"], 3))), Significant = ifelse(cf[, "Pr(>|t|)"] < 0.05, "Yes *", "No"), stringsAsFactors = FALSE, check.names = FALSE ) model_df <- model_df %>% mutate(fitted = fitted(fit), resid = residuals(fit), counterfact = coef(fit)[1] + coef(fit)[2] * t) pre <- model_df %>% filter(t <= t0) post <- model_df %>% filter(t >= t0) b2 <- coef(fit)["D"]; b3 <- coef(fit)["t_post"] p2 <- cf[3, 4]; p3 <- cf[4, 4] level_txt <- if (p2 < 0.05) { dir <- if (b2 > 0) "increased" else "decreased" paste0("The outcome ", dir, " immediately by ", if (metric %in% c("amt_booked","amt_bid")) dollar(abs(round(b2))) else round(abs(b2), 1), " at the intervention point (p = ", round(p2, 3), ").") } else { paste0("No significant immediate level change detected (p = ", round(p2, 3), ").") } slope_txt <- if (p3 < 0.05) { dir <- if (b3 > 0) "accelerated" else "decelerated" paste0("The monthly trajectory ", dir, " by ", if (metric %in% c("amt_booked","amt_bid")) dollar(abs(round(b3))) else round(abs(b3), 3), " per month after the intervention (p = ", round(p3, 3), ").") } else { paste0("No significant change in monthly trajectory detected (p = ", round(p3, 3), ").") } overall <- if (p2 < 0.05 || p3 < 0.05) "Significant intervention effect detected." else "No statistically significant intervention effect detected." its_result(list( series = model_df, pre = pre, post = post, fit = fit, coef_df = cf_df, breakpoints = bp_table, active_date = active_date, t0 = t0, metric = metric, estimator = input$its_estimator, level_txt = level_txt, slope_txt = slope_txt, overall = overall, r2 = round(summary(fit)$r.squared, 3), autodetect = autodetect )) }) output$its_summary_ui <- renderUI({ res <- its_result() if (is.null(res)) return(tags$p("Configure settings and click 'Run Analysis'.", class = "text-muted")) if (!is.null(res$error)) return(tags$p(res$error, style = "color:#c0392b;")) metric_label <- c(amt_booked = "Booked Revenue ($)", amt_bid = "Bid Volume ($)", qty_booked = "Jobs Booked", win_rate = "Win Rate") tagList( tags$table(style = "width:100%; border-collapse:collapse; font-size:13px;", tags$tr( tags$td(style = "padding:6px 10px; font-weight:600; color:#2c3e50; width:40%;", "Estimator"), tags$td(style = "padding:6px 10px; color:#5A6A72;", res$estimator)), tags$tr(style = "background:#f4f6f9;", tags$td(style = "padding:6px 10px; font-weight:600; color:#2c3e50;", "Outcome Metric"), tags$td(style = "padding:6px 10px; color:#5A6A72;", metric_label[res$metric])), tags$tr( tags$td(style = "padding:6px 10px; font-weight:600; color:#2c3e50;", "Intervention Date"), tags$td(style = "padding:6px 10px; color:#5A6A72;", format(res$active_date, "%B %Y"), if (!is.null(input$its_manual_date) && !is.na(input$its_manual_date) && floor_date(as.Date(input$its_manual_date), "month") == res$active_date) tags$span(" (manager-specified)", style = "color:#e67e22; font-size:11px;") else tags$span(" (auto-detected)", style = "color:#27ae60; font-size:11px;"))), tags$tr(style = "background:#f4f6f9;", tags$td(style = "padding:6px 10px; font-weight:600; color:#2c3e50;", "Model R\u00b2"), tags$td(style = "padding:6px 10px; color:#5A6A72;", res$r2)) ), tags$hr(style = "margin:12px 0;"), tags$p(style = "font-size:13px; color:#2c3e50; font-weight:600;", res$overall), tags$p(style = "font-size:12px; color:#5A6A72; margin:4px 0;", tags$b("Level change: "), res$level_txt), tags$p(style = "font-size:12px; color:#5A6A72; margin:4px 0;", tags$b("Slope change: "), res$slope_txt) ) }) output$its_chart <- renderPlotly({ res <- its_result() if (is.null(res) || !is.null(res$error)) return(plotly_empty()) series <- res$series; pre <- res$pre; post <- res$post; metric <- res$metric fmt <- if (metric %in% c("amt_booked","amt_bid")) function(x) dollar(round(x)) else if (metric == "win_rate") function(x) percent(x, 0.1) else function(x) comma(round(x)) y_label <- c(amt_booked = "Booked Revenue ($)", amt_bid = "Bid Volume ($)", qty_booked = "Jobs Booked", win_rate = "Win Rate")[metric] p <- ggplot(series, aes(x = date_group)) + geom_point(aes(y = value, text = paste0("<b>", format(date_group, "%b %Y"), "</b><br>Actual: ", fmt(value))), color = "#95a5a6", size = 2.2, alpha = 0.8) + geom_line(aes(y = counterfact), color = "#c0392b", linetype = "dotted", linewidth = 0.7, alpha = 0.6) + geom_line(data = pre, aes(y = fitted), color = "#2980b9", linewidth = 1.1) + geom_line(data = post, aes(y = fitted), color = "#27ae60", linewidth = 1.1) + geom_vline(xintercept = as.numeric(res$active_date), color = "#e67e22", linetype = "dashed", linewidth = 0.9) + annotate("text", x = res$active_date, y = max(series$value, na.rm = TRUE) * 0.95, label = paste("Intervention\n", format(res$active_date, "%b %Y")), hjust = -0.08, size = 3, color = "#e67e22") + scale_y_continuous(labels = if (metric %in% c("amt_booked","amt_bid")) dollar_format(accuracy = 1) else if (metric == "win_rate") percent_format(accuracy = 1) else comma_format(accuracy = 1)) + labs(x = NULL, y = y_label, title = paste0(res$estimator, " β€” ITS: ", y_label), caption = "Blue = pre-int. trend | Green = post-int. trend | Red dotted = counterfactual") + theme_minimal() + theme(plot.caption = element_text(size = 8, color = "#95a5a6"), plot.title = element_text(size = 12, face = "bold", color = "#2c3e50")) ggplotly(p, tooltip = "text") %>% layout(legend = list(orientation = "h")) }) output$its_breakpoints_table <- renderDataTable({ res <- its_result() if (is.null(res) || !is.null(res$error)) return(NULL) if (is.null(res$breakpoints) || nrow(res$breakpoints) == 0) return(data.frame(Message = "No significant breakpoints detected automatically.")) res$breakpoints %>% mutate(Date = format(Date, "%B %Y")) %>% arrange(desc(`F-Statistic`)) }, selection = "single", options = list(dom = "t", pageLength = 10, scrollX = TRUE)) output$its_coef_table <- renderDataTable({ res <- its_result() if (is.null(res) || !is.null(res$error)) return(NULL) res$coef_df }, options = list(dom = "t", pageLength = 10, scrollX = TRUE)) # ── ADVANCED ANALYTICS (FUNNEL & HEATMAP) ───────────────────────────────── output$bid_funnel_chart <- renderPlotly({ d <- filtered_data() validate(need(nrow(d) > 0, "No data.")) funnel <- d %>% group_by(primary_name) %>% summarise(Bid = sum(amt_bid, na.rm = TRUE), Booked = sum(amt_booked, na.rm = TRUE), .groups = "drop") %>% filter(Bid > 0) %>% mutate(Win_Rate = Booked / Bid, primary_name = fct_reorder(primary_name, Bid)) %>% pivot_longer(c(Bid, Booked), names_to = "Type", values_to = "Amount") %>% mutate(Win_Label = ifelse(Type == "Booked", paste0(percent(Win_Rate, 0.1), " win rate"), NA_character_)) p <- ggplot(funnel, aes(x = primary_name, y = Amount, fill = Type, text = paste0("<b>", primary_name, "</b><br>", Type, ": ", dollar(Amount, accuracy = 1), ifelse(!is.na(Win_Label), paste0("<br>", Win_Label), "")))) + geom_col(position = "dodge", width = 0.65) + geom_text(aes(label = Win_Label), position = position_dodge(width = 0.65), hjust = -0.1, size = 3, na.rm = TRUE, color = "#2c3e50") + scale_fill_manual(values = c("Bid" = "#95a5a6", "Booked" = "#27ae60")) + scale_y_continuous(labels = dollar_format(accuracy = 1), expand = expansion(mult = c(0, 0.25))) + coord_flip() + labs(x = "", y = "Amount ($)", fill = "") + theme_minimal() + theme(legend.position = "top", panel.grid.major.y = element_blank()) ggplotly(p, tooltip = "text") }) output$seasonality_heatmap <- renderPlotly({ df <- get_data() sel <- input$estimatorSelect if (!is.null(sel) && length(sel) > 0) df <- df %>% filter(primary_name %in% sel) validate(need(nrow(df) > 0, "No data.")) hm_metric <- if (!is.null(input$heatmap_metric)) input$heatmap_metric else "amt_booked" metric_sym <- sym(hm_metric) metric_label <- if (hm_metric == "amt_booked") "Booked ($)" else "Bid ($)" heat <- df %>% mutate(Month = month(report_date, label = TRUE, abbr = TRUE), Year = as.integer(year(report_date))) %>% group_by(Year, Month) %>% summarise(Value = sum(!!metric_sym, na.rm = TRUE), .groups = "drop") %>% complete(Year, Month, fill = list(Value = 0)) %>% mutate(Month = factor(Month, levels = rev(month.abb))) p <- ggplot(heat, aes(x = factor(Year), y = Month, fill = Value, text = paste0("<b>", Month, " ", Year, "</b><br>", metric_label, ": ", dollar(Value, accuracy = 1)))) + geom_tile(color = "white", linewidth = 0.4) + scale_fill_gradient(low = "#eaf4fb", high = if (hm_metric == "amt_booked") "#1a5276" else "#7f8c8d", labels = dollar_format(scale = 1e-3, suffix = "K", accuracy = 1), name = metric_label) + labs(x = "Year", y = "") + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1), panel.grid = element_blank(), legend.position = "right") ggplotly(p, tooltip = "text") }) # ── K-MEANS ─────────────────────────────────────────────────────────────── output$scatter_matrix <- renderPlotly({ dat <- filtered_data() validate(need(nrow(dat) > 0, "Upload data to begin.")) lwr <- lag_win_rate() ds_df <- dat %>% group_by(primary_name) %>% summarise(Total_Bid = sum(amt_bid, na.rm = TRUE), Total_Booked = sum(amt_booked, na.rm = TRUE), Jobs_Bid = sum(qty_bid, na.rm = TRUE), .groups = "drop") %>% mutate(Avg_Job_Size = Total_Bid / Jobs_Bid, Win_Rate_SM = Total_Booked / Total_Bid) if (!is.null(lwr) && nrow(lwr) > 0) { lag_summary <- lwr %>% group_by(primary_name) %>% summarise(Lag_Win_Rate = sum(lag_booked_amt, na.rm = TRUE) / sum(amt_bid, na.rm = TRUE), .groups = "drop") ds_df <- ds_df %>% left_join(lag_summary, by = "primary_name") %>% mutate(Win_Rate = coalesce(Lag_Win_Rate, Win_Rate_SM)) } else { ds_df$Win_Rate <- ds_df$Win_Rate_SM } ds_df <- ds_df %>% filter(Total_Bid > 0, !is.na(Win_Rate), !is.infinite(Win_Rate), !is.na(Avg_Job_Size), !is.infinite(Avg_Job_Size)) validate(need(nrow(ds_df) >= 3, "Need at least 3 active estimators to cluster.")) set.seed(42) if (any(sapply(ds_df[, c("Total_Bid", "Win_Rate", "Avg_Job_Size")], function(x) sd(x, na.rm = TRUE) == 0))) { ds_df$Archetype <- "Archetype 1" } else { feats <- ds_df %>% select(Total_Bid, Win_Rate, Avg_Job_Size) %>% scale() k <- min(3, nrow(ds_df) - 1) km <- kmeans(feats, centers = k, nstart = 25) ds_df$Cluster <- as.factor(km$cluster) cl_means <- ds_df %>% group_by(Cluster) %>% summarise(mean_wr = mean(Win_Rate, na.rm = TRUE), .groups = "drop") %>% arrange(desc(mean_wr)) arch_map <- setNames(c("High-Efficiency", "Selective", "New/Developing")[seq_len(nrow(cl_means))], cl_means$Cluster) ds_df$Archetype <- arch_map[as.character(ds_df$Cluster)] } p <- ggplot(ds_df, aes(x = Total_Bid, y = Win_Rate, color = Archetype, size = Total_Booked, text = paste0("<b>", primary_name, "</b><br>", "Archetype: ", Archetype, "<br>", "Pipeline Effort: ", dollar(Total_Bid, 1), "<br>", "Win Rate: ", percent(Win_Rate, 0.1), "<br>", "Avg Job Size: ", dollar(Avg_Job_Size, 1), "<br>", "Total Booked: ", dollar(Total_Booked, 1)))) + geom_point(alpha = 0.85) + scale_color_manual(values = c("High-Efficiency" = "#27ae60", "Selective" = "#f39c12", "High-Volume" = "#e74c3c")) + scale_x_continuous(labels = dollar_format(accuracy = 1)) + scale_y_continuous(labels = percent_format(accuracy = 1)) + labs(x = "Total Amount Bid", y = "Win Rate", color = "Archetype") + theme_minimal() + theme(legend.position = "right") ggplotly(p, tooltip = "text") }) # ── ANNUAL, YOY, QUARTERLY ──────────────────────────────────────────────── output$annual_bar_chart <- renderPlotly({ d <- filtered_data() validate(need(nrow(d) > 0, "No data.")) gran <- input$annual_granularity by_person <- !is.null(input$estimatorSelect) && length(input$estimatorSelect) <= 5 d <- d %>% mutate(x_group = case_when( gran == "year" ~ as.character(year(report_date)), gran == "quarter" ~ paste0(year(report_date), " Q", quarter(report_date)), gran == "month" ~ format(report_date, "%Y-%m"), TRUE ~ as.character(year(report_date)) )) x_levels <- d %>% arrange(report_date) %>% pull(x_group) %>% unique() d <- d %>% mutate(x_group = factor(x_group, levels = x_levels)) if (by_person) { annual <- d %>% group_by(x_group, primary_name) %>% summarise(Booked = sum(amt_booked, na.rm = TRUE), .groups = "drop") p <- ggplot(annual, aes(x_group, Booked, fill = primary_name, text = paste0("<b>", x_group, "</b><br>", primary_name, "<br>", dollar(round(Booked), accuracy = 1)))) + geom_col(position = "dodge") + scale_fill_brewer(palette = "Paired") } else { annual <- d %>% group_by(x_group) %>% summarise(Bid = sum(amt_bid, na.rm = TRUE), Booked = sum(amt_booked, na.rm = TRUE), .groups = "drop") %>% pivot_longer(c(Bid, Booked), names_to = "Type", values_to = "Value") p <- ggplot(annual, aes(x_group, Value, fill = Type, text = paste0("<b>", x_group, "</b><br>", Type, ": ", dollar(round(Value), accuracy = 1)))) + geom_col(position = "dodge") + scale_fill_manual(values = c("Bid" = "#95a5a6", "Booked" = "#27ae60")) } ggplotly(p + scale_y_continuous(labels = dollar_format(accuracy = 1)) + labs(x = "", y = "", fill = "") + theme_minimal() + theme(axis.text.x = element_text(angle = if (gran != "year") 45 else 0, hjust = 1)), tooltip = "text") }) output$yoy_chart <- renderPlotly({ d <- filtered_data() req(input$yoy_metric, input$view_mode) validate(need(nrow(d) > 0, "No data.")) msym <- sym(input$yoy_metric) bar_pos <- input$yoy_position if (input$view_mode == "Individual") { validate(need(length(unique(d$primary_name)) <= 10, "Select 10 or fewer estimators for Individual YoY.")) ag <- d %>% group_by(year_num, primary_name) %>% summarise(V = sum(!!msym, na.rm = TRUE), .groups = "drop") %>% arrange(year_num) %>% group_by(primary_name) %>% mutate(G = (V - lag(V)) / lag(V)) %>% filter(!is.na(G)) p <- ggplot(ag, aes(factor(year_num), G, fill = primary_name, text = paste0(year_num, " β€” ", primary_name, "<br>Growth: ", percent(G, 0.1)))) + geom_col(position = bar_pos) + scale_fill_brewer(palette = "Paired") } else { ag <- d %>% group_by(year_num) %>% summarise(V = sum(!!msym, na.rm = TRUE), .groups = "drop") %>% arrange(year_num) %>% mutate(G = (V - lag(V)) / lag(V)) %>% filter(!is.na(G)) p <- ggplot(ag, aes(factor(year_num), G, fill = G > 0, text = paste0(year_num, "<br>Growth: ", percent(G, 0.1)))) + geom_col(show.legend = FALSE) + scale_fill_manual(values = c("TRUE" = "#27ae60", "FALSE" = "#c0392b")) } ggplotly(p + geom_hline(yintercept = 0) + scale_y_continuous(labels = percent_format()) + labs(x = "Year", y = "Growth %") + theme_minimal(), tooltip = "text") }) output$quarterly_breakdown_chart <- renderPlotly({ d <- filtered_data() req(input$quarter_year_select) validate(need(nrow(d) > 0, "No data.")) q <- d %>% filter(year_num == as.numeric(input$quarter_year_select)) %>% mutate(Quarter = paste0("Q", quarter(report_date))) %>% group_by(Quarter) %>% summarise(Revenue = sum(amt_booked, na.rm = TRUE), .groups = "drop") p <- ggplot(q, aes(Quarter, Revenue, text = paste0(Quarter, "<br>", dollar(Revenue, 1)))) + geom_col(fill = "#3c8dbc", width = 0.6) + scale_y_continuous(labels = dollar_format(accuracy = 1)) + labs(x = "", y = "Revenue ($)") + theme_minimal() + theme(panel.grid.major.x = element_blank()) ggplotly(p, tooltip = "text") }) # ── LEADERBOARD & EXPORT ────────────────────────────────────────────────── output$leaderboard_table <- renderDataTable({ d <- filtered_data() if (nrow(d) == 0) return(NULL) lb <- d %>% group_by(primary_name) %>% summarise(Total_Bids = sum(amt_bid, na.rm = TRUE), Total_Booked = sum(amt_booked, na.rm = TRUE), Jobs_Won = sum(qty_booked, na.rm = TRUE), Jobs_Bid = sum(qty_bid, na.rm = TRUE), .groups = "drop") %>% mutate(Win_Rate = Total_Booked / Total_Bids, Avg_Job_Size = Total_Bids / Jobs_Bid) if (input$win_lag > 0) { lwr <- lag_win_rate() if (!is.null(lwr)) { lag_col <- lwr %>% group_by(primary_name) %>% summarise(Lag_WR = sum(lag_booked_amt, na.rm = TRUE) / sum(amt_bid, na.rm = TRUE), .groups = "drop") lb <- lb %>% left_join(lag_col, by = "primary_name") } } lb <- lb %>% mutate(Total_Bids = dollar(Total_Bids, accuracy = 1), Total_Booked = dollar(Total_Booked, accuracy = 1), Win_Rate = percent(Win_Rate, accuracy = 0.1), Avg_Job_Size = dollar(Avg_Job_Size, accuracy = 1)) %>% rename(Estimator = primary_name, `Total Revenue` = Total_Booked, `Total Bids` = Total_Bids, `Win % (same-mo)` = Win_Rate, `Jobs Won` = Jobs_Won, `Jobs Bid` = Jobs_Bid, `Avg Job Size` = Avg_Job_Size) if ("Lag_WR" %in% names(lb)) lb <- lb %>% mutate(Lag_WR = percent(Lag_WR, accuracy = 0.1)) %>% rename(`Win % (lag-adj)` = Lag_WR) datatable(lb, options = list(pageLength = 15, scrollX = TRUE, order = list(list(2, "desc"))), rownames = FALSE) }) output$ceo_total_pred_90d <- renderValueBox({ f <- ceo_forecast() total_pred <- sum(f$predicted_booked, na.rm = TRUE) valueBox( value = dollar(total_pred, accuracy = 1), subtitle = "Forecasted Bookings (Next 90 Days)", icon = icon("chart-line"), color = "blue" ) }) output$ceo_top_estimator <- renderValueBox({ f <- ceo_forecast() top <- f %>% group_by(primary_name) %>% summarise(pred_90d = sum(predicted_booked, na.rm = TRUE), .groups = "drop") %>% arrange(desc(pred_90d)) %>% slice(1) validate(need(nrow(top) > 0, "No forecast available yet.")) valueBox( value = top$primary_name[[1]], subtitle = paste0("Top Expected Contributor (", dollar(top$pred_90d[[1]], accuracy = 1), ")"), icon = icon("trophy"), color = "green" ) }) output$ceo_at_risk_count <- renderValueBox({ sc <- ceo_scorecard() n_risk <- sum(sc$risk_flag == "At Risk", na.rm = TRUE) valueBox( value = n_risk, subtitle = "Estimators Flagged At Risk", icon = icon("triangle-exclamation"), color = ifelse(n_risk > 0, "red", "olive") ) }) output$ceo_model_quality <- renderValueBox({ f <- ceo_forecast() mae <- mean(f$model_mae, na.rm = TRUE) if (is.na(mae)) mae <- 0 valueBox( value = dollar(mae, accuracy = 1), subtitle = "Average Forecast MAE (In-Sample)", icon = icon("ruler"), color = "purple" ) }) output$ceo_forecast_plot <- renderPlotly({ f <- ceo_forecast() validate(need(nrow(f) > 0, "Insufficient history to forecast.")) p <- f %>% group_by(primary_name) %>% summarise( pred_90d = sum(predicted_booked, na.rm = TRUE), lo80 = sum(lo80, na.rm = TRUE), hi80 = sum(hi80, na.rm = TRUE), .groups = "drop" ) %>% arrange(desc(pred_90d)) %>% mutate(primary_name = fct_reorder(primary_name, pred_90d)) %>% ggplot(aes(x = primary_name, y = pred_90d, text = paste0( "<b>", primary_name, "</b><br>", "Predicted 90D: ", dollar(pred_90d, accuracy = 1), "<br>", "80% band: ", dollar(lo80, accuracy = 1), " - ", dollar(hi80, accuracy = 1) ))) + geom_col(fill = "#3c8dbc", width = 0.65) + geom_errorbar(aes(ymin = lo80, ymax = hi80), width = 0.2, color = "#2c3e50") + coord_flip() + scale_y_continuous(labels = dollar_format(accuracy = 1)) + labs(x = "", y = "Predicted Booked Revenue ($)") + theme_minimal() ggplotly(p, tooltip = "text") }) output$ceo_action_summary <- renderUI({ sc <- ceo_scorecard() f <- ceo_forecast() validate(need(nrow(sc) > 0, "No scorecard data.")) top_strength <- sc %>% arrange(desc(composite_score)) %>% slice(1) top_risk <- sc %>% filter(risk_flag == "At Risk") %>% arrange(composite_score) %>% slice(1) f_rollup <- f %>% group_by(primary_name) %>% summarise(pred_90d = sum(predicted_booked, na.rm = TRUE), .groups = "drop") upside <- f_rollup %>% arrange(desc(pred_90d)) %>% slice(1) tagList( tags$p(tags$b("Top performer:"), paste0(" ", top_strength$primary_name[[1]], " (score ", round(top_strength$composite_score[[1]], 1), ").")), tags$p(tags$b("Highest upside:"), paste0(" ", upside$primary_name[[1]], " with projected ", dollar(upside$pred_90d[[1]], accuracy = 1), " in next 90 days.")), if (nrow(top_risk) > 0) { tags$p(tags$b("Immediate coaching target:"), paste0(" ", top_risk$primary_name[[1]], " (low conversion or unstable output).")) } else { tags$p(tags$b("Risk status:"), " No estimator currently flagged as high risk.") } ) }) output$ceo_scorecard_table <- renderDataTable({ sc <- ceo_scorecard() f <- ceo_forecast() %>% group_by(primary_name) %>% summarise(pred_90d = sum(predicted_booked, na.rm = TRUE), .groups = "drop") view <- sc %>% left_join(f, by = "primary_name") %>% mutate( pred_90d = replace_na(pred_90d, 0), win_rate_used = percent(win_rate_used, accuracy = 0.1), same_month_win_rate = percent(same_month_win_rate, accuracy = 0.1), lag_win_rate = ifelse(is.na(lag_win_rate), "N/A", percent(lag_win_rate, accuracy = 0.1)), total_bid = dollar(total_bid, accuracy = 1), total_booked = dollar(total_booked, accuracy = 1), avg_job_size = dollar(avg_job_size, accuracy = 1), pred_90d = dollar(pred_90d, accuracy = 1), composite_score = round(composite_score, 1) ) %>% transmute( Estimator = primary_name, `Score (0-100)` = composite_score, `Risk` = risk_flag, `Win % (Used)` = win_rate_used, `Win % (Same-Mo)` = same_month_win_rate, `Win % (Lag-Adj)` = lag_win_rate, `Booked Forecast 90D` = pred_90d, `Total Booked` = total_booked, `Total Bid` = total_bid, `Avg Job Size` = avg_job_size, `Months Active` = months_active ) datatable( view, options = list(pageLength = 15, scrollX = TRUE, order = list(list(1, "desc"))), rownames = FALSE ) }) output$download_top_brief <- downloadHandler( filename = function() { paste0("Top_Brief_", format(Sys.Date(), "%Y-%m-%d"), ".xlsx") }, content = function(file) { sc <- ceo_scorecard() fc <- ceo_forecast() req(nrow(sc) > 0) withProgress(message = "Building Top Brief export...", value = 0.2, { wb <- createWorkbook() header_style <- createStyle(fontName = "Calibri", fontSize = 11, fontColour = "#FFFFFF", fgFill = "#2c3e50", halign = "LEFT", valign = "CENTER", textDecoration = "bold", border = "Bottom", borderColour = "#27ae60") title_style <- createStyle(fontName = "Calibri", fontSize = 14, textDecoration = "bold", fontColour = "#2c3e50") dollar_style <- createStyle(numFmt = "$#,##0") pct_style <- createStyle(numFmt = "0.0%") fc_roll <- fc %>% group_by(primary_name) %>% summarise( pred_90d = sum(predicted_booked, na.rm = TRUE), lo80 = sum(lo80, na.rm = TRUE), hi80 = sum(hi80, na.rm = TRUE), model_mae = mean(model_mae, na.rm = TRUE), .groups = "drop" ) top_strength <- sc %>% arrange(desc(composite_score)) %>% slice(1) top_risk <- sc %>% filter(risk_flag == "At Risk") %>% arrange(composite_score) %>% slice(1) top_upside <- fc_roll %>% arrange(desc(pred_90d)) %>% slice(1) addWorksheet(wb, "Top Brief Summary") setColWidths(wb, "Top Brief Summary", cols = 1:3, widths = c(36, 26, 26)) writeData(wb, "Top Brief Summary", "Top Brief - Executive Summary", startRow = 1, startCol = 1) addStyle(wb, "Top Brief Summary", title_style, rows = 1, cols = 1) total_pred <- sum(fc_roll$pred_90d, na.rm = TRUE) at_risk_n <- sum(sc$risk_flag == "At Risk", na.rm = TRUE) avg_mae <- mean(fc_roll$model_mae, na.rm = TRUE) kpi <- data.frame( Metric = c("Forecasted Bookings (Next 90 Days)", "Top Expected Contributor", "Estimators Flagged At Risk", "Average Forecast MAE"), Value = c( dollar(total_pred, accuracy = 1), if (nrow(top_upside) > 0) paste0(top_upside$primary_name[[1]], " (", dollar(top_upside$pred_90d[[1]], accuracy = 1), ")") else "N/A", as.character(at_risk_n), dollar(ifelse(is.na(avg_mae), 0, avg_mae), accuracy = 1) ), stringsAsFactors = FALSE ) writeData(wb, "Top Brief Summary", kpi, startRow = 3, startCol = 1, colNames = TRUE) addStyle(wb, "Top Brief Summary", header_style, rows = 3, cols = 1:2) action_lines <- data.frame( `Action Summary` = c( paste0("Top performer: ", top_strength$primary_name[[1]], " (score ", round(top_strength$composite_score[[1]], 1), ")."), if (nrow(top_upside) > 0) paste0("Highest upside: ", top_upside$primary_name[[1]], " projected ", dollar(top_upside$pred_90d[[1]], accuracy = 1), " over next 90 days.") else "Highest upside: N/A", if (nrow(top_risk) > 0) paste0("Immediate coaching target: ", top_risk$primary_name[[1]], ".") else "Risk status: no estimator currently flagged as high risk." ), stringsAsFactors = FALSE ) writeData(wb, "Top Brief Summary", action_lines, startRow = 9, startCol = 1, colNames = TRUE) addStyle(wb, "Top Brief Summary", header_style, rows = 9, cols = 1) config_df <- data.frame( Parameter = c("Weight: Win Quality", "Weight: Efficiency", "Weight: Stability", "Weight: Output", "At-Risk Win % <", "At-Risk Volatility >"), Value = c( input$ceo_w_win %||% 35, input$ceo_w_eff %||% 20, input$ceo_w_stab %||% 20, input$ceo_w_out %||% 25, input$ceo_risk_win %||% 12, input$ceo_risk_cv %||% 1.25 ), stringsAsFactors = FALSE ) writeData(wb, "Top Brief Summary", config_df, startRow = 14, startCol = 1, colNames = TRUE) addStyle(wb, "Top Brief Summary", header_style, rows = 14, cols = 1:2) setProgress(0.6) addWorksheet(wb, "Scorecard") setColWidths(wb, "Scorecard", cols = 1:12, widths = c(22, 12, 10, 12, 12, 12, 16, 14, 14, 14, 12, 12)) score_export <- sc %>% left_join(fc_roll %>% select(primary_name, pred_90d), by = "primary_name") %>% mutate(pred_90d = replace_na(pred_90d, 0)) %>% transmute( Estimator = primary_name, `Score (0-100)` = round(composite_score, 1), Risk = risk_flag, `Win % (Used)` = win_rate_used, `Win % (Same-Mo)` = same_month_win_rate, `Win % (Lag-Adj)` = lag_win_rate, `Booked Forecast 90D` = pred_90d, `Total Booked` = total_booked, `Total Bid` = total_bid, `Avg Job Size` = avg_job_size, `Months Active` = months_active, `Booked CV` = booked_cv ) %>% arrange(desc(`Score (0-100)`)) writeData(wb, "Scorecard", score_export, startRow = 1, startCol = 1, colNames = TRUE) addStyle(wb, "Scorecard", header_style, rows = 1, cols = 1:ncol(score_export)) addStyle(wb, "Scorecard", pct_style, rows = 2:(nrow(score_export) + 1), cols = c(4, 5, 6), gridExpand = TRUE) addStyle(wb, "Scorecard", dollar_style, rows = 2:(nrow(score_export) + 1), cols = c(7, 8, 9, 10), gridExpand = TRUE) addWorksheet(wb, "Forecast Detail") setColWidths(wb, "Forecast Detail", cols = 1:6, widths = c(22, 14, 14, 14, 14, 14)) fc_export <- fc %>% arrange(primary_name, period) %>% transmute( Estimator = primary_name, Month = format(period, "%b %Y"), `Predicted Booked` = predicted_booked, `Low 80%` = lo80, `High 80%` = hi80, `Model MAE` = model_mae ) writeData(wb, "Forecast Detail", fc_export, startRow = 1, startCol = 1, colNames = TRUE) addStyle(wb, "Forecast Detail", header_style, rows = 1, cols = 1:ncol(fc_export)) addStyle(wb, "Forecast Detail", dollar_style, rows = 2:(nrow(fc_export) + 1), cols = 3:6, gridExpand = TRUE) saveWorkbook(wb, file, overwrite = TRUE) }) } ) output$db_status_text <- renderText({ paste("Database records loaded:", comma(nrow(get_data()))) }) output$preview_table <- renderDataTable({ d <- get_data() if (nrow(d) == 0) return(NULL) d %>% arrange(desc(report_date)) %>% head(5) %>% select(Estimator = primary_name, Date = report_date, Bid = amt_bid, Booked = amt_booked) }, options = list(dom = "t", searching = FALSE)) output$download_report <- downloadHandler( filename = function() { paste0("Estimator_Intelligence_", format(Sys.Date(), "%Y-%m-%d"), ".xlsx") }, content = function(file) { d <- filtered_data(); req(nrow(d) > 0) withProgress(message = "Building Excel report\u2026", value = 0.2, { wb <- createWorkbook() header_style <- createStyle(fontName="Calibri", fontSize=11, fontColour="#FFFFFF", fgFill="#2c3e50", halign="LEFT", valign="CENTER", textDecoration="bold", border="Bottom", borderColour="#27ae60") title_style <- createStyle(fontName="Calibri", fontSize=14, textDecoration="bold", fontColour="#2c3e50") dollar_style <- createStyle(numFmt="$#,##0") pct_style <- createStyle(numFmt="0.0%") addWorksheet(wb, "Summary") setColWidths(wb, "Summary", cols = 1:3, widths = c(30, 22, 22)) writeData(wb, "Summary", "Estimator Intelligence Report", startRow = 1, startCol = 1) addStyle(wb, "Summary", title_style, rows = 1, cols = 1) sel_label <- if (is.null(isolate(input$estimatorSelect)) || length(isolate(input$estimatorSelect)) == 0) "All Estimators" else paste(isolate(input$estimatorSelect), collapse = ", ") meta <- data.frame( Field = c("Reporting Period", "Estimator Scope", "Win Rate Lag", "Generated"), Value = c(paste(format(isolate(input$dateRange[1]), "%b %d %Y"), "\u2014", format(isolate(input$dateRange[2]), "%b %d %Y")), sel_label, paste(isolate(input$win_lag), "month(s)"), format(Sys.Date(), "%B %d, %Y")), stringsAsFactors = FALSE) writeData(wb, "Summary", meta, startRow = 3, startCol = 1, colNames = FALSE) kpi_data <- data.frame( Metric = c("Total Bid Volume", "Total Booked Revenue", "Dollar Win Rate", "Active Estimators"), Value = c(dollar(sum(d$amt_bid, na.rm = TRUE), accuracy = 1), dollar(sum(d$amt_booked, na.rm = TRUE), accuracy = 1), percent(sum(d$amt_booked, na.rm = TRUE) / sum(d$amt_bid, na.rm = TRUE), accuracy = 0.1), as.character(length(unique(d$primary_name)))), stringsAsFactors = FALSE) writeData(wb, "Summary", kpi_data, startRow = 9, startCol = 1, colNames = TRUE) addStyle(wb, "Summary", header_style, rows = 9, cols = 1:2) addWorksheet(wb, "Leaderboard") setColWidths(wb, "Leaderboard", cols = 1:7, widths = c(22, 18, 18, 10, 10, 14, 14)) lb_raw <- d %>% group_by(Estimator = primary_name) %>% summarise(`Total Bids` = sum(amt_bid, na.rm = TRUE), `Total Revenue` = sum(amt_booked, na.rm = TRUE), `Jobs Bid` = sum(qty_bid, na.rm = TRUE), `Jobs Won` = sum(qty_booked, na.rm = TRUE), `Win Rate` = `Total Revenue` / `Total Bids`, `Avg Job Size` = `Total Bids` / `Jobs Bid`, .groups = "drop") %>% arrange(desc(`Total Revenue`)) writeData(wb, "Leaderboard", lb_raw, startRow = 1, colNames = TRUE) addStyle(wb, "Leaderboard", header_style, rows = 1, cols = 1:7) addStyle(wb, "Leaderboard", dollar_style, rows = 2:(nrow(lb_raw) + 1), cols = c(2, 3, 7), gridExpand = TRUE) addStyle(wb, "Leaderboard", pct_style, rows = 2:(nrow(lb_raw) + 1), cols = 6, gridExpand = TRUE) setProgress(0.6) addWorksheet(wb, "Monthly Detail") setColWidths(wb, "Monthly Detail", cols = 1:7, widths = c(22, 14, 14, 10, 14, 10, 14)) monthly_raw <- d %>% arrange(primary_name, report_date) %>% mutate(Date = format(report_date, "%b %Y"), Win_Rate = ifelse(amt_bid > 0, amt_booked / amt_bid, 0)) %>% select(Estimator = primary_name, Date, `Bid ($)` = amt_bid, `Jobs Bid` = qty_bid, `Booked ($)` = amt_booked, `Jobs Won` = qty_booked, `Win Rate` = Win_Rate) writeData(wb, "Monthly Detail", monthly_raw, startRow = 1, colNames = TRUE) addStyle(wb, "Monthly Detail", header_style, rows = 1, cols = 1:7) addStyle(wb, "Monthly Detail", dollar_style, rows = 2:(nrow(monthly_raw) + 1), cols = c(3, 5), gridExpand = TRUE) addStyle(wb, "Monthly Detail", pct_style, rows = 2:(nrow(monthly_raw) + 1), cols = 7, gridExpand = TRUE) setProgress(0.9) saveWorkbook(wb, file, overwrite = TRUE) }) } ) # ── IMS DATA FETCH ──────────────────────────────────────────────────────────── get_ims_data <- reactive({ input$refresh # ties into the existing Refresh button # Check the table exists before querying if (!dbExistsTable(db_pool, "bid___follow_up")) return(data.frame()) dbGetQuery(db_pool, " SELECT fu.bidfuid, fu.bidprojectid, fu.projectname, fu.bidfudate AS follow_up_date, fu.bidfucontractor AS contractor, fu.bidfucontact AS contact, fu.bidfunotes AS notes, fu.bidfucreatedby AS estimator_raw, -- Normalise Scott W. Hutchings β†’ Scott Hutchings to match estimators table CASE WHEN LOWER(TRIM(fu.bidfucreatedby)) = 'scott w. hutchings' THEN 'Scott Hutchings' ELSE TRIM(fu.bidfucreatedby) END AS estimator FROM bid___follow_up fu WHERE fu.bidfucreatedby IS NOT NULL AND fu.bidfucreatedby != '' AND fu.bidfudate IS NOT NULL ORDER BY fu.bidfudate DESC ") }) # ── POPULATE CI FILTER DROPDOWNS ────────────────────────────────────────────── observe({ df <- get_ims_data() if (nrow(df) == 0) return() estimators <- sort(unique(df$estimator)) contractors <- sort(unique(df$contractor[!is.na(df$contractor) & df$contractor != ""])) updatePickerInput(session, "ci_estimator", choices = estimators) updatePickerInput(session, "ci_contractor", choices = contractors) # Default date range to full span of the data min_dt <- min(df$follow_up_date, na.rm = TRUE) max_dt <- max(df$follow_up_date, na.rm = TRUE) updateDateRangeInput(session, "ci_date_range", start = min_dt, end = max_dt) }) # ── FILTERED IMS DATA ───────────────────────────────────────────────────────── ci_filtered <- reactive({ df <- get_ims_data() if (nrow(df) == 0) return(df) # Date filter req(input$ci_date_range) df <- df %>% filter(follow_up_date >= input$ci_date_range[1], follow_up_date <= input$ci_date_range[2]) # Estimator filter sel_est <- input$ci_estimator if (!is.null(sel_est) && length(sel_est) > 0) df <- df %>% filter(estimator %in% sel_est) # Contractor filter sel_con <- input$ci_contractor if (!is.null(sel_con) && length(sel_con) > 0) df <- df %>% filter(contractor %in% sel_con) df }) # ── KPIs ───────────────────────────────────────────────────────────────────── output$ci_kpi_followups <- renderValueBox({ df <- ci_filtered() valueBox( comma(nrow(df)), "Total Follow-Ups", icon = icon("phone"), color = "blue" ) }) output$ci_kpi_contractors <- renderValueBox({ df <- ci_filtered() n <- length(unique(df$contractor[!is.na(df$contractor)])) valueBox( comma(n), "Unique Contractors", icon = icon("building"), color = "green" ) }) output$ci_kpi_top_est <- renderValueBox({ df <- ci_filtered() if (nrow(df) == 0) return(valueBox("β€”", "Most Active Estimator", icon = icon("user"), color = "yellow")) top <- df %>% count(estimator, sort = TRUE) %>% slice(1) valueBox( top$estimator, paste0("Most Active (", comma(top$n), " follow-ups)"), icon = icon("user"), color = "yellow" ) }) output$ci_kpi_date_range <- renderValueBox({ df <- ci_filtered() if (nrow(df) == 0) return(valueBox("β€”", "Data Span", icon = icon("calendar"), color = "purple")) min_dt <- min(df$follow_up_date, na.rm = TRUE) max_dt <- max(df$follow_up_date, na.rm = TRUE) valueBox( paste0(format(min_dt, "%b %Y"), " β€” ", format(max_dt, "%b %Y")), "Follow-Up Date Span", icon = icon("calendar"), color = "purple" ) }) # ── TOP CONTRACTORS BAR CHART ───────────────────────────────────────────────── output$ci_contractor_chart <- renderPlotly({ df <- ci_filtered() validate(need(nrow(df) > 0, "No follow-up data for selected filters.")) top_n <- input$ci_top_n metric <- input$ci_contractor_metric if (metric == "estimators") { agg <- df %>% filter(!is.na(contractor), contractor != "") %>% group_by(contractor) %>% summarise(value = n_distinct(estimator), .groups = "drop") %>% arrange(desc(value)) %>% slice_head(n = top_n) %>% mutate(contractor = fct_reorder(contractor, value)) y_label <- "# Estimators" bar_col <- "#3498db" } else { agg <- df %>% filter(!is.na(contractor), contractor != "") %>% count(contractor, name = "value") %>% arrange(desc(value)) %>% slice_head(n = top_n) %>% mutate(contractor = fct_reorder(contractor, value)) y_label <- "Follow-Up Contacts" bar_col <- "#2c3e50" } p <- ggplot(agg, aes(x = contractor, y = value, text = paste0("<b>", contractor, "</b><br>", y_label, ": ", comma(value)))) + geom_col(fill = bar_col, width = 0.65) + coord_flip() + scale_y_continuous(labels = comma_format()) + labs(x = "", y = y_label) + theme_minimal() + theme(panel.grid.major.y = element_blank(), axis.text.y = element_text(size = 10)) ggplotly(p, tooltip = "text") }) # ── FOLLOW-UP ACTIVITY TIMELINE ─────────────────────────────────────────────── output$ci_timeline_chart <- renderPlotly({ df <- ci_filtered() validate(need(nrow(df) > 0, "No follow-up data for selected filters.")) grain <- input$ci_timeline_grain mode <- input$ci_timeline_mode df <- df %>% mutate(period = floor_date(follow_up_date, grain)) if (mode == "ind") { agg <- df %>% group_by(period, estimator) %>% summarise(n = n(), .groups = "drop") p <- ggplot(agg, aes(x = period, y = n, color = estimator, text = paste0("<b>", format(period, if (grain == "month") "%b %Y" else "Q%q %Y"), "</b><br>", estimator, "<br>Follow-ups: ", comma(n)))) + geom_line(linewidth = 0.9) + geom_point(size = 2) + scale_color_brewer(palette = "Paired") + scale_y_continuous(labels = comma_format()) + labs(x = "", y = "Follow-Up Count", color = "") + theme_minimal() + theme(legend.position = "bottom") } else { agg <- df %>% group_by(period) %>% summarise(n = n(), .groups = "drop") p <- ggplot(agg, aes(x = period, y = n, group = 1, text = paste0("<b>", format(period, if (grain == "month") "%b %Y" else "Q%q %Y"), "</b><br>Follow-ups: ", comma(n)))) + geom_line(color = "#2c3e50", linewidth = 1) + geom_point(color = "#2c3e50", size = 2.5) + geom_area(fill = "#2c3e50", alpha = 0.08) + scale_y_continuous(labels = comma_format()) + labs(x = "", y = "Follow-Up Count") + theme_minimal() } ggplotly(p, tooltip = "text") }) # ── ESTIMATOR Γ— CONTRACTOR MATRIX ───────────────────────────────────────────── output$ci_matrix_chart <- renderPlotly({ df <- ci_filtered() validate(need(nrow(df) > 0, "No follow-up data for selected filters.")) # Build matrix β€” top 12 contractors by volume to keep readable top_contractors <- df %>% filter(!is.na(contractor), contractor != "") %>% count(contractor, sort = TRUE) %>% slice_head(n = 12) %>% pull(contractor) mat <- df %>% filter(contractor %in% top_contractors) %>% group_by(estimator, contractor) %>% summarise(n = n(), .groups = "drop") p <- ggplot(mat, aes(x = estimator, y = contractor, fill = n, text = paste0("<b>", estimator, " \u00d7 ", contractor, "</b><br>Follow-ups: ", n))) + geom_tile(color = "white", linewidth = 0.5) + scale_fill_gradient(low = "#eaf4fb", high = "#1a5276", name = "Follow-ups") + labs(x = "", y = "") + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 9), axis.text.y = element_text(size = 9), panel.grid = element_blank()) ggplotly(p, tooltip = "text") }) # ── AWARD SIGNAL FEED ───────────────────────────────────────────────────────── # ── NLP SCORES DATA ─────────────────────────────────────────────────────────── get_nlp_scores <- reactive({ input$refresh if (!dbExistsTable(db_pool, "follow_up_scores")) return(data.frame()) dbGetQuery(db_pool, " SELECT id, follow_up_date, estimator, contractor, notes, rule_score, award_probability, signal_tier, scored_at FROM follow_up_scores ORDER BY award_probability DESC, follow_up_date DESC ") }) # ── NLP KPI β€” override ci_kpi_top_est with award signal count ──────────────── # Add this additional KPI box to the UI row or repurpose an existing one. # This is a supplementary output β€” wire to a new valueBoxOutput("ci_kpi_signals") output$ci_kpi_signals <- renderValueBox({ df <- get_nlp_scores() n <- nrow(df[df$award_probability >= 0.75, ]) valueBox( comma(n), "High-Confidence Award Signals", icon = icon("trophy"), color = "green" ) }) # ── NLP-POWERED AWARD SIGNAL FEED ───────────────────────────────────────────── output$ci_award_feed <- renderDataTable({ df <- get_nlp_scores() df_filter <- ci_filtered() # reuse existing estimator/date/contractor filters validate(need(nrow(df) > 0, "NLP scores not found. Run nlp_followup_scorer.py first.")) # Apply same filters as the rest of the tab if (!is.null(input$ci_estimator) && length(input$ci_estimator) > 0) df <- df %>% filter(estimator %in% input$ci_estimator) if (!is.null(input$ci_date_range)) { df <- df %>% filter(as.Date(follow_up_date) >= input$ci_date_range[1], as.Date(follow_up_date) <= input$ci_date_range[2]) } if (!is.null(input$ci_contractor) && length(input$ci_contractor) > 0) df <- df %>% filter(contractor %in% input$ci_contractor) # Keep only meaningful signals df <- df %>% filter(award_probability >= 0.50) validate(need(nrow(df) > 0, "No award signals for selected filters.")) # Format for display df %>% arrange(desc(award_probability), desc(follow_up_date)) %>% mutate( Date = format(as.Date(follow_up_date), "%b %d, %Y"), Estimator = estimator, Contractor = contractor, Score = paste0(round(award_probability * 100), "%"), Tier = signal_tier, Note = ifelse( nchar(notes) > 220, paste0(substr(notes, 1, 220), "…"), notes ) ) %>% select(Date, Estimator, Contractor, Score, Tier, Note) %>% datatable( options = list( pageLength = 8, scrollX = TRUE, dom = "ftp", order = list(list(3, "desc")), # sort by Score desc columnDefs = list( list(width = "60px", targets = 0), # Date list(width = "90px", targets = 1), # Estimator list(width = "120px", targets = 2), # Contractor list(width = "50px", targets = 3), # Score list(width = "110px", targets = 4), # Tier list(width = "auto", targets = 5) # Note ) ), rownames = FALSE ) %>% formatStyle( "Tier", backgroundColor = styleEqual( c("Strong Award Signal", "Likely Award", "Mild Positive", "Neutral / Pending"), c("#d4efdf", "#d6eaf8", "#fef9e7", "#f2f3f4") ), fontWeight = styleEqual( c("Strong Award Signal"), c("bold") ) ) }) # ── NLP SCORE DISTRIBUTION CHART (optional addition to UI) ─────────────────── # Shows the distribution of award probabilities across all notes. output$ci_nlp_dist <- renderPlotly({ df <- get_nlp_scores() validate(need(nrow(df) > 0, "No NLP scores available.")) # Apply ALL filters β€” estimator, date, contractor if (!is.null(input$ci_estimator) && length(input$ci_estimator) > 0) df <- df %>% filter(estimator %in% input$ci_estimator) if (!is.null(input$ci_date_range)) df <- df %>% filter(as.Date(follow_up_date) >= input$ci_date_range[1], as.Date(follow_up_date) <= input$ci_date_range[2]) if (!is.null(input$ci_contractor) && length(input$ci_contractor) > 0) df <- df %>% filter(contractor %in% input$ci_contractor) tier_colors <- c( "Strong Award Signal" = "#1e8449", "Likely Award" = "#2e86c1", "Mild Positive" = "#d4ac0d", "Neutral / Pending" = "#808b96" ) agg <- df %>% count(signal_tier, estimator) %>% mutate(signal_tier = factor(signal_tier, levels = c("Strong Award Signal", "Likely Award", "Mild Positive", "Neutral / Pending"))) p <- ggplot(agg, aes( x = estimator, y = n, fill = signal_tier, text = paste0("<b>", estimator, "</b><br>", signal_tier, ": ", n, " notes") )) + geom_col(position = "stack", width = 0.65) + scale_fill_manual(values = tier_colors, name = "Signal Tier") + labs(x = "", y = "Note Count") + theme_minimal() + theme( axis.text.x = element_text(angle = 35, hjust = 1, size = 9), legend.position = "bottom", legend.title = element_text(size = 9) ) ggplotly(p, tooltip = "text") }) # ── DATA FETCH ──────────────────────────────────────────────────────────────── get_revenue_data <- reactive({ input$refresh if (!dbExistsTable(db_pool, "pm___project_file")) return(data.frame()) dbGetQuery(db_pool, " SELECT pm.pmprojectid, pm.projectid, pm.projectname, pm.projectmanager, pm.projectdatecreated, pm.finalcompletiondate, pm.substantialcompletiondate, pm.billingcompletepercent, pm.billingstatus, pf.contractor, pf.contractamt, pf.contractdate, -- Normalise Scott W. Hutchings to match estimators table CASE WHEN TRIM(pm.projectmanager) = 'Scott W. Hutchings' THEN 'Scott Hutchings' ELSE TRIM(pm.projectmanager) END AS manager_clean FROM pm___project_file pm JOIN project_file pf ON pm.projectid::text = pf.projectid::text WHERE pm.projectmanager IS NOT NULL AND pm.projectmanager != '' AND pm.projectmanager != 'Unassigned Project' AND pf.contractamt > 0 ORDER BY pm.projectdatecreated DESC ") }) # ── POPULATE FILTERS ────────────────────────────────────────────────────────── observe({ df <- get_revenue_data() if (nrow(df) == 0) return() managers <- sort(unique(df$manager_clean)) contractors <- sort(unique(df$contractor[!is.na(df$contractor) & df$contractor != ""])) updatePickerInput(session, "ri_manager", choices = managers) updatePickerInput(session, "ri_contractor", choices = contractors) min_dt <- as.Date(min(df$projectdatecreated, na.rm = TRUE)) max_dt <- as.Date(max(df$projectdatecreated, na.rm = TRUE)) updateDateRangeInput(session, "ri_date_range", start = min_dt, end = max_dt) }) # ── FILTERED DATA ───────────────────────────────────────────────────────────── ri_filtered <- reactive({ df <- get_revenue_data() if (nrow(df) == 0) return(df) req(input$ri_date_range) df <- df %>% filter(as.Date(projectdatecreated) >= input$ri_date_range[1], as.Date(projectdatecreated) <= input$ri_date_range[2]) if (!is.null(input$ri_manager) && length(input$ri_manager) > 0) df <- df %>% filter(manager_clean %in% input$ri_manager) if (!is.null(input$ri_contractor) && length(input$ri_contractor) > 0) df <- df %>% filter(contractor %in% input$ri_contractor) df }) # ── KPIs ───────────────────────────────────────────────────────────────────── output$ri_kpi_total_value <- renderValueBox({ df <- ri_filtered() val <- sum(df$contractamt, na.rm = TRUE) valueBox( paste0("$", format(round(val / 1e6, 2), big.mark = ","), "M"), "Total Contract Value", icon = icon("dollar-sign"), color = "green" ) }) output$ri_kpi_total_projects <- renderValueBox({ df <- ri_filtered() valueBox( comma(nrow(df)), "Executed Projects", icon = icon("building"), color = "blue" ) }) output$ri_kpi_completed <- renderValueBox({ df <- ri_filtered() n <- sum(!is.na(df$finalcompletiondate)) valueBox( comma(n), "Completed Projects", icon = icon("check-circle"), color = "yellow" ) }) output$ri_kpi_avg_contract <- renderValueBox({ df <- ri_filtered() avg <- mean(df$contractamt, na.rm = TRUE) valueBox( paste0("$", format(round(avg / 1e3, 1), big.mark = ","), "K"), "Avg Contract Value", icon = icon("chart-line"), color = "purple" ) }) # ── EXECUTED REVENUE CHART ──────────────────────────────────────────────────── output$ri_revenue_chart <- renderPlotly({ df <- ri_filtered() validate(need(nrow(df) > 0, "No project data for selected filters.")) agg <- df %>% group_by(manager_clean) %>% summarise( total_value = sum(contractamt, na.rm = TRUE), n_projects = n(), .groups = "drop" ) %>% arrange(desc(total_value)) %>% mutate(manager_clean = fct_reorder(manager_clean, total_value)) p <- ggplot(agg, aes( x = manager_clean, y = total_value, text = paste0( "<b>", manager_clean, "</b><br>", "Contract Value: $", format(round(total_value), big.mark = ","), "<br>", "Projects: ", n_projects ) )) + geom_col(fill = "#1a5276", width = 0.65) + coord_flip() + scale_y_continuous( labels = function(x) paste0("$", format(x / 1e6, big.mark = ","), "M") ) + labs(x = "", y = "Total Contract Value") + theme_minimal() + theme(panel.grid.major.y = element_blank(), axis.text.y = element_text(size = 10)) ggplotly(p, tooltip = "text") }) # ── PROJECT COMPLETION PIPELINE ─────────────────────────────────────────────── output$ri_pipeline_chart <- renderPlotly({ df <- ri_filtered() validate(need(nrow(df) > 0, "No project data for selected filters.")) metric <- input$ri_pipeline_metric agg <- df %>% mutate(status = ifelse(!is.na(finalcompletiondate), "Completed", "In Progress")) %>% group_by(manager_clean, status) %>% summarise( count = n(), value = sum(contractamt, na.rm = TRUE), .groups = "drop" ) y_col <- if (metric == "count") "count" else "value" y_label <- if (metric == "count") "Projects" else "Contract Value ($)" p <- ggplot(agg, aes( x = manager_clean, y = .data[[y_col]], fill = status, text = paste0( "<b>", manager_clean, "</b><br>", status, "<br>", if (metric == "count") paste0("Projects: ", count) else paste0("Value: $", format(round(value), big.mark = ",")) ) )) + geom_col(position = "stack", width = 0.65) + coord_flip() + scale_fill_manual( values = c("Completed" = "#1e8449", "In Progress" = "#85c1e9"), name = "" ) + scale_y_continuous( labels = if (metric == "value") function(x) paste0("$", format(x / 1e6, big.mark = ","), "M") else comma_format() ) + labs(x = "", y = y_label) + theme_minimal() + theme(panel.grid.major.y = element_blank(), legend.position = "bottom", axis.text.y = element_text(size = 10)) ggplotly(p, tooltip = "text") }) # ── GC REVENUE CONCENTRATION ────────────────────────────────────────────────── output$ri_gc_chart <- renderPlotly({ df <- ri_filtered() validate(need(nrow(df) > 0, "No project data for selected filters.")) top_n <- input$ri_top_gc agg <- df %>% filter(!is.na(contractor), contractor != "") %>% group_by(contractor) %>% summarise( total_value = sum(contractamt, na.rm = TRUE), n_projects = n(), n_managers = n_distinct(manager_clean), .groups = "drop" ) %>% arrange(desc(total_value)) %>% slice_head(n = top_n) %>% mutate(contractor = fct_reorder(contractor, total_value)) p <- ggplot(agg, aes( x = contractor, y = total_value, text = paste0( "<b>", contractor, "</b><br>", "Contract Value: $", format(round(total_value), big.mark = ","), "<br>", "Projects: ", n_projects, "<br>", "Managers: ", n_managers ) )) + geom_col(fill = "#2e86c1", width = 0.65) + coord_flip() + scale_y_continuous( labels = function(x) paste0("$", format(x / 1e6, big.mark = ","), "M") ) + labs(x = "", y = "Total Contract Value") + theme_minimal() + theme(panel.grid.major.y = element_blank(), axis.text.y = element_text(size = 9)) ggplotly(p, tooltip = "text") }) # ── PROJECT DETAIL TABLE ────────────────────────────────────────────────────── output$ri_project_table <- renderDataTable({ df <- ri_filtered() validate(need(nrow(df) > 0, "No project data for selected filters.")) df %>% arrange(desc(contractamt)) %>% mutate( Manager = manager_clean, Project = projectname, GC = contractor, Contract = paste0("$", format(round(contractamt), big.mark = ",")), Created = format(as.Date(projectdatecreated), "%b %Y"), Completed = ifelse( !is.na(finalcompletiondate), format(as.Date(finalcompletiondate), "%b %Y"), "In Progress" ) ) %>% select(Manager, Project, GC, Contract, Created, Completed) %>% datatable( options = list( pageLength = 10, scrollX = TRUE, dom = "ftp", order = list(list(3, "desc")) ), rownames = FALSE ) %>% formatStyle( "Completed", color = styleEqual( c("In Progress"), c("#2e86c1") ) ) }) }
# --- server.R ---
function(input, output, session) {
data_trigger <- reactiveVal(0)
# ── 1. FAST DATA FETCHING (VIA POOL) ──────────────────────────────────────
get_data <- reactive({
input$refresh
data_trigger()
if (!dbExistsTable(db_pool, "monthly_sales")) return(data.frame())
df <- dbGetQuery(db_pool, "
SELECT e.primary_name, s.*, EXTRACT(YEAR FROM s.report_date) AS year_num
FROM monthly_sales s
JOIN estimators e ON s.estimator_id = e.id
")
df
})
# ── 2. UPLOAD ─────────────────────────────────────────────────────────────
observeEvent(input$process_upload, {
req(input$file1)
prog <- shiny::Progress$new(message = "Processing...", value = 0.2)
on.exit(prog$close())
tryCatch({
raw <- read_csv(input$file1$datapath, show_col_types = FALSE)
clean <- raw %>%
mutate(report_date = ym(MoYear),
across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .))),
clean_name = case_when(
Estimator == "Scott W. Hutchings" ~ "Scott Hutchings",
Estimator %in% c("SW", "SH") ~ "Scott Hutchings",
TRUE ~ Estimator))
dbExecute(db_pool, "CREATE TABLE IF NOT EXISTS estimators (id SERIAL PRIMARY KEY, primary_name VARCHAR(100) UNIQUE NOT NULL, active BOOLEAN DEFAULT TRUE);")
dbExecute(db_pool, "CREATE TABLE IF NOT EXISTS monthly_sales (id SERIAL PRIMARY KEY, estimator_id INTEGER REFERENCES estimators(id), report_date DATE NOT NULL, amt_bid NUMERIC(15,2), qty_bid INTEGER, amt_booked NUMERIC(15,2), qty_booked INTEGER, UNIQUE(estimator_id, report_date));")
for (nm in unique(clean$clean_name))
tryCatch(dbExecute(db_pool, "INSERT INTO estimators (primary_name) VALUES ($1) ON CONFLICT DO NOTHING", params = list(nm)), error = function(e) NULL)
est_map <- dbGetQuery(db_pool, "SELECT id, primary_name FROM estimators")
upload <- clean %>%
left_join(est_map, by = c("clean_name" = "primary_name")) %>%
select(estimator_id = id, report_date, amt_bid = AmtBid, qty_bid = QtyBid, amt_booked = AmtBooked, qty_booked = QtyBooked)
dbWriteTable(db_pool, "staging_sales", upload, overwrite = TRUE, temporary = TRUE)
n <- dbExecute(db_pool, "
INSERT INTO monthly_sales (estimator_id,report_date,amt_bid,qty_bid,amt_booked,qty_booked)
SELECT estimator_id,report_date,amt_bid,qty_bid,amt_booked,qty_booked FROM staging_sales
ON CONFLICT (estimator_id, report_date) DO NOTHING;")
dbExecute(db_pool, "DROP TABLE IF EXISTS staging_sales")
showNotification(paste("Success! Added/Merged", n, "records."), type = "message")
data_trigger(data_trigger() + 1)
}, error = function(e) showNotification(paste("Error:", e$message), type = "error"))
})
# ── FILTERING ─────────────────────────────────────────────────────────────
filtered_data <- reactive({
req(input$dateRange)
df <- get_data()
if (nrow(df) == 0) return(df[0, ])
df <- df %>% filter(report_date >= input$dateRange[1], report_date <= input$dateRange[2])
sel <- input$estimatorSelect
if (!is.null(sel) && length(sel) > 0) df <- df %>% filter(primary_name %in% sel)
df
})
lag_win_rate <- reactive({
df <- filtered_data()
lag <- input$win_lag
if (nrow(df) == 0 || lag == 0) return(NULL)
all_data <- get_data()
max_data_date <- max(all_data$report_date, na.rm = TRUE)
cutoff_date <- max_data_date %m-% months(lag)
bookings_lookup <- df %>% select(primary_name, report_date, amt_booked, qty_booked) %>% rename(book_date = report_date)
bid_months <- df %>% select(primary_name, report_date, amt_bid, qty_bid) %>% distinct() %>% filter(report_date <= cutoff_date)
if (nrow(bid_months) == 0) return(NULL)
result <- bid_months %>%
rowwise() %>%
mutate(
lag_booked_amt = sum(bookings_lookup$amt_booked[bookings_lookup$primary_name == .data$primary_name & bookings_lookup$book_date > report_date & bookings_lookup$book_date <= report_date %m+% months(lag)], na.rm = TRUE),
lag_booked_qty = sum(bookings_lookup$qty_booked[bookings_lookup$primary_name == .data$primary_name & bookings_lookup$book_date > report_date & bookings_lookup$book_date <= report_date %m+% months(lag)], na.rm = TRUE)
) %>% ungroup()
result
})
ceo_scorecard <- reactive({
d <- filtered_data()
validate(need(nrow(d) > 0, "No data in selected range."))
monthly <- d %>%
mutate(period = floor_date(report_date, "month")) %>%
group_by(primary_name, period) %>%
summarise(
amt_bid = sum(amt_bid, na.rm = TRUE),
amt_booked = sum(amt_booked, na.rm = TRUE),