Sentry Page Protection
Credit Card Project [3-6]

In the previous module, we created a target base of 2608 customers.
 
In this module, we will learn how to exclude customers who:
  • Are on the national do-not-call list
  • Have received too many promotional messages from us and
  • Have not given consent to receive marketing materials from us.
 
We will also set aside 10% of the target base as the control group for future tracking purposes.
 
National Do-Not-Call List
 
The national do-not-call list is managed by the Federal Trade Commission (FTC). It allows you to register to not receive any telemarketing calls to your phone number.
 
Our promotions include telemarketing calls and we will exclude phone numbers on the national do-not-call list.
 
The do-not-call list data is available in the Do_not_call data set:


​It includes a list of 500,000 phone numbers on the do-not-call list:


The numbers are encrypted for privacy reasons.
 
Now, we will create our first marketing lead based on our target base. We will exclude customers who are:
  1. On the national do-not-call list
  2. Inactive and
  3. Have not provided consent to receive marketing materials from us.
proc sql;
create table lead1_temp1 as
select *
from target_base
where phone not in 
  (select do_not_call_number
   from do_not_call) and
      status = 1 and
      marketing_consent = 1;
quit;

The code above has three conditions in the Where statement.
 
The first condition excludes those who on the national do-not-call list:
proc sql;
create table lead1_temp1 as
select *
from target_base
where phone not in 
  (select do_not_call_number
   from do_not_call) and
      status = 1 and
      marketing_consent = 1;
quit;

​The second and third condition captures only the customers who are active (i.e. status=1) and have provided marketing consent (i.e. marketing_consent = 1).
proc sql;
create table lead1_temp1 as
select *
from target_base
where phone not in 
  (select do_not_call_number
   from do_not_call) and
      status = 1 and
      marketing_consent = 1;
Quit

​After the exclusion, we have 2194 customers left in the target base:


​The exclusion drops about 400 customers from our target base.
 
However, we are not done yet.
 
We need to also exclude customers who have already received too many marketing messages from us.
 
We will look at the promotion_history table to find out how many times a customer has been marketed in our promotions in January 2020.
 
The Promotion_history table contains only three columns. It has the promotion history for our customer base:

​Now, we will complete a quick step to compute the number of times each customer has received promotions in January 2020:

proc sql;
create table promo_count as
select custno, count(*) as num_promo
from promotion_history
group by custno;
quit

For each customer, the code above counts how many times they have been targeted in a promotion.


​Let’s look at the frequency count:

proc freq data=promo_count;
table num_promo;
run;

There are around 20,000 customers who have already been in one of our previous promotions six times or more:

We do not want to bother our customers too many times, so we will exclude them from our target base.
​
First, we will create a data set that contains customers who have received our promotions six times or more:
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;

​We will then exclude these customers from our target base:
proc sql;
create table lead1_temp2 as
select *, 'Cosmetic Promotion' as Promo_name
from lead1_temp1
where custno not in 
  (select custno
   from promo_count_6plus);
quit;

Are we done yet? No, not yet.
 
We wouldn't know whether the promo is effective or not until we track the results.
 
We will need to set aside 10% of the customers to the control group.
 
This can be done by using a simple RAND function in SAS.
data lead1 lead1_control;
set lead1_temp2;
​
randvalue = ranuni(100);

if randvalue < 0.1 then output lead1_control;
else output lead1;
 
drop randvalue;
run;

The Ranuni function (seed=100) generates a random value between 0 and 1 for each observation.

If the random value is less than 0.1, we will assign the customers to the control group.
 
Otherwise, they will be in the target group.
 
The code above creates two data sets:
  • Lead1 and
  • Lead1_control
 
The Lead1 data set contains 1559 customers. 


​This is our final target base!
 
In the next section, we will learn how to export this final target base to a text file that can be sent to the marketing team.