• 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
 
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
 
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.
 
@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.
 
Back
Top