As mentioned previously, your manager has asked you to explore the segment below for this cosmetic promotion:
Let’s first identify customers who are women below 30 years of age.
The gender and age of customers are available in the Customer_base data set.
proc sql;
create table cust_inc40K_age_b30 as
select custno, gender, income
from customer_base
where gender = 0 and income > 40000 and age < 30;
quit;
The code above is a simple Proc sql step that selects customers from the Customer_base data set.
It selects only the women (i.e. Gender = 0) below the age of 30 with an income greater than $40,000.
It captures only the Custno, Gender and Income variables:
It creates a data set called Cust_Inc40K_age_b30.
There are 1934 customers who are women below the age of 30 with an income greater than $40,000.
Now, let’s look at how many of these customers made at least one purchase in the cosmetic category in January, 2020.
Wait a second.
How do we identify which transactions belong to the cosmetic category?
Credit card companies use what is known as the merchant category code (MCC) to identify the purchase category.
Below is an example:
The merchant category code is a 4-digit code that classifies the products or services a retail company offers.
For example, a transaction at a fast food restaurant will be tagged with the MCC code 5814.
A book/newspaper purchase will be tagged with the MCC code 5192.
You can visit Wikipedia for more details about MCC codes.
For the full list of MCC codes, you can visit here.
Now, let's open the MCC code list and search for the word "Cosmetic".
You will find that the MCC code for cosmetic stores is 5977.
We will use the MCC code 5977 to identify transactions that belong to the cosmetic category:
proc sql;
create table tran_5977 as
select distinct custno, MCC
from transaction_table_202001
where MCC = '5977';
quit;
The Tran_5977 data set contains the customers who made a purchase in the cosmetic category in January 2020.
Let's do a quick summary. We currently have two tables created:
(1) Cust_Inc40K_age_b30
This table contains customers who are:
(2) Tran_5977
This table contains customers who have made at least one purchase in the cosmetic category in January, 2020.
The customers that are common in both data sets will be meeting all of our targeting criteria.
They will be our target base!
Let's combine the two data sets:
proc sql;
create table target_base1 as
select a.*, b.MCC
from cust_inc40K_age_b30 a inner join tran_5977 b
on a.custno = b.custno;
quit;
The code above "inner joins" the Cust_Inc40K_age_b30 table and the Tran_5977 table.
This captures only the customers who are present in both tables.
Let’s take a quick look at our final target base:
There are only 123 customers that meet the selection criteria!
That's way too low for our marketing plan.
In the next section, we will learn how to get a larger size across the different age groups.
