[Math] Contribution to change in year over year growth number

percentages

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:

  1. Number of people coming into the shop = (a)
  2. Conversion (number of buyers who come into the store and end up buying) = (b)
  3. 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!

Related Question