Let’s understand the difference between the average check and ARPU using the example of one online store
A colleague approached me with questions about business metrics – average bill and ARPU. Can the indicator be the same for two metrics and not change from month to month, especially within one business that has a fixed number of products and one price? Plus, why businesses are trying to increase these metrics.
In this article, I understood business metrics and answered the questions:
What is ARPU and average bill? How to calculate them? What questions do they answer and what are they needed for?
Can ARPU and average bill be equal to each other? Will the dynamics differ from month to month?
What if in a business the number of products is fixed and they all have the same price? Will the indicator be the same from month to month? What if you calculate average revenue?
Let’s figure everything out in order. And for clarity, let’s calculate these metrics using real data from an online store.
Let’s go!
What is ARPU and average bill? How to calculate them? What questions do they answer and what are they needed for?
Wiki gives a fairly simple definition of metrics:
Average bill – considers the ratio of income to all transactions for a certain period. This is the amount revenue by the number of all transactions for a specific period.
ARPU looks at the ratio of revenue to the number of users in a certain period. This is the amount revenue per quantity usersthat may or may not have been done transaction in a specific period.
We already see a significant difference in the denominator, average check these are transactions ARPU are users who can make transactions. We also see that in such a comparison average bill shows greater accuracy in business profitability, since in ARPU the segment of non-paying users is taken into account, which can settle as a “dead” mass that distorts the picture.
To improve accuracy, it is better to calculate ARPPU(average revenue per paying user) — average income per paying user. Again, there is a difference compared to average checksince in the denominator of the calculation ARPPU It is the users who made one or more transactions for a specific (compared) period that are taken into account.
So, ARPU (average revenue per user) — product revenue for a certain period divided by quantity active users for the same period. For example, moving through sections of the site, clicking on buttons, scrolling pages, having a long session, etc.
Metrica answers the question: how much revenue the average active user brings in over the time period being studied. That is, roughly, it means the average income per client over a period of time (the definition of client depends on the business). The metric can be used when you need to digitize the value of a product for users.
But it’s better to take ARPPU — revenue from paying users (for example, making purchases, connecting to paid services, accepting offers, upselling tariffs, etc.). That is, it takes into account not users, but those who made payments over time. The metric helps to evaluate, for example, how customers perceived price changes.
Average bill helps to understand revenue by orders, not by customers. That is, this is all the revenue for the month divided by the number of orders (transactions). It shows how much money is spent on one order (not per customer).
Again, one order (transaction) can contain several products – for example, as in marketplaces, when one user transaction can contain several products from different categories and with different prices. For example: iPhone and Baskin Robins ice cream.
Thus, the three metrics are different indicators, with different calculation formulas and answering different business needs.
ARPU helps to assess the financial potential of a company – the higher it is and the closer it is to ARPPU, the more users are willing to pay for the product. Either attract investors, or evaluate changes in the tariff schedule.
Considering the number of paying users, you can estimate how income increases. Revenue can grow not due to price increases, but due to an increase in the share of paying users.
Also using ARPU You can analyze the results of advertising campaigns and the quality of traffic.
Average bill — one of the main indicators of successful development of the company. In a good way, it should grow, not decrease.
Average bill helps to assess the solvency of the audience, identify seasonality, evaluate the effectiveness of the website or application interface in scenarios of targeted actions.
Even if revenue is growing, a decrease in the average bill may indicate that revenue is growing due to attracting new customers. But in the long term, purchasing power decreases and prices need to be adjusted.
Thus, in the month of the release of a new iPhone, the average bill in the “gadgets and equipment” product category increases significantly. Or users willingly and best sign up for a subscription through a tariff designer, while this product has the lowest average bill. Perhaps here it is necessary to provide a “boxed” solution – a tariff that includes the most popular subscriptions and is higher in price.
Can ARPU and average bill be equal? Will the dynamics differ from month to month?
We need a dataset. We climb on kaggleand take a dataset from some e-shop.
To analyze and calculate metrics, we need something like the following table:
Дата|id_пользователя|id_заказа или транзакции или продукта|название продукта|ценаI took this one dataset.
# Загружаем датасет
df = pd.read_csv('sales_transactions.csv')
df.head()
Working with the original dataset
Let’s rename the column names for convenience.
We will check the quality of the data (the presence of gaps, incorrect entries, prohibitive values, etc.).
Let’s bring transaction, product and user IDs into a convenient format.
Let’s convert the date to a convenient format.
Let’s do a little EDA (which countries are in the dataset, bring the price to a single currency and understand if there are differences, etc.).
# Переименовываем столбцы для удобства
df.set_axis(['transaction_id', 'date', 'product_id', 'product',
'price', 'quantity', 'user_id', 'country'], axis="columns", inplace = True)# Количествово строк в датасете
df.shape(536350, 8)# Проверка на наличие пропусков
for row in df.columns:
print('Кол-во пропусков в столбце ', row, '=', df[row].isnull().sum())
Missing 55 user id records
This is not good – most likely it is a technical bug or something else that is why there are no IDs. Let’s check.
# Пропуски в id пользаков нельзя восстановить, поэтому заменим их на ноль.
df['user_id'] = df['user_id'].fillna(0)
# Посмотрим записи по таким пользователям
df[df['user_id'] == 0]
Yeah, users with no id in most cases have a negative entry for the number of goods, and the transaction_id starts with the letter “C”, which means “return of goods”. From this sample there is only one record with a positive number of products, but also without a user id.
298352 558245 6/27/2019 22734 Set Of 6 Ribbons Vintage Christmas 10.25 8 0.0 United KingdomIn our dataset there are only 536,350 rows, the proportion of rows with missing user ids is less than one percent. They can be deleted without loss of data quality.
At the same time, we’ll check how many product returns we have.
# Если первая буква «C» в айдишке указывает на «возврат», найдем общее количество возвратов.
import re
def cancelation(x) :
x = str(x)
pattern = r'[C]'
return re.findall(pattern, x)
cancel = df['transaction_id'].apply(lambda x: True if 'C' in cancelation(x) else False)
print("Количество возвратов {}".format(cancel.sum()))Number of returns 8,585
# Делаем отдельный столбец с обозначением возвратов. Понадобится для исключения «возвратов»
df['cancel'] = cancel
# Считаем
df['cancel'].value_counts(normalize=True)*100
The number of returns is 8,585 thousand. This is approximately one and a half percent of the main dataset. We will clean them too.
# Удалим значения с возвратами
index_cancel = df[df['cancel']==True].index
df.drop(index=index_cancel, inplace=True)
# Удалим значения с нулевыми id пользаков
df = df[df['user_id'] != 0]Let’s check if we still have negative values for the number of products. And in general, let’s roughly estimate based on the maximum and minimum values of the dataset (quartiles)
There are no negative values, but we see that there is an exorbitant value for the maximum number of goods, 80,995 pcs. something. Let’s take a closer look.
Hmmm, one user bought 80 thousand small birds made of craft paper. Hah, everyone has their own quirks. Let’s see if this user has any other transactions.
This user previously purchased a broom and brush to clean out the pantry. Perhaps this is some office manager who made a huge order (of paper birds) as gifts for company employees or office decoration. That is, this may be a real order, and not a technical bug. However, for our further research on calculating the average check and ARPU, this quantity of goods in a transaction will look like an “outlier” pulling the average value. Therefore, we will delete this order.
But this is a very bad practice and it is better not to do this at work. Because this is a real user with a real purchase, which may turn out to be a “whale” among the rest. And removing such users leads to a distortion of reality in understanding business performance.
We will also make sure that we have provided data for the entire period and that there are no gaps. To do this, we will group the number of transactions (presumably they were every day) by date, and draw a graph for clarity.
# Смотрим период данных
print('Начало периода: ', df['date'].min())
print('Конец периода: ', df['date'].max())
cnt_transaction = df.groupby('date')['transaction_id'].count().reset_index()
sns.set(font_scale=1.2)
plot = cnt_transaction.plot( x = 'date', y = 'transaction_id',
grid = True, rot=45,
legend=False, figsize=(17,5))
plt.title('Кол-во транзакций в динамике')
plt.xlabel('')
plt.ylabel('Кол-во транзакций')
plt.ylim(bottom=0);Начало периода: 2018-12-01 00:00:00
Конец периода: 2019-12-09 00:00:00
Great! The data is presented for the entire specified period.
The price of the goods is indicated in pounds sterling – I learned about this from the description of the dataset. For our task (finding the average bill and ARPU), it is more convenient to convert the prices of goods into rubles.
To do this, we parse the current exchange rate for currencies from the bank’s page. We get the dataset, find the required currency and exchange rate value, multiply by the price and write the new price in rubles in a separate column price_rubls
import requests
data = requests.get('https://www.cbr-xml-daily.ru/daily_json.js').json()
df['price_rubls'] = df['price'].apply(lambda x: x * data['Valute']['GBP']['Value']).astype(int)
So, the dataset is ready for work.
Let’s look at which countries are represented in the dataset and display the top 15 by the number of records.
df['country'].value_counts().head(15)United Kingdom 477769
France 10393
Germany 10240
EIRE 7807
Belgium 2507
Spain 2386
Netherlands 2326
Switzerland 2303
Portugal 1838
Australia 1631
Norway 927
Austria 884
Iceland 787
Finland 686
Italy 624
Name: country, dtype: int64Wow! Apparently this online store sells all over the world, and not just in Britain, as I previously thought.
While working with the dataset, I noticed that one date may have non-unique transactions, and the records of one transaction may contain several products with different product_ids, but the same user_id.
It is also worth paying attention to the quantity column.
Thus, for our task we need to aggregate the dataset into an example showcase:
Average bill
date | transaction_id | выручка с одной транзакции (сумма всех позиций (кол-во товаров (quantity) * цену(price_rubls)), внутри одной транзакции) ARPU
A similar showcase, but instead of transaction_id we take user_id
# Рассчитываем выручку с одного продукта внутри транзакции (количество продукта умнажаем на цену).
df['revenue_per_transaction'] = (df['quantity']*df['price_rubls']).astype(int)
Now let’s look at the top 15 countries by revenue for the entire period. Let’s estimate what scale we are dealing with so that the calculated average bill and ARPU in the future will correspond to common sense.
country_group = (df.groupby('country', as_index=False)['revenue_per_transaction'].sum()
.sort_values(by='revenue_per_transaction', ascending=False)
.head(15))
plt.figure(figsize=(17,10))
sns.set_style('darkgrid')
sns.barplot(y = 'country',
x = 'revenue_per_transaction',
data = country_group,
palette="RdBu")
plt.title('Топ-15 стран по выручке')
plt.xlabel('Выручка')
plt.ylabel('');
country_group['revenue_per_transaction'] = (country_group['revenue_per_transaction'].apply(lambda x: '{0:,}'.
format(int(x)).replace(',', ' ')))
country_group
Wow! Extraordinary numbers. Just think about it, Britain’s revenue for the year was 6,179,180,204 (Six billion one hundred seventy-nine million one hundred eighty thousand two hundred four)
But let’s return to our main task – comparing the average check and ARPU. Will they be the same? Will the metric be the same from month to month?
And with a fixed number of products and a constant price for these products, will the indicator be the same from month to month?
Average bill
We calculated the revenue for each product within the transaction. Now let’s calculate the total revenue for all positions (products) within the transaction and group it by date.
# Для этого группируем по дате и транзакции, агрегируем сумму по посчтианной выручке за продукт
group_date_tr = (df.groupby(['date','transaction_id'], as_index=False).agg({'revenue_per_transaction':'sum'})
.sort_values(by='date'))
group_date_tr
All that remains is to convert the date to month, group the data by month and for each month divide the total amount by the number of transactions.
# Переводим дату в месяц
group_date_tr['month'] = group_date_tr['date'].astype('datetime64[M]')
# Группируем по месяцу, рассчитываем количество уникальных транзакций и сумму по всем транзакциям
month_average_cheack = group_date_tr.groupby('month', as_index=False).agg({'transaction_id':'nunique',
'revenue_per_transaction':'sum'})
# Переводим количество транзакций в нужный формат
month_average_cheack['transaction_id'] = month_average_cheack['transaction_id'].astype(int)
# Рассчитываем средний чек. Сумма на количество
month_average_cheack['average_check'] = ((month_average_cheack['revenue_per_transaction'] / month_average_cheack['transaction_id'])
.astype(int))
# Представим на графике в динамике по месяцам
plot = month_average_cheack.plot(x = 'month', y = 'average_check',
grid=True, rot=45,
legend=False, figsize=(17,8))
plt.title('Средний чек в динамике по месяцам')
plt.xlabel('')
plt.ylabel('Средний чек')
plt.show();
Yeah! We see that the average bill differs from month to month. But the visualization was chosen poorly. Let’s make it more informative
fig = px.bar(month_average_cheack, x='month', y='average_check', color="average_check",
title="Средний чек по месяцам")
fig.update_xaxes(title="Месяц")
fig.update_yaxes(title="Средний чек")
fig.show()
Now you can clearly compare. And we can conclude that the highest average check was in January 19th, then there was a drawdown. Also, the average bill grew in the fall of 19. We cannot compare seasonality in December and January, and from year to year, since we are limited by the initial period of the dataset.
ARPU
We do the same thing, but with user_id. And as a result, we divide the amount for all transactions by the number of unique users.
The dataset contains only users who have made a purchase, that is, only paying users. And accordingly in this case ARPU=ARPPU
ARPU, just like the average bill, differs from month to month. We also see a high figure for January 2019, but, interestingly, a smoother and more consistent growth from June to August, compared to the average bill. Apparently prices did not change, but users were added.
Let’s compare the average bill with ARPU over months. For clarity, let’s display the metrics on one visualization
That’s all.
General conclusion: Metrics differ from each other and their performance differs from month to month.
Sobut what if you calculate the average revenue and compare the average bill month to month?
To do this, we group by month, calculate the number of unique transactions and the average revenue for all transactions for this month. Next, we divide the average revenue by the number of transactions.
Please. We got an average, but it also differs from month to month.
What if in a business the number of products is fixed and they all have the same price? Will the indicator be the same from month to month? What if you calculate average revenue?
So, what if we have a fixed quantity of a product and a constant price? Yes, in principle, nothing special, the metrics will also be different, because the number of transactions in the case of an average check and the number of users for ARPU will be different.
But still, if the number of users is the same, the number of transactions, the number of products, and the price. For example, a subscription to a premium tariff from Kinopoisk (I’m making it up as I go). We have a limited and constant number of users (closed club, we don’t let newcomers in, old people are all accepted and don’t leave), only one product (subscription to a tariff), the price is also fixed (we don’t reduce it for promotions, we don’t raise it for inflation). Then yes, it’s possible. But the formula also changes: average revenue = average check = ARPU. But I haven’t seen anything like this yet.
My complete notebook can be downloaded Here
