If you answered yes to any of these questions, whether you are aware of it or not, it is likely that you have contacted individuals or households based on an expired or no longer valid address. This issue is not to be confused with bad data. However, it is an issue that requires a campaigner to devise a way to anticipate a contact’s location based on the drop date of a given campaign or the campaign promotion date and location. For instance, let’s say that you are a marketer promoting a seminar in Florida during two set weeks in July. Your data mart includes contact’s like myself with a primary address outside of the state of Florida BUT a re-occurring vacation address in Florida during the summer months. Based on unilateral list pull processes, you would probably miss out on the opportunity to invite me to the seminar while inviting others, whom may be away from their primary Florida address during the campaign promotion time frame. This issue is also true for any direct mail campaign drop date. It is important to mail based on the contact’s location when the campaign is mailed.
This issue requires a way for the campaigner to dynamically choose contacts based on the promotion’s location and date or choose where to mail contacts based on the campaign’s drop date. So why not allow a macro to decide?
Macro’s are one of the must underutilized Unica functions that have the ability to solve some of the most complex and unique business problems. Earlier this year I was tasked with solving both of the business problems described above. Individual campaigners needed to select contacts based on address location and date on a campaign by campaign basis and needed to mail these contacts based on where they would be during the campaign drop date. The macros I created affected between 5-10% of the records selected per campaign. Imagine this percentage multiplied by campaign and compounded over years. This is a produces major cost savings by fine tuning targeting. Not only is it ensuring that the best possible address is chosen to send out your direct mail campaign, but you are also more appropriately selecting your contacts based on address location and date eligibility.
For those of you in the dark about the capabilities of a macro here is a brief definition: A macro is raw sql logic that is applied to campaign records within a given Select or Extract process box. It includes 1 or more variables that are inputted by the campaigner. In the location and date case above, the macro logic would pull back records based on a given date range (the variable).
Below is the general logic for these types of macro:
Business Issue #1- Select contact’s based on address location and date
1. Pull in ALL contact’s with an address within a given location (location of promotion) into a Select process box. You can select these contacts based on city, state, or zip 50 logic. Notice, date doesn’t matter just yet.
2. Attach an Extract process box. Input the same logic in Step 1 and extract address ID.
3. Attach an additional Extract process box to the Select process box (Step 1) and create a derived field which will house the macro. Only pull in those records from Step 1 with an address date populated (date when contact will be at a given address).
4. Create your macro. (The macro logic will vary depending upon your data mart which will need to be assessed by an IT Marketing Solutions Provider like Quaero).
5. The macro will have two variables for the campaigner to input, the begin and end date of the promotion. It will choose those records where the address is during the entire promotion date range while extracting address id.
6. Add a Merge process box that will remove any records with an address id from Step 5 during the entire promotion date range that is not the address id of Step 2. These are the records that had an address within the promotion location BUT will NOT be there during the promotion time frame. Remove these records. All other records can stay, including those without a date populated.
Business Issue #2-Select contacts’ mailing address based on date
1. Pull in ALL contact’s with an address within a given location (location of promotion) into a Select process box. You can select these contacts based on city, state, or zip 50 logic.
2. Attach an Extract process box to the Select process box (Step 1) and create a derived field which will house the macro.
3. Create your macro. (The macro logic will vary depending upon your data mart which will need to be assessed by an IT Marketing Solutions Provider like Quaero).
4. The macro will have one variable for the campaigner to input, the campaign drop date. It will choose the mailing address that has a valid active date range which encompasses the campaign drop date. Be sure to extract address id in this Extract box.
- Candice M. Narvaez
