Example: Automatically generating a set of data filters

Jim needs to create a set of data filters based on countries, cities, and states.

In Campaign, the customer tables have already been mapped and audience levels have been defined.

Obtaining the JDBC driver

Jim knows that his company's customer database is Microsoft™ SQL server. He downloads the appropriate Type 4 driver and places it on the machine where the Marketing Platform is installed, making a note of the name and path of the driver.

  • JDBC driver class name - com.microsoft.sqlserver.jdbc.SQLServerDriver
  • JDBC driver path - C:\tools\Java\MsJdbc\sqljdbc.jar

Obtaining information

Jim obtains the name, host, and port of the customer database, and the credentials he needs to connect to it.

  • Database name - Customers
  • Database host name - companyHost
  • Database port - 1433
  • User name - sa
  • Password - myPassword

Jim looks at the data in his company's customer database and sees that customers exist in every country, city, and state for which he wants to create a data filter. He determines that the Geographic table contains the fields he needs to specify fixed fields and profile fields for the data filters.

The following table illustrates the information Jim obtains about the customer fields and their Campaign mappings.

Table 1. Geographic table fields
Fields

(Physical name)

Fields

(Name in Campaign)

Data Data type
country Country
  • USA
  • France
  • Britain
java.lang.String
city City A finite set of distinct cities java.lang.String
state State A finite set of distinct states (or otherwise named regions, depending on country) java.lang.String
hh_id HouseholdID N/A java.lang.Long
indiv_id IndividualID N/A java.lang.Long

Jim learns that the audience names used in Campaign are household and individual. He notes that the Geographic table contains two audience fields.

  • The hh_id field corresponds to the household audience.
  • The indiv_id field in the Geographic table corresponds to the individual audience.

Because Jim must create one logical field for each audience, and one for each of the fixed and profile fields, he knows he needs a total of five logical fields.

Jim also knows he needs to group the data filters in a data configuration. He decides to name his data configuration Geographic.

Jim is now ready to create the XML.

Creating the XML

Here is the XML that Jim creates. Values based on the information he obtained or decided to use are shown in bold .


