311 Toronto Data Analysis Methodology: Six‑Step Workflow Using the 1.5×IQR Outlier Rule (2010–2025)

Understanding long‑term patterns in 311 Toronto Customer‑Initiated Service Requests requires a structured, transparent, and reproducible analytical workflow. This article outlines the six‑step methodology used to produce the time‑series segmentation featured in the Toronto 311 Service Request Trends (2010–2025): Time‑Series Segmentation by FSA & City Division pillar post.

This workflow is software‑agnostic and can be executed using any database, programming language, analytics platform, or Generative AI tool. In this implementation, SQL, Excel VBA, and Microsoft Copilot were used.

1. Query Raw CSV Datafiles and Build a Unified Dataset

Sixteen CSV files containing 311 Toronto Customer‑Initiated Service Request data (downloaded from the Toronto Open Data Portal) were merged into a single dataset using SQL within an Excel VBA environment.

The SQL queries:

  • Extracted Year and Month from the Date Time Stamp
  • Selected Section and FSA
  • Aggregated the # of Records (monthly request counts)

The resulting dataset contained one summarized record per Year–Month–Section–FSA combination.

2. Prepare the Data for Time‑Series Analysis

An Excel VBA macro reshaped the dataset into a wide‑format table suitable for year‑over‑year trend analysis:

  • Each Year (2010–2025) became its own column (C–S)
  • Columns A and B stored Section and FSA
  • Each cell from C2 onward represented the number of Customer‑Initiated Service Requests for that Section–FSA in a given year

This structure enabled efficient application of the outlier‑detection algorithm.

3. Apply the Time‑Series Outlier Algorithm (1.5×IQR Rule)

A custom Excel VBA algorithm evaluated each of the 16 yearly columns independently using the 1.5×IQR Statistical Outlier Rule.

For each year, the algorithm:

  • Scanned all values in that column
  • Identified High Outliers and Low Outliers
  • Outputted the results into a new column (T–AI)

A final Time Series Trend flag in column AJ counted how many years showed a High Outlier for each Section–FSA combination, producing a 0–16 score representing the strength of upward trend activity.

4. Create Geographic Variables for Deeper Analysis

To support neighbourhood‑level insights, additional geographic variables were added:

  • Canada Post LDU (Region) — matched using publicly available postal data
  • Municipality — derived from the LDU

These fields enabled analysis at multiple geographic scales, including Section, FSA, LDU, and Municipality. Intersection variables in source 311 Toronto Customer‑Initiated Service Request CSV datafiles allow street intersection-level geographic deep dive analysis.

5. Perform Time Series Trend Analysis

Once each FSA has been evaluated for the number of years in which its annual call volume qualifies as a High Outlier under the 1.5×IQR rule, the next step is to convert those counts into a simple, interpretable time-series trend segment score ranging from 0 to 16. This score reflects how consistently an area experiences statistically unusual demand for 311 services over the 16-year period analyzed.

Scores 0–3: Stable, Predictable Workloads

FSAs with scores between 0 and 3 show call volumes that remain within a normal range for nearly the entire period. In these areas, fewer than one-quarter of the years (four or fewer) exhibit unusually high activity. These patterns are expected in a large, service-diverse city: some neighbourhoods simply generate lower or more stable demand for certain municipal services. For operational planning, these FSAs represent baseline workloads that rarely shift year-to-year.

Scores 13–16: Persistently High Outlier Activity

At the opposite end of the spectrum, FSAs scoring 13 to 16 demonstrate call volumes that are statistically, consistently, and unusually high in at least 12 of the 16 years. These areas show entrenched, long-term service pressures. Core City divisions—particularly those responsible for transportation, waste management, and other high-volume operational services—often fall into this category. These FSAs represent structural demand, not short-term fluctuations.

Scores 4–12: Variable, Operationally Significant Patterns

FSAs in the middle range (4–12) are especially important for deeper analysis. These areas frequently shift between normal and unusually high call volumes across the 16-year period. In practical terms, they are the drivers of year-to-year workload change for both the 311 Contact Centre and the operational divisions responding to service requests. Their variability makes them critical for forecasting, resource allocation, and understanding emerging service pressures.

Insights published in the We Protect T.O.R.O.N.T.O: Data‑Driven Insights for Urban Safety, Infrastructure & Analytics series were derived from this pivot‑based exploration.

6. Understand the Macro Impact on Key Trends

Microsoft Copilot’s Generative AI capabilities were used to interpret the macro‑level pressures influencing the observed trends. This included identifying:

  • Seasonal patterns
  • Operational changes
  • Policy impacts
  • Social and environmental factors

A brief overview of how Copilot supported this stage is available in Gen AI Tips: How to Use Microsoft Copilot With Microsoft Excel.

Conclusion

This six‑step methodology provides a transparent, repeatable framework for analyzing long‑term 311 Toronto service request trends. By combining structured data preparation, statistical outlier detection, geographic enrichment, and AI‑assisted interpretation, the workflow supports meaningful insights for planners, analysts, and decision‑makers across the City of Toronto.

To explore the full analysis, visit the main pillar post: Toronto 311 Service Request Trends (2010–2025): Time‑Series Segmentation by FSA & City Division.

Related Posts