Management Center: Adding a customer segment attribute to the customer segment export file

(Developer)
Add a customer segment attribute to the customer segment export file
We can customize the customer segment export feature in Management Center to include more customer segment attributes in the exported file.
Note: To ensure the security of customer information, follow all rules and regulations pertaining to handling, storing, and accessing customer information.

Before beginning
Ensure that the environment is setup to export customer segment data from our production environment.To test our customization in the development environment with customer segment data from the production environment, complete the following task:

Set up the development environment to export customer segment information from the production database.

Task info
By default, business users can include the following customer segment attributes in a customer segment export file:

Email address (ADDRESS.EMAIL1)
Logon ID (USERREG.LOGONID)
Customer title (ADDRESS.PERSONTITLE)
Customer first name (ADDRESS.FIRSTNAME)
Customer last name (ADDRESS.LASTNAME)
Business users can also select to include customers that did not opt in to receive marketing email.
If your business users require more customer information in the export file, we can customize the export feature to include information from any database table, including custom tables. The table, however, must have a matching value or relationship to the MEMBER_ID column of the ADDRESS table. For example, we can customize the customer segment export to include any of the following types of customer information for the list of customers in a segment:

City (ADDRESS.CITY)
State/Province (ADDRESS.STATE)
Country (ADDRESS.COUNTRY)
Phone number (ADDRESS.PHONE1)
Gender (USERDEMO.GENDER)
Marital status (USERDEMO.MARITALSTATUS)

Procedure

Customize the Marketing tool to display new customer segment attribute options for export
Open WebSphere Commerce Developer. Switch to the Enterprise Explorer view.
Define the Marketing tool text properties for the customer segment attribute options.
Go to the LOBTools\Java Resources\src\com.mycompany.commerce.marketing.client.lobtools.properties directory.
If the package does not exist, create the com.mycompany.commerce.marketing.client.lobtools.properties package.
Right-click the com.mycompany.commerce.marketing.client.lobtools.properties package. Click New > Other > General > File > Next. Enter a name for the file, such as customCustomerSegmentExportAttributesLOB.properties, and click Finish.
Repeat the previous step to create the properties files that are to contain the Marketing tool translatable text for our default language. For instance, customCustomerSegmentExportAttributesLOB_en_US.properties. If users access the Marketing tool in different languages, create the properties files for any language that must be supported.
Open the customCustomerSegmentExportAttributesLOB.properties and customCustomerSegmentExportAttributesLOB_en_US.properties files for editing. Add the text for the customer segment attribute options to the files. For example,
customerSegmentExport_Customized_ADDRESS_column=Customer segment BUSINESSTITLE and CITY
customerSegmentExport_Customized_USERDEMO_column=Customer segment GENDER and AGE
customerSegmentExport_Customized_column=Customer segment REGISTRATIONUPDATE
If we created properties files for other supported languages, add the text for the attributes in the appropriate language to the files.
Save and close the files.
Register the new properties in the resource bundle.

Expand LOBTools > WebContent > WEB-INF > src > xml > commerce > shell.
Right-click the shell folder; then, click New > Other.
Name your new file. For example, extMarketingManagementResourceBundle.xml.
Click Finish. The file opens in the default XML editor.
Add the resource bundle keys for the new customer segment attribute options to define the custom resource bundle.
Save and close the file.
Register the resource bundle file wherever it is used. For example:
<dependency localName="extMarketingManagementResourceBundle" moduleName="cmc/MyCompany/extMarketingManagementResourceBundle"/>
Create the customer segment export object definition to include the definition for the new customer segment attribute options. The object definition adds support for the customer segment attribute to be used in a customer segment export object within the Management Center framework.
Go to the LOBTools\WebContent\WEB-INF\src\xml\commerce\marketing\objectDefinitions directory.
Open the CustomerSegmentExportPrimaryObjectDefinition.xml file for editing.
Add the object definition for the customer segment attribute to add to the file. For example,
<!- The property for specifying whether or not to include customer Customized column. ->
<PropertyDefinition displayName="Customized ADDRESS column"
  propertyName="Customized_ADDRESS_column" type="integer">
</PropertyDefinition>

<!- The property for specifying whether or not to include customer Customized column. ->
<PropertyDefinition displayName="Customized USERDEMO column"
  propertyName="Customized_USERDEMO_column" type="integer">
</PropertyDefinition>

<!- The property for specifying whether or not to include customer Customized column. ->
<PropertyDefinition displayName="Customized column"
  propertyName="Customized_column" type="integer">
</PropertyDefinition>
For examples of how to define an object definition, review the existing definitions in the CustomerSegmentExportPrimaryObjectDefinition.xml file and other definition files.
Save and close the file.
Create the customer segment export properties view definition for the new customer segment attribute options. The definition indicates how to render the option for users to include the new customer segment attribute in the export file. For example, a check box to indicate that the attribute information is included in the export file.
Go to the LOBTools\WebContent\WEB-INF\src\xml\commerce\marketing\propertiesViews directory.
Open the CustomerSegmentExportPropertiesView.xml file for editing.
Add the property view definition for the customer segment attribute to add to the Marketing tool. For example,
<PropertyCheckbox
  helpText="Customized_ADDRESS_column_helpText"
  name="Customized_ADDRESS_column_CheckBox"
  promptText="${mktMarketingResources.customerSegmentExport_Customized_ADDRESS_column}"
  trueValue="1" falseValue="0"
  propertyName="Customized_ADDRESS_column" />
<PropertyCheckbox
  helpText="Customized_USERDEMO_column_helpText"
  name="Customized_USERDEMO_column_CheckBox" 
  promptText="${mktMarketingResources.customerSegment_Customized_USERDEMO_column}"
  trueValue="1" falseValue="0"
  propertyName="Customized_USERDEMO_column" />
<PropertyCheckbox
  helpText="Customized_column_helpText"
  name="Customized_column_CheckBox" 
  promptText="${mktMarketingResources.customerSegmentExport_Customized_column}"
  trueValue="1" falseValue="0"
  propertyName="Customized_column" />
For more examples of how to create properties view definition, review existing definitions in the CustomerSegmentExportPropertiesView.xml file and other properties files.
Save and close the file.
Extend the CustomerSegmentExtractCmdImpl command class to customize the customer segment export feature to include the new customer segment attributes.
Create an implementation Java class to handle the new attribute options. For instance, we can name this custom class com.mycompany.marketing.segment.CustomizedCustomerSegmentExtractCmdImpl, where mycompany is the name of your company.
We must use this custom implementation class to extend the default CustomerSegmentExtractCmdImpl implementation class for the CustomerSegmentExtractCmd command class. In your new class, extend the com.ibm.commerce.marketing.segment.commands.CustomerSegmentExtractCmdImpl command class. To extend this class, add code in your new file to call setter methods to retrieve the customer segment attributes to add to the export file. We can use the following setter methods to help you construct your class:
setInclude1stAdditionalADDRESScolumns(String)
This method adds columns for information from the ADDRESS table. We can include multiple columns using the format:
setInclude1stAdditionalADDRESScolumns("Column1, Column2, Column3");
setInclude2ndAdditionalUSERDEMOcolumns(String)
This method adds columns for information from the USERDEMO table. We can include multiple columns using the format:
setInclude2ndAdditionalUSERDEMOcolumns("Column1, Column2, Column3");
setIncludeLastAdditionalCustomTableColumns(String, String, String)
This method adds columns for information from custom tables. When you set this method, identify the table, columns, and the matching value or relationship from the custom table to the ADDRESS.MEMBER_ID column. Use the following format to define this method:
setIncludeLastAdditionalCustomTableColumns("Table", "column1, column2", "relationship to ADDRESS.MEMBER_ID")
For example, the following code calls these setter methods to retrieve customer information from the ADDRESS, USERDEMO, and USERS tables. The relationship between the USERS table and the ADDRESS.MEMBER_ID column is through the USERS.USERS_ID column.
public class CustomizedCustomerSegmentExtractCmdImpl extends CustomerSegmentExtractCmdImpl implements CustomerSegmentExtractCmd{
...
  public void performExecute() throws ECException
  {
    ..
    setInclude1stAdditionalADDRESScolumns("CITY, STATE, COUNTRY");
    setInclude2ndAdditionalUSERDEMOcolumns("GENDER,AGE");
    setIncludeLastAdditionalCustomTableColumns("USERS","PROFILETYPE, LANGUAGE_ID", "USERS_ID");
    super.performExecute();
    ...
  }
...
}
Note: If our customization is more complex, we can use the following setter methods to override the default SQL that the export feature uses to retrieve customer information. By overriding the default SQL, we can define and use custom SQL for to retrieve customer information. When we use the following setter methods to define custom SQL, the preceding setter methods that customize the default SQL are ignored since that SQL is no longer used. When we are creating our custom implementation class, call the following setter methods to use our custom SQL. These setter methods are defined in the CustomerSegmentExtractCmd command class.
setSQLSelect(String)
This method overrides the SQL select statement that the customer segment export feature uses to retrieve information. Define the value of the SQL statement in the following format:
"SELECT TABLE_<n>.COLUMN_1, ...CLOUMN_N FROM TABLE_1, TABLE_2, ...TABLE_N WHERE TABLE_<m>.MEMBER_ID IN (<QUERY>)"
Where (<Query>) is the list of MEMBER.MEMBER_ID values for the registered customers in the customer segment. The customer segment export feature uses the com.ibm.commerce.membergroup.commands.ListUsersInStoreMemberGroupCmd command to generate the list of member ID values for the customer segment that a user selects in the Marketing tool. We cannot customize the retrieval of this list for the customer segment export feature.
setSQLOrderBy(String)
This method overrides the ORDER BY SQL statement that defines how customer information is sorted. Define the value of the SQL statement with the following format:
"ORDER BY TABLE_<x>.COLUMN_<y> ASC"
setFileHeader(String)
This method overrides the file header of the export file. Whenever you use the setSQLSelect(String) setter method, we must use the setFileHeader(String) setter method to change the sequence of the column headings to match the sort order for our SQL. Your file header can resemble the format:
"EMAIL,LOGONID,GENDER,AGE,CITY"
The export feature uses the preceding setter methods whenever you set the methods to have non-null values. The setter methods combine to create an SQL statement that retrieves customer segment attribute information. The complete SQL statement uses the following SQL format:
SELECT T1.C1, T2.C2, T3.C3 
FROM T1, T2, T3 
WHERE T1.C2=T2.C3 AND T1.C3=T3.C4 AND T1.MEMBER_ID 
IN (<Query>) 
ORDER BY T1.MEMBER_ID ASC 
As an example, the following SQL statement overrides the default SQL to retrieve information for the customer email address, title, first name, last name, and logon ID
SELECT LOWER(T1.EMAIL1) AS LOWER_EMAIL1, T1.PERSONTITLE, T1.FIRSTNAME, T1.LASTNAME, T2.LOGONID
FROM ADDRESS T1, USERREG T2
WHERE T1.MEMBER_ID=T2.USERS_ID AND T1.STATUS='P' AND T1.SELFADDRESS=1 AND T1.MEMBER_ID 
IN (<QUERY>)
ORDER BY LOWER_EMAIL1 ASC
Your CustomizedCustomerSegmentExtractCmdImpl.java file can include the following code to override the SQL that the export feature uses to retrieve customer information:
//Customize SQL query to overrides the SQL select statement 
//that the customer segment export feature uses to retrieve information.
		
