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.