Data Overview:

A. Loading dataset:

# Load libraries: 

library(dplyr)
library(ggplot2)
library(corrplot)
library(summarytools)
library(naniar)
library(kableExtra)
library(tidyr)

# Loading the dataset:

data <- read.csv('Assesment_Row.csv')

B. Exploring data dimensions :

# Get the dimensions of the dataset
data_dimensions <- dim(data)

data_dimensions

## [1] 483594     12

C. Exploring data structure:

Get the data type / Class of each column

data_types <- sapply(data, class)


# Convert the result to a data frame

data_types_df <- data.frame(Column = names(data_types), Type = data_types)

# Display the table with kable

data_types_df %>%
  kbl() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"),
                full_width = F,
                position = "left")

	Column	Type
YEAR	YEAR	integer
MONTH	MONTH	integer
CHANNEL	CHANNEL	character
SEGMENT	SEGMENT	character
BONVOYMEMBER	BONVOYMEMBER	character
KEY_MARKETS	KEY_MARKETS	character
BRAND_TYPE	BRAND_TYPE	character
REGION_NAME	REGION_NAME	character
RES_LOS_TIER_TXT	RES_LOS_TIER_TXT	character
RES_NIGHTS_OUT_TIER_TXT	RES_NIGHTS_OUT_TIER_TXT	character
ROOM_NIGHTS	ROOM_NIGHTS	character
ROOM_REVENUE	ROOM_REVENUE	integer

D. Update the data types:

From the results of the previous code, we can see that there are some columns need the class to be updated such as:

Room nights column: Data class is “Character” while it suppose to be “integer”.
Month / Year: Data Class is “Integer” while it suppose to be Date (Month,Year).

But before that will create a function to check if there are NAs in some columns:

check_nas_all_columns <- function(data) {
  
  # Initialize an empty list to store results
  nas_list <- list()
  
  # Loop through each column in the dataset
  for (column_name in names(data)) {
    
    # Calculate the number of NAs in the current column
    nas_count <- sum(is.na(data[[column_name]]))
    
    # Store the result in the list with the column name as the key
    nas_list[[column_name]] <- nas_count
  }
  
  # Convert the list to a data frame for easier viewing
  nas_df <- as.data.frame(do.call(rbind, nas_list))
  names(nas_df) <- c("NA_Count")
  
  return(nas_df)
}


nas_result <- check_nas_all_columns(data)
print(nas_result)

##                         NA_Count
## YEAR                           0
## MONTH                          0
## CHANNEL                        0
## SEGMENT                        0
## BONVOYMEMBER                   0
## KEY_MARKETS                    0
## BRAND_TYPE                     0
## REGION_NAME                    0
## RES_LOS_TIER_TXT               0
## RES_NIGHTS_OUT_TIER_TXT        0
## ROOM_NIGHTS                    0
## ROOM_REVENUE                   0

print(sum(nas_result))

## [1] 0

As there are no NAs in the data set will continue to check if there are non numeric values in the columns that suppose to contain only numeric values such as Room nights and Revenue columns:

#check if there are non numeric values:
# create function to get the non numeric values in certin columns:

check_non_numeric <- function(data, column_name){
  
  non_numeric_rows <- data %>%
    filter(!grepl("^[0-9]+$", .data[[column_name]]))
  
  non_numeric_indexes <- which(!grepl("^[0-9]+$", data[[column_name]]))
  
  print(non_numeric_rows[[column_name]])
  print(non_numeric_indexes)
  
}



Room_nights_non = check_non_numeric(data, "ROOM_NIGHTS")

## [1] "217 rn" "1 rn"   "75 rn"  "21 rn"  "6 rn"  
## [1]  30137 177791 339629 395901 470768

Room_Revenue_non = check_non_numeric(data, "ROOM_REVENUE")

## integer(0)
## integer(0)

year = check_non_numeric(data, "YEAR")

## integer(0)
## integer(0)

month_Revenue_non = check_non_numeric(data, "MONTH")

## integer(0)
## integer(0)

The function showed that there are some data (5 values) in Room nights columns that need to be handled before converting the cloumn into the right class, Rest of the columns contains 0 Non_Numeric values.

Handling the room nights non_numeric values by creating a loop that will loop through the non numeric values index that we optained from the check_non_numeric function and will remove the pattern “rn” from each one.

specific_indexes <- data.frame(index = c(30137, 177791, 339629, 395901, 470768))

# creating the loop :

for (i in specific_indexes) {
  
  data$ROOM_NIGHTS[i] <- gsub(" rn", "", data$ROOM_NIGHTS[i])
  
}

Now will test of the loop worked by:

Run the check_non_numeric function on room night one more time.
will print the indexes to see if the pattern “rn” was cleared.

check_non_numeric(data,'ROOM_NIGHTS')

## character(0)
## integer(0)

