Lässt sich SLA-Reporting ohne neue Software automatisieren?

Ja. Kann Ihr Ticket- oder Telefoniesystem eine Datei exportieren, lässt sich mit Excel und Power Query, oder mit Power BI, falls Sie es schon haben, ein manueller wöchentlicher SLA-Abzug in einen Report verwandeln, der sich selbst aktualisiert. Dafür brauchen Sie keine neue Plattform.

Die Automatisierung ist der leichte Teil. Zeit kostet die Einigung darauf, was die SLA-Zahl eigentlich ist, und dieselben fünf Probleme tauchen jedes Mal auf, wenn ein SLA-Report neu gebaut wird. Jeder Abschnitt unten enthält die Lösung in Power Query oder DAX, in der Reihenfolge, in der die Daten durch den Report fließen.

Die Beispiele setzen einen Ticket-Export mit einer Zeile pro Ticket voraus: TicketID, CreatedTime, ResolvedTime (leer, solange das Ticket offen ist), Priority, ReopenCount (wie oft das Ticket wieder geöffnet wurde) und eine kleine Nachschlagetabelle mit dem SLA-Ziel in Minuten je Priorität. Die Supportzeiten sind Montag bis Freitag, 08:00 bis 18:00 Uhr, in mitteleuropäischer Zeit.

1. Zeitstempel in der falschen Zeitzone

Die meisten Systeme exportieren in UTC, während das SLA nach lokalen Supportzeiten läuft. Ein Ticket, das um 08:30 Uhr in Hamburg erstellt wird, ist im Sommer 06:30 Uhr UTC und im Winter 07:30 Uhr UTC. Wenden Sie die Logik der Geschäftszeiten auf UTC-Zeiten an, ist jedes Ergebnis ein halbes Jahr lang um eine Stunde falsch.

Rechnen Sie einmal um, am Anfang der Abfrage, und nie wieder. Power Query kennt keine benannten Zeitzonen, deshalb kommt für Mitteleuropa die Sommerzeitregel in eine Funktion. Die Sommerzeit gilt ab 01:00 Uhr UTC am letzten Sonntag im März bis 01:00 Uhr UTC am letzten Sonntag im Oktober.

// Query name: UtcToCentralEurope
(utc as datetime) as datetime =>
let
    year = Date.Year(utc),
    lastSundayOf = (month as number) =>
        let
            lastDay = #date(year, month, 31)
        in
            Date.AddDays(lastDay, -Date.DayOfWeek(lastDay, Day.Sunday)),
    summerStart = DateTime.From(lastSundayOf(3)) + #duration(0, 1, 0, 0),
    summerEnd = DateTime.From(lastSundayOf(10)) + #duration(0, 1, 0, 0),
    offsetHours = if utc >= summerStart and utc < summerEnd then 2 else 1
in
    utc + #duration(0, offsetHours, 0, 0)

Ich habe diese Regel für jede Stunde von 2024 bis 2030 gegen die Regeln der Zeitzone Europe/Berlin geprüft, und sie stimmte jedes Mal. Wenden Sie sie auf beide Ticket-Spalten an:

let
    Source = Tickets,
    Local = Table.TransformColumns(
        Source,
        {
            {"CreatedTime", UtcToCentralEurope, type datetime},
            {"ResolvedTime", each if _ = null then null else UtcToCentralEurope(_), type nullable datetime}
        }
    )
in
    Local

Offene Tickets brauchen ein „Jetzt“, gegen das sich messen lässt. Der Power-BI-Dienst läuft in UTC, Excel auf Ihrem Desktop in Ortszeit, deshalb liefert DateTime.LocalNow() je nachdem, wo die Aktualisierung läuft, unterschiedliche Ergebnisse. Legen Sie den Aktualisierungszeitpunkt einmal fest, ausgehend von UTC:

// Query name: RefreshTimeLocal
UtcToCentralEurope(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()))

2. Die Uhr sollte nur während der Supportzeiten laufen

Ein Ticket, das am Freitag um 17:00 Uhr mit einem Ziel von 4 Stunden eröffnet wird, ist am Samstagmorgen nicht verspätet. Kalenderzeit überzeichnet die Verstöße, und der Abstand wächst mit jedem Wochenende und jedem Feiertag.

Diese Funktion zählt nur die Minuten, die in die Supportzeiten fallen, und überspringt Wochenenden und eine Liste von Feiertagen:

