D BI/Analytics Batch Sync Processing

This appendix provides information on batch sync processing with a BI/Analytics system such as Oracle Retail Insights, including file layouts and background on configuration and scheduling.

Customer Engagement sends customer household, and segment data to the BI/Analytics system. The records are in pipe-delimited files. Data files do not include header records; instead, header records are listed in a separate file using the same file name and a .CTX

extension. For example, if the Customer Segment Sync Export file is named W_RTL_CUSTSEG_DS.dat, the .CTX file is named W_RTL_CUSTSEG_DS.dat.ctx. This .CTX file must be provided to the RI/Analytics system. Field names and sequence can vary based on the settings defined in the related .CTX file.

Export file names are defined through System Configuration properties, and cannot be changed when scheduling the export jobs. Because Retail Insights requires specific file names, these file names default to the related System Configuration properties so there is no need to change them if you sync data with Retail Insights.

See the Oracle Retail Customer Engagement 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 BI/Analytics system requests to purge customer or user data. These requests should go directly to the BI/Analytics system.

About this Appendix

This appendix includes information on the following BI/Analytics sync options:

• Customer Export Sync

• Customer Address Sync Export

• Customer Attributes Export Sync

• Household Export Sync

• Loyalty Account Activity Export Sync

• Loyalty Account Export Sync

• Loyalty Award Transaction Export Sync

• Loyalty Program Export Sync

• Segment Export Sync

• BI/Analytics Package Exporter

Customer Export Sync

This section includes:

• Customer Export Sync File Format for BI/Analytics

• Scheduling Customer Sync for BI/Analytics

Customer Export Sync File Format for BI/Analytics

The Customer Export Sync file name is from the Retail Insights Customer Sync Default Filename property, and should be set to W_PARTY_PER_DS for integration with Retail Insights.

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, and CST_DELETES_SYNC tables.

Table D-1 Customer Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
CUSTOMER_NUM	Varchar	32	Unique identifier for a retail customer (individual or organization).
POSTAL_CODE	Varchar	20	The customer’s zip or postal code. From the primary address.
CITY_NAME and CITY_CODE	Varchar	254	The customer’s city. From the primary address.
STATE_NAME and STATE_CODE	Varchar	254	The customer’s state or province. From the primary address.
COUNTRY_NAME and COUNTRY_CODE	Varchar	254	The customer’s country. From the primary address.
ACTIVE_FLG	Number	3	Flag indicating whether the customer is active (1 = active).
PROSPECT_FLG	Number	3	Flag indicating whether the customer is a prospect (1 = a prospect).
SUPPLIER_FLG	Varchar	1	Set to N.
SEX_MF_NAME and SEX_MF_CODE	Varchar	30	The customer’s gender (M or F).
ETHNICITY_NAME and ETHNICITY_CODE	Varchar	64	The customer’s ethnicity.
MARITAL_STAT_NAME and MARITAL_STAT_CODE	Varchar	64	The customer’s marital status.
EDUCATION_BCKGND_NAME and EDUCATION_BCKGND_CODE	Varchar	64	The customer’s educational level.
ADDR_EFF_DT	Date and time		The date when the customer’s current address became effective. Includes a time of 00:00:00.
ANNL_INCOME	Number	18.4	The customer’s annual income.
CALL_FLG	Number	3	The customer’s phone contact flag (1 = OK to call).
CON_ACTIVE_FLG	Number	3	The customer’s active flag (1 = active).
CON_BU_NAME	Varchar	254	The name of the customer’s organization.
CON_FORMED_DT	Date and time		The date when the customer record was created in the system (signup date). Includes a time of 00:00:00.
CUST_END_DT	Date and time		Five years from the date when the customer record was created. Includes a time of 00:00:00.
CUST_SINCE_DT	Date and time		The date when the customer record was created. Includes a time of 00:00:00.
ENTREPRISE_FLG	Number	5	Flag indicating if the customer is an organization or an individual.
SUPPRESS_CALL_FLG	Number	3	Flag indicating whether the customer wishes to be contacted by phone (1 = do not call).
SUPPRESS_EMAIL_FLG	Number	3	Flag indicating whether the customer wishes to be contacted by email (1 = do not email).
SUPPRESS_FAX_FLG	Number	3	Flag indicating whether the customer wishes to be contacted by fax (1 = do not fax).
SUPPRESS_MAIL_FLG	Number	3	Flag indicating whether the customer wishes to be contacted by mail (1 = do not mail).
SRC_EFF_FROM_DT	Date and time		The date when the customer was created. Includes a time of 00:00:00.
DELETE_FLG	Varchar	1	Flag indicating if the customer was deleted (N = not deleted, including deactivated customers).
DATASOURCE_NUM_ID	Number	1	Set to 1.
INTEGRATION_ID		32	Unique identifier for a retail customer (individual or organization).
CUSTOMER_BIRTH_MONTH	Number	3	The customer’s birth month. Numeric (11 = November).
CUSTOMER_BIRTH_YEAR	Number	4	The customer’s birth year.
ANNL_INCOME_RANGE	Number	18.4	ANNL_INCOME_RANGE

When the export is run as an update, the file includes customers that have been created or updated since the last time the export was run. The updated customer records are determined based on the Create Date or Update date from CST_CUSTOMER records or CST_ADDRESS records for primary addresses. However, the first time the customer export is run, it includes all customer records.

Any customers that have been deleted or deactivated are also included in the customer export file. The only information included for deleted customers is the customer ID, active flag, prospect flag, call flag, delete flag, organization flag, and the contact flag settings.

