Average time between purchase orders (1 Viewer)

[jpaokx]

Hi everyone,

I have a small problem for which I have found a partly solution.
I have a transactional history of purchases and I'd like to find the average time between purchases for each customer.

For example, my table1 looks like this:

id customerid order_date
1 1111111 03/02/2011
2 2222222 06/03/2011
3 1111111 11/08/2012
4 2222222 10/09/2012
5 3333333 01/02/2013
6 3333333 02/03/2013
7 3333333 22/04/2014
8 1111111 15/07/2014
9 3333333 02/08/2015
10 1111111 11/11/2015

What I am trying to to get is this one:

customerid avg_time_between_orders_in_months
1111111 5.50
2222222 7.85
3333333 6.22

What I've done so far is to find the average time between 1st and 2nd order by creating two temporary tables (one with the initial order for each customer and another one with the 2nd order). However, considering that my transactional history table is huge (more than a million rows) and a customer may have 1 order or 100 orders, I cannot continue this process of creating temporary tables forever.

Has anyone experienced something like this? Ideally, I am trying to find the average values per year, but, if someone can direct me to the solution, I think that I can work on this to estimate on the yearly values.
Any thoughts? :banghead:

Thanks!

 

[plog]

This can be done much simpler with just SQL. You need a sub-query that gets the next order date for every record. Then you can use DateDiff to get the months between them, then finally, you average that. Here's a query that will get you 95% of the way there:

SELECT table1.customerid, 1*DateDiff("m",[order_date],(SELECT MIN(order_date) FROM table1 as sub1 WHERE table1.order_date<sub1.order_date AND table1.customerid=sub1.customerid)) AS MonthsBetweenOrders
FROM table1
WHERE ((((SELECT MIN(order_date) FROM table1 as sub1 WHERE table1.order_date<sub1.order_date AND table1.customerid=sub1.customerid)) Is Not Null));

Paste that into a query and save it as 'AverageSalesTime_sub1. Then, build another query using it, bring in customerid and MonthsBetweenOrers, make it an aggregate function and get the average of Months BetweenOrders.

 

[arnelgp]

if i may ask, how do you compute the average date value per year?

 

[stopher]

How do you get the results from the supplied data? Take 1111111 for example. The difference in months between each date is 18, 23 and 16 months respectively. So the average is around 19months. How do you get to 5.5?

As an aside, you don't need to calculate the difference for each order. You only need to calculate the difference between the first and last order. Suppose the times for four orders were A, B, C and D. The the time between orders is:
B-A, C-B and D-C. So the average is:

((B-A) + (C-B) + (D-C) ) / 3

Which simplifies to:
(D-A)/3


So just write a query to find the min/max and count for each group and the formula becomes (max-min)/(count-1)

hth
Chris

 

[jpaokx]

Hi guys,

Thanks for the help.
Stopher, your are right. However, that was just an example. These are not real figures. I just wrote some random values so that I can raise this query. The purpose is for me to understand the procedure to extract the average time between transactions.

arnelgp, If I had to calculate values per year, I would simply take the differences between dates within the year and, then, I would take the average.

plog, thanks for this. I will try this tomorrow at work and I'll let you know if this works!
I found something interesting here that may work for me http://sqlmag.com/t-sql/how-determine-average-number-days-between-orders-or-other-important-events
but I dont understand the part how he joins the customerkeys and take the exact next row here
http://sqlmag.com/site-files/sqlmag...om/content/content/101924/101924 Figure 3.jpg

If someone can explain this to me, then I think that this can work for my purposes.

Thanks

 

[sneuberg]

Stopher, your are right. However, that was just an example. These are not real figures. I just wrote some random values so that I can raise this query. The purpose is for me to understand the procedure to extract the average time between transactionss

How would your real figures change what stopher said? What he demonstrated seems to be true for any figures.

 

[jpaokx]

Hi,

As explained above, these were just random numbers for the purposes of this example.
However, if I have to give the real averages (in case someone is confused from my example), then I would expect to get the following for all years:

