Custom Macros for Unica Campaign

Custom Macros for Unica Campaign

Written by James Aziz

Table of Contents

What are Custom Macros?

Custom macros are user-defined queries that integrate into the flowchart workflow, allowing the implementation of more complex logic than natively afforded by IBM Campaign’s point-and-click interface and scripting language.

Why are Custom Macros Important for IBM Campaign?

Leveraging custom macros provides an abundance of benefits. Once defined, custom macros can be reused throughout a flowchart to improve modularity and consistency. They can be integrated as part of the template library and are quickly consumed by users who can’t develop custom macros, but know how to use them. These custom macros centralize flowchart logic and processes, improving flexibility and reducing the risk of user error. Allowing such complex transformations and logic to be contained entirely within Campaign is another benefit of custom macros. Alternatively, users can resort to a myriad of tools (for example, Base SAS or a scripting language such as Python) to process data, and the accompanying mess of integrations to facilitate data transfer out and then back into Campaign. However, these methods introduce a lot of complexity and make campaign implementation brittle and easily subject to breakage, which presents risk and may result in quality issues. Lastly, the user guide also outlines this benefit of using raw SQL custom macros:

 

Support for raw SQL improves performance, allowing complex transactions to be carried out in the database rather than having the raw data filtered and manipulated in the application server.

Overview of Custom Macros

IBM Campaign supports three principle types of custom macros:

  • IBM Macro Expression – the default language for building queries and derived fields in Campaign
  • Raw SQL selecting ID list – used to return a list of IDs according to a specified SELECT clause
  • Raw SQL selecting ID + value – returns a list of IDs and associated values, as specified in the attribute list of the SELECT clause

Moreover, custom macros offer the use of an unlimited number of variables, which are supplied to the expression as parameters passed in a macro invocation.

Now that the benefits of custom macros have been outlined, the rest of the guide delves into the specifics of their use and back-end implementation. Note that the following examples all use a sample dataset, running on a v10.5 DB2 database.

1.0 Custom Macros Interface

While in editing mode within a flowchart, the custom macros dialog can be found by navigating to Options → Custom Macros. This interface presents all the defined custom macros and their properties and allows the creation of folders for organizing macros.

1.1 Folder creation

To create a folder:

  1. Click New folder…
  2. Specify a name for the folder.
  3. Provide a description of the contents or intended contents of the folder.
  4. Specify under which existing folder to create this new folder. Choosing None will leave it in the “root” directory.
  5. Select the applicable security policy.
1.2 Macro creation

To create a custom macro:

  1. Click New item…
  2. Specify a name for the macro.
  3. Select a security policy, if applicable.
  4. Provide a note describing the functionality of the macro – this is critical, as it is presented to end users in the Formula helper.
  5. Select one of the three expression types.
    • For either SQL option, additionally, choose a database.
    • For the Raw SQL selecting ID + value option, select a value type.
      • If text, supply the width of the string in bytes (max 256).
      • If numeric, note that the number of decimal places is specified using the DefaultScale parameter found at Settings → Configuration → Campaign → partitions → partition[n] → dataSources → dataSourcename
  6. Enter the desired expression.
1.3 Move/edit macros

To move or edit a custom macro:

  1. Click Edit/Move…
  2. Change the Save under location to move the macro to another directory.
  3. Make any additional changes as desired, using the same process as macro creation. Note that the security policy cannot be changed at this point.
1.4 Remove macros/folders

To remove a custom macro:

  1. Select the folder/macro in the Items list. Folders can be expanded to see the macros within.
  2. Click Remove. Note that removing a folder eliminates the folder and anything inside.
2.0 IBM Macro Expression

The same IBM expression language found throughout Campaign can be used to define custom logic in a custom macro. This helps code consistency, reusability and modularity, making complex logic simpler to use in the flowchart workflow.

2.1 Demonstration A: Selection
Data

This example uses:

  • B_ACCT_INDIV, mapped in at the Individual ID audience level.
  • This mapped table is backed by database table DBO.ACCT, which contains a collection of account IDs, their associated individual IDs, and additional information associated with each account.
Description

Create a custom macro for selecting individuals that have an account of type ‘C’ or account status of type ’1’.

Implementation

The macro definition (name) is acct_c_or_op_amt(OPERATOR, AMOUNT). The complete configuration is:

Note that this custom macro references the mapped table B_ACCT_INDIV (backed by database table DBO.ACCT). Additionally, the expression uses the built-in OR macro, which is accessible from the Formula helper in the expression definition window (this pops up after clicking on the Expression entry box).

Utilization

An IBM Expression custom macro can be used wherever the IBM Expression language can be used in the tool, such as in Select expressions, Segment queries, and derived fields. The macro can be found under Custom macros in the Formula helper (if Formula helper is grayed out, click Text Builder):

In this example, a Select process box is configured to execute the macro as it’s expression, and the parameters OPERATOR and AMOUNT are passed in at macro invocation:

Note that the macro is subject to the same restrictions as any IBM language expression – any table referenced within the macro must be mapped in and available in the context where it is used. Given custom macros can be used across flowcharts, mistakenly using one in the incorrect context will result in an error. As such, it is critical to organize custom macros in folders, and supply notes describing their operation.

