Library Programming Strategic Analysis

Author

Andrew Ledet

Published

September 6, 2025

Executive Summary

This interactive analysis provides a data-driven exploration of library programming, focusing on strategic insights derived from event registration patterns, consistency, and alignment with library mission.

Core Methodology

Our analysis considers multiple dimensions beyond raw attendance: - Program Consistency - Strategic Alignment - Community Engagement Potential - Operational Efficiency

Data Preparation

Show code 
# Load required libraries
library(tidyverse)
library(janitor)
library(lubridate)
library(kableExtra)
library(plotly)
library(DT)

# Load and prepare the data
event_data <- read_csv("event_data_with_analysis.csv", 
                       col_types = cols(), 
                       na = c("", "NA", "N/A")) %>%
  clean_names() %>%
  mutate(
    # Parse event dates and times in UTC
    event_start_time = ymd_hms(event_start_time, tz = "UTC"),
    
    # Convert to local time (Central Time)
    local_start_time = with_tz(event_start_time, tzone = "America/Chicago"),
    
    # Extract local time components
    event_date = as.Date(local_start_time),
    event_year = year(event_date),
    event_month = month(event_date),
    
    # Create primary subject categorization
    primary_subject = map_chr(as.character(event_subjects), function(subjects) {
      if (is.na(subjects)) return("Uncategorized")
      
      priority_subjects <- c(
        "Books & Authors", "ESL", "Technology Classes", 
        "Business & Nonprofit", "Makerplace", "Senior Center", 
        "Genealogy", "Health & Wellness", "Arts & Culture"
      )
      
      tags <- str_split(subjects, ";")[[1]] %>% 
        str_trim() %>%
        .[!str_detect(., "^\\d+$")]
      
      for (subject in priority_subjects) {
        if (any(str_detect(tags, fixed(subject)))) {
          return(subject)
        }
      }
      
      return(tags[1] %||% "Uncategorized")
    }),
    
    # Filter for valid events
    is_valid_event = 
      !str_detect(tolower(event_title %||% ""), "child|kid|family|youth") &
      !str_detect(tolower(primary_subject), "child|kid|family|youth") &
      !str_detect(tolower(event_title %||% ""), "cancel") &
      !is.na(event_date) &
      !is.na(actual_registrations) & 
      actual_registrations > 0
  ) %>%
  filter(is_valid_event)

# Strategic categories
strategic_categories <- list(
  skill_development = c("Technology Classes", "Business & Nonprofit", "Jobs & Careers"),
  community_connection = c("Books & Authors", "Arts & Culture", "Lectures"),
  inclusive_access = c("ESL", "Senior Center", "Genealogy"),
  local_empowerment = c("Civics & Voting", "Health & Wellness")
)

# Print data overview
cat("Total events:", nrow(event_data), "\n")
Total events: 5740
Show code 
cat("Years covered:", min(event_data$event_year), "to", max(event_data$event_year), "\n")
Years covered: 2021 to 2025

Program Consistency Analysis

Show code 
# Analyze program consistency
program_consistency <- event_data %>%
  group_by(primary_subject, event_year) %>%
  summarise(
    yearly_events = n(),
    avg_registrations = mean(actual_registrations),
    median_registrations = median(actual_registrations),
    registration_variability = sd(actual_registrations) / mean(actual_registrations),
    .groups = 'drop'
  ) %>%
  group_by(primary_subject) %>%
  summarise(
    total_years_active = n_distinct(event_year),
    avg_yearly_events = mean(yearly_events),
    avg_registrations = mean(avg_registrations),
    consistency_score = 1 / (1 + mad(yearly_events)), # Mean Absolute Deviation
    .groups = 'drop'
  ) %>%
  mutate(
    program_stability = case_when(
      consistency_score > 0.8 ~ "Highly Stable",
      consistency_score > 0.5 ~ "Moderately Stable",
      TRUE ~ "Needs Review"
    )
  ) %>%
  arrange(program_stability, desc(avg_registrations))

