Data & BI · Excel · Power Query · Power BI

GrandStay Hospitality — Operations Intelligence (Power BI case study)

A portfolio case study that builds a measurable time, scale and cost baseline for 120,000 guest-support tickets at a fictional global hotel group, before any decision to buy an automated “travel concierge”. Built in Excel, Power Query, PivotTables and Power BI.

TypePortfolio case study (not a client engagement)
ToolsExcel · Power Query · PivotTables · Power BI · PowerPoint
Grain1 row per ticket (120,000) + 12 monthly cost rows
PeriodTickets 9 Jul–7 Oct 2025; costs Nov 2024–Oct 2025
GrandStay executive Power BI dashboard showing 120,000 tickets, median first response time of 4.7 minutes, 28.09% SLA met and 60.11% repetitive tickets
Executive page of the Power BI dashboard (screenshot from the repository).

Problem

Guest support was under pressure: slow first response, missed SLAs, repetitive guest questions, rising cost per contact, falling utilisation and rising overtime. Leadership was considering an Intelligent Travel Concierge. This project measured the baseline first, so that any automation decision could be judged against facts rather than a vendor pitch.

Data and method

Source tables and grain
SourceGrainRows
Agent performance logs1 ticket120,000
Cost efficiency metrics1 month12
Knowledge / escalation sample1 ticket5,000
Inquiry volume patternshour × region × channel43,125
  • Power Query metrics FRT, HANDLE_TIME_MIN and SLA_STATUS, using each ticket’s own sla_target_min (Phone 1.5, Chat 2, Front-Desk 5, Email 30 minutes).
  • Ticket and cost tables were deliberately not joined, because they sit at different grains (ticket vs month).
  • Median first response time is reported instead of the mean: the mean (10.0 min) is pulled up by Email (about 39 min), while live channels sit near 5 minutes.

Results

Executive KPIs
KPIResult
Tickets120,000
Median first response time4.7 min
SLA met28.09%
Median handle time13.37 min
Repetitive tickets60.11%
Cost per contact (Oct 2025)$58.61
SLA and first response by channel (minutes)
ChannelSLA metOff-peak FRTPeak FRT
Phone15.28%2.806.20
Chat23.00%2.786.22
Email47.68%21.9148.90
Front-Desk60.77%2.796.24
Cost and workforce, Nov 2024 vs Oct 2025
MeasureFirstLast
Inquiries11,64916,219 (+39%)
Cost per contact$52.71$58.61
Utilisation86.0%72.8%
Overtime hours1,1961,824

Findings

  • Time. 72% of tickets missed SLA. Phone and Chat fail because their targets are 1.5–2 minutes, not because they are slower than Email.
  • Scale. 60% of tickets are flagged repetitive and take 10.8 minutes of handle time, versus 29.1 for other work.
  • Cost. Volume rose 39% while cost per contact rose and utilisation fell.
  • Automation. “Minutes saved” in the knowledge sample is largest on Legal, Billing, Emergency, Complaint and Contract tickets only because those tickets are already long, so it should not be used to rank automation candidates.

Recommendations

  1. Staff and route Chat and Phone in peak hours before buying a new tool; keep Email on its 30-minute target.
  2. Pilot a concierge on repetitive FAQ categories on Chat, with a human handoff for non-standard requests.
  3. Do not automate Legal, Emergency, Complaint, Contract or Billing first.
  4. Review roster against peak demand rather than hiring 1:1 with monthly ticket growth.
  5. Report median FRT and FRT by channel instead of a single 10-minute company average.

What this project demonstrates

  • Power Query modelling: ticket-level metrics (FRT, HANDLE_TIME_MIN, SLA_STATUS) built against each ticket’s own SLA target.
  • Grain discipline: ticket and monthly cost tables kept separate rather than joined at mismatched grains.
  • Power BI reporting: a three-page dashboard (executive, time and demand, cost and workforce).
  • Decision support: recommendations tied to the evidence, with an explicit warning against ranking automation by a misleading field.

Limits

No CSAT conclusion is drawn. The knowledge file is a sample. Cost cannot be sliced by channel. No bot was implemented, and no hours-saved or money-saved outcome exists in this dataset. The .pbix file and the 50 MB Excel workbook are intentionally kept off GitHub.

Written by Emmanuel Abolade, Data Analyst, Business Intelligence Developer and Full-Stack Software Developer based in Hull, UK. Every figure on this page comes from the linked public repository or live demo.