I have a list of customer records. For each customer, we create a binary variable to see whether they have taken up an offer for any particular month through a campaign period. Say, campaign period runs from July until October. If the customer took an offer in August, the flag equals to 1, 0 otherwise. The customer may take an offer in other months as well during that period. Now, for each customer, I'm interested to know what were their spending prior and post the first time they took the offer. For example, customer id_01 took the offer for the first time in July. Therefore the spend one month before is the spend in June. For customer id_02, the first offer was in the month of August. Therefore the spend one month before for this customer is the spend in July. Basically for each customer, I want to track their spend prior to the first offer take up. Same logic can be used to get the spending post the offer take up. Thanks data have;
input cust_id:$8. cust_take_offer_Jul:$1. cust_take_offer_Aug:$1. cust_take_offer_Sep:$1. cust_take_offer_Oct:$1. cust_spend_Jan:10. cust_spend_Feb:10. cust_spend_Mar:10. cust_spend_Apr:10. /*the rest of the month until December*/ cust_spend_Dec:10. datalines; id_01 1 0 1 0 69 112.5 ...
id_02 0 1 1 1 56.5 84 ...
id_03 0 0 1 1 65.3 98 ...
id_04 0 1 1 0 62.8 102.5 ...
id_05 0 0 1 0 63.5 102.5 ...
;;;; data want;
input cust_id:$8. first_offer_takeup:$1. pre_spend_1month :10. pre_spend_2month :10. pre_spend_3month :10. spend_takeup_offer_month : 10 post_spend_1month :10. post_spend_2month :10. post_spend_3month :10. datalines; id_01 July 69 112.5 ...
id_02 August 56.5 84 ...
id_03 September 65.3 98 ...
id_04 August 62.8 102.5 ...
id_05 September 63.5 102.5 ...
;;;;
... View more