// Query name: BusinessMinutes
(startTime as datetime, endTime as datetime, holidays as list) as number =>
if endTime <= startTime then 0 else
let
    openHour = 8,
    closeHour = 18,
    firstDay = Date.From(startTime),
    dayCount = Duration.Days(Date.From(endTime) - firstDay) + 1,
    allDays = List.Dates(firstDay, dayCount, #duration(1, 0, 0, 0)),
    workDays = List.Select(
        allDays,
        each Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(holidays, _)
    ),
    minutesPerDay = List.Transform(
        workDays,
        (day) =>
            let
                opens = DateTime.From(day) + #duration(0, openHour, 0, 0),
                closes = DateTime.From(day) + #duration(0, closeHour, 0, 0),
                windowStart = List.Max({startTime, opens}),
                windowEnd = List.Min({endTime, closes}),
                minutes = Duration.TotalMinutes(windowEnd - windowStart)
            in
                if minutes > 0 then minutes else 0
    )
in
    if List.Count(minutesPerDay) = 0 then 0 else List.Sum(minutesPerDay)

Die erste Zeile gibt 0 zurück, wenn die Endzeit nicht nach der Startzeit liegt, was bei Zeitabweichungen der Uhr oder einer fehlerhaften ResolvedTime vorkommen kann. Ohne sie würde ein negativer Datumsbereich einen Fehler auslösen und die Aktualisierung anhalten.

Holidays ist eine Abfrage, die eine Liste von Datumswerten zurückgibt, zum Beispiel List.Buffer(Table.Column(HolidayTable, "Date")). Das Puffern liest die Liste einmal, statt bei jeder Zeile. Ich habe dieselbe Logik an Fällen wie Freitag 17:00 Uhr bis Montag 09:00 Uhr (120 Minuten) und einem Zeitraum über einen Feiertag hinweg getestet, und sie lieferte jedes Mal die erwarteten Minuten. So verwenden Sie sie:

Table.AddColumn(
    Local,
    "ActiveBizMinutes",
    each BusinessMinutes(
        [CreatedTime],
        if [ResolvedTime] = null then RefreshTimeLocal else [ResolvedTime],
        Holidays
    ),
    type number
)

Die Funktion durchläuft jeden Tag im Zeitraum, was bei Tausenden von Tickets unproblematisch ist. Bei Millionen von Zeilen erledigen Sie diesen Schritt besser im Quellsystem oder in einer Datenbank.

3. Die Uhr sollte anhalten, solange Sie auf den Kunden warten

Die meisten SLAs pausieren, solange ein Ticket den Status „wartet auf Kunde“ hat. Zeigt Ihr Export nur den aktuellen Status, können Sie diese Pausen nicht nachbilden, und der Report überzeichnet die Verstöße. Bitten Sie Ihren Systemadministrator um einen Export der Statushistorie mit einer Zeile pro Ticket und Statusphase, oder vereinbaren Sie, dass der Report die gesamte verstrichene Supportzeit zeigt, und schreiben Sie das in die Definition.

Liegt eine Statushistorie vor, rechnen Sie zuerst deren beide Zeitspalten genau wie in Abschnitt 1 um, denn dies ist eine andere Tabelle mit eigenen Zeitstempeln. Zählen Sie dann Geschäftsminuten nur für die Status, in denen die Uhr läuft:

let
    Source = StatusHistory,
    Local = Table.TransformColumns(
        Source,
        {
            {"StartTime", UtcToCentralEurope, type datetime},
            {"EndTime", each if _ = null then null else UtcToCentralEurope(_), type nullable datetime}
        }
    ),
    Running = Table.SelectRows(Local, each List.Contains({"New", "Open", "In progress"}, [Status])),
    WithMinutes = Table.AddColumn(
        Running,
        "BizMinutes",
        each BusinessMinutes(
            [StartTime],
            if [EndTime] = null then RefreshTimeLocal else [EndTime],
            Holidays
        ),
        type number
    ),
    PerTicket = Table.Group(
        WithMinutes,
        {"TicketID"},
        {{"ActiveBizMinutes", each List.Sum([BizMinutes]), type number}}
    )
in
    PerTicket

Die Statusnamen in der Liste müssen exakt zu Ihrem System passen. Übernehmen Sie sie aus dem Export, nicht aus dem Gedächtnis.

4. Wieder geöffnete Tickets

Wird ein Ticket gelöst und dann wieder geöffnet, startet dann die SLA-Uhr neu? Zwei Antworten sind üblich, und beide sind legitim. Jede ergibt einen anderen Report.

Startet die Uhr neu, kann das Ticket doppelt gezählt werden, oder ein erster Verstoß verschwindet hinter einer zweiten Lösung. Messen Sie gegen die erste Lösung, zeigt das SLA, wie schnell das Team reagiert hat, und Wiedereröffnungen erscheinen als eigenes Qualitätsproblem. Ich bevorzuge Letzteres und berichte die Wiedereröffnungsquote neben der SLA-Zahl:

Reopen Rate % =
DIVIDE (
    CALCULATE ( COUNTROWS ( Tickets ), Tickets[ReopenCount] > 0 ),
    COUNTROWS ( Tickets )
)

Behalten Sie in Power Query für jedes Ticket die erste Lösungszeit und verwenden Sie diese in den obigen Schritten, nicht die letzte. Welche Regel Sie auch wählen, schreiben Sie sie in den Report.

5. Der Nenner

Welche Tickets zählen? Stornierte Tickets, zusammengeführte Duplikate und Spam gehören nicht ins SLA. Und ein Ticket, das noch offen ist, aber sein Ziel schon überschritten hat, sollte heute als Verstoß zählen, nicht erst an dem Tag, an dem es jemand endlich schließt.

Führen Sie die Prioritäts-Nachschlagetabelle in die Ticket-Tabelle ein, um TargetMinutes zu erhalten, entfernen Sie Tickets außerhalb des Geltungsbereichs und geben Sie dann jedem Ticket einen von drei Status:

Table.AddColumn(
    WithTargets,
    "SlaStatus",
    each if [ActiveBizMinutes] > [TargetMinutes] then "Breached"
        else if [ResolvedTime] <> null then "Met"
        else "Pending",
    type text
)

Dann bleiben die Measures einfach:

Tickets Met = CALCULATE ( COUNTROWS ( Tickets ), Tickets[SlaStatus] = "Met" )

Tickets Breached = CALCULATE ( COUNTROWS ( Tickets ), Tickets[SlaStatus] = "Breached" )

SLA % = DIVIDE ( [Tickets Met], [Tickets Met] + [Tickets Breached] )

Ausstehende Tickets (Pending) bleiben auf beiden Seiten außen vor. Sie sind noch nicht gescheitert und noch nicht gelungen.

Dafür sorgen, dass es sich selbst aktualisiert

Eine Abfrage aktualisiert sich in Excel nicht von selbst. Sie aktualisiert sich, wenn etwas sie dazu auffordert.

Für eine wöchentliche Besprechung ist die einfachste Einstellung unter „Abfragen und Verbindungen“: Öffnen Sie die Abfrageeigenschaften und aktivieren Sie „Daten beim Öffnen der Datei aktualisieren“. Der Report aktualisiert sich dann, sobald jemand ihn öffnet.

Eine geschlossene Excel-Datei aktualisiert sich nicht von allein. Muss der Report aktuell sein, bevor ihn jemand öffnet, ist die übliche Antwort der Power-BI-Dienst mit geplanter Aktualisierung. Dafür brauchen Sie eine Pro- oder Premium-Per-User-Lizenz und, wenn die Quelle eine lokale Datei oder ein Vor-Ort-System ist, ein Datengateway. Haben Sie beides bereits, ist es keine neue Software.

Wann Sie doch etwas anderes brauchen

Drei Fälle rechtfertigen ein neues Werkzeug. Sie brauchen Echtzeit-Warnungen, also dass ein Verstoß im selben Moment gemeldet wird, in dem er passiert, und nicht erst beim nächsten Öffnen eines Reports. Ihr Quellsystem hat weder Export noch API. Oder Ihre Datenmengen liegen jenseits dessen, was Excel komfortabel bewältigt, also irgendwo in den Millionen von Zeilen. Außerhalb dieser drei Fälle bedeutet ein manueller SLA-Report meist, dass die Definition nie aufgeschrieben wurde, und eine neue Plattform würde nur die Verwirrung automatisieren.

Dieselben Schritte, vom rohen Export bis zum fertigen Dashboard, stehen in Vom Rohdatenexport zum Management-Dashboard.