The above configuration produces the following query, run on the back-end and visible in the log:

  1. SELECT DISTINCT INDIV_ID FROM DBO.ACCT WHERE ((DBO.ACCT.ACCT_TYPE_CODE = ‘C’) OR (DBO.ACCT.ACCT_BALANCE > 5000)) ORDER BY INDIV_ID
2.2 Demonstration B: Derived Fields
Data

This example uses:

  • B_ACCT_INDIV, mapped in at the Individual ID audience level.
  • This mapped table is backed by database table DBO.ACCT, which contains a collection of account IDs, their associated individual IDs, and additional information associated with each account.
Description

Create a custom macro that takes two numbers as parameters and produces their arithmetic mean.

Implementation

The macro definition is average(NUM1, NUM2). NUM1 and NUM2 are now parameters that can be used in the expression by enclosing them in a set of <> brackets, e.g., . The expression is therefore ( + ) / 2. The complete configuration is:

Utilization

This custom macro can be used to create a derived field that averages two other table fields:

2.3 Tips
  • Built-in macros can be used when creating IBM Expression custom macros.
  • Just as with any IBM Expression language query, mapped tables can be referenced and used in IBM Expression custom macros.
3.0 Raw SQL Selecting ID List

This type of custom macro can be used to leverage the extensive capabilities of raw SQL selection and package that logic in a way that is readily accessible to a non-technical user.

3.1 Demonstration A - Selection
Data

This example uses:

  • DBO.ACCT, which contains a collection of account IDs, their associated individual IDs, and additional information associated with each account.
Description

Create a custom macro to select all individuals from a table having average account balances greater than some amount for each account type, where the table and amount are supplied as parameters.

Implementation

The macro definition is sel_acct_gt_amt(TABLE, AMOUNT). Parameter names are enclosed within <> when used in the SQL query:

  1. select indiv_id
  2. from <TABLE>
  3. group by indiv_id, acct_type_code
  4. having avg(acct_balance) > <AMOUNT>;
The complete macro configuration:
Utilization

As with all custom macros, this type of macro can be found in the Formula helper wherever IBM Expressions are permitted. However, given this custom macro returns only a lit of IDs, it cannot be used in a derived field. In this example, a Select process box is configured to Select Individual IDs with, with the expression sel_acct_gt_amt(DBO.ACCT, 5000), where DBO.ACCT is the database table to be queried, and 5000 is the amount. Campaign translates this call into the following query, visible in the log:

  1. select INDIV_ID from DBO.ACCT group by INDIV_ID, ACCT_TYPE_CODE having avg(ACCT_BALANCE) > 5000;

The two variables and have been replaced in the query by the parameters passed in a macro invocation. Also note that the selection criteria will be applied to the input – specifically, only the subset of records in both the process box input and raw query result will be in the output cell. In other words, the result of this Select process box will be the intersection of the input set and the set returned by the macro query.

3.2 Demonstration B - TempTable
Data

This example uses:

  • DBO.ACCT, which contains a collection of account IDs, their associated individual IDs, and additional information associated with each account.
  • INDIV, which contains a collection of individual IDs and their associated details.
Description

Create a custom macro to select individuals that are in the Eastern time zone and include only those who have been passed in from a previous process box, if one exists.

Implementation

The macro name is sel_est_incl_prev, and the SQL expression is:

  1. select dbo.indiv.indiv_id 
  2. from dbo.indiv 
  3. {inner join <TEMPTABLE> on dbo.indiv.indiv_id = <TEMPTABLE>.indiv_id} 
  4. where dbo.indiv.time_zone = ‘EST’;

Note that line 3 will be executed only if an upstream &ltTEMPTABLE> exists because it is wrapped in braces.

Utilization

If this custom macro is invoked in the expression of a Select process box that is accepting an input cell from a preceding Select, the executed SQL appears in the log as:

  1. select DBO.INDIV.INDIV_ID from dbo.indiv inner join UAC_14270_l on DBO.INDIV.INDIV_ID = UAC_14270_l.indiv_id where DBO.INDIV.TIME_ZONE = ‘EST’;

…where UAC_14270_l is the TempTable, the cell passed in from the preceding Select. Alternately, if this custom macro is called from a Select configured with a mapped table as input, the log shows:

  1. select DBO.INDIV.INDIV_ID from dbo.indiv  where DBO.INDIV.TIME_ZONE = ‘EST’;
3.3 Tips
  • The records passed out of the Select are deduplicated, even if the raw SQL custom macro selects duplicate individuals.
  • This type of custom macro cannot be used in a derived field.
  • The IDs selected by the raw SQL custom macro must be of the same type (numeric/text) as the audience in the context where the macro is invoked:
    • For example, if a Select is configured to select individual IDs (numeric), the custom macro it calls in its expression must also return IDs of type numeric, e.g. it cannot return time zones;
    • Again, a set intersection is performed here – both sets must contain elements of the same type;
    • As long as the ID types match, Campaign will perform the intersection, regardless of whether it makes logical sense – this may lead to incorrect results.