To track deleted customers for export, Customer Engagement creates records in the CST_DELETES_SYNC table. These deleted customers are indicated by a DELETE_FLG setting of Y in the export file. Deactivated customers are still retained in the regular customer tables, and are not included in the CST_DELETES_SYNC table. Once a customer deletion record is exported, it is removed from the CST_DELETES_SYNC table.

Customers are deleted through a merge or a delete customers Housekeeping job.

Customers are deactivated through a DeleteCustomer web service message.

Scheduling Customer Sync for BI/Analytics

Generation of this file is configured through the Customer Sync Export option in Job Scheduling. If you select an export target system of BI/Analytics, the following options are displayed:

Figure D-1 Customer Sync Export Parameters - BI/Analytics

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 Address Sync Export

This section includes:

• Customer Address Export Sync File Format for BI/Analytics

• Scheduling Customer Address Sync for BI/Analytics

Customer Address Export Sync File Format for BI/Analytics

The Customer Address Sync file name is from the Retail Insights Customer Activities Sync Default Filename property, and should be set to W_RTL_CUST_ADDRESS_DS for integration with Retail Insights.

The Customer Address 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_ADDRESS table. The customer address sync is always a full export rather than an update.

Table D-2 Customer Address Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
CUST_ID	Varchar	32	Unique identifier for a retail customer (individual or organization).
ADDRESS_ID	Number	38	Unique identifier of the address for this customer.
ADDRESS_CLASS_CODE	Varchar	30	User-defined identifier for the type of address.
PRIMARY_ADDDRESS_FLG	Varchar	3	Set to 1 if this is the primary address; otherwise, set to 0.
ADDRESS_START_DT	Date		The date when the address record was created. Includes a time of 00:00:00.
ADDRESS_END_DT	Date		Five years from the date when the address record was created. Includes a time of 00:00:00.
ST_ADDRESS1	Varchar	254	The customer’s street address
ST_ADDRESS2	Varchar	254	Second street address line.
ST_ADDRESS3	Varchar	254	Third street address line.
CITY_CODE and CITY_NAME	Varchar	254	The customer’s city.
STATE_CODE and STATE_NAME	Varchar	254	The customer’s state or province.
COUNTRY_CODE and COUNTRY_NAME	Varchar	254	The customer’s country.
POSTAL_CODE	Varchar	20	The customer’s zip or postal code.
SRC_EFF_FROM_DT	Date		The date when the address record was created. Includes a time of 00:00:00.
DATASOURCE_NUM_ID	Number	1	Set to 1.
INTEGRATION_ID	Varchar		Consists of the customer ID and the address ID, separated by a tilde; for example, 12345~2.

The export file does not include a header row. Instead, there is a separate file with the same name as the customer file, but with an extension of .CTX, that includes the titles of each column in the export file. For example, if the customer export file name is W RTL_CUST_ADDRESS_DS.dat, the file containing the header row names is WRTL_CUST_ADDRESS_DS.ctx.

Deleted addresses are not included in the export file.

Scheduling Customer Address Sync for BI/Analytics

Generation of this file is configured through the Customer Sync Export option in Job Scheduling. An export target system of BI/Analytics defaults and cannot be changed:

Figure D-2 Customer Address Sync Export Parameters - BI/Analytics

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 Export Sync

This section includes:

• Customer Attributes Metadata Export Sync File Format for BI/Analytics

• Customer Attributes Export Sync File Format for BI/Analytics

• Scheduling Customer Attributes Export Sync for BI/Analytics

Customer Attributes Metadata Export Sync File Format for BI/Analytics

Combined metadata file: If both the Customer sync and the Segment sync are run at the same time, the Customer Attributes Metadata is combined with the Customer Segment Attributes Metadata to create a single metadata file for export.

Before being combined with the Segment Attributes metadata, the Customer Attributes Metadata Export Sync file just contains the following information about customer attributes, and indicates how to interpret the information in the Customer Attributes Sync Export file. This information is primarily found in the DTV_ATTRIBUTE_TYPE table.

Table D-3 Customer Attributes Metadata Export Sync File Format for BI/Analytics

Field Name	Description
ATTR_NAME	The customer-defined name for a custom attribute.
SOURCE	Hard-coded to CE.
PHYSICAL_COL_NAME	Identifies whether the attribute is a string (alphanumeric), a date, or numeric. Possible physical column names for customer attributes are: PARTY_ATTR[N]_NAME_VALUE for a string attributePARTY_ATTR[N]_DATE_VALUE for a date attributePARTY_ATTR[N]_NUM_VALUE for a numeric attribute
TABLE_NAME	Set to W_PARTY_ATTR_DS, identifying the target table in the BI/Analytics system.
DESCRIPTION	The description of the attribute.
DATA_TYPE	Possible data types are: VARCHAR for a string attribute DATE for a date attribute NUMBER for a numeric attribute
DATASOURCE_NUM_ID	Hard-coded to 1.
INTEGRATION_ID	Concatenation of the attribute name + CE + ~ + the physical column name + ~ + the target table name, such as: NUM_KIDS~CE~PARTY_ATTR2_NAME~W_PARTY_ATTR_DS.

Sample Rows

The following is an example of customer attribute data in the Attributes Metadata Export Sync file:

NUM_KIDS|CE|PARTY_ATTR1_NUM_VALUE|W_PARTY_ATTR_DS|Number of Kids|NUMBER|1|Number of Kids~CE~PARTY_ATTR1_NUM_VALUE~W_PARTY_ATTR_DS|

Where:

• NUM_KIDS = The attribute’s Name, from the DTV_ATTRIBUTE_TYPE table

