47QMCB23N0007_Attachment B_ Hotel_Data_Elements_2023-10-03.xlsx
XLSX spreadsheet 163 KB Posted
- Solicitation number
- 47QMCB23N0007
- Issued by
-
GSA Federal Acquisition Service
About this file
This document contains an Excel spreadsheet listing data elements and definitions to be provided by vendors responding to Solicitation Number 47QMCB23N0007 for a Government Wide Temporary Duty (TDY) Lodging Solution 2.0. The spreadsheet includes over 100 data fields related to hotel property information, rates, bookings, and financial metrics. Key data to be provided includes hotel identifiers, addresses, rates accepted under the contract, room nights booked, revenues, cost avoidance calculations, property benchmarking data, reservation and voucher transaction details, and monthly financial and channel distribution reports. Providing this level of standardized data across vendors will facilitate analysis of the TDY lodging program performance.
View the file
Other files
Show all 17
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
Data Elements To Provide Govt
| MAINTAIN AND APPEND THESE FIELDS TO EXTRACTS: Master Hotel Property Information (Looking for fields to be normalized within a source of data and amongst data sources) | Response Requested | |||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Hotel ID | Vendor specific identifier for hotel | AHS209382 | Used for cross-referencing and joining information to other tables. Today this is "Harp Number" from current vendor. | Y | ||
| Hotel Name | Specific property name. | THE WESTIN CINCINNATI | Used in searches to identify properties within close proximity to Government location. | Y | ||
| Hotel Group | Corporate franchiser of that particular group are under the same management and belong to the same brand of hotels | MARRIOTT | Y | |||
| Hotel Chain | Administration company, that manages a number of hotels having the same name but being located in different areas. They can be total or partial owners of the hotel and they manage their administration, marketing and promotion. | WESTIN HOTELS | Y | |||
| Hotel Physical Address | Property address. | 21 E. 5TH ST. | Used in searches to identify proporties within close proximity to Government location. | Y | ||
| Hotel Postal City | City in which property is located | CINCINNATI | Used in searches to identify proporties within close proximity to Government location. | Y | ||
| Hotel State | State in which property is located | OH | Non-US properties will default to area or no data will be provided. (UK → State is England / Canada → will be the province) | Y | ||
| Hotel Zip | Postal Code of property | 45202 | Y | |||
| Hotel Country | Country in which property is located | USA | Y | |||
| Hotel County | County in which property is located | HAMILTON | Y | |||
| Hotel OCONUS or CONUS | Indicator whether or not the hotel is CONUS, OCONUS, or NFOCONUS (Non Foreign OCONUS) | CONUS | N | |||
| Hotel World Area | Property locations are registered within specific global areas | NORTH-AMERICA | Africa, Asia, Caribbean, Europe, Latin-America, Middle-East, North-America, Pacific are what are received today - could substitute Continent here if needed | Y | ||
| Hotel MSA | US Census Metropolitan Statistical Area | Cincinnati, OH-KY-IN | "Metro" is rec'd today. Would like it to tie with Census MSA rollup | N | ||
| Hotel MSA Short Name | US Census Metropolitan Statistical Area Shortened Name | Cincinnati OH | "Metro" is rec'd today. Would like it to tie with Census MSA rollup. Shortened name is done for reporting purposes to familiar area + state as opposed to all states (Cincinnati OH instead of Cincinnati, OH-IN-KY) | N | ||
| Hotel Phone | Hotel Phone Number | 1 (513) 621-7700 | Y | |||
| Hotel Per Diem Area | Per Diem area assigned by GSA's Per Diem Team | 282 | City/State and County rollups provided by GSA yearly | N | ||
| NTM Crown Rating | Northstar Travel Media: Editors review everything about the property – accommodations, range of services, amenities, public space and competitive level of the destination. Comments and candid photos on social media sites are also considered. Once all information is reviewed, a star rating is assigned. | 4 CROWNS | To be accepted within Fedrooms/DoD Preferred a property must have at least 2 Crowns. | Y | ||
| Star Rating Scale | Classification of Hotel based on # of Stars - economy, midscale, upscale, upper upscale, or luxury | UPSCALE | Y | |||
| AAA Rating | 3 Diamonds, etc | 3 DIAMONDS | Y | |||
| Sabre Property Number | Numeric indicator assigned to an individual property by Sabre | 16 | Y | |||
| Amadeus Property Number | Numeric indicator assigned to an individual property by Amadeus | 18 | N | |||
| Travelport Property Number | Numeric indicator assigned to an individual property by Amadeus | 19 | N | |||
| Hotel FEMA Number | Number certifies properties compliant with the 1990 Hotel and Motel Fire Safety Act (PL101-391). | OH0255 | United States and Territories are alpha-numeric with the state abbreviation followed by a numeric id. Non-US are numeric only. Note: To be considered the property must have a FEMA number. | Y | ||
| Self-Certified Small Business Indicator Current Year | If hotel was solicited, did the respondant certify this property as a small business | N | Y | |||
| Self-Certified Women Owned Small Business Indicator Current Year | If hotel was solicited, did the respondant certify this property as a woman owned small business | N | Desiring to capture this field if there are enough fields in hotel solicitation to capture as GSA is being asked to report | N | ||
| Self-Certified Veteran Owned Small Business Indicator Current Year | If hotel was solicited, did the respondant certify this property as a veteran owned small business | N | Desiring to capture this field if there are enough fields in hotel solicitation to capture as GSA is being asked to report | N | ||
| Self-Certified Service Disabled Veteran Owned Small Business Indicator Current Year | If hotel was solicited, did the respondant certify this property as a service disabled veteran small business | N | Desiring to capture this field if there are enough fields in hotel solicitation to capture as GSA is being asked to report | N | ||
| Self-Certified Disadvantaged Owned Small Business Indicator Current Year | If hotel was solicited, did the respondant certify this property as a disadvantaged owned small business | N | Desiring to capture this field if there are enough fields in hotel solicitation to capture as GSA is being asked to report | N | ||
| Self-Certified Sustainable Indicator Current Year | If hotel was solicited, did the respondant certify this property has as participating in a sustainable program | Y | N | |||
| Major Market Flag Current Year | Is the hotel identified as a major market of the hotel program (a MSA consisting of 50% of the hotel spend) | Y | Y | |||
| Property GPS Coordinates | Physical GPS coordinates to help distribution channels with mapping | 39.1010° N, 84.5121° W | Y | |||
| DOD Preferred Category Current Year | DOD Preferred, PPV (Public Private Venture), DOD Lodging, No | DOD Preferred | PPV = Army IHG Hotels as example…commercial lodging on a base. |
DOD Lodging= Government owned/operated.
DOD Preferred = In DOD Preferred Program, not on military installation
| No = Lodging property not in DOD Preferred Program or on installation | N | |||
| FedRooms Hotel Current Year | Y/N indicator if the hotel has also been accepted into FedRooms | Y | N |
| FedRooms® and DoD Preferred® Accepted Property Listing For Vendors (Master Hotel Property Information Plus These Elements From Solicitation/Responses). Frequency varies depending on time of year - see RFQ. | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| LRA or NLRA | Was rate accepted in current year a Last Room Available Rate (if there is 1+ rooms available traveler gets that rate) or Non Last Room Available Rate (hotel can close out inventory if hotel gets too full) | LRA | Y | |||
| Currency | Currency of the accepted rate | USD | Should be USD | Y | ||
| Average Contract Rate Blended | If rate is seasonal and has different values depending on the date range, this field is the blended/weighted average daily rate for the year | 128 | Y | |||
| Room 1 Start | Start Date of the accepted rate for the property for Date Range 1 | 1/1/22 | Y | |||
| Room 1 End | End Date of the accepted rate for the property for Date Range 1 | 12/31/22 | Y | |||
| Room1 Period1 Single | Rate bid by the property for Date Range 1 | $128.00 | Y | |||
| Room 2 Start | Start Date of the accepted rate for the property for Date Range 2 | Y | ||||
| Room 2 End | End Date of the accepted rate for the property for Date Range 2 | Y | ||||
| Room1 Period2 Single | Rate bid by the property for Date Range 2 | Y | ||||
| Room 3 Start | Start Date of the accepted rate for the property for Date Range 3 | Y | ||||
| Room 3 End | End Date of the accepted rate for the property for Date Range 3 | Y | ||||
| Room1 Period3 Single | Rate bid by the property for Date Range 3 | Y | ||||
| Room 4 Start | Start Date of the accepted rate for the property for Date Range 4 | Y | ||||
| Room 4 End | End Date of the accepted rate for the property for Date Range 4 | Y | ||||
| Room1 Period4 Single | Rate bid by the property for Date Range 4 | Y | ||||
| Room 5 Start | Start Date of the accepted rate for the property for Date Range 5 | Y | ||||
| Room 5 End | End Date of the accepted rate for the property for Date Range 5 | Y | ||||
| Room1 Period5 Single | Rate bid by the property for Date Range 5 | Y | ||||
| Cancellation Policy | Cancellation policy of hotels. Program hotels should be "BY 16:00 HOURS" | BY 16:00 HOURS | Y | |||
| Tax | Percentage of additional tax charged by locality. State tax may be exempt. | 23.40% | May include Locality Tax, etc. | Y | ||
| IBA Tax Exempt | Boolean (Y/N). Y if state sales tax is tax exempt | N | N | |||
| Breakfast Included? | Is breakfast included in the rate | Y | Y | |||
| Breakfast Type | Type of breakfast included in the rate | Continental Bfst | Y | |||
| Sales Contact | RFP Contact for Hotel | Mark Fidrych | Y | |||
| Sales Contact Email | RFP Contact email address | mark.fidrych@gmail.com | Y | |||
| Signature Date | Accepted rate agreement sign date | 09/24/2021 | Y | |||
| Signature By | Accepted rate agreement | Mark Fidrych | Y | |||
| Hotel Current Year Bid Status | Bid Status in current year's program | ACCEPTED | Y | |||
| Hotel Upcoming Year Bid Status | Bid status for the upcoming year's program | SOLICITED | N | |||
| FedRooms Hotel Current Year | Y/N indicator if the hotel has also been accepted into FedRooms | Y | Y | |||
| DOD Preferred Hotel Current Year | Y/N indicator if the hotel has also been accepted into DOD Preferred | Y | Y | |||
| Hotel Current Status Code | Currently GSA has a list of 22 Status Codes to identify a property's current status within the program and will provide. | Y | ||||
| Parking Included | Boolean (Y/N). Y if parking is included in accepted rate | N | N | |||
| Parking Daily Cost | Daily cost of parking | 16 | N |
| Monthly - FedRooms® and DoD Preferred® Contract Sales (All Master Hotel Property Information Fields Plus These Elements) | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Room Nights | FedRooms / DoD Preferred occupied room nights as reported by properties | 8 | Used to calculate revenue | Y | ||
| Room Revenue | Derived amount representing the total amount received from properties | 1432 | FedRooms Fee divided by FedRooms Rate (39.38/2.75%) As of 10/1/2019 rate was 1.75% | Y | ||
| Average Rate | Derived amount representing the standard amount received from properties | 179 | Total room revenue divided by total room nights (1,432/8) | Y | ||
| DoD Preferred Fee - DoD | Derived amount remitted to DoD of DoD Preferred room night revenue | 30.33 | TBD, will be released with Final RFQ. Room Revenue multiplied by DoD Fee (1,432*DoD Fee %) | N | ||
| DoD Preferred Fee - Vendor | Derived amount remitted to vendor of DoD Preferred room night revenue | 30.33 | Room nights * % fee | N | ||
| FedRooms Fee - DoD | Derived amount remitted to DoD of FedRooms room night revenue | 30.33 | TBD, will be released with Final RFQ. Room Revenue multiplied by DoD Fee (1,432*DoD Fee %) | N | ||
| FedRooms Fee - GSA | Derived amount remitted to GSA of FedRooms room night revenue | 31.85 | TBD, will be released with Final RFQ. Room Revenue multiplied by GSA Fee (1,432*GSA Fee %) | N | ||
| FedRooms Fee - Vendor | Derived amount representing the revenue amount FedRooms received from properties | 39.38 | Room nights * % fee | Y | ||
| Report Month | Month in which property reported a FedRooms occupancy | 201905 | YYYYMM format | Y | ||
| Payment Month | Month in which property paid for a FedRooms occupancy | 201909 | YYYYMM format | Y | ||
| Per Diem Monthly | Applicable lodging rate (based on government approved per diem) for this location for the given month. | 253 | Y |
| Monthly FedRooms® and DoD Preferred® Contract Cost Avoidance/Savings (Master Hotel Property Information Plus These Elements From Collection Data) | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Per Diem_Monthly | Applicable lodging rate (based on government approved per diem) | 253 | Y | |||
| FedRooms Net Rate Paid | Total FedRooms paid exclusive of taxes (room nts * daily rate) | 45,000 | Y | |||
| FedRooms Room Nights | Total Room Nights reported by lodging property for report month | 2500 | Y | |||
| Average Rate difference to Per Diem | Monertary difference between Monthly Per Diem and Average Property Daily Rate | 12.97 | Per Diem Monthly - Average Rate | Y | ||
| Savings to Per Diem | Total room night savings vs Per Diem | 16043.89 | Room Nights * Average Rate Difference to Per Diem | Y | ||
| Per Diem Total | Total monthly Per Diem for room nights booked | 116278 | Room Nights * Per Diem Monthly | Y | ||
| Report Month | Month in which property reported a FedRooms occupancy | 201905 | YYYYMM format | Y | ||
| Payment Indicator | Has amount been collected from Vendor | Y | Y or N | Y |
| Monthly Senior Level Information (Master Hotel Property Information Plus These Elements) | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| FedRooms Room Nights | FedRooms occupied room nights as reported by properties | Y | ||||
| FedRooms Sales | Derived amount representing the total amount received from properties | FedRooms Fee divided by FedRooms Rate (39.38/1.75%) | Y | |||
| FedRooms Cost Avoidance | Total amount of Savings to Per Diem | Sum(Total room night savings vs Per Diem) | Y | |||
| FedRooms IFF | Derived amount representing the revenue amount General Services Admin received from properties | Room Revenue multiplied by GSA Fee (1,432*.75%) | Y | |||
| FedRooms Average Per RN Savings off Per Diem | Average amount saved between Per Diem and FedRooms rate | Y | ||||
| DoD Preferred Room Nights | DoD Preferred occupied room nights as reported by properties | Y | ||||
| DoD Preferred Sales | Total revenue from booked DoD nights | DoD Fee divided by DoD Rate (39.38/2.75%) As of 10/1/2019 rate will be 1.75% | Y | |||
| DoD Preferred Cost Avoidance | Total amount of Savings to Per Diem | Y | ||||
| DoD Preferred IFF | Derived amount representing the revenue amount General Services Admin received from properties | Room Revenue multiplied by GSA Fee (1,432*.75%) | Y | |||
| DoD Preferred Average Per RN Savings off Per Diem | Average amount saved between Per Diem and DoD rate | Y | ||||
| FedRooms & DoD Preferred Room Nights | Total properties booked | Sum(FedRooms Room Nights & DoD Room Nights) | Y | |||
| FedRooms & DoD Preferred Sales | Total sales from properties booked | Sum(FedRooms Sales & DoD Sales ) | Y | |||
| FedRooms & DoD Preferred Cost Avoidance | Total savings based on Per Diem vs sales | Sum(FedRooms Cost Avoidance & DoD Cost Avoidance) | Y | |||
| FedRooms & DoD Preferred IFF | Total amount of fees paid to GSA | Sum(FedRooms IFF & DoD IFF) | Y | |||
| FedRooms & DoD Preferred Avg Per RN Savings off Per Diem | Total savings based on Per Diem vs sales | Sum(FedRooms Average Per RN Savings off Per Diem & DoD Average Per RN Savings off Per DiemIFF) | Y | |||
| Market Share YTD | Derived amount representing FedRooms & DoD Room Nights as a % of all Room Nights | Y | ||||
| Average Daily Rate YTD | Derived Amount. Total Room Nights * Total Sales | Y | ||||
| Percent below Per Diem | Total Cost Avoidance / (Total Sales + Total Cost Avoidance) | Y | ||||
| Historical Market Share | Total Sales / 5 yr Historical Average | Y |
| MONTHLY PROPERTY LEVEL BENCHMARK DATA EXTRACT: Master Hotel Property Information Plus These Elements From Agreed Upon Benchmark Data (which can then roll up into City Benchmarks, etc) | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Report Month | Month of Report | 202406 | (at a city level) | |||
| Valid Benchmark | Does the hotel listed have a valid benchmark sample | Y | Makes it easier to filter out | (at a city level) | ||
| FedRooms Hotel Current Quarter | Is the property listed a FedRooms property | Y | If the property was in the program at all for the Quarter then Y | (at a city level) | ||
| DOD Preferred Hotel Current Quarter | Is the property listed a DoD Preferred property | Y | If the property was in the program at all for the Quarter then Y | (at a city level) | ||
| Hotel Current Status Code | Current status code of the property | 9 | Allows us to filter out suspended, etc. | (at a city level) | ||
| FedRooms Includes LRA | If the FedRooms rate includes LRA then 1 else 0 | 1 | (at a city level) | |||
| FedRooms Includes Internet | If the FedRooms rate includes High Speed Internet then 1 else 0 | 1 | (at a city level) | |||
| FedRooms Includes Parking | If the FedRooms rate includes Parking then 1 else 0 | 1 | (at a city level) | |||
| FedRooms Includes Breakfast | If the FedRooms rate includes Breakfast then 1 else 0 | 1 | (at a city level) | |||
| FedRooms Total Spend | Total FedRooms voucher spend for designated month | $5,000 | (at a city level) | |||
| FedRooms Nights | Total Nights at FedRooms rates for designated month | 50 | (at a city level) | |||
| FedRooms Average Daily Rate | Average Daily Rate for FedRooms bookings for month | $100 | (at a city level) | |||
| Nightly Lodging Per Diem | Nightly per diem for hotel for month | $121 | (at a city level) | |||
| Avg Nightly Diff To Per Diem | Derived: Nightly Lodging Per Diem minus FedRooms Average Daily Rate | $21 | (at a city level) | |||
| Total $ Difference To Per Diem | Derived: Avg Nightly Diff To Per Diem * FedRooms Nights | $1,050 | Represents Property Level Savings to Per Diem | (at a city level) | ||
| % Diff From Per Diem | Derived: Avg Nightly Diff To Per Diem / (FedRooms Average Daily Rate - Nightly Lodging Per Diem) | 17.36% | 21/121 = 17.36% | (at a city level) | ||
| Corporate Benchmark Average Daily Rate Negotiated | Average Daily Rate Negotiated From Corporate Rate Benchmark | $108 | Meeting parameters of RFQ. Uses negotiated rates at property. | (at a city level) | ||
| Avg Corp Benchmark Rate Negotiated Diff To FedRooms | Derived: Corporate Benchmark Average Daily Rate minus FedRooms Average Daily Rate | $8 | Difference from negotiated rates | (at a city level) | ||
| Total $ Difference To Corporate Benchmark Rate Negotiated | Derived: Avg Corp Benchmark Diff To FedRooms * FedRooms Nights | $400 | Represents Savings To Corporate Benchmark negotiated rates | (at a city level) | ||
| % Diff From Corporate Benchmark Rate Negotiated | Derived: Avg Corp Benchmark Diff To FedRooms / Corp Benchmark Average Daily Rate | 7.41% | (at a city level) | |||
| % Rates At Property Including LRA | From Benchmark DB: What % of hotel property's rates include LRA | 72% | (at a city level) | |||
| % Rates At Property Including Internet | From Benchmark DB: What % of hotel property's rates include internet | 23% | (at a city level) | |||
| % Rates At Property Including Parking | From Benchmark DB: What % of hotel property's rates include parking | 14% | (at a city level) | |||
| % Rates At Property Including Breakfast | From Benchmark DB: What % of hotel property's rates include breakfast | 34% | (at a city level) | |||
| Corporate Benchmark Average Daily Rate Paid | Average Daily Rate Negotiated From Corporate Prices Paid data for month | $106 | Meeting parameters of RFQ. Uses corporate prices paid data at property. | N | ||
| Avg Corp Benchmark Rate Paid Diff To FedRooms | Derived: Corporate Benchmark Average Daily Rate Paid minus FedRooms Average Daily Rate | $6 | Difference from prices paid | N | ||
| Total $ Difference To Corporate Benchmark Rate Paid | Derived: Avg Corp Benchmark Paid Diff To FedRooms * FedRooms Nights | $300 | Represents Savings To Corporate Benchmark prices paid | N | ||
| % Diff From Corporate Benchmark Rate Paid | Derived: Avg Corp Benchmark Paid Diff To FedRooms / Corp Benchmark Average Daily Rate | 5.66% | N | |||
| DoD Preferred Nights | Total Nights at DoD Preferred rates for designated month | 20 | (at a city level) | |||
| DoD Preferred Average Daily Rate | Average Daily Rate for DoD Preferred bookings for month | $96 | (at a city level) | |||
| Avg Nightly Diff To Per Diem | Derived: Nightly Lodging Per Diem minus DoD Preferred Average Daily Rate | $25 | (at a city level) | |||
| Total $ Difference To Per Diem | Derived: Avg Nightly Diff To Per Diem * DoD Preferred Nights | $500 | Represents Property Level Savings to Per Diem | (at a city level) | ||
| % Diff From Per Diem | Derived: Avg Nightly Diff To Per Diem / (DoD Preferred Average Daily Rate - Nightly Lodging Per Diem) | 20.66% | 21/121 = 17.36% | (at a city level) | ||
| Avg Corp Benchmark Rate Negotiated Diff To DoD Preferred | Derived: Corporate Benchmark Average Daily Rate minus DoD Preferred Average Daily Rate | $12 | Difference from negotiated rates | N | ||
| Total $ Difference To Corporate Benchmark Rate Negotiated | Derived: Avg Corp Benchmark Diff To DoD Preferred * DoD Preferred Nights | $240 | Represents Savings To Corporate Benchmark negotiated rates | N | ||
| % Diff From Corporate Benchmark Rate Negotiated | Derived: Avg Corp Benchmark Diff To DoD Preferred / Corp Benchmark Average Daily Rate | 11.11% | N | |||
| Avg Corp Benchmark Rate Paid Diff To DoD Preferred | Derived: Corporate Benchmark Average Daily Rate Paid minus DoD Preferred Average Daily Rate | $10 | Difference from prices paid | N | ||
| Total $ Difference To Corporate Benchmark Rate Paid | Derived: Avg Corp Benchmark Paid Diff To DoD Preferred * DoD Preferred Nights | $200 | Represents Savings To Corporate Benchmark prices paid | N | ||
| % Diff From Corporate Benchmark Rate Paid | Derived: Avg Corp Benchmark Paid Diff To DoD Preferred / Corp Benchmark Average Daily Rate | 9.43% | N |
| MONTHLY RESERVATION TRANSACTIONAL EXTRACT TO GSA FROM VOUCHER DATA POST VENDOR CLEANSING: Master Hotel Property Information Plus These Elements From Reservation Data. | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Agency Name | Agency to which traveler belongs | DOJ | Used in searches to identify properties within close proximity to Government location. | Y | ||
| Sub-Agency Name | Sub-Agency to which the traveler belongs | DEPT OF JUSTICE-ONSITE ETS2 / DHSCBP015? | Used in searches to identify properties within close proximity to Government location. | Y | ||
| Travel Authorization Number | Alpha Numeric unique identifier for | T180106832 | Non-US properties will default to area or no data will be provided. (UK → State is England / Canada → will be the province) | Y | ||
| Hotel Check-In Date | Date in which stay began | 3/1/23 | MM/DD/YYYY | Y | ||
| Hotel Check-Out Date | Date in which stay ended | 3/13/23 | MM/DD/YYYY | Y | ||
| Per Diem Daily Rate | Applicable lodging rate (based on government approved per diem) - weighted if stay crossed multiple per diem periods | 185 | May not be provided or provided correctly by input files. Cross-referencing to per diem location and rates may be a QC. | Y | ||
| Daily Room Rate | Derived amount representing the standard amount received from properties | 179 | Total room revenue divided by total room nights (1,432/8) | Y | ||
| Total Room Nights | FedRooms occupied room nights as reported by properties | 12 | Used to calculate revenue | Y | ||
| Reservation Total | Derived amount representing the total amount received from properties | 2148 | FedRooms Fee divided by FedRooms Rate (39.38/1.75%) As of 10/1/2019 rate was 1.75% | Y | ||
| Rate Code | Rate Code on the booking | A00 | Y | |||
| Rate Type | A01AM9 | Y | ||||
| Rate Type Grouping | Grouping Based on Rate Type | XVU/XVC | Government will work with vendors to specify rules (XVU, GOV, ROH, etc) | N | ||
| FedRooms Rate? | Did consumer used FedRooms rate | Y/N/P | Boolean either Yes (Y) or No (N) Probable (P). If Rate Code not helpful for property/chain, vendor can match daily rate to FedRooms rate and indicate it as "Probable". | Y | ||
| DOD Preferred Rate | Did consumer used DOD Lodging rate | Y/N | Boolean either Yes (Y) or No (N) Probable (P). If Rate Code not helpful for property/chain, vendor can match daily rate to DOD Preferred rate. | N | ||
| Travel Purpose Code | Travel Purpose Code on the authorization | TRN | May or may not make its way onto the PNR | Y | ||
| Trip Purpose Code | Trip Purpose Code on the authorization | P2 | May or may not make its way onto the PNR | Y | ||
| GDS Record Locator Number | PNR | UQYTJH | Y | |||
| Cost Avoidance | Savings due to choosing FedRooms rate vs using Per Diem | 72 | Y | |||
| DOD Cost Avoidance | Savings due to choosing DOD Preferred rate vs using Per Diem | 0 | N | |||
| Trip Purpose Code | Trip Purpose Code on the authorization | P2 | Y | |||
| GDSBooking Indicator | Which GDS booked the reservation | SABRE | Sabre, Amadeus, Travelport | Y | ||
| Gds Property Number | GDS Identifier of the property | 321052 | Y | |||
| Hotel Confirmation Number | Confirmation Number Given By The Hotel | H239480 | Used to help with matching | N | ||
| Month Begin Date | First Day of Month of Hotel Check In Date (3/1/23, 4/1/23, etc) | 3/1/23 | N | |||
| Calendar Quarter Begin Date | First Day of Quarter of Hotel Check In Date (1/1/23, 4/1/23, 7/1/23, 10/1/23, etc) | 1/1/23 | N | |||
| Calendar Quarter Description | Descriptive Indicator of Calendar Quarter Begin Date | FY23Q2 | N | |||
| Calendar Year | Year of Hotel Check In Date | 2023 | N | |||
| Fiscal Year | Fiscal Year of Hotel Check In Date | FY23 | N | |||
| 30Plus Night Stay | If Total Room Nights >=30 then Y else N | N | N | |||
| Daily Difference To Per Diem | Daily Room Rate - Per Diem Daily Rate | 6 | Both values have to be present - if blanks in either Daily Room Rate or Per Diem Daily Rate then leave blank. | N | ||
| Total Difference To Per Diem (formerly Cost Avoidance) | Daily Difference To Per Diem * Total Room Nights | 72 | N | |||
| Difference To Per Diem Grouping | Descriptive indicator if Reservation was below, at, or above per diem | At Per Diem, Below Per Diem, Above Per Diem, N/A | Other is used if Total Difference To Per Diem is Blank | N |
| MONTHLY VOUCHER TRANSACTIONAL EXTRACT TO GSA FROM VOUCHER DATA POST VENDOR CLEANSING: Master Hotel Property Information Plus These Elements From Voucher Data. | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Agency Name | Agency to which traveler belongs | DOJ | Used in searches to identify properties within close proximity to Government location. | Y | ||
| Sub-Agency Name | DEPT OF JUSTICE-ONSITE ETS2 / DHSCBP015? | Used in searches to identify properties within close proximity to Government location. | Y | |||
| Travel Authorization Number | Alpha Numeric unique identifier for | T180106832 | Non-US properties will default to area or no data will be provided. (UK → State is England / Canada → will be the province) | Y | ||
| Hotel Check-In Date | Date in which stay began | 3/1/23 | MM/DD/YYYY | Y | ||
| Hotel Check-Out Date | Date in which stay ended | 3/13/23 | MM/DD/YYYY | Y | ||
| Per Diem Daily Rate | Applicable lodging rate (based on government approved per diem) - weighted if stay crossed multiple per diem periods | 185 | May not be provided or provided correctly by input files. Cross-referencing to per diem location and rates may be a QC. | Y | ||
| Daily Room Rate | Derived amount representing the standard amount received from properties | 179 | Total room revenue divided by total room nights (1,432/8) | Y | ||
| Total Room Nights | FedRooms occupied room nights as reported by properties | 12 | Used to calculate revenue | Y | ||
| Reservation Total | Derived amount representing the total amount received from properties | 2148 | FedRooms Fee divided by FedRooms Rate (39.38/1.75%) As of 10/1/2019 rate was 1.75% | Y | ||
| Rate Code | Rate Code on the booking | A00 | May not be captured on the voucher - ETSNext Dependent | Y | ||
| Rate Type Grouping | Grouping Based on Rate Type | XVU/XVC | Government will work with vendors to specify rules (XVU, GOV, ROH, etc) | Y | ||
| FedRooms Rate? | Did consumer used FedRooms rate | Y/N | Boolean either Yes (Y) or No (N). If Rate Code not helpful for property/chain, vendor can match daily rate to FedRooms rate. | Y | ||
| DOD Preferred Rate | Did consumer used DOD Lodging rate | Y/N | Boolean either Yes (Y) or No (N). If Rate Code not helpful for property/chain, vendor can match daily rate to DOD Preferred rate. | N | ||
| Travel Purpose Code | TRN | From ETS | Y | |||
| Trip Purpose Code | P2 | From ETS | Y | |||
| GDS Record Locator Number | PNR | UQYTJH | Y | |||
| Cost Avoidance | Savings due to choosing FedRooms rate vs using Per Diem | 72 | Y | |||
| DOD Cost Avoidance | Savings due to choosing DOD Preferred rate vs using Per Diem | 0 | N | |||
| Trip Purpose Code | Trip Purpose Code on the authorization | P2 | Y | |||
| GDSBooking Indicator | Which GDS booked the reservation | SABRE | Sabre, Amadeus, Travelport | Y | ||
| Gds Property Number | GDS Identifier of the property | 321052 | Y | |||
| Hotel Confirmation Number | Confirmation Number Given By The Hotel | H239480 | Used to help with matching | N | ||
| TA/Voucher Number | Voucher number | 88888888888 | Y | |||
| Month Begin Date | First Day of Month of Hotel Check In Date (3/1/23, 4/1/23, etc) | 3/1/23 | N | |||
| Calendar Quarter Begin Date | First Day of Quarter of Hotel Check In Date (1/1/23, 4/1/23, 7/1/23, 10/1/23, etc) | 1/1/23 | N | |||
| Calendar Quarter Description | Descriptive Indicator of Calendar Quarter Begin Date | FY23Q2 | N | |||
| Calendar Year | Year of Hotel Check In Date | 2023 | N | |||
| Fiscal Year | Fiscal Year of Hotel Check In Date | FY23 | N | |||
| Rate Type Grouping | Grouping Based on Rate Type | XVU | Government will work with vendors to specify rules (XVU, GOV, ROH, etc) | N | ||
| 30Plus Night Stay | If Total Room Nights >=30 then Y else N | N | N | |||
| Daily Difference To Per Diem | Daily Room Rate - Per Diem Daily Rate | 6 | Both values have to be present - if blanks in either Daily Room Rate or Per Diem Daily Rate then leave blank. | N | ||
| Total Difference To Per Diem (formerly Cost Avoidance) | Daily Difference To Per Diem * Total Room Nights | 72 | N | |||
| Difference To Per Diem Grouping | Descriptive indicator if Reservation was below, at, or above per diem | At Per Diem, Below Per Diem, Above Per Diem, N/A | Other is used if Total Difference To Per Diem is Blank | N | ||
| N | ||||||
| MONTHLY EXTRACT TO GSA & DoD FROM SMARTPAY CARD DATA POST VENDOR CLEANSING: Master Hotel Property Information Plus These Elements From SmartPay Card Data | ||||||
| ElementName | Definition | Example Value | Notes | Received Today | Can Vendor Provide | Vendor Comments/Considerations |
| Bank | Issuing bank of credit card | USBK | Y | |||
| IBA CBA Indicator | Was card an IBA or CBA | I | Boolean (I = IBA, C=CBA) | Y | ||
| Debit/Credit Indicator | Is charge a debit or credit | D | Boolean (D=Debit, C=Credit) | Y | ||
| Transaction Date | Posting Date of Transaction | 6/29/23 | Y | |||
| Transaction Amount | Amount of Credit Card Charge | 110.74 | Y | |||
| Tax Amount | Tax Amount of Credit Card Charge | 12.74 | Y | |||
| Merchant ID | Merchant id of lodging vendor | 873827612 | ||||
| Y | ||||||
| MCC Vendor Code | MCC Code | 3715 | ||||
| Y | ||||||
| MCC Vendor Grouping | MCC Code Descriptive Name | FAIRFIELD INN | ||||
| Y | ||||||
| Agency Code | Federal Agency Identifier | 12G2 | ||||
| Y | ||||||
| Agency Code Description | Federal Agency Description | Food Safety and Inspection Service | ||||
| Y | ||||||
| Daily Rate | Daily Rate of Hotel | 98 | Would be 0 for an ancillary charge | Y | ||
| Total Spend | Y | |||||
| Room Nights | Number of Room Nights | 1 | Would be 0 for an ancillary charge - From Level 3 Data | Y | ||
| Daily Tax | Daily Tax Rate | 12.74 | Would be 0 for an ancillary charge | Y |
| BI-WEEKLY DEC-APR AND MONTHLY THEREAFTER - IMPLEMENTATION STATUS AND GDS AUDIT FINDINGS REPORT/DELIVERABLE (this is at program level, not individual property level) | ||||
| ElementName | Definition | Example Value | Notes | Received Today |
| Report Date | Report Date | 2/16/23 | Y | |
| Total Hotels Solicited | The number of hotels solicited for program year | 12,135 | Y | |
| Total Hotels Accepted | The number of hotels accepted for program year | 11,203 | Y | |
| Total Hotels Correctly Loaded | Rates and terms are correctly loaded; | 11,103 | Y | |
| Total Hotels at Risk | Number of hotels at risk of being suspended for incorrect loading of rates and terms; | 100 | Y | |
| Total Squatters | Number of squatters identified. | 342 | Y |
| MONTHLY FedRooms.com BOOKING REPORT (Master Hotel Property Information Plus These Fields) | ||||
| ElementName | Definition | Example Value | Notes | Received Today |
| ReportMonth | Month for which report is run | 202406 | Y | |
| Agency Name | Agency to which traveler belongs (if provided) | DOJ | Used in searches to identify properties within close proximity to Government location. | Y |
| Website RoomNights | # of room nights booked through the FedRooms.com channel | 231 | Y | |
| Website Rate Booked | Average daily rate (for that property) for the given month | $172 | Y | |
| Website Total Room Spend Booked | Total daily rate * # of room nights for property for the month | $39,732 | Y |
| QUARTERLY FEDROOMS & DoD Preferred CHANNEL DISTRIBUTION (Master Hotel Property Information Plus These Fields) | ||||
| ElementName | Definition | Example Value | Notes | Received Today |
| ReportQuarter | Quarter of Report | FY24Q1 | Y | |
| Channel Description | Was booking ETS2/DTS, TMC, Call Direct, FedRooms.com, Brand.com | ETS/DTS | Y | |
| Room Nights | Room Nights for each channel for the reporting quarter | 29,409 | Y |
mailto:mark.fidrych@gmail.comhttp://fedrooms.com/http://brand.com/ Notional Input Files From Govt
| MONTHLY RESERVATION TRANSACTIONAL DATA FILE: Need to Load/Analyze/Normalize Reservation Data Received From ETS | ||||||||||||||
| ElementName | Definition | Example Value | Notes | Received Today | Vendor Comments/Considerations | |||||||||
| Hotel Name | Specific property name. | THE WESTIN CINCINNATI | Used in searches to identify properties within close proximity to Government location. | Y | ||||||||||
| Hotel Physical Address | Property address. | 21 E. 5TH ST. | Used in searches to identify proporties within close proximity to Government location. | y | ||||||||||
| Hotel Postal City | City in which property is located | CINCINNATI | Used in searches to identify proporties within close proximity to Government location. | Y | ||||||||||
| Hotel State | State in which property is located | OH | Not required for non-US locations, but may be provided as local standards apply (e.g.; Canada / Australia will use the two letter province code) | Y | ||||||||||
| Hotel Zip | Postal Code of property | 45202 | Y | |||||||||||
| Hotel Country | Country in which property is located | USA | Y | Hotel Country | Country in which property is located | USA | Hotel Country | Country in which property is located | USA | Hotel Country | Country in which property is located | USA | ||
| Agency Name | Government agency to which traveler belongs | DOJ | Identifies the US Government agency or sub-agency for which the traveller belongs (or is traveling for) | Y | ||||||||||
| Sub-Agency Name | Government Department, Division, Office or other entity under the agency reported in the field Agency Name | DEPT OF JUSTICE-ONSITE ETS2 / DHSCBP015? | Y | |||||||||||
| Travel Authorization Number | Alpha Numeric unique identifier for | T180106832 | Civilian TA Number is usually 10 characters - DOD is usually 6 characters | Y | ||||||||||
| Hotel Check-In Date | Date in which stay began | 3/1/23 | MM/DD/YYYY | Y | ||||||||||
| Hotel Check-Out Date | Date in which stay ended | 3/13/23 | MM/DD/YYYY | Y | ||||||||||
| Per Diem Daily Rate | Applicable lodging rate (based on government approved per diem) - weighted if stay crossed multiple per diem periods | 170 | Weighting would cover cross-seasonal periods. If the traveler stayed at a property where 3 nights where the per diem was $150 and 2 nights where the per diem was $200, the formula would be ((3*150)+(2*200))/5 days = $170 | Y | ||||||||||
| Daily Room Rate | Hotel average daily room rate | 135 | Weighting would cover cross-seasonal periods. If the traveler stayed at a property where 3 nights where the daily rate was $125 and 2 nights where the daily rate was $150, the formula would be ((3*125)+(2*150))/5 days = $135 | Y | ||||||||||
| Total Room Nights | Total Room Nights | 5 | Y | |||||||||||
| Reservation Total | Derived Field representing Daily Room Rate * Total Room Nights. | 675 | This total is used to calculate the total FedRooms fee. 0.75% * total = FedRooms fee. | Y | ||||||||||
| Rate Code | Rate Code on the booking | XVU | This has been a source of challenge for TMC's and OBT's writing the correct rate code. Some chains scramble their rate code or have it within the remarks. This is a key to understanding FedRooms Rate Usage for audit. | Y | ||||||||||
| Travel Purpose Code | Captured in the tool or by the agent | TRN | From ETS | Intermittent | ||||||||||
| Trip Purpose Code | Captured in the tool or by the agent | P2 | From ETS | Intermittent | ||||||||||
| GDS Record Locator Number | PNR | UQYTJH | Y | |||||||||||
| GDSBooking Indicator | Which GDS booked the reservation | SABRE | Sabre, Amadeus, Travelport | Y | ||||||||||
| Gds Property Number | GDS Identifier of the property | 321052 | Y | |||||||||||
| Hotel Confirmation Number | Confirmation Number Given By The Hotel | H239480 | Used to help with matching | Intermittent |
| MONTHLY VOUCHER TRANSACTIONAL DATA FILE: Need to Load/Analyze/Normalize Reservation Data Received From ETS | |||||
| ElementName | Definition | Example Value | Notes | Received Today | Vendor Comments/Considerations |
| Hotel Name | Specific property name. | THE WESTIN CINCINNATI | Used in searches to identify properties within close proximity to Government location. | Y | |
| Hotel Physical Address | Property address. | 21 E. 5TH ST. | Used in searches to identify proporties within close proximity to Government location. | Y | |
| Hotel Postal City | City in which property is located | CINCINNATI | Used in searches to identify proporties within close proximity to Government location. | Y | |
| Hotel State | State in which property is located | OH | Not required for non-US locations, but may be provided as local standards apply (e.g.; Canada / Australia will use the two letter province code) | Y | |
| Hotel Zip | Postal Code of property | 45202 | Y | ||
| Hotel Country | Country in which property is located | USA | Y | ||
| Agency Name | Government agency to which traveler belongs | DOJ | Identifies the US Government agency or sub-agency for which the traveller belongs (or is traveling for) | Y | |
| Sub-Agency Name | Government Department, Division, Office or other entity under the agency reported in the field Agency Name | DEPT OF JUSTICE-ONSITE ETS2 / DHSCBP015? | Y | ||
| Travel Authorization Number | Alpha Numeric unique identifier for | T180106832 | Civilian TA Number is usually 10 characters - DOD is usually 6 characters | Y | |
| Hotel Check-In Date | Date in which stay began | 3/1/23 | MM/DD/YYYY | Y | |
| Hotel Check-Out Date | Date in which stay ended | 3/13/23 | MM/DD/YYYY | Y | |
| Per Diem Daily Rate | Applicable lodging rate (based on government approved per diem) - weighted if stay crossed multiple per diem periods | 170 | Weighting would cover cross-seasonal periods. If the traveler stayed at a property where 3 nights where the per diem was $150 and 2 nights where the per diem was $200, the formula would be ((3*150)+(2*200))/5 days = $170 | Y | |
| Daily Room Rate | Hotel average daily room rate | 135 | Weighting would cover cross-seasonal periods. If the traveler stayed at a property where 3 nights where the daily rate was $125 and 2 nights where the daily rate was $150, the formula would be ((3*125)+(2*150))/5 days = $135 | Y | |
| Total Room Nights | Total Room Nights | 5 | Y | ||
| Reservation Total | Derived Field representing Daily Room Rate * Total Room Nights. | 675 | This total is used to calculate the total FedRooms fee. 0.75% * total = FedRooms fee. | Y | |
| Rate Code | Rate Code on the booking | XVU | This has been a source of challenge for TMC's and OBT's writing the correct rate code. Some chains scramble their rate code or have it within the remarks. This is a key to understanding FedRooms Rate Usage for audit. | Y | |
| Travel Purpose Code | Captured in the tool or by the agent | TRN | From ETS | Intermittent | |
| Trip Purpose Code | Captured in the tool or by the agent | P2 | From ETS | Intermittent | |
| GDS Record Locator Number | PNR | UQYTJH | Y | ||
| GDSBooking Indicator | Which GDS booked the reservation | SABRE | Sabre, Amadeus, Travelport | Y | |
| Gds Property Number | GDS Identifier of the property | 321052 | Y | ||
| Hotel Confirmation Number | Confirmation Number Given By The Hotel | H239480 | Used to help with matching | Intermittent | |
| Travel Voucher Number | Unique Identifier of Travel Voucher | 209852 | May or may not differ from Travel Authorization Number. | Intermittent |
| MONTHLY SMARTPAY FILE: Need to Load GSA SmartPay Credit Card Data (either from GSA or directly from bank) Containing These Elements To Help Understand Universe Of Spend/Leakage | ||||
| ElementName | Definition | Example Value | Notes | Received Today |
| Bank | Issuing bank of credit card | USBK | XXX | Y |
| IBA CBA Indicator | Was card an IBA or CBA | I | Boolean (I = IBA, C=CBA) | Y |
| Debit/Credit Indicator | Is charge a debit or credit | D | Boolean (D=Debit, C=Credit) | Y |
| Transaction Date | Posting Date of Transaction | 6/29/23 | Y | |
| Transaction Amount | Amount of Credit Card Charge | 110.74 | Y | |
| Tax Amount | Tax Amount of Credit Card Charge | 12.74 | Y | |
| Lodging Vendor Name | Lodging vendor name | FAIRFIELD INN & SUITES | ||
| Y | ||||
| Lodging Vendor Address | Lodging vendor address | 105 WEST INTERSTATE 20 | ||
| Y | ||||
| Lodging Vendor City | Lodging vendor city | MARSHALL | Y | |
| Lodging Vendor State | Lodging vendor state | TX | Y | |
| Lodging Vendor Zip | Lodging vendor zip | 7457200000000 | Y | |
| Lodging Vendor Country | Lodging vendor country | USA | Y | |
| Lodging Vendor Phone | Lodging vendor phone number | 9039387666 | ||
| Y | ||||
| Merchant ID | Merchant id of lodging vendor | 873827612 | ||
| Y | ||||
| MCC Vendor Code | MCC Code | 3715 | ||
| Y | ||||
| MCC Vendor Grouping | MCC Code Descriptive Name | FAIRFIELD INN | ||
| Y | ||||
| Agency Code | Federal Agency Identifier | 12G2 | ||
| Y | ||||
| Agency Code Description | Federal Agency Description | Food Safety and Inspection Service | ||
| Y | ||||
| Daily Rate | Daily Rate of Hotel | 98 | Would be 0 for an ancillary charge | Y |
| Room Nights | Number of Room Nights | 1 | Would be 0 for an ancillary charge - From Level 3 Data | Y |
| Daily Tax | Daily Tax Rate | 12.74 | Would be 0 for an ancillary charge | Y |
| Total Tax | Total Tax Paid (Daily Tax * Room Nights) | 12.74 | Would be 0 for an ancillary charge |
File details come from the government source that posted it. Updated .