Patron Usage Analysis

A request for statistics, not for analysis

The ask was specific and operational: produce the patron count and daily shelf-reading log statistics for the current academic year, provide a data breakdown for each, and build a spreadsheet showing attendance by month year over year across three years — following the layout of the sample breakdowns supplied. A deeper look at study room usage was added alongside.

Delivered as asked. But a table of monthly totals answers "how many" without answering "so what," and the underlying data supported a resource question the brief had not thought to ask. That became the analysis.

What the data would not do on its own

• Uneven collection intensity. Reporting intervals per month ranged from 9 to 177. Raw monthly totals conflate more patrons with more counting — the single most important trap in this dataset.
• A missing field in two of three years. The earlier survey forms carried no explicit day-of-week field, so weekday analysis had to be derived from submission timestamps.
• An incomplete first year. No August submissions exist for the earliest year, making that month non-comparable across the full period.
• Free-text everywhere in the shelf log. Duration recorded as "20 min", "20 minutes", "20", and "about 20 minutes"; staff names in mixed case and abbreviated forms; some bookcase numbers logged as unknown.
• No prior-year baseline for shelf condition. The log began this year, so condition data has no comparison point yet.

Approach

1. Consolidate three years into one schema Merged multi-year form exports in Power Query, reconciling field differences between survey versions.
2. Derive what the form failed to capture Reconstructed day-of-week from submission date for the two years lacking the field, then validated the derivation against the year that had both.
3. Normalize the free text Standardized duration strings, condition ratings, and staff names in the shelf log before any counting.
4. Model per-interval averages, not totals Because collection intensity varied nearly twentyfold between months, time-of-day and weekday findings are expressed as averages per reporting interval. Slots with fewer than five observations were excluded rather than reported as noise.
5. Separate the measure from the mix Split every count into computer workstation, open study, and study room use — which is where the actual finding surfaced.
6. Deliver in three formats for three audiences A narrative PDF for leadership, a workbook with a Power BI–ready flat table for whoever inherits the data, and a recorded walkthrough for anyone who would not read either.

What the analysis showed

Charts below show the real analytical shape, indexed so the peak equals 100. Absolute patron counts are institutional data and are withheld here pending approval — the pattern is the finding, and the pattern is intact.

Usage mix by academic year

Open study Study rooms Computer workstations

Demand by time of day — indexed to peak

Demand by day of week — indexed to peak

What I would do differently

• Headcounts are not occupancy. I would pair interval counts with a short observational sample to estimate how long patrons actually stay, which is what converts a count into a capacity figure.
• I recommended adding an explicit day-of-week field to the collection form. I would go further and specify the whole instrument up front — controlled dropdowns for duration and condition would have removed the entire normalization step.
• Reporting intensity is itself a variable. I normalized for it, but I did not model it — a month with 177 intervals and a month with 9 carry very different confidence, and the report does not show that difference.
• I delivered recommendations without a measurement plan. If staffing shifts to match the peak window, nothing currently in place would tell anyone whether it worked.
• The shelf log's first year is a baseline, not a trend. I flagged that in the report, but the more useful deliverable would have been a simple recurring schedule that guarantees the second year is comparable.

Other analysis in the same environment

Holdings reconciliation

Merged roughly 139,000 catalog records across two datasets using Python and openpyxl, producing a three-tab workbook separating matched records, records unique to each source, and records flagged for manual review. The design decision that mattered was refusing to auto-resolve ambiguous matches — a flagged record a person reviews is worth more than a silently wrong merge.

Shelf and barcode reconciliation

Scanned physical media against catalog records using Power Query and Python to identify discrepancies between shelf state and system state.

← All projects