Calculate average length of buying (1 Viewer)

jpaokx

Registered User.
Local time
Today, 15:45
Joined
Sep 23, 2013
Messages
37
Hi all,

I have a problem to calculate the average length of buying any product for each customer. The way that I need to calculate this is to find the length based on payments for consecutive months.
For example,
I have a table called tbl.transactions with the following fields. For a customer, I have this:

Customer_Id, payment_date, payment_amount, product_type
10000001, 15/01/2016, $40, jean
10000001, 03/02/2016, $20, shoes
10000001, 23/03/2016, $30, t-shirt
10000001, 13/04/2016, $40, jean
10000001, 03/07/2016, $20, shirt
10000001, 03/08/2016, $60, jacket
10000001, 05/10/2016, $50, jean
10000001, 08/11/2016, $60, shoes
10000001, 27/12/2016, $20, jean


What I want to achieve is to find the difference of the months (not dates) based on consecutive payments first regardless of the product sold. So, I'd like to see

Customer_Id, initial_payment_date, difference_in_months
10000001, 15/01/2016, 4
10000001, 03/07/2016, 2
10000001, 05/10/2016, 3
*imagine that there are other customers with other customerids but this is a simplified example so that I can see how to achieve this.

And then I simply need to get an average for that customer. If I get the previous table somehow, then I can do the rest easily and achieve this:

Customer_Id, average_buying_in_months
10000001, 3

I put this last result just in case you think there is a better way for me to achieve this without the previous table.

Any thoughts how to do this? What type of query should I do? Anyone who has done something like this before?

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:45
Joined
May 7, 2009
Messages
19,169
Do you have autonumber field in your table? If yes it's doable.
 

jpaokx

Registered User.
Local time
Today, 15:45
Joined
Sep 23, 2013
Messages
37
Do you have autonumber field in your table? If yes it's doable.

No, but I can append this easily. So, if you have a solution based on auto number, then - yes - consider this too.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:45
Joined
Jan 23, 2006
Messages
15,364
Usually you would have a payment against an Invoice. I'm not sure how your set up would work if some one bought jeans( different brands) at different times. But maybe I'm not following your set up.
 

jpaokx

Registered User.
Local time
Today, 15:45
Joined
Sep 23, 2013
Messages
37
Usually you would have a payment against an Invoice. I'm not sure how your set up would work if some one bought jeans( different brands) at different times. But maybe I'm not following your set up.

Dont worry about the product and the different brands. My boss wants to have an overall idea about the purchasing behavior for each customer. If I have methodology to solve this one here, then I can work around another time and drill this down for different brands.

As for the invoices, these are not available to me.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:45
Joined
Jan 23, 2006
Messages
15,364
Is it purchasing behavior or payment behavior? You field names tend to describe Payments.
I would have expected SalesAmount or PurchaseAmt?? Just trying to clarify --not intended to be picky.
 

jpaokx

Registered User.
Local time
Today, 15:45
Joined
Sep 23, 2013
Messages
37
Is it purchasing behavior or payment behavior? You field names tend to describe Payments.
I would have expected SalesAmount or PurchaseAmt?? Just trying to clarify --not intended to be picky.

Here, I am looking at payments and how long each customer is staying based on their payments throughout their life cycle. The purchase_amount describes the payment made on that date.
 

stopher

AWF VIP
Local time
Today, 22:45
Joined
Feb 1, 2006
Messages
2,396
Unless I'm misunderstanding the problem, then to calculate the average payment period in months then you just need:

( (Last paymentment month) - (first paymentment) ) / (number of payments)

This is easy to calculate from queries or dlookups.

I don't understand how you calculated difference_in_months from your dates so I might be misunderstanding the whole thing. But as an example if you have say 4 payments with the first in feb (month 2) and the last in Aug (month 8) then the average payment period is:

(8-2)/4 = 1.5 i.e. a payment every 1.5 months.

hth
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:45
Joined
Jan 23, 2006
Messages
15,364
Chris,
I agree. I'm confused with the payment info and the products --too much info??
 

jpaokx

Registered User.
Local time
Today, 15:45
Joined
Sep 23, 2013
Messages
37
Hi stopher/jdraw,

Just to explain this a bit better.

Earlier, I explained that you need to ignore the product. I simply need to calculate the average of length of payments on consecutive months no matter of which product was sold.

Also, I don't want to see how often they are paying but for how long they are paying on average.

Let's take the first group from my example:

Customer_Id, payment_date, payment_amount, product_type
10000001, 15/01/2016, $40, jean
10000001, 03/02/2016, $20, shoes
10000001, 23/03/2016, $30, t-shirt
10000001, 13/04/2016, $40, jean

In this case, there are payments in 4 consecutive months. So, I'll need to put 4 down there while the initial date of transaction was 15/03/2016.

Customer_Id, initial_payment_date, difference_in_months
10000001, 15/01/2016, 4

Same thing for the other groups of consecutive months.
So, this one:
10000001, 03/07/2016, $20, shirt
10000001, 03/08/2016, $60, jacket

will give me 2 months.

And this one:
10000001, 05/10/2016, $50, jean
10000001, 08/11/2016, $60, shoes
10000001, 27/12/2016, $20, jean

will give me 3 months.

So, the table that I want to calculate is basically the average length of service/payment which will be this one:

Customer_Id, initial_payment_date, difference_in_months
10000001, 15/01/2016, 4
10000001, 03/07/2016, 2
10000001, 05/10/2016, 3

The average of 4, 2 and 3 is 3. So, practically, I want to say that that customer 10000001 is paying or staying for 3 months on average.

I cannot use the formula ( (Last paymentment month) - (first paymentment) ) / (number of payments) because it ignores the gaps being made in the between periods. For example, there wasn't any payment in September 2016 and, so, the person wasn't considered as a customer. So, I should really need to work based on start and end dates of consecutive months.

Does this make sense now what I am after?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:45
Joined
May 7, 2009
Messages
19,169
Please find the attached dB.
 

Attachments

  • AvgBuyingInMonth.zip
    43.4 KB · Views: 215

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:45
Joined
May 7, 2009
Messages
19,169
It's been days. I thought it's another unsolved case.
 

Users who are viewing this thread

Top Bottom