Adding a customer segment attribute to the customer segment export file
You can customize the customer segment export feature in Management Center to include more customer segment attributes in the exported file.
Before you begin
About this task
- Email address (ADDRESS.EMAIL1)
- Logon ID (USERREG.LOGONID)
- Customer title (ADDRESS.PERSONTITLE)
- Customer first name (ADDRESS.FIRSTNAME)
- Customer last name (ADDRESS.LASTNAME)
If your business users require more customer information in the export file, you 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.
- City (ADDRESS.CITY)
- State/Province (ADDRESS.STATE)
- Country (ADDRESS.COUNTRY)
- Phone number (ADDRESS.PHONE1)
- Gender (USERDEMO.GENDER)
- Marital status (USERDEMO.MARITALSTATUS)
Procedure
- 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 your
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,
If you created properties files for other supported languages, add the text for the attributes in the appropriate language to the files.customerSegmentExport_Customized_ADDRESS_column=Customer segment BUSINESSTITLE and CITY customerSegmentExport_Customized_USERDEMO_column=Customer segment GENDER and AGE customerSegmentExport_Customized_column=Customer segment REGISTRATIONUPDATE
- Save and close the files.
For an example of the complete files, see the sample customCustomerSegmentExportAttributesLOB.properties and customCustomerSegmentExportAttributesLOB_en_US.properties files in the CustomerSegmentExportAttribute.zip compressed file.
- Go to the LOBTools\Java Resources\src\com.mycompany.commerce.marketing.client.lobtools.properties directory.
- Register the new text properties files in a resource bundle.
- Go to the LOBTools\WebContent\WEB-INF\src\lzx\mycompany\marketing directory. If the directory structure does not exist, create the directory structure.
- Right-click the marketing directory. Click New > Other > General > File and click Next. Enter a name for the file, such as extMarketingManagementResourceBundle.lzx, and click Finish.
- Open the extMarketingManagementResourceBundle.lzx file
for editing. Add the resource bundle keys for the new customer segment
attribute options. For example,
For an example of how to define the resource bundle keys, review the LOBTools\WebContent\WEB-INF\src\lzx\commerce\marketing\restricted\MarketingManagementResourceBundle.lzx file.<library> <class name="extMarketingResourceBundle" extends="wcfResourceBundle" baseName="com.mycompany.commerce.marketing.client.lobtools.properties.customCustomerSegmentExportAttributesLOB"> <wcfResourceBundleKey name="customerSegmentExport_Customized_ADDRESS_column"/> <wcfResourceBundleKey name="customerSegmentExport_Customized_USERDEMO_column"/> <wcfResourceBundleKey name="customerSegmentExport_Customized_column"/> </class> <extMarketingResourceBundle id="extMarketingResources"/> </library>
- Save and close the file.
For an example of a complete file, see the sample extMarketingManagementResourceBundle.lzx file in the CustomerSegmentExportAttribute.zip compressed file.
- Include the extMarketingManagementResourceBundle.lzx file
in the marketing extensions library to make the file available for
use.
- Go to the LOBTools\WebContent\WEB-INF\src\lzx\commerce\marketing directory.
- Open the MarketingExtensionsLibrary.lzx file for editing.
- Add the following code to the file to point to you customized
resource bundle file.
<library> <!-- File to add customer libraries --> <include href="../../mycompany/marketing/extMarketingManagementResourceBundle.lzx"/> </library>
- Save and close the file.
For an example of a complete file, see the sample MarketingExtensionsLibrary.lzx file in the CustomerSegmentExportAttribute.zip compressed file.
- 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\config\commerce\marketing\objectDefinitions directory.
- Open the CustomerSegmentExportPrimaryObjectDefinition.def file for editing.
- Add the object definition for the customer segment attribute
that you want to add to the file.For example,
For examples of how to define an object definition, review the existing definitions in the CustomerSegmentExportPrimaryObjectDefinition.def file and other definition files.<!- 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>
- Save and close the file.
For an example of a complete file, see the sample CustomerSegmentExportPrimaryObjectDefinition.def file in the CustomerSegmentExportAttribute.zip compressed 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\config\commerce\marketing\propertiesViews directory.
- Open the CustomerSegmentExportPropertiesView.def file for editing.
- Add the property view definition for the customer segment
attribute that you want to add to the Marketing tool.For example,
For more examples of how to create properties view definition, review existing definitions in the CustomerSegmentExportPropertiesView.def file and other properties files.<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" />
- Save and close the file.
For an example of a complete file, see the sample CustomerSegmentExportPropertiesView.def file in the CustomerSegmentExportAttribute.zip compressed file.
- Create an implementation Java class to handle the new attribute
options. For instance, you can name your class.For instance, you can name this custom class com.mycompany.marketing.segment.CustomizedCustomerSegmentExtractCmdImpl, where mycompany is the name of your company.
You must use this custom implementation class to extend the default CustomerSegmentExtractCmdImpl implementation class for the CustomerSegmentExtractCmd command class.
For an example of a complete implementation Java class, see the sample CustomizedCustomerSegmentExtractCmdImpl.java file in the CustomerSegmentExportAttribute.zip compressed file.
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. You can use the following setter methods to help you construct your class:setInclude1stAdditionalADDRESScolumns(String)
- This method adds columns for information from the ADDRESS table.
You can include multiple columns by using the format:
setInclude1stAdditionalADDRESScolumns("Column1, Column2, Column3");
setInclude2ndAdditionalUSERDEMOcolumns(String)
- This method adds columns for information from the USERDEMO table.
You can include multiple columns by 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 your customization is more complex, you 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, you can define and use custom SQL for to retrieve customer information. When you 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 you are creating your custom implementation class, call the following setter methods to use your 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:
Where"SELECT TABLE_<n>.COLUMN_1, ...CLOUMN_N FROM TABLE_1, TABLE_2, ...TABLE_N WHERE TABLE_<m>.MEMBER_ID IN (<QUERY>)"
(<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. You 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, you must use thesetFileHeader(String)
setter method to change the sequence of the column headings to match the sort order for your SQL. Your file header can resemble the format:"EMAIL,LOGONID,GENDER,AGE,CITY"
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
Your CustomizedCustomerSegmentExtractCmdImpl.java file can include the following code to override the SQL that the export feature uses to retrieve customer information: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
//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 your 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');
- Right-click the LOBTools project and select Build OpenLaszlo Project.
- 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 in to Management Center as a Marketing Director.
- Open the Marketing tool and select your store.
- In the Explorer view, click Customer Segments. The list of all customer segments for your 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 that you want 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.