Column name | Column definition | Business rule |
|---|
email_id
| Uniquely identifies the email address record. | Email ID of distinct email from EMAIL. |
undeliverable_ind
| When Y, email address is determined to be undeliverable based on the rules. | (Placeholder until Email QOS FL implemented.) |
valid_email_ind
| When Y, email address is determined to be valid based on the rules. | (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. | 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). | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | 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. | From EMAIL.original_create_datetime.
|
original_source_id
| Uniquely identifies the original source system for the email record to be created in RPG db. | From EMAIL.original_create_source_code. |
