C Marketing Batch Sync Processing
This appendix provides information on batch sync processing with a Marketing system such as Oracle Responsys, including file layouts and background on configuration and scheduling.
The content available for export includes award accounts and coupons, customers, loyalty accounts, promotions, and segments.
These files are delimited text files and each includes a header row.
The sync options can be modified through the use of System Configuration settings. This appendix describes the default file formats and contents, which are compatible with Responsys and its Interact platform.
If the Delete Files After Copy property is set to Yes, the Marketing batch sync processing files are deleted from the application server after they are moved to the remote server.
See the Customer Engagement Cloud Services Implementation Guide for more information about System Configuration, including the related settings described in the Batch Exporter Properties chapter.
Note:
Customer Engagement does not send the Marketing system requests to purge customer or user data. These requests should go directly to the Marketing system.
Award Sync Export
This section includes:
Award Account Export Sync File Format for Marketing
The Award Account Export Sync file can include all active records, or only records that have been created or updated since the last time the job was run. Only award accounts that are associated with a customer are included in the file. If a customer is associated with an inactive or expired card, it is included in the file.
The file name defaults from the Responsys Award Account Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix.
The default static filename setting is _AwardAccountsSync.
The Award Account Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.This information is primarily found in the ACT_CARD, ACT_ACCOUNT, and ACT_PROGRAM tables.
Table C-1 Award Account Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer. |
|
CARD_NUMBER |
Varchar |
64 |
Unique card number. From the CARD_NUM in the ACT_CARD table. |
|
CARD_ACTIVE_FLAG |
Number |
3 |
Card active flag (set to 1 if active; if not active, indicates a deactivation or merge). From the ACTIVE_FLAG in the ACT_CARD table. |
|
CARD_EXPIRATION_DATE |
Date (YYYY-MM-DD) |
The date when the card expires. From the EXPIRY_DATE in the ACT_CARD table. |
|
|
FIRST_USED_DATE Date |
Date (YYYY-MM-DD) |
The date when the card was first used. From the INITIAL_ACTIVITY_DATE in the ACT_ACCOUNT table. |
|
|
LAST_USED_DATE |
Date (YYYY-MM-DD) |
The date when the card was last used. From the LAST_ACT_DATE in the ACT_ACCOUNT table. |
|
|
PROGRAM_NAME |
Varchar |
64 |
The program name description for the award account. From the PROGRAM_NAME in the ACT_PROGRAM table. |
|
PROGRAM_ID |
Varchar |
32 |
Unique identifier for the program. From the PROGRAM_ID in the ACT_PROGRAM table. |
|
UPDATE_DATE |
Date (YYYY-MM-DD) |
The date when the card was last updated. From the UPDATE_DATE in the ACT_ACCOUNT table. |
|
|
CURRENCY_CODE |
Varchar |
30 |
The currency for the award. From the PROG_CURRENCY_TYPCODE in the ACT_PROGRAM table. |
|
ACCOUNT_EXPIRATION_DATE |
Date (YYYY-MM-DD) |
The date when the account expires. From the EXPIRY_DATE in the ACT_ACCOUNT table. |
|
|
ACCOUNT_ID |
Numeric |
19,0 |
Unique award account identifier. From the ACCOUNT_ID in the ACT_ACCOUNT table. |
|
AWARD_BALANCE |
Numeric |
17,6 |
Balance of the award. From the sum of the unexpired amounts in the AWARD_AMOUNT in the AWD_AWARD_COUPONS table. |
|
EAWARD_COUNT |
Numeric |
19,0 |
Total number of eawards generated. From the total number of AWD_AWARD_COUPONS records that are not expired. |
|
ENTITLEMENT_COUNT |
Numeric |
19,0 |
Total number of entitlement coupons generated. From the total number of AWD_ACCT_ETL_COUPONS records that are not expired. |
Sample rows: The following is an example of data in the Award Account Export Sync file:
CUSTOMER_ID CARD_NUMBER CARD_ACTIVE_FLAG CARD_EXPIRATION_DATE FIRST_USED_DATE LAST_USED_DATE PROGRAM_NAME PROGRAM_ID UPDATE_DATE CURRENCY_CODE ACCOUNT_EXPIRATION_DATE ACCOUNT_ID AWARD_BALANCE EAWARD_COUNT ENTITLEMENT_COUNT <CUSTOMER_ID> <CARD_NUMBER> 1 2016-12-19 2016-12-01 2016-12-01 AwardPgm 1513 2016-12-01 USD 2016-12-31 <ACCOUNT_ID> 1 1 0
Award Coupon Export Sync File Format for Marketing
The Award Coupon Export Sync file can include all unexpired records, or only records that have been created or updated since the last time the job was run. When the pdate option is selected, eaward coupons with zero balances and entitlement coupons that have exceeded the maximum redemption count are included to provide updates to previously exported records.
The file name defaults from the Responsys Award Coupon Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix.
The default static filename setting is _AwardCouponsSync.
The Award Coupon Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.
Table C-2 Award Coupon Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer. |
|
CARD_NUMBER |
Varchar |
64 |
Unique card number. From the CARD_NUM in the ACT_CARD table. |
|
ACCOUNT_ID |
Numeric |
19,0 |
Unique award account identifier. From the ACCOUNT_ID in the ACT_ACCOUNT table. |
|
PROGRAM_ID |
Varchar |
32 |
Unique identifier for the program. From the PROGRAM_ID in the ACT_PROGRAM table. |
|
COUPON_TYPECODE |
Varchar |
32 |
Indicates the type of award coupon: EAWARD or ENTITLEMENT. |
|
COUPON_ID |
Varchar |
32 |
Unique identifier for an award coupon. From the COUPON_ID in the AWD_ACCT_ETL_COUPONS or AWD_AWARD_COUPONS table. |
|
COUPON_BALANCE |
Number |
17,6 |
Balance of the e-award coupon. From the AWARD_AMOUNT in the AWD_AWARD_COUPONS table. Not included for an entitlement coupon. |
|
CURRENCY_CODE |
Varchar |
30 |
The currency for the award. From the PROG_CURRENCY_TYPCODE in the ACT_PROGRAM table. |
|
DEAL_NAME |
Varchar |
64 |
The user-defined name for an entitlement award coupon. From the NAME in the AWD_ENTITLEMENT_DEAL table. Not included for an e-award. |
|
DEAL_ID |
Number |
19 |
Unique identifier for the entitlement deal. Not included for an e-award. |
|
EXPIRATION_DATE |
Date (YYYY-MM-DD) |
The date when the coupon expires. |
|
|
UPDATE_DATE |
Date (YYYY-MM-DD) |
The date when the coupon was last updated. |
Sample rows: The following is an example of data in the Award Coupon Export Sync file:
CUSTOMER_ID CARD_NUMBER ACCOUNT_ID PROGRAM_ID COUPON_TYPECODE COUPON_ID COUPON_BALANCE CURRENCY_CODE DEAL_NAME DEAL_ID EXPIRATION_DATE UPDATE_DATE <CUSTOMER_ID> <CARD_NUMBER> <ACCOUNT_ID> <PROGRAM_ID> ENTITLEMENT <COUPON_ID> USD Award Deal Name <DEAL_ID> 2016-10-24
Scheduling Award Sync for Marketing
Generation of these files is configured through the Award Sync Export option in Job Scheduling.
Figure C-1 Award Sync Export Window
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.
Customers Sync Export
This section includes:
Customer Export Sync File Format for Marketing
The Customer Export Sync file can include all customer records, or only records that have been created or updated since the last time the job was run.
The file name defaults from the Responsys Customer Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _CustomerSync.
Only customer records with email addresses are included in the export.
The Customer Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. This information is primarily found in the CST_CUSTOMER, CST_ADDRESS, CST_EMAIL, and CST_CUST_PHONE tables.
Table C-3 Customer Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
HOME_STORE |
Varchar |
64 |
The retail location where the customer shops the most. |
|
SALUTATION |
Varchar |
254 |
Title that comes before the customer’s name. |
|
FIRST_NAME |
Varchar |
254 |
Customer’s first name. |
|
SECOND_FIRST_NAME |
Varchar |
254 |
Customer’s second first name used for sorting. |
|
MIDDLE_NAME |
Varchar |
254 |
Customer’s middle name. |
|
LAST_NAME |
Varchar |
254 |
Customer’s last name. |
|
SECOND_LAST_NAME |
Varchar |
254 |
Customer’s second last name used for sorting. |
|
SUFFIX |
Varchar |
254 |
Title that comes after the customer’s name. |
|
ACTIVE_FLAG |
Number |
3 |
Flag indicating whether the customer is active. A customer that was deactivated through a web service request has this flag set to 0. |
|
ADDRESS1 through ADDRESS4 |
Varchar |
254 |
The customer’s primary address. |
|
APARTMENT |
Varchar |
254 |
The customer’s apartment or suite number. |
|
CITY |
Varchar |
254 |
The customer’s city. |
|
STATE |
Varchar |
254 |
The customer’s state or province. |
|
POSTAL_CODE |
Varchar |
20 |
The customer’s zip or postal code. |
|
COUNTRY |
Varchar |
254 |
The customer’s country. |
|
EMAIL_TYPECODE |
Varchar |
30 |
Code identifying the type of email address (for example, home). |
|
EMAIL_ADDRESS |
Varchar |
254 |
The customer’s primary email address. |
|
PHONE_TYPECODE |
Varchar |
30 |
Code identifying the type of phone number (for example, business). |
|
PHONE_NUMBER |
Varchar |
34 |
The customer’s primary area code and phone number. |
|
MAIL_CONTACT_FLAG |
Number |
3 |
Flag indicating whether the customer wishes to be contacted by mail. |
|
EMAIL_CONTACT_FLAG |
Number |
3 |
Flag indicating whether the customer wishes to be contacted by email. |
|
PHONE_CONTACT_FLAG |
Number |
3 |
Flag indicating whether the customer wishes to be contacted by phone. |
|
FAX_CONTACT_FLAG |
Number |
3 |
Flag indicating whether the customer wishes to be contacted by fax. |
Customer Deletes Export Sync File Format for Marketing
The Customer Deletes Export Sync file is generated when the Customer Sync job is run as a scheduled update, and includes each customer that has been deleted from the database through a merge or a Housekeeping job. These customer records are stored temporarily in the CST_DELETES_SYNC table until they are included in the Customer Delete Sync file.
Customers that are deactivated through a web service request are retained in the database, and are included instead in the Customer Sync File with the ACTIVE_FLAG set to 0.
The file name defaults from the Responsys Customer Deletes Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix.
The default static filename setting is _CustomerDeletesSync.
The Customer Delete Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.
Table C-4 Customer Deletes Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
ACTIVE_FLAG |
Number |
3 |
Set to 0 for each deleted customer. |
Scheduling Customer Sync for Marketing
Generation of these files is configured through the Customer Sync Export option in Job Scheduling. If you select an export target system of Marketing, the following options are displayed:
Figure C-2 Customer Sync Export Parameters - Marketing
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.
Customer Activity Sync Export
This section includes:
Customer Activity Export Sync File Format for Marketing
The Customer Activity Export Sync file can include all customer activity, or can include only activity records that occurred since the last time the job ran.
The file name defaults from the Responsys Customer Activities Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix.
The default static filename setting is _CustomerActivitiesSync.
The Customer Activities Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. This information is primarily found in the CST_DEPARTMENT_TOTALS table.
Table C-5 Customer Activity Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
YEAR |
Varchar |
4 |
The year when the activity took place. |
|
ITEM_HIER_LEVEL1 |
Varchar |
64 |
Unique identifier for the highest level of the merchandise hierarchy, such as the department. The level is 0 if there is no hierarchy definition for that level. |
|
ITEM_HIER_LEVEL2 |
Varchar |
64 |
Unique identifier for the second level of the merchandise hierarchy, such as the sub-department. The level is 0 if there is no hierarchy definition for that level. |
|
ITEM_HIER_LEVEL3 |
Varchar |
64 |
Unique identifier for the third level of the merchandise hierarchy, such as the class. The level is 0 if there is no hierarchy definition for that level. |
|
ITEM_COUNT |
Number |
19,6 |
The total unit count for the items included in the activity. The count is 0 if the activity did not involve an item quantity. |
|
ITEM_VALUE |
Number |
19,6 |
The total value of the items included in the activity. Includes up to two decimal positions if the amount is not a whole number. |
|
LAST_LOCATION |
Varchar |
64 |
Code identifying the location associated with the activity. Blank if the activity was not associated with a location. |
|
LAST_BUSINESS_DATE |
Date |
Last date for the activity. |
|
|
LAST_UPDATE_DATE |
Date |
Date last date when the activity was updated. |
Sample rows: The following is an example of data in the Customer Activity Export Sync file:
"CUSTOMER_ID YEAR ITEM_HIER_LEVEL1 ITEM_HIER_LEVEL2 ITEM_HIER_LEVEL3 ITEM_COUNT ITEM_VALUE LAST_LOCATION LAST_BUSINESS_DATE LAST_UPDATE_DATE" "<CUSTOMER_ID> 2017 900 900-M 900-M-S 3 15000 <LAST_LOCATION> 2017-07-28 2017-07-28"
Scheduling Customer Activity Export Sync for Marketing
Generation of this file is configured through the Customer Activity Sync Export option in Job Scheduling. If you select an export target system of Marketing, the following options are displayed:
Figure C-3: Customer Activity Sync Export Parameters - Marketing
Figure C-3 Customer Activity Sync Export Parameters - Marketing
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.
Customer Attributes Sync Export
This section includes:
Customer Attributes Export Sync File Format for Marketing
Customer attributes can be included in this file if the Publish to Batch Exporter option is selected, and only if they are assigned to active customers. All eligible attributes are included each time the job runs.
The Customer Attributes Export file includes a single row per customer, with all eligible attributes included in the row.
By default, a maximum of 50 attributes can be exported, but this maximum is configurable through the Batch Exporter Customer Attribute Count property.
The file name defaults from the Responsys Customer Attributes Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix.
The default static filename setting is _CustomerAttributesSync.
The Customer Attributes Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. The default information in this file is from the CST_CUST_ATTRIBUTES table.
Table C-6 Customer Attributes Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EMAIL_ADDRESS |
Varchar |
254 |
The customer’s primary email address. |
|
Attribute Name and ID |
Varchar |
254 |
There is a separate column for each customer attribute selected for inclusion in the export file. The field heading includes the attribute name and its ID; for example, a heading of 17_BIRTH_MONTH indicates the BIRTH_MONTH attribute, with a unique numeric ID for the attribute of 17. The components of the field headings are separated by underscores; for example, 17_BIRTH_MONTH. |
Scheduling Customer Attribute Export Sync for Marketing
Generation of this file is configured through the Customer Sync Export option in Job
Scheduling. If you select an export target system of Marketing, the following options are displayed:
Figure C-4 Customer Attribute Sync Export Parameters - Marketing
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.
Loyalty Accounts Sync Export
This section includes:
Loyalty Account Export Sync File Format for Marketing
The Loyalty Account Export Sync file can include all active records, or only records that have been created or updated since the last time the job was run. If a customer is associated with an inactive or expired card, it is included in the file.
The file name defaults from the Responsys Loyalty Account Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix.
The default static filename setting is _LoyaltyAccountsSync
The Loyalty Account Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. This information is primarily found in the ACT_CARD, ACT_ACCOUNT, and LYL_LOYALTY_ACCT tables.
Table C-7 Loyalty Account Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer. |
|
CARD_NUMBER |
Varchar |
64 |
Unique card number. |
|
CARD_ACTIVE_FLAG |
Number |
3 |
Card active flag (set to 1 if active; if not active, indicates a deactivation or merge). |
|
CARD_EXPIRATION_DATE |
Date (YYYY-MM-DD) |
The date when the card expires. |
|
|
ACCOUNT_ID |
Number |
19 |
Unique identifier for the account. |
|
FIRST_USED_DATE |
Date (YYYY-MM-DD) |
The date when the card was first used. |
|
|
LAST_USED_DATE |
Date (YYYY-MM-DD) |
The date when the card was last used. |
|
|
LTD_BALANCE |
Number |
19.6 |
The life-to-date point balance. |
|
YTD_BALANCE |
Number |
19.6 |
The year-to-date point balance. |
|
EARNED_POINT_BALANCE |
Number |
19.6 |
Current earned point balance. |
|
ESCROW_POINT_BALANCE |
Number |
19.6 |
Current loyalty points that are waiting to become earned. |
|
PROGRAM_NAME |
Varchar |
64 |
The program name description. |
|
LEVEL_ID |
Number |
32 |
Unique identifier of the program level. |
|
LEVEL_NAME |
Varchar |
64 |
Name of the level. |
|
ACCOUNT_EXPIRATION_DATE |
Date (YYYY-MM-DD) |
The date when the account expires. |
|
|
UPDATE_DATE |
Date (YYYY-MM-DD) |
The date when the card was last updated. |
Scheduling Loyalty Account Export Sync for Marketing
Generation of this file is configured through the Loyalty Account Sync Export option in Job Scheduling.
Figure C-5 Loyalty Account Sync Export Window
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.
Points/Awards Notification Export
This export includes information to generate notifications about points transfers and/or award transfers. A separate file is generated for each type of transfer, and information is included to notify both the originating Customer for the transfer and the recipient.
The information enables the integrated system to generate transfer notification emails to the originator and recipient of the award or points transfer, based on each Customer’s notification preferences. SMS messages can also be generated if the integrated system supports it.
When interactive notification requests are sent through a web service message, use of the export file occurs only if an interactive request to generate the notification did not succeed. In this case, the details of the transfer are retained in the database until a scheduled notification export job runs.
The Responsys Integration Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide describes the configuration required for transfer notification integration with a marketing system such as Responsys.
This section includes:
Award Transfer Notifications Sync Export File
The Award Transfer Notifications Sync file name defaults from the Responsys Awards Transfer Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is AwardTransferSync.
An entry for a transfer is included in the file only if there is phone or email contact information for either the originator of the transfer or the recipient. Also, if interactive requests are sent through a web service message, this file is sent only if the interactive notification request was not successful.
The Award Transfer Notifications Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.The information in this file is primarily from the TRANSFER_NOTIFICATION table.
Table C-8 Award Transfer Notifications Sync Export File
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
FROM_CARD_NUM |
Varchar |
64 |
The Card number originating the transfer. The number is partially masked with Xs |
|
TO_CARD_NUM |
Number |
64 |
The Card number receiving the transfer. The number is partially masked with Xs. |
|
FROM_EMAIL_ADDRESS |
Varchar |
128 |
The primary email address of the Customer originating the transfer. Included only if the Customer’s contact permissions allow it. |
|
FROM_PHONE_NUM |
Varchar |
50 |
The primary phone number of the Customer originating the transfer. Included only if the Customer’s contact permissions allow it. Notification by SMS might not be supported by the integrating system, such as Responsys. |
|
TO_EMAIL_ADDRESS |
Varchar |
128 |
The primary email address of the Customer receiving the transfer. Included only if the Customer’s contact permissions allow it. TO_PHONE_NUM Varchar 50 The primary phone number of the Customer receiving the transfer. Included only if the Customer’s contact permissions allow it. Notification by SMS might not be supported by the integrating system, such as Responsys. |
|
ACTIVITY_DATETIME |
Date (YYYY-MM-DD) |
Date and time when the transfer took place. |
|
|
COUPON_BALANCE |
Number 17,6 |
The amount of the Award Coupon. Might be 0 for an Entitlement Coupon. If multiple Coupons are transferred, a single record is sent. |
Points Transfer Notifications Sync Export File
The Points Transfer Notifications Sync file name defaults from the Responsys Points Transfer Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PointTransferSync.
An entry for a transfer is included in the file only if there is phone or email contact information for either the originator of the transfer or the recipient. Also, if interactive requests are sent through a web service message, this file is sent only if the interactive notification request was not successful.
The Points Transfer Notifications Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.The information in this file is primarily from the TRANSFER_NOTIFICATION table.
Table C-9 Points Transfer Notifications Sync Export File
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
FROM_CARD_NUM |
Varchar |
64 |
The Card number originating the transfer. The number is partially masked with Xs. |
|
TO_CARD_NUM |
Number |
64 |
The Card number receiving the transfer. The number is partially masked with Xs. |
|
FROM_EMAIL_ADDRESS |
Varchar |
128 |
The primary email address of the Customer originating the transfer. Included only if the Customer’s contact permissions allow it. |
|
FROM_PHONE_NUM |
Varchar |
50 |
The primary phone number of the Customer originating the transfer. Included only if the Customer’s contact permissions allow it. |
|
TO_EMAIL_ADDRESS |
Varchar |
128 |
The primary email address of the Customer receiving the transfer. Included only if the Customer’s contact permissions allow it. |
|
TO_PHONE_NUM |
Varchar |
50 |
The primary phone number of the Customer receiving the transfer. Included only if the Customer’s contact permissions allow it. |
|
ACTIVITY_DATETIME |
Date (YYYY-MM-DD) |
Date and time when the transfer took place. |
|
|
NUMBER_OF_POINTS |
Number |
17,6 |
The number of points transferred. |
Promotion Sync Export
This section includes:
Promotion Export Sync File Format for Marketing
The Promotion Export Sync file name defaults from the Responsys Promotion Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PromotionSync.
The Promotion Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.The information in this file is primarily from the CMP_PROMOTION table.
Table C-10 Promotion Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
PROMOTION_TYPE |
Varchar |
30 |
The type of promotion: AWARD, COUPON, MESSAGE, or PRODUCT. |
|
PROMOTION_NAME |
Varchar |
64 |
The user-defined name of the promotion. |
|
DESCRIPTION |
Varchar |
254 |
The user-defined description of the promotion. |
|
START_DATE |
Date (YYYY-MM-DD) |
The date when the promotion starts. |
|
|
START_TIME |
Varchar |
24 |
The time when the promotion starts. HH:MM format. |
|
END_DATE |
(YYYY-MM-DD) |
24 |
The date when the promotion ends. |
|
END_TIME |
Varchar |
24 |
The time when the promotion ends. HH:MM format. |
|
STATUS |
Varchar |
50 |
The status of the promotion. |
|
SERIALIZED_COUPON_FLAG |
Number |
3 |
Set to 1 for a serialized coupon. |
|
AWARD_PROGRAM_ID |
Varchar |
32 |
Unique identifier for the award program specified for the award certificate. From the CMP_PROMO_AWARD_CERTIFICATE table. |
|
COUPON_PREFIX |
Varchar |
16 |
The prefix specified for award certificates. From the CMP_PROMO_AWARD_CERTIFICATE table. |
Promotion Award Coupons Export Sync File Format for Marketing
The Promotion Award Coupons Export Sync file name defaults from the Responsys Promotion Award Coupons Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PromotionAwardCouponsSync.
The Promotion Award Coupons Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.
The information in this file is from the CMP_PROMOTION, CMP_PROMO_AWARD_COUPONS_MAP, and CMP_CUST_PROMO_TARGET tables.
Table C-11 Promotion Award Coupons Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
COUPON_ID |
Varchar |
32 |
Unique identifier for the promotion award coupon. |
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
Promotion Deals Export Sync File Format for Marketing
The Promotion Deals Export Sync file name defaults from the Responsys Promotion Deals Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is PromotionDealsSync.
The Promotion Deals Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. The information in this file is from the CMP_PROMOTION, CMP_PROMODEAL, and DLS_DEAL tables.
Table C-12 Promotion Deals Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
DEAL_ID |
Number |
19 |
Unique identifier for the deal. |
|
DEAL_NAME |
Varchar |
64 |
User-specified name of the deal. |
|
DEAL_TYPE |
Varchar |
32 |
The type of deal, such as TRANSACTION_DISCOUNT or LINE_ITEM_DISCOUNT. |
|
ENABLED_FLAG |
Number |
3 |
Set to 1 if the deal is enabled. |
|
DEAL_GROUP |
Varchar |
39 |
Unique identifier for the deal group. Deal Groups are available only if Promotion Coupon (Ring Code) Management is enabled. |
Promotion Deals Export Sync File Format for Marketing
The Promotion Deals Export Sync file name defaults from the Responsys Promotion Deals Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PromotionDealsSync.
The Promotion Deals Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. The information in this file is from the CMP_PROMOTION, CMP_PROMODEAL, and DLS_DEAL tables.
Table C-13 Promotion Deals Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
DEAL_ID |
Number |
19 |
Unique identifier for the deal. |
|
DEAL_NAME |
Varchar |
64 |
User-specified name of the deal. |
|
DEAL_TYPE |
Varchar |
32 |
The type of deal, such as TRANSACTION_DISCOUNT or LINE_ITEM_DISCOUNT. |
|
ENABLED_FLAG |
Number |
3 |
Set to 1 if the deal is enabled. |
|
DEAL_GROUP |
Varchar |
39 |
Unique identifier for the deal group. Deal Groups are available only if Promotion Coupon (Ring Code) Management is enabled. |
Promotion Deal Attributes Export Sync File Format for Marketing
The Promotion Deal Attributes Export Sync file name defaults from the Responsys Promotion Deals Attributes Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PromotionDealsAttributesSync.
The Promotion Deal Attributes Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.
The information in this file is primarily from the CMP_PROMOTION, CMP_PROMODEAL, and CMP_PROMODEAL_ATTRIBUTES tables.
Table C-14 Promotion Deal Attributes Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
DEAL_ID |
Number |
19 |
Unique identifier for the deal. |
|
ATTR_CLASSIFICATION |
Varchar |
19 |
The intended use for the attribute, such as DEAL or PROMO-DEAL. |
|
ATTRIBUTE_ID |
Number |
19 |
Unique identifier for the attribute. |
|
ATTRIBUTE_NAME |
Varchar |
64 |
User-defined name for the attribute. |
|
ATTRIBUTE_SEQ |
Number |
10 |
Unique identifier for the attribute’s assignment to the promotion. |
|
ATTRIBUTE_VALUE |
Varchar |
254 |
Selected setting for the attribute. |
Promotion Serialized Coupons Export Sync File Format for Marketing
The Promotion Serialized Coupons Export Sync file name defaults from the Responsys Promotion Serialized Coupon Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PromotionSerializedCouponsSync.
The Promotion Serialized Coupons Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. The information in this file is from the CMP_PROMOTION and CMP_SERIALIZED_COUPON_CODES tables.
Table C-15 Promotion Serialized Coupons Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
COUPON_ID |
Varchar |
32 |
Unique identifier for the promotion award coupon. |
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
Promotion Targets Sync File Format for Marketing
The Promotion Targets Export Sync file name defaults from the Responsys Promotion Targets Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _PromotionTargetsSync.
The Promotion Targets Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. The information in this file is from the CMP_PROMOTION and CMP_CUST_PROMO_TARGET tables.
Table C-16 Promotion Targets Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. |
|
PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
TARGET_NAME |
Varchar |
64 |
The name of the segment selected as the target for the promotion. |
|
ACTIVE_FLAG |
Number |
1 |
Set to 1. |
|
START_DATE |
Date (YYYY-MM-DD) |
The date when the promotion starts. |
|
|
START_TIME |
Varchar |
24 |
The time when the promotion starts. HH:MM format. |
|
END_DATE |
(YYYY-MM-DD) |
24 |
The date when the promotion ends. |
|
END_TIME |
Varchar |
24 |
The time when the promotion ends. HH:MM format. |
|
There is an additional, unlabeled field at the end of each row, consisting of the number 1. |
An unlabeled field at the end of each row, set to 1. |
||
Generating the Promotion Export Sync Files for Marketing
These files are generated by selecting Export at the Options After Save step when you complete creation of a coupon, message, or product promotion.
You can also generate the files by selecting Export To Batch from the Actions menu at the Promotion List screen for any promotion type.
See the Customer Engagement User Guide for more information on creating and working with promotions.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Implementation Guide for more information about setting related properties.
Promotion Response Sync Import
The Promotion Response Sync Import files contain customer email response information from the Marketing system, such as Responsys, that Customer Engagement uses to update the CMP_CUST_PROMO_TARGET table. This information is displayed on the Promotion Scorecard page, and individual activity is recorded in Customer Notes.
Import properties are defined in the csv-bean-mapping-config.xml file. Contact your Oracle representative for more information.
This section includes:
SENT_EMAILS Promotion Import File Format
The SENT_EMAILS Promotion Import file contains the following information indicating that the email was sent to the customer:
Table C-17 SENT_EMAILS Promotion Import File Format
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CE_CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. Required by Customer Engagement.The Marketing system needs to be configured to include this information. |
|
CE_PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
EVENT_TYPE_ID |
Number |
1 |
Set to 1. |
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EVENT_CAPTURED_DT |
Datetime (MM/DD/YY HH:MM:SS) |
The date and time when the email was sent. |
CLICK_EMAILS Promotion Import File Format
The CLICK_EMAILS Promotion Import file contains the following information indicating that the customer clicked a link in the email:
Table C-18 CLICK_EMAILS Promotion Import File Format
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
EVENT_TYPE_ID |
Number |
1 |
Set to 1. |
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EVENT_CAPTURED_DT |
Datetime (MM/DD/YY HH:MM:SS) |
The date and time when the email was sent. |
|
|
CE_CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. Required by Customer Engagement.The Marketing system needs to be configured to include this information. |
|
CE_PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. |
|
OFFER_NAME |
Varchar |
64 |
Name of the offer from the marketing system. |
|
OFFER_NUMBER |
Varchar |
254 |
Number of the offer from the marketing system. |
|
OFFER_CATEGORY |
Varchar |
254 |
Category for the offer from the marketing system. |
|
OFFER_URL |
Varchar |
1024 |
URL that the customer clicked. |
OPEN_EMAILS Promotion Import File Format
The OPEN_EMAILS Promotion Import file contains the following information indicating that the customer opened the email:
Table C-19 OPEN_EMAILS Promotion Import File Format
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
EVENT_TYPE_ID |
Number |
1 |
Set to 4. |
|
ACCOUNT_ID |
Number |
The account ID in the Marketing system, such as Responsys. |
|
|
LIST_ID |
Number |
The list ID in the Marketing system, such as Responsys. |
|
|
RIID |
The Responsys ID. |
||
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EVENT_CAPTURED_DT |
Datetime (MM/DD/YY HH:MM:SS) |
The date and time when the email was sent. |
|
|
CE_CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. Required by Customer Engagement.The Marketing system needs to be configured to include this information. |
|
CE_PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. Required by Customer Engagement. |
FAIL_EMAILS Promotion Import File Format
The CLICK_EMAILS Promotion Import file contains the following information indicating that the email was not successfully sent to the customer:
Table C-20 FAIL_EMAILS Promotion Import File Format
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EVENT_TYPE_ID |
Number |
1 |
Set to 8. |
|
EVENT_CAPTURED_DT |
Datetime (MM/DD/YY HH:MM:SS) |
The date and time when the email was sent. |
|
|
CE_CAMPAIGN_ID |
Number |
19 |
Unique identifier for the campaign. Required by Customer Engagement.The Marketing system needs to be configured to include this information. |
|
CE_PROMOTION_ID |
Number |
19 |
Unique identifier for the promotion. Required by Customer Engagement. |
Scheduling the Promotion Response Data Import
Import of the response data files is configured through the Promotion Response Data Import option in Job Scheduling.This Task has no additional parameters.
Figure C-6 Promotion Response Data Import
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.
Segments Export Sync
This section includes:
Customer Stratified Segment Export Sync File Format for Marketing
The Customer Stratified Segment Export Sync file can include all customers within each segment. The file includes a single row per customer, with all eligible segments included in each row. For each segment that the customer belongs to, the Segment ID + Segment name column contains a 1; otherwise, the column includes a 0.
Each file can include multiple stratified segments, or multiple unstratified or manual segments, but not both. To generate export files for multiple types of segments, schedule multiple segment export jobs, or use a different file name for the export files created by each task.
By default, a maximum of 50 stratified can be exported, but this maximum is configurable through the Batch Exporter Stratified Segment Count property.
The file name defaults from the Responsys Segment Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _SegmentSync.
The Customer Stratified Segment Export Sync file for Marketing contains the following information by default, but the exported information is configurable with the help of Oracle consultants. Only segments that have Publish to Batch Exporter selected can be included.
Table C-21 Customer Stratified Segment Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EMAIL_ADDRESS |
Varchar |
254 |
The customer’s primary email address. |
|
Segment ID + Segment name |
Number |
1 |
The field heading includes the segment name and its ID; for example, a heading of 2750_Strat_Sample indicates the StratSample segment, with a unique numeric ID for the segment of 2750. The components of the field headings are separated by underscores; for example, 2750_StratSample. For each customer that belongs to the segment, this column contains a 1; otherwise, this column contains a 0. If this column contains a 0, indicating that the customer does not belong to the segment, the remaining columns are empty for the customer and segment. |
|
Segment ID + Segment name + Format |
Varchar |
The Segment stratification format (RANGE or NTILE). |
|
|
Segment ID + Segment name + Type |
Varchar |
The Segment strata type (Customer, Loyalty, or Transaction). |
|
|
Segment ID + Segment name + BasedOn |
Varchar |
The data that the strata is based on (Customer for a Customer strata type; Loyalty Summary or Loyalty Detail for a Loyalty strata type; Header or Detail for a Transaction strata type). |
|
|
Segment ID + Segment name + StrataField |
Varchar |
The field to stratify on, such as annual income. |
|
|
Segment ID + Segment name + StrataID |
Number |
38 |
A unique ID to identify the stratum. |
|
Segment ID + Segment name + StrataValue |
Number |
19 |
The related value for the customer; for example, the customer’s annual income. |
Customer Unstratified Segment Export Sync File Format for Marketing
The Customer Unstratified Segment Export Sync file can include all customers within each segment. The file includes a single row per customer, with all eligible segments included in each row. For each segment that the customer belongs to, the Segment ID + Segment name column contains a 1; otherwise, the column includes a 0.
Each file can include multiple stratified segments, or multiple unstratified or manual segments, but not both. To generate export files for multiple types of segments, schedule
multiple segment export jobs, or use a different file name for the export files created by each task.
The file name defaults from the Responsys Segment Sync Default Filename property, but the default can be overridden, and can include a date/time prefix or suffix. The default static filename setting is _SegmentSync.
The Customer Unstratified Segment Export Sync file for Marketing contains the following information by default, but the exported information is configurable with the help of Oracle consultants.
Table C-22 Customer Unstratified Segment Export Sync File Format for Marketing
| Field Name | Data Type | Maximum Length | Description |
|---|---|---|---|
|
CUSTOMER_ID |
Varchar |
32 |
Unique identifier for a retail customer (individual or organization). |
|
EMAIL_ADDRESS |
Varchar |
254 |
The customer’s primary email address. |
|
Segment ID + Segment name |
Number |
1 |
The field heading includes the segment name and its ID; for example, a heading of 2749_Unstrat_Sample indicates the UnstratSample segment, with a unique numeric ID for the segment of 2749. The components of the field headings are separated by underscores; for example, 2749_UnstratSample. For each customer in the segment, this column is populated with 1. |
Scheduling Customer Segment Export Sync for Marketing
Generation of this file is configured through the Segment Sync Export option in Job Scheduling. If you select an export target system of Marketing, the following options are displayed:
Figure C-7 Segment Sync Export Parameters - Marketing
See the Oracle Retail Customer Engagement Cloud Services User Guide for more information on Job Scheduling.
Each file can include multiple stratified segments, or multiple unstratified or manual segments, but not both. To generate export files for multiple types of segments, schedule multiple segment export jobs, or use a different file name for the export files created by each task.
See the Batch Exporter Properties chapter of the Oracle Retail Customer Engagement Cloud Services Implementation Guide for more information about setting related properties.