CPL Dashboard — Element Source & Algorithm Map

Every number on the dashboard, where it comes from, and how it's computed
Click any element to expand its source, algorithm, data flow, and the Excel column where it can be adjusted.
LIVE — scraped from CCCCO API EXCEL — from Project List tab EXHIBIT — from CustomReport JSON COMPUTED — pipeline algorithm HARDCODED — static in Python HISTORY — from kpi_history.json
60+ dashboard elements mapped
3 data sources (CCCCO API, Excel, CustomReport JSON)
8 dashboard sections
2 new Excel columns: Source & Logic + Algorithm Override

1. Headline KPI Cards

10 CARDS
▶ KPI-1 Cumulative CPL Students LIVE FALLBACK: Excel

Primary Source

live_metrics.json → raw.Students (scraped from CCCCO API)

CCCCO API → Cloudflare Worker → live_metrics.json → merge_live_metrics() → KPI Card

Fallback Source

Excel → Project 3.1 → Column R (kpi_metric)

Default: 42,620

📊 Excel: Row 3.1, Col R (kpi_metric)

Algorithm

# compute_headline_kpis() students = get_metric("3.1", "42,620") # Then overwritten by merge_live_metrics(): kpis["cumulative_students"]["value"] = metric["value"] # Value = ALL COLLEGES row, Students field

Breakdowns

Injected from live_metrics.json → metrics[0].breakdowns:

• Military — raw.MilitaryStudents

• Workforce — raw.NonMilitaryStudents

• Apprentice — raw.AprenticeStudents (API typo)

Override / Adjust

New Excel Column AF (Source & Logic): "LIVE from CCCCO API raw.Students; fallback = Project 3.1 Col R"

New Excel Column AG (Algorithm Override): Leave blank to use live data. Enter a number to override.

▶ KPI-2 Eligible Units LIVE

Primary Source

live_metrics.json → raw.Units

API endpoint: /api/potential-savings → ALL COLLEGES row

Algorithm

# merge_live_metrics() — direct pass-through kpis["eligible_units"] = { "value": metric["value"], # formatted by Worker "breakdowns": [Mil, WF, App units] }

Breakdowns

• Military — raw.MilitaryCredits

• Workforce — raw.NonMilitaryCredits

• Apprentice — raw.ApprenticeshipCredits

• Avg per Student — raw.AverageUnits

Fallback

Excel Project 3.2 Col R: 96,449

📊 Excel: Row 3.2, Col R
▶ KPI-3 Transcribed Units LIVE + EXHIBIT

Primary Source

live_metrics.json → raw.TranscribedUnits

Algorithm

# merge_live_metrics() — direct value # merge_exhibit_metrics() adds Applied Credits: if credit_distribution: bds.append({"label": "Applied Credits", "value": cd["total_applied_credits"]})

Breakdowns

• Military — raw.TranscribedMilitaryUnits

• Workforce — raw.TranscribedNonMilitaryUnits

• Apprentice — raw.TranscribedApprenticeshipUnits

• Applied Credits — from CustomReport credit_distribution (enrichment)

Fallback

Excel Project 3.2 Col R: same as eligible units

▶ KPI-4 Credit Recommendations EXHIBIT

Primary Source

CustomReport_*.json → View_ArticulatedMAPExhibits_APIDataset

Parsed by _parse_exhibits() → exhibit_data["total_credit_recs"]

Algorithm

# _parse_exhibits() counts rows in exhibits dataset total_credit_recs = len(exhibit_rows) # merge_exhibit_metrics() replaces KPI: kpis["credit_recommendations"] = { "value": total_credit_recs, "breakdowns": [CCC Collaborative, Local] }

Breakdowns

• CCC Collaborative — exhibits where Collaborative Type = CCC

• Local — individual college articulations

• Footnote — Top 5 CPL types by count

Fallback

Excel Project 2.1 Col R: 576

📊 Excel: Row 2.1, Col R
▶ KPI-5 MAP Exhibits EXHIBIT

Primary Source

CustomReport_*.json → exhibit_data["unique_exhibits"]

Algorithm

