Custom Macros for Unica Campaign
Custom Macros for Unica Campaign
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?
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:
- Click New folder…
- Specify a name for the folder.
- Provide a description of the contents or intended contents of the folder.
- Specify under which existing folder to create this new folder. Choosing None will leave it in the “root” directory.
- Select the applicable security policy.
1.2 Macro creation
To create a custom macro:
- Click New item…
- Specify a name for the macro.
- Select a security policy, if applicable.
- Provide a note describing the functionality of the macro – this is critical, as it is presented to end users in the Formula helper.
- 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
- Enter the desired expression.
1.3 Move/edit macros
To move or edit a custom macro:
- Click Edit/Move…
- Change the Save under location to move the macro to another directory.
- 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:
- Select the folder/macro in the Items list. Folders can be expanded to see the macros within.
- 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:
- 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:
- select indiv_id
- from <TABLE>
- group by indiv_id, acct_type_code
- 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:
- 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:
- select dbo.indiv.indiv_id
- from dbo.indiv
- {inner join <TEMPTABLE> on dbo.indiv.indiv_id = <TEMPTABLE>.indiv_id}
- where dbo.indiv.time_zone = ‘EST’;
Note that line 3 will be executed only if an upstream <TEMPTABLE> 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:
- 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:
- 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.
Related Blog
4.0 Raw SQL Selecting ID + Value
This type of custom macro extends the capability of the Raw SQL Selecting ID macro to allow the selection of an additional column from the table. Once again, custom macros allow complex logic like this to be encapsulated in a way that is easily accessible to non-technical users.
4.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.
- B_ACCT_INDIV, which is DBO.ACCT, mapped in at the Individual ID audience level.
Description
Create a custom macro that determines the difference between an individual’s total credit limit across all their accounts and the total balance across all their accounts. Select only individuals with a difference greater than 5000.
Implementation
This macro is defined as credit_minus_balance. A note helps explains the functionality of this macro to end users and is visible in the Formula helper. The full query is then:
- select indiv_id, sum(credit_limit) – sum(acct_balance)
- from DBO.ACCT
- group by indiv_id;
Utilization
As with all custom macros, a raw SQL selecting ID + value type macro can be found in the Formula helper. As an example use case, a Select is configured to Select Individual IDs with and the expression defined as credit_minus_balance() > 5000:
The output of this Select will be the intersection of the input set (all individuals in the B_ACCT_INDIV table in this case) and the subset of the macro query result that have a (credit – balance) difference greater than 5000. The selection process is visible in the log, abridged here:
- select INDIV_ID, sum(CREDIT_LIMIT) – sum(ACCT_BALANCE) from DBO.ACCT group by INDIV_ID;
- Query completed; starting data retrieval.
- Data retrieval completed; 60725 records retrieved and returned to caller.
- Data retrieved to IBM Campaign server. Processing query expression.
- Select: N_RECORDS = 56509
Initially, the custom macro SQL query runs and returns 60725 records. Then the query expression in the Select is processed against the input set, and the remaining record count is 56509.
Equivalently, this selection criterion could have been applied using a custom macro returning only a list of IDs, and not values. In that case, the macro query would be:
- select indiv_id
- from DBO.ACCT
- group by indiv_id
- having (sum(credit_limit) – sum(acct_balance)) > <AMOUNT>
The corresponding selection query is credit_minus_balance_gt_amount(5000). These two approaches produce identical results – subsequently, the design pattern of choice is dependent primarily on the use case. For example, if requirements dictate that the selection criteria (e.g., using less than instead of greater than) be readily adjustable, using a custom macro to return values and then applying the criteria in the selection expression is likely the better technique. This is because custom macros should be designed to be easily used by non-technical users, who should not have to modify the macro definition in their workflow.
4.2 Demonstration B - Derived Fields
Data
This example uses:
- DBO.ACCT, which contains a collection of account IDs, their associated individual IDs, and additional information associated with each account
- B_ACCT_INDIV, which is DBO.ACCT, mapped in at the Individual ID audience level.
Description
Create a custom macro that determines the difference between an individual’s total credit limit across all their accounts and the total balance across all their accounts. Create a derived field that reports this difference for everyone.
Implementation
The custom macro in use is the same that was created in Demonstration A, namely credit_minus_balance. An Extract process is configured to select all records from the preceding Select process, which itself selects all individual IDs from the same DBO.ACCT table used by the custom macro:
Then, a derived field is configured to invoke the macro:
Then, a derived field is configured to invoke the macro:
An abridged transcript of the execution log follows. The below execution pattern is specific to this flowchart setup but serves to demonstrate the overall data flow. Create and populate the input table (output of the preceding Select) that goes into the Extract (referred to as table h):
- CREATE TABLE UAC_14270_h (INDIV_ID INTEGER)
- INSERT INTO UAC_14270_h SELECT DISTINCT INDIV_ID FROM DBO.ACCT
Create the extract table (referred to as table i):
- CREATE TABLE UAC_EX_14270_i (INDIV_ID INTEGER, credit_minus_balance DECIMAL(15,5))
Create and populate the table defined by the custom macro (referred to as table j):
- CREATE TABLE UAC_14270_j (INDIV_ID INTEGER, zinternal_value DOUBLE)
- INSERT INTO UAC_14270_j
- select indiv_id, sum(CREDIT_LIMIT) – sum(ACCT_BALANCE)
- from DBO.ACCT
- group by indiv_id;
A new table will be created as above for each custom macro configured in this fashion. The first column is taken to be the list of IDs at the same audience level as the Extract and does not have to be normalized, i.e., the values need not be unique. The second column is the corresponding list of values. Populate the extract table i:
- INSERT INTO UAC_EX_14270_i(INDIV_ID, credit_minus_balance)
- SELECT DBO.ACCT.INDIV_ID, UAC_14270_j.ZINTERNAL_VALUE
- FROM ((UAC_14270_h
- LEFT OUTER JOIN DBO.ACCT ON UAC_14270_h.INDIV_ID = DBO.ACCT.INDIV_ID)
- LEFT OUTER JOIN UAC_14270_j ON UAC_14270_h.INDIV_ID = UAC_14270_j.INDIV_ID)
- WHERE (DBO.ACCT.INDIV_ID IS NOT NULL) OR (UAC_14270_j.INDIV_ID IS NOT NULL)
To build an extract table a series of LEFT OUTER JOINs are performed, one for each attribute to be extracted. The input list of IDs (table h) is on the left, and the attribute table (DBO.ACCT and then table j in this example) on the right. This is particularly noteworthy when the custom macro raw SQL does not SELECT DISTINCT or GROUP BY on the ID column, thereby removing duplicates. Thus, due to theLEFT OUTER JOIN, not using aggregation may introduce undesired duplicates in the extracted table. Note that when the Extract is configured to accept a mapped table directly as input, the log does not explicitly state the LEFT OUTER JOIN operations, but the result produced is consistent with that behavior. This is also why the above example uses a dummy Select preceding the Extract.
4.3 Tips
- For numeric value types, the number of decimal places is specified using the DefaultScale parameter found at Settings → Configuration → Campaign → partitions → partition[n] → dataSources → dataSourcename.
- As with raw SQL selecting ID list macros, the IDs selected by this type of custom macro must be of the same type (numeric/text) as the audience IDs in the context where the macro is invoked
- As long as the ID types match, Campaign will perform the intersection and/or join, regardless of whether it makes logical sense – this may lead to incorrect results
- If the value returned by this macro is not used in comparison (e.g., in a Select/Extract expression), non-zero values are considered TRUE, and zero values or text strings are FALSE
- IDs associated with the TRUE records are selected
Conclusion
Custom macros are a valuable means of bringing complex functionality into IBM Campaign, and improving performance. Their many benefits include improved modularity and standardization, particularly as they can be leveraged not only by users that have the technical capacity to create them but by anyone familiar with IBM Campaign. Custom macros also help contain complex logic within Campaign, avoiding a mess of integrations with external tools. Altogether, this improves reliability, efficiency and the quality of your campaigns, particularly across organizations.
Sales Inquiries + 1 (514) 223 3648
General Inquiries + 1 (514) 392 9822
sales@munvo.com
© 2024 Munvo is a trademark of Munvo Solutions Inc.
TL;DR article summary
Custom macros in IBM Campaign are user-defined queries that allow more complex logic than the standard interface provides. They are crucial for improving modularity, consistency, and reducing user error by centralizing flowchart logic.
Custom macros enhance flexibility, prevent campaign breakage, and support raw SQL for better performance by processing data in the database instead of the application server.
IBM Campaign supports three main types of custom macros: IBM Macro Expression, Raw SQL selecting ID list, and Raw SQL selecting ID + value. These macros can take unlimited variables and significantly streamline campaign workflows.