Customization of existing Unica Insights reports for Campaign

Unica Campaign Custom Audiences

Unica Campaign is delivered with a single audience level called Customer. You can define any additional audience levels that you require. Audience levels allow the flowchart designers target specific groups, such as households, in marketing campaigns.

Customer’ (number) is the default audience, which is available in the system to run marketing campaigns. In cases where business may require to run campaigns on other audience types, for example, a financial organization wants to contact its customers by using its customers’ “AccountNO” instead of “Customerid”, they must use new audience as ‘Account’ (text) to run campaigns. In order to show ‘Account’ audience data in reports, administrator must create the underlying tables or views so that reports can show relevant KPIs correctly.

Tables

These are the references how the tables are modified or replicated. Once this is done, users must modify the existing Unica Insights report design template in order to view the report in Unica See Generate views for ACCOUNT audience after this section.

• These tables are replica of ‘Customer’ audience tables and are created by replacing ‘CUSTOMERID’ column with the new audience field, example, "ACCOUNTNO". Here is a sample script.

create table UA_DTLCONTACTHIST_ACNO
(
  ACCOUNTNO        VARCHAR2(20) not null,
  TREATMENTINSTID   NUMBER(19) not null,
  CONTACTSTATUSID   NUMBER(19),
  CONTACTDATETIME   TIMESTAMP(6),
  UPDATEDATETIME    TIMESTAMP(6),
  USERDEFINEDFIELDS CHAR(18),
  DATEID            NUMBER(19) not null,
  TIMEID            NUMBER(19) not null,
  VALUEBEFORE       NUMBER(19,2),
  USAGEBEFORE       NUMBER(19,2)
);

create table UA_CONTACTHISTORY_ACNO
(
  ACCOUNTNO        VARCHAR2(20) not null,
  CELLID            NUMBER(19) not null,
  PACKAGEID         NUMBER(19) not null,
  CONTACTDATETIME   TIMESTAMP(6),
  UPDATEDATETIME    TIMESTAMP(6),
  CONTACTSTATUSID   NUMBER(19),
  DATEID            NUMBER(19),
  TIMEID            NUMBER(19),
  USERDEFINEDFIELDS CHAR(18),
  VALUEBEFORE       NUMBER(19,2),
  USAGEBEFORE       NUMBER(19,2)
);

create table UA_RESPONSEHISTORY_ACNO
(
  ACCOUNTNO         VARCHAR2(20) not null,
  TREATMENTINSTID    NUMBER(19) not null,
  RESPONSEPACKID     NUMBER(19) not null,
  RESPONSEDATETIME   TIMESTAMP(6) not null,
  WITHINDATERANGEFLG NUMBER(10),
  ORIGCONTACTEDFLG   NUMBER(10),
  BESTATTRIB         NUMBER(10),
  FRACTIONALATTRIB   FLOAT,
  DIRECTRESPONSE     NUMBER(10),
  CUSTOMATTRIB       FLOAT,
  RESPONSETYPEID     NUMBER(19),
  DATEID             NUMBER(19),
  TIMEID             NUMBER(19),
  USERDEFINEDFIELDS  CHAR(18),
  VALUEAFTER        NUMBER(19,2),
  USAGEAFTER        NUMBER(19,2),
  RESPONSEREVENUE   NUMBER(19,2),
  SALESCOST         NUMBER(19,2),
  RESPONSECHANNEL   VARCHAR2(16)
);

Understanding of Reporting Schema

Unica Campaign reports works on pre-aggregated views. These views are created by using Report SQL Generator’ functionality and can be found under Platform settings. This feature has reporting schemas and each reporting schema is associated with multiple pre-aggregated views.

The following is the list of all reporting schemas with associated views summary. The template names available in Platform configuration are also provided for each schema.

• Campaign Views – Summary views based on Campaign, Offer, Cell, and Time
  • Campaign custom attributes
  • Campaign performance star schema
  • Offer performance star schema
  • Campaign offer response breakout star schema
  • Campaign offer contact status breakout
• Campaign Custom Attributes – Summary views on Custom attributes, Campaign/Offer/Cell
  • Campaign custom attributes
  • Campaign performance star schema
  • Offer performance star schema
  • Campaign offer response breakout star schema
  • Campaign offer contact status breakout
• Campaign Offer Response Breakout – Summary views on Campaign, offer Response
  • Campaign custom attributes
  • Campaign performance star schema
  • Offer performance star schema
  • Campaign offer response breakout star schema
  • Campaign offer contact status breakout
