Unica Campaign: Let A Macro Decide Who to Contact and Where

Does your data mart include contacts with multiple valid address records whose current flag varies based on date? Was it ever necessary to vary the messaging, look and feel of a direct marketing campaign based on a contact’s location? Have you ever qualified campaign contacts based on their address?

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

You can run, but you can't hide Rogue DHCP Server

This one is for my network administrators...

Have you ever been at work, doing your regular maintenance activities. Watching system logs scroll by and all of a sudden on your DHCP server logs, you see a DHCPNAK request for an IP address that is not on your network? Shortly afterwards, you start receiving phone calls from your users complaining that they cannot get to the Internet and none of their network applications are working.

Now, before you panic, you have learned two key pieces of information to solve this predicament.

1. There is a host on your network that is attempting to renew an IP address through DHCP that is not valid on your network.

2. The same computer currently is not able to access any of the network resources.

So you probably are thinking, yeah, I know these two things, but these do not tell me how to fix the problem. Well, I'm glad you asked because below are the steps you can take to figure out the issue.

The first thing to do is to get access to the computer of a user who is exhibiting the problem. First, do an "ipconfig /all" from command line to identify the "DHCP Server"

Ethernet adapter Wireless Network Connection:

Connection-specific DNS Suffix . : whereimat.com

Description . . . . . . . . . . . : Atheros AR5B95 Wireless Network Adapter

Physical Address. . . . . . . . . : XX-XX-XX-XX-XX-XX

Dhcp Enabled. . . . . . . . . . . : Yes

Autoconfiguration Enabled . . . . : Yes

IP Address. . . . . . . . . . . . : 192.168.1.101

Subnet Mask . . . . . . . . . . . : 255.255.255.0

Default Gateway . . . . . . . . . : 192.168.1.1

DHCP Server . . . . . . . . . . . : 192.168.1.1

DNS Servers . . . . . . . . . . . : 0.0.0.0

0.0.0.0

Lease Obtained. . . . . . . . . . : Thursday, June 02, 2011 7:25:32 PM

Lease Expires . . . . . . . . . . : Friday, June 03, 2011 7:25:32 PM

Once you know what your DHCP server is, attempt to ping it to make sure your arp table is up to date with the latest MAC address from that machine. Once you complete your ping, check your arp table which should look something like:

Interface: 192.168.1.101 --- 0x3

Internet Address Physical Address Type

192.168.1.1 XX-XX-XX-XX-XX-XX dynamic

Now, you REALLY have all the information you need. The hard part is over, just go over to your managed switch (Cisco, HP or whatever flavor you use), look to see what port the MAC address for the DHCP server (192.168.1.1) exists and then disconnect the port or talk to the user who is connected to the port and have them to cease and desist!

For those who care for some details, common causes of this problem are VMWare server computers that are configured for bridging the bridge network with a DHCP interface. Also, the common cable/dsl routers with the wrong port (LAN instead of WAN) connected to the switch can cause this problem as well. In either case, now that you have the tools to find this problem and resolve it, you can meet these problems head on with the knowledge that it will get resolved.

You got more questions? I have more answers! Leave a comment below or contact me at questions(at)myitguy4u.com.

-MyITGuy

EDIT: Thanks Jason G for the spelling correction! FIXED!

Unica Campaign: Target Customer Based On Proximity to a Location

Whether you are preparing for a major store grand opening or a special program, at some point every database marketer has had to pull a list of households or individuals based on proximity to a known location.

As a former marketer at Macy's Central, it was very typical of our department to pull list, sometimes adhoc, for local promotions. In all cases we leveraged state, zip and county data in our system to select contacts based on their proximity to the event. When Macy's was once divided into several districts it was easier (although not by much) for each corporate district to insert neighboring counties (in proximity to event location) in a Unica campaign and pull their own list. But if you are structured like most companies, there is typically a single national office pulling campaign lists. With that said, its difficult to pull list based on contact's locality without having detailed information or intimate knowledge of the surrounding areas (neighboring counties, zips, states-when event is on the border).


