Custom list management process (Sales Query Templates)

Introduction

This white paper provides an overview of an end to end solution to custom list management process. In today’s business scenario, very often we come across requirements, where client wants to generate a list of Parties/customers for different Campaigns. The Campaign can be Sales or Email and user can be Sales one or Marketing one. In this white paper we will discuss on an end to end solution how we can create custom list management process for different conditions/attributes/parameters based on which Sales/Marketing can get the  Parties/Customer list for Sales/Email Campaigns.

Business Case

In Oracle marketing, Very often clients want to get the list of parties/customers based on their own conditions as per their business need, which seeded Sales Query Template fails to provide, in such cases we need to provide Custom list management process based on their conditions. This custom list management process consists:

1.    Creation of custom view based on business requirements.

2.    Custom Data Source creation based the custom view created above.

3.    Custom Sales Query Template based on the custom Data Source created above.

Technology Used

The following technologies will be used while designing the solution:

1.    PL/SQL

Solution Overview

      Prerequisites

                  User should have Audience Administrator access.

      Data Base Table/View

Create custom database table/view based on your business requirements which will include all the attributes/conditions in form of columns.

Ex. A database view named XX_TEST_V has been created.

Prerequisites – You should have correct database access.

        Data Source     

Data sources are a fundamental component of the list management process. It helps the list creating process. Data can be stored in the application and use them to manage the audience for their products.

In short, they determine the type of data retrieved for lists.

Data sources determine:

• Attributes available to the end user during list creation

• TCA information mapping (when remote lists are migrated to a local instance)

• Data available in list entry

• Attributes available for splitting and charting

• Deduplication rules

Data sources map columns from a source table (or view) to the marketing list entries table AMS_LIST_ENTRIES. Once mapped, data from the table or view can be used for lists.

Data sources can be either child or parent. The parent data source determines the type of list that will be created. Usually, the attributes included in the parent data source are mapped to the list entries. Each parent data source also includes seeded templates and de-duplication rules. A child data source provides additional information about the parent. A parent can have an unlimited number of child data sources associated.

Creation of Data Source

If the seeded parent data sources do not meet your business requirements you can

create your own custom data source. When doing so, you can point to the Oracle TCA.

You can also use data that resides in a remote location

Prerequisites – You should have a table/view ready to use.

Configuring Data Source Attributes

When creating or updating data sources you will define the attributes for it. Using the

data source attribute pages, you can define data source attributes for list management

as well as data mining. The following table gives the descriptions for data source

attributes.

Data Sources Attributes

Attribute Name	Description
Attribute	The attributes of the table or view that the data source references. The values are displayed as they exist in the table or view
Display Name	Allows you to change the display name for the attributes. The values entered in this column are displayed to the end user during the list creation process.
Map to List Entries	Use this field to map or organize how this data source is displayed in the list entries screen for the end user. Note :- These are actually columns of AMS_LIST_ENTRIES table.
Display in List Entries	Use this checkbox to indicate that you want this data source to display data (by default) in the List Entries page for the end user.
Use for Split	The end user has the option to split a list by attribute. Therefore, as the administrator, when defining data source attributes, you must specify that it is available for splitting. Note :  If you want to use for splitting you must mapthe attribute.
Define LOV Chart	Selecting this icon enables you to define LOV and chart ranges for this data source. If you are charting an attribute, you must also map to list entries.
LOV	If a list of values are defined for this attribute, then you will see a check in this column.
Chart	If a chart is defined for this attribute, then you will see a check in this column.
Enabled	Place a check in this checkbox to enable the attribute for the data source. If this checkbox is left unchecked, this attribute will not be available for use in the Sales Query template creation process.

Defining the List of Values and Charts for Data Sources

When defining data source attributes, you can choose to define a list of values that

correspond to it. This list of values can also be used to drive the chart ranges. This

reduces the chance of error because you are limiting the data the user can select.

To define the LOV and chart attribute, login as a user that has the Audience