• CE = Hard-coded to CE

• A physical column name such as:

  • PARTY_ATTR[N]_NUM_VALUE = the [N] numeric attribute passed, or

  • PARTY_ATTR[N]_NAME = the [N] name attribute passed, or

  • PARTY_ATTR[N]_DATE= the [N] date attribute passed

Where:

• PARTY_ATTR is hard-coded

• [N] indicates the column number for that datatype, and

• NUM_VALUE, NAME, or DATE is the data type for the attribute.

  For example, PARTY_ATTR3_NUM_VALUE identifies the third numeric value attribute.

  Since there can be multiple attributes of the same data type passed, such as two date attributes, it is necessary to number the attributes with the same data type.

• W_PARTY_ATTR_DS = the target table in the BI/Analytics system

• Number of Kids = the attribute’s Description, from the DTV_ATTRIBUTE_TYPE table

• VARCHAR = the data type of the attribute in the BI/Analytics system; displayed as Character at the Attribute Definition list page. Mapping of attribute data types:

  • Enumerated, Character, or Boolean = VARCHAR

  • Number = NUMBER

  • Date = DATE

• 1 = Hard-coded to 1

• NUM_KIDS~CE~PARTY_ATTR1_NUM_VALUE~W_PARTY_ATTR_DS = concatenation of the attribute name + CE + ~ + the physical column name + ~ + the target table name

The file name is from the Retail Insights Attribute Metadata Sync Default Filename property, and should be set to W_RTL_INT_METADATA_G for integration with Retail Insights.

Customer Attributes Export Sync File Format for BI/Analytics

The information in the Customer Attributes Export Sync file is defined by the Customer Attributes Metadata Sync file. Aside from the hard-coded and mapping information, the information in this file is primarily found in the CST_CUST_ATTRIBUTES table.

The Customer Attributes file always includes 40 columns of attribute data, where:

• the first 20 columns are string attributes

• the next 10 columns are date attributes

• the last 10 columns are numeric attributes

If fewer than the indicated number of string, date, or numeric attributes are passed, then the remaining columns for that attribute type are empty. For example, if there are two string attributes, then the first two columns are populated, and the remaining 18 columns are empty, as in the following example.

The file name is from the Retail Insights Customer Attributes Sync Default Filename property, and should be set to W_RTL_PARTY_PER_ATTR_DS for integration with Retail Insights.

Table D-4 Customer Attributes Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
SUPPLIER_NUM	Varchar	32	Unique identifier for a customer. From the CST_CUST_ATTRIBUTES table.
W_CATEGORY			Hard-coded to RETAIL.
SUPPLIER_FLG			Hard-coded to N.
PARTY_ATTR[N]_NAME or PARTY_ATTR[N]_DATE or PARTY_ATTR[N}_NUM_VALUE where [N] indicates the sequence of the attribute of that data type, and NAME or DATE or NUM_VALUE indicates the data type	Varchar	254	The value of the attribute assigned to the customer. There can be multiple attributes of each data type (NAME, DATE, or NUM), and each is indicated in the related .CTX file by a unique sequence number and data type suffix. For example, the second DATE attribute included in the file is indicated by PARTY_ATTR2_DATE. The attribute export file always includes 40 columns of attribute data, where: the first 20 columns are string attributes the next 10 columns are date attributes the last 10 columns are numeric attributes If fewer than the indicated number of string, date, or numeric attributes are passed, then the remaining columns for that attribute type are empty. For example, if there are two string attributes, then the first two columns are populated, and the remaining 18 columns are empty, as in the following example.
DATASOURCE_NUM_ID			Hard-coded to 1.
INTEGRATION_ID	Varchar	32	Unique identifier for a customer. From the CST_CUST_ATTRIBUTES table.

Sample Rows

The following is an example of data in the Customer Attributes file:

<CUST_ID>|RETAIL|N|Attribute Value Example
1||||||||||||||||||||||||||||||||||||||||1|<CUST_ID>|
<CUST_ID>|RETAIL|N|Attribute Value Example 2!!||||||||||||||||||||2016-12-
31;00:00:00||||||||||33333||||||||||1|<CUST_ID>|

Where:

• RETAIL = Hard-coded

• N = Hard-coded

• Attribute Value Example = The Attribute character, number, or date Value, from the CST_CUST_ATTRIBUTES table (Varchar 254)

• 1 = Hard-coded

Scheduling Customer Attributes Export Sync for BI/Analytics

Generation of this file is configured through the Customer Attributes Sync Export option in Job Scheduling. If you select an export target system of BI/Analytics, the following options are displayed:

Figure D-3 Customer Attributes Sync Export Parameters - BI/Analytics

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.

This job option generates both the Customer Attributes Export Sync and the Customer Attributes Export Sync.

Household Export Sync

This section includes:

• Household Export Sync File Format for BI/Analytics

• Customer Household Export Sync File Format for BI/Analytics

• Additional Files

• Scheduling Customer Attributes Export Sync for BI/Analytics

Household Export Sync File Format for BI/Analytics

The Household Sync file name is from the Retail Insights Household Sync Default Filename property, and should be set to W_HOUSEHOLD_DS for integration with Retail Insights.

The Household 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_HOUSEHOLD table.

Table D-5 Household Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
NAME			Hard-coded to blank.
DATASOURCE_NUM_ID	Number	1	Hard-coded to 1.
INTEGRATION_ID	Char	28	Unique identifier for a household. From the HOUSEHOLD_KEY in the CUST_HOUSEHOLD table. Formatted as binary data.
HOUSEHOLD_ID	Char	28	Unique identifier for a household. From the HOUSEHOLD_KEY in the CUST_HOUSEHOLD table. Formatted as binary data.

Customer Household Export Sync File Format for BI/Analytics

The Customer Household Sync file name is from the Retail Insights Household Customer Association Sync Default Filename property, and should be set to W_RTL_CUST_HOUSEHOLD_DS for integration with Retail Insights.

The Customer Household 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_HOUSEHOLD table.

Table D-6 Customer Household Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
HOUSEHOLD_ID	Char	28	Unique identifier for a household. From the HOUSEHOLD_KEY in the CUST_HOUSEHOLD table. Formatted as binary data.
CUST_ID	Varchar	32	Unique identifier for a customer associated with the household.
DATASOURCE_NUM_ID	Number	1	Hard-coded to 1.
INTEGRATION_ID	Char	28	The concatenation of the household key + ~ + the customer ID.

Additional Files

The following files are always generated as part of the household export sync, but are empty:

• W_RTL_HOUSEHOLD_COMP_DS

• W_RTL_HOUSEHOLD_GRP_DS

Scheduling Customer Household Sync Export for BI/Analytics

Generation of these files is configured through the Customer Household Sync Export option in Job Scheduling. The following options are displayed:

Figure D-4 Customer Household Sync Export Parameters

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 Account Activity Export Sync

This section includes:

• Loyalty Account Activity Sync Export File Format for BI/Analytics

• Scheduling Loyalty Account Activity Sync Export for BI/Analytics

Loyalty Account Activity Sync Export File Format for BI/Analytics

The Loyalty Account Activity Sync export file name is from the Retail Insights Loyalty Transaction Data Sync Export Default Filename property, and should be set to W_RTL_LYL_TRANS_DS for integration with Retail Insights.

The Loyalty Account Activity Sync 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 LYL_LOYALTY_ACCT_ACT table, the ACT_CARD_ACCT_MAP table, and the ACT_CARD_CUST_MAP table.

Table D-7 Loyalty Account Activity Sync Export File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
Loyalty Account Activity ID	Number	19,0	Unique identifier for the loyalty account activity. From the LYL_LOYALTY_ACCT_ACT table.
Program ID	Varchar	32	Unique identifier for the loyalty program. From the LYL_LOYALTY_ACCT_ACT table.
Program Level ID	Number	38,0	Unique identifier for a loyalty program level. From the LYL_LOYALTY_ACCT_ACT table.
Account ID	Number	19,0	Unique identifier for an account. From the LYL_LOYALTY_ACCT_ACT table.
Card Serial Number	Varchar	64	Sixteen-digit number embossed on card that consists of 5-digit prefix, 2-digit series number, 3-digit batch number, and 6-digit sequence number. From the ACT_CARD_ACCT_MAP table.
Customer ID	Varchar	32	Unique identifier for the customer. From the ACT_CARD_CUST_MAP table.
Retail Transaction Business Date	Datetime		A calendar date that corresponds to an accounting period fiscal day at a particular retail store, has financial transactions attributed to it, and is aggregated using the Calendar Period structure. From the LYL_LOYALTY_ACCT_ACT table.
Account Activity Typecode	Varchar	30	Type code to categorize account activities. Possible typecodes include Activate, Deactivate, Issue, PointRecovery, Return, Award, Expire, ChangeLevel, SustainExtension, AccountMerge, ResetExpirationDate, TransferOut, and TransferIn. A typecode of Inquiry is ignored. From the LYL_LOYALTY_ACCT_ACT table.
Trigger Type	Varchar		Indicates where the transaction was triggered, such as POS, CRM server, or the loyalty admin module.
Retail Transaction ID	Varchar	128	The transaction ID from the POS system. From the LYL_LOYALTY_ACCT_ACT table.
Points Accrued	Number	19,6	The total number of points accrued through the activity. Included as a positive number when the transaction type is Issue, Point Recovery, and Earn. Included as a negative number when the transaction type is Return. From the NUM_POINTS in the LYL_LOYALTY_ACCT_ACT table.
Points Redeemed	Number	19,6	The total number of points redeemed through an Award. From the NUM_POINTS in the LYL_LOYALTY_ACCT_ACT table.
Bonus Points	Number	19,6	The total number of bonus points for the transaction. Included as a positive number when the transaction type is Issue, Point Recovery, and Earn. Included as a negative number when the transaction type is Return. From the NUM_POINTS in the LYL_LOYALTY_ACCT_ACT table.
Points Expired	Number	19,6	The total number of points expired. Included as a negative number when the transaction type is Expire. From the NUM_POINTS in the LYL_LOYALTY_ACCT_ACT table.
Requested Amount	Number	17,6	Monetary amount before being converted to the program currency when the transaction type is Issue or Return. From the LYL_LOYALTY_ACCT_ACT table.
Requested Currency Code	Varchar	3	Original currency typecode for the monetary fields used in the calculation of loyalty points when the transaction type is Issue or Return. From the LYL_LOYALTY_ACCT_ACT table.
Converted Amount	Number	17,6	Monetary amount after being converted to the program currency when the transaction type is Issue or Return. From the LYL_LOYALTY_ACCT_ACT table.
Program Currency Code	Varchar	30	The code identifying the currency defined for the loyalty program. Included for all transaction types.
Retail Location ID	Varchar	64	The ID of the retail location where the transaction occurred. From the LYL_LOYALTY_ACCT_ACT table.
Points Transferred Out	Number	19,6	The total number of points transferred out for the transaction. Included as a negative number when the transaction type is Transfer Out. From the NUM_POINTS in the LYL_LOYALTY_ACCT_ACT table.
Points Transferred In	Number	19,6	The total number of points transferred in for the transaction. Included as a positive number when the transaction type is Transfer In. From the NUM_POINTS in the LYL_LOYALTY_ACCT_ACT table.

Scheduling Loyalty Account Activity Sync Export for BI/Analytics

Generation of this file is configured through the Loyalty Account Activity Sync Export option in Job Scheduling. The following options are displayed:

Figure D-5 Loyalty Account Activity Sync Export Parameters

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 Account Export Sync

This section includes:

• Loyalty Account Sync Export File Format for BI/Analytics

• Scheduling Loyalty Account Sync Export for BI/Analytics

Loyalty Account Sync Export File Format for BI/Analytics

The Loyalty Account Sync export file name is from the Retail Insights Loyalty Account Sync Export Default Filename property, and should be set to W_RTL_LYL_ACCOUNT_DS for integration with Retail Insights.

The Loyalty Account Sync Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.

This information is found primarily in the LYL_LOYALTY_ACCT table.

Table D-8 Loyalty Account Activity Sync Export File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
Loyalty Account ID	Number	19,0	Unique identifier for a loyalty account. From the LYL_LOYALTY_ACCT table.
Card Serial Number	Varchar	64	Sixteen-digit number embossed on card that consists of 5-digit prefix, 2-digit series number, 3-digit batch number, and 6-digit sequence number. From the ACT_CARD_ACCT_MAP table.
Primary Customer Number	Varchar	32	Unique identifier for the customer. From the ACT_CARD_CUST_MAP table.
Loyalty Program ID	Varchar	32	Unique identifier for a loyalty program. From the LYL_LOYALTY_ACCT table.
Loyalty Program Level ID	Number	38	Unique identifier for a loyalty program level. From the LYL_LOYALTY_ACCT table.
Account Active Flag	Number	3,0	Flag indicating if the account has expired. From the ACT_ACCOUNT table.
Account Expiry Flag	Number	3,0	Flag indicating if the account is active.From the ACT_ACCOUNT table.
Earned Points Balance	Number	19,6	Current earned points balance for the account. From the LYL_LOYALTY_ACCT table.
Escrow Points Balance	Number	19,6	Current loyalty points balance in escrow until earned. From the LYL_LOYALTY_ACCT table.
Last Award Processed Date	Datetime		The last date when an award was processed for the account. The time is included with all zeroes, for example, 2017-12-31;00:00:00. From the LYL_LOYALTY_ACCT table.
Last Accrual Date	Datetime		The last date when an Issue transaction was processed for the account. The time is included with all zeroes, for example, 2017-12-31;00:00:00. From the LYL_LOYALTY_ACCT_ACT table.
Last Program Level Change Date	Datetime		The last date when the program level was changed for the account. The time is included with all zeroes, for example, 2017-12-31;00:00:00. From the LYL_LOYALTY_ACCT table.
Last Transaction Date	Datetime		The last date when a transaction occurred for the account. The time is included with all zeroes, for example, 2017-12-31;00:00:00. From the LYL_LOYALTY_ACCT_ACT table.

Scheduling Loyalty Account Sync Export for BI/Analytics

Generation of this file is configured through the Loyalty Account Sync Export option in Job Scheduling. The following options are displayed:

Figure D-6 Loyalty Account Sync Export Parameters

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 Award Transaction Export Sync

This section includes:

• Loyalty Award Transaction Sync Export File Format for BI/Analytics

• Scheduling Loyalty Award Transaction Sync Export for BI/Analytics

Loyalty Award Transaction Sync Export File Format for BI/Analytics

The Loyalty Award Transaction Sync export file name is from the Retail Insights Loyalty Award Transaction Data Sync Export Default Filename property, and should be set to W_RTL_LYL_AWD_DS for integration with Retail Insights.

The Loyalty Award Transaction Sync Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants. This information is found primarily in the AWD_ACCT_ACTIVITY table.

