All the work in this section was performed in a Python (Jupyter Notebook) environment.
Upon examining the obtained data, it was observed that the payment_date data in the users_payment.csv file was not defined as a date but instead as a string.
Furthermore, while metrics such as MRR, Paid Users, ARPPU, New Paid Users, and New MRR can be calculated with the available information, it was determined that calculating metrics like Churn Users, Churn Rate, Expansion MRR, and Contraction MRR requires preprocessing.
In this context, the operations performed are explained step by step and Jupyter Notebook file used for this section can be found
here
.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
games_paid_users = pd.read_csv('games_paid_users.csv')
games_payments = pd.read_csv('games_payments.csv')
games_payments['payment_date'] = pd.to_datetime(games_payments['payment_date'])
The payment_date data provided in the database was given as a string in the YYYY-MM-DD format and was converted to a datetime format in the previous step. Since the calculations in this study will be performed on a monthly basis, all date data has been transformed to the first day of the month, and the resulting new data has been saved in a column named.
games_payments['payment_month'] = games_payments['payment_date'].values.astype('datetime64[M]')
The payments made by each user for each game within the considered month were summed and initially assigned to a column named monthly_revenue. Subsequently, this data was assigned to the revenue_amount_usd column, and the column was renamed to total_revenue. This ensured that the structure of the dataset remained intact.
monthly_revenue = games_payments.groupby(['user_id', 'game_name', 'payment_month'], as_index=False)[
'revenue_amount_usd'].sum()
monthly_revenue.rename(columns={'revenue_amount_usd': 'total_revenue'}, inplace=True)
Upon examining the dataset, it was determined that users made payments in some months, made a single payment in some months, and made multiple payments in others. To standardize users with multiple payments, the payment_month data was created in a previous step. To calculate metrics such as missing payment data (e.g., churn, etc.), a calendar was created spanning from the first payment date to the last payment date in the dataset.
min_month = monthly_revenue['payment_month'].min()
max_month = monthly_revenue['payment_month'].max()
all_months = pd.date_range(min_month, max_month, freq='MS')
unique_users_games = monthly_revenue[['user_id', 'game_name']].drop_duplicates()
calendar = unique_users_games.assign(key=1).merge(pd.DataFrame({'payment_month': all_months, 'key': 1}), on='key').drop(
'key', axis=1)
The obtained calendar dataset was populated with the user_id, game_name and payment_month data from the existing dataset, and cells in payment_month with no data were assigned a value of 0.
full_data = calendar.merge(monthly_revenue, on=['user_id', 'game_name', 'payment_month'], how='left')
full_data['total_revenue'] = full_data['total_revenue'].fillna(0)
The dataset at hand has been sorted based on the user_id, game_name and payment_month columns, respectively.
full_data = full_data.sort_values(by=['user_id', 'game_name', 'payment_month'])
To calculate metrics such as Churn, Expansion MRR, and Contraction MRR, information about whether a payment was made in the previous month is required. In this context, a column named total_revenue_previous was created, and the payment information from the previous month was transferred to this column.
ir
full_data['total_revenue_previous'] = full_data.groupby(['user_id', 'game_name'])['total_revenue'].shift(1).fillna(0)
To facilitate the calculations, a status column was created to determine the current status of users. In this column, users are defined as follows:
Users making a payment for the first time: new
Users who made a payment this month and also made a payment in the previous month: active
Users who made a payment this month but did not make a payment in the previous month: back
Users who did not make a payment this month but made a payment in the previous month: churn
Users who did not make a payment this month and also did not make a payment in the previous month: deactive
def determine_status(row):
if row['total_revenue'] > 0:
if row['total_revenue_previous'] == 0:
# Check first payment
user_game_payments = full_data[
(full_data['user_id'] == row['user_id']) &
(full_data['game_name'] == row['game_name']) &
(full_data['payment_month'] < row['payment_month'])
]
if user_game_payments['total_revenue'].sum() == 0:
return 'new'
return 'back'
return 'active'
else:
if row['total_revenue_previous'] > 0:
return 'churn'
return 'deactive'
full_data['status'] = full_data.apply(determine_status, axis=1)
Finally, the user_id and game_name columns in the full_data and games_paid_users datasets were converted to string format. This ensured that potential errors due to differences in data types were avoided during the merging of these datasets. Subsequently, the columns present in the games_paid_users dataset but absent in the full_data dataset were identified, and these missing columns were added to the
full_data.
full_data[['user_id', 'game_name']] = full_data[['user_id', 'game_name']].astype(str)
games_paid_users[['user_id', 'game_name']] = games_paid_users[['user_id', 'game_name']].astype(str)
missing_cols = games_paid_users.columns.difference(full_data.columns)
full_data = full_data.merge(games_paid_users[missing_cols.tolist() + ['user_id', 'game_name']] on=['user_id', 'game_name'], how='left')
The obtained full_data dataset has been saved as final_game_dataset.csv for use in other studies.
full_data.to_csv('final_games_dataset.csv', index=False)