print(data$ROOM_NIGHTS[c(30137, 177791, 339629, 395901, 470768)])

## [1] "217" "1"   "75"  "21"  "6"

Proceed with updating the data Classes:

Converting Room nights into integer.
Converting all Character class columns into factors.

df <- data %>%
  mutate(ROOM_NIGHTS = as.integer(ROOM_NIGHTS),
         YEAR = as.factor(YEAR))%>%
  mutate_if(is.character, as.factor)

Final check the updated data classes:

data_types_df <- sapply(df, class)


# Convert the result to a data frame

data_types_df <- data.frame(Column = names(data_types_df), Type = data_types_df)

# Display the table with kable

data_types_df %>%
  kbl() %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"),
                full_width = F,
                position = "left")

	Column	Type
YEAR	YEAR	factor
MONTH	MONTH	integer
CHANNEL	CHANNEL	factor
SEGMENT	SEGMENT	factor
BONVOYMEMBER	BONVOYMEMBER	factor
KEY_MARKETS	KEY_MARKETS	factor
BRAND_TYPE	BRAND_TYPE	factor
REGION_NAME	REGION_NAME	factor
RES_LOS_TIER_TXT	RES_LOS_TIER_TXT	factor
RES_NIGHTS_OUT_TIER_TXT	RES_NIGHTS_OUT_TIER_TXT	factor
ROOM_NIGHTS	ROOM_NIGHTS	integer
ROOM_REVENUE	ROOM_REVENUE	integer

E. Elementary Data Analysis

Will do some Elementary exploration for each year

# for 2016 : 

CY <- df %>%
  filter(YEAR == '2016')

kable(head(CY,5), format = "pipe")

YEAR	MONTH	CHANNEL	SEGMENT	BONVOYMEMBER	KEY_MARKETS	BRAND_TYPE	REGION_NAME	RES_LOS_TIER_TXT	RES_NIGHTS_OUT_TIER_TXT	ROOM_NIGHTS	ROOM_REVENUE
2016	1	Property	GROUP	Non-member	Other	Premium	Europe	Tier 3: 12 to 29	Tier 5: 30 to 59	233	46916
2016	1	Property	TRANSIENT/RENTALS	Non-member	Other	Select	Middle East	Tier 2: 5 to 11	Tier 6: 60+	178	26140
2016	1	Property	GROUP	Non-member	Other	Premium	Europe	Tier 1: 1 to 4	Tier 4: 16 to 29	6955	1373710
2016	1	Property	GROUP	Non-member	Dubai	Select	Middle East	Tier 1: 1 to 4	Tier 5: 30 to 59	164	14617
2016	1	Property	CONTRACT/OWNERS	Non-member	Heathrow	Premium	Europe	Tier 1: 1 to 4	Tier 1: 0 to 3	10859	1063374

# for 2015 : 

LY <- df%>%
  filter(YEAR == '2015')

kable(head(LY,5), format = 'pipe')

YEAR	MONTH	CHANNEL	SEGMENT	BONVOYMEMBER	KEY_MARKETS	BRAND_TYPE	REGION_NAME	RES_LOS_TIER_TXT	RES_NIGHTS_OUT_TIER_TXT	ROOM_NIGHTS	ROOM_REVENUE
2015	11	Property	TRANSIENT/RENTALS	Non-member	Dubai	Select	Middle East	Tier 2: 5 to 11	Tier 2: 4 to 7	723	119695
2015	11	eChannel	TRANSIENT/RENTALS	Non-member	Other	Luxury	Europe	Tier 1: 1 to 4	Tier 1: 0 to 3	1225	437852
2015	11	Marriott.com	TRANSIENT/RENTALS	Titanium	Amsterdam	Select	Europe	Tier 2: 5 to 11	Tier 5: 30 to 59	57	7725
2015	11	Marriott.com	TRANSIENT/RENTALS	Basic	Other	Select	Europe	Tier 1: 1 to 4	Tier 5: 30 to 59	3442	446536
2015	11	Marriott.com	TRANSIENT/RENTALS	Platinum	Other	Premium	Africa	Tier 3: 12 to 29	Tier 6: 60+	65	11804

Will see how many month in each year so the comparison will be relevant:

print(sort(unique(CY$MONTH)))

## [1] 1 2 3 4 5 6 7

print(sort(unique(LY$MONTH)))

##  [1]  1  2  3  4  5  6  7  8  9 10 11 12

2016 as CY shows only data for 7 months (Jan - Jul) while in 2015 it shows the full year, therefore will filter out 2015 months from (Aug - Dec) to have a relevant comparison.

LY <- LY %>%
  filter(MONTH < 8 )


print(sort(unique(LY$MONTH)))

## [1] 1 2 3 4 5 6 7

Will merge the both years in one data frame (cleaned_data)

cleaned_data <- rbind(CY,LY)