Administrator responsibility and navigate to the Administration Dashboard.

Notes

• Locate the Data Source you want to configure and in the Attributes mid-tab, select

the Define, LOV chart icon.

• In the Add List of Values Using drop-down, select one of the following:

• Manual Entries: This option enables you to manually define the LOV for the

attribute. If using this option, select "Add Another Row" and enter a value code

and display name.

• Value code: Exact value (as it appears in the database). For example, the value

code for country could be CAN for Canada.

• Display name: Text box that allows you to change the code name to a more

user-friendly display name. For example, the display name for CAN could be

Canada.

• Another List of Values: Allows you to pick a specific list of values that have

previously been defined for similar attribute. This option allows you to re-use

the LOV that has previously been defined. This will save you time. Using this

option you essential share the LOV with other attribute, therefore changing the

definition of the LOV will affects all attributes sharing this LOV.

• List of Value Name: Specify the values that you'd like this attribute to have. For

example, if you'd like the attribute "COUNTRY" to have "United States" and "

Canada" as the list of value options, select them here.

• Custom SQL: Using this option, you can define the list of values using data

retrieved with SQL logic. Use the Custom SQL box to enter an SQL statement.

           

***Note: If you define an SQL query for an LOV, ensure that it does not

return a null value for the columns you selected.

        Set Up Details For Data Source

q     Click On APPLY Button

q     In the next screen provide the “Description” same as the name          

q     Here you will get the columns associated with view selected (Ex. XX_TEST_V here) as Attributes

q     Do the following changes against Display Name, Map To List Entries, Display In List Entries and Define LOV, Chart

How To Change “Display Name”

q   Just update the appropriate Display Name against each attributes.

How to “Map To List Entries”

q   Click on ‘Search’ symbol, right side of the Map to List Entries column.

q   Search by Column name and select the appropriate column.

How to do “Display in List Entries”

Ø  Check the check box against the attributes, which you want to show in the List/Campaign result.

How to do “Define LOV, Chart”

***Note: If you define an SQL query for an LOV, ensure that it does not

return a null value for the columns you selected.

Ø  Click on the symbol ‘Define LOV, Chart’ ageist an attribute

Ø  Select the appropriate option for “Add List of Values Using”

                   >> Steps to do when you select “Custom SQL” as “Add List of Values Using”

v  Click on “Go”

v  Give the appropriate “List of Values Name” and “Custom SQL” value.

v  Click on “Validate SQL”

v  It will give the confirmation of valid query, click on “Apply”

>> Steps to do when you select “Another List of Values” as “Add List of Values Using”

v  Case when you want to reuse the same LOV

v  Click on “Go”

v  It will Create a “Search” Button against “List of Value Name”

v  Search (Click on the search Button) the appropriate LOV and Select.

v  It will automatically populate Custom SQl, validate query and apply, as you did before.

q   Steps to do when you select “Manual Entries” as “Add List of Values Using”

v  Click on “Go”

v  Then Click on “Add Another Row” Button

v  Give the appropriate “List of Values Name” and add as many rows you need to add and “Apply” button.

Sales Query Template

Sales Query Templates enable the end users to quickly and easily create lists. When creating a list, end user hastwo different list template options to choose from:

• Standard List Template: You can specify conditions using the Natural Language

Query (NLQ) template, just as you would naturally think of them. You can define

your selection criteria by selecting the operator and the corresponding value for

each condition.

• Parameterized SQL List Template: The Parameterized SQL List Template is based

                   on an SQL statement with parameters you provide to your user. Users must enter

 values from the selection parameters while creating a list.

Creation Of Sales Query Template

Custom Sales Query Template can be created with additional user-defined Conditions to meet            business requirements. These query templates can be based on the out-of-box data sources (Organization Contacts, Persons, Organizations) or any user-defined data source (for example, an industry-specific data mart). You can create these templates using either the Standard or Parameterized SQL template options.

Prerequisites – You should have a Data Source ready to use

