Column name | Column definition | Data type | Business rule |
|---|
email_id
| Uniquely identifies the email address record. | BIGINT
| Email ID of distinct email from EMAIL. |
undeliverable_ind
| When Y, email address is determined to be undeliverable based on the rules. | NVARCHAR(1)
| (Placeholder until Email QOS FL implemented) |
valid_email_ind
| When Y, email address is determined to be valid based on the rules. | NVARCHAR(1)
| (Placeholder until Email QOS FL implemented) |
active_email_ind
| When Y, active email is defined as one with a transaction or opens/clicks in the past 1095 days. | NVARCHAR(1)
| Set this column to Y when either: Count (*) of records from RESPONSE_EVENT where RESPONSE_EVENT.event_disposition_code in (opened, clicked) and response_datetime is within the last 1095 days is > 0 for this email_id.
OR This email_id exists in TRANSACTION_DETAIL_SUMMARY.bill_to_email_id where transaction_datetime is within the last 1095 days.
|
email_domain
| An email domain is the web address that comes after the @ symbol in an email address that is identified by email_id (e.g., gmail.com). | NVARCHAR(255)
| Select email_domain from ir.EMAIL where EMAIL.email_id = EMAIL_SUMMARY.email_id. |
first_open_datetime
| The first date when email promotion that was sent to this email address has been opened. | DATETIME
| Min (RESPONSE_EVENT.response_datetime) for this email_id and event_disposition_code = opened. |
last_open_datetime
| The latest date when email promotion that was sent to this email address has been opened. | DATETIME
| Max (RESPONSE_EVENT.response_datetime) for this email_id and event_disposition_code = opened. |
first_click_thru_datetime
| The first date when a click-through event occurred as a result of promotion sent to this email address. | DATETIME
| Min (RESPONSE_EVENT.response_datetime) for this email_id and event_disposition_code = clicked. |
last_click_thru_datetime
| The latest date when a click-through event occurred as a result of promotion sent to this email address. | DATETIME
| Max (RESPONSE_EVENT.response_datetime) for this email_id and event_disposition_code = clicked. |
first_sent_datetime
| The first date when email promotion was sent to this email address. | DATETIME
| Min (CAMPAIGN_EVENT_CUSTOMER_MAP.event_datetime) for this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
last_sent_datetime
| The latest date when email promotion was sent to this email address. | DATETIME
| Max (CAMPAIGN_EVENT_CUSTOMER_MAP.event_datetime) for this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
first_bounce_datetime
| The first date when email promotion that was sent to this email address has been bounced. | DATETIME
| Min (RESPONSE_EVENT.response_datetime) for this email_id and event_disposition_code = bounced. |
last_bounce_datetime
| The latest date when email promotion that was sent to this email address has been bounced. | DATETIME
| Max (RESPONSE_EVENT.response_datetime) for this email_id and event_disposition_code = bounced. |
email_click_thru_past_30_days_count
| Number of click-throughs generated by this email address during past 30 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = clicked and RESPONSE_EVENT.response_datetime is within the last 30 days. |
email_click_thru_past_90_days_count
| Number of click-throughs generated by this email address during past 90 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = clicked and RESPONSE_EVENT.response_datetime is within the last 90 days. |
email_click_thru_past_180_days_count
| Number of click-throughs generated by this email address during past 180 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = clicked and RESPONSE_EVENT.response_datetime is within the last 180 days. |
email_click_thru_past_365_days_count
| Number of click-throughs generated by this email address during past 365 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = clicked and RESPONSE_EVENT.response_datetime is within the last 365 days. |
email_open_past_30_days_count
| Number of emails opened during past 30 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = opened and RESPONSE_EVENT.response_datetime is within the last 30 days. |
email_open_past_90_days_count
| Number of emails opened during past 90 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = opened and RESPONSE_EVENT.response_datetime is within the last 90 days. |
email_open_past_180_days_count
| Number of emails opened during past 180 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = opened and RESPONSE_EVENT.response_datetime is within the last 180 days. |
email_open_past_365_days_count
| Number of emails opened during past 365 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = opened and RESPONSE_EVENT.response_datetime is within the last 365 days. |
email_bounce_past_30_days_count
| Number of emails bounced during past 30 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = bounced and RESPONSE_EVENT.response_datetime is within the last 30 days. |
email_bounce_past_90_days_count
| Number of emails bounced during past 90 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = bounced and RESPONSE_EVENT.response_datetime is within the last 90 days. |
email_bounce_past_180_days_count
| Number of emails bounced during past 180 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = bounced and RESPONSE_EVENT.response_datetime is within the last 180 days. |
email_bounce_past_365_days_count
| Number of emails bounced during past 365 days. | INT
| Count (*) records from RESPONSE_EVENT for this email_id and RESPONSE_EVENT.event_disposition_code = bounced and RESPONSE_EVENT.response_datetime is within the last 365 days. |
email_sent_past_7_days_count
| Number of emails sent to this email address during past 7 days. | INT
| Count (*) records from CAMPAIGN_EVENT_CUSTOMER_MAP where event_datetime is within the last 7 days for this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
email_sent_past_14_days_count
| Number of emails sent to this email address during past 14 days. | INT
| Count (*) records from CAMPAIGN_EVENT_CUSTOMER_MAP where event_datetime is within the last 14 days for this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
email_sent_past_30_days_count
| Number of emails sent to this email address during past 30 days. | INT
| Count (*) records from CAMPAIGN_EVENT_CUSTOMER_MAP where event_datetime is within the last 30 days this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
email_sent_past_90_days_count
| Number of emails sent to this email address during past 90 days. | INT
| Count (*) records from CAMPAIGN_EVENT_CUSTOMER_MAP where event_datetime is within the last 90 days this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
email_sent_past_180_days_count
| Number of emails sent to this email address during past 180 days. | INT
| Count (*) records from CAMPAIGN_EVENT_CUSTOMER_MAP where event_datetime is within the last 180 days this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
email_sent_past_365_days_count
| Number of emails sent to this email address during past 365 days. | INT
| Count (*) records from CAMPAIGN_EVENT_CUSTOMER_MAP where event_datetime is within the last 365 days this email_id and RESPONSE_EVENT.event_disposition_code = delivered. |
original_create_datetime
| The datetime of when the email was recorded in the database for the very first time. | DATETIME
| From EMAIL.original_create_datetime.
|
original_source_id
| Uniquely identifies the original source system for the email record to be created in RPG db. | NVARCHAR(100)
| From EMAIL.original_create_source_code. |