# _parse_exhibits() counts unique MAP Exhibit Name values unique_exhibits = len(set(row["MAP Exhibit Name"] for row in exhibits)) # Breakdowns split CCC Collaborative vs Local

Breakdowns

• CCC Collaborative — ccc["unique_exhibits"]

• Local — exhibit_data["local"]["unique_exhibits"]

Sub-label

"{originating_colleges} originating colleges"

▶ KPI-6 CCC Collaborative Adoption EXHIBIT

Source & Algorithm

# Colleges adopting statewide exhibits value = ccc["adopting_colleges"] # Count of unique colleges with Collaborative Type = CCC # from View_ArticulatedMAPExhibits_APIDataset

Breakdowns

• Collaborative Exhibits — ccc["unique_exhibits"]

• Collaborative Credit Recs — ccc["credit_recs"]

▶ KPI-7 Active Colleges LIVE 3-of-5 TIER ALGO

Source

live_metrics.json → computed by Cloudflare Worker from per-college API data

3-of-5 Tier Classification Algorithm

# Cloudflare Worker classifies each college (115 total) criteria = [ Students ≥ 500, # Scale of program Units ≥ 3,000, # Breadth of credit recommendations AverageUnits ≥ 5, # Per-student benefit depth TranscribedUnits/Units ≥ 25%, # Back-end follow-through TranscribedAverage ≥ 3 # Verified per-student benefit ] Leading = meets ≥ 3 of 5 criteria Inactive = Students < 10 AND Units = 0 Advancing = everything else (active, building)

Breakdowns

• Leading — tiers.leading.count (13)

• Advancing — tiers.advancing.count (82)

• Inactive — tiers.inactive.count (20)

• Active Total = Leading + Advancing (95)

Footnote

Auto-generated criteria list from Worker response

📊 Excel Fallback: Row 3.3, Col R
▶ KPI-8 Articulating Colleges EXHIBIT

Algorithm

# merge_exhibit_metrics() value = exhibit_data["articulation_colleges"] # = count of unique colleges in exhibits dataset breakdowns: Originating = exhibit_data["originating_colleges"] Adopting CCC = ccc["adopting_colleges"]

Source

CustomReport_*.json → View_ArticulatedMAPExhibits_APIDataset

Unique "College" values in exhibit rows

▶ KPI-9 Estimated Savings LIVE

Source

live_metrics.json → raw.Savings

Computed by CCCCO: eligible units × avg credit cost

Algorithm

# compute_headline_kpis() sets static default: savings = "$269M" # merge_live_metrics() overwrites with CCCCO value # Breakdowns proportional to Mil/WF/App credit ratios
▶ KPI-10 Veteran Sprint EXCEL + LIVE INJECT

Primary Source

Excel Project 4.1a Col R — Star Colleges count

Algorithm

# compute_headline_kpis() value = proj_map["4.1a"]["kpi_metric"] # merge_live_metrics() replaces placeholders: {jst_credits} → MilitaryStudents from live data {eligible_cpl} → MilitaryCredits from live data # Basic Training Credit = value + " Colleges"

Breakdowns (hybrid)

• JST Credits — {jst_credits} placeholder → replaced by live_data → cumulative_students → Military breakdown

• Basic Training Credit — 4.1a kpi_metric + " Colleges" (Excel)

• Eligible CPL — {eligible_cpl} placeholder → replaced by live_data → eligible_units → Military breakdown

▶ KPI-11 20-Year Impact LIVE

Source

live_metrics.json → raw.YearImpact

Computed by CCCCO: 20-year economic impact model (Beacon Economics)

Algorithm

# merge_live_metrics() — direct pass-through # Breakdowns proportional to credit ratios

2. Activity KPI Cards

19 CARDS

Universal Source Pattern for All 19 Activity KPIs

# build_activity_kpis() reads from Excel Project List tab: metric = Excel Col R (kpi_metric) # current value unit = Excel Col S (kpi_unit) # label (e.g. "colleges", "units") goals = Excel Cols T-AC # 5-year goals & stretch targets status = Excel Col K # project status pct = Excel Col L # progress % (0-100 or 0.0-1.0) notes = Excel Col P (update), Col AD (workplan) # Special: 4.1 = composite of 4.1a-4.1d (count of sprint projects)
Excel Project List → read_projects() → build_activity_kpis() → render_activity_kpis_html() → Dashboard Cards
▶ 1.1 MAP Platform Development EXCEL Col R