Mapping Table of Sales Query Templates (Attributes)

You must configure the attributes that you will use to create a query, which are actually the parameters for the query while getting the Campaign List.

Attribute Name	Description
Attributes	The attributes that you define for the query template. These are actually the parameters (where clause) of the query used at the time of Campaign List creation
Attribute Display Name	The name of the attribute as it is displayed in the table. For example: an attribute “ZIP Code “might have “Postal Code” as the display name.
Available Operators	These are specific operators that you may choose from to add value to the attribute. For example: you can define the operator 'is between' , 'is after' for the “Contract Date”. ***Note :- You can choose more than one operator for an attribute.
Operator Default	Choose the default value of the operator
Value	List of values.
Value Display	Description against the attribute what to do, whether to enter the value manually or pick from the LOV
Default	If checked, this value is the default.
Mandatory	If checked, this attribute is the mandatory
In Use	Shows if the attribute is in use already. ***Note :- If it is checked, you cannot modify (update/delete) that attribute.

Set Up Details For Sales Query Templates

Go to Marketing Administrator Responsibility ->Campaign Dashboard

                ->  Right side in Related Link Click on Audience Workbench

                ->  Right side in Related Link Click on Audience Administrator

                ->  Right side in Shortcuts Click on Create Query Template

q   Enter the following values and Click on ‘Continue’ button

Field	Value
Query Template Name	Test Sales Query Template
Query Template Type	Standard
Data Source Name	Test  Data Source
Purpose	It is required to select a purpose. Purpose types are seeded, however, you can extend the purpose type lookup. Seeded Purpose Types: • Cross Sell: Used for creating lists for the cross selling purposes. • Customer Acquisition: Used for creating lists for customer acquisition purposes. • Customer Retention: Used for creating lists for customer retention purposes. • General Purpose: Used for creating general lists (not tied to a specific purpose). • Lead Maturation: Used for creating lead maturation lists • Up Sell: Used for creating lists for up selling purposes. >> Select as per your requirement, here we have selected “ALL”

      Add Following attributes by clicking ‘Add Another Row’ Button.

Ø  How to add an attribute

      Click on Search button ‘Select the Attribute Value’ for “Attributes”

      It will open an window with all the columns associated with the view linked with the related Data Source, ‘Select to Expand’ all the attributes/columns

Creating Self Joins for Complex Queries - * Not in the scope of this implementation

In Oracle Marketing you can create a Self Join for complex queries. A Self Join is a query in which a table is joined to itself. You can use a Self Join to compare the values in a column with other values in the same column of the same table. This way you can get running counts and running totals in the SQL query. You can create a Self Join at the attribute stage.

        Select the appropriate attribute

      Now click on the Search button for ‘Available Operators’ and select the appropriate operator/operators.

      Next to click on Search button for ‘Operator Default’ and select the operator defaulted

   

      Steps to select ALL VALUE for “Value “

      Click on Search button for “Value” and select ‘List of Values’ from LOV and click on Go

      Select all the values populated

v  Example 1

v  Example 2

      Based on the operator selected please enter user friendly text in the ‘Value Display’ field, like “:Enter a Value”, “:Pick any value”

      Click on ‘Validate’ for ‘SQL validation’ and then on ‘Back’

      After adding all the attributes and associated values, click on ‘Finish’ button

                              *** We are done with the Setups***

Verification Checklist

Include a subset of test steps that will confirm that the setup has been done properly and working fine.

      Please navigate to Marketing Administrator Responsibility -> Campaign Dashboard -> Audience Workbench. This should open Activities Page. Click on Create List button.

      Enter the required fields

Field	Value
List Name	xx_test_list
Audience	Test Data Source
List Creation Method	Standard
List Template Name	Test Sales Query Template

      Click on ‘Continue’ button

      In next window default conditions/parameters/attributes will automatically be populated in ‘Selection’ section

      Enter the values for those parameters.

      Add more conditions by clicking on ‘Add Conditions’ button

      Enter the values for the selected conditions and click on ‘Finish’

      Click on ‘Generate List’

      Refresh list status to generate the list ‘Status’ will change from draft->Scheduled-> Generating > Available

