Customization of existing Unica Insights reports for Interact

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

Unica Interact Custom Audiences

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

Customer (number) is the default audience, which is available in the system to deliver offer in marketing Interact. In cases, where business require to deliver offers to 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 Interact. In order to show ‘Account’ audience data in reports, administrator must modify the underlying tables or views so that reports can show related KPIs correctly.

Tables 

The following CH/RH tables are supposed to be created under Campaign system database. For more details, see the Unica Campaign Administrator Guide.

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

CREATE TABLE [dbo].[ACCT_UA_DtlContactHist](
[AccountID] [varchar](512) NOT NULL,
[TreatmentInstID] [bigint] NOT NULL,
[ContactStatusID] [bigint] NULL,
[ContactDateTime] [datetime] NULL,
[UpdateDateTime] [datetime] NULL,
[UserDefinedFields] [nchar](18) NULL,
[DateID] [bigint] NOT NULL,
[TimeID] [bigint] NOT NULL,
[RTSelectionMethod] [int] NULL,
[RTLearningMode] [int] NULL,
[RTLearningModelID] [bigint] NULL ) ON [PRIMARY]
CREATE INDEX ACCT_cDtlContHist_IX1 ON ACCT_UA_DtlContactHist
 (AccountID,TreatmentInstID);
ALTER TABLE [dbo].[ACCT_UA_DtlContactHist]  WITH CHECK ADD  CONSTRAINT [ACCT_DCH_FK3] FOREIGN KEY([TimeID]) REFERENCES [dbo].[UA_Time] ([TimeID])
ALTER TABLE [dbo].[ACCT_UA_DtlContactHist]  WITH CHECK ADD  CONSTRAINT [ACCT_DtlCH_FK1] FOREIGN KEY([ContactStatusID]) REFERENCES [dbo].[UA_ContactStatus] ([ContactStatusID])
ALTER TABLE [dbo].[ACCT_UA_DtlContactHist]  WITH CHECK ADD  CONSTRAINT [ACCT_DtlCH_FK2] FOREIGN KEY([DateID]) REFERENCES [dbo].[UA_Calendar] ([DateID])
alter table ACCT_UA_DtlContactHist add RTSelectionMethod int;
  