cleaned_data <- cleaned_data %>%
  mutate(ADR = round(ROOM_REVENUE / ROOM_NIGHTS))

# extract the cleaned data frame for further Tablue visualization:

write.csv(cleaned_data, 'Cleaned_data.csv')

F. Overall YOY Room nights and Revenue:

convert_number <- function(x) {
  if (x >= 1e9) {
    return(paste0(round(x / 1e9, 1), "B"))
  } else if (x >= 1e6) {
    return(paste0(round(x / 1e6, 1), "M"))
  } else if (x >= 1e3) {
    return(paste0(round(x / 1e3, 1), "K"))
  } else {
    return(as.character(x))
  }
}


overall_performance <- cleaned_data %>%
  group_by(YEAR)%>%
  summarize(RNS = sum(ROOM_NIGHTS),
            Revenue = sum(ROOM_REVENUE),
            ADR = round(Revenue / RNS))

overall_performance$RNS = sapply(overall_performance$RNS, convert_number)
overall_performance$Revenue = sapply(overall_performance$Revenue, convert_number)

kable(overall_performance, format = "pipe")

YEAR	RNS	Revenue	ADR
2015	20.6M	4.7B	229
2016	22.8M	5.3B	231

Visualize Results:

library(gridExtra)



Rev_Vis <- ggplot(overall_performance, aes(x = YEAR, y = Revenue, fill = YEAR))+
  geom_col(alpha = 0.5)+
  theme_classic()+
  labs(title = "Revenue Performance YOY (2015 - 2016)", subtitle = "YTD From Jan - Jul")+ geom_text(aes(label = Revenue), vjust = -0.5)

RNS_Vis <- ggplot(overall_performance, aes(x = YEAR, y = RNS, fill = YEAR))+
  geom_col(alpha = 0.5)+
  theme_classic()+
  labs(title = "RNS Performance YOY (2015 - 2016)", subtitle = "YTD From Jan - Jul")+ geom_text(aes(label = RNS), vjust = -0.5)

ADR_Vis <- ggplot(overall_performance, aes(x = YEAR, y = ADR, fill = YEAR))+
  geom_col(alpha = 0.5)+
  theme_classic()+
  labs(title = "ADR Performance YOY (2015 - 2016)", subtitle = "YTD From Jan - Jul")+ geom_text(aes(label = ADR), vjust = -0.5)



RNS_Vis

Rev_Vis

ADR_Vis

Comment:

The revenue growth in 2016 was primarily driven by a volume strategy, with a significant increase in room nights. The slight rise in ADR contributed, but the primary driver was the higher occupancy. Overall, the focus was on boosting volume rather than significantly increasing rates.

Q1 : What regions, markets or brands are driving our YTD Revenue vs last year?

Regions:

# Grouping by YEAR and REGION_NAME to summarize RNS, Revenue, and ADR
regions <- cleaned_data %>%
  group_by(YEAR, REGION_NAME) %>%
  summarize(RNS = sum(ROOM_NIGHTS),
            Revenue = sum(ROOM_REVENUE),
            ADR = round(Revenue / RNS))

# Calculating YoY percentage changes for RNS, Revenue, and ADR
regions_YOY <- regions %>%
  group_by(REGION_NAME) %>%
  arrange(YEAR) %>%
  mutate(
    RNS_YOY = paste0(round((RNS / lag(RNS) - 1) * 100, 1), '%'),
    Rev_YOY = paste0(round((Revenue / lag(Revenue) - 1) * 100, 1), '%'),
    ADR_YOY = paste0(round((ADR / lag(ADR) - 1) * 100, 1), '%')
  ) %>%
  filter(YEAR == 2016) %>%
  select(Year = YEAR, Region = REGION_NAME, RNS_YOY, Rev_YOY, ADR_YOY)

# Convert numbers to a more readable format
regions$RNS <- sapply(regions$RNS, convert_number)
regions$Revenue <- sapply(regions$Revenue, convert_number)

# Displaying the tables using kable
kable(regions, format = 'pipe', caption = "Regional Performance Summary") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

Regional Performance Summary
YEAR	REGION_NAME	RNS	Revenue	ADR
2015	Africa	2.4M	359.8M	152
2015	Europe	14.1M	3.5B	249
2015	Middle East	4.2M	858.4M	207
2016	Africa	2.4M	383.5M	160
2016	Europe	15.9M	4B	249
2016	Middle East	4.5M	920.8M	206

kable(regions_YOY, format = 'pipe', caption = "Year-over-Year Performance Summary") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

Year-over-Year Performance Summary
Year	Region	RNS_YOY	Rev_YOY	ADR_YOY
2016	Africa	1.1%	6.6%	5.3%
2016	Europe	12.7%	12.7%	0%
2016	Middle East	7.4%	7.3%	-0.5%

Comment:

