Freshdesk Agent Performance Reporter

Freshdesk Agent Performance Reporter

Problem Statement

Support teams using Freshdesk generate a wealth of operational data, but extracting meaningful per-agent performance metrics requires navigating the Freshdesk UI ticket-by-ticket or building custom reports inside the platform. Managers need a repeatable, scriptable way to pull metrics like average resolution time, first-response time, and CSAT scores for any given period, then share the results as a clean report for standups or quarterly reviews.

This script solves that by querying the Freshdesk REST API directly, aggregating per-agent statistics for a chosen time window, and emitting a human-readable report (Markdown, JSON, or CSV). It's designed to be run from cron, CI, or a laptop, with pagination handled automatically and rate-limit backoff built in.

How It Works

The script authenticates with Freshdesk using HTTP Basic auth (API key as username, X as password). It fetches tickets within the requested period using the /api/v2/search/tickets endpoint with a date-range query, then paginates through results. For each ticket, it extracts the assignee, the created/resolved timestamps, and the CSAT rating from the satisfaction survey. Metrics are aggregated in a defaultdict keyed by agent ID, then joined against an agent directory fetched from /api/v2/agents.

Design decisions: (1) Streaming aggregation — we never hold all tickets in memory beyond the current page, so the script scales to large datasets. (2) Retry with exponential backoff on 429/5xx responses via a small requests.Session wrapper. (3) Output renderers are pluggable functions keyed by file extension, so adding a new format is a one-function change. (4) Period parsing accepts YYYY-MM or YYYY-MM-DD:YYYY-MM-DD, keeping the CLI ergonomic.

Full Script

#!/usr/bin/env python3
"""
Freshdesk Agent Performance Reporter

Fetches tickets for a given period from Freshdesk, aggregates per-agent
metrics (resolution time, first response time, CSAT), and writes a report
in Markdown, JSON, or CSV format.

Usage:
    python3 freshdesk_report.py --period 2025-07 \
        --metric resolution-time,csat --output report.md

Environment:
    FRESHDESK_DOMAIN   e.g. "acme" (for acme.freshdesk.com)
    FRESHDESK_API_KEY  API key from Profile Settings -> API Key
"""

from __future__ import annotations

import argparse
import csv
import json
import os
import sys
import time
from collections import defaultdict
from dataclasses import dataclass, field, asdict
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Callable, Iterable

import requests

# ---------------------------------------------------------------------------
# Configuration
# ---------------------------------------------------------------------------

FRESHDESK_DOMAIN = os.environ.get("FRESHDESK_DOMAIN", "").strip()
FRESHDESK_API_KEY = os.environ.get("FRESHDESK_API_KEY", "").strip()
API_BASE = f"https://{FRESHDESK_DOMAIN}.freshdesk.com/api/v2" if FRESHDESK_DOMAIN else ""
PAGE_SIZE = int(os.environ.get("FRESHDESK_PAGE_SIZE", "100"))
MAX_RETRIES = int(os.environ.get("FRESHDESK_MAX_RETRIES", "5"))
REQUEST_TIMEOUT = float(os.environ.get("FRESHDESK_TIMEOUT", "30"))

# Metric registry: name -> (label, unit, extractor)
Metrics = dict[str, dict[str, Any]]


# ---------------------------------------------------------------------------
# Data model
# ---------------------------------------------------------------------------

@dataclass
class AgentStats:
    agent_id: int
    name: str = "Unknown"
    email: str = ""
    tickets_assigned: int = 0
    tickets_resolved: int = 0
    resolution_seconds_total: float = 0.0
    first_response_seconds_total: float = 0.0
    first_response_count: int = 0
    csat_sum: float = 0.0
    csat_count: int = 0
    _resolved_with_time: int = field(default=0, repr=False)

    def add_ticket(self, ticket: dict[str, Any]) -> None:
        self.tickets_assigned += 1
        created = _parse_ts(ticket.get("created_at"))
        resolved = _parse_ts(ticket.get("resolved_at"))
        first_responded = _parse_ts(ticket.get("first_responded_at"))

        if resolved and created:
            self.resolution_seconds_total += (resolved - created).total_seconds()
            self._resolved_with_time += 1
            self.tickets_resolved += 1

        if first_responded and created:
            self.first_response_seconds_total += (
                first_responded - created
            ).total_seconds()
            self.first_response_count += 1

        csat = ticket.get("satisfaction_rating") or {}
        score = csat.get("score")
        # Freshdesk returns CSAT as a string: "positive", "negative", "neutral"
        if score == "positive":
            self.csat_sum += 1.0
            self.csat_count += 1
        elif score == "neutral":
            self.csat_sum += 0.5
            self.csat_count += 1
        elif score == "negative":
            self.csat_sum += 0.0
            self.csat_count += 1

    @property
    def avg_resolution_hours(self) -> float | None:
        if self._resolved_with_time == 0:
            return None
        return self.resolution_seconds_total / self._resolved_with_time / 3600.0

    @property
    def avg_first_response_hours(self) -> float | None:
        if self.first_response_count == 0:
            return None
        return self.first_response_seconds_total / self.first_response_count / 3600.0

    @property
    def csat_pct(self) -> float | None:
        if self.csat_count == 0:
            return None
        return 100.0 * self.csat_sum / self.csat_count


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

