• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Determine order frequency

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

Excel Ninja
Staff member
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

upload_2016-3-29_22-46-6.png
 
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

Well-Known Member
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

Well-Known Member
@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.
 
Top