I am trying to figure out a solution to a unique problem.
I am trying to calculate the contribution to year over year change (over quarters) in the sales of a shop, highlighting the contribution of 3 areas:
- Number of people coming into the shop = (a)
- Conversion (number of buyers who come into the store and end up buying) = (b)
- Average sales price of items sold = (c)
Essentially the above 3 points can be summarized into:
Total Sales = (a) * (b) * (c)
If the below is true:
- Q1: Year over year sales of 10%
- Q2: Year over year sales of 20%
How can I build a contribution to growth walk, where I break down how much each aspect (traffic, conversion, average sales price) had in the 10ppt difference between Year over year in Q1 to Q2?
Is there a way of breaking this down into 3 numbers (one each for (a), (b) and (c)) where I show the contribution each had to the 10ppt difference?
Below is a concrete example with numbers, in case that helps with understanding:
- 2014 Q1: Sales of 100 dollars. 1000 customers, conversion of 10%, average sales price of 1 dollar
- 2015 Q1: sales of 110 dollars. 2000 customers, conversion of 5%, average sales price of 1.1 dollars
- 2014 Q2: Sales of 150 dollars. 1500 customers, conversion of 20%, average sales price of 0.5 dollars
- 2015 Q2: Sales of 180 dollars. 2000 customers, conversion of 15%, average sales price of 0.6 dollars
Any help would be greatly appreciated!
Thanks,
D
Best Answer
This sounds like more a question of interpretation than of mathematics, but my intuition is to take the logarithm of everything. So, if the parameters in one quarter were $s_1 = a_1b_1c_1$ and those in the second quarter were $s_2 = a_2b_2c_2$, then we can also write
$$ \log s_1 = \log a_1 + \log b_1 + \log c_1 \\ \log s_2 = \log a_2 + \log b_2 + \log c_2 $$
and you should find that the percentages are easier to define. For instance, with your $2014$ numbers, from the first quarter to the second, we have
$$ \log 100 = \log 1000 + \log 0.1 + \log 1 \\ 2 = 3 + (-1) + 0 $$
as compared to
$$ \log 150 = \log 1500 + \log 0.2 + \log 0.5 \\ 2.176 = 3.176 + (-0.699) + (-0.301) $$
The log of the total sales went up $0.176$, of which increase in customers accounted for $0.176$, increase in conversion accounted for $0.301$, and increase (negative) in sales price accounted for $-0.301$. The percentage breakdown would be increase in customers representing $100$ percent, increase in conversion representing $301/176\cdot 100 \doteq 171$ percent, and increase (negative) in sales price representing $-171$ percent.
A scenario where each component accounted for a positive increase would be as follows. Suppose that in the second quarter, we instead had
$$ \log 210 = \log 1250 + \log 0.12 + \log 1.4 \\ 2.322 = 3.097 + (-0.921) + 0.146 \\ $$
In this case, we would have an increase in log sales of $0.322$, of which $0.097$ came from customer count, $0.079$ came from conversion, and $0.146$ came from sales price, resulting in a percentage split of approximately $30$, $25$, and $45$ percent, respectively.
EDIT: I fixed the numerical values in the second example, some of which were in error. Sorry about that!