|Oracle Marketing Implementation Guide|
Part Number E13578-05
This chapter covers the following topics:
As an Audience Administrator, you need to perform administrative activities to support list management, create data sources and query templates for list generation.
Once you log on to the Oracle Marketing Application choose the Audience Administrator responsibility and click the Audience Administration Dashboard, where you can perform most of the Audience administration activities. But before that you must perform the following implementation procedures:
There are two seeded responsibilities for the Audience Workbench: Audience User and Audience Administrator. Because menus, navigation and login flows depend on the responsibility of the logged in user, you will assign one or both of the following to your users:
Audience User Responsibility
Users with this responsibility typically:
Use the Audience Dashboard as their home page
Establish and manage the internal list selection process for marketing departments
Manage lists from affiliates and vendors
Develop and manage processes for list data quality
Ensure data enrichment and list data integrity
Note: If an Audience user updates a sales campaign in the Audience Dashboard using the Advanced option and if a Sales user tries to update this campaign in ASN, the data in the Advanced option will be lost. The Audience user should be aware of this implication. This is specific to sales campaigns alone.
Audience Administrator Responsibility
Users with this responsibility typically:
Perform administrative activities to support list management
Create and maintain data sources used by the end user to create lists
Configure query templates
Maintain suppression lists
Note: To remove the quick link to the Campaign Dashboard you must exclude the specific function AMS_WB_CAMP_DASHBOARD (for e.g., from Audience User/Audience Administrator responsibility).
Set the following profiles for lists. If using Oracle Discoverer for list generation, additional profiles are required.
|AMS: Enable List Recalculation and Preview||Optional||Site||User Defined||If set to "No", users will not be able to recalculate or preview the entries in a list or target group.|
If set to "Yes", users can recalculate the target audience size and preview list entries after modifying list selection conditions. This helps in estimating and arriving at an optimal list size before actually generating the list.
The default value is "No".
See The Recalculation Profile Option for more information.
|AMS: Orders Look Back Period for Query Template Processing (Days)||Optional||Site|
|Yes/No||This profile is valid only for the seeded orders-based query template, for performance problems related to large amounts of Order History Data.|
It is used during Cross-sell/up-sell based on orders - B2B. If set to Yes, then the user can fetch orders for a specified time period in days. The default value is 730 days.
See Creating Query Templates for more information.
The site level profile AMS: Enable List Recalculation and Preview allows businesses to decide if they want to implement the recalculation or preview entries function or not for lists created using Natural Language Query Builder (NLQB) templates. This profile option can take one of two values - Yes or No. The default value for this profile is set to No.
When the Profile is Yes
When the profile value is set to Yes, the Recalculation Table Status field is displayed in the NLQB template definition page. The Recalculation Table Status will be:
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 again to restart the recalculation table generation process.
When marketers use this layout to create lists in the Standard list creation method, the Recalculate and the Preview Entries functions are available. Projected Running Total and Change columns will be displayed. If a marketer completes all the target selections and clicks Recalculate or Preview Entries when the table is not yet generated, the system will display error messages. However, when the table is available, the system will perform the recalculation and display the entries.
The Projected Running Total and Change columns will not be displayed in the Advanced list creation method, and users cannot use the recalculate feature in this mode.
When the Profile Value is "No"
When the profile value is set to "No", the Recalculation Table Status field is not displayed in the NLQB template definition page. When you click Apply in the NLQB template definition page, no concurrent request is launched. If any associated recalculation table exists, it is dropped. The Recalculate and Preview Entries functions are not available to the marketers when they create lists. Projected Running Total and Change columns will not be displayed when marketers create lists using either the Standard or the Advanced method.
Refer to Oracle Marketing User Guide for information on list creation methods.
Run the following concurrent programs as needed.
|Workflow Agent Listener||Yes||List generation is invoked through business events. These business events need the background agent Workflow Agent Listener to execute them.|
This background agent picks up and executes all the scheduled list generation processes.
The WS_Deferred parameter must be selected for the Workflow Agent Listener business event.
|AMS Migrate Word Replacement Rules for Remote List Processing||Yes||This program migrates word replacement rules from a local to remote instance. |
This is a scheduled program.
|AMS Migrate Remote List||Yes||This program migrates a list generated in a remote instance to a local instance. This is a scheduled program and can be executed for a particular list or for all lists.|
|AMS Generate Materialized View for Template||Yes||This program executes when a query template is created (when the Finish button is selected on the query template screen.) It creates the materialized view for the template.|
|AMS Re-Generate Materialized View for All Template||No||This program refreshes the materialized views for all templates.|
|AMS Refresh Metrics||Yes||This request set populates the List Effectiveness bin and the Trend graph on the Audience Dashboard.|
The following lookups populate the list of values for list functionality. If implementing Oracle Discoverer for lists, you will need to verify additional lookups. For more information see Integrating Oracle Marketing and Oracle Discoverer.
|Target group selection type.|
|AMS_LIST_ DEDUP_ TYPE||User||Import Persons|
|Type of Deduplication Rules to be applied.|
|AMS_LIST_GENERATION_ TYPE||System||Full Refresh|
Append New Records
Update Attributes Only
|Full refresh of all the entries.|
New entries meeting the criteria added.
Only the attributes of the entries updated.
|AMS_LIST_ ROW_ SELECT_ TYPE||System||Nth Record|
|How to select rows during list generation. Standard is top down selection.|
|AMS_LIST_ SEGMENT_ STATUS||User||Archived|
|AMS_LIST_ SEGMENT_ TYPE||System||Workbook|
|Two types of segments supported. Based on a Discoverer workbook or an SQL statement.|
|AMS_LIST_ SELECTION_ ACTION||System||Include|
|How each selection is added to the list. Exclude means that all entries that exist in the excluded list are removed from the current list. Intersect causes the current list to become a list of only those entries which are on the intersected list and the current list.|
|Components of list used in list selection.|
|Possible list segment statuses.|
|Type of chart available|
|Two types of templates are supported: Standard and Parameterized SQL.|
Standard templates are created using the NLQ (natural language query builder) interface.
|AMS_LIST_ TYPE||System||Manual List|
|List of possible list types. Note where these appear in the program.|
|AMS_AUDIENCE_METRIC_TYPES||System||Booked Order Amount|
Booked Order Count
Invoiced Order Amount
|Metrics that are tracked for Lists used in target groups.|
|Valid operators for expression builder.|
|Data sources are based on tables/views that are located in the current database (local instance) or views/tables located in a remote database (remote instance). The remote instance is accessed using a database link.|
|Types of data sources available.|
|Category of data source.|
|AMS_SPLIT_MODE||System||Split by Attribute|
Split by Percentage
Split by Number
|List splitting methods|
List Import is an Oracle Marketing feature that facilitates importing lists of prospects and their related information from outside sources.
The following types of lists can be imported:
Purchased mailing lists
Mailing lists from merged or acquired organizations
Mailing lists provided by internal or external sales people
Using the List Import feature lists may be added directly to TCA tables. When importing from a purchased list, data is stored in the TCA schema. However, when importing from a rented list data is not stored in the TCA schema.
The Oracle Marketing list import functionality supports many different business requirements. For example, event registration can be automatically executed from list import.
When a list import is performed, B2B or B2C data is imported into the Marketing and TCA tables. When doing so, you can import directly into TCA or you can choose to preview it first.
Marketing import tables:
TCA import tables:
Use this procedure to create a location for the SQL loader control file. This step enables you to import data from a file located on the server.
Prerequisites: SQL Loader is installed
Launch a DOS prompt and create the Bin Directory under PRODUCT TOP ($AMS_TOP) for the SQL Loader control file. This directory must have read, write and execute privileges.
Set the following list import profile options:
|AMS : IMPORT CONTROL FILE PATH||Yes||Site||Control File Location||Enter the path for the bin directory: ($AMS_TOP/bin/)|
This path is relative to the mid-tier server that the SQL loader control file is written to.
Improper setup will cause the server side import to fail.
|AMS : IMPORT DATA FILE PATH||Yes||Site||Data File Location||Enter the path for the data file location: ($AMS_TOP/bin/). |
This path is relative to the mid-tier server and indicates the location of the import data file.
For the data file you may also set a different path to where the data is kept.
Improper setup will cause the server side import to fail.
|AMS : HZ DEDUPE RULE||Optional||Site||Yes/No||Indicates whether de-duplication rules are used during the TCA import process. |
The system may create duplicate records if this profile is not set to Yes.
|AMS : Import Client File Size||Yes||Site||Numeric value||Size of file in bytes. Based on this profile, the import program decides whether to use the concurrent manager to upload the file. |
Default value is 1000000. For Oracle Marketing List Import functionality, this value sets the file size limit in terms of bytes that can be uploaded by client.
|AMS : Default Data Source||Yes||Site||Data Source name||Data source name that is defaulted in the list/target group creation screens.|
|HZ : Key Word Count||Optional||Site||Number of words||This number determines how many words in the customer name are used to generate the keys|
|HZ : Address Key Length||Optional||Site||Length of the Address||This determines the length of Address key|
|HZ : Postal Code Key Length||Optional||Site||Length of the Postal Code||This determines the length of Postal Code key|
Use the following table for Lookups, types, values, and meanings.
|AMS_ IMPORT_ STATUS||System||New|
Entries are imported and available for viewing
Import is setup and ready to be completed at the scheduled time
Imported entries have been purged from the Marketing Import Table
The List Import has been cancelled and may not be reactivated
An error occurred during List Import
Duplicate record found
|AMS_ IMPORT_ TYPE||System||B2B Customer|
|Organizations, Contacts, Address|
Note: For Event Registrations, you can put the Default Registration Method in the import file and then map the default registration method to any value. If you do not map any value, then the Registration method that will be imported will be CALL CENTER.
If the concurrent manager server is different from the server where your data is located (and you want to import data from the server) - you must run NFS Mount. This ensures that your data file directory is mounted to the concurrent manager server.
In Oracle marketing there are a set of pre-defined data sources that help the list creating process. These data sources are called seeded data sources. Local data sources are created by marketing users. They can store the data in the application and use them to manage the audience for their products.
As the audience administrator, you are responsible for setting up data sources and their respective attributes. Data sources are a fundamental component of the list management process. 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
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.
Parent and Child Data Source Example
Amy is the list administrator for Vision Computers. Her marketing team wants the ability to generate a list for cross sell purposes. The end goal is to generate a list of customers who have recently purchased a Vision Desktop computer.
Based on the business requirements given to her, Amy must create the appropriate data sources. As such, she will create a parent data source "Persons" and a child data source "Order Detail."
Parent Data Source: Persons
Unique Identifier: PARTY_ID
Information of interest: Person First Name, Person Last Name, Email Addresses, Language
Note: This data source provides details about the person/customer who has recently purchased a Vision Desktop computer
Child Data Source: Order Detail
Unique Identifier: PARTY_ID
Information of interest: Booked date, Creation Date, Unit Selling Price, Order ID
Note: This data source provides transactional information about the person (and customer) who has placed the order
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. For more information about creating data sources (custom or remote), see Creating Data Sources in the Audience Administration Dashboard.
Related Data Sources help define the relationship between the corresponding data source and its related data source. For a parent data source, it displays the relationship with its child data sources. For a child data source it displays the relationship with the parent data source.
The Related Data Sources mid tab is displayed for all data sources (parent or child). If a relationship is defined in the parent data source (between itself and a child data source), then on navigating to the child data source, you see the relationship between the child and the parent. This implies that creating a relationship in one data source, displays the inverse relationship in the “Related Data Sources” mid tab of the other data source.
For example, if the Parent Data Source is Organization Contacts and the child data source is Locations, then if a relationship is created between the Organization Contacts and Locations in Organization Contacts, then the Related Data Sources mid-tab of Locations automatically displays the relationship of Locations to Organization Contacts.
The following related data sources are seeded:
Data mining Aggregates
Data Mining Score
The following parent data sources are seeded:
Person: Represents Business to Consumer (B2C) customer attributes such as first name, last name, marital status, household income, etc.
Organization Contacts: Represents Business to Business (B2B) attributes such as first name, last name, job title, work e-mail address, etc.
Organization: Represents organization attributes such as company name, business type, revenue, etc.
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.
|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 pulled from the table or view. 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.|
This attribute is important because if you want to enable the user to split the list, chart distribution of data for this attribute, or use this attribute in a dedupe rule then you must map the attribute to list entries.
For example, if you want gender to be the first column displayed to the end user, in this column you would enter COL1.
Use the Search and Select flashlight to pick from the list of values.
|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. |
If this checkbox is left unchecked, the data source will not display by default and the end user will have to explicitly select it through the personalization option.
|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. |
If you want to use for splitting you must map the 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 list template creation process.|
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.
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.
To establish a parent and child data source relationship, first create the parent and child data sources. On the audience Administration Dashboard, select the parent data source and navigate to the Related Data Source mid-tab.
Related Data Sources: Search and select the child data source.
Related Data Sources Column: Search and select the unique identifier for the child data source. This is the child data source attribute that would define the join condition between the child and the parent. If this column is left blank the child's unique identifier will be used as the join condition.
Primary Data Source Column: Search and select the unique identifier for the child and the parent. By selecting the same unique identifier, the child becomes associated to the parent. If this column is left blank the parent's unique identifier will be used as the join condition.
If the parent data source is selected first (and the child is related after) the primary data source columns refers to the parent and the related data source column refers to the child.
To link a child to a parent data source, select the child data source first (and parent is related after) the primary data source columns refers the child and the related data source column refers to the parent.
Data sources can be local or remote. Remote data sources can be setup and used for list generation. Remote data sources point to objects residing in a remote instance. For example, an external data warehouse system. To define a remote data source, you will point to a remote object using a database link (DB link).
A list created by the end user using a remote data source will reside in the remote instance until it is migrated.
Optionally, once you have established the DB link, you can migrate a single list (or all remote lists) to your local instance. If migrating, remote lists go through a migration process. For target group generation, data validation and TCA inserts are performed. Lists are generated through a package available on the remote instance.
Use the following procedures to setup remote data sources:
Use the following guidelines when creating the DB link in the local instance:
DBLINK_FROM_LOCAL_TO_REMOTE: Database link name
Username: Username used to connect to remote instance
Password: Password used to connect to remote instance
REMOTE_DATABASE_NAME: Name of remote instance
To create the DB link in the local instance:
CREATE PUBLIC DATABASE LINK DBLINK_FROM_LOCAL_TO_REMOTE CONNECT TO USERNAME IDENTIFIED BY PASSWORD USING REMOTE_DATABASE_NAME;
You can create a data source using the Audience Administration Dashboard. However, if any changes are made to the underlying data source table or view, the data source has to be refreshed to display these changes in the user interface.
For details about how to create a data source see Creating Data Sources in the Audience Administration Dashboard.Creating Data Sources in the Audience Administration Dashboard.
For details about how to refresh a data source see Refreshing Data Source.
You can create a data source customized to your needs. This data source might be local or remote. For remote data sources you need to run a script to setup the schema in the remote data source, and create the remote database link in the local instance.
To create a data source, log in as an Audience Administrator and navigate to the Audience Administration Dashboard.
Navigation: Audience Administration Dashboard > Create Data Source
Code: This code is used as a reference for the data source when creating Discoverer Workbooks. For more information about creating Discoverer Workbooks, see Integrating Oracle Marketing and Oracle Discoverer.
Type: Based on requirements, choose either parent or child.
Category: Selecting a category enables you to organize the data source by type. Category selection is mandatory when creating parent data sources and optional when creating child data sources.
The following categories are seeded:
TCA Data Source: Select this check box if the Data Source you create belongs to a TCA. Ensure that the PARTY_ID column contains a valid TCA party.
Table or View Information region:
Location: Choose Remote if you want to create a remote Data Source.
Database Link: Enter the database link information. This is the link created in Creating the Database Link in the Local Instance.
Table or View Name: Select the appropriate table or view that the data source is using. By selecting a view (given it has been created) you are able to narrow down the amount of data that the end user is able to retrieve when creating lists.
Unique Identifier: Enables you to set a default join condition between a parent and child data source. Specifying the join condition here enables the child and parent to be explicitly joined. Although you can relate the parent and child at a later time (using the Related Data Sources mid-tab), if you know your parent/child relationships up front, you can join them in the data source creation phase.
Complete the Attributes information for the data source in the Update Data Source page. You can select Data Mining Details from the View Drop-down to customize the following attributes for Data Mining:
Auto Binning: Select this if you want the application to automatically bin the data.
Binning Details: Select this icon if you want to manually bin the data and enter the Number of Buckets to be used for the Manual Binning. You can then enter the bucket number and the value for each of these buckets.
In addition, you can also create manual bins within multiple values by specifying the multiple values (for the same bucket) in multiple lines. For example, to create two buckets with multiple values for an attribute of type VARCHAR, you can specify the following:
Map to List Entries: If generating a list from the data mining results, then map the data source attributes to the corresponding list entries columns within this column.
Display in List Entries: Select this check box to display the columns within the Entries mid-tab for a list.
Oracle Marketing allows you to create a data source based on either a TCA or Non-TCA schema table or view. Once it is created, the data source does not reflect any modification that is made in the underlying table or view. Users must click the Refresh button to view these modifications and use them in customer selections.
Data Source Refresh Example
Amy is the list administrator for Vision Computers. Her marketing team wants the ability to generate a list for the new Quarterly Loyalty Magazine. The list should display the details of customers who have joined the Vision Computers Loyalty Scheme.
Based on the business requirements given to her, Amy creates a Custom data source "VC_PERSONS_LOYALTY".
Custom Data Source Name: “VC_PERSONS_LOYALTY”
Table: VC_PERSON_LOYALTY_SCHEME, with the following attributes:
Person_Loyalty_ID (unique identifier)
This data source provides details about the customers who have joined the Vision Computers Loyalty Scheme.
The Marketing team realizes they cannot make the appropriate customer selections, as one of the attributes they require is not available in this data source. Therefore, Amy modifies the Table VC_PERSON_LOYALTY_SCHEME to include the following attribute:
To expose the new attribute, Amy navigates to the "VC_PERSONS_LOYALTY" custom data source and clicks on the ‘REFRESH’ button.
The New Loyalty_Scheme_Sub_Code attribute is now available in the "VC_PERSONS_LOYALTY" custom data source for use by the Marketing team to select the customers.
As the audience administrator, you will create query templates. These templates enable your end users to quickly and easily create lists. When creating a list, your end user has two 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.
The ten commonly used standard query templates that are seeded with the Oracle Marketing application are detailed in the following table.
|Query Template Name||Data Source||Filter Conditions|
|Cross Sell to Install Base - B2B||Organization Contacts||Current install base products, purchase amounts, firmographics, and contact profile attributes|
|Cross Sell/Up Sell based on Orders - B2B||Organization Contacts||Purchase history, firmographics, and contact profile attributes|
|Customer Acquisition - B2B||Organization Contacts||Firmographics and contact profile attributes|
|Customer Retention based on Orders - B2B||Organization Contacts||Purchase history, firmographics, and contact profile attributes|
|Lead Maturation - B2B||Organization Contacts||Lead information, firmographics, and contact profile attributes|
|Interaction Follow Up - B2B||Organization Contacts||Interaction history, firmographics, and contact profile attributes|
|Cross/Sell Up Sell based on Orders - B2C||Persons||Purchase history, demographics, and person profile attributes|
|Customer Acquisition - B2C||Persons||Demographics and person profile attributes|
|Customer Retention based on Orders - B2C||Persons||Purchase history, demographics, and person profile attributes|
|Interaction Follow Up - B2C||Persons||Interaction history, demographics, and person profile attributes|
Within the Audience Administration interface, you can create additional user-defined query templates to meet your 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.
To create a user-defined standard query template, login as a user that has the Audience Administrator responsibility and navigate to the Audience Administration Dashboard.
Prerequisites: Data Sources are created
Query Template Name: Enter a logical name for the template. For example, cross sell list template.
Query Template Type: Choose Standard.
Data Source Name: Select the data source that will be used for this template. The data source determines the type of data retrieved.
Purpose: When the end users are creating lists, they are required to select a purpose. After solacing the purpose, templates (of that purpose type only) are displayed to them. Therefore, as the administrator, you must carefully organize your template purposes. 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.
Query Template Selections Section Notes
Attributes: Using the search and select icon choose an attribute for the query template. For example, if the list template filters by age or gender, you would select age or gender as an attribute.
Attribute Display Name: Enter a user-friendly display name for the attribute selected.
Available Operators: Select an operator for the attribute. For example, if filtering by “people who are age 30", then the operator would be Is.
Value: Depending on the available operator and default, you will choose an appropriate value mode. The following modes are available:
Calculation: If the data source is being used for “comparison” purposes, use this type of value mode.
Constant: If the data source has a static value, select this value mode. For example, if your query template is filtering for “age is 30" then constant (as a value mode) would be selected. Once constant is selected in the mode drop-down menu, a text box appears. Enter the constant value in this text box. For example, for “age is 30", enter the value 30.
Attributes: If attributes have previously been defined, you can re-use them by selecting this value mode.
List of Values: Selecting this value mode enables you to define a list of values for the data source.
Value Display: Use this text box to change the display value for the end user. For example, if the attribute selected is “default city”, the value display could simply be “city”.
Mandatory: Select this check box to enforce this particular attribute -- a mandatory selection for a template cannot be deleted by the end user.
You must configure the attributes that you will use to create a query. These attributes are defined when you create a query template. Using the Query Template Selection table, you can define query template attributes for list management as well as data mining. Once you configure the attributes, you can preview the template. The following table lists the descriptions for query template attributes.
|Attributes||The attributes that you define for the query template.|
|Attribute Display Name||The name of the attribute as it is displayed in the table. For example: an attribute Order Detail.Extended Price might have Order Value 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 order booking date.|
|Operator Default||Choose the default value of the operator.|
|Value||List of values.|
|Value Display||Specific value of the attribute.|
|Default||If checked, this value is the default.|
|Mandatory||If checked, this attribute is the default.|
|In Use||Shows if the attribute is in use already.|
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.
Within the Audience Dashboard, a marketer can use query templates to create lists. After selecting a query template, the marketer can enter values for the query template conditions and obtain a quick cascading count of these conditions as well as previewing the entries before creating the list. These counts are based on the values (operator, operand) provided by the marketer for the different query template conditions. The numbers for these cascading counts (that is, the Projected Running Total column within the List Selections table) is determined using the table created for the corresponding query template.
The information stored in the table is a snapshot of the data available specifically meeting the conditions of the template at the time of template creation. The tables pre-store the information therefore reducing the need to re-compute the data being queried when the user wants to preview the projected total and entries. Pre-storing the data supports faster viewing of the results.
The table supporting the individual query template is automatically created or updated depending on whether you are creating a new template or updating an existing one. You can also create the tables using a concurrent program.
For seeded query templates (see Seeded Standard Query Templates ) you must create the tables supporting the template prior to marketers using these templates to obtain cascading counts. Each query template should be enabled before creating its corresponding table. To create tables supporting each of these templates, simply select the appropriate template(s) and click Apply.
The section below illustrates the process of creating a table for a query template if you choose to use a concurrent program.
Before you run the concurrent program to create the table(s), ensure that all the conditions are defined for the corresponding query template(s) and the template is enabled.
Ensure that there is sufficient disk space. The space requirement for a table is proportional to the customer and transactional data within the data source and the number of conditions within the query template definition. A large B2C (business to consumer) organization will typically have more customer and transactional data than a B2B (business to business) organization. Hence the table created for B2C implementations will normally require more disk space.
The conditions for seeded query templates reference data within the Oracle E-Business Suite schema. It is estimated that these query templates will require approximately 35 MB of disk space for each query template. Based on the Oracle E-Business Suite data within your organization, you may require additional disk space.
Note the template name for the corresponding query template.
Log in to Oracle Forms and submit a new single request.
Find: AMS Generate Table For Template
Template Name: If you do not enter a template name in this window, then tables for all enabled query templates will be generated (or regenerated).
Note the Request ID. You can monitor the status of this concurrent request by providing the Request ID within the Find Request window.
After the table is created successfully, a marketer can use the query template within the Audience Workbench to determine the cascading counts and preview entries that meet the template conditions.
Note: In case of remote data sources, the recalculation table is created in the remote instance.
You can define a List Of Values (LOV) for query template attributes from the Audience Administration Dashboard. You must log in as the Audience Administrator and navigate to the Query Templates page. Select a data source to define the LOV.
Navigation: Audience Administration Dashboard > Query Templates > Data Source
Add List of Values using: The method by which you can add the List of Value. Select 'Another List of Values', 'Custom SQL', or 'Manual Entries' from the drop-down menu.
Display Name: Enter the Display Name of the value. For example - Age.
You can define charts for the query template attributes from the Audience Administration Dashboard. You must log in as the Audience Administrator and navigate to the Query Templates page. Select a data source to define the LOV and click the Define LOV, Chart icon from this page. Click the Continue button
Navigation: Audience Administration Dashboard > Query Templates > Data Source
Default Chart Type:You can choose a 'Pie', 'Column', or 'Bar' chart to represent the attribute.
In some cases, it might be easier to create templates based on a set of parameters. This may be the case when the SQL statement is very complex or if you are a power user of SQL. In these cases, you can set up a template so that the end user only needs to input values for these parameters to generate the list.
If you set up a template using parameterized SQL, then the end user simply selects the template and the corresponding parameters for the template are displayed. The end user selects the purpose and the list template in the first step of the Create List process. The mechanism to display either the Standard Template (based on natural language) or the Parameterized SQL Template depends on how you have set up the template. The following steps display the Parameterized SQL template creation process.
Step 1: As the Administrator, you will create the template based on a pre-selected SQL statement. Once you have set this up, when creating lists, the user will only need to input values for these parameters.
Step 2: During the create list creation process, because you have setup parameterized SQL templates, this is displayed in the drop-down menu (instead of the standard template).
Step 3: Because the parameters are pre-defined, the corresponding parameterized SQL template parameters are displayed.
To create a parameterized SQL template, login as a user that has the Audience Administrator responsibility and navigate to the Audience Administration Dashboard.
Query Template Type: Parameterized SQL.
Data Source Name: Select a Parent Data Source for the template.
Purpose: Select a purpose for the template. For example, if creating a list template for cross selling purposes, choose Cross Sell as the purpose type.
SQL Definition Region: Enter the parameterized SQL condition.
Definition: Use this field to enter editable text for the SQL Description. The text entered here appears in the end user interface within the Selections section for a list that uses this template.
SQL: Use this text box to enter a parameterized SQL condition. For example,
Select * from Table A where A.COLUMN_NAME1 = :Column_Name1 and A.COLUMN_NAME2 = :Column_Name2”
Validate SQL: After entering the custom SQL, use this button to validate the SQL statement. Clicking the button takes you to the standard Validate SQL page. There are three sections within the Validate SQL page:
SQL Statement: Read only text of the SQL Statement entered in the Custom SQL page
SQL Validation: Read only text that indicates if there are any errors in constructing the SQL, like SQL syntax, table names, column names within the table, etc.
Index Results: Read only text that identifies if the SQL condition is based on any non-index attributes
Deduplication rules check for list duplications, such as duplicate names, email addresses, first names, last names, etc.
Because list creation requires a parent data source to be selected, when creating deduplication rules, you are only able to select one that is associated to a parent data source.
There are a set of pre-defined or seeded deduplication rules for the data sources Organizations, Organization Contacts, and persons. Deduplication rules for persons are pre-fixed with 'B2C'. You can view the these rules from the Audience Administration Dashboard. Click the Data Source name to view the rules.
The following de-duplication rules are seeded in Oracle Marketring:
To setup custom deduplication rules, login as a user that has the Audience Administrator responsibility.
Attribute Name: Select the attribute that the deduplication rule is being created for. For example, if creating a rule to eliminate duplicate email addresses, you could choose Contact Email Address.
Word Standardization Rule: Optionally, select a standardization rule for the corresponding attribute. Word standardization allows words that have alias or alternate spellings to be normalize when going through the de-duplication process. For example Oracle Pkwy means the same thing as Oracle Parkway. Therefore, when using word standardization, the system will recognize it to be a duplicate when it goes through the deduplication process.
You can standardize words using the following criteria:
Fatigue rules provide your end users with a tool that helps prevent them from over contacting customers. As the administrator, you will setup up fatigue rules that define contact parameters.
Fatigue rules provide the following business benefits:
Promote Brand Awareness: If promotions are strategically timed, customers are not overexposed, therefore when they are contacted they are likely to be more receptive.
Support Marketing Strategy decisions: Helps enforce marketing rules dictating customer contact boundaries.
Maximize Marketing Dollar Effectiveness: If strategically contacting customers, every marketing dollar spent receives (in theory) a better response rate, therefore improving effectivity.
Improve Collaboration: Enables you to setup enterprise wide rules. For example, Telesales, direct mail, and email (all communication channels having the potential to fatigue the consumer) - by centralizing the rules, you can prevent all channels from over-contacting the consumer.
A fatigue rule defines maximum permissible contacts within a specified time period. For example, “Do not contact customers more than two times per month by any channel.” The defined time period adheres to a rolling period.
Rolling Period Example
An e-mail schedule is set to execute on February 13th and Amy is on the target list of customers to be contacted. The fatigue rule specifies, “Do not contact customers more than two times per month by any channel.”
On 13th February (when the schedule executes) it determines how many times Amy has been contacted in the last one month.
Contact count for Amy in the last 30 days:
Email was sent to Amy on Jan. 15 -- one contact was counted.
Another email was sent to Amy on Jan. 21 -- the count became two.
As of Feb. 13, Amy had already reached her threshold of “two contacts per month.” Therefore, she is fatigued and cannot be contacted.
However, if the schedule executed on Feb. 14th, the total number of contacts within the last 30 days would have been 1. The contact on Jan. 15th would have been rolled out from the past 30-day period starting Feb. 14th.
Fatigue rule time periods are defined as follows:
Week = 7 days
Month = 30 days
Quarter = 90 days
Year = 365 days
Using this type of rule (threshold) you can set an absolute limit on the number of contacts for a given time period.
If exclusively setting channel-specific limits -- global rules can be null
For example: do not contact any customer more than 4 times per month by any channel
To create a global fatigue rule, login as a user that has the Audience Administrator responsibility.
Navigation: Administration > Fatigue Rules Setup
Global Contact Limit: Enter a number to set an absolute limit to the number of contacts that can be initiated by the Marketing department to the customer – for a given time period. For example, if you do not want to contact a customer more than 2 times per month, enter 2 in this field.
Global Limit: Use this drop-down menu to choose a time period for the numeric limit selected. For example, if you do not want to contact a customer more than 2 times per month, select Per Month.
In addition to setting global limits, you can define further restrictions by selecting a marketing channel. If channel-specific rules are not set, then there is no limit per channel (within the defined global contact limit.)
Within the limits set globally, you can also establish specific rules (thresholds) for each outbound channel.
Includes all direct marketing channels: direct mail (print), email, fax, and telemarketing (phone)
For example: of the 4 total contacts allowed per month, do not contact customer by phone more than 2 times per month.
When determining which rule to use (global vs. channel) the system will always use the most restrictive combination.
For Example, if you have a global rule (do not contact more than 4 times per month) and a channel specific rule (do not contact more than 2 times per month by email or fax) -- in this example, the system will first look at the channel contact limit. If further restrictions are needed, then the global rules would apply. In other words, if both global and channel specific rules are established, the most restrictive combination is used.
To create channel specific rules, login as a user that has the Audience Administrator responsibility.
Navigation: Administration > Fatigue Rules Setup
Channel: Using the drop-down menu, select a channel for the fatigue rule. The channel selected here will, by default, always use the fatigue rule created. For example, if creating a fatigue rule for the "email" channel, select email.
Maximum Contact: Enter a maximum contact limit for the selected channel. For example, if creating a rule that states you can only contact a customer 2 times per month (by email), enter 2 in this field.
Time Frame: Select a time frame (per week, month, year, quarter) for the fatigue rule. For example, if creating a rule that states you can only contact a customer 2 times per month by email, then select per month.
When executing a campaign activity (containing a fatigue rule) the following concurrent program must be running in the background. This program must be scheduled and should simultaneously with schedule executions.
AMS: Apply Fatigue Rules
Oracle Marketing helps companies comply with privacy policies by providing pre-defined suppression filters. Consumers can opt out of marketing activities in a number of ways - by unsubscribing from an e-mail activity, by informing the telesales representative, or by signing up for Do not Call registries set up by government. The Oracle Customer Model (TCA) has provision to capture contact preference of customers. This information is stored within the HZ_CONTACT_PREFERENCES table.
Based on the suppression information stored either within HZ_CONTACT_PREFERENCES or within user defined suppression lists, members are automatically suppressed during target group generation for a marketing schedule. Members within the target group are checked with the suppression list members and the matching records are automatically suppressed.
Out-of-the-box, Oracle Marketing provides the following seeded suppression lists:
Do Not Contact for Persons and Organization Contacts
Do Not Mail for Persons and Organization Contacts
Do Not Email for Persons and Organization Contacts
Do Not Fax for Persons and Organization Contacts
The seeded suppression lists are based on information stored in the HZ_CONTACT_PREFERENCES table of the TCA.
In addition marketers may create user-defined suppression lists to prevent targeting specified categories of people. For example, an administrator can create a suppression list of all CEOs.
Navigation: Audience Administration Dashboard > Suppression Lists shortcut
Personalize: The personalize page allows you the flexibility to control the number of rows displayed, the sort order, and any other filter criteria you may want to set. For example, show me all suppression lists where channel is E-mail. By default, the "All Suppression Lists" view is sorted based on the Last Updated Date (descending).
Channel: More than one channel may be associated with a suppression list. When entering multiple channels, separate the names with commas.
Use for Suppression: By default, any suppression source that you add is used for suppression. You can disable a suppression source by deselecting this field.
Customers of marketing activities receive marketing communication via different channels, such as e-mail, direct mail, fax, and phone. They may wish to select specific channels from which to receive communication and may also wish to modify their contact information.
Log in as the Audience Administrator to navigate to the Contact Preference page from the Audience Administration Dashboard. Select one or all options to include in the unsubscribe text box on the page. The options are listed as fields on the right hand side of the text box. Once you select an option; for example Email Channel, the text box displays an unsubscribe message that you can customize. This message is displayed alongside the unsubscribe check box on the preview page. Customers can select the check box to unsubscribe from the Email Channel as their contact preference.
The following contact preference options are seeded:
Primary Contact Points
If you add the Primary Contact Points option to the Contact Preferences page, the end users can update their primary e-mail address and primary phone number in the customer database. They can choose values from the active list of existing e-mail addresses and phone numbers from TCA.
Note: For customers using previous releases of Oracle Marketing, as the administrator, you must explicitly revisit the Contact Preference page and insert the options currently available. Only then will end users be able to view the contact preference options available to them. Because end users can no longer update campaign opt out and product interest information, any suppression lists created based on this information in earlier releases will not be refreshed.
Note: Functionality that allowed end users to update product interests or to opt out of specific campaigns, is no longer available.
To administer list import, you are required to perform one or all the following procedures:
Existence checking uses predefined rules that are used only when the AMS: HZ_DEDUPE_RULES profile is set to "YES" at the site level. Existence Checking is done using fuzzy keys and word replacement rules. List import programs use algorithms to create any TCA entity.
Word replacement rules are defined in TCA and those rules are applied to the customer name while populating the key in the AMS_IMP_SOURCE_LINES table.
Existence checking is done for the following:
Existence checking is based on Person First Name, Person Last Name, Email Address, Phone Country Code, Phone Area Code, Phone Number, Address and Country.
First checks are done to see if the person exists with a provided E-mail address. If it does not exist then it checks with First Name and Last Name with phone attributes. If this fails then it looks for First Name and Last Name with address attributes. If this does not exist, then a new Person record is created (Party of type “Person”).
Organization existence checking is based on:
First it checks if an organization exists with the same name if it does not exists then it creates a new record. If it does exist then it checks if an organization record exists with this name, address1 and country. If it does not exists then it checks with name and country if this also does not exists then it picks up the maximum (party_id) record with the same name and without checking address1 and country column.
Even if pick up records with name, address1 and country or name and country it always picks up the maximum party_id for the organization name.
Organization Contact Existence checking is based on:
Phone area code
The system checks if a person exists with the provided first name and last name. If this exists , it checks if a records exists with above details. If this does not exist, then it checks the first name and last name with the email address. If this also does not exist, then it checks the first name and , last name with the phone area code, number and extension.
Address existence checking is based on:
The List Import checks if there is a location for the provided address1, city, postal code and country column. If this does not exist then it checks for address1 with country and picks up that location if it is available. Otherwise, it creates a new one.
Original system reference is a target field that has been added to the List Import process. This affects the existence checking logic in the following way:
Organizations (B2B): If original system reference is mapped, existence checking will use this value along with organization name and other attributes, such as Address1 and Country to find the exact match. If it is unable to find matching record then existence checking is done without the Original System Reference column.
Organization Contacts (B2B): If original system reference is mapped then existence checking will use this along with Organization Name, Person First Name, Person Last Name and other attributes, such as E-mail Address, Phone Area Code, Phone Number and Phone Extension to find the exact match. If it is unable to find matching records then the existence checking will be done without the original system reference column.
Persons (B2C): If original system reference is mapped then existence checking will use this along with Person First Name, Person Last Name and other attributes like Address1, Country, Email Address, Phone Country Code, Phone Area Code and Phone Number to find the exact match. If it is unable to find matching records then the existence checking will be done without the Original System Reference column.
Use the following sections to import lists:
Marketers have the ability to import lists from an XML or CSV file into Oracle Marketing. Based on the type of imported list data, the Import Wizard will walk a marketers through the steps.
The XML import is available for the following:
Person addresses import
The XML import file must be in zip format. The zip must contain the following:
XML File: This contains the B2B or B2C customer information, it can be in any format.
DTD File: This file formats the xml data. For example, if the xml file contains a tag “address” the DTD file will format the tag into “address = city + state + zip” for the application.
B2B and B2C CSV List Import
A CSV import can be performed for any B2B or B2C type of import. For more information on how to perform a List Import, using XML or CSV see the Oracle Marketing User Guide.
When Oracle Marketing is integrated with Oracle Telesales, the list import feature can be used to assign lists to TeleSales Agents. In this scenario, a TeleSales agent can log into Oracle TeleSales Universal Work Queue and access a list of customers assigned to them by the marketing team. This enables a list of prospects collected in marketing to be followed up on by the sales team.
Note that lead creation from Universal Work Queue is supported only for PERSON and ORGANIZATION CONTACT source types. The PARTY_ID column must be mapped in AMS_LIST_ENTRIES for both these source types.
To set up List Import for Universal Work Queue follow the procedures:
To enable the list to be displayed in Universal Work Queue the following profiles must be set:
AMS: Queue: Marketing Lists – Manual Assignment at Site or Application level. This profile determines if a Telesales user can work on Marketing Lists – Manual Assignment.
AMS: Queue Order: Marketing Lists – Manual Assignment at Application or Site Level. Choose a display order of 1-56. This profile determines the order of appearance for the “Marketing List – Manual Assignment” in the work queue in relation to the other Queue Order Profiles.
To set up the List Import for Universal Work Queue, login as a user that has the Audience Workbench Super User responsibility.
TeleSales Agent Responsibility is assigned to the user
A csv file with the Sales Agent (i.e. the application login id for the sales agent) column is populated
A Telemarketing Campaign Activity has been created in Oracle Marketing
Import Mapping section: Map the sales agent column from the Source Column to the “Sales Agent ID” target field column. The Sales Agent ID is the application login id for the sales agent(s.).
Import the data into Oracle Marketing and generate a list using the imported data.
Associate the generated list to the Telemarketing Schedule created within Oracle Marketing.
Generate the target group for this Telemarketing Schedule.
After the list has been created in Oracle Marketing it is available for use in Oracle Telesales. To verify that the list has been properly implemented, log in to Oracle Telesales and navigate to the Universal Work Queue tab.
Prerequisites: User has TeleSales Agent Responsibility
Work Type: Select the Marketing List – Manual Assignment.
View the lists that have been assigned to you from Oracle Marketing.
List Import pre and post user hooks allow you to write custom PL/SQL that is invoked during the list import process prior to list generation. The purpose is to allow you to cleanse the list before the list data is inserted into TCA (pre) and after (post) before list generation.
The following business events are used in the list import user hook process:
List Import - Pre Business Event: oracle.apps.ams.list.ListImportPreEvent.
List Import - Post Event: oracle.apps.ams.list.ListImportPostEvent
Oracle Discoverer is a business intelligence/analytics application that is implemented with Oracle Marketing for list management purposes.
For more information on integrating Oracle Marketing and Oracle Discoverer see Integrating Oracle Marketing and Oracle Discoverer in Optional Integrations.
A Discoverer query can be launched from within the Oracle Marketing application, and a list manager can create and manage workbooks and worksheets needed for list management purposes.
You can create a list of person and organizational contacts, and the organization information can be part of the attribute in organization contact list. When creating a list from Discoverer, you will need to select a mandatory identiﬁer, which is a constant key. The other ﬁelds including "include organization", "include email", etc. are optional depending on what kind of list you want to generate.
There are two seeded Business Areas within Discoverer: Organization and Person. You can create workbooks for these business areas and use the workbooks for list generation.
To create workbooks in Discoverer, you need to launch the Workbook Wizard and select the items from the Items tab. These are seeded Business Areas of either Organization or Person. From the Details page, enter the values for the business area to create a workbook.
Similar to creating workbooks for seeded Business Areas in Discoverer, the workbook wizard contains pages where you can assign conditions and calculations to create custom business areas.
For more details on workbooks, see the Oracle Discoverer Implementation Guide.
My List status perpetually displays ‘Generating’ or ‘Scheduled’. Why?
This happens when the Workflow Listener is down or service components are down. To resolve this issue try the following steps:
Navigate to Oracle Applications Manager > Workflow Manager (Your system administrator can provide this path).
Check if the Agent Listeners are up.
In case you need to refresh the Listeners, click the icon adjacent to the Listeners. This displays a list of all the Listeners.
Refresh the Listeners by stopping and restarting the services.
If the service components are down, then restart them with the Application DBA’s help.
If this does not resolve the error, you might have to refresh the Concurrent Manager as well. Contact your system administrator.
If you are still not able to resolve the problem, your system queue might be corrupted. Contact the database administrator to resolve it.
I carried out all the steps correctly to generate my list, but it still displays the status ‘Failed’. Why?
Check the system log in the Generation mid-tab on the List Details page for the following errors:
ERROR: ORA-20100: File l012345.tmp creation for FND_FILE failed
Resolution: Contact the operations database administrator or the system administrator, and get additional space in the udump dir server.
Errors related to table space or database shutdown.
Resolution: Contact your DBA to resolve these errors.
Use the following steps to create schema used for remote processing.
Login to SQL*Plus in the remote database.
Note: As a best practice, you should create a new schema and give access to tables and views which will be used for remote data processing. Typically this would be views and tables that will store customer information with which you are going to create lists. By creating new schema, you are limiting apps user access to only the relevant tables and views which might be needed for list generation.
Download the zip file amsrlpkg.zip and unzip the contents.
Run the script amsremot.sql on the remote database (for the same schema). This script creates the following tables:
Apply the following AMS .pls files :
Copyright © 2008, 2010, Oracle and/or its affiliates. All rights reserved.