Europe shows the highest growth, with room nights increasing from 14,101,505 in 2015 to 15,897,185 in 2016, driving a significant revenue increase from 3.51 billion to 3.96 billion. The Middle East also saw notable growth in both room nights and revenue, though at a slightly lower rate than Europe. Africa, while showing the smallest absolute numbers, still experienced a modest increase in both metrics, reflecting steady but slower growth compared to the other regions. Overall, Europe contributed the most to the total revenue increase for Marriott in 2016.

Markets:

A. Top 10 Revenue generating Key Markets.

B. Top 10 Key markets in terms of YOY revenue growth.

# Grouping by YEAR and Market to summarize RNS, Revenue, and ADR

Markets <- cleaned_data %>%
  group_by(YEAR,KEY_MARKETS ) %>%
  summarize(RNS = sum(ROOM_NIGHTS),
            Revenue = sum(ROOM_REVENUE),
            ADR = round(Revenue / RNS))%>%
  arrange(desc(Revenue))



Markets_YOY <- Markets %>%
  group_by(KEY_MARKETS) %>%
  arrange(YEAR) %>%
  mutate(
    RNS_YOY_num = (RNS / lag(RNS) - 1) * 100,
    Rev_YOY_num = (Revenue / lag(Revenue) - 1) * 100,
    ADR_YOY_num = (ADR / lag(ADR) - 1) * 100
  ) %>%
  filter(YEAR == 2016) %>%
  arrange(desc(Rev_YOY_num)) %>%  
  mutate(
    RNS_YOY = paste0(round(RNS_YOY_num, 1), '%'),
    Rev_YOY = paste0(round(Rev_YOY_num, 1), '%'),
    ADR_YOY = paste0(round(ADR_YOY_num, 1), '%')
  ) %>%
  select(Year=YEAR, Market = KEY_MARKETS, RNS_YOY, Rev_YOY, ADR_YOY)%>%
  head(10)
  

# Convert numbers to a more readable format
Markets$RNS <- sapply(Markets$RNS, convert_number)
Markets$Revenue <- sapply(Markets$Revenue, convert_number)

# Displaying the tables using kable


kable(Markets_YOY, format = 'pipe', caption = "Top 10 YOY Revenue Growth") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

Top 10 YOY Revenue Growth
Year	Market	RNS_YOY	Rev_YOY	ADR_YOY
2016	Munich	32.3%	45.5%	10.2%
2016	Rome	26.4%	28.9%	2.2%
2016	Frankfurt	17.8%	24.8%	5.9%
2016	Doha	15.7%	22%	5.5%
2016	Johannesburg	0.9%	21.7%	21.7%
2016	Madrid	3.6%	19.8%	15.6%
2016	Abu Dhabi	11.2%	18.2%	6.7%
2016	Amsterdam	26.1%	16.3%	-7.6%
2016	Istanbul	25.2%	14.8%	-8.3%
2016	Riyadh	6.6%	14.5%	7.4%

kable(head(Markets,10), format = 'pipe', caption = "TOP 10 Revenue Generating Markets") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

TOP 10 Revenue Generating Markets
YEAR	KEY_MARKETS	RNS	Revenue	ADR
2016	Other	12.1M	2.6B	218
2015	Other	11.1M	2.4B	213
2016	London	1.2M	517M	437
2015	London	1.2M	512M	439
2016	Dubai	1.8M	350.4M	194
2015	Dubai	1.7M	330.3M	197
2016	Paris	752.2K	307.1M	408
2015	Paris	730.2K	299M	409
2016	Barcelona	468K	145.4M	311
2016	Rome	415.7K	137.9M	332

Comment:

Munich market is on the top of the best YOY revenue performers, followed by Rome and Frankfurt. in The Middle East Doha market had an increase by 22% in terms of Revenue followed by Abu Dhabi then Riyadh.

Brands:

# Grouping by YEAR and Market to summarize RNS, Revenue, and ADR

Brands <- cleaned_data %>%
  group_by(YEAR,BRAND_TYPE ) %>%
  summarize(RNS = sum(ROOM_NIGHTS),
            Revenue = sum(ROOM_REVENUE),
            ADR = round(Revenue / RNS))%>%
  arrange(desc(Revenue))



Brands_YOY <- Brands %>%
  group_by(BRAND_TYPE) %>%
  arrange(YEAR) %>%
  mutate(
    RNS_YOY_num = (RNS / lag(RNS) - 1) * 100,
    Rev_YOY_num = (Revenue / lag(Revenue) - 1) * 100,
    ADR_YOY_num = (ADR / lag(ADR) - 1) * 100
  ) %>%
  filter(YEAR == 2016) %>%
  arrange(desc(Rev_YOY_num)) %>% 
  mutate(
    RNS_YOY = paste0(round(RNS_YOY_num, 1), '%'),
    Rev_YOY = paste0(round(Rev_YOY_num, 1), '%'),
    ADR_YOY = paste0(round(ADR_YOY_num, 1), '%')
  ) %>%
  select(Year=YEAR, Brand = BRAND_TYPE, RNS_YOY, Rev_YOY, ADR_YOY)%>%
  head(10)
  

