Build an SLA report from ticket-history CSV files

A ticket CSV containing creation and latest resolution dates is not always enough to reproduce an SLA result. It may omit pauses, first responses, reopen cycles, account-specific terms or the history of priority changes. Start by asking what the files can support, rather than promising the report before inspecting them.

This workflow owns the path from exported rows to a reviewed report. Detailed clock arithmetic, population rules and timestamp diagnostics remain separate methods. The per-account SLA guide owns the business measure.

Inspect the available files

Build a mapping sheet with source filename, source column, intended field, type, time zone, completeness and transformation. Keep original values alongside normalized ones. A field called closed_date may represent an administrative closure after resolution, so obtain the source system's meaning from its administrator.

These are a proposed analysis schema, not a list of connectors Covirage ships. Export availability and permission vary by system. Do not copy private message bodies, personal contact details or attachments unless the agreed question actually needs them.

Preserve the event grain

A single ticket can have many events. Keep that relationship rather than joining all event rows directly to a report that assumes one row per ticket. Retain event identifiers so repeated exports can be deduplicated without deleting legitimate events that share a timestamp.

Normalize event types into documented actions such as clock_start, pause_start, pause_end and clock_stop. Include source events that establish exclusions or reopen behavior. Sort by instant and a reliable source sequence when available. If two conflicting events have the same time and no ordering evidence, mark the cycle ambiguous rather than inventing an order.

Atlassian documents SLA start, pause and stop conditions; those conditions need to be mapped to the specific exported events. The existence of an event called resolved does not prove that it stops every SLA in every configuration. SLA conditions documentation.

Reconstruct a small example by hand

The following fictional account uses a four-business-hour resolution target, with a nine-to-five calendar and customer-wait pauses. Both tickets occur on a normal working day.

The reviewed completed-cycle total is two, with one pass and one breach: 50% for this small illustrative sample. Gross elapsed time for T-101 is four hours, but one hour was a permitted pause. Joining only creation and resolution fields would conceal the reason its counted time is three hours.

Before expanding, ask a reviewer to trace both results back to their source rows. A third ticket with no stop event belongs in the open population, not the completed denominator.

Join targets without multiplying cycles

Use account, obligation type, the policy's priority basis and applicable version timing to select a target. First count candidate target rows per cycle. Zero candidates mean missing terms or mapping; multiple candidates mean an ambiguous join. Resolve these before calculation.

Microsoft documents multi-column merges and compatible key types. Preserve pre-merge and post-merge counts and retain unmatched rows for review; expanding several matches can multiply observations. Merge queries overview.

Issue a reviewable output

Include cycle-level inputs and result, a summary by account and priority, completed/open/unknown counts, source coverage and an exception list. Check that every eligible cycle occurs once in the outcome table and that all outcome buckets reconcile to the source population.

Freeze the extract time, field mapping, target version and calculation version so the report can be repeated. Later corrections should produce a new report version with a stated reason rather than overwrite a previously issued customer result without explanation.

Decide what the files cannot answer

A latest-state export cannot reliably reconstruct historical pause durations. A missing account key cannot select account-specific terms. A date-only event cannot support a minute-level target. State these limitations in the output and agree whether additional history or a narrower question is needed.

For a practical starting point, bring a small authorized ticket/event export, the column meanings and applicable target table to discuss an analysis scope. File review establishes what is possible; it does not promise a help-desk integration, automatic contract interpretation or ongoing report delivery.