2
$\begingroup$

I am trying to figure out the average items sold per customer for the year 2019

I have multiple customers per day, who each have a random number of items. Sometimes a customer makes more than one purchase a day - about 6% of the time. In that case - all sales for that customer are considered to be 1 sale.

I have calculated this in 2 fashions - The 1st:

  1. Total # Items Sold ( 27,427,131 ) / Total # customers ( 6,556,133 ) = 4.18
  2. AVG(Items Sold Per Customer who make a purchase Per Day) = 4.21

The second way I did using an SQL function on an MS SQL Server The first way I did by getting the total # from the same SQL Database

With > 1 million customers - I can't replicate the 2nd method in Excel - and so I can't step it through and confirm the data.

I don't quite understand why the 2 avgs are different - but I suspected they would be and would like to use the # I think is more accurate - the 2nd one: 4.21 My boss has asked me to provide the formula used, and wants me to "prove" my answer. Not an unreasonable request, but I'm a little lost in explaining why I am getting 2 different averages. How do I explain this?

OR - and this is a real possibility - The numbers SHOULD be exactly the same and I am doing something wrong in one of my steps to calculate this.

$\endgroup$

1 Answer 1

1
$\begingroup$

You're running into weighted averages on unequal sample sizes. The second formula is incorrect.

Suppose there's only the following two days:

  1. 2 items sold, 1 customer
  2. 2 items sold, 2 customers

It's obvious the first formula generates the correct result ($4/3$, 4 items/3 customers), the second is $\mathrm{AVG}(2,1)$ which is not $4/3$.

See Simpson's paradox for how bad it can get.

$\endgroup$
2
  • $\begingroup$ This does help, but in my example, I am only referring to customers who actually made a purchase - clearly in the example you provide one customer didn't make a purchase $\endgroup$
    – kiltannen
    Commented Jun 4, 2020 at 2:58
  • $\begingroup$ Change it to 2/1 and 2/2 or whatever ($\mathrm{avg}(2,1)\neq4/3$). The point is avg(rate per sample) is fundamentally not correct to calculate a global rate. $\endgroup$
    – obscurans
    Commented Jun 4, 2020 at 9:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .