# --- 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
Bids & bookings matched to same month"), 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
Set lag > 0 in sidebar"), icon = icon("clock"), color = "purple"))
lwr <- lag_win_rate()
if (is.null(lwr) || nrow(lwr) == 0) return(valueBox("—", HTML("Lag-Adjusted Win Rate
Insufficient complete windows in range"), 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
", excl_label, "")), 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)) {
"⚠️ Data insufficient for Pearson correlation."
} else if (r < 0) {
paste0("🔴 Negative Correlation (r=", round(r,2), "): More bids → lower revenue. Signals Estimator Fatigue — bid fewer, higher-probability jobs.")
} else if (r < 0.3) {
paste0("⚠️ Weak Correlation (r=", round(r,2), "): Bid quantity has minimal impact on revenue. Quality > quantity.")
} else {
paste0("🟢 Positive Correlation (r=", round(r,2), "): Higher bid volume is translating to revenue. Pipeline mechanics are healthy.")
}
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("", format(date_group, "%b %Y"), "
", primary_name, "
Booked: ", dollar(round(Val), 1), "
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("", format(date_group, "%b %Y"), "
", "Booked: ", dollar(round(Val), 1), "
Baseline Mean: ", dollar(round(BL_Mean), 1), if (show_both) paste0("
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("", format(date_group, "%b %Y"), "
", primary_name, "
Jobs: ", comma(Val, 1), "
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("", format(date_group, "%b %Y"), "
", "Jobs Booked: ", comma(Val, 1), "
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("", format(date_group, "%b %Y"), "
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("", primary_name, "
", Type, ": ", dollar(Amount, accuracy = 1),
ifelse(!is.na(Win_Label), paste0("
", 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("", Month, " ", Year, "
", 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("", primary_name, "
",
"Archetype: ", Archetype, "
",
"Pipeline Effort: ", dollar(Total_Bid, 1), "
",
"Win Rate: ", percent(Win_Rate, 0.1), "
",
"Avg Job Size: ", dollar(Avg_Job_Size, 1), "
",
"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("", x_group, "
", primary_name, "
", 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("", x_group, "
", 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, "
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, "
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, "
", 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(
"", primary_name, "
",
"Predicted 90D: ", dollar(pred_90d, accuracy = 1), "
",
"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("", contractor, "
",
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("", format(period, if (grain == "month") "%b %Y" else "Q%q %Y"),
"
", estimator, "
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("", format(period, if (grain == "month") "%b %Y" else "Q%q %Y"),
"
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("", estimator, " \u00d7 ", contractor,
"
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("", estimator, "
",
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(
"", manager_clean, "
",
"Contract Value: $", format(round(total_value), big.mark = ","), "
",
"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(
"", manager_clean, "
",
status, "
",
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(
"", contractor, "
",
"Contract Value: $", format(round(total_value), big.mark = ","), "
",
"Projects: ", n_projects, "
",
"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")
)
)
})
}