DATA Step, Macro, Functions and more

Identify transaction frequency of customer

Reply
Occasional Contributor
Posts: 6

Identify transaction frequency of customer

We need to find out the transaction frequency of customers. How many customers are doing atleast one transaction weekly once/ monthly once/ 2 months once, etc.can you please help on this.
Super User
Posts: 13,498

Re: Identify transaction frequency of customer

Posted in reply to aranganayagi

Provide example data in the form of a datastep and what the desired result is. Do you need a report that people read or a data set to feed another analysis process?

 

Since you are talking about date intervals the process will almost certainly work better if your date information is in the form of a SAS date valued variable an not character.

Occasional Contributor
Posts: 6

Re: Identify transaction frequency of customer

Posted in reply to aranganayagi
Thanks for the reply. I want to use the dataset for further analysis.
Dataset: I have last 3 months transaction data. If customer is doing atleast one transaction every week
I want to put the freq as 'weekly once'. If he is doing some transaction two week once, then freq should be '2 weeks once
If he is doing transaction monthly once, then it should be 'monthly once',
' 2 months once' , ' 3 months once, etc.
If one month the frequency is monthly once and on another month, if it is 2 weeks once, the freq should be lowest one ' 2 weeks once.
Customer_Id txn_date txn_id
A 13/5/2018 01
A 12/6/2018 02
B. 1/6/2018. 03
B. 8/6/2018. 04
B 15/6/2018 05
C. 1/4/2018. 06


Now I want the output to be like
Customer_id txn_freq
A monthly once
B weekly once
C 2 months once.
Regular Contributor
Posts: 209

Re: Identify transaction frequency of customer

Posted in reply to aranganayagi

That's an interesting problem ... will have to think about it ... calculating diffs (=date_diff) between dates (in weeks) and setting txn_freq to the lowest value found for each customer_id ... formatting date_diff to show the expected text ... just some initial thoughts.

Trusted Advisor
Posts: 1,337

Re: Identify transaction frequency of customer

Posted in reply to aranganayagi

I see how A is once per month, and B is once per week, given the intervals between records.  But there is no interval for C since it has only one record.  So why is it termed "2 months once"?  

 

Occasional Contributor
Posts: 6

Re: Identify transaction frequency of customer

Posted in reply to aranganayagi
Hi Error_prone, Thanks for the idea.
Hi Mkeintz, You are correct. Dataset should look like below. For the above dataset,C's freq should be 3 months once.

A 13/5/2018 01
A 12/6/2018 02
B. 1/6/2018. 03
B. 8/6/2018. 04
B 15/6/2018 05
C. 1/4/2018. 06
C 1/6/2018. 07
Trusted Advisor
Posts: 1,337

Re: Identify transaction frequency of customer

Posted in reply to aranganayagi

State your rules.

 

You now have 2 C records, one in April and one in June.  So how are we to know why you call this once per 3 months?

 

Please clearly state the criteria you use to infer each possible frequency.  For an effective coding solution, a clear specification of the requirements is needed.

Ask a Question
Discussion stats
  • 6 replies
  • 73 views
  • 0 likes
  • 4 in conversation