Table D-9 Loyalty Award Transaction Sync Export File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
Award Transaction ID	Varchar	32	Transaction ID of the award. From the AWD_ACCT_ACTIVITY table.
Award Account ID	Number	19,0	Identifies the award account associated with the activity. From the AWD_ACCT_ACTIVITY table.
Award Program ID	Varchar	32	Identifies the program. From the AWD_AWARD_ACCT table.
Card Serial Number	Varchar	64	Sixteen-digit number embossed on card that consists of 5-digit prefix, 2-digit series number, 3-digit batch number, and 6-digit sequence number. From the ACT_CARD table.
Customer ID	Varchar	32	Unique identifier for the customer. From the ACT_CARD_CUST_MAP table.
Award Type	Varchar	32	Indicates if the activity was for an E_AWARD or ENTITLEMENT. Blank for certain activities, such as Active Account, Deactivate Account, or Account Merge. From the AWD_ACCT_ACTIVITY table.
Activity Typecode	Varchar	32	The type of activity, including IssueCoupon, IssueEntitlementCoupon, Redeem, AutomaticRedeem, TransferIn, TransferOut, VoidAwardTransaction, NotifyCouponExpire, ActivateAccount, DeactivateAccount, and AccountMerge. From the AWD_ACCT_ACTIVITY table.
Coupon ID	Varchar	32	Unique identifier for an award coupon. Blank for certain activities, such as Active Account, Deactivate Account, or Account Merge. From the AWD_ACCT_ACTIVITY table.
Coupon Effective Date	Datetime		The date when the coupon is effective. The time is included with all zeroes, for example, 2017-12-31;00:00:00. Blank unless related to the activity, such as for Issue Promo Award Coupon or Notify Coupon Expire. From the AWD_AWARD_COUPONS table.
Coupon Expiry Date	Datetime		The date when the coupon expires. The time is included with all zeroes, for example, 2017-12-31;00:00:00. Blank unless related to the activity, such as for Issue Promo Award Coupon, Issue Entitlement Coupon, Automatic Redeem, or Notify Coupon Expire. From the AWD_AWARD_COUPONS table.
Issue Amount	Number	17,6	The amount issued. Included only when the activity is Issue Coupon or Issue Promo Award Coupon. From the ACTIVITY_AMOUNT in the AWD_ACCT_ACTIVITY table.
Redeem Amount	Number	17,6	The amount redeemed. Included only when the activity is Redeem or Automatic Redeem. From the ACTIVITY_AMOUNT in the AWD_ACCT_ACTIVITY table.
Balance Amount	Number	17,6	The balance of an E-Award after the activity. From the AWARD_BALANCE in the AWD_ACCT_ACTIVITY table.
Deal ID	Number	19,0	The unique number identifying a deal. Included only for an Entitlement Deal. From the AWD_ACCT_ETL_COUPONS table.
Deal Name	Varchar	64	The name of the deal. Included only for an Entitlement Deal. From the DLS_DEAL table.
External Deal ID	Varchar	32	The unique identifier of the Deal in Promote. Included only for an Entitlement Deal. From the DLS_DEAL table.
Max Redeem Count	Number	11,0	The maximum redeemable count for the deal. Included only for an Entitlement Deal. From the AWD_ENTITLEMENT_DEAL table.
Redeemed Count	Number	11,0	The redeemed count for this specific entitlement coupon record. Included only for an Entitlement Deal. From the AWD_ACCT_ETL_COUPONS table.
Redemption Balance	Number	11,0	The total count eligible for redemption. Calculated by subtracting the Redeemed Count from the Max Redeem Count. Included only for an Entitlement Deal.
Award Currency	Varchar	30	The currency defined for the award program. From the ACT_PROGRAM table.
Award Transaction Date	Datetime		The date the transaction took place. The time is included with all zeroes, for example, 2017-12-31;00:00:00. From the AWD_ACCT_ACTIVITY table.
Coupon Fully Redeemed Flag	Number	1	Set to 1 if the Coupon has been fully redeemed; otherwise, set to 0.
Coupon Expiry Flag	Number	1	Set to 1 if the Coupon Expiry Date has passed; otherwise, set to 0.
Transfer Out	Number	17,6	The amount transferred out. Included only when the activity is Transfer Out. From the ACTIVITY_AMOUNT in the AWD_ACCT_ACTIVITY table.
Transfer In	Number	17,6	The amount transferred in. Included only when the activity is Transfer In. From the ACTIVITY_AMOUNT in the AWD_ACCT_ACTIVITY table.

Scheduling Loyalty Award Transaction Sync Export for BI/Analytics

Generation of this file is configured through the Loyalty Award Transaction Sync Export option in Job Scheduling. The following options are displayed:

Figure D-7 Loyalty Award Transaction Sync Export Parameters

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 Program Export Sync

This section includes:

• Loyalty Program Sync Export File Format for BI/Analytics

• Scheduling Loyalty Program Sync Export for BI/Analytics

Loyalty Program Sync Export File Format for BI/Analytics

The Loyalty Program Sync export file name is from the Retail Insights Loyalty Program Data Sync Export Default Filename property, and should be set to W_RTL_LYL_PROG_DS for integration with Retail Insights.

The Loyalty Program Sync Export Sync file contains the following information by default, but the exported information is configurable with the help of Oracle consultants.

This information is found in the LYL_LOYALTY_PROGRAM table, the ACT_PROGRAM table, and the LYL_PROG_LVL table.

Table D-10 Loyalty Program Sync Export File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
LOYALTY_PROGRAM_ID	Varchar	32	Unique identifier for the loyalty program. From the LYL_LOYALTY_PROGRAM table.
LOYALTY_PROGRAM_NAME	Varchar	64	Display name for the loyalty program. From the ACT_PROGRAM table.
POINTS_NAME	Varchar	64	User-defined name for the loyalty program points. From the LYL_LOYALTY_PROGRAM table.
POINTS_CURRENCY_VALUE	Number	17,6	Value of points in currency. From the LYL_LOYALTY_PROGRAM table.
LOYALTY_PROGRAM_ACTIVE_FLAG	Number	3	Flag indicating if the loyalty program is active. Set to 1 if active. From the ACT_PROGRAM table.
LOYALTY_PROGRAM_START__DATE	Datetime		The date when the loyalty program goes into effect. From the ACT_PROGRAM table.
LOYALTY_PROGRAM_END_DATE	Datetime		The date when the loyalty program ends. From the ACT_PROGRAM table.
LOYALTY_PROGRAM_LEVEL_ID	Number	38	Unique identifier for a loyalty program level. From the LYL_PROG_LVL table.
LOYALTY_PROGRAM_LEVEL_NAME		64	Display name for the loyalty program level. From the LYL_PROG_LVL table.
LOYALTY_PROGRAM_LEVEL_ACTIVE_FLAG	Number	3	Flag indicating if the loyalty program level is active. Set to 1 if active. From the LYL_PROG_LVL table.
DEFAULT_PROGRAM_LEVEL_FLAG	Number	3	Flag indicating if this is the starting level for the loyalty program when an account is first created. Set to 1 if the default. From the LYL_PROG_LVL table.
PROGRAM_CURRENCY	Varchar	30	Default currency for the loyalty program. From the ACT_PROGRAM table.

