V_QUERY_BBDW_EMAILACTIVITY
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| EMAILACTIVITYFACTID | bigint | ||
| CONSTITUENTDIMID | int | ||
| EMAILSENDDATE | datetime | ||
| EMAILSENDDATEDIMID | int | ||
| RECIPIENTOPEN | bit | ||
| RECIPIENTOPENDATE | datetime | yes | |
| RECIPIENTOPENDATEDIMID | int | yes | |
| RECIPIENTCLICK | bit | ||
| RECIPIENTCLICKDATE | datetime | yes | |
| RECIPIENTCLICKDATEDIMID | int | yes | |
| RECIPIENTACTION | bit | yes | |
| RECIPIENTACTIONDATE | datetime | yes | |
| RECIPIENTACTIONDATEDIMID | int | yes | |
| RECIPIENTHARDBOUNCE | bit | yes | |
| RECIPIENTSOFTBOUNCE | bit | yes | |
| RECIPIENTUNSUBSCRIBE | bit | yes | |
| FORWARDEDOPENCOUNT | int | yes | |
| FORWARDEDACTIONCOUNT | int | yes | |
| TOTALCLICKCOUNT | int | yes | |
| TOTALOPENCOUNT | int | yes | |
| EMAILMESSAGENAME | nvarchar(255) | yes | |
| EMAILMESSAGEPURPOSE | nvarchar(255) | yes | |
| EMAILMESSAGESENDERNAME | nvarchar(255) | yes | |
| EMAILMESSAGESUBJECT | nvarchar(255) | yes | |
| EMAILCAMPAIGNNAME | nvarchar(255) | yes | |
| EMAILCAMPAIGNPURPOSE | nvarchar(255) | yes | |
| EMAILCAMPAIGNTYPE | nvarchar(64) | yes |
Definition
Copy
create view BBDW.[V_QUERY_BBDW_EMAILACTIVITY] as
select
er.[EMAILACTIVITYFACTID],
er.[CONSTITUENTDIMID],
er.[EMAILSENDDATE],
er.[EMAILSENDDATEDIMID],
er.[RECIPIENTOPEN],
er.[RECIPIENTOPENTIME] as [RECIPIENTOPENDATE],
er.[RECIPIENTOPENDATEDIMID],
er.[RECIPIENTCLICK],
er.[RECIPIENTCLICKTIME] as [RECIPIENTCLICKDATE],
er.[RECIPIENTCLICKDATEDIMID],
er.[RECIPIENTACTION],
er.[RECIPIENTACTIONTIME] as [RECIPIENTACTIONDATE],
er.[RECIPIENTACTIONDATEDIMID],
er.[RECIPIENTHARDBOUNCE],
er.[RECIPIENTSOFTBOUNCE],
er.[RECIPIENTUNSUBSCRIBE],
er.[FORWARDEDOPENCOUNT],
er.[FORWARDEDACTIONCOUNT],
er.[TOTALCLICKCOUNT],
er.[TOTALOPENCOUNT],
em.[EMAILMESSAGENAME],
em.[EMAILMESSAGEPURPOSE],
em.[EMAILMESSAGESENDERNAME],
em.[EMAILMESSAGESUBJECT],
ec.[EMAILCAMPAIGNNAME],
ec.[EMAILCAMPAIGNPURPOSE],
ec.[EMAILCAMPAIGNTYPE]
from BBDW.[FACT_EMAILACTIVITY] er
left join BBDW.[DIM_EMAILMESSAGE] em on er.[EMAILMESSAGEDIMID] = em.[EMAILMESSAGEDIMID] and er.[EMAILMESSAGEDIMID] <> 0
left join BBDW.[DIM_EMAILCAMPAIGN] ec on em.[EMAILCAMPAIGNDIMID] = ec.[EMAILCAMPAIGNDIMID] and em.[EMAILCAMPAIGNDIMID] <> 0