def _parse_ts(value: str | None) -> datetime | None:
    """Parse Freshdesk ISO-8601 timestamps into aware datetimes."""
    if not value:
        return None
    try:
        # Freshdesk uses e.g. "2025-07-14T09:12:33Z"
        return datetime.fromisoformat(value.replace("Z", "+00:00"))
    except ValueError:
        return None


def parse_period(spec: str) -> tuple[datetime, datetime]:
    """Accept 'YYYY-MM' or 'YYYY-MM-DD:YYYY-MM-DD' and return UTC bounds."""
    if ":" in spec:
        start_s, end_s = spec.split(":", 1)
        start = datetime.fromisoformat(start_s).replace(tzinfo=timezone.utc)
        end = datetime.fromisoformat(end_s).replace(tzinfo=timezone.utc)
        return start, end

    try:
        year, month = (int(x) for x in spec.split("-"))
    except ValueError as exc:
        raise SystemExit(f"Invalid --period '{spec}'. Use YYYY-MM or YYYY-MM-DD:YYYY-MM-DD") from exc

    start = datetime(year, month, 1, tzinfo=timezone.utc)
    if month == 12:
        end = datetime(year + 1, 1, 1, tzinfo=timezone.utc)
    else:
        end = datetime(year, month + 1, 1, tzinfo=timezone.utc)
    return start, end


# ---------------------------------------------------------------------------
# Freshdesk client
# ---------------------------------------------------------------------------

class FreshdeskClient:
    def __init__(self, domain: str, api_key: str) -> None:
        if not domain or not api_key:
            raise SystemExit(
                "FRESHDESK_DOMAIN and FRESHDESK_API_KEY must be set in the environment."
            )
        self.session = requests.Session()
        self.session.auth = (api_key, "X")
        self.session.headers.update({"Accept": "application/json"})
        self.base = f"https://{domain}.freshdesk.com/api/v2"

    def _get(self, path: str, params: dict[str, Any] | None = None) -> Any:
        url = f"{self.base}{path}"
        delay = 1.0
        for attempt in range(1, MAX_RETRIES + 1):
            resp = self.session.get(url, params=params, timeout=REQUEST_TIMEOUT)
            if resp.status_code == 429 or 500 <= resp.status_code < 600:
                if attempt == MAX_RETRIES:
                    resp.raise_for_status()
                retry_after = resp.headers.get("Retry-After")
                wait = float(retry_after) if retry_after else delay
                print(
                    f"[warn] {resp.status_code} from {url}; retrying in {wait:.1f}s "
                    f"(attempt {attempt}/{MAX_RETRIES})",
                    file=sys.stderr,
                )
                time.sleep(wait)
                delay = min(delay * 2, 60.0)
                continue
            resp.raise_for_status()
            return resp.json()
        raise RuntimeError("unreachable")

    def list_agents(self) -> dict[int, dict[str, Any]]:
        agents: dict[int, dict[str, Any]] = {}
        page = 1
        while True:
            batch = self._get("/agents", params={"page": page, "per_page": PAGE_SIZE})
            if not batch:
                break
            for agent in batch:
                agents[agent["id"]] = agent
            if len(batch) < PAGE_SIZE:
                break
            page += 1
        return agents

    def iter_tickets(self, start: datetime, end: datetime) -> Iterable[dict[str, Any]]:
        """Yield tickets created within [start, end) using the search endpoint."""
        # Freshdesk search uses a query DSL; date format is 'YYYY-MM-DD'.
        query = (
            f'"created_at:>\'{start.strftime("%Y-%m-%d")}\' '
            f'AND created_at:<\'{end.strftime("%Y-%m-%d")}\'"'
        )
        page = 1
        while True:
            payload = self._get(
                "/search/tickets",
                params={"query": query, "page": page},
            )
            results = payload.get("results", []) if isinstance(payload, dict) else payload
            if not results:
                break
            for ticket in results:
                yield ticket
            if len(results) < 30:  # search endpoint caps at 30/page
                break
            page += 1


# ---------------------------------------------------------------------------
# Aggregation
# ---------------------------------------------------------------------------