CREATE TABLE [dbo].[ACCT_UA_ContactHistory](
[AccountID] [varchar](512) NOT NULL,
[CellID] [bigint] NOT NULL,
[PackageID] [bigint] NOT NULL,
[ContactDateTime] [datetime] NULL,
[UpdateDateTime] [datetime] NULL,
[ContactStatusID] [bigint] NULL,
[DateID] [bigint] NULL,
[TimeID] [bigint] NULL,
[UserDefinedFields] [nchar](18) NULL,
 CONSTRAINT [ACCT_CHist_PK] PRIMARY KEY CLUSTERED 
([AccountID] ASC,,[CellID] ASC,[PackageID] ASC)
CREATE INDEX ACCT_cContactHist_IX1 ON ACCT_UA_ContactHistory(CellID);
CREATE INDEX ACCT_cContactHist_IX2 ON ACCT_UA_ContactHistory(PackageID,CellID);
ALTER TABLE [dbo].[ACCT_UA_ContactHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_CHist_FK1] FOREIGN KEY([ContactStatusID])
REFERENCES [dbo].[UA_ContactStatus] ([ContactStatusID])
ALTER TABLE [dbo].[ACCT_UA_ContactHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_CHist_FK2] FOREIGN KEY([DateID])
REFERENCES [dbo].[UA_Calendar] ([DateID])
ALTER TABLE [dbo].[ACCT_UA_ContactHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_CHist_FK3] FOREIGN KEY([TimeID])
REFERENCES [dbo].[UA_Time] ([TimeID])

  
  
CREATE TABLE [dbo].[ACCT_UA_ResponseHistory](
[AccountID] [varchar](512) NOT NULL,
[TreatmentInstID] [bigint] NOT NULL,
[ResponsePackID] [bigint] NOT NULL,
[ResponseDateTime] [datetime] NOT NULL,
[WithinDateRangeFlg] [int] NULL,
[OrigContactedFlg] [int] NULL,
[BestAttrib] [int] NULL,
[FractionalAttrib] [float] NULL,
[DirectResponse] [int] NULL,
[CustomAttrib] [float] NULL,
[ResponseTypeID] [bigint] NULL,
[DateID] [bigint] NULL,
[TimeID] [bigint] NULL,
[UserDefinedFields] [nchar](18) NULL,
[RTSelectionMethod] [int] NULL,
[RTLearningMode] [int] NULL,
[RTLearningModelID] [bigint] NULL,
 CONSTRAINT [ACCT_RHistory_PK] PRIMARY KEY CLUSTERED 
([AccountID] ASC,[TreatmentInstID] ASC,[ResponsePackID] ASC)
ALTER TABLE [dbo].[ACCT_UA_ResponseHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_RHistory_FK1] FOREIGN KEY([TreatmentInstID])
REFERENCES [dbo].[UA_Treatment] ([TreatmentInstID])
ALTER TABLE [dbo].[ACCT_UA_ResponseHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_RHistory_FK2] FOREIGN KEY([TimeID])
REFERENCES [dbo].[UA_Time] ([TimeID])
ALTER TABLE [dbo].[ACCT_UA_ResponseHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_RHistory_FK3] FOREIGN KEY([ResponseTypeID])
REFERENCES [dbo].[UA_UsrResponseType] ([ResponseTypeID])
ALTER TABLE [dbo].[ACCT_UA_ResponseHistory]  WITH CHECK ADD  CONSTRAINT [ACCT_RHistory_FK4] FOREIGN KEY([DateID])
REFERENCES [dbo].[UA_Calendar] ([DateID])
alter table ACCT_UA_ResponseHistory add RTSelectionMethod int;

Understanding of Reporting Schema 

Unica Interact reports works on pre-aggregated views. These views are created by using Report SQL Generator’ functionality. This feature has reporting schemas and each reporting schema is associated with “n” number of pre-aggregated views. 

The details of view are described in the “SQL scripts by data source” section.

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

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

1. Use Interact Performance Star Schema to create Interact Performance Schema. Complete the following substeps to create an Interact Performance schema.
   1. Select Settings > Configuration and expand Reports > Schemas > Interact > Interact Performance Star Schema.
   2. In the New category name field, enter a descriptive name for the reporting schema that indicates the audience level. For example, Interact Performance Household.
   3. In the Input Tables section, identify the tables that support the audience level and the audience key.
   4. In the Schema Settings section, select all the Over Time Variations options that apply and then click Save Changes. A new node appears in the Configuration tree for the schema. You cannot change the name of the node.

The remaining views, i.e. Deployment History, Interact Runtime Views and Interact Learning Views are audience independent so they are same for any custom audience.   

Generate views for ACCOUNT audience 

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

1. Navigate to Settings > Reports SQL Generator . All schemas are listed under product ‘Interact’. 
2. Select all ACNO categories and generate views. 
3. Save the scripts and import in the respective data source.

For composite audience level

Merge Customer and ACCOUNT audiences views 

To merge Customer and ACCOUNT audiences views, complete the following steps. Administrator must merge Customer and Account audiences’ views, as explained above.

1. Perform the “union all" action on each view of Interact Performance for both the audience levels.
2. Keep the views names same as defined by system for Customer audience.

The above procedure enables the summary views to have both the audience data and the marketers can view all out of the box reports using same reports and model.

For example, you want to use Account audience level.

1. Drop the following views from Campaign database:
   • UARI_ZONEPERF_PRES_REJ
   • UARI_ZONEPERF_MASTER
2. Open the file from the Interact installation location <INTERACT_HOME>/reports/ddl/<db type folder>/InteractDT.sql.
3. Change the name of following tables in above-mentioned views to audience specific as per the tables you created above. Save the files and import them in database.
   • UA_ResponseHistory > ACCT_UA_ResponseHistory
   • UA_DtlContactHist > ACCT_UA_DtlContactHist