Scheduling Loyalty Program Sync Export for BI/Analytics

Generation of this file is configured through the Loyalty Program Sync Export option in Job Scheduling. The following options are displayed:

Figure D-8 Loyalty Program Sync Export Parameters

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.

Segment Export Sync

The segment sync is always a full export rather than an update.

This section includes:

• Segment Export Sync File Format for BI/Analytics

• Customer Segment Attribute Metadata Export Sync File Format for BI/Analytics

• Customer Segment Attributes Export Sync File Format for BI/Analytics

• Customer Segment Association Export Sync File Format for BI/Analytics

• Customer Segment Category Export Sync File Format for BI/Analytics

• Scheduling Customer Segment Sync Export for BI/Analytics

Segment Export Sync File Format for BI/Analytics

The Segment Sync file name is from the Retail Insights Segment Sync Default Filename property, and should be set to W_RTL_CUSTSEG_DS for integration with Retail Insights.

The Segment 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 QRY_USER_QUERY table.

Table D-11 Segment Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
CUSTSEG_ID	Number	19	Unique identifier for a segment. From the QRY_USER_QUERY table.
CUSTSEG_NAME	Varchar	64	Name of segment that is displayed on screens. From the QRY_USER_QUERY table.
DATASOURCE_NUM_ID	Number	1	Hard-coded to 1.
CUSTSEG_TYPE		50	Set to RULE BASED.
If attributes whose names match the following exist, they are included in the file. This information may be mapped from Oracle Retail Advanced Science Engine Cloud Services.
AGE_RANGE	Varchar	30	Age group for the segment.
SEX_MF_CODE	Varchar	50	Gender for the segment.
FAMILY_SIZE	Number		Family size for the segment.
GENERATION_CODE	Varchar	50	Generation code for the demographic of the segment.
OCCUPATION_CODE	Varchar	50	Occupation code for the segment.
EDUCATION_BCKGND_CODE	Varchar	50	Educational background code for the segment.
ETHNICITY_CODE	Varchar	50	Ethnicity code for the segment.
NATIONALITY_CODE	Varchar	30	Nationality code for the segment.
RELIGION_CODE	Varchar	50	Religion code for the segment.
SOCAL_CLASS_CODE	Varchar	50	Social status class hierarchy code for the segment.
FAMILY_LIFE_CYCL_CODE	Varchar	50	Family life-cycle status code for the segment.
REGION_CODE	Varchar	50	Geographical region code for the segment.
METRO_AREA_SIZE	Number		Metropolitan region size for the segment.
POPULATION_DENSITY	Varchar	50	Type of population density for the segment, such as urban, suburban, or rural.
CLIMATE_CODE	Varchar	50	The climate code for the segment.
BENEFIT_SOUGHT_CODE	Varchar	50	The main benefits, such as health or taste, looked for by customers in the segment.
USAGE_RATE	Varchar		The usage rate for the segment.
READINESS_TO_BUY_CODE	Varchar	50	Indicates the segment’s customers’ buying mindset.
OCCASION_CODE	Varchar	50	Indicates the occasion that tends to stimulate purchases for the segment.
ACTIVITY_CODE	Varchar	50	Activity code assigned based on AIO survey results.
INTEREST_CODE	Varchar	50	Interest code assigned based on AIO survey results.
OPINION_CODE	Varchar	50	Indicates the political, environmental, or other opinion for the segment.
ATTITUDE_CODE	Varchar	50	Indicates the attitude of the segment.
VALUE_CODE	Varchar	50	Indicates the traditional and social values of the segment.
EFFECTIVE_START	Date		The date when the record in the source system becomes effective.
EFFECTIVE_END	Date		The date when the record in the source system is no longer effective.
ANNL_INCOME_RANGE	Varchar	50	The income range for the segment.
INTEGRATION_ID	Number	19	Unique identifier for a segment.
CUSTSEG_SRC_TYPE	Varchar		Set to CUSTOMER.
CUSTSEG_DESC	Varchar	64	The display name of the segment.

Customer Segment Attribute Metadata Export Sync File Format for BI/Analytics

Combined metadata file: If both the Customer sync and the Segment sync are run at the same time, the Customer Attributes Metadata is combined with the Segment Attributes Metadata to create a single metadata file for export.

Before being combined with the Segment Attributes metadata, the Customer Attributes Metadata Export Sync file just contains the following information about segment attributes.

Table D-12 Customer Segment Attribute Metadata Export Sync File Format for BI/Analytics

Field Name	Description
ATTR_NAME	The customer-defined name for a custom attribute.
SOURCE	Hard-coded to CE.
PHYSICAL_COL_NAME	Identifies whether the attribute is a string (alphanumeric), a date, or numeric. Possible physical column names for customer attributes are: CUSTSEG_ATTR[N]_NAME for a string attribute CUSTSEG_ATTR[N]_DATE for a date attribute CUSTSEG_ATTR[N]_NUM_VALUE for a numeric attribute
TABLE_NAME	Set to W_RTL_CUSTSEG_ATTR_DS, identifying the target table in the BI/Analytics system.
DESCRIPTION	The description of the attribute.
DATA_TYPE	Possible data types are: VARCHAR for a string attribute DATE for a date attribute NUMBER for a numeric attribute
DATASOURCE_NUM_ID	Hard-coded to 1.
INTEGRATION_ID	Concatenation of the attribute name + CE + ~ + the physical column name + ~ + the target table name, such as: <SEG_REG~CE~CUSTSEG_ATTR3_NAME~W_RTL_CUSTSEG_ATTR_DS>.

