How to Create Automated Ticket Resolution Reports

An automated ticket resolution report with metrics runs every Monday morning and lands in your inbox before you open your first ticket. I build these for support managers who spend hours compiling performance data manually. The system pulls directly from your ticket database and generates a readable summary with the numbers that actually matter.

No spreadsheets. No manual counting. Just the facts, delivered on schedule.

What You Need Before Starting

• A ticketing system with resolution timestamps and CSAT data
• Python 3.8+ with sqlite3 and smtplib
• A scheduler (cron or APScheduler)
• Email or Slack for delivery

Step 1: Define Your Key Metrics

REPORT_METRICS = [
    "total_tickets",
    "resolved_tickets",
    "avg_first_response_minutes",
    "avg_resolution_hours",
    "sla_compliance_rate",
    "csat_score",
    "tickets_by_category",
    "top_agents_by_resolution",
]

Step 2: Query the Data

import sqlite3
from datetime import datetime, timedelta

def get_report_data(days=7):
    conn = sqlite3.connect("tickets.db")
    start = (datetime.now() - timedelta(days=days)).isoformat()

    total = conn.execute("SELECT COUNT(*) FROM tickets WHERE created_at > ?", (start,)).fetchone()[0]
    resolved = conn.execute("SELECT COUNT(*) FROM tickets WHERE resolved_at IS NOT NULL AND created_at > ?", (start,)).fetchone()[0]

    avg_response = conn.execute("""
        SELECT AVG((julianday(first_response_at) - julianday(created_at)) * 24 * 60)
        FROM tickets WHERE first_response_at IS NOT NULL AND created_at > ?
    """, (start,)).fetchone()[0]

    avg_resolution = conn.execute("""
        SELECT AVG((julianday(resolved_at) - julianday(created_at)) * 24)
        FROM tickets WHERE resolved_at IS NOT NULL AND created_at > ?
    """, (start,)).fetchone()[0]

    csat = conn.execute(
        "SELECT AVG(csat_score) FROM tickets WHERE csat_score IS NOT NULL AND created_at > ?", (start,)
    ).fetchone()[0]

    by_category = conn.execute("""
        SELECT category, COUNT(*) FROM tickets WHERE created_at > ?
        GROUP BY category ORDER BY COUNT(*) DESC
    """, (start,)).fetchall()

    return {
        "period_days": days,
        "total_tickets": total,
        "resolved_tickets": resolved,
        "resolution_rate": round(resolved / total * 100, 1) if total else 0,
        "avg_first_response_min": round(avg_response or 0, 1),
        "avg_resolution_hours": round(avg_resolution or 0, 1),
        "csat_score": round(csat or 0, 1),
        "by_category": dict(by_category),
    }

Step 3: Generate the Report

def generate_report(data):
    report = f"""Support Report - Last {data['period_days']} Days

SUMMARY
Total Tickets: {data['total_tickets']}
Resolved: {data['resolved_tickets']} ({data['resolution_rate']}%)
Avg First Response: {data['avg_first_response_min']} minutes
Avg Resolution Time: {data['avg_resolution_hours']} hours
CSAT Score: {data['csat_score']}/5.0

TICKETS BY CATEGORY
"""
    for category, count in data["by_category"].items():
        report += f"  {category}: {count}\n"

    return report

Step 4: Add Week-Over-Week Comparison

Compare this week to last week for trend visibility:

def add_comparison(current, previous):
    comparisons = {}
    for key in ["total_tickets", "resolution_rate", "avg_first_response_min", "avg_resolution_hours", "csat_score"]:
        current_val = current.get(key, 0)
        prev_val = previous.get(key, 0)
        if prev_val:
            change = round((current_val - prev_val) / prev_val * 100, 1)
            direction = "up" if change > 0 else "down"
            comparisons[key] = {"current": current_val, "previous": prev_val, "change_pct": change, "direction": direction}
    return comparisons

Step 5: Schedule and Deliver

import smtplib
from email.mime.text import MIMEText

def send_report():
    current_data = get_report_data(days=7)
    previous_data = get_report_data(days=14)  # Compare with previous week
    report_text = generate_report(current_data)

    msg = MIMEText(report_text)
    msg["Subject"] = f"Support Report - Week of {datetime.now().strftime('%B %d')}"
    msg["From"] = os.getenv("REPORT_EMAIL")
    msg["To"] = "[email protected]"

    with smtplib.SMTP("smtp.gmail.com", 587) as server:
        server.starttls()
        server.login(os.getenv("REPORT_EMAIL"), os.getenv("EMAIL_PASSWORD"))
        server.send_message(msg)

# Schedule for Monday 8 AM
from apscheduler.schedulers.blocking import BlockingScheduler
scheduler = BlockingScheduler()
scheduler.add_job(send_report, "cron", day_of_week="mon", hour=8)
scheduler.start()

What to Build Next

Add per-agent performance breakdowns. Show each agent their resolution time, CSAT score, and ticket volume compared to the team average. Individual dashboards drive improvement better than team-level reports.

Related Reading

• AI-Powered Reporting That Actually Gets Read - how to make reports people actually open
• The Measurement Framework That Actually Works - picking the right support metrics
• Financial Reporting with AI - automated reporting patterns applied to any domain