Sentry Page Protection

Please Wait...

Solution

---

​** Restaurant spenders who have made 3+ restaurant spending a month **;
proc sql;
create table tran_rest as
select custno, MCC, count(*) as num_transaction, mean(transaction_amount) as avgamt
from transaction_table_202001
where MCC in ('5812', '5813', '5814')
group by custno, MCC
order by custno, MCC;
quit;

proc sql;
select MCC, mean(num_transaction) as avg_transaction_cnt,
            mean(avgamt) as avg_spend
from tran_rest
group by MCC
order by MCC;
quit;


** Further Analysis **;
proc sql;
create table tran_rest2 as
select custno, count(*) as num_transaction, mean(transaction_amount) as avgamt
from transaction_table_202001
where MCC in ('5812', '5813')
group by custno
order by custno;
quit;

proc freq data=tran_rest2;
table num_transaction;
run;



**** Target: restaurant spenders with no more than 3 times spending and income > 80000 ****;
proc sql;
create table target_base_temp1 as
select *
from tran_rest2
where num_transaction <=3;
quit;

proc sql;
create table target_base3 as
select a.*, b.income, age, gender, marketing_consent, phone, status
from target_base_temp1 a inner join customer_base b
on a.custno = b.custno
where income > 80000;
quit;



**** Campaign Execution ****;
***** Campaign Execution *****;
** (1) Create lead **;
** (2) Filter exclusion: Promotion History / DO-NOT-CALL / INACTIVE / NO MARKETING CONSENT **;

proc sql;
create table lead2_temp1 as
select *
from target_base3
where phone not in 
  (select do_not_call_number
   from do_not_call) and
      status = 1 and
      marketing_consent = 1;
quit;

proc sql;
create table promo_count_6plus as
select custno, count(*) as num_promo
from promotion_history
group by custno
having num_promo >=6;
quit;

proc sql;
create table lead2_temp2 as
select *, 'Restaurant Promotion' as Promo_name
from lead2_temp1
where custno not in 
  (select custno
   from promo_count_6plus);
quit;

** Set assign 10% control **;
data lead2 lead2_control;
set lead2_temp2;
randvalue = ranuni(100);
if randvalue < 0.1 then output lead2_control;
else output lead2;

drop randvalue;
run;


filename lead2 '/folders/myfolders/Projects/Credit Card/lead2.txt';

data _null_;
set lead2;
file lead2;
put @1 custno @20 Promo_name;
run;



**** Tracking ****;

data all_lead2;
set lead2 (in=a) lead2_control (in=b);
length flag $8;
if a then flag = 'Target';
else if b then flag = 'Control';
run;

** Spending in 202002 **;
proc sql;
create table tran_track_temp1 as
select a.*, age, income, gender, flag
from transaction_table_202002 a inner join all_lead2 b
on a.custno = b.custno
order by a.custno;
quit;

** Did Target group customers make more transaction than control? **;
proc sql;
create table tran_track as
select custno, flag, count(*) as num_transac, sum(transaction_amount) as tot_spend
from tran_track_temp1
group by custno, flag
order by custno, flag;
quit;

proc ttest data=tran_track;
class flag;
var tot_spend;
run;

proc ttest data=tran_track;
class flag;
var num_transac;
run;