# Convert numbers to a more readable format
#Brands$RNS <- sapply(Brands$RNS, convert_number)
#Brands$Revenue <- sapply(Brands$Revenue, convert_number)

# Displaying the tables using kable


kable(Brands_YOY, format = 'pipe', caption = "Top 10 YOY Revenue Growth") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

Top 10 YOY Revenue Growth
Year	Brand	RNS_YOY	Rev_YOY	ADR_YOY
2016	Select	10.7%	18.2%	6.8%
2016	Premium	10.5%	11.5%	0.9%
2016	Luxury	8.6%	6.3%	-2%
2016	Midscale	NA%	NA%	NA%

kable(head(Brands,10), format = 'pipe', caption = "TOP 10 Revenue Generating Brand Type") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

TOP 10 Revenue Generating Brand Type
YEAR	BRAND_TYPE	RNS	Revenue	ADR
2016	Premium	12176467	2787702734	229
2015	Premium	11020568	2500275878	227
2016	Luxury	3281681	1440634924	439
2015	Luxury	3022346	1354955528	448
2016	Select	7286434	1030757540	141
2015	Select	6584400	872216625	132
2016	Midscale	10158	951423	94

# Plotting 

ggplot(Brands, aes(x=BRAND_TYPE, y=Revenue/1e9, fill=YEAR)) +
  geom_bar(stat="identity", position="dodge") +
  geom_text(aes(label=sprintf("%.1f", Revenue/1e9)), 
            position=position_dodge(width=0.9), vjust=-0.5) +
  scale_y_continuous(name="Revenue (in billions)", labels=scales::dollar) +
  labs(title="Revenue by Brand Type and Year",
       x="Brand Type",
       fill="Year") +
  theme_minimal() +
  theme(legend.position="bottom")

Comment:

The Select segment experienced the highest growth, with revenue increasing from 872 million in 2015 to over 1.03 billion in 2016, and room nights rising from 6.58 million to 7.29 million. The Premium segment followed closely, with revenue climbing from 2.5 billion to 2.79 billion and room nights from 11.02 million to 12.18 million, making it a key contributor to Marriott’s overall performance. The Luxury segment also grew, with revenue increasing from 1.35 billion to 1.44 billion, and room nights from 3.02 million to 3.28 million, reflecting steady demand. The newly introduced Midscale segment in 2016 added 951,423 in revenue and 10,158 room nights, marking its entry into Marriott’s brand portfolio. Overall, Select and Premium brands drove the most significant growth, with Luxury maintaining solid gains and Midscale entering the market as a new growth opportunity.

Digital

Q2 : What are the key drivers for our YTD Marriott.com performance?

Will group the data by Channel and will focus on analysing Marriott.com

digital <- cleaned_data %>%
  group_by(YEAR, CHANNEL, KEY_MARKETS) %>%
  summarize(
    RNS = sum(ROOM_NIGHTS),
    Rev = sum(ROOM_REVENUE),
    ADR = round(Rev / RNS)
  ) %>%
  filter(CHANNEL == 'Marriott.com')

# Calculate YOY Growth 
digital_YOY <- digital %>%
  pivot_wider(names_from = YEAR, values_from = c(RNS, Rev, ADR)) %>%
  mutate(
    RNS_YOY = round((RNS_2016 - RNS_2015) / RNS_2015 * 100, 1),
    Rev_YOY = round((Rev_2016 - Rev_2015) / Rev_2015 * 100, 1),
    ADR_YOY = round((ADR_2016 - ADR_2015) / ADR_2015 * 100, 1)
  ) %>%
  select(CHANNEL, KEY_MARKETS, RNS_YOY, Rev_YOY, ADR_YOY) %>%
  arrange(desc(Rev_YOY))


kable(head(digital_YOY,10), format = 'pipe', caption = "TOP 10 Marriott.com YOY growth performers") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

TOP 10 Marriott.com YOY growth performers
CHANNEL	KEY_MARKETS	RNS_YOY	Rev_YOY	ADR_YOY
Marriott.com	Munich	30.6	47.4	13.1
Marriott.com	Riyadh	35.8	45.5	6.8
Marriott.com	Doha	21.9	24.8	2.3
Marriott.com	Istanbul	23.1	24.7	1.1
Marriott.com	Jeddah	14.3	22.4	6.7
Marriott.com	Madrid	11.4	21.9	9.7
Marriott.com	Frankfurt	12.3	20.0	7.1
Marriott.com	Johannesburg	0.7	18.1	17.0
Marriott.com	Abu Dhabi	10.9	17.8	6.5
Marriott.com	Other	13.6	17.2	3.1

