Using Prospect User-Defined Fields to Make List Creation More Powerful

Many InOrder users are familiar with User-Defined Fields (UDFs) on Prospect records. These can be used to collect demographic information about your potential customers. They can be exposed on your web interface for your visitors to enter information such as age, gender, interests, where they first heard about you, or any other type of question that helps you target specific prospects and provide them with a more personalized experience.

Because these UDFs are all available as Selection Criteria in [List Creation], you can use these fields to pull very specific mailing lists.

But what if you want to make [List Creation] more powerful and include more prospect-based criteria than exists as built-in choices? These Prospect UDFs don’t have to be merely the Q&A fields your prospects answer themselves. You can also have UDFs as “concept” fields; updated regularly using a scheduled “SQL statement” job that categorizes customers based on buying behaviors or patterns that you are looking for.

InOrder client Rod’s Western Palace wanted to do just this. They needed lists of people who purchased any product from a specific department. But they also wanted to be able to pull a list of people who had purchased any product that was NOT in that same department. We worked with them to achieve this by adding two “concept” fields, both simple Yes/No fields that are recomputed by a nightly job that reviews each customer’s order history.

Here is how you can implement this type of Prospect UDF at your company.

First, create one or more Prospect UDFs in the [Client Setup] window. Make sure to set the ‘Data Type’ appropriately (YesNo, Text, Number, etc) for the type of information you will be populating into the field.

Rod’s used two UDFs set to the YesNo data type. The first (UDF Field 1) was called ‘Tack Buyers’ (customers who ordered items from the Tack department) and the other (UDF Field 2) ‘No Tack Buyers’ (customers who placed an order but never ordered any items from the Tack department).

In the screenshot, we show you how to do this using our test database with “Chocolate Buyers” as the example:

Next, determine the criteria to calculate the computed value for this field on each prospect record. If the criteria is based on items ordered, what distinguishes the items? SKU? Inventory Type? Inventory Status? One of the 21 Inventory User-Defined Fields? Do the items need to be part of a posted shipment in order to be used in determining the calculation? This criteria will be used to build your SQL statement.

Next, if you know SQL then you can build the SQL statement, otherwise contact Morse Data Technical Support for assistance. If you build this statement in-house, remember to always have Morse Data Technical Support approve your statement before executing it against your live database, to avoid unexpected results, and to stay within the terms of your software agreement. (We recommend having Morse Data help you the first time, and then consider trying it yourself if you ever need to make adjustments or add new fields.)

Here is an example update statement that uses inventory types, with a description of what each step does.

1) Insert the two Prospect UDFs into prospect records where they currently do not exist, and set the values to ‘N.’:

Insert tblProspectUserField (lab_KEY, lab_FIELD_NUMBER, lab_USER_DEFINED)
Select lab_key,’3′,’N’
from tblProspect pro
where not exists
(Select * from tblProspectUserField prev
where prev.lab_KEY = pro.lab_KEY
and prev.lab_FIELD_NUMBER = ‘3’)–Chocolate Buyer Prospect Field
Go
Insert tblProspectUserField (lab_KEY, lab_FIELD_NUMBER, lab_USER_DEFINED)
Select lab_key,’5′,’N’
from tblProspect pro
where not exists
(Select * from tblProspectUserField prev
where prev.lab_KEY = pro.lab_KEY
and prev.lab_FIELD_NUMBER = ‘5’) –Non-Chocolate Buyer Prospect Field
Go

2) Update the value on the first UDF to ‘Y’ if: there exists a posted order associated with this customer, and that order contains a posted line item of an inventory item belonging to one of three inventory types:

Update puf
Set lab_user_defined = Case
When Exists (Select * from tblPostedOrder por
INNER JOIN tblPostedLine pli on por.por_ORD_KEY = pli.por_ORD_KEY
INNER JOIN tblInventoryPrimary inp on inp.own_OWNER_CODE = pli.own_OWNER_CODE
and inp.inp_SHORT_ITEM_NUMBER = pli.inp_SHORT_ITEM_NUMBER
Where por.cus_key = puf.lab_KEY
and inp.ity_INVENTORY_TYPE_CODE IN (‘DK’, ‘MK’, ‘WT’))
THEN ‘Y’
ELSE ‘N’
END
From tblProspectUserField puf
Where puf.lab_FIELD_NUMBER = ‘3’ –Chocolate Buyer
Go

3) Update the value of the second UDF to ‘Y’ if: there exists a posted order associated with this customer and there exists no posted orders associated with the customer containing a posted line item where the inventory item belongs to one of three inventory types:

Update puf
Set lab_user_defined = Case
When Exists (Select * from tblPostedOrder por
INNER JOIN tblPostedLine pli on por.por_ORD_KEY = pli.por_ORD_KEY
INNER JOIN tblInventoryPrimary inp on inp.own_OWNER_CODE = pli.own_OWNER_CODE
and inp.inp_SHORT_ITEM_NUMBER = pli.inp_SHORT_ITEM_NUMBER
Where por.cus_key = puf.lab_KEY)
AND NOT Exists (Select * from tblPostedOrder por
INNER JOIN tblPostedLine pli on por.por_ORD_KEY = pli.por_ORD_KEY
INNER JOIN tblInventoryPrimary inp on inp.own_OWNER_CODE = pli.own_OWNER_CODE
and inp.inp_SHORT_ITEM_NUMBER = pli.inp_SHORT_ITEM_NUMBER
Where por.cus_key = puf.lab_KEY
and inp.ity_INVENTORY_TYPE_CODE IN (‘DK’, ‘MK’, ‘WT’)
THEN ‘Y’
ELSE ‘N’
END
From tblProspectUserField puf
Where puf.lab_FIELD_NUMBER = ‘5’ –Non-Chocolate Buyer

A regularly-scheduled SQL job similar to this one allows Rod’s Western Palace to perform targeted mailings to their buyers of specific items.  Once you are back in the [List Creation] window, these fields are available for use as Selection Criteria.

The nicest part about these SQL statements is that they can consider more complex logic that you might want to build into a UDF, like only considering sales that occurred in the last 120 days, sales that were not returned, or sales of specific items that were placed where the order was responding to an abandoned cart email campaign.  If you can think of it, there is probably a way to compute it.  And amazingly, these SQL statements can evaluate hundreds of thousands of customers and their respective order history, usually in a matter of minutes each night!

Share on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someone

1 Comment

Leave a response

1 (888) 667-7332

Contact Us

Literature Downloads


Contact

Corporate Headquarters
Morse Data Corporation
16 Pierce Street
Dover, NH 03820
Toll Free: (888) 667-7332
Phone: (603) 742-2500
Fax: (603) 742-8178
Technical Offices
Morse Data Corporation
9661 W. 143rd St. Suite 200
Orland Park, IL 60462
Toll Free: (800) 860-9515
Phone: (708) 873-0010
Fax: (708) 873-9967

About