+57 304 492 7907

Many reservations are canceled at the last minute? Lets deploy an effective cancellation policy!

We will analyze the case of a hotel that +40% of its reservations are canceled the same day of arrival and whose only cancellation policy offers payment on arrival without penalty.

Hotel ABC has a 100% flexible cancellation policy. In which guests can make cancellations at any time without having a penalty and make the payment upon arrival at the hotel.

This policy generates a behavior of a high cancellation percentage (close to 40%, 4 out of 10 reservations are canceled 😳 ) and that the vast majority of these cancellations occur in the last days of arrival. They are ephemeral reservations, mostly with a time to live (TTL) of a single day. Most reservations created and canceled before 5 days of arrival. This entropy generates great uncertainty about revenue expectations and makes revenue management difficult, leading it to "micro-management" of price management and last-minute promotions to put out the fire. 🚒

Luckily, the current strategy has been performing successfully (in the post-covid tourism boom), with an average occupancy rate of the first semester close to 90%. The focus here is on strategy optimization.

How to optimize the strategy?

The first thing to find out is how much do the guests of this hotel make their payments by credit card? Although an advance payment is not required, with direct charge to the credit card; it would still be possible that guests prefer to use it upon arrival at the hotel. In this case, migrating towards a policy that requires advance payment by credit card, will be a natural step that would not generate a potential rejection in the conversion of reserves, within its natural market. To respond to this hypothesis, we will analyze the means of payment that have been used in a period of the last 5 months.

Two data files obtained from online queries to the hotel software have been created:

  • export_payment_list.csv It is a table of payments made, it contains the means of payment used and the amount .
  • rsv_df.csv It is a table of all reservations, it contains details of each reservation such as the agency that sold the reservation and the country of the guest.

By joining the two data sets, we will be able to determine not only which is the most used payment method to respond to the first hypothesis; but also how these payments are made by sales channel or country.

How often do guests use credit cards as a means of payment?

Only 20% do so, that is, 1 in 5 reservations are paid by credit card . In other words, if you want to implement the advance payment of reservations, 80% of the guests would be outside. There is a risk of immediate implementation.

import pandas as pd

# Read data sources.  Payments & Reservations
payments = pd.read_csv('export_payment_list.csv', sep=';')
df_rsvs = pd.read_csv('data/rsv_df.csv', parse_dates=[5, 7, 15, 16],  dayfirst=True)
df_rsvs.fillna(value={'agency':'Hotel ABC', 'customer_country':'Unknown'}, inplace=True)

# Inner Join payments & reservations dataframes
payments_rsv = payments.merge(df_rsvs, left_on='Reserva', right_on='id_human', how='inner', indicator=True)
payments_rsv.fillna({'agency':'Unknown', 'Tipo':'Unknown', 'customer_country':'Unknown'}, inplace=True)

payments_rsv_type = payments_rsv.groupby('Tipo', as_index=False)[['Cantidad']].sum().\
                    set_index('Tipo').sort_values(by='Cantidad', ascending=False)
payments_rsv_type.loc['Total'] = payments_rsv_type.sum()
payments_rsv_type['%'] = (payments_rsv_type['Cantidad'] / payments_rsv_type.iloc[-1,0])*100{"Cantidad": "${:20,.0f}", "%": "{:20,.2f}%"})


How is the credit card used by channels?

By disaggregating this 20% by sales channels, we have that 12.5% of payments by credit card are made by guests from and 7.8% by direct hotel sales, fourth and seventh place in the amount total payments. While the first three types of cash payments (Debit Card and Cash), correspond to about 50% of the total amount. In the case of (which is the channel through which the application of a more restrictive policy would begin), around one (1) out of every four (4) pesos paid is by credit card.

# Groupby type of payments by agency
payments_rsv_type_agency = payments_rsv.groupby(['agency', 'Tipo'])[['Cantidad']].sum().\
                                                sort_values(by='Cantidad', ascending=False)
payments_rsv_type_agency['%'] = (payments_rsv_type_agency['Cantidad'] / payments_rsv_type_agency\
                                 ['Cantidad'].sum())*100{"Cantidad": "${:20,.0f}", "%": "{:20,.2f}%"})\
                                        .bar(subset=["Cantidad",], color='lightgreen')


What kind of customers pay by credit card?

42% of guests who come from and pay by credit card are Colombian. It is a couple who create their reservation a week in advance, they have a stay for two nights at an average price of $170,000. 1 out of every 4 customers who pay with a credit card is Colombian, meaning that the majority of customers who pay with a credit card are foreigners .

customer_pay_by_credit_card = payments_rsv[payments_rsv['Tipo'] == 'Tarjeta De Crédito']
customer_pay_by_credit_card_agency = \
customer_pay_by_credit_card.groupby(['agency', 'customer_country'])\
                                    [['Cantidad', 'los', 'bWindow', 'adr', 'occupancy']].\
                                    agg({'Cantidad':'sum', 'los':'mean', 'bWindow':'mean',\
                                         'adr':'mean', 'occupancy':'mean'})\
                                        .sort_values(by='Cantidad', ascending=False)
customer_pay_by_credit_card_agency['%'] = (customer_pay_by_credit_card_agency['Cantidad']\
                                           / customer_pay_by_credit_card_agency['Cantidad'].sum())*100
customer_pay_by_credit_card_agency = customer_pay_by_credit_card_agency.\
                                     reindex(columns=['Cantidad', '%', 'los', 'bWindow', 'adr', 'occupancy']){"Cantidad":"${:20,.0f}", "adr":"${:20,.0f}",\
                        "%":"{:20,.2f}%", "los":"{:20,.1f}", "bWindow":"{:20,.1f}", "occupancy":"{:20,.1f}"})\
                                                .bar(subset=["Cantidad",], color='lightgreen')


What kind of customers pay with other means?

77% of payments made by payment methods other than credit cards are Colombian. It is a couple that makes a reservation between 10 and 6 days before their arrival, for 2 days and at an average price of $185,000 pesos per night. 3 out of 4 pesos paid by means other than a payment card are Colombian. The minority of customers who pay by means other than credit cards are foreigners .

customer_pay_by_other = payments_rsv[payments_rsv['Tipo'] != 'Tarjeta De Crédito']
customer_pay_by_other_agency = \
customer_pay_by_other.groupby(['agency', 'customer_country'])\
                                    [['Cantidad', 'los', 'bWindow', 'adr', 'occupancy']].\
                                    agg({'Cantidad':'sum', 'los':'mean', 'bWindow':'mean',\
                                         'adr':'mean', 'occupancy':'mean'})\
                                        .sort_values(by='Cantidad', ascending=False)
customer_pay_by_other_agency['%'] = (customer_pay_by_other_agency['Cantidad']\
                                           / customer_pay_by_other_agency['Cantidad'].sum())*100
customer_pay_by_other_agency = customer_pay_by_other_agency.\
                                     reindex(columns=['Cantidad', '%', 'los', 'bWindow', 'adr', 'occupancy']){"Cantidad":"${:20,.0f}", "adr":"${:20,.0f}",\
                        "%":"{:20,.2f}%", "los":"{:20,.1f}", "bWindow":"{:20,.1f}", "occupancy":"{:20,.1f}"})\
                                                .bar(subset=["Cantidad",], color='lightgreen')


To do?

  • Implement credit card advance payment policies only for foreign guests;
  • Make this implementation at the most economical rate and measure the results.
  • Apply the most restrictive policy for all nationalities in future booking periods, 3 months ahead and observe the changes. If they are positive, definitively implement the new policy in all rates.