Customer Onboarding

Step #	Description	Salesforce Marketing Cloud
1	Complete the SQL Mapping Worsksheet. Note: For details on each data field and the required format, please see the Data Fields section below this table.	Customer Onboarding 29.00 KB
2	Open Journey Builder > Automation Studio.
3	Select the Activities tab and open the list of SQL Queries.
4	Select the Relay Customer Onboarding SQL Query and select the edit icon (pencil).
5	Edit the SQL Activity shell. All tablename.fieldname entries must be updated (or removed) based on your entries in the mapping worksheet. The Test table name must be replaced with the actual table name where the data will be pulled from. (If more than one table is used to obtain data, a properly coded SQL 'join' statement can be used.) All input_ parameters required for the trigger_id must be mapped. Add additional mappings for each input_ parameter to support the requirements of the experience.
6	Click through to save the updated query. Schedule the automation.

Step #

Description

Salesforce Marketing Cloud

Complete the SQL Mapping Worsksheet.
Note: For details on each data field and the required format, please see the Data Fields section below this table.

Customer Onboarding

29.00 KB

Open Journey Builder > Automation Studio.

Select the Activities tab and open the list of SQL Queries.

Select the Relay Customer Onboarding SQL Query and select the edit icon (pencil).

Edit the SQL Activity shell.
- All tablename.fieldname entries must be updated (or removed) based on your entries in the mapping worksheet.
- The Test table name must be replaced with the actual table name where the data will be pulled from. (If more than one table is used to obtain data, a properly coded SQL 'join' statement can be used.)
- All input_ parameters required for the trigger_id must be mapped. Add additional mappings for each input_ parameter to support the requirements of the experience.

Click through to save the updated query.
Schedule the automation.

Field Name	Data Type	Notes
CCID	Text (100)	Primary Key. Required. Uniquely identifies a Client’s Customer
PHONE_NUMBER	Text (50)	Primary Key. Required. The phone number associated with the Customer. Example: “2155551234”
PRODUCT_GROUP	Text (100)	Required. The product group Id assigned to the Customer. A Customer can only be assigned to a single product group.
CONSENT_TYPE	Text (100)	Required. The consent type for the Phone_Number. Valid values: “express” and “written”
TRIGGER_ID	Text (100)	Optional. Experience trigger_id. If specified, the onboarded Customer will be sent the experience associated with the specified trigger_id.
DEACTIVATE_PREVIOUS_CONNECTION	Boolean	Optional. Specifies if previous connections (mobile numbers) should be removed. Valid values are: true - if ccid has any associated phone numbers, remove all phone numbers associated prior to establishing a new connection. false - ignore if ccid has any associated phone numbers, add the new phone number to the list of associated phone numbers. If no value is provided, this defaults to false.
SECONDARY_ACCOUNT_ID	Text (100)	Optional. Alternate account number used for validation when CCID contains characters or symbols OR CCID is unknown to Customers.
FIRST_NAME	Text (100)	Optional. The first name of the Customer
MIDDLE_NAME	Text (100)	Optional. The middle name of the Customer
LAST_NAME	Text (100)	Optional. The last name of the Customer
PHONE_TYPE	Text (10)	Optional. Identifies the type of phone number provided. Must be one of the following values: mobile - The phone number provided is a mobile number landline - The phone number provided is a landline number unknown - it is unknown if the phone number provided is a landline or a mobile number.
COUNTRY_CODE	Text (3)	Optional. The country code associated with the Customer phone number. If not provided, defaults to 1.
EMAIL_ADDRESS	Email Address	Optional. The email address of the Customer.
ADDRESS1	Text (100)	Optional. Identifies the first line of the Customer address
ADDRESS2	Text (100)	Optional. Identifies the second line of the Customer address.
CITY	Text (100)	Optional. Identifies city of the Customer address.
STATE_PROVINCE	Text (2)	Optional. Identifies state or province of the Customer address
POSTAL_CODE	Text (10)	Optional. Identifies postal code of the Customer address
COUNTRY	Text (2)	Optional. Identifies country of the Customer address.
DATE_OF_BIRTH	date	Optional. Identifies the date of birth of the Customer. Format: yyyy-mm-dd
SSN_LAST4	Text (4)	Optional. Contains the last 4 digits of the Customer social security number.
GENDER	Text (1)	Optional. Identifies the gender of the Customer.
CLIENT_MESSAGE_TAG	Text (100)	Optional. This is an arbitrary passthrough property specified by the Client for reporting and tracking by the Client.
LB_NAME	Text (255)	Optional. Client defined property to store the Launched_by Name record. Identifies the individual who launched a certain experience.
LB_SOURCE	Text (255)	Optional. Client defined property to store the Launched_by Source record. Identifies the system where an experience was launched.
EXT_XXX	Text (100)	Optional. Client defined, extensible properties to be stored with the Customer record. Each extensible property must be prefixed with EXT_. Custom EXT_ properties are provided by the Client Zero or more EXT_ properties can be provided Different EXT_ properties can be provided for each Customer The maximum length of each EXT_ property is 100 characters
INPUT_XXX	Text (100)	Optional input parameters to the message. Each input parameter is prefixed with INPUT_ followed by the input parameter name. Custom input parameters are provided by the Client Zero or more INPUT_ parameters can be specified Different INPUT_ parameters can be specified on each row in the file The maximum length of each INPUT_ parameter is 100 characters