def aggregate(
    tickets: Iterable[dict[str, Any]],
    agents: dict[int, dict[str, Any]],
) -> dict[int, AgentStats]:
    stats: dict[int, AgentStats] = {}
    for ticket in tickets:
        agent_id = ticket.get("responder_id") or ticket.get("agent_id")
        if not agent_id:
            continue
        if agent_id not in stats:
            agent = agents.get(agent_id, {})
            stats[agent_id] = AgentStats(
                agent_id=agent_id,
                name=agent.get("contact", {}).get("name", f"Agent {agent_id}"),
                email=agent.get("contact", {}).get("email", ""),
            )
        stats[agent_id].add_ticket(ticket)
    return stats


# ---------------------------------------------------------------------------
# Renderers
# ---------------------------------------------------------------------------

def render_markdown(
    stats: dict[int, AgentStats],
    metrics: list[str],
    period_label: str,
) -> str:
    lines = [
        f"# Freshdesk Agent Performance — {period_label}",
        "",
        f"_Generated {datetime.now(timezone.utc).isoformat(timespec='seconds')}_",
        "",
    ]
    header = ["Agent", "Tickets", "Resolved"]
    if "resolution-time" in metrics:
        header.append("Avg Resolution (h)")
    if "first-response" in metrics:
        header.append("Avg First Response (h)")
    if "csat" in metrics:
        header.append("CSAT %")
    lines.append("| " + " | ".join(header) + " |")
    lines.append("|" + "|".join(["---"] * len(header)) + "|")

    for s in sorted(stats.values(), key=lambda x: x.tickets_assigned, reverse=True):
        row = [s.name, str(s.tickets_assigned), str(s.tickets_resolved)]
        if "resolution-time" in metrics:
            row.append(_fmt(s.avg_resolution_hours))
        if "first-response" in metrics:
            row.append(_fmt(s.avg_first_response_hours))
        if "csat" in metrics:
            row.append(_fmt(s.csat_pct))
        lines.append("| " + " | ".join(row) + " |")
    return "\n".join(lines) + "\n"


def render_json(
    stats: dict[int, AgentStats],
    metrics: list[str],
    period_label: str,
) -> str:
    payload = {
        "period": period_label,
        "generated_at": datetime.now(timezone.utc).isoformat(),
        "metrics": metrics,
        "agents": [
            {
                **{k: v for k, v in asdict(s).items() if not k.startswith("_")},
                "avg_resolution_hours": s.avg_resolution_hours,
                "avg_first_response_hours": s.avg_first_response_hours,
                "csat_pct": s.csat_pct,
            }
            for s in stats.values()
        ],
    }
    return json.dumps(payload, indent=2, default=str)


def render_csv(
    stats: dict[int, AgentStats],
    metrics: list[str],
    period_label: str,
) -> str:
    import io

    buf = io.StringIO()
    writer = csv.writer(buf)
    header = ["agent_id", "name", "email", "tickets_assigned", "tickets_resolved"]
    if "resolution-time" in metrics:
        header.append("avg_resolution_hours")
    if "first-response" in metrics:
        header.append("avg_first_response_hours")
    if "csat" in metrics:
        header.append("csat_pct")
    writer.writerow(header)
    for s in stats.values():
        row: list[Any] = [s.agent_id, s.name, s.email, s.tickets_assigned, s.tickets_resolved]
        if "resolution-time" in metrics:
            row.append(s.avg_resolution_hours)
        if "first-response" in metrics:
            row.append(s.avg_first_response_hours)
        if "csat" in metrics:
            row.append(s.csat_pct)
        writer.writerow(row)
    return buf.getvalue()


def _fmt(value: float | None) -> str:
    return "—" if value is None else f"{value:.2f}"


RENDERERS: dict[str, Callable[[dict[int, AgentStats], list[str], str], str]] = {
    ".md": render_markdown,
    ".markdown": render_markdown,
    ".json": render_json,
    ".csv": render_csv,
}


# ---------------------------------------------------------------------------
# CLI
# ---------------------------------------------------------------------------

def build_parser() -> argparse.ArgumentParser:
    p = argparse.ArgumentParser(
        description="Generate a Freshdesk agent performance report.",
    )
    p.add_argument(
        "--period",
        required=True,
        help="Reporting window: 'YYYY-MM' or 'YYYY-MM-DD:YYYY-MM-DD'.",
    )
    p.add_argument(
        "--metric",
        default="resolution-time,csat",
        help="Comma-separated metrics: resolution-time,first-response,csat",
    )
    p.add_argument(
        "--output",
        default="report.md",
        help="Output path; extension selects format (.md, .json, .csv).",
    )
    return p


