Mail Solicitation and Response Using SQL

/***********************************************************************/
-- Mail Solicitations;
/***********************************************************************/
-- create features;
drop table ANALYTICS_STG..CAMPAIGN_ADDRESS_3;
create table ANALYTICS_STG..CAMPAIGN_ADDRESS_3 as
select a.*,
row_number() over(partition by a.EMAIL_ADDRESS_ID order by a.EMAIL_ADDRESS_ID, a.SEND_TS) seq_freq,
extract(DAY from a.SEND_TS - LAG(a.SEND_TS, 1) OVER (partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEND_TS)) as recency,
b.reach_freq as tot_freq
from ANALYTICS_STG..CAMPAIGN_ADDRESS_2 a
join (select EMAIL_ADDRESS_ID, count(*) as reach_freq from ANALYTICS_STG..LH_CAMPAIGN_ADDRESS_2 group by 1) b
on a.EMAIL_ADDRESS_ID = b.EMAIL_ADDRESS_ID;


/***********************************************************************/
/* Multiple Email Responses */
/***********************************************************************/
/* Email Promotions with EM feedback */
drop table ANALYTICS_STG..RESPONSE_ADDRESS_1;
create table ANALYTICS_STG..RESPONSE_ADDRESS_1 as
select a.CAMPAIGN_ID, a.ELECTRONIC_ADDRESS_ID, a.CONCEPT_ID, a.FEEDBACK_EVENT_CD, a.MAIL_TS,
a.FEEDBACK_EVENT_TS,
a.CUST_HHLD_ADDR_SKID,
a.CUSTOMER_ID,
a.SRCSYS_CUSTOMER_ID
from EDW_MCF_VW..CAMPAIGN_FEEDBACK_EM a
inner join (select CAMPAIGN_ID, EMAIL_ADDRESS_ID, count(*) from ANALYTICS_STG..LH_CAMPAIGN_ADDRESS_3 group by 1,2) b
on a.CAMPAIGN_ID = b.CAMPAIGN_ID
and a.ELECTRONIC_ADDRESS_ID = b.EMAIL_ADDRESS_ID
where a.MAIL_TS between '2012-02-18' and '2014-03-08'
and a.FEEDBACK_EVENT_TS between '2012-02-18' and '2014-03-08'
;

-- dedupe email feedback;
drop table ANALYTICS_STG..RESPONSE_ADDRESS_2;
create table ANALYTICS_STG..RESPONSE_ADDRESS_2 as
select a.*,
case when upper(a.FEEDBACK_EVENT_CD)='OPEN' then 1 else 0 end FEEDBACK_EVENT_CD_1,
case when upper(a.FEEDBACK_EVENT_CD)='CLICK' then 1 else 0 end FEEDBACK_EVENT_CD_2,
case when upper(a.FEEDBACK_EVENT_CD)='UNDEL' then 1 else 0 end FEEDBACK_EVENT_CD_3,
case when upper(a.FEEDBACK_EVENT_CD)='CONVERSION' then 1 else 0 end FEEDBACK_EVENT_CD_4,
case when upper(a.FEEDBACK_EVENT_CD)='UNSUB' then 1 else 0 end FEEDBACK_EVENT_CD_5,
case when upper(a.FEEDBACK_EVENT_CD)='SPAM' then 1 else 0 end FEEDBACK_EVENT_CD_6
from (
select *, row_number()
over (partition by CAMPAIGN_ID, ELECTRONIC_ADDRESS_ID, CONCEPT_ID, FEEDBACK_EVENT_CD, MAIL_TS
order by  CAMPAIGN_ID, ELECTRONIC_ADDRESS_ID, CONCEPT_ID, FEEDBACK_EVENT_CD, MAIL_TS, FEEDBACK_EVENT_TS
) row_id
from ANALYTICS_STG..RESPONSE_ADDRESS_1
) a
where a.row_id = 1;


/* 2.2.1 drop non-open, non-click, non-unsubscribe */
drop table ANALYTICS_STG..RESPONSE_ADDRESS_3;
create table ANALYTICS_STG..RESPONSE_ADDRESS_3 as
select a.*,
row_number() over (partition by a.ELECTRONIC_ADDRESS_ID order by a.ELECTRONIC_ADDRESS_ID, a.MAIL_TS, a.FEEDBACK_EVENT_TS) seq_engagement,
b.tot_engagement,
b.tot_open,
b.tot_click,
b.tot_unsub,
extract(DAY from (a.FEEDBACK_EVENT_TS - a.MAIL_TS)) as feedback_day
from ANALYTICS_STG..LH_RESPONSE_ADDRESS_2 a
inner join (select ELECTRONIC_ADDRESS_ID,
count(*) as tot_engagement,
sum(FEEDBACK_EVENT_CD_1) as tot_open,
sum(FEEDBACK_EVENT_CD_2) as tot_click,
sum(FEEDBACK_EVENT_CD_5) as tot_unsub
from ANALYTICS_STG..LH_RESPONSE_ADDRESS_2
where FEEDBACK_EVENT_CD_1 = 1 or FEEDBACK_EVENT_CD_2 = 1 or FEEDBACK_EVENT_CD_5 = 1
group by 1
) b
on a.ELECTRONIC_ADDRESS_ID = b.ELECTRONIC_ADDRESS_ID
where a.FEEDBACK_EVENT_CD_1 = 1 or a.FEEDBACK_EVENT_CD_2 = 1 or a.FEEDBACK_EVENT_CD_5 = 1;

