0
$\begingroup$

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

$\endgroup$
1
  • $\begingroup$ I have tried an algebraic route to split up the numbers in the numerator to either traffic, conversion or ABP (yellow, red, green in the file), but have things in the numerator which are a mixture left over (in blue). 1drv.ms/1Euin76 $\endgroup$ Apr 23 '15 at 8:35
1
$\begingroup$

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!

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.