LATEST BLOG: Identity Resolution: A Comparative Analysis of Leading Customer Data Platforms Read More

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?

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.

Img1
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:

Img2

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):

Img3

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:

Img4

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:

Img5
Utilization

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

Img6
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:
Img7
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.
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:

  1. select indiv_id, sum(credit_limit) – sum(acct_balance) 
  2. from DBO.ACCT 
  3. group by indiv_id;
The value type to be returned must be Numeric here, corresponding to the second attribute (column) in the select clause. The complete macro configuration is:
Img8
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:

Img9

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:

  1. select INDIV_ID, sum(CREDIT_LIMIT) – sum(ACCT_BALANCE) from DBO.ACCT group by INDIV_ID;
  2. Query completed; starting data retrieval.
  3. Data retrieval completed; 60725 records retrieved and returned to caller.
  4. Data retrieved to IBM Campaign server.  Processing query expression.
  5. 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:

  1. select indiv_id 
  2. from DBO.ACCT 
  3. group by indiv_id 
  4. 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:

Img10
Then, a derived field is configured to invoke the macro:
Img11
Then, a derived field is configured to invoke the macro:
Img11 1

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):

  1. CREATE TABLE UAC_14270_h (INDIV_ID INTEGER)
  2. INSERT INTO UAC_14270_h SELECT DISTINCT INDIV_ID FROM DBO.ACCT

Create the extract table (referred to as table i):

  1. 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):

  1. CREATE TABLE UAC_14270_j (INDIV_ID INTEGER, zinternal_value DOUBLE)
  2. INSERT INTO UAC_14270_j 
  3.   select indiv_id, sum(CREDIT_LIMIT) – sum(ACCT_BALANCE) 
  4.   from DBO.ACCT 
  5.   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:

  1. INSERT INTO UAC_EX_14270_i(INDIV_ID, credit_minus_balance)
  2.   SELECT DBO.ACCT.INDIV_ID, UAC_14270_j.ZINTERNAL_VALUE
  3.   FROM ((UAC_14270_h
  4.   LEFT OUTER JOIN DBO.ACCT ON UAC_14270_h.INDIV_ID = DBO.ACCT.INDIV_ID)
  5.   LEFT OUTER JOIN UAC_14270_j ON UAC_14270_h.INDIV_ID = UAC_14270_j.INDIV_ID)
  6.   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.

Talk To Our Experts Today

Sales Inquiries + 1 (514) 223 3648
General Inquiries + 1 (514) 392 9822
sales@munvo.com

© 2024 Munvo is a trademark of Munvo Solutions Inc.


Pin It on Pinterest

Plus Company Privacy Policy

Underline

(Canada) EN

Updated as of September 11th. 2023

“PLUS COMPANY” AND “COMPANY” MEAN PLUS COMPANY CANADA INC. AND ITS AFFILIATES AND BUSINESS UNITS IN CANADA.

Plus Company respects the privacy of its customers.

This Policy concerns you. It describes how we collect, use, disclose and protect your personal information, including when you visit our website or any website we own, operate or control (collectively, the “Site”), when you contact us by phone or email or when you communicate with us via social media.

We may update this Policy (see “Changes to the Policy” below).

You should read this entire Policy before submitting information to us or using our Site. If you submit personal information to us, we assume that you authorize us to use and disclose it as described in this Policy.

What is personal information?

Personal information is information that identifies you directly or indirectly, on its own or with other information, such as your name, contact details or IP address.

We may make full use of all information that is de-identified, aggregated or otherwise not in personally identifiable form.

Information collection and use

We collect personal information …

  • From you
  • Using automated technology when you visit our Site or communicate with us through email
  • When third parties, such as service providers, collect such personal information on our behalf

When do we collect your personal information?

  • When you browse our site
  • When you contact us by phone or email
  • When you communicate with us via social media

What type of personal information do we collect?

  • Usage information – IP address, information on the device, domain name, browser used to visit our site, pages displayed, time spent on a page, link clicks
  • With your consent, we may have access to your geolocation and
    site-navigation patterns.
  • Information you provide to us, such as personally identifiable information or contact information, as well as your comments or questions.

Why do we need it?

  • To better understand how you use our Site.
  • To modify or improve our Site, services and business activities.
  • For marketing, research and legal purposes.
  • To comply with our policies, procedures and legal obligations, including complying with law enforcement or governmental authority requests, investigating fraudulent activity, resolving disputes and enforcing our legal agreements and policies.

With whom do we share this personal information?

As part of our business operations, we may disclose personal information to the following categories of third parties:

  • Consultants, service providers and contractors we use to support our business and operations (e.g., hosting or operating our Site and services, data collection, reporting, Site metrics and analytics, data analysis, fraud detection services) who have agreed to keep the information confidential and use it only to provide the applicable services.
  • Government authorities, agencies, if required to do so by law, regulation or court order, or to respond to governmental and/or law enforcement requests.
  • An acquirer or successor in interest in the event of a reorganization, merger, sale, change of control, consolidation, joint venture, assignment, transfer or other disposition of all or any part of Plus Company or its affiliates, including any negotiation thereof.

Where is your personal information stored?

We currently retain personal information in North America.

We may disclose personal information in locations other than your country, province or state of residence, where privacy laws may differ.

If your personal information is used outside your country, province or state of residence, it is subject to the laws of the place where it is located and may be disclosed to governments, courts, law enforcement agencies or regulatory bodies of that place, or disclosed in accordance with the laws of that place. However, our practices regarding your personal information will remain governed by this Policy and by applicable privacy laws.

Retention of personal information

We will retain your personal information (collected through online and offline methods) for as long as it is necessary for the purposes described in this Policy. We will also retain and use your personal information to the extent necessary to comply with our legal obligations, resolve disputes and enforce our legal agreements and policies.

Intended audience of Site

Our Site, social media accounts and online activities are intended for persons aged 17 and over. Therefore, we do not request personal information from minors aged 16 years or younger, nor do we knowingly collect such information from minors aged 16 or younger. If you are not 17 or older, you should not visit or use our Site.

How do we protect your personal information?

We take reasonable, appropriate steps to protect personal data from loss, misuse and unauthorized access, disclosure, alteration or destruction, whether in transmission or storage. Remember, however, that no security system is infallible and that transmission over the Internet is not perfectly secure or error-free.

We use a secure server. Only authorized persons have the right to access this information, which they are required to keep strictly confidential.

Your rights with regard to your personal information

Right to access and correct

You may request access to and obtain a copy of the personal information we hold about you.

If any personal information about you is inaccurate, incomplete or ambiguous, or if the collection, disclosure or retention of such personal information is not permitted by law, you may require that it be rectified.

You can also ask us for information about the source of your personal information (if it was not obtained from you), as well as the names of persons who have access to your information and details about how long it is kept.

Right to withdraw your consent

You may request to withdraw your consent to our use or disclosure of your personal information.

In most cases, withdrawing your consent means that we will no longer be able to offer you certain services. Otherwise, we will inform you of the consequences of refusal in our request for consent.

To exercise your rights, please send a request in writing, along with proof of identity, to our Privacy Officer at the contact information provided under How to contact us.

Once your request has been received, we will respond in writing within 30 days.

Cookies and other automated technology

A “cookie” is a small text file that is placed onto an Internet user’s web browser or device and is used to remember and/or obtain information about the user. A “web beacon” is a small object or image that is embedded into a web page, application or email and is used to track activity. Web beacons are also sometimes referred to as pixels and tags. We and/or third parties, including our service providers on our behalf, may use cookies, web beacons and other similar technology to collect information for the purposes described in this Policy, including analytics and monitoring performance and improvement of our Site (traffic, errors, page load time, popular pages, etc.). Before using these technologies to geolocate you or evaluate certain characteristics about you, such as your personal preferences, interests or online behaviour, we will ask for your permission by informing you how to enable such features. We use Google Analytics to understand how our Site, services and products perform and how you use them. To learn more about how Google processes your data, visit https://policies.google.com/privacy. To change your settings relating to cookies and other automated technology, visit our Cookie Manager.
For information and questions about the use of your personal information or this Policy, you may contact our Privacy Officer at privacy@pluscompany.com.
This Policy may be revised from time to time for any reason. In such a case, we will notify you of such changes by indicating the date of the last update. Review the Policy before submitting personal information or using our Site, services or social media pages.
Search