# 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 *****;
** (2) Filter exclusion: Promotion History / DO-NOT-CALL / INACTIVE / NO MARKETING CONSENT **;

proc sql;
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;
select *, 'Restaurant Promotion' as Promo_name
where custno not in
(select custno
from promo_count_6plus);
quit;

** Set assign 10% control **;
randvalue = ranuni(100);
if randvalue < 0.1 then output lead2_control;

drop randvalue;
run;

data _null_;
put @1 custno @20 Promo_name;
run;

**** Tracking ****;

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;