This chapter covers the following topics:
Metrics are measurements used for tracking the effectiveness of marketing activities. Metrics include both forecast and actual values. These values are either added manually or automatically calculated by the Marketing application. Metrics can also be used to initiate Campaign Triggers. For example, a Responses Count metric greater than a desired value could be used to trigger a follow-up Campaign Activity to a larger audience.
To understand metrics see the following sections.
The metric hierarchy dictates how objects relate to each other and how their associated metric values will rollup within the hierarchy. Ultimately, the metric hierarchy dictates how metric values will be totalled across marketing objects.
Within a single marketing object, metrics of similar categories (cost, revenue, response, and so on) are organized into summary hierarchies. For example, within a direct mail campaign activity, cost-category metrics can be related hierarchically as shown in the following figure:
Summary Hierarchy
In this example, Postage Cost, Creative Costs, and Other Costs are summarized to Total Direct Cost. These lower level costs are added to Total Indirect Cost (for example, overhead expenses) to obtain Total Cost for the Campaign Activity.
The Rollup Hierarchy refers to links between metrics of different objects within the marketing object hierarchy. A typical marketing object hierarchy is shown in the following figure:
Rollup Hierarchy
The Rollup Hierarchy enables metrics from lower-level marketing objects (for example, Campaign Activities and Event Schedules) to be rolled up to parent objects (Campaigns and Events) and grandparent objects (Programs).
The following figure represents a detailed Rollup Metric Hierarchy. In this example, Direct Costs, Revenues and Responses are rolled up the marketing object hierarchy from the Schedule level to the Campaign/Event level to the Program level.
Marketing Rollup Metric Hierarchy
Using metric formulas, you can measure the effectiveness of a marketing object based on a composite value calculated from different metrics. Using formulas, the marketing department can understand activity effectiveness better.
Formula metrics:
Calculate composite values from other metrics defined for the same object type.
Can be used in the metrics side navigation menus for measuring the performance of various marketing objects.
Is calculated as null if the dependent metrics are not assigned to the same object.
Perform basic mathematical calculations including, addition, subtraction, multiplication, and division, along with a parentheses for grouping.
Is defined by a unique name for a particular object type so that the calculations are consistent throughout the system for the same formula.
A formula is defined with a unique name for a particular object type so that the calculations are consistent throughout the system for the same formula. Formulas can also be defined generically for object type "Any". The sources of variables for the formula metrics type "Any" are either categories or rollup and summary metrics. For example, to define the ROI formula of type "Any" the source values can be defined as the categories cost and revenue, then the ROI formula can be attached to all supported marketing objects, because there are no object specific dependencies. The category variable will total all the lower level metrics (manual, function, and rollups) within the same category. For a sub-category, only the metrics within the same sub-category are added. The variables within the formula are only from the metrics associated with the same object type. When a formula is added to an object, the relevant metrics are automatically added.
Formula metrics calculate measures based on other metrics. This is an extension of the metrics calculation types. Formula metrics can be used in the metrics side navigation menus for measuring the performance of various marketing objects. A Formula Metrics can calculate composite values from other metrics defined for the same object type. If the dependent metrics are not assigned to the same object the formula will calculate null. Formulas can perform basic mathematical calculations including, addition, subtraction, multiplication, and division, along with a parentheses for grouping.
Metrics for Control Group Analysis helps the marketer make a precise comparison between the Contact and Control Groups. While making this comparison, the system, instead of viewing all the metrics for an activity, chooses the metrics specifically collected for the Control Group Analysis within the Control Group Analysis Report.
Summary metrics selected for Control Group analysis comprises two child metrics to represent the Control and Contact Group data respectively. This helps the system identify the control and contact groups and appropriately calculate response rates.
Set up metrics for Control Group analysis in the following formats:
Top Level Metric: Represents the total metric value for the Target Group. For Example, inferred orders can be a top-level summary metric to hold the inferred orders for the target group. The base data for this is selected to “Target Group”.
Contact Group Metric: Function or Manual metric for Contact Group. Summarize this to the Top Level Metric. For Example, Contact Group Orders summarizes to the Inferred Orders. The base data for this is selected to “Contact Group”.
Control Group Metric: Function or Manual metric for Control Group members. Summarize this to the Top Level Metric. For Example: Control Group Orders summarizes to the Inferred Orders. The base data for this is selected to “Control Group”.
Navigation: Administration > Marketing > Initiatives Setup > Metrics > Create
Notes
Calculation Type: Select Control Group Analysis. This is required as there can be numerous metrics but it might not be meaningful to display all the metrics within all the measurement areas within the application.
Only those metrics identified for Control Group Analysis are displayed in the Control Group report. You are still required to associate the metrics to an activity. The Control Group Analysis checkbox is available only while defining a summary metric.
Users can create the two child metrics when a summary metrics with Control Group analysis is created. This also reduces the number of steps to set up a proper metric infrastructure for Control Group Reporting. Clicking the Control Group Analysis checkbox will navigate the user to the Child Metrics page.
Navigation: Administration > Marketing > Initiatives Setup > Metrics > Create
Notes
Calculation Type: Once you select the checkbox the page refreshes to display the Child metrics section. There is no section header for this section. Instead this information is displayed at the bottom of the page. The metric name and the type are mandatory fields. Enter the child metric name before saving the summary metric.
Select the Manual option by default. If you select the Function radio button, you are mandatorily required to enter the Function name. After you create the Summary metric and child metrics, the Control Group Analysis child metrics is displayed with the Metric Names as hyperlinks. As an administrator, you can drill down to the child metric detail. The Control Group child metrics section is not displayed for Rollup metrics or for Summary metrics not identified for Control Group Analysis.
The metric calculation type determines the way in which forecast and actual values for a metric are entered into the system.
The following metric calculation types are available:
Manual: Receives both forecast and actual values from user input.
Function: Receives actual values from a custom PL/SQL function or stored procedure. This function or procedure contains logic for calculating the actual value based on information within the Oracle E-Business Suite.
Summary: Receives both forecast and actual values from lower-level metrics in the summary hierarchy. May have subordinate metrics of any type (summary, manual, rollup or function) within the same metric category (Cost, Revenue, Response, and so on). This metric is automatically associated with a marketing object when any subordinate metric is associated.
Rollup: Receive both forecast and actual values from lower levels in the marketing object hierarchy. Only manual and function metrics within a lower-level object (for example, Campaign Activity) can be linked to a rollup metric in a higher-level object (for example, Campaign). All child metrics of a rollup metric must be in the same metric category (Cost, Revenue, Response, and so on).
Formula: Enables a marketer to measure the performance of a marketing object based upon a composite value. Marketers can define a formula based upon relevant metrics to calculate this composite value.
Control Group Analysis: Enables a marketer to create two child metrics when a summary metric with Control Group analysis is created. This reduces the number of steps required to set up metric infrastructure for Control Group Reporting. Control Group Analysis is required for the system to identify control and contact groups and appropriately calculate response rates for the metric.
It drives the method in which metric values are rendered on the screen. Possible types are integer, percent and currency. Integers are displayed and saved as whole numbers.
Metrics with the categories of Cost and Revenue have an attribute of “Currency”. Two currencies apply to each metric, one for the displayed (transactional) value and one for the internal (functional) value.
Rollup and Summary metrics display the currency value according to the marketing object's currency. Manual and Function metrics are defaulted to the marketing object's currency but may be changed for each metric. All Cost and Revenue metrics are rolled up using the internal functional currency, which is the same system wide. Use FND_PROFILE.VALUE ('AMS_DEFAULT_CURR_CODE') to determine functional currency.
The transactional value is rounded to the GL 'Minimum Accountable Unit'. The function value is stored without rounding to maintain required precision when converting to the displayed (transactional) currency.
For example if the transactional currency is Mexican Paso (MXP) and functional currency is US Dollar (USD), with the conversion rate of 15,000 MXP/USD then conversion from 10,001 MXP to 0.67 USD (rounded), converting back produces 10,050 MXN, and removing the rounding on the functional currency produces 0.66673333 -- USD to 10,001 MXN.
Metric templates enable you to standardize the metrics associated with a marketing activity. For example, you can define a metric template that automatically associates specific call center costs to all new telemarketing activities. To streamline template setup and administration, each template is associated to a single marketing activity type for example, email, telemarketing, direct mail) or a single marketing object type (for example, Campaign, Campaign Activity, Event, Event Schedule).
To understand and implement Metric Templates see the following sections:
Before you create a Metric Template, ensure that metrics have been defined.
Navigation: Administration > Marketing > Initiatives Templates > Metric Templates > Create
Notes
Name: The metric name value must correlate with the marketing object it is associated with.
Enabled: Select to have the template enabled on creation.
Associated With: Select either Object Type or Custom Setup. A Metric Template can be associated with either a marketing object (campaigns, events, and so on) or with a custom setup. If the metric template is associated with a marketing object, all metrics added to the template would have the same "Used With" value.
If the metric template is associated with a custom setup, all metric added to the template will have the same used with object as the custom setup.
Existing templates, which refer more than one object type, are to be split into each object type. The Metrics and Custom Setups would be distributed according to the same Object Types to the new templates.
Association Name: Use the flashlight icon to display a list of all the custom setups (in the case where you have selected custom setup in the Association Type drop down.) If you have selected used with in the association type drop-down, a list of all the object types (for example, Campaign Activities, Campaigns, and so on) is displayed when selecting the flashlight LOV.
Define Metrics table:
Metric Name: Select the metrics that will be part of this template.
Used With: This shows what types of object the metrics can be assigned to. The associations include defining which object types or custom setups these metrics are assigned to on creation.
Enabled: Select this to enable the metric for this template.
Enabling occurs at each level of the metric template. The entire template is enabled or disabled, or with each metric individually. If the template is disabled then the associations and metrics within are ignored. If metrics are disabled within the template then those metrics are not assigned (at least because of this template, but another template could still assign that metric).
Usage of a metric template is transparent to the marketer creating the business object. When the marketing object is created, the metric templates are all searched to generate a composite list of metrics to assign. The metrics are required to have the same "Used With" object type as the new business object. The metrics must be enabled at the metric definition. The template, association type, and included metric all must be enabled to assign the metric. If duplicates of a metric are found across the templates only one metric is assigned to the object.
Metric templates can be associated with a marketing object (campaign, campaign activity, event, and so on) or a custom set up. When a marketer creates a marketing object with a particular custom set up, the metrics available for that particular object will include the metrics included in the metric templates associated with that custom set up and those included in the metric templates associated with that particular marketing object type.
Example
Let us assume that a marketer is creating a campaign 'live it up' with the custom setup 'ABC'. Prior to creating this campaign, you created a metric template 'AA' with metrics A1, A2, and A3, and associated it with the custom setup 'ABC'. You had also created another template 'BB' with metrics B1, B2, and B3, and associated it with the marketing object of type 'Campaign'. The campaign 'live it up' will include the metrics A1, A2, and A3 from metrics template 'AA', which is associated with the custom setup 'ABC', and B1, B2, and B3 from the metrics template 'BB', which is associated with the marketing object of type 'Campaign'.
A number of commonly used metrics are seeded with the product and enabled at time of deployment. If implementing additional metrics, defined specifically for your organization, as the Marketing Administrator you must create and set up these up. The procedures in this section will demonstrate how to accomplish that.
Metric creation will depend on the marketing object being measured. When creating a new metric for a marketing object, a calculation method must be selected. The calculation method indicates how a metric receives input values. For example, if the calculation method “values roll up from lower level” is selected, the metric values will roll up from a lower level marketing object.
When creating a new Metric, it can be simple or very complicated, depending on the aspect of the marketing object being measured. Regardless of the complexity, the process flow for creating a metric remains constant. The general flow is shown below.
Process Flow Diagram: Creating Metrics
To maximize metric performance, set the profile AMS : Batch Size at site level. This profile takes an integer that represents the maximum number of records to save in a batch. This helps tune the performance of the metric refresh program. By setting this profile, when updates or inserts are performed, they are limited to the batch size. This prevents the rollback segment from becoming too big and saves any intermediate processing. If the program fails then not all the work would be lost. The refresh would start from where it left off. The batch processing is written to prevent committing any corrupt state.
Summary metrics enable values to be calculated vertically in the hierarchy. Values are totalled at the marketing object level. Summary metrics may have subordinate metrics of any type (summary, manual, or function). Summary metrics should be created to total the manual metrics at that marketing object level.
Summary Metrics are assigned automatically to the marketing object when any subordinate metric is assigned.
For example, you can create a summary metric called "Total Cost" that is used with any object type. Then create other cost metric for campaign activities, such as "Venue Costs", "Postage Costs", and so on, all of which summarize to "Total Cost". When assigning "Venue Costs" and "Postage Costs" to a campaign activity, Total Cost will add these values together to give a summary value.
Summary metrics can be rolled up at same level but are not permitted to be rolled up to parent level.
Navigation: Administration > Marketing > Initiatives Setup > Metrics > Create
Notes
Calculation Type region: To simplify the process of choosing a "used with" object, select a calculation type up front. Select Summarize as the Calculation type. The "used with" object is automatically populated with "Any".
Display Type: Display type drives how the metric values are displayed in the UI. Select either integer, percent, or currency.
Aggregation Type: The aggregation type drives the method that calculates the summary metric. It can be calculated by summarization or by average. When using summarization, all child values are totalled. When using average, instead of creating a total, an average is created.
Accrual region: Applies to manual and function metrics only. Therefore, this region is disabled.
Summarize To region: Select Summarize at Same Level. This is an optional selection. If this option is selected, the value of the metric being created is summarized to this metric. However, both metrics will always be attached to the same marketing object.
Rollup to Parent: Use this option only when using manual, rollup, or calculate using PL/SQL metric.
A rollup metric receives its values from lower level marketing objects. A rollup metric must have the same category as all of its child metrics.
Manual and Function Metrics are assigned to a Rollup Metric, therefore results can be propagated to parent marketing objects. Rollup Metrics may be assigned to themselves so that any hierarchical marketing object structure will collect all values from subordinate objects. Values cannot be entered. All values are totaled from the child level. Rollup Metrics always have a Used With field value of Any.
For example, you may want to establish a venue cost at the Event Schedule level. To view venue costs across multiple Event Schedules, a rollup metric called “Rolled-up Venue cost” can be created for the “Venue cost” manual metric. The rollup metric will appear at the Event and program levels totaling all 'Venue Costs' from the Event Schedules.
Navigation: Administration > Marketing > General > Metric > Create
Notes
Calculation type: Select Rollup
To have the rollup metric continue to rollup the marketing object hierarchy, set the “Summarize to: of the parent” to this same rollup metric.
Note: To have a rollup metric recursively rollup to itself, you will need to create the rollup with "of the parent" blank then update the rollup metric with this field assigned to itself. During creation of the rollup metric it does not have an entry in the database and the LOV will not show this as an entry.
Set "Summarize to: at the same level” to the desired summary metric to total at the same object level.
A manual metric receives values from user input. Manual metrics are entered by hand into the system.
For example, an Oracle Marketing Administrator can create a manual metric for the Campaign Activity object called “Venue Cost”. This enables a marketer who is using the application to manually enter costs associated with Venues.
Each manual metric can be assigned to an object multiple times. A transaction date and description distinguishes each entry.
Each metric is optionally assigned a summary metric, which totals values within the same marketing object. When the manual metric is assigned to the object, the summary metric is also assigned. The summary metric must have the same category, display type, and aggregation type as all its child metrics.
Each metric is optionally assigned a Rollup Metric, which totals value at the parent business object. When the values of a marketing object are refreshed the rollup metric is either created at the parent level, or if the appropriate rollup metric exists, a link is made so the rollup can determine which values to total. The Rollup Metric must have the same category, display type, and aggregation type as all its child metrics.
A manual metric receives values from user input. Manual metrics are entered by hand into the system.
For example, an Oracle Marketing Administrator can create a manual metric for the Campaign Activity object called “Venue Cost”. This enables a marketer who is using the application to manually enter costs associated with Venues.
Navigation: Administration > Marketing > Initiatives Setup > Metrics > Create
Notes
Enabled: Select this check box to make it available for association with marketing objects.
Enter Values Manually: Retain the default setting.
Used With: Select the marketing object that will use this metric.
Summarize To section: Select a Parent level/and or Same Level metric. This step is optional and only is performed if the function metric created will be Rolled up to a parent or Summarized at same level.
PL/SQL Program Metrics enables the Metrics Refresh Engine to interface with database stored procedures and functions. This allows for the implementation of customized logic for calculating metric values based on business needs. Each time the Refresh Metric Engine is invoked, all PL/SQL Program metrics in the system are executed.
Two types of PL/SQL Program metrics are supported:
Procedure: Procedure Metrics, based on database stored procedures, are executed once regardless of how many times it is used in the system. For example, seeded procedure metrics are used to calculate the number of leads generated from a campaign activity. Each lead generated is assigned a source code that references a marketing object. The number of leads for a campaign activity is determined by counting the number of leads with the same source code linked to the campaign activity.
Function: Function Metrics, based on database stored functions, are executed once for each usage. For example, if 10 Campaigns use the Function Metric, the function is executed 10 times.
For performance purposes, use of Procedure Metrics is highly recommended over Function Metrics. As the data grows the overhead of function metrics grows proportionally. Execution of the function is proportional to the number of instances of the function metric attached to objects. Where as, procedure metrics are executed once per refresh no matter the size of data. The queries and other program logic are executed once to gather all the required data in bulk and then written in bulk to the activity metric table.
Procedure Metrics are intended for enabling metric value calculations to be performed in a batch mode. Because Procedure Metrics are expected to update metric values for multiple Marketing objects, the database bulk update features are used.
To understand and implement Procedure Metrics see the following:
The first select statement represents when the procedure metric is called by the AMS – Metric Refresh program concurrent program. This select statement queries for all activity metrics system wide that are relevant to this operation. The query is not specific to any marketing object.
The second select statement (within “ELSIF p_arc_act_metric_used_by = ‘CSCH' THEN”) is for a specific object to update. In this case a campaign activity. The query adds to the where clause for the specific object from the input parameters.
The output of these queries includes a list of activity metric ids and corresponding actual values. Bulk Collect is used to maximize the processing of the query. These results are passed to ‘UPDATE_ACTMETRICS_BULK' to do a bulk update of the activity metrics table. This update also includes setting the dirty flag of the parent activity metrics to be refreshed.
A check should be made to prevent updating the actual value if it has not changed. For example, if the number of leads for a campaign activity is 20 and on refresh the leads are calculated to be 20 again then do not update the activity metric. The list of activity metric ids and actual values should not include these items. If the activity metric is updated then the refresh engine will attempt to recalculate the rollup and summary metrics, thus wasting processing.
A Procedure Metric must have the following parameters:
Parameter Name | Data Type | Default Value |
---|---|---|
p_arc_act_metric_used_by | VARCHAR2 | Null |
P_act_metric_used_by_id | NUMBER | Null |
This metric is executed once per refresh for each metric with the same procedure name. All values must be computed and saved in memory for a bulk update.
Procedure metrics are not supported for Cost or Revenue metrics. Once a cost metric has been posted to the budget the actual value cannot be updated. Currency conversions are expensive to process.
An API is used to perform the bulk update. Because of efficiency very little validation is performed.
Procedure Metric Sample
PROCEDURE run_metrics(p_arc_act_metric_used_by VARCHAR2 := NULL, p_act_metric_used_by_id NUMBER := NULL) IS l_actual_values_table num_table_type; l_activity_metric_id_table num_table_type; BEGIN IF p_arc_act_metric_used_by IS NULL THEN SELECT NVL(actual_value, 0), activity_metric_id bulk collect INTO l_actual_values_table, l_activity_metric_id_table FROM some_table ELSIF p_arc_act_metric_used_by = 'CSCH' THEN SELECT NVL(actual_value, 0), activity_metric_id bulk collect INTO l_actual_values_table, l_activity_metric_id_table FROM some_table WHERE object_type = 'CSCH' END IF; update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table); END;
See Sample Procedure Metric for Calculating Leads for a detailed use of Procedure Metrics.
Navigation: Administration > Marketing > General > Metric > Create
Notes
Calculation: Calculate Using Program.
Procedure Name: SCHEMANAME.RUN_METRICS
To test the metrics, specify the "used with" object as Campaign Activity. After the metric is created, assign it to a Campaign Activity. This procedure is executed when either the refresh button is pressed or the concurrent metrics engine executes. Verify results with source tables.
The following is a sample procedure Metric for Leads. This is an excerpt from package AMS_ACTMETRICS_SEED_PVT.
PROCEDURE Calculate_Seeded_Metrics( p_arc_act_metric_used_by VARCHAR2 := NULL, p_act_metric_used_by_id NUMBER := NULL) IS l_actual_values_table Ams_Actmetrics_Seed_Pvt.num_table_type; l_activity_metric_id_table Ams_Actmetrics_Seed_Pvt.num_table_type; BEGIN -- Get all object data to update. IF p_arc_act_metric_used_by IS NULL THEN SELECT NVL(actual_value, 0), activity_metric_id bulk collect INTO l_actual_values_table, l_activity_metric_id_table FROM (SELECT actual_value, activity_metric_id FROM( --R9 Campaign Schedule/Leads SELECT /*+ index(X AS_SALES_LEADS_N4) index(B ams_campaign_schedules_b_u1) index(ALB ams_metrics_all_b_n3) index(c ams_source_codes_u2) index(Y as_statuses_b_u1) index(AL AMS_ACT_METRICS_ALL_N5)*/ COUNT(1) actual_value, AL.activity_metric_id, AL.func_actual_value FROM ams_campaign_schedules_b B, ams_source_codes C, as_sales_leads X, as_statuses_b Y, ams_act_metrics_all AL, ams_metrics_all_b ALB WHERE X.status_code = Y.status_code AND B.schedule_id = C.source_code_for_id AND C.arc_source_code_for = ‘CSCH’ AND Y.lead_flag = 'Y' AND Y.enabled_flag = 'Y' AND B.source_code = C.source_code AND X.source_promotion_id = C.source_code_id AND NVL(X.DELETED_FLAG,'N') <> 'Y' AND AL.arc_act_metric_used_by = ‘CSCH’ AND AL.act_metric_used_by_id = B.schedule_id AND AL.metric_id = ALB.metric_id AND ALB.metric_category = 906 -- Leads AND ALB.function_name = 'AMS_ACTMETRICS_SEED_PVT.CALCULATE_SEEDED_METRICS' AND ALB.metric_calculation_type = 'FUNCTION' GROUP BY AL.activity_metric_id, AL.func_actual_value) WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1) ); ELSIF p_arc_act_metric_used_by = ‘CSCH’ THEN SELECT NVL(actual_value, 0), activity_metric_id bulk collect INTO l_actual_values_table, l_activity_metric_id_table FROM ( SELECT actual_value, activity_metric_id FROM( --R9 Campaign Schedule/Leads SELECT /*+ index(X AS_SALES_LEADS_N4) index(B ams_campaign_schedules_b_u1) index(ALB ams_metrics_all_b_n3) index(C ams_source_codes_u2) index(Y as_statuses_b_u1) index(AL AMS_ACT_METRICS_ALL_N5)*/ COUNT(1) actual_value, AL.activity_metric_id, AL.func_actual_value FROM ams_campaign_schedules_b B, ams_source_codes C, as_sales_leads X, as_statuses_b Y, ams_act_metrics_all AL, ams_metrics_all_b ALB WHERE X.status_code = Y.status_code AND B.schedule_id = C.source_code_for_id AND C.arc_source_code_for = ‘CSCH’ AND Y.lead_flag = 'Y' AND Y.enabled_flag = 'Y' AND B.source_code = C.source_code AND X.source_promotion_id = C.source_code_id AND NVL(X.DELETED_FLAG,'N') <> 'Y' AND AL.arc_act_metric_used_by = ‘CSCH’ AND AL.act_metric_used_by_id = B.schedule_id AND AL.metric_id = ALB.metric_id AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id AND ALB.metric_category = 906 -- Leads AND ALB.function_name = 'AMS_ACTMETRICS_SEED_PVT.CALCULATE_SEEDED_METRICS' AND ALB.metric_calculation_type = 'FUNCTION' GROUP BY AL.activity_metric_id, AL.func_actual_value ) WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1) ); END IF; update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table); END Calculate_Seeded_Metrics;
Local Variables
Two PL/SQL tables are needed to “bulk” capture the activity_metric_id and the calculated values corresponding to the ID's. The two tables should be of type num_table_type from the package ams_actmetrics_seed_pvt:
l_values_tab Ams_Actmetrics_Seed_Pvt.num_table_type;
l_ids_tab Ams_Actmetrics_Seed_Pvt.num_table_type;
The body of the procedure consists of code to select the values from a business-specific table. The business table needs to be able to join with a corresponding Marketing object table, such as Campaigns.
The activity metric table includes the id and object type of the corresponding object. For a campaign, the field arc_act_metric_used_by is ‘CAMP' and act_metric_used_by_id is the campaign id.
Object Type | Object Code | Table Column and Reference |
---|---|---|
Campaign | CAMP | AMS_CAMPAIGNS_ALL_B.CAMPAIGN_ID |
Campaign Activity | CSCH | AMS_CAMPAIGN_SCHEDULES_B.SCHEDULE_ID |
Event | EVEH | AMS_EVENT_HEADERS_ALL_B.EVENT_HEADER_ID |
Event Schedule | EVEO | AMS_EVENT_OFFERS_ALL_B.EVENT_OFFER_ID |
One-Off Event | EONE | AMS_EVENT_OFFERS_ALL_B.EVENT_OFFER_ID |
Deliverable | DELV | AMS_DELIVERABLES_ALL_B.DELIVERABLE_ID |
Dialog | DILG | AMS_DIALOGS_ALL_B.DIALOG_ID |
Within the object the specific relevant metrics can be referenced by the metric_id. Thus to write the select statement the "where" clause includes
WHERE Actmetric.Arc_act_metric_used_by = <object_code>
AND actmetric.act_metric_used_by_id = <object_id>
AND actmetric.metric_id = <metric_id>
Where the object_id is a join to the business table that references the marketing object. The metric_id needs to match the procedure metrics' id. To find the correct metric id query the metric table for the matching procedure name. For example:
SELECT metric_id, metrics_name, arc_metric_used_for_object
FROM ams_p_metrics_v
WHERE FUNCTION_NAME = 'SCHEMANAME.RUN_METRICS' -- Use upper case.
Procedure metric queries must utilize ‘BULK COLLECT' feature of PL/SQL to maximize performance. Bulk Collect minimizes the amount of context switching required by fetching values one at a time. Bulk Collect changes context to SQL to read in all values to PL/SQL arrays, which reduces context switching to once. After the data is fetched, the procedure must execute the bulk update procedure:
Ams_Actmetrics_Seed_Pvt.update_actmetrics_bulk (
l_activity_metric_ids_tab, l_actual_values_tab);
Function metrics execute the PL/SQL function for each instance of the activity metric on the system. The return value of a number is the actual value for the activity metric to update.
To understand and implement Function Metrics see the following:
The stored PL/SQL function or procedure name must be the full name which includes the schema name, package name, and module name (if applicable). Function metrics are similar to a manual metric leaf level metric, data values are entered, however, difference forecasted (marketing is expending) values vs. actual (what they actually spend).
A Function metric is executed once for each assigned metric to a marketing object. The parameter for the function is the activity metric id, which distinctly references the marketing object.
For the marketing object specification - use the following query parameters:
Table: AMS_ACT_METRICS_ALL
Object type: ARC_ACT_METRIC_USED_BY
Object id: ACT_METRIC_USED_BY_ID
The function metric returns the actual value to be set. In the case of Cost and Revenue metrics the actual value must be in the 'Default Currency'. (Use Fnd_Profile.Value ('AMS_DEFAULT_CURR_CODE') to derive the default currency). For Costs, once the cost is posted to a budget the actual value cannot be updated.
Because function metrics are executed once per object and the value is updated one at a time, this method is very slow when processing thousands of metrics.
The parameters include the activity metric id. Each execution of this function is for a single activity metric to retrieve the current actual value. The activity metric id is passed as the parameter. With this activity metric id the all the information about the activity metric can be accessed including the relevant object and current values. The actual value calculation may include counting a number of items, such as leads, or totaling values such as revenue.
The value returned is updated in the corresponding AMS_ACT_METRICS_ALL record. Currency conversion is performed for Cost and Revenue metrics.
FUNCTION run_metric(p_activity_metric_id IN NUMBER) RETURNS NUMBER IS L_actual_value NUMBER; BEGIN -- Query actual value SELECT actual_value INTO l_actual_value FROM business, ams_act_metrics_all actmetric WHERE actmetric.activity_metric_id = p_activity_metric_id AND business.id = actmetric.act_metric_used_by_id; RETURN l_actual_value; END;
Navigation: Administration > Marketing > General > Metric > Create
Notes
Calculation: Calculate Using Program. Enter the schema, package, and function name.
Summarize To: select a Parent level/and or Same Level object. This is optional and need only be performed if the function metric being created is to be Rolled up to a parent or Summarized at same level.
When creating a variable metric you are required to select a multiplier metric. A multiplier metric can be any manual or function metric with the same "Used With" object - is not a cost or revenue category.
The variable metric unit value can be entered by the user for forecast value. However, the actual unit value is calculated based on the actual value divided by the actual value of the multiplier metric.
When the variable metric is assigned to a marketing object the multiplier metric is also assigned (if not already present). When the metrics are refreshed the variable metric is updated.
For example, “postage costs” can be associated to a number of people in a target group (contacts). To contact them you have a mailer you sent out, and postage for this is.37/person. You have 500 people in the target contact group. The variable metric takes.37 and multiplies it by the # of people in your contact group.
Prerequisites: The multiplier metric must be defined before creating the variable metric
Navigation: Administration > Marketing > General > Metric > Create
Notes
Calculation: Enter value manually
Accrual: Set to Variable
Summarize To: If required, set summarization to parent or same level, as needed.
You can create formula metrics from the administration area. Log in as the Administrator and create the formula metrics.
Navigation: Administration > Marketing > Initiatives Setup > Metrics > Create
Notes
Calculation Type region: To simplify the process of choosing a "used with" object, select a calculation type up front. Select Summarize as the Calculation type. The "used with" object is automatically populated with "Any".
Display Type: Display type drives how the metric values are displayed in the UI. Select either integer, percent, or currency.
Category: Defaults to Cost. The Category determines the location of the formula metric within the end user interface. If a formula is associated with a Cost or Revenue category, then this formula is displayed within the Cost & Revenue side navigation menu. If the formula is associated with any other category (Response, Lead, Opportunity, and so on), then this formula is displayed within the Metric side navigation menu of the end user interface.
Used With: This drop down box displays the object that the formula metrics is associated with. Metrics sources used for a formula should be within the same object type as the formula metrics. The objects with which users can associate the formula metrics include- campaigns, campaign activities, events, event schedules, one-off events and deliverables. You can also select “Any” to associate the formula metric to any object type
Note: The Create page is generic for all metric types and some options, such as Aggregation, may not be applicable for all metric calculation types. For example, aggregation does not apply to formula metrics although it does appear in the Create page.
Calculation: Calculate Using Formula. This selection is required for formula metrics.
In the Formula table enter the following details:
Remove: Use this check box to remove a line item from the formula definition.
Source Type: To indicate the type for the formual component. The options include, Category, Metric, Number, and Operator. Note: In this release, six operators are supported: -, +, *, /, (, and ).
Source Name: Use to select the source for metric or category source types.
Sequence: Use this field to reorder the sequence of entries in the formula. You cannot reuse existing numbers within a sequence order.
Update: When you click this button, the formula is validated. If the formula is valid, the following message appears:
Formula is Valid.
Total Responses/Target Group Count
If the formula is not valid, the following message appears:
Formula is Invalid
the formula is displayed with a "^ - Error – " that indicates the spot of the error
You can create metrics for any object that exists in the Oracle Marketing application. After the metric has been created by the Administrator, the marketer will use the metric in association with the Campaign object being measured.
For example, if a Manual Metric called “total cost” has been created for the Campaign Object, a marketer will simply navigate to the Campaign Details page and select Metric. The Manual Metric "total cost" will be available and will be associated to the Campaign Object by the marketer. For more details, see the Oracle Marketing User Guide.
After implementing any type of metric, the concurrent manager for metrics must be set up. The Metrics Refresh Engine utilizes PL/SQL features to quickly, and correctly roll up values.
A check is made to see if any rollup metrics are missing. That is, a metric defined with a rollup parent, and the associated activity metric does not have a rollup parent.
If so, the rollup is created and the activity metric is updated to point to the new rollup metric.
Alternatively, if the rollup parent exists, the activity metric is update to point to the existing one.
All activity metrics marked with a dirty flag (indicates values may not be correctly totaled) are read into memory.
All parent activity metrics of the dirty metrics are also read into memory.
Each node in the dirty tree is calculated from the leaf level up.
Totals are kept in memory until the operation is completed.
On completion, values are bulk updated into the activity metrics table and the dirty flag is reset.
This process is broken into 2 parts: Rollups and Summaries.
Update History is optionally performed as indicated by an input parameter.
The following steps are made in the refresh process:
Prerequisites: Metric is properly set up
Navigation: Applications > Functions > Setups > Concurrent Requests > Submit a New Request
Notes
Concurrent Manager Program: AMS - Metrics refresh program.
Schedule the program to execute at least once daily.
Update History.
Metric history records the activity metric records on a daily basis. History is recorded by the AMS: Metric Refresh Program only when the Update_history flag is set to Yes.
The refresh must be executed on a daily basis for the history to be recorded. If Update_History has been set to No, then no history will be recorded. The history is recorded for each metric as a single record for a given day. If the metric record is updated more than once in a day, only the last value of that day is recorded. If a metric is not updated on a given day then no history is recorded.
The history can be shown by the history section on the Metrics and Cost & Revenue side navigation menus. Select the Metric Name, Start and End dates, Interval Value Type (cumulative or incremental), then select View History to show a table of values and charts. Recording history is required for support of Business Intelligence for Marketing (BIM).
Control Groups help analyze campaign effectiveness. The post activity analysis report provides a detailed illustration of the selected metric for the Contact and Control Group members, and how it can leverage the campaign. Marketers can use the Control Group Analysis report to compare the behavior of the Contact and Control Groups. This in turn helps them arrive at definitive conclusions on the effectiveness of their marketing activities.
From the Control Group report search page, marketers can drill down to the actual report with the values between the Contact and the Control Groups per Activity by clicking on the corresponding View Report icon. At an activity level, this report provides insight into the performance of the Control Group and Contact Group members.
Every metric line item in the metrics table contains a History icon. However, the Metric history column is not displayed by default in any of the seeded views. The user can personalize the seeded views to display the Metrics History icon. The Metric History icon is displayed for Rollup, Summary, Manual, Formula and Function metrics for both seeded and user-defined metrics.
The Metric History page displays only the actual values associated with the metric. Clicking on the history icon navigates the user to a new Metric History page. The following are the components of the metrics history page:
Metric Search Section
History Graph
Table of Data
Return to Link
A number of commonly used marketing metrics, such as Total Cost, Total Revenue, Responses Count and Event Registrants, are included in with the deployment of the Oracle Marketing application. The following is a listing of the metrics which are selectable for association to specific marketing activities/objects.
Metric Name | Metric Id | Used With Object | Calculation Type | Category | Metric Parent Id | Summary Metric Id |
Total Campaign Cost | 1 | Any | Summary | Cost | - | - |
Total Campaign Direct Cost | 2 | Any | Summary | Cost | - | 1 |
Rolled-up Campaign Deliverable Cost | 3 | Any | Rollup | Cost | 3 | 2 |
Rolled-up Campaign Event Cost | 4 | Any | Rollup | Cost | 4 | 2 |
Rolled-up Campaign Cost | 5 | Any | Rollup | Cost | 5 | 57 |
Total Campaign Indirect Cost | 6 | Any | Summary | Cost | - | 1 |
Total Campaign Revenue | 7 | Any | Summary | Revenue | - | - |
Rolled-up Campaign Revenue | 8 | Any | Rollup | Revenue | 8 | 62 |
Total Campaign Response | 9 | Any | Summary | Response | - | - |
Campaign Response | 10 | Campaign | Manual | Response | 11 | 64 |
Rolled-up Campaign Response | 11 | Any | Rollup | Response | 11 | 64 |
Total Campaign Activity Cost | 12 | Any | Summary | Cost | - | - |
Total Campaign Activity Direct Cost | 13 | Any | Summary | Cost | - | 12 |
Rolled-up Campaign Activity Deliverable Cost | 14 | Any | Rollup | Cost | 14 | 13 |
Rolled-up Campaign Activity Event Cost | 15 | Any | Rollup | Cost | 15 | 13 |
Direct Cost | 16 | Campaign Activity | Manual | Cost | 171 | 57 |
Total Campaign Activity Indirect Cost | 17 | Any | Summary | Cost | - | 12 |
Total Campaign Activity Revenue | 18 | Any | Summary | Revenue | - | - |
Revenue | 19 | Campaign Activity | Manual | Revenue | 8 | 62 |
Total Campaign Activity Response | 20 | Any | Summary | Response | - | - |
Response | 21 | Campaign Activity | Manual | Response | 11 | 64 |
Total Event Cost | 22 | Any | Summary | Cost | - | - |
Total Event Direct Cost | 23 | Any | Summary | Cost | - | 22 |
Rolled-up Event Deliverable Cost | 24 | Any | Rollup | Cost | 24 | 22 |
Rolled-up Event Campaign Cost | 25 | Any | Rollup | Cost | 25 | 23 |
Rolled-up Event Cost | 26 | Any | Rollup | Cost | 26 | 57 |
Total Event Indirect Cost | 27 | Any | Summary | Cost | - | 22 |
Total Event Revenue | 28 | Any | Summary | Revenue | - | - |
Rolled-up Event Revenue | 29 | Any | Rollup | Revenue | 29 | 62 |
Total Event Response | 30 | Any | Summary | Response | - | - |
Event Response | 31 | Event | Manual | Response | 32 | 64 |
Rolled-up Event Response | 32 | Any | Rollup | Response | 32 | 64 |
Total Event Schedule Cost | 33 | Any | Summary | Cost | - | - |
Total Event Schedule Direct Cost | 34 | Any | Summary | Cost | - | 33 |
Rolled-up Event Schedule Deliverable Cost | 35 | Any | Rollup | Cost | 35 | 34 |
Rolled-up Event Schedule Campaign Cost | 36 | Any | Rollup | Cost | 36 | 34 |
Direct Cost | 37 | Event Schedule | Manual | Cost | 172 | 57 |
Total Event Schedule Indirect Cost | 38 | Any | Summary | Cost | - | 33 |
Total Event Schedule Revenue | 39 | Any | Summary | Revenue | - | - |
Revenue | 40 | Event Schedule | Manual | Revenue | 29 | 62 |
Total Event Schedule Response | 41 | Any | Summary | Response | - | - |
Response | 42 | Event Schedule | Manual | Response | 32 | 64 |
Total Deliverable Cost | 43 | Any | Summary | Cost | - | - |
Total Deliverable Direct Cost | 44 | Any | Summary | Cost | - | 43 |
Creative Cost | 45 | Deliverable | Manual | Cost | - | 57 |
Other Cost | 46 | Deliverable | Manual | Cost | - | 57 |
Postage Cost | 47 | Deliverable | Manual | Cost | - | 57 |
Total Deliverable Indirect Cost | 48 | Any | Summary | Cost | - | 43 |
Total Deliverable Revenue | 49 | Any | Summary | Revenue | - | - |
Revenue | 50 | Deliverable | Manual | Revenue | - | 62 |
Total Deliverable Response | 51 | Any | Summary | Response | - | - |
Response | 52 | Deliverable | Manual | Response | - | 64 |
Other Cost | 53 | Campaign | Manual | Cost | 5 | 57 |
Other Cost | 54 | Event | Manual | Cost | 26 | 57 |
Allocation | 55 | Campaign | Allocation | Allocation | - | - |
Total Cost | 56 | Any | Summary | Cost | - | - |
Total Direct Cost | 57 | Any | Summary | Cost | - | 56 |
Rolled-up Deliverable Cost | 58 | Any | Rollup | Cost | 58 | 57 |
Rolled-up Event Program Cost | 59 | Any | Rollup | Cost | 59 | 57 |
Rolled-up Cost | 60 | Any | Rollup | Cost | 60 | 57 |
Total Indirect Cost | 61 | Any | Summary | Cost | - | 56 |
Total Revenue | 62 | Any | Summary | Revenue | ||
Rolled-up Revenue | 63 | Any | Rollup | Revenue | 63 | 62 |
Total Response | 64 | Any | Summary | Response | - | - |
Rolled-up Response | 65 | Any | Rollup | Response | 65 | 64 |
Campaign Response | 66 | Program | Manual | Response | 65 | 64 |
Other Cost | 67 | Program | Manual | Cost | 60 | 57 |
Allocation | 68 | Program | Allocation | Allocation | - | - |
Total One-Off Event Cost | 69 | Any | Summary | Cost | - | - |
Total One-Off Event Direct Cost | 70 | Any | Summary | Cost | - | 69 |
Rolled-up One-Off Event Deliverable Cost | 71 | Any | Rollup | Cost | 71 | 70 |
Rolled-up One-Off Event Campaign Cost | 72 | Any | Rollup | Cost | 72 | 70 |
Direct Cost | 73 | One-Off Event | Manual | Cost | 173 | 57 |
Total One-Off Event Indirect Cost | 74 | Any | Summary | Cost | - | 69 |
Total One-Off Event Revenue | 75 | Any | Summary | Revenue | - | - |
Revenue | 76 | One-Off Event | Manual | Revenue | 174 | 62 |
Total One-Off Event Response | 77 | Any | Summary | Response | - | - |
Response | 78 | One-Off Event | Manual | Response | 175 | 64 |
Total Leads | 81 | Any | Summary | Leads | - | - |
Rolled-up Campaign Leads | 82 | Any | Rollup | Leads | 82 | 81 |
Leads | 83 | Campaign Activity | Function | Leads | 82 | 81 |
Leads | 84 | Event Schedule | Function | Leads | 86 | 81 |
Leads | 85 | One-Off Event | Function | Leads | 87 | 81 |
Rolled-up Event Leads | 86 | Any | Rollup | Leads | 86 | 81 |
Rolled-up One-Off Event Leads | 87 | Any | Rollup | Leads | 87 | 81 |
Total Opportunities | 91 | Any | Summary | Opportunities | - | - |
Rolled-up Campaign Opportunities | 92 | Any | Rollup | Opportunities | 92 | 91 |
Opportunities | 93 | Campaign Activity | Function | Opportunities | 92 | 91 |
Opportunities | 94 | Event Schedule | Function | Opportunities | 96 | 91 |
Opportunities | 95 | One-Off Event | Function | Opportunities | 97 | 91 |
Rolled-up Event Opportunities | 96 | Any | Rollup | Opportunities | 96 | 91 |
Rolled-up One-Off Event Opportunities | 97 | Any | Rollup | Opportunities | 97 | 91 |
Total Orders Amount | 101 | Any | Summary | Orders Amount | - | |
Rolled-up Campaign Orders Amount | 102 | Any | Rollup | Orders Amount | 102 | 101 |
Orders Amount | 103 | Event Schedule | Function | Orders Amount | 106 | 101 |
Orders Amount | 104 | One-Off Event | Function | Orders Amount | 107 | 101 |
Orders Amount | 105 | Campaign Activity | Function | Orders Amount | 102 | 101 |
Rolled-up Event Orders Amount | 106 | Any | Rollup | Orders Amount | 106 | 101 |
Rolled-up One-Off Event Orders Amount | 107 | Any | Rollup | Orders Amount | 107 | 101 |
Total Registrants | 121 | Any | Summary | Registrants | - | - |
Rolled-up Event Registrants | 122 | Any | Rollup | Registrants | 122 | 121 |
Registrants | 123 | Event Schedule | Function | Registrants | 122 | 121 |
Registrants | 124 | One-Off Event | Function | Registrants | 125 | 121 |
Rolled-up One-Off Event Registrants | 125 | Any | Rollup | Registrants | 125 | 121 |
Total Attendees | 131 | Any | Summary | Attendees | - | - |
Rolled-up Event Attendees | 132 | Any | Rollup | Attendees | 132 | 131 |
Attendees | 133 | Event Schedule | Function | Attendees | 132 | 131 |
Attendees | 134 | One-Off Event | Function | Attendees | 135 | 131 |
Rolled-up One-Off Event Attendees | 135 | Any | Rollup | Attendees | 135 | 131 |
Total Cancellations | 141 | Any | Summary | Cancellations | - | - |
Rolled-up Event Cancellations | 142 | Any | Rollup | Cancellations | 142 | 141 |
Cancellations | 143 | Event Schedule | Function | Cancellations | 142 | 141 |
Cancellations | 144 | One-Off Event | Function | Cancellations | 145 | 141 |
Rolled-up One-Off Event Cancellations | 145 | Any | Rollup | Cancellations | 145 | 141 |
Total Orders Count | 151 | Any | Summary | Orders Count | - | - |
Rolled-up Campaign Orders Count | 152 | Any | Rollup | Orders Count | 152 | 151 |
Rolled-up Event Orders Count | 153 | Any | Rollup | Orders Count | 153 | 151 |
Rolled-up One-Off Event Orders Count | 154 | Any | Rollup | Orders Count | 154 | 151 |
Orders Count | 155 | Campaign Activity | Function | Orders Count | 152 | 151 |
Orders Count | 156 | Event Schedule | Function | Orders Count | 153 | 151 |
Orders Count | 157 | One-Off Event | Function | Orders Count | 154 | 151 |
Total Responses Count | 161 | Any | Summary | Response | - | - |
Rolled-up Campaign Responses Count | 162 | Any | Rollup | Response | 162 | 161 |
Rolled-up Event Responses Count | 163 | Any | Rollup | Response | 163 | 161 |
Rolled-up One-Off Event Responses Count | 164 | Any | Rollup | Response | 164 | 161 |
Responses Count | 165 | Campaign Activity | Function | Response | 162 | 161 |
Responses Count | 166 | Event Schedule | Function | Response | 163 | 161 |
Responses Count | 167 | One-Off Event | Function | Response | 164 | 161 |
Rolled-up Campaign Direct Cost | 171 | Any | Rollup | Cost | 171 | 57 |
Rolled-up Event Direct Cost | 172 | Any | Rollup | Cost | 172 | 57 |
Rolled-up One-Off Event Direct Cost | 173 | Any | Rollup | Cost | 173 | 57 |
Rolled-up One-Off Event Revenue | 174 | Any | Rollup | Revenue | 174 | 62 |
Rolled-up One-Off Event Response | 175 | Any | Rollup | Response | 175 | 64 |
Rolled-up Campaign Indirect Cost | 176 | Any | Rollup | Cost | 176 | 17 |
Rolled-up Event Indirect Cost | 177 | Any | Rollup | Cost | 177 | 38 |
Rolled-up One-Off Event Indirect Cost | 178 | Any | Rollup | Cost | 178 | 74 |