String customOrderby = "ORDER BY LOWER_EMAIL1 DESC";
String customHeader = "EMAIL, PHONE1, ADDRESS1 ";
StringBuilder sb = new StringBuilder("SELECT ");
sb.append(
  "LOWER(T1.EMAIL1) AS LOWER_EMAIL1, T1.PHONE1, T1.ADDRESS1, T3.PASSWORDCREATION 
   FROM ADDRESS T1, USERDEMO T2, USERREG T3 ")
  .append("WHERE T1.MEMBER_ID=T2.USERS_ID")
  .append(" AND T1.MEMBER_ID=T3.USERS_ID ")
  .append("AND T1.MEMBER_ID ").append("IN ").append("(<QUERY>)");

String customSQL = sb.toString();
setSQLSelect(customSQL);

//Override the ORDER BY SQL statement that defines how customer information is sorted
setSQLOrderBy(customOrderby);

//Override the file header of the export file.
setFileHeader(customHeader);
Update the customer segment export commands to include the new customer segment attributes.
Open a connection to the database.
Run the following SQL statement to update the implementation class of the CustomerSegmentExtractCmd class in the CMDREG database table to be your new custom implementation class:
INSERT INTO cmdreg (STOREENT_ID, INTERFACENAME, DESCRIPTION, CLASSNAME) 
VALUES (0, 'com.ibm.commerce.marketing.segment.commands.CustomerSegmentExtractCmd', 
'customized controller command to create Customer Segment Export', 
'com.mycompany.marketing.segment.commands.CustomizedCustomerSegmentExtractCmdImpl');
Republish the application to pick up the LOBTools changes.
Start or restart the WebSphere Commerce Test Server for the changes to take effect. Right-click your server and select either Start or Restart.
Verify that the customer segment attributes can be included when a customer segment is exported with the Marketing tool.

Log on to Management Center as a Marketing Director.
Open the Marketing tool and select the store.
In the Explorer view, click Customer Segments. The list of all customer segments for the store displays.
Right-click a customer segment. Click Export Customer Segment.
In the Export Customer Segment tab, enter the Name for the export CSV file.
Select the attribute option that you added and select any other customer information to include in the file.
Click Save to begin the export process. Since customer segments can include many customers, the customer segment export process can take time.
In the Explorer view, click Customer Segment Exports.
Right-click the customer segment export. Click Download Customer Segment. Save the file to your local file system. Open the file and verify that the information for the attribute you added is included in the file.
Related concepts
Customer segments

Related tasks
Adding a new customer segment attribute to Management Center
Exporting customer segments from the production database in a development environment
Customizing customer segments
Manage customer segments
Exporting customer segment information

Related reference
Customer segment attributes

Related information:

CustomerSegmentExport Cmd