event

n:1 relation with sending and every other group

describe what the communication history event is about (type of event (sent, open click, …), time of the event, …)

event_id, event_type, event_category, datetime

sending
connected to one and only one channel, communication, message, thread and campaign

related to when a communication was sent to a particular receiver (this is about when your system actually sent a message out)

sending_id, sending_sys_cd, run_id, external_id

channel
1:n relation with the sending

describe what is the channel of the sending

channel_id, channel_name

communication

1:n relation with sending

describe a message communicated with (a) specific receiver(s), if you send a message several times (possibly in different channels) to the same receiver(s) the communication id will be the same while the sending id will not

communication_id

message
1:n relation with communication

describe a message which could be send to several people (communications) and possibly several times (sendings)

message_id, message_name

thread
1:n relation with either a message or a communication

communication branch starting from a prior message (if not unique to (a) specific receiver(s)) or communication, then the branch will be ordered by event datetime of ‘sent’ event_type

thread_id, thread_type, thread_name

campaign
1:n relation with message

describe a campaign which is a group of 1 or several messages

campaign_id, campaign_name

receiver_type

+ (if applicable)
receiver_email, receiver_visitor_ids

+ (optionally)
receiver_id, receiver_title, receiver_firstname, receiver_lastname, receiver_attribute_[1-5]_name, receiver_attribute_[1-5]_value

receiver_type

+ (if applicable)
receiver_email, receiver_visitor_ids

+ (optionaly)
receiver_id, receivers

event_id

STRING

NULLABLE

a unique communication event id (PK), optional (mainly diagnostic / debugging / maintenance)

event_type

STRING

REQUIRED

the type of event, is one of the following values: 'sent', 'bounce', 'open', 'click', ...

event_category

STRING

REQUIRED

the category of event, is one of the following values: 'inbound', 'outbound', ...

datetime

TIMESTAMP

NULLABLE

the timestamp of the event

sending_id

STRING

REQUIRED

the id of the sending of the communication (unique per sending and per communication, but can have several events, like sent, open, click, the sending id must be generated by the channel as a unique identifier of each sent event...

sending_sys_cd

STRING

REQUIRED

the system code which sent the message (e.g.: 'NEWSLETTER', 'E-SHOP', ...)

channel_id

STRING

NULLABLE

the id of the communication channel (optional)

channel_name

STRING

REQUIRED

the channel (name) of the communication: 'email', ...

run_id

STRING

NULLABLE

the id of the batch execution (can cover several sending ids) (optional)

communication_id

STRING

REQUIRED

a unique communication id should be unique per message sent to a specific person and should match the communication_id of the communication_planning table

message_id

STRING

REQUIRED

the id of the communication (unique per communication, but could be applied to many receipients, ...)

message_name

STRING

NULLABLE

the name of the communication

thread_id

STRING

NULLABLE

the id of the communication thread (should refer to a prior communication_id or message_id which is the root of the thread)

thread_type

STRING

NULLABLE

the type of the communication thread (typically 'message' of 'communication', by default is is considered as a 'communication' thread)

thread_name

STRING

NULLABLE

the name of the communication thread

campaign_id

STRING

NULLABLE

the id of the campaign or communication group

campaign_name

STRING

NULLABLE

the name of the campaign or communication group

external_id

STRING

NULLABLE

an external id if provided by a separated system (for example, the request_id if provided by boxalino narrative response)

receiver_id

STRING

NULLABLE

the persona_id as referenced in other tables

receiver_type

STRING

NULLABLE

the type of persona (user, guest, ...)

receiver_title

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

receiver_firstname

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

receiver_lastname

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

receiver_email

STRING

NULLABLE

the email address (can be null)

receiver_visitor_ids

STRING

REPEATED

the visitor cookie ids from Boxalino of Google Analygtics (can be empty)

receivers

CONTACT

REPEATED

typically not use in case the receive is only 1 persona defined by its persona_id, but available for other cases

receiver_attribute_1_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_1_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_2_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_2_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_3_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_3_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_4_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_4_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_5_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

receiver_attribute_5_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure receivers

senders

CONTACT

REPEATED

information about the communication senders

sender_id

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_type

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_title

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_firstname

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_lastname

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_email

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_1_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_1_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_2_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_2_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_3_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_3_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_4_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_4_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_5_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

sender_attribute_5_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure senders

subject

STRING

NULLABLE

the subject line of the communication

contents

COMMUNICATION_CONTENT

REPEATED

information about the content of the communication, typically only provided for the 'sent' event, as the other events should refer to the same message_id and persona_id / email so no need to indicate the content every time, only for sent

content_id

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_id_field

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_language

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_title

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_short_description

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_description

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_1_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_1_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_2_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_2_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_3_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_3_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_4_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_4_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_5_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

content_attribute_5_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure contents

string_attributes

RECORD

REPEATED

additional string (not localized) attributes of the content

localized_string_attributes

MAP

REPEATED

additional localized string attributes of the content

numeric_attributes

MAP

REPEATED

additional numeric (not localized) attributes of the content

localized_numeric_attributes

MAP

REPEATED

additional localized numeric attributes of the content

datetime_attributes

MAP

REPEATED

additional datetime (not localized) attributes of the content

localized_datetime_attributes

MAP

REPEATED

additional localized datetime attributes of the content

attribute_1_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_1_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_2_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_2_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_3_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_3_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_4_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_4_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_5_name

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

attribute_5_value

STRING

NULLABLE

use only if providing data in flat format (like CSV) if you skip the nested structure attributes

creation_tm

DATETIME

REQUIRED

technical field

client_id

INTEGER

REQUIRED

technical field

src_sys_id

INTEGER

REQUIRED

technical field