Special techniques
Overview
The projects in the repository folder Samples\Basic\Special techniques demonstrate techniques for:
Finding all the records in one table that match records in another table
Assigning values from records in one table to matching records in another table
Assigning values from records in one table to matching records in another table
The sample project cross assign attempts to append information from one table onto another using an exact key match. In this example, we start with a vendor-purchase table containing a purchase amount, date, and vendor ID. We want to append the vendor name from a table containing the vendor ID and name. But there's a problem: there isn't a guaranteed match on vendor ID, because not all vendors with purchase records are in the vendor table. For purchase records with no matching vendor, we will append a blank vendor name. Since Data Management's Join tool provides both inner and outer join results, we can simultaneously work with the matched and unmatched purchase records to produce a consolidated result.
This is very similar to a join with a query, except that you must use both the inner and outer parts of the join. The inner part of the join are the records that successfully matched, so you have a vendor name. The outer part are those records that didn't match, so you want to fill in a blank vendor name.
Performing RFM customer analysis
The sample project customer rfm performs RFM quintile analysis on a customer/purchase database. RFM (Recency, Frequency, Monetary) analysis is a technique used to place each customer in a 5x5x5 "cube," where each element of the cube represents a ranking in each of the Recency, Frequency, and Monetary dimensions. Once you assign customers a quintile rank for each of the RFM measurements, you can identify your best and worst customers and devise marketing strategies appropriate to each group.
This project assigns each customer a rank from 1 (worst) to 5 (best) for each of the three measurements:
Recency: How many days since the last purchase?
Frequency: How many purchases were made this year?
Monetary: How much money has this customer spent altogether?
An excellent source for further reading on this subject is "The Complete Database Marketer," by Arthur M Hughes.
Finding all the records in one table that match records in another table
Suppose you have two tables with a common key, but not all of the records in both tables link on that key. This may be the case because you've received this data from third parties and it's not as clean as it should be. Or perhaps the "key" is something like phone number, so you don't expect every record in two separate tables to match.
The sample project inter table match finds all the records in a table of purchase records that match a table of vendor records on a Tax ID join key. It answers the question "which of the purchase records have matching vendor records?" The matching records are separated from the non-matching ones. We use the Join tool to perform the matching, since the Tax ID should be either an exact match or no match. We could also have used the Table Lookup tool, since the vendor table is small. Note the use of the Right Unique join type, which prevents replication of the purchase records in the event that there are duplicate vendor records.
Finding the geographical overlap of two lists
The sample project unique3 finds three things:
The common set of ZIP Codes between two mailing lists (that is, the overlapping geographical area).
The customers in the second list that fall inside of the common set of ZIP Codes.
The customers in the second list that fall outside of the common set of ZIP Codes.
The general strategy is: