Adding Attributes to be Included in Campaign Analysis
Campaign Analysis enables you to identify the customer attributes (groups of customers) who are most responsible for permanence anomalies identified in the campaign being investigated. See the Campaign Analysis page in the Oracle Help Centre for more information.
The following steps should be taken to add or edit the attributes that will be available for the campaign analysis.
NOTE: It is currently recommended to have a maximum of 8 attributes. Each attribute selected should only have up to 8 values. The attributes should not be Intelligent Attributes and they should not be aggregated attributes.
- Add the new attribute to both the Customer data object and the MasterCustomer data object.
- Call Retrieve a data object on Customer to check if a similar attribute already exists.
- If one does not exist, call PUT /metadata/tables/{tableId}/columns on Customer with the response from the previous step and add the following to the columns array:
{ "tenantId": <mcpsTenantId>, "name": "<attribute_name>", "description": "<attribute_desc>", "active": true, "type": { "columntype": ".ColumnPrimitiveType", "dataType": "<attribute_type>" }, "tableId": "Customer", "fieldId": "<attribute_fieldId>", "systemAttribute": false, "required": false }- Call Retrieve a data object on MasterCustomer to check if a similar attribute already exists.
- If one does not exist, call PUT /metadata/tables/{tableId}/columns on MasterCustomer with the response from the previous step and add the following to the columns array:
{ "tenantId": <mcpsTenantId>, "name": "<attribute_name>", "description": "<attribute_desc>", "active": true, "type": { "columntype": ".ColumnPrimitiveType", "dataType": "<attribute_type>" }, "tableId": "MasterCustomer", "fieldId": "<attribute_fieldId>", "systemAttribute": false, "required": false } - Check to see if there is a promotion rule to pick the latest value for this new attribute from Customer to MasterCustomer with whatever order or configuration you want the value to get promoted with.
- Call GET /metadata/aggregatedtables/{aggregatedTableId}/promotionrules on MasterCustomer to see if one already exists.
- If one does not exist, call POST /metadata/aggregatedtables/{aggregatedTableId}/promotionrules on MasterCustomer and add it. Here is an example where you wish to promote the value based on the latest data:
{ "tenantId": <tenantId>, "name": "DemographyAttributes_Promotion_Rule", "active": true, "tableId": "MasterCustomer", "ruleId": "DemographyAttributes_Promotion_Rule1", "atomic": true, "outputAttributes": [ { "atype": ".ReferenceAttribute", "tableName": "customer", "attributeName": "<attr_name>" } ], "configs": [ { "tenantId": <tenantId>, "name": "confId_1", "active": true, "tableId": "MasterCustomer", "ruleId": "DemographyAttributes_Promotion_Rule1", "configId": "confId_1", "mergeType": "MAX", "attribute": { "atype": ".ReferenceAttribute", "tableName": "customer", "attributeName": "RowModifiedTS" }, "order": 1 } ] } - Publish the changes to the MCPS tenant. The new attribute should be present on the MasterCustomer table at this point.
- Add this new attribute to be picked up from the MasterCustomer table to the existing DSV EVENT_CUBE_REPORT if a similar attribute does not exist already.
- Call Retrieve a data source view on EVENT_CUBE_REPORT to see if a similar attribute does not exist already.
- If one does not exist, then call Update a data source view on EVENT_CUBE_REPORT with the response from the previous step with the following attributes added.
- Add the sourceTableQuery.MCPSQuery.operation.outputAttributes array with the following attribute:
{ "atype": ".ReferenceAttribute", "tableName": "Top_QRY", "attributeName": "<attr_fieldId>" }- Add the sourceTableQuery.MCPSQuery.operation.operands[0].outputAttributes array with the following attribute:
{ "atype": ".ReferenceAttribute", "tableName": "Top_QRY", "attributeName": "<attr_fieldId>", "alias": "<attr_name>" } - Add this new attribute to as a new dimension and also to the groupedBy to the existing CUBE CR_EventCube if a similar attribute does not exist already.
- Call Retrieve a cube on the model CR_EventModel and the CUBE CR_EventCube to see if a similar attribute does not exist already.
- If not, call Update a cube on the model CR_EventModel and the CUBE CR_EventCube with the response from the previous step, update the CUBE state to
DELETED, add new attributes todimensionarray, andaggregationSettingsin the same payload.- Add the new attribute to dimensions array:
{ "tenantId": <mcpsTenantId>, "name": "<attr_name>", "active": true, "dimensionId": "<attr_name>", "cubeId": "CR_EventCube", "sourceAttribute": { "atype": ".DSVAttribute", "dsvId": "EVENT_CUBE_REPORT", "attributeName": "<attr_name>" }, "shardByColumn": false }- Add the new attribute to aggregationSettings[k].grouped and aggregationSettings[k].attributeList arrays where k is the index of the array which already has OOTB Gender mentioned for B2C and for B2B, Auto-B2B, it would be
JobTitle:
{ "atype": ".DSVAttribute", "dsvId": "EVENT_CUBE_REPORT", "attributeName": "<attr_name>" } - Rebuild the CUBE so that the queries can now work with this new attribute.
- Run the Publish job to delete the CUBE.
- Update the same CUBE state to ACTIVE and run Publish again to build the cube with the new metadata
- Run DW and Analytics to rebuild the CUBE to populate the cube with th new attributes' data.
-
-
The following are required for this step:
-
IDCS client app details like
DCS_URL,client_idandclient_secret. -
CxUnityTenanthost url and its login details likeusernameandpassword. For more details related to IDCS authentication, see Authenticate with OAuth 2.0. -
MCPS tenant id details. Retrieve MCPS tenant id using path
hostURL/cxunity/infrastructure/v1/me. Sample response would be:{"user":{"id":"XX","userName":"XX.XX@oracle.com","email":"XX.XX@oracle.com","firstName":"XX","lastName":"XX","isAdmin":false},"cxunityTenant":{"id":"XX","name":"CXU4Auto","idcsUrl":"https://idcs-XX.identity.oraclecloud.com"},"mcpsTenants":[{"id":100032,"key":"XX","displayName":""}]}
In the above response, MCPS tenant id is 100032.
-
Download this collection and import it in Postman.
-
-
For the collection, go to the Variables section and change the details according to the respective tenant in the CURRENT VALUE attribute.
-
Once every thing is ready in postman collection, go to collection request Enable Campaign Analysis attribute and in the body section, add or update the attributes which you want as part of campaign analysis.
Note: This is the list of dimension attributes you have created in step 5.
-
Select collection and click on the Run button to execute the API.
-
Once the API has run successfully, you will see the 200 and 201 responses.
-
Queries
For each demographic attribute that the customer selects, a separate query is fired which groups the metrics by that particular demographic attribute. So for example, when we have 6 demographic categories that the customer wants to analyse the campaigns on, the client will fire 6 different MCPS queries and merge the response sets of those to build the widget.
Each query that powers this analysis can be broken down like this:
- Metrics to be calculated are
- OpenRate
- ClickRate
- ClickToOpenRate
- BounceRate
- Revenue
- TargetedCustomers
- Look back period is the full time period the cube currently supports (180 days)
- Grouping on
- CampaignId
- CampaignName
- Selected demography category
- Filtering will be an AND on
- Campaign IDs,
- Any demographic attribute and its multiple filter values
Example SQL Query
Following is a sample generated SQL for one single query.
- <USER_ATTRIBUTE_GROUP> is replaced with the user attribute group like Gender when you want to group the query with Gender.
- <OTHER_CRITERIA> is replaced with more filters when the customer selects other user attributes to do a drill down on them.
SELECT EVENT_CUBE_REPORT.CampaignID AS CampaignID, EVENT_CUBE_REPORT.CampaignName, EVENT_CUBE_REPORT.Revenue, EVENT_CUBE_REPORT.Demography, EVENT_CUBE_REPORT.DemographyVal, EVENT_CUBE_REPORT.TargetedCustomers AS TargetedCustomers, EVENT_CUBE_REPORT.OpenedCount, EVENT_CUBE_REPORT.ClickedCount, EVENT_CUBE_REPORT.OpenRate, EVENT_CUBE_REPORT.ClickRate, EVENT_CUBE_REPORT.ClickToOpenRate
FROM
(SELECT EVENT_CUBE_REPORT.CampaignID AS CampaignID, EVENT_CUBE_REPORT.CampaignName, COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount) AS TargetedCustomers, CONCAT('<USER_ATTRIBUTE_GROUP>', ' ') AS Demography, EVENT_CUBE_REPORT.<USER_ATTRIBUTE_GROUP> AS DemographyVal, SUM(EVENT_CUBE_REPORT.forOpenedCount) AS OpenedCount, SUM(EVENT_CUBE_REPORT.forClickedCount) AS ClickedCount, (cast(coalesce(SUM(EVENT_CUBE_REPORT.forOpenedCount), 0) as FLOAT) / cast(CASE WHEN (coalesce(COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount), 0) <= 0) THEN 1 ELSE COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount) END as FLOAT)) AS OpenRate, (cast(coalesce(SUM(EVENT_CUBE_REPORT.forClickedCount), 0) as FLOAT) / cast(CASE WHEN (coalesce(COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount), 0) <= 0) THEN 1 ELSE COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount) END as FLOAT)) AS ClickRate, (cast(coalesce(SUM(EVENT_CUBE_REPORT.forClickedCount), 0) as FLOAT) / cast(CASE WHEN (coalesce(SUM(EVENT_CUBE_REPORT.forOpenedCount), 0) <= 0) THEN 1 ELSE SUM(EVENT_CUBE_REPORT.forOpenedCount) END as FLOAT)) AS ClickToOpenRate, SUM(EVENT_CUBE_REPORT.OrderTotal) AS Revenue
FROM
tmp_mcpsDSV_EVENT_CUBE_REPORT_cubeanalytics EVENT_CUBE_REPORT
WHERE (
(EVENT_CUBE_REPORT.CampaignID IN ('SRC_99637468134551062893', 'SRC_99529651835387796918', 'SRC_97999009644536536659', 'SRC_95859683215510275913'))
AND
<OTHER_CRITERIA>
)
GROUP BY EVENT_CUBE_REPORT.CampaignID, EVENT_CUBE_REPORT.CampaignName, EVENT_CUBE_REPORT.<USER_ATTRIBUTE_GROUP>
HAVING ((COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount) > 0))
ORDER BY CampaignID DESC
) EVENT_CUBE_REPORT
LIMIT 100
Example MCPS Query
Following is a sample MCPS query for one single query.
- $userCategory is replaced with the user attribute group like Gender when you want to group the query with Gender.
- $criteria is replaced with more filters when the customer selects other user attributes to do a drill down on them.
{
"MCPSQuery": {
"operation": {
"ctype": ".SetOperation",
"name": "setOp1",
"tenantId": 0,
"uniqueId": "setOp1",
"operands": [
{
"ctype": ".ObjectSet",
"tenantId": 0,
"name": "EVENT_CUBE_REPORT",
"description": "EVENT_CUBE_REPORT",
"objectName": "EVENT_CUBE_REPORT",
"uniqueId": "EVENT_CUBE_REPORT",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "CampaignID",
"alias": "CampaignID"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "CampaignName"
},
{
"atype": ".AggregateAttribute",
"type": "COUNT",
"attribute": {
"atype": ".FunctionAttribute",
"type": "DISTINCT",
"attributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forTargetedCount"
}
]
},
"alias": "TargetedCustomers"
},
{
"atype": ".FunctionAttribute",
"type": "CONCATENATE",
"alias": "Demography",
"attributes": [
{
"atype": ".StaticAttribute",
"data": "${userCategory}"
},
{
"atype": ".StaticAttribute",
"data": " "
}
]
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "${userCategory}",
"alias": "DemographyVal"
},
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forOpenedCount"
},
"alias": "OpenedCount"
},
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forClickedCount"
},
"alias": "ClickedCount"
},
{
"atype": ".FunctionAttribute",
"type": "DIVIDE",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CAST",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CUSTOM",
"attributes": [
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forOpenedCount"
}
},
{
"atype": ".StaticAttribute",
"data": 0
}
],
"functionName": "coalesce"
},
{
"atype": ".StaticAttribute",
"data": "FLOAT"
}
]
},
{
"atype": ".FunctionAttribute",
"type": "CAST",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CASE",
"attributes": [
{
"atype": ".CriteriaAttribute",
"criteria": {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [
{
"ctype": ".FunctionAttribute",
"type": "CUSTOM",
"attributes": [
{
"atype": ".AggregateAttribute",
"type": "COUNT",
"attribute": {
"atype": ".FunctionAttribute",
"type": "DISTINCT",
"attributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forTargetedCount"
}
]
}
},
{
"atype": ".StaticAttribute",
"data": 0
}
],
"functionName": "coalesce"
},
{
"ctype": ".StaticAttribute",
"data": 0
}
]
}
},
{
"atype": ".StaticAttribute",
"data": 1
},
{
"atype": ".AggregateAttribute",
"type": "COUNT",
"attribute": {
"atype": ".FunctionAttribute",
"type": "DISTINCT",
"attributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forTargetedCount"
}
]
}
}
]
},
{
"atype": ".StaticAttribute",
"data": "FLOAT"
}
]
}
],
"alias": "OpenRate"
},
{
"atype": ".FunctionAttribute",
"type": "DIVIDE",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CAST",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CUSTOM",
"attributes": [
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forClickedCount"
}
},
{
"atype": ".StaticAttribute",
"data": 0
}
],
"functionName": "coalesce"
},
{
"atype": ".StaticAttribute",
"data": "FLOAT"
}
]
},
{
"atype": ".FunctionAttribute",
"type": "CAST",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CASE",
"attributes": [
{
"atype": ".CriteriaAttribute",
"criteria": {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [
{
"ctype": ".FunctionAttribute",
"type": "CUSTOM",
"attributes": [
{
"atype": ".AggregateAttribute",
"type": "COUNT",
"attribute": {
"atype": ".FunctionAttribute",
"type": "DISTINCT",
"attributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forTargetedCount"
}
]
}
},
{
"atype": ".StaticAttribute",
"data": 0
}
],
"functionName": "coalesce"
},
{
"ctype": ".StaticAttribute",
"data": 0
}
]
}
},
{
"atype": ".StaticAttribute",
"data": 1
},
{
"atype": ".AggregateAttribute",
"type": "COUNT",
"attribute": {
"atype": ".FunctionAttribute",
"type": "DISTINCT",
"attributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forTargetedCount"
}
]
}
}
]
},
{
"atype": ".StaticAttribute",
"data": "FLOAT"
}
]
}
],
"alias": "ClickRate"
},
{
"atype": ".FunctionAttribute",
"type": "DIVIDE",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CAST",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CUSTOM",
"attributes": [
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forClickedCount"
}
},
{
"atype": ".StaticAttribute",
"data": 0
}
],
"functionName": "coalesce"
},
{
"atype": ".StaticAttribute",
"data": "FLOAT"
}
]
},
{
"atype": ".FunctionAttribute",
"type": "CAST",
"attributes": [
{
"atype": ".FunctionAttribute",
"type": "CASE",
"attributes": [
{
"atype": ".CriteriaAttribute",
"criteria": {
"ctype": ".Criteria",
"operator": "LESS_EQUAL_THAN",
"operands": [
{
"ctype": ".FunctionAttribute",
"type": "CUSTOM",
"attributes": [
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forOpenedCount"
}
},
{
"atype": ".StaticAttribute",
"data": 0
}
],
"functionName": "coalesce"
},
{
"ctype": ".StaticAttribute",
"data": 0
}
]
}
},
{
"atype": ".StaticAttribute",
"data": 1
},
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forOpenedCount"
}
}
]
},
{
"atype": ".StaticAttribute",
"data": "FLOAT"
}
]
}
],
"alias": "ClickToOpenRate"
},
{
"atype": ".AggregateAttribute",
"type": "SUM",
"attribute": {
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "OrderTotal"
},
"alias": "Revenue"
}
],
"distinct": false,
"criteria": ${criteria},
"havings": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [
{
"ctype": ".Criteria",
"operator": "GREATER_THAN",
"operands": [
{
"ctype": ".AggregateAttribute",
"type": "COUNT",
"attribute": {
"atype": ".FunctionAttribute",
"type": "DISTINCT",
"attributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "forTargetedCount"
}
]
}
},
{
"ctype": ".StaticAttribute",
"data": 0
}
]
}
]
},
"groupBy": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "CampaignID"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "CampaignName"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "${userCategory}"
}
],
"sortBy": [
{
"atype": ".SortedAttribute",
"order": "DESC",
"attribute": {
"atype": ".ReferenceAttribute",
"alias": "CampaignID"
}
}
]
}
],
"operator": "UNION",
"distinct": false,
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "CampaignID",
"alias": "CampaignID"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "CampaignName"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "Revenue"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "Demography"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "DemographyVal"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "TargetedCustomers",
"alias": "TargetedCustomers"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "OpenedCount"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "ClickedCount"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "OpenRate"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "ClickRate"
},
{
"atype": ".ReferenceAttribute",
"tableName": "EVENT_CUBE_REPORT",
"attributeName": "ClickToOpenRate"
}
],
"joinConditions": []
},
"type": "CUBE"
}
}