List Generation Status

• Draft: when the recalculation table is not available.

• In Progress: when you click the Apply button in the NLQB template definition

page. This implies that the concurrent request is submitted and recalculation table

generation is in process.

• Available: when the recalculation table generates successfully.

• Failed: when the recalculation table generation fails. Click the Apply button againto restart the recalculation table generation process

      After changing the status to ‘Available’, check the generated list by clicking on ‘Entries’ tab.

      Check the list summary by clicking on Selections->statistics tab.

Important Points To Remember/ Limitations

There are few important points to remember while creating the custom list management process.

v  Unique key limitations.

Ø  While defining Data Sources, you need to mention the Unique Key, which should be one of the columns of the view used.

Ø  Now while defining Sales Query Templates we define different parameters but Unique Key gets priority over all others and be seen as duplicate record in Campaign/List result.

Ex:- suppose we defined Party_id as a Unique Key, and party name, City, State, Service Contract etc. as other parameters in Sales Query Template.

While getting the Campaign/List we passed the parameter State as ‘WB’ and it gave three results as :

246836 Cognizant Technology Solutions      Kolkata           WB    23789

246836 Cognizant Technology Solutions      Bardhman      WB    68456

246836 Cognizant Technology Solutions      Ramput Hat   WB    98799     

Even if these three rows are different, campaign/List results will be treated as Duplicate results, because Party_id is the Unique Key.

Solution :- If you want to see these three rows as unique rows, create a custom column, combining values of different columns, like here you can define a custom column by combining (party_id + territory_id + contract_number)

v  Re-creation of Sales Query Templates.

Ø  Once you done with creating Sales Query Template and used once, you cannot modify in between, means you cannot add new parameter in between. You can only add at the end, so get the final list and order of the parameters to be defined in the Template before creating.

v  For using ‘Like’ operator, it needs to provide % operator explicitly in the parameter value.

v  Parameter values cannot be handled through front end, it can only be handled while creating database view. Ex. For free form fields, you cannot allow user to enter text in any case, it should be fixed either in upper case or in lower case or the case which can be handled at the time of view creation.

v  Profile Options values cannot be retrieved in view used, of course it can be retrieved when you            fetch from application.

v  No FNDLOAD available to download LDT of these setups, you have to do it manually if you want to migrate it to another instance 

Troubleshooting

v  List in 'Scheduled' Status.

Ø  While creating List, sometimes List status always be there in ‘Scheduled’ status.

Ø  Cause: The lock is in Workflow process at AMS_LIST_INIT_DET LIST_GENERATION

Ø  Solution: To implement the solution, please execute the following steps:

      1) Bounce the Concurrent Manager if Workflow Agent Listener is already running.

      2) If after bouncing the Concurrent Manager it still does not work then restart the Workflow Agent Listener for (Deferred) events.

v  Error: Query has exceeded <N number> rows. Potentially more rows exist, please restrict your query.

Ø  When your LOV query returns more rows, you can get the above error at the time of List generation.

Ø  Cause: The profile option 'FND: View Object Max Fetch Size' was set at 100.  This profile option controls the number of rows displayed in the OA Framework.

Ø  Solution: Increase the value for the profile option 'FND: View Object Max Fetch Size'. The default value is 200, but greater values can affect performance.  

Summary

This white paper provides the basic features of Custom list management process, we have more sophisticated features like parent-child Data Source, parameterized Sales Query Template etc. which also have some limitations, but those parts have not been discussed here. Again it depends upon business requirement which way we can achieve.

To implement custom list management process we have more setup related activities rather than technical work (creating database view/table and writing SQL queries for list of values). We have few limitations while creating list management process but most of the business requirements can be achieved through it which cannot be done through seeded Data Sources and Sales Query Templates.