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
|
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.