Field Name

Data Type

Notes

CCID

Text (100)

Primary Key. Required. Uniquely identifies a Client’s Customer

PHONE_NUMBER

Text (50)

Primary Key. Required. The phone number associated with the Customer. Example: “2155551234”

PRODUCT_GROUP

Text (100)

Required. The product group Id assigned to the Customer. A Customer can only be assigned to a single product group.

CONSENT_TYPE

Text (100)

Required. The consent type for the Phone_Number. Valid values: “express” and “written”

TRIGGER_ID

Text (100)

Optional. Experience trigger_id. If specified, the onboarded Customer will be sent the experience associated with the specified trigger_id.

DEACTIVATE_PREVIOUS_CONNECTION

Boolean

Optional. Specifies if previous connections (mobile numbers) should be removed. Valid values are: true - if ccid has any associated phone numbers, remove all phone numbers associated prior to establishing a new connection. false - ignore if ccid has any associated phone numbers, add the new phone number to the list of associated phone numbers. If no value is provided, this defaults to false.

SECONDARY_ACCOUNT_ID

Text (100)

Optional. Alternate account number used for validation when CCID contains characters or symbols OR CCID is unknown to Customers.

FIRST_NAME

Text (100)

Optional. The first name of the Customer

MIDDLE_NAME

Text (100)

Optional. The middle name of the Customer

LAST_NAME

Text (100)

Optional. The last name of the Customer

PHONE_TYPE

Text (10)

Optional. Identifies the type of phone number provided. Must be one of the following values: mobile - The phone number provided is a mobile number landline - The phone number provided is a landline number unknown - it is unknown if the phone number provided is a landline or a mobile number.

COUNTRY_CODE

Text (3)

Optional. The country code associated with the Customer phone number. If not provided, defaults to 1.

EMAIL_ADDRESS

Email Address

Optional. The email address of the Customer.

ADDRESS1

Text (100)

Optional. Identifies the first line of the Customer address

ADDRESS2

Text (100)

Optional. Identifies the second line of the Customer address.

CITY

Text (100)

Optional. Identifies city of the Customer address.

STATE_PROVINCE

Text (2)

Optional. Identifies state or province of the Customer address

POSTAL_CODE

Text (10)

Optional. Identifies postal code of the Customer address

COUNTRY

Text (2)

Optional. Identifies country of the Customer address.

DATE_OF_BIRTH

date

Optional. Identifies the date of birth of the Customer. Format: yyyy-mm-dd

SSN_LAST4

Text (4)

Optional. Contains the last 4 digits of the Customer social security number.

GENDER

Text (1)

Optional. Identifies the gender of the Customer.

CLIENT_MESSAGE_TAG

Text (100)

Optional. This is an arbitrary passthrough property specified by the Client for reporting and tracking by the Client.

LB_NAME

Text (255)

Optional. Client defined property to store the Launched_by Name record. Identifies the individual who launched a certain experience.

LB_SOURCE

Text (255)

Optional. Client defined property to store the Launched_by Source record. Identifies the system where an experience was launched.

EXT_XXX

Text (100)

Optional. Client defined, extensible properties to be stored with the Customer record. Each extensible property must be prefixed with EXT_. Custom EXT_ properties are provided by the Client Zero or more EXT_ properties can be provided Different EXT_ properties can be provided for each Customer The maximum length of each EXT_ property is 100 characters

INPUT_XXX

Text (100)

Optional input parameters to the message. Each input parameter is prefixed with INPUT_ followed by the input parameter name. Custom input parameters are provided by the Client Zero or more INPUT_ parameters can be specified Different INPUT_ parameters can be specified on each row in the file The maximum length of each INPUT_ parameter is 100 characters

Customer Onboarding

Setting Up the Automation

Data Fields

What's Next