The file name is from the Retail Insights Attribute Metadata Sync Default Filename property, and should be set to W_RTL_INT_METADATA_G for integration with Retail Insights.

Customer Segment Attributes Export Sync File Format for BI/Analytics

The Customer Segment Attributes Export Sync file name is from the Retail Insights Segment Attributes Sync Default Filename property, and should be set to W_RTL_CUSTSEG_ATTR_DS for integration with Retail Insights.

The Customer Segment Attributes 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 QRY_SEGMENT_ATTRIBUTES table.

Table D-13 Customer Segment Attributes Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
CUSTSEG_ID	Number	19	Unique identifier for a segment. From the QRY_CUSTOMER_SEGMENT table.
CUSTSEG_TYPE	Varchar		Set to Rule Based.
CUSTSEG_SRC_TYPE	Varchar		Set to CUSTOMER.
DATASOURCE_NUM_ID	Number	1	Hard-coded to 1.
CUSTSEG_ATTR[N]_NAME or CUSTSEG_ATTR[N]_DATE or CUSTSEG_ATTR[N}_NUM_VALUE where [N] indicates the sequence of the attribute of that data type, and NAME or DATE or NUM_VALUE indicates the data type			The value of the attribute assigned to the segment. There can be multiple attributes of each data type (NAME, DATE, or NUM), and each is indicated in the related .CTX file by a unique sequence number and data type suffix. For example, the second DATE attribute included in the file is indicated by CUSTSEG_ATTR2_DATE. The attribute export file always includes 40 columns of attribute data, where: the first 20 columns are string attributes the next 10 columns are date attributes the last 10 columns are numeric attributes If fewer than the indicated number of string, date, or numeric attributes are passed, then the remaining columns for that attribute type are empty. For example, if there are two string attributes, then the first two columns are populated, and the remaining 18 columns are empty, as in the following example.
INTEGRATION_ID	Varchar	52	Unique identifier for a segment. From the QRY_CUSTOMER_SEGMENT table.

Sample Rows

The following is an example of data in the Customer Segment Attributes Export Sync file:

28|Rule Based|CUSTOMER|abcd||blue||||||||||||||||||||||||||||28|4.9|||||||||1|28|
26|Rule Based|CUSTOMER|char|false|red||||||||||||||||||2026-08-
22;00:00:00||||||||||26|1.1|||||||||1|26|

The information that might typically be included in the export file, based on the existing defined Segment Attribute types and the definitions in the metadata file, are:

• CUSTSEG_ID

• CATEGORY_ID

• CUSTSEG_NAME

• CUSTSEG_TYPE

• CUSTSEG_SRC_TYPE

• TOTAL_TRIP

• AVG_TRIP

• AVG_TRX_CNT

• AVG_PURCHASE

• SRC_EFF_FROM_DT

• SRC_EFF_TO_DT

• DATASOURCE_NUM_ID

• INTEGRATION_ID

Customer Segment Association Export Sync File Format for BI/Analytics

The Customer Segment Association Export Sync file name is from the Retail Insights Segment Customer Association Sync Default Filename property, and should be set to W_RTL_CUST_CUSTSEG_DS for integration with Retail Insights.

The Customer Segment Association 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 QRY_CUSTOMER_SEGMENT table.

Table D-14 Customer Segment Association Export Sync File Format for BI/Analytics

Field Name	Data Type	Maximum Length	Description
CUSTSEG_ID	Number	19	Unique identifier for a segment. From the QRY_CUSTOMER_SEGMENT table.
CUST_ID		32	Unique identifier for a customer in the segment. From the QRY_CUSTOMER_SEGMENT table.
DATASOURCE_NUM_ID	Number	1	Hard-coded to 1.
INTEGRATION_ID	Varchar	52	Concatenation of the CUSTSEG_ID + ~ + CUST_ID.

Customer Segment Category Export Sync File Format for BI/Analytics

Currently, an empty Customer Segment Behavior Export Sync File is produced.

Scheduling Customer Segment Sync Export for BI/Analytics

Generation of these files is configured through the Customer Household Sync Export option in Job Scheduling. If you select an export target system of BI/Analytics, the following options are displayed:

Figure D-9 Customer Segment Sync Export Parameters

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.

BI/Analytics Package Exporter

When running the export to Oracle Retail Insights, the last scheduled task should be to package the export sync files into a single .ZIP file. To create the packaged file correctly, the export job should include all BI/Analytics exports, including customer, customer address, customer attributes, household, and segment sync tasks.

One of the steps that the task completes before creating the .ZIP file is to combine the customer attribute metadata and the segment attribute metadata into a single metadata file.

To combine the two metadata files, the task temporarily renames the two metadata files:

The customer attribute metadata file is temporarily renamed as W_RTL_INT_METADATA_G.cst

The segment attribute metadata file is temporarily renamed as W_RTL_INT_METADATA_G.seg

The contents of the two metadata files are then combined into a single file named W_RTL_INT_METADATA_G.dat.

After the combined metadata file is created, the .ZIP file is created, including all of the BI/Analytics export sync files. This file is moved to the remote destination folder.

If the Retail Insights Delete Files After Copy Flag property is set to Yes, the last step is to delete the .ZIP file and export files from the application server.

• Scheduling the BI/Analytics Package Exporter

Scheduling the BI/Analytics Package Exporter

This task packages the export files to the BI/Analytics system, and must be the last task in a single job that includes all other exports to the BI/Analytics system, as in the following example.

Figure D-10 Task Packages Export Files to BI/Analytics

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.