Determine order frequency

[Yelizaveta Evreeva]

Hello,
The other day I was looking at a report containing supply orders from 2015 to present for various clients. With the help of pivot table I was able to look at all orders for each client in a nice organized tubular format. However, on piece of information that I could not get out of it just by quickly looking at the table is how often does a client place order? Is it once a month? Twice a month? Weekly? Quarterly? etc. Is there a formula that can be used or some easier way to determine how often a client orders?

The data looks like:

• Client A order dates:

• 1/9/2015
• 1/11/2015
• 1/14/2015
• 1/19/2015

• Client B order dates
  
  • 1/21/2015
    2/19/2015
    3/23/2015
    4/21/2015
    5/15/2015
• Client C order dates
  • 2/16/2015
    4/16/2015
    6/15/2015
    8/14/2015
    10/13/2015
    11/12/2015

And so on...it is obvious that the first client orders few times a week, client second client orders once a month, and the last one every other month or so...Now imagine I have 100,000+ clients and 200,000+ orders and no way to easy tell how often they place an order.

Any thoughts or ideas would be appreciated.

Thank you

 

[Hui]

Simplistically it is (Last Order date - First Order date)/No of Orders

That will give you Days per Order

or No of Orders/(Last Order date - First Order date)
Which is Orders per Day

 

[Yelizaveta Evreeva]

Everything genius is always simple. Thank you Hui! Now i recall something being similar discussed in one of my corporate finance classes :D

 

[marti_marti_marti]

Hi,
I have the same problem - how to build the table from Hui's comment from the pivot table (same as the one from Yelizaveta's post)

Thanks in advance for any advice!

Marti

 

[Peter Bartholomew]

I would suggest a slight modification to Hui's formula. Instead of
(Last Order date - First Order date) / No of Orders,
I would use
(Last Order date - First Order date) / No. of intervals between Orders
where

No. of intervals between Orders = No of Orders - 1

BTW You gets exactly the same result if you introduce a 'Days since last order' column and average that.​

 

[Peter Bartholomew]

@marti

If your pivot table has aggregated data by month then the count of transactions gives you the orders per month directly. If you then want an average value over a different time period you will need to take care that you do not average over periods for which a particular client data does not exist.