# Interactive table with sorting
datatable(program_consistency, 
          options = list(pageLength = 10),
          caption = "Program Consistency and Stability Analysis") %>%
  formatRound(columns = c('avg_yearly_events', 'avg_registrations', 'consistency_score'), digits = 2)

Strategic Subject Categorization and Performance

Show code 
# Categorize and analyze strategic alignment
subject_strategic_analysis <- program_consistency %>%
  mutate(
    strategic_category = case_when(
      primary_subject %in% strategic_categories$skill_development ~ "Skill Development",
      primary_subject %in% strategic_categories$community_connection ~ "Community Connection",
      primary_subject %in% strategic_categories$inclusive_access ~ "Inclusive Access",
      primary_subject %in% strategic_categories$local_empowerment ~ "Local Empowerment",
      TRUE ~ "Other"
    ),
    strategic_alignment = case_when(
      avg_registrations > 20 ~ "High Alignment",
      avg_registrations > 10 ~ "Moderate Alignment",
      TRUE ~ "Low Alignment"
    )
  )

# Temporal trend analysis
temporal_trends <- event_data %>%
  group_by(primary_subject, event_year) %>%
  summarise(
    yearly_registrations = sum(actual_registrations),
    .groups = 'drop'
  ) %>%
  group_by(primary_subject) %>%
  summarise(
    trend_direction = tryCatch({
      coef(lm(yearly_registrations ~ event_year))[2]
    }, error = function(e) 0),
    trend_category = case_when(
      trend_direction > 0 ~ "Growing",
      trend_direction < 0 ~ "Declining",
      TRUE ~ "Stable"
    )
  )

# Comprehensive analysis
comprehensive_analysis <- subject_strategic_analysis %>%
  left_join(temporal_trends, by = "primary_subject") %>%
  mutate(
    overall_recommendation = case_when(
      strategic_alignment == "High Alignment" & trend_category == "Growing" ~ 
        "Continue and Expand",
      strategic_alignment == "Moderate Alignment" & trend_category == "Stable" ~ 
        "Review and Optimize",
      trend_category == "Declining" ~ 
        "Requires Significant Redesign",
      TRUE ~ "Monitor Closely"
    )
  )

# Interactive table of comprehensive analysis
datatable(comprehensive_analysis, 
          options = list(pageLength = 10),
          caption = "Comprehensive Strategic Programming Analysis") %>%
  formatRound(columns = c('avg_yearly_events', 'avg_registrations', 'consistency_score'), digits = 2)

Interactive Visualizations

Strategic Performance Bubble Chart

Show code 
# Prepare bubble chart data
bubble_data <- comprehensive_analysis %>%
  mutate(
    size = abs(trend_direction) * 10,
    color_group = case_when(
      strategic_alignment == "High Alignment" ~ "High Alignment",
      strategic_alignment == "Moderate Alignment" ~ "Moderate Alignment",
      TRUE ~ "Low Alignment"
    )
  )

# Create interactive bubble chart
bubble_plot <- ggplot(bubble_data, 
       aes(x = avg_registrations, 
           y = avg_yearly_events, 
           size = size,
           color = color_group,
           text = paste(
             "Subject:", primary_subject, 
             "<br>Avg Registrations:", round(avg_registrations, 2),
             "<br>Trend:", trend_category,
             "<br>Strategic Category:", strategic_category
           ))) +
  geom_point(alpha = 0.7) +
  scale_color_manual(values = c(
    "High Alignment" = "green", 
    "Moderate Alignment" = "orange", 
    "Low Alignment" = "red"
  )) +
  labs(
    title = "Library Programming Performance",
    x = "Average Registrations",
    y = "Average Yearly Events",
    color = "Strategic Alignment",
    size = "Trend Strength"
  ) +
  theme_minimal()

# Convert to interactive plotly
ggplotly(bubble_plot, tooltip = "text")

Key Insights and Strategic Recommendations

Program Stability Observations

  • Identified programs with consistent performance
  • Highlighted areas needing strategic review

Strategic Alignment

  • Mapped programs to library’s mission dimensions
  • Assessed alignment with community development goals