<ExecuteBatch>
                <!-- ********************************** -->
                <!--          Data configuration        -->
                <!-- ********************************** -->
  <name>SeedData</name>
  <operations>
      <ExecuteBatch>
        <name>DataFilters</name>
        <operations>
            <AddDataConfiguration>
                <dataConfiguration>
                    <id>1</id>
                    <name>Geographic</name>
                </dataConfiguration>
                </AddDataConfiguration>
        </operations>
      </ExecuteBatch>
                <!-- ********************************** -->
                <!--         Logical fields             -->
                <!-- ********************************** -->  
      <AddLogicalFields>
        <logicalFields>
            <LogicalField>
                <id>1</id>
                <name>Country</name>
                <type>java.lang.String</type>
            </LogicalField>
            <LogicalField>
                <id>2</id>
                <name>City</name>
                <type>java.lang.String</type>
            </LogicalField>
            <LogicalField>
                <id>3</id>
                <name>State</name>
                <type>java.lang.String</type>
            </LogicalField>
            <LogicalField>
                <id>4</id>
                <name>HouseholdID</name>
                <type>java.lang.Long</type>
            </LogicalField>
            <LogicalField>
                <id>5</id>
                <name>IndividualID</name>
                <type>java.lang.Long</type>
            </LogicalField>
            </logicalFields>
      </AddLogicalFields>
                <!-- ********************************** -->
                <!--       Generate data filters        -->
                <!-- ********************************** -->
      <GenerateDataFilters>
                <!-- ******************************************************* -->
                <!-- Specify the table to be scanned for unique combinations -->
                <!-- of values  from which data filters will be defined.     -->
                <!-- ******************************************************* -->
              <tableName>Geographic</tableName>
                <!-- ******************************************************* -->
                <!-- Identify the data configuration  with which             -->
                <!-- generated data filters will be associated.              -->
                <!-- ******************************************************* -->
             <configurationName>Geographic</configurationName>
              <!-- Specify the data source connection information. -->
             <jdbcUrl>
               jdbc:sqlserver://localhost:1433;databaseName=Customers
             </jdbcUrl>
             <jdbcUser>sa</jdbcUser>
             <jdbcPassword>myPassword</jdbcPassword>
             <jdbcDriverClass>
                    com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass>
             <jdbcDriverClassPath>
                    <string>C:\tools\Java\MsJdbc\sqljdbc.jar</string>
             </jdbcDriverClassPath>
                <!-- ********************************** -->
                <!--      Specify the fixed fields      -->
                <!-- ********************************** -->
              <fixedFields>
                  <FixedField>
                     <expression>USA</expression>
                     <logicalFieldName>Country</logicalFieldName>
                     <physicalFieldName>country</physicalFieldName>
                  </FixedField>
                  <FixedField>
                     <expression>France</expression>
                     <logicalFieldName>Country</logicalFieldName>
                     <physicalFieldName>country</physicalFieldName>
                  </FixedField>
                  <FixedField>
                     <expression>Britain</expression>
                     <logicalFieldName>Country</logicalFieldName>
                     <physicalFieldName>country</physicalFieldName>
                  </FixedField>
              </fixedFields>
              <!-- Specify the profile fields. -->
              <profileFields>
                  <ProfileField>
                     <logicalFieldName>State</logicalFieldName>
                     <physicalFieldName>state</physicalFieldName>
                  </ProfileField>
                  <ProfileField>
                     <logicalFieldName>City</logicalFieldName>
                     <physicalFieldName>city</physicalFieldName>
                  </ProfileField>
              </profileFields>
       </GenerateDataFilters>
                <!-- ********************************** -->
                <!--   Map physical to logical fields   -->
                <!-- ********************************** -->
       <ExecuteBatch>
              <name>addTables</name>
              <operations>
              <AddDataTable>
                  <dataTable>
                  <id>1</id>
                  <name>Geographic</name>
                  <fields>
                     <TableField>
                            <name>country</name>
                            <logicalFieldId>1</logicalFieldId>
                     </TableField>
                     <TableField>
                            <name>city</name>
                            <logicalFieldId>2</logicalFieldId>
                     </TableField>
                     <TableField>
                            <name>state</name>
                            <logicalFieldId>3</logicalFieldId>
                     </TableField>
                     <TableField>
                            <name>hh_id</name>
                            <logicalFieldId>4</logicalFieldId>
                     </TableField>
                     <TableField>
                            <name>indiv_id</name>
                            <logicalFieldId>5</logicalFieldId>
                     </TableField>
                  </fields>
                  </dataTable>
                  </AddDataTable>
              </operations>
       </ExecuteBatch>
                <!-- ********************************************************* -->
                <!--              Audience table associations                  -->
                <!-- ********************************************************* -->
       <ExecuteBatch>
              <name>addAudiences</name>
              <operations>
              <AddAudience>
                  <audience>
                  <id>1</id>
                  <name>household</name>
                  <fields>
                     <AudienceField>
                        <logicalFieldId>4</logicalFieldId>
                        <fieldOrder>0</fieldOrder>
                     </AudienceField>
                  </fields>
                  </audience>
              </AddAudience>
              <AddAudience>
                  <audience>
                  <id>2</id>
                  <name>individual</name>
                  <fields>
                     <AudienceField>
                       <logicalFieldId>5</logicalFieldId>
                       <fieldOrder>0</fieldOrder>
                     </AudienceField>
                  </fields>
                  </audience>
              </AddAudience>
              </operations>
       </ExecuteBatch>
                <!-- ******************************************************* -->
                <!--            Associate table-audience pairs               -->
                <!--               with data configuration                   -->
                <!-- ******************************************************* -->
       <ExecuteBatch>
              <name>addAudienceTableAssociations</name>
              <operations>
              <AddAudienceTableAssociation>
                 <audienceTableAssociation>
                     <audienceId>1</audienceId>
                     <tableId>1</tableId>
                     <configId>1</configId>
                 </audienceTableAssociation>
              </AddAudienceTableAssociation>
              <AddAudienceTableAssociation>
                 <audienceTableAssociation>
                     <audienceId>2</audienceId>
                     <tableId>1</tableId>
                     <configId>1</configId>
                 </audienceTableAssociation>
              </AddAudienceTableAssociation>
              </operations>
       </ExecuteBatch>
    </operations>
 </ExecuteBatch>

Populating the system tables

Jim has named his data filter XML file geographicDataFilters.xml and saved it in tools/bin directory under his Marketing Platform installation. He opens a command prompt and uses the datafilteringScriptTool utility to populate the data filter system tables.

The utility creates many data filters. In each data filter, the criteria are a country (the fixed field) and a unique combination of city and state obtained when the utility queried the database for records containing the fixed field value. All unique combinations of city and state are used for each country specified as a fixed field.

Assigning users and groups to the data filters

Finally, Jim logs in to the Marketing Platform with an account that has Admin access in Marketing Platform.

He knows that groups have already been set up in Marketing Platform with users assigned by city.

He goes to the Data Filter section and sees that the country, city, and state values from his data filters are available in the advanced search for data filters. He performs a search for a data filter, using Boston, a city in the USA, as a search criterion. The data filter for Boston appears in the search results.

Next, Jim performs a search for the Boston user group, which has been set up in Marketing Platform to hold all field marketers who are responsible for marketing to customers in Boston. The Boston group appears in the search results.

Jim then selects the group and the data filter in the search results, and assigns the group to the data filter by clicking the Assign button.

He continues to perform searches for data filters and groups until all assignments are completed.