Hi all, I am working with email campaign data and I am trying to narrow down all activities that occurred only within 14 days AFTER the email send date. TO QC my data, I noticed the numbers being to high for a specific campaign, so I've narrowed down to look just at that specific campaign which was sent on 02/01/18 I originally have the INTCK function in the "ON" section for the join, but I moved it to "WHERE" since this sped up the processing time. However, the numbers remain the same and as you can see, I'm still getting date values in the activity_date field that are more than 14 days after the send date (2/1). You can see the output in the attached pic. Here's the code for reference. Can someone tell me if the see an error in how I am using INTCK? proc sql;
create table tableau.test as
select a.person_id,
a.execution_id,
a.campaign_id,
datepart(a.campaign_send_datetime) as send_date format= mmddyy8.,
datepart(b.activity_date) as activity_date format= mmddyy8.,
max(case when activity_type_dim_id = 5 then 1 else 0 end) as opened,
max(case when activity_type_dim_id = 2 then 1 else 0 end) as clicked,
max(case when activity_type_dim_id in (10,11,14) then 1 else 0 end) as bounced
from ldmprod.vw_email_campaign_history as a
left join ldmprod.vw_email_campaign_activity as b
on a.execution_id = b.execution_id and
a.person_id = b.person_id
where datepart(a.campaign_send_datetime) >= '01JAN2018'd
and intck('day', b.activity_date, a.campaign_send_datetime) <= 14
and upcase(a.receipient_id) not like '%SEED%'
and upcase(a.receipient_id) not like '%SD%'
and a.campaign_id like 'M00884004904%'
and (substr (a.campaign_id_id, 1, 3) = 'M00'
or substr (a.campaign_id, 1, 3) = 'O00')
group by 1,2,3,4
;
quit;
... View more