Well, have you ever tried leveraging longitudinal and latitudinal data to select contacts within a given radius of your event's location?

As a marketer you can do this in Campaign, independently of your hosting company or internal IT department and you can earn some serious brownie points with your manager!

It's pretty simple. Let's say you are a preparing an email or direct mail campaign for a local promotion. The promotion is only good at a given location (maybe at a store grand opening). The location is Charlotte, North Carolina (which is not too far from the border of South Carolina). Well, you wouldnt want to pull contacts based on state because you'd miss out on contacts near the border of South and North Carolina while picking up someone in Raleigh, 2hrs away from the location! It also wouldnt be feasible to look up neighboring counties and zip codes because everytime you pull a similar campaign you'd have to do the same leg work.

Instead, start out by selecting contacts based on state (yes, i know, i already told you not to do that but bare with me). Once you have a smaller subset of your database, you can refine this group based on distance from event location by applying 'Zip 50' logic to this group. In a select box, using raw sql (dont get scared, I'll provide the sql below) apply a trigonometric calculation to pick up only those records within a certain distance of the event. The guessing game is eradicated. You can finally choose contacts based on their exact proximity to an event and not based on some estimate. It is also a highly transferable solution. You can take the same logic and just pop it into another campaign. O Joy!

Below is the basic trigonometric calculation. Feel free to comment or contact me at mass.it.consulting@gmail.com if you have any issues implementing this solution.

select distinct t.master_individual_id (audience level)
from  <temptable> t
inner join individual_table base with (nolock)
on t.individual_id = base.individual_id
inner join address_table s with (nolock)
on base.primary_address_id = s.address_id
where coalesce(s.longitude,'x') <> 'Unknown' and coalesce(s.latitude,'x') <> 'Unknown'
and
case when s.zip_code = (event location zip code) then 0.0
when s.longitude = (event location longitude) and s.latitude = (event location latitude) then 0.0 else
acos(cos(radians(s.latitude))*cos(radians(s.longitude))*cos(radians(event location latitude))*cos(radians(event location latitude)) + cos(radians(s.latitude))*sin(radians(s.longitude))*cos(radians(b.event location latitude))*sin(radians(event location longitude)) + sin(radians(s.latitude))*sin(radians(event location latitude))) * 3963.1 end
>= 50 (mean within 50 miles, you can change it)
order by 1

Too little bandwidth and too little time to figure out who is using it all

The Problem

Have you ever been using the Internet in your office or place of residence and experience a problem where your connection becomes SUPER slow. It isn't just affecting your computer though, everyone around you is experiencing the same issue. I am talking about where you start to get messages on Gmail that say "taking a long time to load...still trying". Or maybe the online radio you are listening to sounds like an scratched up CD where every other word is skipped.

This is a problem that I was tasked with identifying and solving. The basic business class Internet service provided by Time Warner is 7 Mbps download and 1 Mbps upload. While this is ample in most cases when services like basic HTTP(S), email and instant messaging are being used, it can easily get out of hand when you introduce specialized applications in the environment that use high amounts of bandwidth for extended periods of time.

For example, let's say you are using some type of synchronization solution (such as a backup utility) to sync your work computer files with a home (or other off site server). By default, these programs are set to utilize the full amount of bandwidth available to perform the synchronization as soon as possible. It is easy to identify the source of the problem when you are the one that is causing the issue because you experience the network degradation of turning on the synchronization solution. However, how do you figure it out when you are just another computer on the network and the problem seemingly comes out of nowhere.

The Solution

I always use Google as a baseline for my connectivity tests. PING tests showed that there was high latency when connecting to Google. While during normal network operations, ping times are < 10 milliseconds (ms), I noticed that at the time that the problem was occurring, PING times were upwards to 500+ ms. In addition to that, network performance is down the drain with the Google website taking on average 5 - 10 literal seconds to load.

The Tools

Spare server

Managed Switch

Enter "nTop" stage right

Luckily, I had access to a spare server and a managed switch that allowed me to setup a SPAN (tap) port. Combine these two things with the free open-sourced program "nTop" and you have all of the tools to identify users who use the offending applications on your network with ease. Once everything is setup correctly, you can access the nTop webpage and see on a per-ip basis how much bandwidth is being used, the servers being connected to, the amount of traffic that has been transferred as well as other useful statistics.

Now, whenever the network gets slow, I pop into nTop, identify the user that is using all of the available bandwidth and notify them. Once they quit the offending application, all goes back to normal and all is happy again.

All Computer Support is not Good Computer Support

Recently, there has been some buzz about a scam that is going around. Someone posing as a Microsoft Support specialist has been calling unsuspecting users on the phone and convincing people to download malware onto their computers. But it does not stop right there, they have even taken a step further by coercing users into downloading software that allows them to control their screen, while downloading malware as the user watches.

They have redirected the users to a specific website where they can pay for this "service" since their support contract has expired and once the customer has paid the funds, they complete the download of the malware and then terminate/disconnect the phone call. All in all, these types of "social engineering" attacks can be avoided by following a few easy rules.

1. Trust but verify

When someone calls you, it is okay to make them verify who they say they are. When you contact a bank or a credit card company, they always ask for different pieces of information for you to verify your identity. If a company states that they are Microsoft and have your computer under a contract, they should be able to identify your license key or product serial (without you providing that information first).

2. Treat your computer like your home

Would you let a random contractor come into your house from off the street? Would you allow him to tell you that you have a problem with your home without looking at it? Would you immediately let him do work without getting a second opinion? Well, if you said yes to any of those questions, then I have some ocean front property in North Dakota I want to sale you. All jokes aside, never let anyone use scare tactics to make you accept a service without getting a trusted source to verify/corroborate the initial assessment.

In short, if something does not seem right, it probably is not. Always follow your gut feeling when using your computer. If you did not purposely download a program, then do not install it. Remember, although a computer can be a great tool, it could be a dangerous one as well in the wrong hands.

Reference Article:

http://isc.sans.org/diary/Microsoft+Support+Scam+again+/10912

Auto Populate Fields in Unica Plan

    A few months ago I embarked on an awesome change order request for a client that housed an executive approval type document outside of Unica Plan. Their goal was to find a way to incorporate the form, which included marketing details such as strategy, competitor information, and campaign details, seamlessly into Plan. Outside the system, the form was completed by marketers and submitted to their respective director or campaign executive for approval. 


The problem:

1. The form was managed outside the system (Unica Plan) requiring marketers to manage their time and effort between planning processes within the tool and outside the tool, some of which included redundant tasks.

2. There existed common fields on the form and current project templates (redundancy)

3. Executives that are less IT savvy would need to access the tool once the form was incorporated into the system.  

The solution (in a nutshell):

1. Introduce a new form to an existing project template. This brings marketing planning processes within a single system making it easier to manage and more importantly, easily tracked!

2. Remove redundancies between existing fields in Plan templates and fields on the latest form by creating auto populating functionality. The new form created should auto populate data into fields on shared project forms through a database linkage on object id. Now, Unica's Admin Guide and Forms Editor doesn't make this capability easy to implement. It requires a bit of creativity. Once the skeletal of the form is created through Forms Editor you will need to make manual edits within the xml file. I literally had to teach myself XML code for this solution, you can learn anything on Google!

3. Add an approval process on the workflow tab. Any approval created sends an email to an approver, the approver is then sent to the summary tab of the intended project. Therefore the newly introduced form should be added to the summary tab of a project template. This sends the less savvy approver directly to the content that is relevant to him or her. Yes, it does require additional licenses if these approvers don't already hold a license BUT only the most basic license is needed.


    While this was a painful solution to create, it was implemented successfully and 50+ projects and counting are leveraging it. I experienced many firsts during this change order: designing AND implementing a Plan solution, writing XML code, creating a form and updating existing forms to auto populated data inputted from the newly introduced form, and creating a new template and transferring functionality over from existing templates.

All and all it was a major success!

For any questions on any part of this solution or any Unica Campaign, Plan or Emessage related question, feel free to leave a comment below.