# =============================================================================
# MODULE: Cohort Win Rate Analysis
# File: mod_cohort_analysis.R
#
# Drop-in Shiny module for the Estimator Intelligence Dashboard.
# Measures how much work bid in a given period is eventually rewarded,
# regardless of when the booking is recorded.
#
# Usage in ui.R → tabItem(tabName = "cohort", cohortAnalysisUI("cohort"))
# Usage in server.R → cohortAnalysisServer("cohort", get_data = get_data)
#
# Pass `get_data` — the UNFILTERED pool reactive — so the module has full
# history for forward attribution windows beyond the sidebar date range.
# Expected columns: primary_name, report_date (Date), amt_bid, qty_bid,
# amt_booked, qty_booked
# =============================================================================
# -----------------------------------------------------------------------------
# UI
# -----------------------------------------------------------------------------
cohortAnalysisUI <- function(id) {
ns <- NS(id)
tagList(
# --- Control Row ---
fluidRow(
box(
title = "Cohort Parameters",
status = "primary",
solidHeader = TRUE,
width = 12,
collapsible = TRUE,
fluidRow(
column(3,
dateRangeInput(
ns("cohort_range"),
label = "Bid Cohort Window",
start = floor_date(Sys.Date() %m-% months(12), "month"),
end = floor_date(Sys.Date() %m-% months(1), "month"),
format = "M yyyy",
startview = "year"
),
p(class = "text-muted", style = "font-size:11px; margin-top:-6px;",
"Define the period during which bids were submitted.")
),
column(3,
sliderInput(
ns("forward_window"),
label = "Forward Attribution Window (months)",
min = 1,
max = 18,
value = 9,
step = 1,
ticks = FALSE,
post = " mo"
),
p(class = "text-muted", style = "font-size:11px; margin-top:-6px;",
"How many months past the cohort end to capture delayed bookings.")
),
column(3,
pickerInput(
ns("estimator_filter"),
label = "Estimators:",
choices = NULL,
multiple = TRUE,
options = list(
`actions-box` = TRUE,
`live-search` = TRUE,
`selected-text-format` = "count > 2",
`count-selected-text` = "{0} Selected",
`none-selected-text` = "All Estimators"
)
)
),
column(3,
br(),
actionButton(ns("run_cohort"), "Calculate Cohort",
icon = icon("calculator"),
class = "btn-success btn-block",
width = "100%")
)
)
)
),
# --- KPI Row ---
fluidRow(
valueBoxOutput(ns("box_total_bid"), width = 3),
valueBoxOutput(ns("box_total_booked"), width = 3),
valueBoxOutput(ns("box_win_rate_amt"), width = 3),
valueBoxOutput(ns("box_win_rate_qty"), width = 3)
),
# --- Chart Row ---
fluidRow(
box(
title = "Cohort Win Rate vs. Same-Month Win Rate by Estimator",
status = "primary",
solidHeader = TRUE,
width = 8,
plotlyOutput(ns("comparison_chart"), height = "380px"),
p(class = "text-muted", style = "font-size:11px;",
"Blue = cohort win rate (bookings attributed forward across the full window).
Orange = same-month win rate (bookings recorded within the cohort window only).
A large positive gap means delayed wins are real — they just don't show up in-period.")
),
box(
title = "Attribution Window Sensitivity",
status = "warning",
solidHeader = TRUE,
width = 4,
plotlyOutput(ns("sensitivity_chart"), height = "340px"),
p(class = "text-muted", style = "font-size:11px;",
"Aggregate cohort win rate at each forward window length.
The plateau marks your empirical bid-to-close lag.")
)
),
# --- Table Row ---
fluidRow(
box(
title = "Cohort Detail by Estimator",
status = "warning",
solidHeader = TRUE,
width = 12,
dataTableOutput(ns("cohort_table")),
p(class = "text-muted", style = "font-size:11px; margin-top:6px;",
"Lag Gap = Cohort Win Rate minus Same-Month Win Rate.
Green = bookings trailing bids (work is being rewarded, just later).
Red = same-month rate exceeds cohort rate (bookings drawing from older pipelines).")
)
),
# --- Reading Guide ---
fluidRow(
box(
title = "Reading This Analysis",
status = "info",
solidHeader = FALSE,
width = 12,
collapsible = TRUE,
collapsed = TRUE,
tags$p(HTML(
"Cohort Win Rate answers: of every dollar bid during the selected window,
how much eventually converted — even if the booking appeared in a later month?"
)),
tags$p(HTML(
"Same-Month Win Rate (existing dashboard metric) answers: in the same period
the bids were submitted, what was the ratio of bookings recorded to bids?
These two figures diverge whenever your bid-to-award cycle spans multiple months."
)),
tags$p(HTML(
"The sensitivity curve sweeps the forward window from 0 to 18 months.
The plateau point is your approximate bid-to-close lag — where additional
window months stop adding meaningful bookings. That number is useful context for
the President/CEO: it's the lag between effort and outcome in the pipeline."
))
)
)
)
}
# -----------------------------------------------------------------------------
# Server
# -----------------------------------------------------------------------------
cohortAnalysisServer <- function(id, get_data) {
moduleServer(id, function(input, output, session) {
ns <- session$ns
# --- Populate estimator picker from live pool ---
observe({
df <- get_data()
req(nrow(df) > 0)
estimators <- sort(unique(df$primary_name))
updatePickerInput(session, "estimator_filter",
choices = estimators,
selected = estimators)
})
# --- Core computation helper ---
# Returns per-estimator cohort stats for a given forward attribution window.
compute_cohort <- function(df, cohort_start, cohort_end, forward_months) {
attribution_end <- cohort_end %m+% months(forward_months)
# Bids placed within the cohort window
bids_in_cohort <- df %>%
filter(report_date >= cohort_start, report_date <= cohort_end) %>%
group_by(primary_name) %>%
summarise(
cohort_amt_bid = sum(amt_bid, na.rm = TRUE),
cohort_qty_bid = sum(qty_bid, na.rm = TRUE),
.groups = "drop"
)
# Bookings recorded from cohort_start through attribution_end (full window)
bookings_forward <- df %>%
filter(report_date >= cohort_start, report_date <= attribution_end) %>%
group_by(primary_name) %>%
summarise(
cohort_amt_booked = sum(amt_booked, na.rm = TRUE),
cohort_qty_booked = sum(qty_booked, na.rm = TRUE),
.groups = "drop"
)
# Same-month baseline: bookings only within cohort_start → cohort_end
bookings_same_mo <- df %>%
filter(report_date >= cohort_start, report_date <= cohort_end) %>%
group_by(primary_name) %>%
summarise(
sm_amt_booked = sum(amt_booked, na.rm = TRUE),
sm_qty_booked = sum(qty_booked, na.rm = TRUE),
.groups = "drop"
)
bids_in_cohort %>%
left_join(bookings_forward, by = "primary_name") %>%
left_join(bookings_same_mo, by = "primary_name") %>%
mutate(
cohort_win_rate_amt = cohort_amt_booked / cohort_amt_bid,
cohort_win_rate_qty = cohort_qty_booked / cohort_qty_bid,
sm_win_rate_amt = sm_amt_booked / cohort_amt_bid,
sm_win_rate_qty = sm_qty_booked / cohort_qty_bid,
lag_gap_amt = cohort_win_rate_amt - sm_win_rate_amt
)
}
# --- Reactive result — fires on button click ---
cohort_result <- eventReactive(input$run_cohort, {
df <- get_data()
cohort_start <- as.Date(input$cohort_range[1])
cohort_end <- as.Date(input$cohort_range[2])
validate(
need(nrow(df) > 0, "No data available."),
need(cohort_start < cohort_end, "Cohort start must be before cohort end.")
)
sel <- input$estimator_filter
if (!is.null(sel) && length(sel) > 0) {
df <- df %>% filter(primary_name %in% sel)
}
compute_cohort(df, cohort_start, cohort_end, input$forward_window)
}, ignoreNULL = FALSE)
# --- KPI Value Boxes ---
output$box_total_bid <- renderValueBox({
r <- cohort_result(); req(r)
valueBox(
value = dollar(sum(r$cohort_amt_bid, na.rm = TRUE), accuracy = 1),
subtitle = "Total Bid in Cohort",
icon = icon("file-invoice-dollar"),
color = "blue"
)
})
output$box_total_booked <- renderValueBox({
r <- cohort_result(); req(r)
valueBox(
value = dollar(sum(r$cohort_amt_booked, na.rm = TRUE), accuracy = 1),
subtitle = "Attributed Bookings",
icon = icon("check-circle"),
color = "green"
)
})
output$box_win_rate_amt <- renderValueBox({
r <- cohort_result(); req(r)
rate <- sum(r$cohort_amt_booked, na.rm = TRUE) /
sum(r$cohort_amt_bid, na.rm = TRUE)
valueBox(
value = percent(rate, accuracy = 0.1),
subtitle = HTML("Cohort Win Rate ($)
Forward-attributed"),
icon = icon("trophy"),
color = "yellow"
)
})
output$box_win_rate_qty <- renderValueBox({
r <- cohort_result(); req(r)
rate <- sum(r$cohort_qty_booked, na.rm = TRUE) /
sum(r$cohort_qty_bid, na.rm = TRUE)
valueBox(
value = percent(rate, accuracy = 0.1),
subtitle = HTML("Cohort Win Rate (Jobs)
Forward-attributed"),
icon = icon("hard-hat"),
color = "purple"
)
})
# --- Comparison Bar Chart ---
output$comparison_chart <- renderPlotly({
r <- cohort_result()
validate(need(!is.null(r) && nrow(r) > 0,
"Click 'Calculate Cohort' to run the analysis."))
plot_data <- r %>%
select(primary_name,
`Cohort (Forward)` = cohort_win_rate_amt,
`Same-Month` = sm_win_rate_amt) %>%
tidyr::pivot_longer(-primary_name,
names_to = "metric",
values_to = "win_rate") %>%
mutate(
primary_name = reorder(primary_name, win_rate, FUN = max),
win_rate = pmin(win_rate, 2) # cap display at 200%
)
p <- ggplot(plot_data,
aes(x = primary_name,
y = win_rate,
fill = metric,
text = paste0("", primary_name, "
",
metric, ": ", percent(win_rate, 0.1)))) +
geom_col(position = position_dodge(width = 0.7),
width = 0.6, alpha = 0.9) +
geom_hline(yintercept = 1, linetype = "dashed",
color = "#95a5a6", linewidth = 0.5) +
scale_y_continuous(
labels = percent_format(accuracy = 1),
expand = expansion(mult = c(0, 0.12))
) +
scale_fill_manual(values = c("Cohort (Forward)" = "#3c8dbc",
"Same-Month" = "#f39c12")) +
coord_flip() +
labs(
x = NULL,
y = "Win Rate ($ Value)",
fill = NULL,
caption = paste0(
"Cohort: ", format(as.Date(input$cohort_range[1]), "%b %Y"),
" - ", format(as.Date(input$cohort_range[2]), "%b %Y"),
" | Attribution window: +", input$forward_window, " months"
)
) +
theme_minimal() +
theme(
legend.position = "top",
panel.grid.major.y = element_blank(),
plot.caption = element_text(size = 9, color = "#95a5a6")
)
ggplotly(p, tooltip = "text") %>%
layout(legend = list(orientation = "h", x = 0, y = 1.08))
})
# --- Sensitivity Curve ---
# Sweeps forward window 0–18 months; shows plateau = empirical lag
output$sensitivity_chart <- renderPlotly({
df <- get_data()
cohort_start <- as.Date(input$cohort_range[1])
cohort_end <- as.Date(input$cohort_range[2])
req(nrow(df) > 0, cohort_start, cohort_end)
sel <- input$estimator_filter
if (!is.null(sel) && length(sel) > 0) df <- df %>% filter(primary_name %in% sel)
sensitivity <- purrr::map_dfr(0:18, function(m) {
r <- compute_cohort(df, cohort_start, cohort_end, m)
tibble(
forward_months = m,
win_rate = sum(r$cohort_amt_booked, na.rm = TRUE) /
sum(r$cohort_amt_bid, na.rm = TRUE)
)
})
current_m <- input$forward_window
p <- ggplot(sensitivity,
aes(x = forward_months, y = win_rate)) +
geom_line(color = "#3c8dbc", linewidth = 1) +
geom_point(aes(text = paste0(forward_months, " mo: ",
percent(win_rate, 0.1))),
color = "#3c8dbc", size = 2) +
geom_vline(xintercept = current_m,
linetype = "dashed", color = "#f39c12", linewidth = 0.7) +
annotate("text",
x = current_m + 0.4,
y = max(sensitivity$win_rate, na.rm = TRUE) * 0.95,
label = paste0(current_m, " mo"),
color = "#f39c12", hjust = 0, size = 3.5) +
scale_x_continuous(breaks = seq(0, 18, 3)) +
scale_y_continuous(labels = percent_format(accuracy = 1)) +
labs(x = "Forward Window (months)", y = "Aggregate Win Rate") +
theme_minimal()
ggplotly(p, tooltip = "text")
})
# --- Detail Table ---
# FIXED: Replaced Unicode "\u2013" with standard hyphens "-" inside backticks.
output$cohort_table <- renderDataTable({
r <- cohort_result()
validate(need(!is.null(r) && nrow(r) > 0,
"Run the analysis to populate this table."))
display <- r %>%
arrange(desc(cohort_win_rate_amt)) %>%
transmute(
Estimator = primary_name,
`Bid ($)` = dollar(cohort_amt_bid, accuracy = 1),
`Bid (Jobs)` = cohort_qty_bid,
`Booked - Cohort ($)` = dollar(cohort_amt_booked, accuracy = 1),
`Booked - Cohort (Jobs)` = cohort_qty_booked,
`Win Rate - Cohort` = percent(cohort_win_rate_amt, accuracy = 0.1),
`Win Rate - Same Mo.` = percent(sm_win_rate_amt, accuracy = 0.1),
`Lag Gap` = percent(lag_gap_amt, accuracy = 0.1)
)
datatable(
display,
rownames = FALSE,
options = list(
pageLength = 15,
dom = "tp",
scrollX = TRUE,
order = list(list(5, "desc"))
)
) %>%
formatStyle(
"Lag Gap",
color = styleInterval(
c(-0.001, 0.001),
c("#c0392b", "#888888", "#27ae60")
)
)
})
})
}