Metric

Col R value for project 1.1 — platform operational indicator

Annual Goal Current

populate_current_metrics: lambda: 1 # MAP platform operational = 1
▶ 1.2 System Integrations EXCEL Col R

Metric

Col R: count of integrations (e.g., CCCApply, eTranscript)

Annual Goal Current

populate_current_metrics: _pcount(proj_map, "1.2")
▶ 1.3 Student Portal EXCEL Col R

Metric

Col R value for project 1.3

Annual Goal Current

lambda: 1 if pct ≥ 50 else 0 # binary: portal launched?
▶ 1.4 Credential Registry Integration EXCEL Col R

Algorithm

populate_current_metrics: _pmetric_int(proj_map, "1.4") # Reads Col R, strips commas/k suffix, converts to int
▶ 2.1 Credit Recommendations EXCEL Col R

Metric

Col R: count of credit recommendations created by workgroups

Note

This is the activity-level card showing Excel value. The headline KPI-4 is overwritten by CustomReport JSON exhibit count.

▶ 2.2 Faculty Workgroups EXCEL Col R

Algorithm

populate_current_metrics: _pmetric_int(proj_map, "2.2") # Pathway templates ≈ workgroups (proxy metric)
▶ 2.3 Industry Partnerships EXCEL Col R

Algorithm

_pmetric_int(proj_map, "2.3")
▶ 2.4 AI-Ready Baccalaureate EXCEL Col R COMPUTED

Algorithm

# Links to project 5.1 (AI-Ready California) populate_current_metrics: lambda: 1 if _ppct(proj_map, "5.1") > 0 else 0
▶ 3.1 Cumulative Students (Activity) EXCEL Col R

Note

Same project row as headline KPI-1. Activity card shows Excel Col R value; headline KPI is overwritten by live data.

▶ 3.2 Eligible Units (Activity) EXCEL Col R

Note

Activity card shows Excel Col R. Headline KPI overwritten by live data.

▶ 3.3 Active Colleges (Activity) EXCEL Col R

Note

Activity card from Excel. Headline KPI overwritten by live tier classification.

▶ 3.4 CPL Savings Analysis EXCEL Col R

Algorithm

_pmetric_int(proj_map, "3.4")
▶ 3.5 Success Stories EXCEL Col R

Note

Annual goal current maps to _pmetric_int(proj_map, "3.5")

▶ 3.6 Data Reporting EXCEL Col R

Algorithm

_pmetric_int(proj_map, "3.6")
▶ 4.1 Sprints (Composite: 4.1a-4.1d) COMPOSITE

Composite Algorithm

# build_activity_kpis() aggregates sprint sub-projects: sprint_ids = ["4.1a", "4.1b", "4.1c", "4.1d"] metric = len(sprint_projects) # count of active sprints (currently 4) unit = "active sprints" pct = avg(sprint_project.pct for each sprint) # Components shown inline: 4.1a Veteran Sprint, 4.1b Apprenticeship, etc. # Goals aggregated: sum of 4.1a-d goals per year
▶ 4.2 Title 5 Regulatory Updates EXCEL Col R + HARDCODED

Algorithm

populate_current_metrics: lambda: 2 # Hardcoded: "2 Title 5 regulatory updates in progress" # Col R for display, hardcoded for annual goal current
▶ 4.3 Training & Professional Development EXCEL Col R

Algorithm

_pmetric_int(proj_map, "4.3")
▶ 4.4 Technical Assistance & College Support EXCEL Col R PROXY

Algorithm

populate_current_metrics: _pmetric_int(proj_map, "4.3") # ← uses 4.3's metric as proxy # TA & college support ≈ trainings delivered
▶ 4.5 Research & Evaluation EXCEL Col R PROXY

Algorithm

populate_current_metrics: _pmetric_int(proj_map, "5.4") # ← uses project 5.4 (RP Group survey)

3. KPI Trends Card