Comment:

The top 10 Marriott.com markets show significant Year-Over-Year (YOY) growth, with Munich and Riyadh leading the performance in both RNS and Revenue growth. Munich achieved a remarkable 47.4% increase in Revenue, while Riyadh closely followed with a 45.5% increase. These markets also saw notable increases in ADR, particularly in Munich and Johannesburg, indicating strong pricing power. Overall, these results reflect robust growth across key international markets for Marriott.com.

Q3 : What is the absolute mix of Marriott.com?

Marriott.com rooms sold / Total Rooms sold

marriott_com <- cleaned_data %>%
  group_by(YEAR, CHANNEL)%>%
  summarize(
    RNS = sum(ROOM_NIGHTS),
    Rev = sum(ROOM_REVENUE),
    ADR = round(Rev / RNS))%>%
  filter(CHANNEL == "Marriott.com",
         YEAR == 2016)

Marriott_com_RNS = marriott_com$RNS
total_hotel_RNS = sum(CY$ROOM_NIGHTS)

Marriott_com_mix = (Marriott_com_RNS / total_hotel_RNS)*100

round(Marriott_com_mix,1)

## [1] 32.5

Loyalty:

Q4 : What are the key drivers for our YTD Member Mix performance?

members <- cleaned_data %>%
  group_by(year = YEAR,member = BONVOYMEMBER, market = KEY_MARKETS)%>%
  filter(!member == "Non-member")%>%
  summarize (RNS = sum(ROOM_NIGHTS),
    Rev = sum(ROOM_REVENUE),
    ADR = round(Rev / RNS))


members_driver <- members %>%
  select(-member)%>%
  group_by(year, market)%>%
  summarize (RNS = sum(RNS),
    Rev = sum(Rev))%>%
  pivot_wider(names_from = year, values_from = c(RNS,Rev))%>%
  mutate(RNS_YOY = round((RNS_2016-RNS_2015)/RNS_2015*100,1),
         Rev_YOY = round((Rev_2016-Rev_2015)/Rev_2015*100,1)) %>%
  select(market,RNS_YOY,Rev_YOY)%>%
  arrange(desc(Rev_YOY))%>%
  head(10)



kable(members_driver, format = 'pipe', caption = "TOP 10 YOY performers in membership Revenue") %>%
  kable_styling(bootstrap_options = "striped", full_width = F)

TOP 10 YOY performers in membership Revenue
market	RNS_YOY	Rev_YOY
Munich	35.5	48.5
Johannesburg	12.7	28.9
Madrid	13.7	27.8
Jeddah	17.0	25.3
Riyadh	18.7	23.9
Frankfurt	16.2	23.0
Rome	20.5	23.0
Milan	12.6	20.3
Other	15.6	17.3
Cape Town	1.9	17.2

Comment:

The top 10 YOY performers in membership revenue highlight that Munich leads with a significant Rev_YOY of 48.5%, accompanied by a strong RNS_YOY of 35.5%. Johannesburg and Madrid follow, demonstrating notable revenue growth despite relatively lower RNS_YOY figures. Interestingly, markets like Cape Town, with a low RNS_YOY of 1.9%, still achieve a respectable Rev_YOY of 17.2%, indicating strong revenue generation potential even with modest room night growth.

Q5 : Calculate Loyalty Room Mix.

Member rooms sold / Total Rooms sold

mamber_mix <- members %>%
  filter(year == 2016)

member_rns_CY = sum(mamber_mix$RNS)
  
member_mix_results = paste0(round(member_rns_CY / total_hotel_RNS,2)*100,'%')

member_mix_results

## [1] "52%"

Sales:

Q6 : What are the key drivers for our YTD Group performance?

Overall Groups Performance YOY

overall_group <- cleaned_data %>%
  group_by(YEAR,SEGMENT)%>%
  summarize (RNS = sum(ROOM_NIGHTS),
    Rev = sum(ROOM_REVENUE),
    ADR = round(Rev / RNS))%>%
  filter(SEGMENT == "GROUP")%>%
  pivot_wider(names_from = "YEAR", values_from = c(RNS,Rev,ADR))%>%
  mutate(RNS_YOY = round((RNS_2016-RNS_2015)/RNS_2015 *100 ,1),
         Rev_YOY = round((Rev_2016-Rev_2015)/Rev_2015 *100 ,1),
         ADR_YOY = round((ADR_2016-ADR_2015)/ADR_2015 *100 ,1))%>%
  select(Segment = SEGMENT, RNS_YOY,Rev_YOY,ADR_YOY)





kable(overall_group, format = 'pipe')

Segment	RNS_YOY	Rev_YOY	ADR_YOY
GROUP	12.1	17.7	4.8

