Data Overview

This section covers the data provided for the analysis — schema definitions, period labels, row counts, and data quality checks. It establishes the analytical foundation before any segment-level analysis begins.

Data Assets

File Level Description
ops_cases.csv Case One row per case.
ops_reviews.csv Review / touch One row per review action on a case. A case can have multiple reviews.

The dataset covers Email-channel cases only, from January to June 2023 (Weeks 1–26), across Internal and Vendor handler teams.

Period Definitions

The period column in ops_cases encodes three analytical windows that are used throughout this book:

Period Weeks What was live
Baseline 1–10 No changes
Self-serve Only 11–13 Automation live; routing unchanged
Both Changes 14–26 Both automation and skill-based routing live

This three-period structure is critical: it allows the effect of each change to be isolated rather than treating the post-Week 11 period as a single block.

Code 
import pandas as pd
import matplotlib.pyplot as plt
import warnings

warnings.filterwarnings("ignore")

# Load data
cases = pd.read_csv(
    "ops_cases.csv", parse_dates=["created_at", "resolved_at", "due_at"]
)
reviews = pd.read_csv(
    "ops_reviews.csv", parse_dates=["intended_start_at", "actual_start_at", "end_at"]
)

# Enforce period ordering throughout the book
period_order = ["Baseline", "Self-serve Only", "Both Changes"]
cases["period"] = pd.Categorical(cases["period"], categories=period_order, ordered=True)

print(f"ops_cases:   {cases.shape[0]:,} rows × {cases.shape[1]} columns")
print(f"ops_reviews: {reviews.shape[0]:,} rows × {reviews.shape[1]} columns")
ops_cases:   12,841 rows × 17 columns
ops_reviews: 17,135 rows × 11 columns
Code 
# Row counts by period
print("Cases by period:")
print(cases.groupby("period")["case_id"].count())
print()
print("Cases by handler type:")
print(cases.groupby("initial_handler_type")["case_id"].count())
print()
print("Cases by queue:")
print(cases.groupby("queue_name")["case_id"].count())
Cases by period:
period
Baseline           5750
Self-serve Only    1342
Both Changes       5749
Name: case_id, dtype: int64

Cases by handler type:
initial_handler_type
Internal    7699
Vendor      5142
Name: case_id, dtype: int64

Cases by queue:
queue_name
Basic_Support        3769
Complex_Cases        3679
Technical_Support    5393
Name: case_id, dtype: int64
Code 
# Data quality checks
print("=== Missing values — ops_cases ===")
print(cases.isnull().sum()[cases.isnull().sum() > 0])
print()
print("=== Missing values — ops_reviews ===")
print(reviews.isnull().sum()[reviews.isnull().sum() > 0])
print()

# Check for resolution_hours anomalies
negative_res = (cases["resolution_hours"] < 0).sum()
print(f"Negative resolution_hours: {negative_res}")

# Check review ordering
max_reviews = cases["num_reviews"].max()
print(f"Max reviews on a single case: {max_reviews}")
print(f"Cases with >3 reviews: {(cases['num_reviews'] > 3).sum()}")
=== Missing values — ops_cases ===
initial_vendor_name    7699
dtype: int64

=== Missing values — ops_reviews ===
vendor_name    10992
dtype: int64

Negative resolution_hours: 0
Max reviews on a single case: 4
Cases with >3 reviews: 167
Code 
# Review categorical variables with more than one options - final outcome and channel only have one option
print("Queue Name Options:", cases["queue_name"].unique().tolist())
print("Initial Handler Type:", cases["initial_handler_type"].unique().tolist())
print(
    "Initial Vendor Name Options:",
    cases["initial_vendor_name"].unique().tolist(),
)
Queue Name Options: ['Basic_Support', 'Technical_Support', 'Complex_Cases']
Initial Handler Type: ['Internal', 'Vendor']
Initial Vendor Name Options: [nan, 'VendorB', 'VendorC', 'VendorA']
Code 
# Found an overlap of week_number 10, 13 between baseline and self-serve only for week 13
print(
    "Baseline Weeks:",
    cases[cases["period"] == "Baseline"]["week_number"].unique().tolist(),
)
print(
    "Self-Serve Weeks:",
    cases[cases["period"] == "Self-serve Only"]["week_number"].unique().tolist(),
)
print(
    "Both Changes Weeks:",
    cases[cases["period"] == "Both Changes"]["week_number"].unique().tolist(),
)
Baseline Weeks: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
Self-Serve Weeks: [10, 11, 12, 13]
Both Changes Weeks: [13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26]

There is an overlap in the week numbers but not fixing it assuming there is a reason for the same.

Now checking if there is an overlap in the handler types.

Code 
reviews = reviews.merge(
    cases[["case_id", "period", "initial_handler_type"]], on="case_id", how="left"
)

print(
    reviews[
        (reviews["initial_handler_type"] == "Internal")
        & (reviews["handler_type"] == "Vendor")
    ].shape[0],
    reviews[
        (reviews["initial_handler_type"] == "Vendor")
        & (reviews["handler_type"] == "Initial")
    ].shape[0],
)
0 0

New Metrics Created

The notebooks leverage multiple new metrics that are created.

  1. Average Speed of Answer (ASA) - Average time is takes an agent to get started on the case from when the case lands in their queue. Calculated as the difference between the actual_start_at and intended_start_at at a Review Level.

  2. First Time Resolution (FTR) - It is the ratio of cases out of all cases where the case was resolved in it’s first review. It is calculated at case level using num_reviews = 1 and at a review level with review_order=1 and outcome='Completed

They are also explained in the deck and in the notebooks when they are used. Any other measure created is calculated in the notebook itself.