16 METRICS TRACKED
▶ TRENDS Daily KPI Snapshot & Sparklines HISTORY

Source

kpi_history.json — appended daily by log_daily_snapshot()

live_metrics.json + CustomReport JSON → log_daily_snapshot() → kpi_history.json → render_kpi_history_card() → Trend Card

Metrics Logged Daily

• students, students_military, students_workforce, students_apprentice

• eligible_units, transcribed_units

• savings_m (Savings / 1M), year_impact_b (YearImpact / 1B)

• active_colleges, leading_colleges, star_colleges

• credit_recs, map_exhibits, ccc_collaborative, articulating_colleges

Algorithm: Sparkline

# _sparkline_svg(): inline SVG polyline points = normalize(values, 0→height) SVG = polyline + gradient fill + end dot # _delta_badge(): % change from N days ago diff = current - previous pct = diff / previous × 100 color = green(▲) or red(▼)

Grouping

Academic quarters: Q1=Jul-Sep, Q2=Oct-Dec, Q3=Jan-Mar, Q4=Apr-Jun

Delta badges compare current entry to quarter start, 30 days ago, and 7 days ago

4. College Activity Card

PER-COLLEGE
▶ COLLEGE Per-College Activity & Military Student Counts LIVE EXHIBIT

Last Activity Per College

# _compute_college_last_activity() source: View_ArticulatedCollegeCourses_APIDataset field: "Last Submitted On" # Max datetime per college name # Filters out test colleges

Military Students Per College

# _compute_college_military_students() source: View_StudentAggregatedValues_APIDataset # Count distinct students where MilitaryCredits > 0 # Filters test colleges and potential students

Tier Classification

From live_metrics.json → tiers (Worker-computed, 3-of-5 algorithm)

Render

render_college_activity_card() — combines live tiers, last activity dates, and military counts into a sortable card

5. Workplan Trajectory Charts

2 CHARTS + TABLE
▶ CHART-G Goal Trajectory (250K target) LIVE HARDCODED BASELINES EXCEL GOALS

Algorithm: render_workplan_charts_html()

# Baselines (hardcoded — 2024 actuals) BASE_MIL = 8,248 BASE_WF = 9,181 BASE_APP = 196 # Current values (from live data sub-populations) mil_now = live → MilitaryStudents (default 21,866) wf_now = live → NonMilitaryStudents (default 21,526) app_now = live → AprenticeStudents (default 681) # Annual goal/stretch from workplan_goals (Excel Cols T-AC) # Looked up by name containing "military"/"workforce"/"apprentice" # build_trajectory(baseline, annual_vals, endpoint): if annual_vals are cumulative (ascending): pts = [(2024, baseline), (2025, midpoint), (2026+i, val)] else: # incremental proportional interpolation from baseline → endpoint # Actuals (hardcoded for historical years) mil_actual = [[2024, 8248], [2025, 18500], [2026, mil_now]] wf_actual = [[2024, 9181], [2025, 19200], [2026, wf_now]] app_actual = [[2024, 196], [2025, 300], [2026, app_now]] # Chart: solid line = actuals, dashed = projection # Target lines at 250K (goal) and 500K (stretch)

Adjustable Values

• Baselines: Hardcoded in Python (line 1655)

• 2025 actuals: Hardcoded (lines 1710-1713)

• Current values: From live data sub-populations

• Goal/Stretch endpoints: From workplan_goals (Excel)

• Targets: 250K / 500K hardcoded

Proposed Override

New Excel columns can override baselines, historical actuals, and target values. Pipeline reads overrides before building trajectories.

▶ TABLE Annual Workplan Goals Table EXCEL DERIVED

Algorithm: build_workplan_goals_from_projects()

# Derives workplan goals from Project List tab (no separate sheet needed) for each core sub-activity (1.1–4.5): goal_values = [Col T, Col V, Col X, Col Z, Col AB] # goals per year stretch_values = [Col U, Col W, Col Y, Col AA, Col AC] current_metric = Col R (kpi_metric) # placed in 2025-26 current goal_total = sum(goal_values) stretch_total = sum(stretch_values) # Special: 4.1 = aggregate sum of 4.1a-d goals per year # Percentages detected: all(0 < v < 1) → displayed as %