customerid avg_time_between_orders_in_months
1111111 19.36
2222222 18.47
3333333 10.13

 

[arnelgp]

how would stopher formula work (the simplified one (D-A)/3), when for instance there is a big gap in years between PO's?

 

[arnelgp]

im using same formula as stopher (the first formula not the simplified), but im getting different results than yours.

Public Function fnAvgPOPerCust(vCustID As Variant, sQuery As String) As Double
Dim d As Double
Dim l As Long
Dim diff As Long
Dim d1 As Date
Dim d2 As Date
Dim second As Boolean
Dim db As DAO.Database
Dim rs As DAO.recordSet

Set db = CurrentDb
Set rs = db.QueryDefs(sQuery).OpenRecordset(dbOpenSnapshot)
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
        If TypeName(vCustID) = "String" Then
            .FindFirst "[customerid] = " & Chr(34) & vCustID & Chr(34)
        Else
            .FindFirst "[customerid] = " & vCustID
        End If
        If Not .NoMatch Then
            Do While Not .EOF
                If vCustID <> !CustomerID Then Exit Do
                l = l + 1
                If Not second Then
                    d2 = CDate(Format(!order_date, "mm/dd/yyyy"))
                    second = True
                Else
                    d1 = CDate(Format(d2, "mm/dd/yyyy"))
                    d2 = CDate(Format(!order_date, "mm/dd/yyyy"))
                    diff = diff + DateDiff("m", d1, d2)
                End If
                .MoveNext
            Loop
        End If
    End If
    .Close
End With
Set rs = Nothing
Set db = Nothing
If diff = 0 Then
    fnAvgPOPerCust = 1
Else
    fnAvgPOPerCust = diff / l
End If
End Function

for this to work you need to create a query based on your table (say qryCustOrderDate):

select customerid, order_date from table1 order by customerid, order_date;

then create a new query that will use our function.
pass the customerid and the query name you made above:

SELECT distinct customerid, fnAvgPOPerCust([customerid],"qryCustOrderDate") as expr
FROM table1;

 

[sneuberg]

However, if I have to give the real averages (in case someone is confused from my example), then I would expect to get the following for all years:

customerid avg_time_between_orders_in_months
1111111 19.36
2222222 18.47
3333333 10.13

I implemented stopher's simplification in the attached database and with the assumption that a month has 30 days it produces these results. The final answer is given by the query qyrStopher2

 

[arnelgp]

that's great mr.sneuberg, only one thing.
you'll have a division by zero when there is only one po count.

 

[sneuberg]

that's great mr.sneuberg, only one thing.
you'll have a division by zero when there is only one po count.

That could be easily fixed. I didn't post this as solution but to prove a point. I guess I should have said that.

 

[plog]

I always like the simplest solution, so my vote is for stopher's method. Not via vba (that is overkill). Stopher's method can be implemented just in SQL, probably in one query, unlike mine which technically took 3.

 

[jpaokx]

ahhhh....so simple solution!!! sneuberg, I like your solution.
Thanks a lot everyone!

Just out of curiosity, if I want to work on yearly averages, should it be based on the min date of the corresponding year and the max of any year?

 

[jdraw]

you'll have a division by zero when there is only one po count.

Not to be too picky, but having only 1 PO would indicate there is NO between based on the post title Average time between purchase orders.

I agree that, if there is only 1 PO, there should be an appropriate message and handled as part of the logic.

I like stopher's solution - clear and simple.

Good luck.

 

[sneuberg]

that's great mr.sneuberg, only one thing.
you'll have a division by zero when there is only one po count.

The attached the qryStopher2 query in the attached database has been modified so that the Avg Months expression doesn't produce an error if there is only one PO. The expression evaluates to zero for these cases. The modified expression is:

Avg Months: DateDiff("d",[MinOforder_date],[MaxOforder_date])/(IIf([CountOfID]-1=0,1,[CountOfID]-1)*30)