Customization of existing Unica Insights reports for Campaign

The following section includes details on generating customized Unica Campaign Unica Insights reports based on Unica Campaign custom audiences and custom attributes. See the Unica Campaign Administrator Guide for more details on custom audience and attributes.

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.

To support such business requirements, the Unica Campaign administrator must create new audience levels in the system along with an Audience ID in the system. This can be created inside the Campaign settings>Manage audience level. For this, the “Customer” audience’s CH/RH tables must be new created inside the Campaign DB and this must be replica of the following existing customer audience CH/RH tables.
  • ua_contacthistory
  • ua_dtlcontacthist
  • ua_responsehistory

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
Category SQL Configuration
Campaign Views Campaign View
Offer View
Cell View
Campaign to Offer View
Calendar View
Time View
Campaign Custom Attributes Campaign Custom Attribute View
Offer Custom Attribute View
Cell Custom Attribute View
Campaign Offer Response Breakout Campaign Response Breakout
Campaign Offer Response Breakout
Campaign Offer Contact Status Breakout Campaign Contact Status Contact History
Campaign Offer Contact Status Contact History
Campaign Performance Campaign Contact History
Campaign Cell Contact History
Campaign Offer Contact History
Campaign Offer Cell Contact History
Campaign Cell Offer Contact History
Campaign Response History
Campaign Offer Response History
Campaign Cell Response History
Campaign Offer Cell Response History
Campaign Cell Offer Response History
Campaign Contact History Summary
Campaign Cell Contact History Summary
Campaign Offer Contact History Summary
Campaign Offer Cell Contact History Summary
Campaign Cell Offer Contact History Summary
Campaign Response History Summary
Campaign Offer Response History Summary
Campaign Cell Response History Summary
Campaign Offer Cell Response History Summary
Campaign Cell Offer Response History Summary
Offer Performance Offer Contact History
Offer Response History
Offer Campaign Contact History
Offer Campaign Response History
Offer Campaign Cell Contact History
Offer Campaign Cell Response History
Offer Contact History Summary
Offer Response History Summary
Offer Campaign Contact History Summary
Offer Campaign Response History Summary
Offer Campaign Cell Contact History Summary
Offer Campaign Cell Response History Summary
Offer Performance Metrics Summary

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