def main(argv: list[str] | None = None) -> int:
    args = build_parser().parse_args(argv)

    metrics = [m.strip() for m in args.metric.split(",") if m.strip()]
    valid = {"resolution-time", "first-response", "csat"}
    unknown = set(metrics) - valid
    if unknown:
        print(f"[error] Unknown metric(s): {', '.join(sorted(unknown))}", file=sys.stderr)
        return 2

    start, end = parse_period(args.period)
    period_label = (
        args.period
        if ":" in args.period
        else start.strftime("%Y-%m")
    )

    out_path = Path(args.output)
    renderer = RENDERERS.get(out_path.suffix.lower())
    if renderer is None:
        print(
            f"[error] Unsupported output extension '{out_path.suffix}'. "
            f"Use one of: {', '.join(sorted(RENDERERS))}",
            file=sys.stderr,
        )
        return 2

    client = FreshdeskClient(FRESHDESK_DOMAIN, FRESHDESK_API_KEY)

    print(f"[info] Fetching agents...", file=sys.stderr)
    agents = client.list_agents()
    print(f"[info] Found {len(agents)} agents.", file=sys.stderr)

    print(
        f"[info] Streaming tickets from {start.date()} to {end.date()}...",
        file=sys.stderr,
    )
    stats = aggregate(client.iter_tickets(start, end), agents)
    print(f"[info] Aggregated {len(stats)} agents with activity.", file=sys.stderr)

    content = renderer(stats, metrics, period_label)
    out_path.write_text(content, encoding="utf-8")
    print(f"[info] Wrote {out_path} ({len(content)} bytes).", file=sys.stderr)
    return 0


if __name__ == "__main__":
    try:
        sys.exit(main())
    except requests.HTTPError as exc:
        print(f"[error] Freshdesk API error: {exc}", file=sys.stderr)
        sys.exit(1)
    except KeyboardInterrupt:
        print("\n[error] Interrupted.", file=sys.stderr)
        sys.exit(130)

Configuration

CLI flags: --period (required), --metric (default resolution-time,csat), --output (default report.md). Output format is inferred from the file extension (.md, .json, .csv).

Usage Examples

1. Monthly Markdown report for standup:

$ export FRESHDESK_DOMAIN=acme
$ export FRESHDESK_API_KEY=abc123XYZ
$ python3 freshdesk_report.py --period 2025-07 \
    --metric resolution-time,csat --output report.md
[info] Fetching agents...
[info] Found 42 agents.
[info] Streaming tickets from 2025-07-01 to 2025-08-01...
[info] Aggregated 18 agents with activity.
[info] Wrote report.md (1842 bytes).

report.md contains a table like:

| Agent        | Tickets | Resolved | Avg Resolution (h) | CSAT % |
|--------------|---------|----------|--------------------|--------|
| Ada Lovelace | 87      | 84       | 6.42               | 92.50  |
| Grace Hopper | 63      | 61       | 4.11               | 88.10  |

2. Custom date range as JSON for a dashboard:

$ python3 freshdesk_report.py \
    --period 2025-06-15:2025-06-30 \
    --metric resolution-time,first-response,csat \
    --output june_second_half.json

3. Cron job writing CSV to a shared drive:

0 6 1 * * FRESHDESK_DOMAIN=acme FRESHDESK_API_KEY=$KEY \
  python3 /opt/scripts/freshdesk_report.py \
    --period $(date -d "last month" +\%Y-\%m) \
    --metric resolution-time,csat \
    --output /mnt/reports/freshdesk-\$(date -d "last month" +\%Y-\%m).csv

Error Handling

• Missing credentials → SystemExit with a clear message before any network call.
• Invalid --period → SystemExit explaining the accepted formats.
• Unknown metric names → exit code 2 with the offending names listed.
• Unsupported output extension → exit code 2 with the valid set.
• HTTP 429 / 5xx → retried up to MAX_RETRIES with exponential backoff and Retry-After respect; a warning is logged per attempt.
• Other HTTP errors (401, 403, 404) → requests.HTTPError bubbles up and is caught at the entry point, exiting with code 1.
• Malformed timestamps → _parse_ts returns None, and the ticket is counted but excluded from the timing averages rather than crashing the run.
• Keyboard interrupt → exits cleanly with code 130.

Extending Ideas

• Add SLA breach tracking: compare each ticket's resolution time against a per-priority SLA table and add a sla_breach_pct column.
• Slack/Teams delivery: after writing the file, POST the Markdown body to an incoming webhook so the report lands in a channel automatically.
• HTML/PDF output: register a .html renderer using jinja2 and a .pdf renderer via weasyprint — the RENDERERS dict makes this a drop-in addition.
• Historical trending: persist each run's JSON to a SQLite file and add a --trend mode that plots week-over-week CSAT per agent with matplotlib.
• Group-level rollups: aggregate by group_id in addition to responder_id by extending AgentStats with a group field and adding a second aggregation pass.