6. Budget Section

5 SUB-SECTIONS
▶ BUD-1 Funding Overview & Grand Total EXCEL: Budget Tab

Source: read_budget_plan()

# Excel tab: "20260324 CPL Budget" (or any tab with "budget" in name) Funding Sources: rows 6–11, cols B–M B=name, E=source, F–J=yearly budgets, L=total, M=avg Subtotals: rows 12–14 (Ops, Special Projects, Local Funding) Grand Total: row 15, col L # Stacked bar chart = % of grand total per source
▶ BUD-2 Expenditure Summary (by Category & Area) EXCEL: Budget Tab

By Category

Rows 60–67, Cols B, F–J, L, M

Top 8 expense categories with yearly and total values

By Area

Rows 70–72, Cols B, F–J, L

All expense areas with yearly and total values

▶ BUD-3 Expenditure Detail (Line Items) EXCEL: Budget Tab

Source

# Rows 19–52 in Budget tab Cols: B=description, C=category, D=type, E=source F–J = yearly budgets (5 years) L = total # Grouped by type: Operations, Special Projects, Local Funding # Displayed as collapsible accordion
▶ BUD-4 Personnel Plan EXCEL: Budget Tab

Source

Rows 87–99: B=title, F–J=FTE per year, L=total compensation

Row 100: totals (FTE in col F, total comp in col L)

Algorithm

Direct pass-through — no computation. Values from Excel cells.

▶ BUD-5 Key Factors (FTE, COLA, Indirect, Platform) EXCEL: Budget Tab AVERAGED

Algorithm

# Rows 75–81 in Budget tab factor_rows = { 75: "fte", 76: "platform_maint", 77: "platform_dev", 78: "college_funding", 79: "colleges_eligible", 80: "cola_rate", # displayed as % 81: "indirect_rate" # displayed as % } # Each factor = average of non-zero values across 5 years avg = sum(non_zero_vals) / len(non_zero_vals)

7. Vision 2030 Section

3 GOALS
▶ V2030-1 Goal 1: Expand Equitable Access COMPUTED PARTIAL HARDCODE

Algorithm

# main() → data["vision2030"]["goals"][0] current = kpis['cumulative_students']['value'] # dynamic from headline KPI target = "250,000 Californians by 2030" # hardcoded progress = 17 # hardcoded percentage # Progress bar should be: current / 250000 × 100
▶ V2030-2 Goal 2: Build Unified System HARDCODED

Values

progress = 55 # hardcoded current = "MAP at 116 colleges; Dashboard live; Portal launching"

Note

Entirely hardcoded in main(). Not derived from any data source. Would benefit from an Excel override field.

▶ V2030-3 Goal 3: Sustainable Policies & Resources HARDCODED

Values

progress = 60 # hardcoded current = "AB 123 chaptered; $20M allocated; 1,000+ trained"

Note

Entirely hardcoded. Would benefit from Excel override.

8. Exhibit Analysis Tables

6 ANALYSIS VIEWS
▶ EXH Statewide Exhibit Analysis (6 tables) EXHIBIT COMPUTED

Source & Algorithm: build_exhibit_analysis_tables()

# All from CustomReport_*.json → View_ArticulatedMAPExhibits_APIDataset 1. By College: group exhibits by Articulation College → count recs, exhibits, disciplines per college → classify CCC Collaborative vs Local vs Industry 2. By Discipline: TOP Code → CCC Discipline mapping (TOP_Code_Lookup.xlsx) → count recs, exhibits, courses, colleges per discipline 3. By CPL Type: group by CPL Type Description → count recs, exhibits, colleges per type 4. By Mode of Learning: group by CPL Mode of Learning → count recs, exhibits, colleges per mode 5. Top 50 Exhibits: sort by credit rec count DESC, take top 50 → show title, recs, courses, colleges, CPL type, discipline 6. Collaborative Analysis: CCC Collaborative vs Local vs Industry → count recs, exhibits, colleges per collaborative type

Output

Exported to statewide_data.js as window.CPL_STATEWIDE

Rendered interactively by statewide_interactive.js