/***********************************************************************/
/* Responses with email promotion; */
/***********************************************************************/
create table ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_1 as
select a.*,
b.FEEDBACK_EVENT_TS,
b.FEEDBACK_EVENT_CD,
b.FEEDBACK_EVENT_CD_1,
b.FEEDBACK_EVENT_CD_2,
b.FEEDBACK_EVENT_CD_5,
b.SEQ_ENGAGEMENT,
b.TOT_CLICK,
b.TOT_OPEN,
b.TOT_ENGAGEMENT,
b.TOT_UNSUB,
b.FEEDBACK_DAY
from  ANALYTICS_STG..CAMPAIGN_ADDRESS_3 a
inner join ANALYTICS_STG..RESPONSE_ADDRESS_3 b
on a.CAMPAIGN_ID = b.CAMPAIGN_ID
and a.CONCEPT_ID = b.CONCEPT_ID
and a.EMAIL_ADDRESS_ID = b.ELECTRONIC_ADDRESS_ID
and a.SEND_TS = b.MAIL_TS
;


/*2.2.2 Attributed Solicitation/Promotion  and Response*/
drop table ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_2;
-- 2,147,483,647 rows affected;
create table ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_2 as
select a.*,
b.FEEDBACK_EVENT_TS,
b.FEEDBACK_EVENT_CD,
b.FEEDBACK_EVENT_CD_1,
b.FEEDBACK_EVENT_CD_2,
b.FEEDBACK_EVENT_CD_5,
b.TOT_ENGAGEMENT,
b.SEQ_ENGAGEMENT,
b.TOT_CLICK,
b.TOT_OPEN,
b.TOT_UNSUB,
b.FEEDBACK_DAY,
SUM(case when b.FEEDBACK_EVENT_CD_1=1 then 1 else 0 end) OVER(partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEQ_FREQ, b.SEQ_ENGAGEMENT rows unbounded preceding)/a.SEQ_FREQ CUM_OPEN_PERC,
SUM(case when b.FEEDBACK_EVENT_CD_2=1 then 1 else 0 end) OVER(partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEQ_FREQ, b.SEQ_ENGAGEMENT rows unbounded preceding)/a.SEQ_FREQ CUM_CLICK_PERC,
SUM(case when b.FEEDBACK_EVENT_CD_5=1 then 1 else 0 end) OVER(partition by a.EMAIL_ADDRESS_ID ORDER BY a.EMAIL_ADDRESS_ID, a.SEQ_FREQ, b.SEQ_ENGAGEMENT rows unbounded preceding)/a.SEQ_FREQ CUM_UNSUB_PERC
from ANALYTICS_STG..CAMPAIGN_ADDRESS_3 a
left join ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_1 b
on a.CAMPAIGN_ID=b.CAMPAIGN_ID
and a.CONCEPT_ID=b.CONCEPT_ID
and a.EMAIL_ADDRESS_ID = b.EMAIL_ADDRESS_ID
and a.SEQ_FREQ = b.SEQ_FREQ
;

-- Sequencial Frequency
select SEQ_FREQ,
NTILE(20) OVER(ORDER BY SEQ_FREQ DESC NULLS LAST) as SEQ_FREQ_DECILE,
count(distinct EMAIL_ADDRESS_ID) cnt,
sum(FEEDBACK_EVENT_CD_1) open,
sum(FEEDBACK_EVENT_CD_2) click,
sum(FEEDBACK_EVENT_CD_5) unsubscrib
from ANALYTICS_STG..CAMPAIGN_ADDRESS_RESPONSE_2
group by 1
order by 1;


select aa.seq_freq,
aa.cnt,
SUM(aa.cnt) OVER(ORDER BY aa.SEQ_FREQ rows unbounded preceding) cum_email,
aa.OPEN,
SUM(aa.open) OVER(ORDER BY aa.SEQ_FREQ rows unbounded preceding) cum_open,
aa.CLICK,
SUM(aa.click) OVER(ORDER BY aa.SEQ_FREQ rows unbounded preceding) cum_click,
aa.UNSUBSCRIB,
SUM(aa.unsubscrib) OVER(ORDER BY aa.SEQ_FREQ rows unbounded preceding) cum_unsubscrib
from lh_tmp aa
order by aa.SEQ_FREQ;