Groups Performance by Month:

group <- cleaned_data %>%
  group_by(YEAR,MONTH,SEGMENT)%>%
  summarize (RNS = sum(ROOM_NIGHTS),
    Rev = sum(ROOM_REVENUE),
    ADR = round(Rev / RNS))%>%
  filter(SEGMENT == "GROUP")
  
kable(group, format = 'pipe')

YEAR	MONTH	SEGMENT	RNS	Rev	ADR
2015	1	GROUP	390192	77055386	197
2015	2	GROUP	393726	78080726	198
2015	3	GROUP	450675	88693202	197
2015	4	GROUP	419619	88504979	211
2015	5	GROUP	532724	118412381	222
2015	6	GROUP	483108	113449717	235
2015	7	GROUP	390038	75325316	193
2016	1	GROUP	463929	92398232	199
2016	2	GROUP	461601	92947069	201
2016	3	GROUP	456236	92556569	203
2016	4	GROUP	536922	118891218	221
2016	5	GROUP	574740	138115759	240
2016	6	GROUP	480584	111400634	232
2016	7	GROUP	457056	106103988	232

# Melt the data to long format for easier plotting

data_long <- gather(group, key = "Metric", value = "Value", RNS, Rev, ADR)

# Plot the data

ggplot(data_long, aes(x = MONTH, y = Value, color = YEAR, group = interaction(YEAR, Metric))) +
  geom_line(size = 1) +
  geom_point(size = 2) +
  facet_wrap(~Metric, scales = "free_y", ncol = 1) +
  labs(title = "Monthly Group Segment Performance (2015 vs 2016)",
       x = "Month",
       y = "Value",
       color = "Year") +
  theme_minimal() +
  theme(legend.position = "bottom")

Groups Performance by Channel:

group_channel <- cleaned_data %>%
  group_by(YEAR,SEGMENT,CHANNEL)%>%
  summarize (RNS = sum(ROOM_NIGHTS),
    Rev = sum(ROOM_REVENUE),
    ADR = round(Rev / RNS))%>%
  filter(SEGMENT == "GROUP")%>%
  pivot_wider(names_from = "YEAR", values_from = c(RNS,Rev,ADR))%>%
  mutate(RNS_YOY = round((RNS_2016-RNS_2015)/RNS_2015 *100 ,1),
         Rev_YOY = round((Rev_2016-Rev_2015)/Rev_2015 *100 ,1),
         ADR_YOY = round((ADR_2016-ADR_2015)/ADR_2015 *100 ,1))%>%
  select(Segment = SEGMENT,Channel = CHANNEL, RNS_YOY,Rev_YOY,ADR_YOY)%>%
  arrange(desc(Rev_YOY))
  
kable(group_channel,format = 'pipe')

Segment	Channel	RNS_YOY	Rev_YOY	ADR_YOY
GROUP	Other	2021.4	1135.3	-41.8
GROUP	eChannel	4.3	30.2	24.6
GROUP	Property	12.2	17.8	5.3
GROUP	Marriott.com	11.0	15.7	4.2
GROUP	GDS	-79.5	-84.1	-22.3

Comment:

The “Other” channel shows significant positive growth in both RNS and Rev, but a notable decline in ADR, indicating more rooms booked at lower rates. The “eChannel,” “Property,” and “Marriott.com” channels also exhibit positive growth across all metrics, reflecting healthy performance. In contrast, the “GDS” channel shows substantial declines across all three metrics, suggesting a sharp downturn in performance through this channel.

Q7 : What months, regions, markets are driving our YTD Revenue vs last year?

Months:

group_month <- group %>%
  pivot_wider(names_from = "YEAR", values_from = c(RNS,Rev,ADR))%>%
  mutate(RNS_YOY = round((RNS_2016-RNS_2015)/RNS_2015 *100 ,1),
         Rev_YOY = round((Rev_2016-Rev_2015)/Rev_2015 *100 ,1),
         ADR_YOY = round((ADR_2016-ADR_2015)/ADR_2015 *100 ,1))%>%
  select(Segment = SEGMENT,month = MONTH, RNS_YOY,Rev_YOY,ADR_YOY)%>%
  arrange(desc(Rev_YOY))

kable(group_month,format= 'pipe', caption = "Groups Performance by mothly YOY Growth")

Groups Performance by mothly YOY Growth
Segment	month	RNS_YOY	Rev_YOY	ADR_YOY
GROUP	7	17.2	40.9	20.2
GROUP	4	28.0	34.3	4.7
GROUP	1	18.9	19.9	1.0
GROUP	2	17.2	19.0	1.5
GROUP	5	7.9	16.6	8.1
GROUP	3	1.2	4.4	3.0
GROUP	6	-0.5	-1.8	-1.3

ggplot(group_month, aes(x = month, y = Rev_YOY, fill = month))+
  geom_area()+
  theme_bw()+
  theme(legend.position = 'none')

