Image Source: IBM
What is IBM Campaign?
IBM Campaign (formerly Unica Campaign – a.k.a “Unica”) is software designed for the purpose of campaign management. Unica provides a graphical user interface to design, execute, and measure customer-driven communication across all marketing channels. The software also stores contact and response history which enable the user to know which customers have responded to which offers over time. The combination of the 2 history tables allows a business to understand the relative success of marketing campaigns. Particularly, when used in combination with the Campaign Analytics module which provides simple pre-defined reports for all key metrics.
Summary of Key Points:
- Simple user interface (although SQL knowledge is helpful)
- Keeps history of all marketing campaigns
- Allows responders to be tracked for every campaign
- Provides simplified reporting of campaign outcomes
Relational Database Design and SQL
IBM Campaign is a Graphical User Interface (GUI) which allows drag-and-drop functionality in order to query information in a set of database tables. The things that the user does in Campaign are translated into Structured Query Language (SQL) behind the scenes. There are different flavours of SQL for different Relational Database Management Systems (RDBMS). Some RDBMS include Oracle, DB2, Teradata and Microsoft SQL Server. It is very useful to understand the bare minimum when it comes to both:
- Relational Databases: “A relational database is a type of table design that promotes data integrity. A relational database is created using tables with primary and foreign keys. These keys link tables so that all information is consistent across the entire database”. (source: http://www.ehow.com).
Image Source: www-03.ibm.com
SQL Server syntax:
As mentioned above there are different “flavours” of SQL. By this I mean that there are different types of syntax for different Relational Database Management Systems (RDBMS). Find out which version of SQL your organisation uses. Become familiar with what is available and how it might impact your experience with IBM Campaign.
How does Unica Work?
When working in IBM Campaign (and all Campaign Management Software) it pays to understand what the software is doing behind the scenes. Nearly every time you add a box in a Unica flow chart it is creating a temporary database table with just a list of ids. A simple select statement will run and save a table like the one shown in the image below. When you then link that select box to another one it uses the first temporary table as its starting point. This makes the queries more efficient by starting with a smaller base at each new level.
Joining a temporary Table
If we use any of the Unica process boxes (see next image) and connect the source input to a preceding Unica process box, providing both are at the same audience level – it will allow the query to be a subset of the first temporary table.
Figure Above: Unica Process Box Options
In the figure below is a section from a Unica flow chart containing 2 select boxes. The first box has 6 members, the second box starts with those 6 members when applying the next level of criteria. you will see that the preceding process box limits the volume of the subsequent process box.
What this means in terms of behind the scenes process is shown in the figure below:
The figure above shows that we have joined a temporary table of 6 customer id’s to another schema table and then applied some additional criteria (in this case age <=72). Unica will treat this as a 2 table join. The first table only has 6 customer ids in it; the second is a full table of all customers (circa 6 million). The query will be very quick because it uses the indexes in the 2nd table and effectively only has to query against 6 records. The result of this will be a new Unica temporary table of only 3 records. Nearly every time you run a select query it will only store the results of that query as a list of ids (id’s are based on the audience level that you are working at – typically customer id or account id). This is demonstrated in the flow chart image below… each step in the flow chart will be referencing or creating a new temporary table.
Instances where Unica will store more fields in the temporary table are:
- An extract box, which keeps all fields from the extract TAB.
- A persistent Derived field will be stored as long as the audience level doesn’t change.
An Audience level switch will stop either of the above conditions from keeping their values further down the flow chart.
Setting up a campaign
Campaign Naming Conventions
It is recommended that the following naming convention be applied across the board.
Campaign: <Auto Generated Unica Campaign Code> – <Campaign Name>
e.g. C000000042 – Contributions
Flow Chart: <Number>_<Process Type – Extract/Response Report/Process Steps>_MMMYYYY
Before setting up a campaign in the Campaigns module offer information needs to be set-up first (See image below). Read through the targeting brief thoroughly and determine if any offers are required and input their relevant details in the Offers TAB. It is important to include as many financials as you can so that they are available for reporting at a later date. Some key values to consider include variable cost per offer, percentage discounts, product details etc. Whatever you are sure about in relation to each offer you need to develop in advance.
Campaign Structure and Build
A campaign will typically have the following structure:
- Inclusion Group – Who you want to start with (e.g. Active/Deemed Accumulation Members)
- What exclusions you need to apply (e.g. Deceased, Opt-outs, invalid addresses, etc.)
- What are the specific campaign criteria? (Who to target? E.g. Age, contributions, etc.)
- What segmentation is required (cell matrix, types of offers, different groups for different treatments or testing different marketing/letter copy)
- Do you need a control group, and if so what is its size?
- File Output:
- What type of file format is required?
- Which fields are needed?
- Which channels are we using (Telephony, DM, Web, etc.)
- Should it be logged to History?
- What constitutes a responder? => Build the response capture as a separate flow chart within the same campaign.
Unica Select Process Box
This is one of the most used Unica options. A select box allows the user to reference a strategic segment or to query against any of the tables/files that are mapped into Unica using Table Mapping (Admin Menu | Tables). Within a select box you can either choose to use click and drop functionality (Point & Click) whereby you will be given guidance on the correct syntax or you can choose to do free-form selections (Text Builder). Use Point & Click if you aren’t very good at SQL and need help building a query, otherwise Text Builder should suffice. The query builder section is effectively the Where clause of an SQL query, remembering that Unica will always just store the audience level key by default.
Unica Merge Process Box
A merge box allows you to join separate Unica flow chart objects together. Merge/Purge is the same thing as an “Or” statement in SQL. Match is the same thing as an “And” statement in SQL. If you want to keep one selection and remove another you use the “Records to Include” to identify those customers that you want to keep, and the “Records to Exclude” to identify those customers that you want to remove.
Be very careful with Merge boxes because they are the easiest way to completely reverse the quality of a selection – i.e. accidentally using “Match” with an exclusion will only keep people that you definitely don’t want… for this reason it is always a good idea to see what the volume is after a merge box has run and compare it against expectations.
These are predefined queries that create a table of results for use in other parts of Unica Campaign. The advantage of setting up a strategic segment is that if it is updated it will automatically flow through to all objects and campaigns that use it. Using strategic segments is very efficient, as referencing a segment means that you don’t need to rerun a query against the source table, you only have to interrogate the much smaller table of results. It also removes the need for analysts to remember how to code everything. As a minimum I recommend 2 categories – Exclusions and Inclusions. Exclusion segments are groups of customers that will be removed from selections (Opt-outs, deceased, etc.). Inclusion segments are groups of customers who have been selected for positive rather than negative reasons (Active, Good Credit Rating, etc.).
When you want to reference a strategic segment you would look at the “Input” section of a Unica Process Box (normally a Select Box). By selecting the segment you are making Unica look at the temporary table that it created when the segment was last refreshed. For this reason it is very important to stay on top of the scheduling and refreshing of segments.
Refreshing Strategic Segments
Strategic Segments can be refreshed using the Campaign | Sessions Menu
Another piece of Unica functionality that can make good use of Unica Strategic Segments is the Template functionality. Templates are predefined Unica objects saved by a user. When you want to use a template you right click in the blank space of a flow chart (in edit mode) and select “Paste from Template Library”. Alternatively you can go to the Options menu and select “Stored Templates”. Templates are brilliant because they also keep the table mapping from when they were originally created, meaning that tables shouldn’t have to be remapped. The only issue that you may have is if the table mapping already contains the same table with more dimension tables mapped to it. The template will override what is already there. A template can be used to predefine up to 80% of the requirements of a generic campaign. Then it is just a matter of tailoring the remaining elements – campaign code, selection criteria, cell breakdown, control group, filename etc.
A Unica Segment box is used to break down a group of customers into specific segments (also known as a cells or target groups). As an example in the pension industry a Segment box might be used to differentiate between customers with different age bands and total contributions amounts. You can choose to segment by a table/field, or by individual queries (more likely). Ticking the “Mutually Exclusive Segments” box means that a customer can only fall into the first group where they satisfy the criteria. This is despite the fact that they might satisfy the requirements for more than one group. If you choose to segment by a query, double-clicking each segment line will launch an “edit segment” box which will provide similar functionality as a Unica select box.
Control groups are normally taken using a Unica Sample box (see figure below). Select from the input drop-downs the inputs that you want as your starting point. If a campaign has a cell matrix defined in the Segment box above then you will need to select multiple cells and pull through all of those cells required. The “…” button will allow you to do this using a check box against available options. Refer to the previous section on Reporting to understand the size of the control group that you should be taking. In the example below, for every input cell the sample process box will split it into 2 separate outputs – a control group of 20% and a Mail group of 80% of the inputs volume.
Custom macros can be created under the Options | Custom Macros tab. The following example of a macro shows a raw SQL query that allows phone number fields to be transformed for use or output. This particular macro is used to clean Fixed Line Phone numbers. In order to use this macro you would create a derived field (see syntax in section below).
The derived field example below creates a cleaned home phone number by calling the custom macro ‘CleanFixedPhone’ (shown above). This macro cleans and formats the fixed_line_phone field; it removes all spaces, removes country codes, removes text and ensures 10 characters starting 0. This derived field will include mobiles if they are saved in the field.
Derived fields are a way to create new fields that can subsequently be referenced within Unica for querying or extract. If you create a derived field and you want the value of that field to be available outside of the Select box that you created it in, you will need to click the “Make Persistent” check box. This will store the results of the Derived Field for every subsequent Unica Option box (unless there is an audience switch in which case it will be dropped). Below is how you would create a derived field based on the custom macro above.
CleanAusMobile(Durable_Mbr_Acc_id, dds.mbr_acc_ss_d, mobile_phone)
In order for Unica to work it needs to know how the relational database is set-up. This is where table mappings are used. The table mappings section allows Unica to understand which tables are related and how to join the information between them. You should have a default table mapping catalogue for your organisation (predefined table mappings for the majority of your database). However, there will always be a need to map in new tables. The best advice for adding a table mapping is to work out exactly what you want to do on paper before you try to replicate it in table mappings. If you have a clear picture it is a lot easier to translate the logic within the rigid framework of table mappings. If you consider the questions below it will be easier to see newly mapped tables and know how they are joined and the results you are likely to get if you use them.
- What is the naming convention used for tables that are already in the Table Mapping.
- In light of the above how will I name this table?
- What do I want to use this table for?
- Does it need to join to an existing base table? In which case you would map it in as a dimension table.
- Will I need to set-up the table with an inner (records must be in both tables) or outer join (records only need to be in the base table).
Within a campaign, select the “admin menu” and then “Tables”, this will load the table mappings box and allow new tables and files to be added. In most organisations this is an administrator function, so seek advice if you are doing it for the first time. If you select a table and click the “Details” button it will show you how the selected table has been mapped, what it joins to, and the fields and specifications within each table.
Mail List Configuration
When configuring the Mail List Process box you effectively work from the left TAB (FulFilment – output format) through to the right TAB (General). Populate everything that seems relevant. The treatment TAB is used for assigning offers to your cells. If you are planning to have control groups then you need to have set this up using a Sample process box just above the Mail List Process Box. If you have created control groups in a Sample Box be sure to select them as well in your Input box “Multiple Cells” under the fulfilment TAB. Click the “Use Holdout Control Groups” and then go through each line to identify which were the controls. Assign an offer to all non-control cells (see image below for example).
Every campaign should keep a history of contact for a customer – whether it is a marketing, service/informational or legal message. Logging a customer contact is very simple, within the MailList Process Configuration box, click on the Log TAB and then tick the “Log to Contact History Tables” check box (see image below). This will store a record for every customer that you have selected, including those in the control groups (if applicable)
The response history table allows Unica to track all responders to the records that it has stored in the Campaign History table. If a given marketing campaign includes an offer or expected outcome from contacting the customer then this is where you set-up a process to capture responders and log them to Response History. You should create a generic template to make this process a lot easier for every campaign.
Response Reporting – Campaign Analytics Report
The campaign Analytics module provides an in-built means to report on campaign performance. There are a number of simple pre-built reports. Provided the campaign has been built with all relevant inputs (particularly financials) then the reports should run without problems. You simply choose the required report in the drop-down list. Selecting the campaign and then un-hiding the control detail. These reports can be generated and output as PDF format and distributed to key stakeholders by email. The report represents the data (including uplift above control) that was written to the campaign history and response tables.
Staff Training Sessions in IBM (Unica) Campaign
If you have any questions about IBM Campaign, or you would like to get me to train your staff in the use of this software then please contact me.