To merge Customer and ACCOUNT audiences views, complete the following steps.
  1. Perform the “union all" action on each view of both audiences, example of UARC_COCH_MO and UARC_CORH_MO are attached. Unica Campaign has 37 audience dependent views; administrators must follow this procedure for all.
    CREATE OR REPLACE VIEW UARC_COCH_MO AS
    (
    (( SELECT DISTINCT
     UA_Treatment.CampaignID AS CAMPAIGNID,
     UA_Treatment.OfferID AS OFFERID,
     UA_Calendar.Month AS MONTH, UA_Calendar.Year AS YEAR,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS_CG,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 0 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 1 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS_CG,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 0 and UA_ContactStatus.CountsAsContact=1 THEN UA_ContactHistory_ACNO.ACNO END)) as UNIQUE_RECIPIENTS,
     count(distinct (CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ContactHistory_ACNO.ACNO END)) as UNIQUE_RECIP_CG FROM
     UA_ContactStatus,
     UA_Calendar,
     UA_Treatment
     LEFT OUTER JOIN
     UA_ContactHistory_ACNO
     ON
     UA_Treatment.PackageID = UA_ContactHistory_ACNO.PackageID
     WHERE
     UA_ContactHistory_ACNO.CellID = UA_Treatment.CellID
     AND
     UA_ContactHistory_ACNO.ContactStatusID = UA_ContactStatus.ContactStatusID
     AND
     UA_ContactHistory_ACNO.DateID = UA_Calendar.DateID
     AND
     UA_Treatment.HasDetailHistory = 0 GROUP BY
     UA_Treatment.CampaignID,
     UA_Treatment.OfferID, UA_Calendar.Month, UA_Calendar.Year ) UNION ALL ( SELECT DISTINCT
     UA_Treatment.CampaignID AS CAMPAIGNID,
     UA_Treatment.OfferID AS OFFERID,
     UA_Calendar.Month AS MONTH, UA_Calendar.Year AS YEAR,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS_CG,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 0 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 1 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS_CG,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 0 and UA_ContactStatus.CountsAsContact=1 THEN UA_DtlContactHist_ACNO.ACNO END)) as UNIQUE_RECIPIENTS,
     count(distinct (CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_DtlContactHist_ACNO.ACNO END)) as UNIQUE_RECIP_CG FROM
     UA_ContactStatus,
     UA_Calendar,
     UA_Treatment
     LEFT OUTER JOIN
     UA_DtlContactHist_ACNO
     ON
     UA_Treatment.TreatmentInstID = UA_DtlContactHist_ACNO.TreatmentInstID
     WHERE
     UA_DtlContactHist_ACNO.ContactStatusID = UA_ContactStatus.ContactStatusID
     AND
     UA_DtlContactHist_ACNO.DateID = UA_Calendar.DateID
     AND
     UA_Treatment.HasDetailHistory = 1 GROUP BY
     UA_Treatment.CampaignID,
     UA_Treatment.OfferID, UA_Calendar.Month, UA_Calendar.Year ))
    UNION ALL
    (( SELECT DISTINCT
     UA_Treatment.CampaignID AS CAMPAIGNID,
     UA_Treatment.OfferID AS OFFERID,
     UA_Calendar.Month AS MONTH, UA_Calendar.Year AS YEAR,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS_CG,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 0 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 1 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS_CG,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 0 and UA_ContactStatus.CountsAsContact=1 THEN UA_ContactHistory.CustomerID END)) as UNIQUE_RECIPIENTS,
     count(distinct (CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ContactHistory.CustomerID END)) as UNIQUE_RECIP_CG FROM
     UA_ContactStatus,
     UA_Calendar,
     UA_Treatment
     LEFT OUTER JOIN
     UA_ContactHistory
     ON
     UA_Treatment.PackageID = UA_ContactHistory.PackageID
     WHERE
     UA_ContactHistory.CellID = UA_Treatment.CellID
     AND
     UA_ContactHistory.ContactStatusID = UA_ContactStatus.ContactStatusID
     AND
     UA_ContactHistory.DateID = UA_Calendar.DateID
     AND
     UA_Treatment.HasDetailHistory = 0 GROUP BY
     UA_Treatment.CampaignID,
     UA_Treatment.OfferID, UA_Calendar.Month, UA_Calendar.Year ) UNION ALL ( SELECT DISTINCT
     UA_Treatment.CampaignID AS CAMPAIGNID,
     UA_Treatment.OfferID AS OFFERID,
     UA_Calendar.Month AS MONTH, UA_Calendar.Year AS YEAR,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_Treatment.TreatmentSize END) as NUM_OF_OFFERS_CG,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 0 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS,
     count (distinct (case UA_Treatment.CntrlTreatmtFlag when 1 then UA_Treatment.OfferHistoryID END)) as NUM_OFF_VERS_CG,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 0 and UA_ContactStatus.CountsAsContact=1 THEN UA_DtlContactHist.CustomerID END)) as UNIQUE_RECIPIENTS,
     count(distinct (CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_DtlContactHist.CustomerID END)) as UNIQUE_RECIP_CG FROM
     UA_ContactStatus,
     UA_Calendar,
     UA_Treatment
     LEFT OUTER JOIN
     UA_DtlContactHist
     ON
     UA_Treatment.TreatmentInstID = UA_DtlContactHist.TreatmentInstID
     WHERE
     UA_DtlContactHist.ContactStatusID = UA_ContactStatus.ContactStatusID
     AND
     UA_DtlContactHist.DateID = UA_Calendar.DateID
     AND
     UA_Treatment.HasDetailHistory = 1 GROUP BY
     UA_Treatment.CampaignID,
     UA_Treatment.OfferID, UA_Calendar.Month, UA_Calendar.Year ))
    );
    
    
    CREATE OR REPLACE VIEW UARC_CORH_MO AS
    (
    (SELECT DISTINCT
     UA_Treatment.CampaignID AS CAMPAIGNID,
     UA_Treatment.OfferID AS OFFERID,
     UA_Calendar.Month AS MONTH, UA_Calendar.Year AS YEAR,
     count (CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_ResponseHistory_ACNO.BestAttrib END) as RESP_TRANS,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ResponseHistory_ACNO.BestAttrib END) as RESP_TRANS_CG,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 0  THEN UA_ResponseHistory_ACNO.ACNO END)) as UNIQUE_RESPONDERS,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 1  THEN UA_ResponseHistory_ACNO.ACNO END)) as UNIQUE_RESP_CG,
     count(distinct (CASE WHEN UA_ResponseHistory_ACNO.OrigContactedFlg = 0 AND UA_Treatment.CntrlTreatmtFlag= 0 THEN UA_ResponseHistory_ACNO.ACNO END)) as NOT_CONT_RESP,
     count (CASE WHEN UA_ResponseHistory_ACNO.WithinDateRangeFlg=0 AND UA_Treatment.CntrlTreatmtFlag=0 THEN UA_ResponseHistory_ACNO.BestAttrib END) as RESP_AFTER_EXP,
     count (CASE WHEN UA_ResponseHistory_ACNO.WithinDateRangeFlg=0 AND UA_Treatment.CntrlTreatmtFlag=1 THEN UA_ResponseHistory_ACNO.BestAttrib END) as RESP_AFTER_EXP_CG, AVG(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_ResponseHistory_ACNO.ResponseRevenue END) AS REVENUE_PER_RESP, SUM(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_ResponseHistory_ACNO.ResponseRevenue END) AS GROSS_REVENUE, AVG(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ResponseHistory_ACNO.ResponseRevenue END) AS REV_PER_RESP_CG, SUM(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ResponseHistory_ACNO.ResponseRevenue END) AS GROSS_REVENUE_CG FROM
     UA_UsrResponseType,
     UA_Calendar,
     UA_Treatment
     LEFT OUTER JOIN
     UA_ResponseHistory_ACNO
     ON
     UA_Treatment.TreatmentInstID = UA_ResponseHistory_ACNO.TreatmentInstID
     WHERE
     UA_ResponseHistory_ACNO.ResponseTypeID = UA_UsrResponseType.ResponseTypeID
     AND
     UA_UsrResponseType.CountsAsResponse = 1
     AND
     UA_ResponseHistory_ACNO.BestAttrib = 1
     AND
     UA_ResponseHistory_ACNO.DateID = UA_Calendar.DateID GROUP BY
     UA_Treatment.CampaignID,UA_Treatment.OfferID, UA_Calendar.Month, UA_Calendar.Year)
    Union All
    (SELECT DISTINCT
     UA_Treatment.CampaignID AS CAMPAIGNID,
     UA_Treatment.OfferID AS OFFERID,
     UA_Calendar.Month AS MONTH, UA_Calendar.Year AS YEAR,
     count (CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_ResponseHistory.BestAttrib END) as RESP_TRANS,
     count(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ResponseHistory.BestAttrib END) as RESP_TRANS_CG,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 0  THEN UA_ResponseHistory.CustomerID END)) as UNIQUE_RESPONDERS,
     count(distinct (CASE WHEN UA_Treatment.CntrlTreatmtFlag = 1  THEN UA_ResponseHistory.CustomerID END)) as UNIQUE_RESP_CG,
     count(distinct (CASE WHEN UA_ResponseHistory.OrigContactedFlg = 0 AND UA_Treatment.CntrlTreatmtFlag= 0 THEN UA_ResponseHistory.CustomerID END)) as NOT_CONT_RESP,
     count (CASE WHEN UA_ResponseHistory.WithinDateRangeFlg=0 AND UA_Treatment.CntrlTreatmtFlag=0 THEN UA_ResponseHistory.BestAttrib END) as RESP_AFTER_EXP,
     count (CASE WHEN UA_ResponseHistory.WithinDateRangeFlg=0 AND UA_Treatment.CntrlTreatmtFlag=1 THEN UA_ResponseHistory.BestAttrib END) as RESP_AFTER_EXP_CG, AVG(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_ResponseHistory.ResponseRevenue END) AS REVENUE_PER_RESP, AVG(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ResponseHistory.ResponseRevenue END) AS REV_PER_RESP_CG, SUM(CASE UA_Treatment.CntrlTreatmtFlag WHEN 0 THEN UA_ResponseHistory.ResponseRevenue END) AS GROSS_REVENUE, SUM(CASE UA_Treatment.CntrlTreatmtFlag WHEN 1 THEN UA_ResponseHistory.ResponseRevenue END) AS GROSS_REVENUE_CG FROM
     UA_UsrResponseType,
     UA_Calendar,
     UA_Treatment
     LEFT OUTER JOIN
     UA_ResponseHistory
     ON
     UA_Treatment.TreatmentInstID = UA_ResponseHistory.TreatmentInstID
     WHERE
     UA_ResponseHistory.ResponseTypeID = UA_UsrResponseType.ResponseTypeID
     AND
     UA_UsrResponseType.CountsAsResponse = 1
     AND
     UA_ResponseHistory.BestAttrib = 1
     AND
     UA_ResponseHistory.DateID = UA_Calendar.DateID GROUP BY
     UA_Treatment.CampaignID,UA_Treatment.OfferID, UA_Calendar.Month, UA_Calendar.Year)
    );
    
Note:
  • Keep the views names same as defined by the system for Customer audience.
  • The above procedure enables the summary views to have the audience data and the marketers can view the out-of-the-box reports using same reports and model.

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

To include text custom attributes in schema and views, complete the following steps:
Note: It is assumed that text attribute belongs to the Campaign 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 template (Campaign custom column) and provide information for the following entries.
    • New category name
    • Column Name as the offer custom attribute name.
    • Attribute ID from the abomined query.
    • Value type as NumberValue.
  4. Click Save Changes.

Numeric custom attributes

To include numeric custom attributes in schemas and views, complete the following steps.
Note: For this, it is assumed that the numeric attribute belongs to Offer 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.