Introduction:
At Cambridge Finance, we’re always exploring smarter ways to improve data analysis in financial environments. In this session, Felipe, one of our newest team members, walks us through a practical application of Power Query in Excel—a powerful but often underused tool. The focus? Using real-time guest feedback to enhance hotel performance through Net Promoter Score (NPS) analytics.
Why Customer Satisfaction Matters in Real Estate
Customer satisfaction, especially in hospitality and real estate, is more than just a metric—it’s a driver of loyalty and revenue. A satisfied hotel guest is more likely to:
- Return for future stays
- Recommend the hotel to others
- Maintain price consistency through high occupancy rates
In contrast, unhappy guests contribute to lower occupancy, discounting, and brand damage.
The Power of Net Promoter Score (NPS)
NPS is a standardized metric used globally to gauge customer loyalty. Guests are asked a simple question:
“On a scale of 0 to 10, how likely are you to recommend our hotel to a friend or colleague?”
Based on their answers, guests are categorized as:
- Promoters (9–10)
- Neutrals (7–8)
- Detractors (0–6)
The NPS formula:
% of Promoters – % of Detractors = NPS (range: -100 to +100)
For reference, the hotel industry average NPS is around 14. But a score above 50 is considered strong.
Tools Used for NPS Analysis
- Google Forms – For survey creation and real-time data collection
- Google Sheets – To store responses automatically
- Excel + Power Query – To transform and analyze data
- Power BI (optional) – For more advanced visualizations
Step-by-Step: Creating an NPS Dashboard
1. Build Your Survey in Google Forms
Include questions such as:
- NPS Question
- Hotel name (dropdown)
- Room number
- Satisfaction ratings (internet, breakfast, cleanliness, etc.)
- Open feedback
2. Connect Google Form to Google Sheets
Each response is automatically added to a Google Sheet.
3. Export to Excel
Download the spreadsheet to your local system to use with Power Query.
4. Use Power Query in Excel
- Go to
Data > Get Data > From File > Excel Workbook - Load or transform the data using Power Query Editor
- Add calculated columns:
- Promoter (1 if score ≥ 9)
- Detractor (1 if score ≤ 6)
- Neutral (1 if score = 7 or 8)
- Count (1 for every row)
- Year, Month, Start of Week (based on submission date)
5. Build a Pivot Table for NPS
- Add Start of Week to Rows
- Add Promoters, Detractors, Count to Values
- Create calculated fields:
% Promoters,% Detractors, then compute NPS as:(Promoters/Count – Detractors/Count) * 100
6. Visualize the Data
- Insert charts (line or bar)
- Add slicers for filters like Year, Month, Hotel
- Create a user-friendly dashboard for insights
Final Output: An Interactive NPS Report
With a few simple tools and some Power Query logic, you now have a dynamic report that tracks NPS over time, compares hotels, and identifies service areas needing attention.
Why This Matters
Implementing this workflow allows hotel managers and analysts to:
- Monitor customer satisfaction in real-time
- Act quickly on feedback
- Make data-driven decisions to improve service quality
It also showcases how Excel—when combined with modern tools like Power Query and Power BI—can handle robust analytics typically associated with more advanced platforms.
Conclusion:
At Cambridge Finance, we believe in empowering businesses with practical, efficient tools. Whether you’re forecasting cash flows or managing guest satisfaction, tools like Power Query bridge the gap between raw data and actionable insight. Thanks again to Felipe for this enlightening session!