• Campaign Offer Contact Status Breakout – Summary views on Campaign, offer Contacts
  • Campaign custom attributes
  • Campaign performance star schema
  • Offer performance star schema
  • Campaign offer response breakout star schema
  • Campaign offer contact status breakout
• Campaign Performance – Summary views on campaign performance various analysis
  • Campaign custom attributes
  • Campaign performance star schema
  • Offer performance star schema
  • Campaign offer response breakout star schema
  • Campaign offer contact status breakout
• Offer Performance – Summary views on offer performance various analysis
  • Campaign custom attributes
  • Campaign performance star schema
  • Offer performance star schema
  • Campaign offer response breakout star schema
  • Campaign offer contact status breakout

Campaign installer registers ‘Customer’ audience’s report views. It also registers report views’ templates, which are used to generate report views for new audiences.

Create reporting schema for custom audience

To create reporting schemas for ACCOUNT audience, complete the following steps.

1. Select a tempalate out of the five campaign reporting schema templates, which use CH/RH tables for their SQL definition.
2. Provide New Category Name and respective contact and response history tables and its Audience key (column name) for all relevant categories. Administrators may also add additional time level grouping if required, new views definitions are added for each time level grouping.
   1. Campaign Views and Campaign Custom Attributes category are audience independent so they will be same for any custom audience.
   2. Campaign Offer Response Breakout, Campaign Offer Contact Status Breakout, Campaign Performance and Offer Performance categories have columns. For all custom audience category you need to create exact same columns which are available for the default Customer audience.
3. Repeat the above step for all templates. All new categories will be listed under campaign.
4. Select each ACNO category and configure them for their view names under ‘Sql configuration’, these view names must be unique to ‘Customer’ audience’s view names. There are two types of view names one that ends with underscore (UARC_OCH_) and without underscore (UARC_CRBO_ACNO). The first one is used to create various time level views like UARC_OCH_ACNO_DY, UARC_OCH_MO, etc.

Select each ACNO category and configure them for their Key point indicator (KPI) using column template under Columns’. Administrator must ensure that all KPIs are created by ‘Customer’ audience.

There are two types of KPI metric templates, Contact and Response. Contact metric is defined from contact history table column, whereas Response metric is defined from response history table column.

Generate views for ACCOUNT audience

To generate views for ACCOUNT audience, complete the following steps.

1. Navigate to Settings > Reports SQL Generator option. All schemas are listed under product ‘Campaign’.
2. Select all ACNO categories and generate views.
3. Save the scripts.

   Merge Customer and ACCOUNT audiences views

Custom Attributes

You can customize campaigns by adding custom campaign attributes to store metadata about each campaign.

Before you begin:

Create the custom attribute. See the Campaign Administration Guide for more details.

To configure Unica Campaign reports for custom attributes, complete the following steps.

Text custom attributes

1. Get AttributeID campaign system database using below query:

   select AttributeID,Name,DisplayName from UA_AttributeDef where DisplayName = <>

2. Navigate to Settings > Configuration > Report > schemas > Campaign > Campaign custom attributes.
3. Click the Offer custom column template and provide the following information.
   • New category name.
   • Column Name as the offer custom attribute name.
   • Attribute ID from the abomined query.
   • Value type as NumberValue.
4. To include Number attribute as a KPI in report views, navigate to the schema. For example:

   Settings > Configuration > Report > schemas > Campaign Performance.

5. Select the column template Contact metric if the Number attribute is related to Contact or (Response metric) if the Number attribute is belonged to response. Fill in the following information
   • New category name.
   • Column Name as the offer custom attribute name.
   • Function from the Count/Count Distinct/Min/Max/Avg list.
   • Column name
   • Under Control treatment flag, the default value is 0 and if the KPI is applicable for control cell the value is 1.
6. Click Save Changes.

If a custom attribute is relevant to more reporting schemas for your business reporting requirement, repeat steps 4 and 5 for each schema. It is not required that a KPI must fall under all reporting schemas, to understand more on this, see the Understanding of Reporting Schema section of this document.

Generate Custom Audience and Custom Attribute Views using Run SQL Generator

To generate views using above custom attributes, complete the following steps.

1. Navigate to Settings > Reports SQL Generator > Update or Create new schema names from the Schema list.
2. Select DB type.
3. Download views.

Downloaded SQL script must be executed in the Campaign system database. You may have to drop view(s) and recreate them if they already exist in the system.

Composite audience report views

Report views can also be created for composite audience case, where two fields are combined together for identifying unique target customer. To create report views, one common set of CH/RH tables must be created that have both the audience fields. All columns must be captured in comma separated in Audience Key field of the template configuration.