B2C Analytics playbook
Oracle Unity analytics widgets have multiple metrics for calculating analytics data. These widgets need to bring in data for the metrics from various data objects and attributes in the Oracle Unity data model. To properly display data for these widgets and metrics, you need to map out how to link your organization's customer data to Oracle Unity data model objects and attributes. This document explains how the data needs to be mapped.
Note: The campaign data used for creating the widgets is imported from Oracle Responsys.
Once the data is properly configured, it will be displayed in the following pages: Home page, One time campaigns, Recurring campaigns, Campaign analysis.
To learn more about importing data into Oracle Unity, refer to the following:
Subtype attribute
The Order Item data object uses the Subtype attribute to identify the status of an order.
There are four attribute values for Subtype:
- Demand (an order was placed but not shipped),
- Shipped (item was sent to customer),
- Canceled (order was canceled before it was shipped),
- Returned (order was received then canceled).
To correctly configure the data for this attribute, every change in order status needs to generate a new record and not override an older order status record. For example, for every Returned value, there will also have to be a record for Demand and Shipped.
Medium, Target, and Type attributes
Email is a ready-to-use attribute value for the Medium and Target attributes.
The Type attribute has the following ready-to-use attribute values:
- Sent
- Opened
- Clicked
- Bounced
- Subscribed
- Unsubscribed
- Spam
Key metrics widget
The Key metrics widget displays important customer metrics to your organization and allows you to assess the current state of your customer base.
To display six time periods in the widget, at least six months of historical data needs to be imported into Oracle Unity.
Data requirements
You can select to map out any of the following metrics so that they appear in the widget.
| Metric | Data object | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Average order value | Order Item |
Subtype Extended price |
Shipped for Subtype Total amount for Extended price |
Sum of Total amount/Sum of Shipped values
The count is calculated at the order level, although it checks subtype at order item level. |
| Bounce rate (email) | Event | Medium Target Type |
Email for Medium and Target Bounced and Sent for Type |
Bounced/Sent x 100 |
| Click rate (email) | Event | Medium Target Type |
Email for Medium and Target Clicked and Sent for Type |
Clicked/Sent x 100 |
| Click to open rate | Event | Medium Target Type |
Email for Medium and Target Opened and Clicked for Type |
Clicked/Opened x 100 |
| Engaged customers | Event | Medium Target Type |
Email for Medium and Target Opened, Clicked, Subscribed, and Unsubscribed for Type You can create custom events with custom attribute values for your remaining marketing channels |
Sum of customers that have at least one engagement attribute value |
| Open rate (email) | Event | Medium Target Type |
Email for Medium and Target Sent and Opened for Type |
Opened/Sent x 100 |
| Opted in | Event | Medium Target Type |
Email for Medium and Target Subscribed for Type You can create custom events with custom attribute values for your remaining marketing channels |
Sum of Subscribed values |
| Opted out | Event | Medium Target Type |
Email for Medium and Target Unsubscribed for Type You can create custom events with custom attribute values for your remaining marketing channels |
Sum of Unsubscribed values |
| Purchasers | Order Item | Subtype | Demand and Shipped | Sum of customers that have both Demand and Shipped values for at least one order |
| Revenue | Organization Order Item |
Type for Organization Extended Price and Subtype for Order Item |
Online and In-store for Type Shipped and Returned for Subtype Total amount for Extended Price |
Sum of Total amount values with Shipped and Returned attribute values |
| Returners | Order Item | Subtype | Returned | Calculated as COUNT_DISTINCT(ReturnerCount) from CR_OrderCube ReturnerCount is derived as customerId when total_return_count > 0 total_return_count is derived as a metric from total_return_count from CA_OrderItem cube total_return_count metric in the cube is calculated as COUNT_DISTINCT(return_orderid) return_orderid is OrderItem.orderid when OrderItem.SubType = Returned |
| Returns | Order Item | Subtype | Returned | Derived from total_return_count from MasterCustomer total_return_count is derived as a metric from total_return_count from CA_OrderItem cube total_return_count metric in the cube is calculated as COUNT_DISTINCT(return_orderid) return_orderid is OrderItem.orderid when OrderItem.SubType = Returned |
| Targeted customers | Event | Medium Target Type |
Email for Medium and Target Sent for Type |
Sum of Sent values |
| Total orders | Order Item | Subtype | Shipped, Returned, and Canceled |
Sum of Shipped, Returned, and Canceled values
The count is calculated at the order level, although it checks subtype at order item level. |
| Web visitors | Event | Cookie | Values that are not Null | Sum of not Null values |
One time campaigns data (widget, performance page, and campaign analysis)
One time campaigns data is available in the following areas: the One time campaigns widget on the Analytics dashboard, the One time campaigns page, and the Campaign analysis page.
One time campaigns widget
The One time campaigns widget displays the performance of the 10 most recent one time campaigns that occurred in the past 90 days.
A one-time campaign is a campaign that is created for a specific marketing event, and all sent events are sent within seven days of the campaign launch date. The launch date is determined by the date the first event was sent.
The One time campaigns widget has the following metrics:
-
Average order value
-
Click rate
-
Conversion rate
-
Customer count
-
Open rate
-
Revenue
One time campaigns performance page
The One time campaigns page gives a more expansive look at metrics for each one time campaign.
The One time campaigns page has the following metrics:
-
Average order value
-
Bounce rate
-
Channel
-
Click rate
-
Click -to-open rate
-
Conversion rate
-
Customer count
-
Open rate
-
Revenue
Campaign analysis page
The Campaign analysis page allows you to investigate problematic campaigns, identify the groups of contacts that are responsible for the poor performance, and re-target the problematic contacts.
The Campaign analysis page has the following metrics:
-
Average order value
-
Click rate
-
Click to open rate
-
Conversion rate
-
Open rate
One time campaign metrics
Review the requirements for one time campaign metrics.
| Metric | Data object | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Average order value | Order Item |
Subtype Extended price |
Shipped for Subtype Total amount for Extended price |
Sum of Total amount/Sum of Shipped values |
| Click rate (email) | Event | Medium Target Type |
Email for Medium and Target Clicked and Sent for Type |
Clicked/Sent x 100 |
| Click to open rate (email) | Event | Medium Target Type |
Email for Medium and Target Opened and Clicked for Type |
Clicked/Opened x 100 |
| Conversion rate (email) | Event | Medium Target Type |
Email for Medium and Target Sent, Clicked, and Sent for Type |
Calculated as COUNT(DISTINCT EVENT_CUBE_REPORT.forConversionCount)/ COUNT(DISTINCT EVENT_CUBE_REPORT.forTargetedCount) forConversionCount is defined as customerid when OrderTotal > 0 forTargetedCount is defined as customerid when Type is Sent |
| Customer count | Event | Medium Target Type |
Email for Medium and Target Sent for Type |
Sum of Sent values |
| Open rate (email) | Event | Medium Target Type |
Email for Medium and Target Sent and Opened for Type |
Opened/Sent x 100 |
| Revenue | Event | Medium Target Type Order Total |
Email for Medium and Target Sent for Type |
Sum of Order Total values for Email event records |
Recurring campaigns data (widget and performance page)
Recurring campaigns data is available in the following areas: the Recurring campaigns widget on the Analytics dashboard and the Recurring campaigns page.
Recurring campaigns widget
The Recurring campaigns widget displays the performance of active recurring campaigns, which are campaigns that have had triggered events in the time period selected and also the time period prior to that triggered event. The launch date is determined by the date the first event was sent.
A recurring campaign is a campaign whose marketing messages are sent based on triggers of a customer's behavior or their profile.
The Recurring campaigns widget has the following metrics:
-
Bounce rate
-
Click rate
-
Click-to-open rate
-
Open rate
Recurring campaigns page
The Recurring campaigns page gives a more expansive look at metrics for each active recurring campaign.
The Recurring campaigns page has the following metrics:
-
Bounce rate
-
Click rate
-
Click-to-open rate
-
Open rate
Recurring campaigns metrics
Review the requirements for recurring campaign metrics.
| Metric | Data object | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Bounce rate | Event | Medium Target Type |
Email for Medium and Target Bounced and Sent for Type |
Bounced/Sent x 100 |
| Click rate | Event | Medium Target Type |
Email for Medium and Target Clicked and Sent for Type |
Clicked/Sent x 100 |
| Click to open rate | Event | Medium Target Type |
Email for Medium and Target Opened and Clicked for Type |
Clicked/Opened x 100 |
| Open rate | Event | Medium Target Type |
Email for Medium and Target Sent and Opened for Type |
Opened/Sent x 100 |
Engagement by channel widget
The Engagement by channel widget displays a breakdown of how your customers engaged with your organization's marketing channels. The widget displays the percentage of customers whose "initial touchpoint" of their engagement were exclusively in a single channel versus customers who engaged on multiple channels within the aggregated time period selected. Examples of channels to include in the widget include email, web, store, direct, and mobile.
Data requirements
There is only one metric available to display in the widget.
| Metric | Data object | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Engagement by channel percentages | Event | Medium Target |
Set custom attribute values for your engagement channel names. For example, Email, Web, Store, Direct, and Mobile. | Sum of customers that engage with a channel/Total customer count x 100 |
In-store vs online widget
The In-store vs online widget widget displays a comparison of trends between in-store and online transaction data for revenue and orders.
This widget uses two data objects: Organization and Order Item. The Order Item data object has a relationship with the Organization data object and they are both linked through the SourceOrganizationID attribute. Make sure that the SourceOrganization attribute in the Order Item data object has up-to-date records so that the data between the two data objects is properly linked.
Data requirements
This widget displays comparisons of the Orders or Revenue metrics.
| Metric | Data objects | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Orders | Organization Order Item |
Type for Organization Subtype for Order Item |
Online and In-store for Type Shipped or Demand for Subtype |
Sum of Shipped or Demand values |
| Revenue | Organization Order Item |
Type for Organization Extended Price and Subtype for Order Item |
Online and In-store for Type Shipped and Returned for Subtype Total amount for Extended Price |
Sum of Total amount values with Shipped and Returned attribute values |
Comparative Trends widget
The Comparative trends widget widget displays trends of up to four metrics over time. The graph in the widget displays the percentage change of the metric compared to the previous time period.
For the Revenue metric, two data objects are needed: Organization and Order Item. The Order Item data object has a relationship with the Organization data object and they are both linked through the SourceOrganizationID attribute. Make sure that the SourceOrganization attribute in the Order Item data object has up-to-date records so that the data between the two data objects is properly linked.
| Metric | Data objects | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Average order value | Order Item |
Subtype Extended price |
Shipped for Subtype Total amount for Extended price |
Sum of Total amount/Sum of Shipped values |
| Click rate | Event | Medium Target Type |
Email for Medium and Target Clicked and Sent for Type |
Clicked/Sent x 100 |
| Click to open rate | Event | Medium Target Type |
Email for Medium and Target Opened and Clicked for Type |
Clicked/Opened x 100 |
| Engaged customers | Event | Medium Target Type |
Email for Medium and Target Opened, Clicked, Subscribed, and Unsubscribed for Type You can create custom events with custom attribute values for your remaining marketing channels |
Sum of customers that have at least one engagement attribute value |
| Open rate | Event | Medium Target Type |
Email for Medium and Target Sent and Opened for Type |
Opened/Sent x 100 |
| Orders | Organization Order Item |
Type for Organization Subtype for Order Item |
Online and In-store for Type Shipped or Demand for Subtype |
Sum of Shipped or Demand values |
| Purchasers | Order Item | Subtype | Demand and Shipped | Sum of customers that have both Demand and Shipped values for at least one order |
| Revenue | Organization Order Item |
Type for Organization Extended Price and Subtype for Order Item |
Online and In-store for Type Shipped and Returned for Subtype Total amount for Extended Price |
Sum of Total amount values with Shipped and Returned attribute values |
| Targeted customers | Event | Medium Target Type |
Email for Medium and Target Sent for Type |
Sum of Sent values |
| Web visitors | Event | Cookie | Values that are not Null | Sum of not Null values |
Top Products widget
The Top products widget widget displays information on the top-performing products based on the metric and time period selected.
For the Revenue metric, two data objects are needed: Organization and Order Item. The Order Item data object has a relationship with the Organization data object and they are both linked through the SourceOrganizationID attribute. Make sure that the SourceOrganization attribute in the Order Item data object has up-to-date records so that the data between the two data objects is properly linked.
| Metric | Data objects | Attributes | Attribute values | Calculation |
|---|---|---|---|---|
| Average order value | Order Item |
Subtype Extended price |
Shipped for Subtype Total amount for Extended price |
Sum of Total amount/Sum of Shipped values |
| Orders | Organization Order Item |
Type for Organization Subtype for Order Item |
Online and In-store for Type Shipped or Demand for Subtype |
Sum of Shipped or Demand values |
| Revenue | Organization Order Item |
Type for Organization Extended Price and Subtype for Order Item |
Online and In-store for Type Shipped and Returned for Subtype Total amount for Extended Price |
Sum of Total amount values with Shipped and Returned attribute values |
| Returns | Order Item | Subtype | Returned | Derived from total_return_count from MasterCustomer total_return_count is derived as a metric from total_return_count from CA_OrderItem cube total_return_count metric in the cube is calculated as COUNT_DISTINCT(return_orderid) return_orderid is OrderItem.orderid when OrderItem.SubType = Returned |