1 / 40

Science of Hotel Optimization Rooms Revenue Workshop

Science of Hotel Optimization Rooms Revenue Workshop. Day 1: Data Day 2: Analysis Day 3: Optimization. 50 minute periods. 10 minute break every 50 minutes. http://www.forsmarthotels.com/sohodocs. Ask every question. Dozens of Books, Lectures, Courses in 4 hrs . Contribute examples.

leland
Download Presentation

Science of Hotel Optimization Rooms Revenue Workshop

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Science of Hotel OptimizationRooms Revenue Workshop Day 1: Data Day 2: Analysis Day 3: Optimization

  2. 50 minute periods. 10 minute break every 50 minutes. http://www.forsmarthotels.com/sohodocs SOHO Day 1

  3. Ask every question. Dozens of Books, Lectures, Courses in 4 hrs. Contribute examples. Give me a Pace. I will go back. SOHO Day 1

  4. Day 1 Objectives Hour 1 • The Analytic RM • Hotel Data Science • Databases & Data Access Hour 2 • PMS Databases • MS Query Tool Hour 3 • Opera Data Tables • OLAP Tools (Pivot Table) • SQL Queries Hour 4 • SQL Queries • Excel Criteria Functions SOHO Day 1

  5. What is Analytics? • Not Reporting. • Misused and Abused. e.g. “Luxury” Applying mathematics to data to extract insights that lead to better decisions. SOHO Day 1

  6. The ‘M’ Word SOHO Day 1

  7. The Analytic RM • The Talent Shortage • 50% shortage in analytic positions by 2018. • The Hotel RM experience. • Analytic maturity. • 1-2% use RMS. • Black box algorithms. • Era of Rate Distribution ROI is over. • Incremental value of RM. SOHO Day 1

  8. Who would you pick to do your taxes? Bookkeeper Accountant SOHO Day 1

  9. The Analytic RM SOHO Day 1

  10. The Revenue Manager of 2023 Skillset Probability Statistics Analytics Toolbox Data & Granularity Choice & Demand Modeling Guest Behavior Drivers Forecasts Optimization Market Biases Rhythm of Business Communication SOHO Day 1

  11. The Hotel Data Scientist • Harvard Business Review. • Many disciplines. • Broad skill set. • Not from hospitality. • Ability to think “nerd” and speak “business”. • One foot in IT and one foot in the boardroom. • Extracts profit from data. • Start in RM. “This Workshop is your launch point.” SOHO Day 1

  12. Data Science Pyramid SOHO Data Science Prescriptive Day 3 Day 2 Predictive Day 1 Descriptive SOHO Day 1

  13. Your new world view • Data in its native environment. • At the databases level • Think “The Matrix”. • Put your “tech” hat on. SOHO Day 1

  14. Databases • A set of spreadsheet-like tables. • Multiple tables for efficiency. • Works hidden behind transaction software. SOHO Day 1

  15. Relational Occupancy Graph Reservation Entry Client Reservation Guest Profile Room Types OOOs Database SOHO Day 1

  16. Simple PMS Schema Unique keys connect each record(row). You must reconnect these tables to get all the data. SOHO Day 1

  17. Databases: Important Definitions Table: related data grid. Schema: Organization of tables Fact : Transaction e.g. Rate Paid one night Dimension: Explains Fact e.g. Source, Room Type Granularity : Level of detail e.g. POS has ticket detail Columns: Field Data Dictionary: Explains tables & columns. Warehouse: An organized copy. SOHO Day 1

  18. PMS Database Vendors Two licenses SOHO Day 1

  19. Opera Tables: Reservations History Sample company Upper Creek Resort 100 Rooms RESERVATION_DAILY_ELEMENTS Organized by Night RESERVATION_DAILY_ELEMENTS_NAME Organized by Reservation SOHO Day 1

  20. ODBC • Open Database Connection. • “Like rubbing the lamp.” • Access data via ODBC client. • Driver available from each database vendor (not PMS) for FREE. • Usually installed by IT. • Oracle ODBC client at Instructions at sohodocs. SOHO Day 1

  21. Four Data Table Analysis tools • Query Wizard – MS Query • OLAP – Picot Table • SQL • Excel – Multi-Criterion Functions SOHO Day 1

  22. MSQUERY • The most common operation for a business analyst is the query. • MSQuery is the simplest way to query a database. • Built into Excel. • Database agnostic. Vendor Neutral. • Creates a Real-time link to data. Data > From Other Sources > From Microsoft Query SOHO Day 1

  23. MSQUERY Connect Update Refresh SOHO Day 1

  24. Query Wizard Select Table Select Columns Filter Return Sort SOHO Day 1

  25. MS Query power tools • Criteria > Add Criteria • Table > Add Tables • Drag and Drop Join SOHO Day 1

  26. Excel Table • Behaves like a database table. • For related data. • Header Row • Filters • Sorting • Calculated Columns • Formatting • Insert/Delete • Structured references Instead of C2 [@[RESV_DAILY_EL_SEQ]] SOHO Day 1

  27. Pivot Table • OLAP Tool • Fast way to investigate data. • Multi-dimensional • Multi-perspectives • Drill-down • Slicing more at sohodocs Insert > Pivot Table SOHO Day 1

  28. Pivot Table What you are measuring • Values The Dimensions • Columns • Rows Slice of the data • Filters Tips • Start with the end in mind • Creative Thinking SOHO Day 1

  29. SQL: Standard Query Language • Common Language for Database • A very specific way to ask a database a question. • Universal ANSI standard. • We will use the query part. • DML Data manipulation language. • “The coding” is sensitive. SOHO Day 1

  30. Types of SQL Developed to connect data source to a programming language. Slight variations in code. SOHO Day 1

  31. SELECT • To select the columns to return. • What is to be returned. • “*” represents all SELECT column_name,column_name SOHO Day 1

  32. FROM • Defines the Table(s) to be used • Always required. [Database].[Table] SELECT column_name,column_name FROM table_name; SELECT RESV_NAME_ID FROM `C:\SOHODAY1.xlsx`.`Element$` SOHO Day 1

  33. WHERE • Your filters • Use operators • =, >, >=, <, <= • Between • In (list) • Multiple Conditions joined by “and” SELECT column_name,column_name FROM table_name WHERE column_name operator value; SELECT * FROM `C:\SOHODAY1.xlsx`.`Element$` WHERE (RESERVATION_DATE>{ts '2012-01-01 00:00:00'}) SOHO Day 1

  34. JOINS SELECT * FROM `C:\SOHODAY1.xlsx`.`Element_Name$` `C:\SOHODAY1.xlsx`.`Elements$` WHERE `Elements$`.RESV_DAILY_EL_SEQ = `Element_Name$`.RESV_DAILY_EL_SEQ SOHO Day 1

  35. GROUP BY • Group Functions • Common Arithmetic Count, Sum, Max, Min, Avg SELECT RESERVATION_DATE, SUM(RATE_AMOUNT), SUM(QUANTITY) FROM `C:\SOHODAY1.xlsx`.`Element$` WHERE (RESERVATION_DATE>{ts '2012-01-01 00:00:00'}) GROUP BY RESERVATION_DATE SOHO Day 1

  36. ALIASES • Renames a column in query • Careful not to use database or SQL words SELECT RESERVATION_DATE STAY, SUM(RATE_AMOUNT) REVENUE, SUM(QUANTITY) NIGHTS FROM `C:\SOHODAY1.xlsx`.`Element$` WHERE (RESERVATION_DATE>{ts '2012-01-01 00:00:00'}) GROUP BY RESERVATION_DATE SOHO Day 1

  37. Excel Vlookup • Go get data according to a match • Searches first column. • VLOOKUP( DATA TO MATCH, DATA GRID, COLUMN TO SEARCH, TYPE OF MATCH) SOHO Day 1

  38. Excel Multiple Criteria Functions • COUNTIFS • COUNTIFS(Column, Criteria,Column2, Criteria2,…) • SUMIFS • SUMIFS(Column to Sum,Column,Criteria1,…) • AVERAGEIFS Criteria filter – “operator”& e.g. “>=“&25 SOHO Day 1

  39. What we covered? • The New RM • Data • Databases • Tables • Columns • Dimensions • MSQuery • Pivot Table • SQL • Excel SOHO Day 1

  40. Next Friday: Analysis • Probability • Variance • Expected Value • Demand Curves • Forecasting SOHO Day 1

More Related