Regions:

regions_channel <- cleaned_data %>%
  group_by(YEAR, SEGMENT,REGION_NAME) %>%
  summarize(RNS = sum(ROOM_NIGHTS),
            Revenue = sum(ROOM_REVENUE),
            ADR = round(Revenue / RNS))%>%
  filter(SEGMENT == "GROUP")%>%
  pivot_wider(names_from = "YEAR", values_from = c(RNS,Revenue,ADR))%>%
  mutate(RNS_YOY = round((RNS_2016 - RNS_2015)/RNS_2015 *100,1),
         Rev_YOY = round((Revenue_2016 - Revenue_2015)/Revenue_2015 *100,1),
         ADR_YOY = round((ADR_2016 - ADR_2015)/ADR_2015 *100,1))%>%
  select(Segment = SEGMENT, Region = REGION_NAME, RNS_YOY,Rev_YOY,ADR_YOY)%>%
  arrange(desc(Rev_YOY))


kable(regions_channel,format = 'pipe', caption = "Groups YOY performance by Region")

Groups YOY performance by Region
Segment	Region	RNS_YOY	Rev_YOY	ADR_YOY
GROUP	Europe	16.4	22.5	5.1
GROUP	Africa	-3.0	6.5	9.8
GROUP	Middle East	5.5	3.8	-1.7

ggplot(regions_channel)+
  geom_col(aes(x = Region, y = Rev_YOY, fill = Region))+
  theme_bw()+
  theme(legend.position = 'none')

Market:

Groups_Markets <- cleaned_data %>%
group_by(YEAR,KEY_MARKETS,SEGMENT ) %>%
  summarize(RNS = sum(ROOM_NIGHTS),
            Revenue = sum(ROOM_REVENUE),
            ADR = round(Revenue / RNS))%>%
  filter(SEGMENT == "GROUP")%>%
  pivot_wider(names_from = "YEAR", values_from = c(RNS,Revenue,ADR))%>%
  mutate(RNS_YOY = round((RNS_2016 - RNS_2015)/RNS_2015 *100,1),
         Rev_YOY = round((Revenue_2016 - Revenue_2015)/Revenue_2015 *100,1),
         ADR_YOY = round((ADR_2016 - ADR_2015)/ADR_2015 *100,1))%>%
  select(Segment = SEGMENT, Market = KEY_MARKETS, RNS_YOY,Rev_YOY,ADR_YOY)%>%
  arrange(desc(Rev_YOY))%>%
  head(10)


kable(Groups_Markets, format = 'pipe', caption = "TOP 10 Markets in YOY Group Revenue Growth")

TOP 10 Markets in YOY Group Revenue Growth
Segment	Market	RNS_YOY	Rev_YOY	ADR_YOY
GROUP	Paris	48.9	127.0	52.8
GROUP	Amsterdam	150.3	81.4	-27.3
GROUP	Johannesburg	20.9	43.2	19.0
GROUP	Rome	33.6	38.2	3.3
GROUP	Doha	17.3	31.2	11.3
GROUP	Munich	23.6	21.3	-2.1
GROUP	Riyadh	-11.7	19.5	35.4
GROUP	Other	8.2	14.3	5.6
GROUP	Madrid	-1.7	14.0	16.0
GROUP	Barcelona	-0.7	11.3	12.1

ggplot(Groups_Markets)+
  geom_col(aes(x = Market, y = Rev_YOY, fill = Market))+
  theme_bw()+
  theme(legend.position = 'none')

Comment:

Paris and Amsterdam show significant growth in RNS, with Paris also experiencing a substantial increase in both Rev and ADR. Conversely, Amsterdam, despite a strong RNS increase, sees a decline in ADR, indicating a drop in rates. Markets like Riyadh and Madrid present contrasting trends, with Riyadh showing a drop in RNS but an increase in both Rev and ADR, while Madrid experiences a slight decline in RNS but growth in Rev and ADR. Overall, most markets demonstrate positive trends, except for a few with minor declines in specific metrics.

Marriott Assessment Analysis

Mostafa Saltah

2024-08-21

Data Overview:

A. Loading dataset:

B. Exploring data dimensions :

C. Exploring data structure:

D. Update the data types:

E. Elementary Data Analysis

F. Overall YOY Room nights and Revenue:

Q1 : What regions, markets or brands are driving our YTD Revenue vs last year?

Digital

Q2 : What are the key drivers for our YTD Marriott.com performance?

Q3 : What is the absolute mix of Marriott.com?

Loyalty:

Q4 : What are the key drivers for our YTD Member Mix performance?

Q5 : Calculate Loyalty Room Mix.

Sales:

Q6 : What are the key drivers for our YTD Group performance?

Q7 : What months, regions, markets are driving our YTD Revenue vs last year?