I am trying to predict expected Cost and Revenue for hotel_name and Channel from user inputs: ROAS (Revenue / Cost), hotel_name, and month. I’ve attempted using Multioutput Regression and the pymc-marketing library but haven’t found a satisfactory solution. The predictions are not close to real data and major variabilities. Could someone suggest a method or a library that might be better suited for this problem?
Sample data
import pandas as pd
data = {
'hotel_name': [
'Jumeirah Burj Al Arab', 'Jumeirah Beach Hotel', 'Atlantis The Palm',
'Burj Khalifa Hotel', 'Armani Hotel Dubai', 'Jumeirah Burj Al Arab',
'Jumeirah Beach Hotel', 'Atlantis The Palm', 'Burj Khalifa Hotel',
'Armani Hotel Dubai', 'Jumeirah Burj Al Arab', 'Jumeirah Beach Hotel',
'Atlantis The Palm', 'Burj Khalifa Hotel', 'Armani Hotel Dubai'
],
'Channel': [
'Bing_Search', 'Bing_Search', 'Bing_Search', 'Bing_Search', 'Bing_Search',
'Google_Search', 'Google_Search', 'Google_Search', 'Google_Search', 'Google_Search',
'Google_Search', 'Metasearch', 'Metasearch', 'Metasearch', 'Metasearch'
],
'market': [
'Australia', 'UAE', 'UK', 'US', 'World Wide', 'Australia', 'Canada',
'UAE', 'UK', 'US', 'World Wide', 'India', 'UAE', 'UK', 'US'
],
'year': [
2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2023, 2024, 2023, 2023, 2023
],
'month': [
2, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 2, 2, 2
],
'Cost': [
38.1, 27.0, 26.2, 426.2, 119.8, 1177.8, 291.3, 16727.9, 10178.4, 4592.7,
44880.7, 162.2, 281.8, 45.0, 321.4
],
'Revenue': [
20946.6, 30081.5, 21308.8, 174064.0, 22784.2, 105614.4, 13672.4, 509304.4,
692854.5, 353565.6, 1164871.3, 107757.7, 27406.1, 31325.9, 80625.0
],
'ROAS': [
549.78, 1114.13, 813.31, 408.41, 190.19, 89.67, 46.94, 30.45, 68.07, 76.98,
25.95, 664.35, 97.25, 696.13, 250.86
]
}
Multi Output Model script I have
import pandas as pd
import numpy as np
from warnings import filterwarnings
from sklearn.pipeline import Pipeline
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder
from sklearn.multioutput import MultiOutputRegressor
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split
filterwarnings('ignore')
# Define function to exclude outliers
def exclude_outliers_using_iqr(df, group_columns, columns, multiplier=1.5):
def exclude_outliers(group):
for column in columns:
Q1 = group[column].quantile(0.25)
Q3 = group[column].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - multiplier * IQR
upper_bound = Q3 + multiplier * IQR
group = group[(group[column] >= lower_bound) & (group[column] <= upper_bound)]
return group
df = df.groupby(group_columns).apply(exclude_outliers).reset_index(drop=True)
return df
# Define function to make new prediction
def make_new_prediction(new_data, pipeline):
new_df = pd.DataFrame([new_data])
new_X = new_df[['month', 'channel_grup', 'market', 'ROAS']]
new_prediction = pipeline.predict(new_X)
return new_prediction
# Define function to predict ROAS for all
def predict_roas_for_all(df, model, roas, month):
days_in_month = {
1: 31, 2: 28, 3: 31, 4: 30, 5: 31, 6: 30,
7: 31, 8: 31, 9: 30, 10: 31, 11: 30, 12: 31
}
num_days = days_in_month[month]
unique_combinations = df[['channel_grup', 'market']].drop_duplicates()
predictions = []
for _, row in unique_combinations.iterrows():
channel_group = row['channel_grup']
market = row['market']
input_data = {
'channel_grup': channel_group,
'market': market,
'ROAS': roas,
'month': month
}
input_df = pd.DataFrame([input_data])
prediction = model.predict(input_df)
cost = prediction[0][0] * num_days
revenue = prediction[0][1] * num_days
prediction_result = {
'channel_grup': channel_group,
'market': market,
'ROAS': roas,
'month': month,
'cost': cost,
'revenue': revenue
}
predictions.append(prediction_result)
predictions_df = pd.DataFrame(predictions)
return predictions_df
# Load data
df = pd.read_csv(r'data.csv')
df['date'] = pd.to_datetime(df['date'])
# Define unique hotels list
hotels_list = df['hotel_name'].unique()
# Initialize final results list
final_results = []
for hotel in hotels_list:
print(hotel)
df_hotel = df.loc[
(df['Revenue'] > 1) &
(df['hotel_name'] == hotel)
].reset_index(drop=True)
if df_hotel.shape[0] == 0:
continue
df_hotel.loc[df_hotel['channel_group'] == 'Search', 'channel_grup'] = df_hotel['channel'] + '_' + df_hotel['channel_group']
df_hotel.loc[df_hotel['channel_grup'].isna(), 'channel_grup'] = df_hotel['channel_group']
group = df_hotel.groupby(by=['date', 'channel_grup', 'hotel_name', 'market'])[['Cost', 'Revenue']].sum().reset_index()
group['ROAS'] = (group['Revenue'] / group['Cost']).round(2)
market_counts = group.groupby(by=['market'])['date'].count().reset_index().sort_values(by=['date'])
top_market_percent = market_counts.tail(int(np.ceil(0.75 * len(market_counts))))
top_market_percent = top_market_percent.drop(columns=['date'])
group = pd.merge(group, top_market_percent, on=['market'], how='right')
group = exclude_outliers_using_iqr(group, ['market', 'channel_grup'], ['ROAS', 'Cost', 'Revenue'])
group['month'] = group['date'].dt.month
X = group[['month', 'channel_grup', 'market', 'ROAS']]
y = group[['Cost', 'Revenue']]
categorical_features = ['channel_grup', 'market']
categorical_transformer = OneHotEncoder(handle_unknown='ignore')
preprocessor = ColumnTransformer(
transformers=[
('cat', categorical_transformer, categorical_features)
],
remainder='passthrough'
)
pipeline = Pipeline(verbose=True, steps=[
('preprocessor', preprocessor),
('regressor', MultiOutputRegressor(RandomForestRegressor()))
])
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
pipeline.fit(X_train, y_train)
for increment in range(1, 70, 1):
for month in range(1, 13):
increment_predictions = predict_roas_for_all(group, pipeline, increment, month)
increment_predictions['hotel_name'] = hotel
increment_predictions['increment'] = increment
increment_predictions['month'] = month
final_results.append(increment_predictions)
# Combine all results into a single DataFrame
final_results_df = pd.concat(final_results, ignore_index=True)
Thank you!