Content: Planned communications

Planning of future Omnichannel Communications

Overview

In this Data Type, you can define all the data about the planned communications.

This table is not an output table of your system, but a table you can use to query information to your system. It is therefore not necessary that you use (learn about) all the fields and you can first start with the simple use-case section to know how to use it.

If you want to send omnichannel communications in a specific sending system (for example a newsletter system) then you should regularly (typically every day) retrieve the information of what e-mail should be sent to whom and when from this table.

Calling the Communication Planning Feed

We describe here the format and content of the data but read https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/915505153 to learn how to operate the feed.

Field groups

This table is constituted of the following field groups:

Communication

Fields: communication_id, datetime, sending_sys_cd, message_id

These fields give information about what the communication is (identifiers and datetime of sending and for which sending system).

Thread

Fields: thread_id, thread_type, thread_name, thread_conditions, thread_conditions_or

These fields give information about the thread. This can be purely informative (to know what thread id to indicate in the communicate_history table) but if thread_conditions are defined, it means that the communication should be sent only if these conditions are fullfiled.

Quite often, you want to retrieve only communications which have the condition:
array_length(thread_conditions) = 0
This way you only get communications which can be sent at the indicated date time and without any additional check required (this is the case for most newsletter systems)

Campaign and Channel

Fields: name, channel_ids, channel_names, campaign_id, campaign_name

These fields give information about the campaign and the channel

Typically, you want to select only the communications to be sent by your sending system, which will work with a condition like:

‘MY CHANNEL ID’ in unnest(channel_names)

and ( array_length(sending_sys_cd) = 0 or ‘MY SENDING CODE’ in unnest(sending_sys_cd))

Receivers

Fields: receivers, receiver_visitor_ids

Indicates which person should receive the message

Requests

Fields: requests

Information about what requests should be sent to Boxalino Narrative API to retrieve the content of the communication through API

Attributes

Fields: string_attributes, localized_string_attributes, numeric_attributes, localized_numeric_attributes, datetime_attributes, localized_datetime_attributes

Additional attributes about the communication to be sent out.

Example

In this example, we provide a simple SQL example of how to retrieve data from this table (typically, such sql can be saved into a BigQuery view and then you can simply retrieve the data from a data-feed URL).

Here is the example where the system ‘my-newsletter’ retrieves the communications of the account ‘myaccount’ to be sent for the channel ‘newsletter’ on the ‘2022-12-01’ which have no thread conditions

SELECT 
  *
FROM `bx-bdp-53322.myaccount_views.communication_planning` 
WHERE 
  DATE(datetime) = "2022-12-01"
  AND 'newsletter' in unnest(channel_names)
  AND (array_length(sending_sys_cd) = 0 OR 'my-newsletter' in unnest(sending_sys_cd))
  AND array_length(thread_conditions) = 0

Properties

Field name	Type	Mode	Description

Field name	Type	Mode	Description
communication_id	STRING	REQUIRED	a unique generated communication UUID (PK)
datetime	TIMESTAMP	NULLABLE	the timestamp at which the communication should be sent
sending_sys_cd	STRING	REPEATED	the systems code which sent the message (e.g.: 'NEWSLETTER', 'E-SHOP', ...)
message_id	STRING	REQUIRED	a unique generated communication UUID (PK) which could be the same for different receipients
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
thread_conditions	THREAD_CONDITION	REPEATED	the conditions for the thread to be true
thread_conditions_or	BOOLEAN	NULLABLE	are the conditions and or or to be true
name	STRING	NULLABLE	the name of the campaign or communication
channel_ids	STRING	REPEATED	the ids of the communication channels (optional)
channel_names	STRING	REPEATED	the channels (name) of the communication: 'email', ...
campaign_id	STRING	NULLABLE	the id of the campaign or communication group
campaign_name	STRING	NULLABLE	the name of the campaign or communication group
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_visitor_ids	STRING	REPEATED	the visitor cookie ids from Boxalino of Google Analygtics (can be empty)
senders	CONTACT	REPEATED	information about the communication senders
subject	STRING	NULLABLE	the subject 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
requests	NARRATIVE_REQUEST	REPEATED	requests to be sent to the Boxalino Narrative API
string_attributes	MAP	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

Resources

BigQuery JSON Schema

https://github.com/boxalino/data-integration-doc-schema/blob/master/doc/communication_planning.json

BigQuery DDL

https://github.com/boxalino/data-integration-doc-schema/blob/master/ddl/communication_planning.sql

Boxalino Public Knowledge Base

communication_planning