Email summary

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.