Fluent Essays

Problem Statement: ATT is a large telecommunications company and they have really good data about phone calls globally. They would like to build new business services around the nice data assets that they have, similar to a data broker. One beautifully rich data set they have is Call Detail Records (CDRs). They would like to build a data broker service around this internal data. (google: call detail records). There are privacy restrictions around data that they can sell, but they can derive new data fields from their rich proprietary data. If averaged at a large enough geographical region (zip9, zip5, zip3?) these derived fields can be sold. Also, they can sell new fields at the customer/address/phone number level that are derived from CDR fields if the new fields are of a different nature than the CDRs. This would allow them to sell a segment label/description. They see a company called Claritas and others that make a lot of money selling highly descriptive consumer segments (google: Claritas PRIZM segments). An ATT executive wants to build such a data broker business, selling such demographic-like segments built using the CDRs. Let’s start with the U.S. only. Design a process to build PRIZM-like segments using CDR records. Requirement: Here are three examples (see attached files) of pretty good submissions for homeworks. It's best if you begin by stating what the business problem is, in your own words, so it's clear to the reader that you understand the problem. Then describe the proposed solution approach. If appropriate, you can also describe any assumptions you made, particularly about data that is needed. You might describe several solution paths depending on what data might be available. Describe the input and out put of the framework you designed. The goal is for the reader to read it, easily understand it, and believe it will work well to solve the problem. It's not enough to have a great idea - you also need to communicate it well. Questions and Answers: From whom does AT&T collect CDRs? From AT&T customers only? In other words, is it the case that whenever an AT&T customer calls someone or receives a call from someone, there will be a CDR collected? If not, how were CDRs collected? They collect the data for the CDRs from their own internal network, and they only see their own customers’ behavior. ATT sees about 40% of all US phone traffic. If the CDRs only include AT&T customers’ calls, what data do we have available on these customers other than the CDRs? (Demographic data?) We know their PII, so we can link them at a data broker. We don’t have our own demographic data about them. Do we match customers in the CDRs to other data through their phone numbers? We can match by phone number to any other data that we have or could get. We also have their names and addresses. Are the characteristics of each segment we have in the end based on the CDR information only (e.g. a segment in which the average call duration is above 30 mins)? Or on any information we can find on a customer? We could use anything we can gather about a customer to help define the segments. Could you please explain “average at a geographical region (zip9, zip5, zip3...)” again with examples? Example: Credit bureau data is very sensitive. Companies can’t buy CB data at the individual person level unless the person gives them permission (BTW - every time you apply for a credit product you give the company permission to get your CB for as long as you have their credit product.) BUT, a company can buy averaged CB data at geographic region levels, with the smallest region being zip9 (9 digit zip code). This is about 4 square city blocks. After we have our segments and say a company gives us a list of people to assign the segments, what information/data will the company provide to us on the list of people? Generally you’d get what one sends to a data broker, enough PII (name, address, phone number…) to link to your database. Where can we access to the slide of class 2 and the notebook you shared with us during class? Should be on Blackboard now. They should appear after the class. Is over. Who will buy our data (demographic-like segments built using the CDRs) and how they will make use of these data? Companies that want to do marketing solicitation, just like we went to a data broker when we wanted to do installment loan solicitations. What type of information in CDRs that we cannot use or assume that we cannot obtain? (For instance, like the destination number, can we use it?) Internally, we can use anything on the CDR for analysis, model building, mapping to other data sets etc. We generally can’t sell the CDC info. Do we have information about content in SMS they receive? And what about browsing history? And geolocation data from cell towers? No, not content, but I bet we can get geolocation/tower identification data. That would be useful. What data does PI include which we have? Age, Gender etc? PII is personal identifying data fields, most commonly name, address, phone number, SSN, date of birth… But all we have are a few fields, like name, address, phone number. Is there a preferred time scope for the data product to be valid for sale? For instance, data in the past one year. I'm flexible, so no preference on time scale. I just want the resulting segments that I can sell to be useful in consumer models so people will buy them. How many call destinations do we have? Do we have the call destination names if they are not owned by a person but a business, or public institute? If the destination number is owned by an individual, do we have their occupation? We know very little about the destination number, unless it’s a phone number in our customer base. We can do elaborate statistical analysis to make inferences on the destination numbers which might provide insight. Do CDRs include locations of the 2 persons having the phone call? Not explicitly, but we can frequently make inferences from the first 3 digits. For landlines, the first 6 digits tell the geography. NARC v #3 Contact us: 312-348-7884 www.infutor.com INFUTOR - Name and Address Resource Consumer NARC v. # 3 File Layout File format: Tab delimited % populated Field Name Length Description CA FL TX #Records 25,033,293 17,280,033 18,108,213 Base Name and Address CA FL TX 1 HHID 20 Internal Use (blank) 0% 0% 0% 2 PID 20 Internal Use (blank) 0% 0% 0% 3 FNAME 15 First name (blank) 0% 0% 0% 4 MNAME 1 Single Alpha Character (blank) 97% 98% 97% 5 LNAME 20 Surname (blank) 0% 0% 0% 6 SUFFIX 10 Jr., Sr., I, II, II, etc. Left justified (blank) 100% 100% 100% 7 Gender 1 M = Male M 45% 45% 45% F = Female F 48% 48% 48% (blank) 8% 7% 7% 8 AGE 2 Adult Estimated Age based on YOB (blank) 37% 31% 37% 9 DOB 6 YYYYMM (blank) 37% 31% 37% 1910 0% 0% 0% 1920 2% 3% 2% 1930 5% 6% 4% 1940 9% 10% 8% 1950 14% 13% 13% 1960 14% 13% 14% 1970 10% 10% 12% 1980 7% 9% 8% 1990 1% 2% 1% 10 HHNBR 1 Internal Use (blank) 0% 0% 0% 11 ADDRID 20 Internal Use (blank) 0% 0% 0% 12 ADDRESS 64 House Number, Directional, Street Name, Apartment Number (blank) 0% 0% 0% 13 HOUSE 10 Physical Street Number (blank) 0% 0% 0% 14 PREDIR 2 Physical Street Pre Direction (blank) 83% 69% 88% 15 STREET 28 Physical Street Name (blank) 0% 0% 0% 16 STRTYPE 4 Physical Street Suffix (blank) 8% 4% 12% 17 POSTDIR 2 Physical Street Post Direction (blank) 100% 92% 99% 18 APTTYPE 15 Unite Designator (blank) 87% 87% 91% 19 APTNBR 15 Apartment # or Suite # (blank) 87% 87% 91% 20 CITY 28 As listed in USPS Publication 26, Directory of Post Offices. Post Office names in excess of 28 positions have been abbreviated by USPS (blank) 0% 0% 0% 21 STATE 2 Two position alpha FIPS State code (blank) 0% 0% 0% 22 ZIP 5 Five-Position numeric as assigned in USPS publication 65, National Zip Code Directory (blank) 0% 0% 0% 23 Z4 4 Four-Position numeric as assigned in USPS Publication 65, National Zip Code Directory (blank) 2% 2% 3% 24 DPC 3 Delivery Point Code with Check Digit (blank) 1% 1% 2% 25 Z4TYPE 1 USPS Zip+4 RecordType (blank) 1% 1% 1% 26 CRTE 4 Carrier Route Code (blank) 1% 0% 1% 27 DPV 4 Delivery Point Validation (blank) 1% 0% 1% 28 VACANT 4 USPS Vacant Flag (Y or N or Blank) (blank) 9% 9% 11% Geographic Delineations CA FL TX 29 MSA 4 MSA (blank) 6% 9% 15% 30 CBSA 5 CBSA (blank) 8% 10% 12% 31 DMA 5 DMA (blank) 0% 1% 1% 32 County Code 3 FIPS county Code used in conjunction with the State Code (blank) 1% 1% 1% 33 Time Zone 1 Hawaii=2, Alaska=3, Pacific=4, Mountain=5, Central=6, Eastern=7, Atlantic=8 (blank) 0% 0% 0% 34 Daylight Savings 1 Spring/Fall Time Change Observed = Y or Blank (blank) 0% 0% 0% 35 Latitude 11 Latitude (blank) 4% 3% 5% 36 Longitude 11 Longitude (blank) 4% 3% 5% Telephone Number Data The Telephone Number select indicates if up to three telephone numbers are available for a given record. All telephone numbers are processed for DNC compliance with flags available, but telemarketers are encouraged to perform additional processing prior to a telemarketing campaign. A SAN number is required to output DNC flagged telephone numbers on an order. CA FL TX 37 Telephone Number - 1 10 Directory Assistance Phone 1 (blank) 84% 80% 77% 38 DMA TPS DNC Flag - 1 1 Telephone Preference Service Do Not Call = N (blank) 90% 87% 87% N 10% 13% 13% 39 Telephone Number - 2 10 Directory Assistance Phone 2 (blank) 100% 100% 100% 40 DMA TPS DNC Flag - 2 1 Telephone Preference Service Do Not Call = N (blank) 100% 100% 100% N 0% 0% 0% 41 Telephone Number - 3 10 Directory Assistance Phone 3 (blank) 100% 100% 100% 42 DMA TPS DNC Flag - 3 1 Telephone Preference Service Do Not Call = N (blank) 100% 100% 100% N 0% 0% 0% Household Demographic Data CA FL TX 43 Length of Residence 3 The number of years located at present address (blank) 2% 2% 2% 0 1% 0% 0% 1 1% 1% 2% 2 2% 2% 3% 3 3% 3% 3% 4 4% 4% 5% 5 5% 5% 7% 6 7% 7% 9% 7 11% 9% 11% 8 13% 11% 11% 9 12% 12% 10% 10 10% 11% 8% 11 7% 8% 6% 12 4% 5% 4% 13 3% 4% 3% 14 2% 3% 2% 15 13% 9% 10% >15 2% 7% 7% 44 Homeowner/Renter 1 O = Homeowner O 60% 62% 64% R = Renter R 8% 8% 5% (blank) 32% 30% 31% 45 Year Built 4 The year home was built according to Real Estate Data, ie, 2013 (blank) 22% 18% 23% <1990 60% 50% 47% 1990 1% 2% 1% 1991 1% 1% 1% 1992 1% 1% 1% 1993 1% 1% 1% 1994 1% 2% 1% 1995 1% 1% 1% 1996 1% 1% 1% 1997 1% 2% 1% 1998 1% 2% 2% 1999 1% 2% 2% 2000 1% 2% 2% 2001 1% 2% 2% 2002 1% 2% 2% 2003 1% 2% 2% 2004 1% 2% 2% 2005 1% 2% 2% 2006 1% 2% 2% 2007 1% 1% 1% 2008-2014 1% 1% 3% 46 Mobile Home Indicator 1 Residence is a mobile home as indicate by Real Estate Data - Mobile home = Y or blank (blank) 99% 95% 96% Y 1% 5% 4% 47 Pool Owner 1 Pool owner as indicated by Real Estate Data, Pool = Y or blank (blank) 82% 83% 93% Y 18% 17% 7% 48 Fireplace in home 1 Home with fireplace as indicated by RE Data. Fireplace = Y or blank (blank) 61% 86% 68% Y 39% 14% 32% 49 Estimated Income 1 A = Less than $20,000 A 11% 15% 15% B = $20,000 - $29,999 B 6% 8% 7% C = $30,000 - $39,999 C 14% 15% 14% D = $40,000 - $49,999 D 12% 13% 14% E = $50,000 - $74,999 E 22% 24% 23% F = $75,000 - $99,999 F 13% 12% 11% G = $100,000 - $124,999 G 11% 7% 8% H = $125,000 or More H 10% 5% 7% (blank) 1% 1% 1% 50 Marital Status 1 M= Married M 35% 34% 37% S= Single S 26% 29% 24% A = Inferred Married A 3% 2% 3% B = Inferred Single B 1% 1% 1% (blank) 35% 34% 35% 51 Single Parent 1 Modeled select based on presence of children and married selection. Single Parent = Y or blank (blank) 97% 97% 97% Y 3% 3% 3% 52 Senior In HH 1 Known resident of HH that is 65 or older, Senior = Y or blank (blank) 75% 72% 77% Y 25% 28% 23% 53 Credit Card User 1 Known use of Credit Card for purchases - Credit card = Y or blank (blank) 68% 70% 68% Y 32% 30% 32% 54 Wealth Score - Estimated Net worth 1 Model based on income, homeownership, other assets owned (blank) 67% 69% 66% A - Estimated Net less than $5000 A 1% 1% 1% B - Estimated Net $5K - $19k B 1% 1% 3% C - Estimated Net $20k-$49k C 2% 4% 5% D - Estimated Net $50k-$80k D 3% 8% 10% E - Estimated Net  $81k-$99k E 0% 0% 0% F - Estimated Net $100k-$249k F 10% 10% 9% G - Estimated Net $250k - $499K G 10% 6% 5% H - Estimated Net -over $500k H 6% 2% 2% 55 Donator to Charity or Causes 1 Donor = Y or Blank (blank) 77% 79% 77% Y 23% 21% 23% 56 Dwelling Type 1 M - MFDU M 16% 16% 11% S - SFDU; S 81% 81% 85% (blank) 3% 3% 4% 57 Home Market Value 1 Estimated market value - model based information (blank) 49% 16% 15% A = $1,000 - $24,999 A 0% 2% 4% B = $25,000 - $49,999 B 1% 6% 8% C = $50,000 - $74,999 C 1% 9% 12% D = $75,000 - $99,999 D 2% 10% 12% E = $100,000 - $124,999 E 2% 9% 11% F = $125,000 - $149,999 F 2% 7% 8% G = $150,000 - $174,999 G 2% 6% 6% H = $175,000 - $199,999 H 2% 5% 4% I = $200,000 - $224,999 I 2% 4% 3% J = $225,000 - $249,999 J 2% 3% 2% K = $250,000 - $274,999 K 2% 2% 2% L = $275,000 - $299,999 L 2% 2% 1% M = $300,000 - $349,999 M 4% 3% 2% N = $350,000 - $399,999 N 3% 2% 1% O = $400,000 - $449,999 O 3% 2% 1% P = $450,000 - $499,999 P 2% 1% 1% Q = $500,000 - $749,999 Q 8% 5% 2% R = $750,000 - $999,999 R 4% 4% 2% S = $1,000,000 Plus S 4% 2% 3% 58 Education 1 A= Completed High School A 12% 12% 13% B= Completed College B 10% 9% 11% C= Completed Graduate School C 4% 4% 4% D= Attended Vocational/Technical D 0% 0% 0% (blank) 74% 75% 72% 59 ETHNICITY 1 Hispanic = Ethnic code Y Y 8% 4% 8% African American = Ethnic code F F 1% 1% 1% Asian = Ethnic code A A 2% 0% 1% (blank) 89% 94% 90% 60 Child 1 Presence of Children = Y or Blank (blank) 64% 68% 63% Y 36% 32% 37% 61 Child Age Ranges 1 A = Presence of Children under 6 A 5% 5% 6% B = Presence of Children aged 6 - 10 B 4% 3% 4% C = presence of Children Age 11 - 15 C 4% 3% 4% D = Presence of Children Age 16-17 D 4% 4% 4% (blank) 83% 85% 81% 62 Number of Children in HH 1 A = no children A 12% 12% 12% B = less than 3 B 16% 14% 17% C = 3 - 5 C 1% 1% 2% (blank) 70% 73% 69% 63 Luxury Vehicle Owner 1 Luxury owner = Y or blank (blank) 98% 95% 96% Y 2% 5% 4% 64 SUV Owner 1 SUV owner = Y or Blank (blank) 93% 79% 76% Y 7% 21% 24% 65 Pickup Truck Owner 1 Pickup = Y or Blank (blank) 96% 85% 73% Y 4% 15% 27% Lifestyle Bundles The following selections identify consumers who have responded positively to two or more related hobby, lifestyle, and/or preference categories. Data is applied at the household level and is sourced from self-reported sources, consumer surveys, product and warranty registrations, and buying activities. CA FL TX 66 Price Club and value Purchasing Indicator 1 Value shopper = Y or NULL (blank) 92% 93% 94% Y 8% 7% 6% 67 Women's Apparel Purchasing Indicator 1 A = Purchased Women's Apparel A 11% 9% 9% B = Purchased Plus size Women's Apparel B 4% 4% 5% (blank) 85% 87% 86% 68 Men's Apparel Purchasing Indcator 1 A = Purchased Men's Apparel A 4% 4% 4% B = Purchased Big and Tall Men's Apparel B 1% 1% 1% (blank) 95% 96% 96% 69 Parenting and Children's interest Bundle 1 Parenting and Children's interest = Y or Blank (blank) 85% 88% 86% Y 15% 12% 14% 70 Pet Lover's or owners 1 A = has Pets A 4% 3% 3% B= Equestrian B 0% 0% 0% C= Cat owner C 1% 2% 1% D= Dog owner D 6% 7% 9% (blank) 88% 88% 87% 71 Book Buyers 1 Buyer = Y or Blank (blank) 96% 94% 94% Y 4% 6% 6% 72 Book Readers 1 Reader = Y or Blank (blank) 73% 75% 73% Y 27% 25% 27% 73 Hi-Tech Enthusiasts 1 Hi Tech Enthusiasts = Y or Blank (blank) 71% 73% 70% Y 29% 27% 30% 74 Arts Bundle 1 A = Interest in Arts A 1% 1% 1% B = Avid Music Listener B 8% 9% 8% C = Interest in Antiques C 4% 3% 3% D = Interest in Performing Arts D 1% 1% 1% (blank) 86% 87% 87% 75 Collectibles Bundle 1 A = General Interest in Collectibles A 0% 0% 0% B = Interest in Antique Collectibles B 16% 15% 15% C = Interest in Sports Collectibles C 1% 2% 2% (blank) 83% 83% 83% 76 Hobbies, Home and Garden Bundle 1 A = Interest in Sewing and Knitting A 14% 12% 13% B = Interest in Woodworking B 2% 3% 3% C = Interest in Photography C 3% 3% 3% D = Home and Garden D 3% 3% 3% (blank) 78% 80% 78% 77 Home Improvement 1 A – Home Improvement Interest A 1% 1% 1% B – Home Improvement DIY B 27% 25% 27% (blank) 72% 74% 72% 78 Cooking and Wine 1 A = Gourmet Food and Wine A 7% 7% 6% B = Cooking B 18% 17% 18% C = Natural Foods C 0% 0% 0% (blank) 75% 77% 76% 79 Gaming and Gambling Enthusiast 1 Gaming casino, sweepstakes - Y or Blank (blank) 90% 90% 89% Y 10% 10% 11% 80 Travel Enthusiasts 1 A = Travel A 7% 8% 9% B = Domestic B 12% 12% 14% C = International C 0% 0% 0% D = Cruise D 2% 3% 2% (blank) 79% 76% 75% 81 Physical Fitness 1 A = Interest in Health Exercise A 17% 16% 17% B = Running B 0% 0% 0% C = Walking C 3% 4% 4% D = Aerobics D 1% 1% 1% (blank) 78% 79% 77% 82 Self Improvement 1 Health, medical, dieting weight loss, self-improvement (blank) 80% 81% 80% A- Health & Medical A 4% 3% 4% B- Dieting Weight loss B 8% 9% 9% C- Self Improvement C 9% 7% 7% 83 Automotive DIY 1 Parts and accessories, auto work = Y or Blank (blank) 92% 84% 83% Y 8% 16% 17% 84 Spectator Sports Interest 1 A = Interst in Sports, General A 10% 8% 8% B = Interest in Footbal B 4% 6% 6% C - Interest in Baseball C 1% 1% 1% D = Interest in Golf D 3% 3% 3% E = Interest in Tennis E 0% 0% 0% F - Interest in Auto and Motorcycle racing F 2% 3% 3% (blank) 80% 80% 80% 85 Outdoors 1 A = Interest in Outdoor - General A 9% 7% 7% B = Interest in Snow Sports B 1% 1% 1% C = interest in Water Sports C 0% 0% 0% D = Interst in Hunting and Fishing D 7% 11% 8% (blank) 83% 81% 84% 86 Avid Investors 1 Investing active = Y or Blank (blank) 80% 82% 82% Y 20% 18% 18% 87 Avid Interest in Boating 1 Interest in Boating = Y or Blank (blank) 96% 94% 95% Y 4% 6% 5% 88 Avid Interest in Motorcycling 1 Interest on Motorcycling = Y or Blank (blank) 99% 99% 98% Y 1% 1% 2% Geographic Demographics CA FL TX 89 Percent_Range_Black 1 Penetration Range - See Below (blank) 3% 2% 3% A - Greater than 95 A 0% 1% 0% B - 95 thru 91 B 0% 1% 0% C - 90 thru 86 C 0% 1% 0% D - 85 thru 81 D 0% 1% 0% E - 80 thru 76 E 0% 1% 1% F - 75 thru 71 F 0% 1% 0% G - 70 thru 66 G 0% 1% 0% H - 65 thru 61 H 0% 1% 1% I - 60 thru 56 I 0% 1% 1% J - 55 thru 51 J 0% 1% 1% K - 50 thru 46 K 0% 1% 1% L - 45 thru 41 L 0% 1% 1% M - 40 thru 36 M 0% 1% 2% N - 35 thru 31 N 1% 2% 2% O - 30 thru 26 O 1% 3% 3% P - 25 thru 21 P 2% 4% 4% Q - 20 thru 16 Q 3% 6% 5% R - 15 thru 11 R 6% 11% 9% S - 10 thru 6 S 11% 17% 16% T - Less than 6 T 72% 43% 50% 90 Percent_Range_White 1 Penetration Range - See Below (blank) 1% 1% 1% A - Greater than 95 A 1% 11% 3% B - 95 thru 91 B 3% 16% 10% C - 90 thru 86 C 7% 16% 15% D - 85 thru 81 D 8% 11% 13% E - 80 thru 76 E 8% 9% 11% F - 75 thru 71 F 8% 7% 9% G - 70 thru 66 G 8% 6% 7% H - 65 thru 61 H 7% 5% 6% I - 60 thru 56 I 8% 3% 5% J - 55 thru 51 J 8% 2% 4% K - 50 thru 46 K 8% 2% 3% L - 45 thru 41 L 7% 1% 3% M - 40 thru 36 M 5% 1% 2% N - 35 thru 31 N 4% 1% 2% O - 30 thru 26 O 3% 1% 1% P - 25 thru 21 P 3% 1% 1% Q - 20 thru 16 Q 2% 1% 1% R - 15 thru 11 R 1% 1% 1% S - 10 thru 6 S 1% 1% 1% T - Less than 6 T 0% 1% 1% 91 Percent_Range_Hispanic 1 Penetration Range - See Below (blank) 1% 2% 2% A - Greater than 95 A 1% 2% 3% B - 95 thru 91 B 2% 1% 3% C - 90 thru 86 C 2% 1% 3% D - 85 thru 81 D 2% 1% 2% E - 80 thru 76 E 2% 1% 2% F - 75 thru 71 F 3% 1% 2% G - 70 thru 66 G 3% 1% 2% H - 65 thru 61 H 3% 1% 2% I - 60 thru 56 I 3% 1% 3% J - 55 thru 51 J 4% 1% 3% K - 50 thru 46 K 4% 2% 3% L - 45 thru 41 L 4% 2% 4% M - 40 thru 36 M 5% 3% 4% N - 35 thru 31 N 5% 3% 5% O - 30 thru 26 O 7% 4% 7% P - 25 thru 21 P 8% 6% 8% Q - 20 thru 16 Q 10% 9% 10% R - 15 thru 11 R 12% 13% 13% S - 10 thru 6 S 14% 21% 14% T - Less than 6 T 5% 24% 6% 92 Percent_Range_Asian 1 Penetration Range - See Below (blank) 2% 4% 8% A - Greater than 95 A 0% 0% 0% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 0% 0% D - 85 thru 81 D 0% 0% 0% E - 80 thru 76 E 0% 0% 0% F - 75 thru 71 F 1% 0% 0% G - 70 thru 66 G 1% 0% 0% H - 65 thru 61 H 1% 0% 0% I - 60 thru 56 I 1% 0% 0% J - 55 thru 51 J 1% 0% 0% K - 50 thru 46 K 2% 0% 0% L - 45 thru 41 L 2% 0% 0% M - 40 thru 36 M 2% 0% 0% N - 35 thru 31 N 3% 0% 0% O - 30 thru 26 O 3% 0% 1% P - 25 thru 21 P 5% 0% 1% Q - 20 thru 16 Q 7% 0% 2% R - 15 thru 11 R 12% 1% 4% S - 10 thru 6 S 21% 7% 10% T - Less than 6 T 37% 87% 72% 93 Percent_Range_English_Speaking 1 Penetration Range - See Below (blank) 7% 8% 8% A - Greater than 95 A 2% 11% 7% B - 95 thru 91 B 5% 15% 9% C - 90 thru 86 C 6% 12% 10% D - 85 thru 81 D 7% 10% 9% E - 80 thru 76 E 7% 7% 8% F - 75 thru 71 F 7% 6% 7% G - 70 thru 66 G 7% 4% 6% H - 65 thru 61 H 6% 4% 5% I - 60 thru 56 I 6% 3% 4% J - 55 thru 51 J 5% 3% 4% K - 50 thru 46 K 5% 2% 3% L - 45 thru 41 L 5% 2% 3% M - 40 thru 36 M 4% 2% 3% N - 35 thru 31 N 4% 2% 2% O - 30 thru 26 O 4% 1% 2% P - 25 thru 21 P 3% 1% 2% Q - 20 thru 16 Q 3% 1% 2% R - 15 thru 11 R 2% 1% 2% S - 10 thru 6 S 2% 1% 2% T - Less than 6 T 1% 2% 2% 94 Percnt_Range_Spanish_Speaking 1 Penetration Range - See Below (blank) 11% 16% 11% A - Greater than 95 A 0% 1% 1% B - 95 thru 91 B 1% 1% 2% C - 90 thru 86 C 1% 1% 2% D - 85 thru 81 D 1% 1% 1% E - 80 thru 76 E 2% 1% 1% F - 75 thru 71 F 2% 1% 2% G - 70 thru 66 G 2% 1% 2% H - 65 thru 61 H 2% 1% 2% I - 60 thru 56 I 2% 1% 2% J - 55 thru 51 J 3% 1% 2% K - 50 thru 46 K 3% 2% 3% L - 45 thru 41 L 3% 2% 3% M - 40 thru 36 M 4% 2% 3% N - 35 thru 31 N 4% 3% 4% O - 30 thru 26 O 5% 4% 5% P - 25 thru 21 P 6% 5% 6% Q - 20 thru 16 Q 7% 6% 8% R - 15 thru 11 R 10% 9% 10% S - 10 thru 6 S 14% 15% 14% T - Less than 6 T 17% 25% 16% 95 Percent_Range_Asian_Speaking 1 Penetration Range - See Below (blank) 28% 60% 59% A - Greater than 95 A 0% 0% 0% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 0% 0% D - 85 thru 81 D 0% 0% 0% E - 80 thru 76 E 0% 0% 0% F - 75 thru 71 F 0% 0% 0% G - 70 thru 66 G 0% 0% 0% H - 65 thru 61 H 1% 0% 0% I - 60 thru 56 I 1% 0% 0% J - 55 thru 51 J 1% 0% 0% K - 50 thru 46 K 1% 0% 0% L - 45 thru 41 L 1% 0% 0% M - 40 thru 36 M 2% 0% 0% N - 35 thru 31 N 2% 0% 0% O - 30 thru 26 O 3% 0% 1% P - 25 thru 21 P 4% 0% 1% Q - 20 thru 16 Q 6% 0% 2% R - 15 thru 11 R 9% 1% 4% S - 10 thru 6 S 15% 6% 8% T - Less than 6 T 27% 32% 26% 96 Percent_Range_SFDU 1 Penetration Range - See Below (blank) 20% 19% 15% A - Greater than 95 A 78% 78% 80% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 0% 0% D - 85 thru 81 D 0% 0% 1% E - 80 thru 76 E 0% 0% 1% F - 75 thru 71 F 0% 0% 0% G - 70 thru 66 G 0% 0% 1% H - 65 thru 61 H 0% 0% 1% I - 60 thru 56 I 0% 0% 1% J - 55 thru 51 J 0% 0% 0% K - 50 thru 46 K 0% 0% 0% L - 45 thru 41 L 0% 0% 0% M - 40 thru 36 M 0% 0% 0% N - 35 thru 31 N 0% 0% 0% O - 30 thru 26 O 0% 0% 0% P - 25 thru 21 P 0% 0% 0% Q - 20 thru 16 Q 0% 0% 0% R - 15 thru 11 R 0% 0% 0% S - 10 thru 6 S 0% 0% 0% T - Less than 6 T 0% 0% 0% 97 Percent_Range_MFDU 1 Penetration Range - See Below (blank) 82% 82% 86% A - Greater than 95 A 15% 15% 10% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 0% 0% D - 85 thru 81 D 0% 0% 0% E - 80 thru 76 E 0% 0% 0% F - 75 thru 71 F 0% 0% 0% G - 70 thru 66 G 0% 0% 0% H - 65 thru 61 H 0% 0% 0% I - 60 thru 56 I 0% 0% 0% J - 55 thru 51 J 0% 0% 0% K - 50 thru 46 K 0% 0% 0% L - 45 thru 41 L 0% 0% 0% M - 40 thru 36 M 0% 0% 0% N - 35 thru 31 N 0% 0% 0% O - 30 thru 26 O 0% 0% 0% P - 25 thru 21 P 0% 0% 0% Q - 20 thru 16 Q 0% 0% 0% R - 15 thru 11 R 0% 0% 0% S - 10 thru 6 S 0% 0% 1% T - Less than 6 T 1% 1% 2% 98 MHV 1 Median House Value Code (blank) 2% 2% 2% A - Less than $50,000 A 0% 1% 4% B = $50,000 - $99,999 B 1% 8% 32% C = $100,000 - $149,999 C 3% 17% 28% D = $150,000 - $249,999 D 14% 42% 23% E = $250,000 - $349,999 E 18% 17% 7% F = $350,000 - $499,999 F 27% 8% 3% G = $500,000 - $749,999 G 22% 3% 1% H = $750,000 - $999,999 H 10% 1% 0% I = $1,000,000 or More I 4% 0% 0% 99 MOR 1 Penetration Range Mail Order Respondent (blank) 12% 13% 13% A - Greater than 95 A 10% 10% 13% B - 95 thru 91 B 1% 0% 0% C - 90 thru 86 C 3% 2% 2% D - 85 thru 81 D 3% 2% 2% E - 80 thru 76 E 4% 3% 4% F - 75 thru 71 F 7% 6% 6% G - 70 thru 66 G 8% 8% 8% H - 65 thru 61 H 3% 3% 3% I - 60 thru 56 I 8% 7% 7% J - 55 thru 51 J 2% 2% 2% K - 50 thru 46 K 12% 13% 12% L - 45 thru 41 L 5% 5% 5% M - 40 thru 36 M 5% 6% 6% N - 35 thru 31 N 6% 7% 6% O - 30 thru 26 O 2% 3% 3% P - 25 thru 21 P 4% 4% 4% Q - 20 thru 16 Q 3% 3% 3% R - 15 thru 11 R 1% 1% 1% S - 10 thru 6 S 0% 0% 0% T - Less than 6 T 0% 0% 0% 100 CAR 1 Penetration Range Automobile owner (blank) 1% 1% 2% A - Greater than 95 A 81% 67% 81% B - 95 thru 91 B 1% 0% 0% C - 90 thru 86 C 5% 9% 5% D - 85 thru 81 D 4% 8% 5% E - 80 thru 76 E 0% 0% 0% F - 75 thru 71 F 3% 6% 3% G - 70 thru 66 G 0% 0% 0% H - 65 thru 61 H 2% 4% 2% I - 60 thru 56 I 0% 0% 0% J - 55 thru 51 J 1% 1% 1% K - 50 thru 46 K 0% 0% 0% L - 45 thru 41 L 1% 1% 0% M - 40 thru 36 M 0% 0% 0% N - 35 thru 31 N 0% 0% 0% O - 30 thru 26 O 0% 0% 0% P - 25 thru 21 P 0% 0% 0% Q - 20 thru 16 Q 0% 0% 0% R - 15 thru 11 R 0% 0% 0% S - 10 thru 6 S 0% 0% 0% T - Less than 6 T 0% 0% 0% 101 MEDSCHL 3 Median Years of School (blank) 3% 3% 4% 0-50 1% 1% 2% 51-100 7% 2% 8% 101-150 74% 87% 75% >151 15% 6% 11% 102 Penetration Range White Collar 1 Penetration Range (blank) 2% 2% 2% A - Greater than 95 A 0% 0% 0% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 0% 0% D - 85 thru 81 D 0% 0% 0% E - 80 thru 76 E 0% 0% 0% F - 75 thru 71 F 0% 0% 0% G - 70 thru 66 G 1% 0% 1% H - 65 thru 61 H 2% 1% 4% I - 60 thru 56 I 4% 3% 7% J - 55 thru 51 J 8% 6% 9% K - 50 thru 46 K 11% 9% 9% L - 45 thru 41 L 12% 11% 10% M - 40 thru 36 M 13% 13% 11% N - 35 thru 31 N 12% 15% 11% O - 30 thru 26 O 12% 14% 11% P - 25 thru 21 P 10% 11% 11% Q - 20 thru 16 Q 7% 8% 8% R - 15 thru 11 R 4% 5% 4% S - 10 thru 6 S 2% 2% 2% T - Less than 6 T 0% 0% 0% 103 Penetration Range Blue Collar 1 Penetration Range (blank) 12% 13% 11% A - Greater than 95 A 0% 0% 0% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 0% 0% D - 85 thru 81 D 0% 0% 0% E - 80 thru 76 E 0% 0% 0% F - 75 thru 71 F 0% 0% 0% G - 70 thru 66 G 0% 0% 0% H - 65 thru 61 H 0% 0% 0% I - 60 thru 56 I 0% 0% 0% J - 55 thru 51 J 0% 0% 0% K - 50 thru 46 K 0% 0% 0% L - 45 thru 41 L 0% 0% 0% M - 40 thru 36 M 0% 0% 1% N - 35 thru 31 N 0% 0% 2% O - 30 thru 26 O 2% 1% 4% P - 25 thru 21 P 5% 3% 9% Q - 20 thru 16 Q 11% 10% 17% R - 15 thru 11 R 21% 22% 21% S - 10 thru 6 S 26% 30% 21% T - Less than 6 T 22% 21% 14% 104 Penetration Range Other Occupation 1 Penetration Range (blank) 9% 9% 9% A - Greater than 95 A 0% 0% 0% B - 95 thru 91 B 0% 0% 0% C - 90 thru 86 C 0% 1% 0% D - 85 thru 81 D 1% 1% 0% E - 80 thru 76 E 1% 2% 1% F - 75 thru 71 F 3% 5% 2% G - 70 thru 66 G 5% 7% 4% H - 65 thru 61 H 9% 10% 7% I - 60 thru 56 I 13% 13% 10% J - 55 thru 51 J 17% 16% 12% K - 50 thru 46 K 17% 14% 14% L - 45 thru 41 L 13% 11% 15% M - 40 thru 36 M 7% 6% 13% N - 35 thru 31 N 3% 2% 9% O - 30 thru 26 O 1% 0% 3% P - 25 thru 21 P 0% 0% 1% Q - 20 thru 16 Q 0% 0% 0% R - 15 thru 11 R 0% 0% 0% S - 10 thru 6 S 0% 0% 0% T - Less than 6 T 0% 0% 0% 105 DEMOLEVEL 1 DEMO APPEND Level (blank) 0% 0% 0% 5 - Zip 5 4% 3% 5% 7 - Zip, Zip+2 7 0% 1% 1% 9 - Zip, Zip+4 9 96% 96% 93% Recipient agrees to comply with all federal, state, and/or local privacy laws, data protection laws, rules and regulations, and other such similar laws, rules or regulations, which are or which may in the future be applicable to the accompanying Data.The information in this document may not be altered, changed, or recreated without written consent of Infutor Data Solutions Inc. – “Infutor Confidential and Proprietary” DSO 510 Assignment 5 Problem Statement The goal of this analytics approach is to optimize Home Depot’s sales through their coupon system. After a purchase is made at Home Depot, the receipt can contain up to 3 coupons. Given that each basket purchase cannot be tied to a customer, there is no way for Home Depot to track customer identities or purchase history. For every purchase made at Home Depot, they collect the following data: · What products were bought in that basket · Time of purchase · Price of items in the basket They are looking to leverage an analytics framework that allows them to choose the coupon combination for each basket purchase receipt that is most likely to result in recurring purchases (and subsequently, an increase in sales). I will therefore propose a strategy to accomplish this goal. Offering the Right Coupon I must consider the following when designing a strategy that offers a coupon that looks to optimize sales: · The right product category · Am I offering this person a coupon for a product or product category that they will likely purchase from the next time they visit Home Depot? · Understanding this most promising product category is important to designing an effective solution. · The right discount and stretch · Based on the characterization of this basket, am I offering the right discount to this customer? · In order to maximize sales, it is important that Home Depot offers a coupon that maximizes their revenue and offers a coupon at the higher price end of a basket’s item assortment. · The right contextual timing · Considering that Home Depot is mainly a hardware and tool retailer, weather and regional conditions must also be taken into consideration when offering an appropriate coupon. · For example, it would not likely be appropriate to offer a coupon for snow chains in the summer, even though chains of another sort were included in a basket purchase. Proposed Analytics Framework In order to take into account that my solution offers up to 3 coupons in the right product category, the right discount, and the right timing, I propose the following analytics approach. It is comprehensive, efficient, and has a high likelihood of offering the right coupon to the right customer that they are most likely to redeem in the future. I believe that an effective way to address this is through the use of association analysis. Association analysis (most commonly performed with the apriori algorithm) is commonly used to determine, for each item in a store, which items are most likely to be bought with that item. This would be a useful way to determine what coupons to provide a customer depending on what's in their basket: offer them coupons for products that are most commonly bought with their items. I could apply this to Home Depot Coupon Optimization as follows:  · Step 1: Assign each item sold in Home Depot a product category · This provides a baseline that categorizes each item but also relates items to one another through their shared product category family. · Step 2: Build a database of items most commonly bought with each item at Home Depot from historical transaction data using association analysis (that crosses a certain lift threshold). · For example, we have Huggies diapers as a sample item. When we run the association analysis for this item, we identify the items to be bought with these diapers that score above a lift of 2 (2 times more likely to buy the combination of items rather than just Huggies diapers alone) · As an example, it is found that Coors Light is most commonly purchased with Huggies Diapers from the association analysis · Do this for every item in the store and build a database that contains this information for every item. Build one such database each month to account for seasonality. · Step 3: Now we look at how this system runs in real time during a basket purchase. After all items in a basket are scanned, sort them by descending price and look to the most expensive item in the basket to see what items are commonly bought with that item from the database. · For example, from a sample basket purchase, the most expensive item is Huggies diapers, and Coors Light is most commonly bought with that · (I start with the most expensive item in order to maximize sales and offer coupons for items in the higher end of the basket's price range, with the assumption that commonly bought items with more expensive could be higher in cost) · Step 4: If an item on that list is not already in the basket, offer a coupon for that item (or a coupon most closely related to that item in the case there is no coupon for that specific item) and continue until all items in the list have been exhausted for that item · If a customer didn't already purchase Coors Light, offer a coupon out of all of Home Depot’s available coupons that most closely matches to Coors Light · Step 5: Keep iterating to the next item by descending price and perform the same analysis until up to 3 coupons have been printed on the receipt · Step 6: In the case that all suggested items from the database have been bought, offer coupons from the most likely to purchase product category Summary I argue that the proposed solution will be both feasible and effective in helping Home Depot to increase their sales, but it will result in the right coupons being printed to increase sales for the following reasons: · The right product category: It is comprehensive in determining the correct product category from which to offer coupons (covers all product categories and discovers trends in commonly bought items that may not be obvious). In addition, association analysis has the potential to offer coupons for items bought both frequently and unexpectedly. · The right discount and stretch: It also uses price range order as a priority to offer coupons with just the right discount and stretch for that basket to increase sales. · The right contextual timing: It accounts for seasonality by building separate association databases for different seasons of the year; this way, seasonal items and purchase combinations will also be accounted for in each month’s association analysis Therefore, this strategy would be effective for Home Depot to be able to optimize their coupon system with the ultimate goal of increasing future sales. Coupon Optimization Solution Overview: Find what to print on coupon based on the association rules. Solution Details: 1. Generate frequent itemsets from a list of items using Apriori principle: Support > minsup • What I want to do in Step 1 is to find all frequent itemsets with support > minsup (as shown in the graph below). Here, I hope to clarify a few concepts I will use to build up the solution: itemsets is the combination of items sold in Home Depot support is the fraction of transaction containing certain itemset. Support of X = +,-./-0123./ 03.1-2.2.4 5 +31-6 .789:, 3; 1,-./-0123./ minsup is a threshold decided based on professional judgement and used to filter the itemsets. Apriori principle is a methodology that helps us find frequent itemsets in a more efficient way (compared to create millions of combinations of all products and then compare their support to the minsup). It tells us that all subsets of a frequent itemsets must also be frequent. That is, if we remove an item from a certain item, the support of the itemset will either go up or remain the same. In other words, if we realized the support of {light, toilet paper} is below the minsup, and an itemset with any item added to it, for example, {light, toilet paper, pillow} will never go beyond minsup too. The reason I want to filter the itemsets is that, if some itemset have a very low support, then I don’t have enough information on the association between its items, hence we can barely draw conclusion from such rule. Let’s get started with generating frequent itemsets. • Create itemsets having only one item sold in Home Depot. The itemsets will look similar to this: {light bulb}, {toilet paper}, {pillow}, {quilt}. • Seek frequent itemsets. Choose itemsets with Support > minsup, let’s say we filtered out {quilt}. • Generate all itemsets of length 2 and then trim the ones with support < minsup. Itemsets of length 2 will looks like: {light bulb, toilet paper}, {toilet paper, pillow}, {light bulb, toilet paper} • Generate all itemsets of length N+1 and then trim the ones with support < minsup. • Stop until when all itemsets of length K are below the minsup. Now we have the Maximal frequent itemset, where no item can be added so that the itemsets still remains above the minsup threshold. 2. Generate all possible rules from the frequent itemsets: lift > minconf • What I try to do in Step 2 is to identify rules with lift > minconf Like what I did in Step 1, I’d like to define the concepts I will use in the following discussion: Support of X to Y: The fraction of having both itemset X and itemset Y in the one transaction Confidence of X to Y: The conditional probability of occurrence of Y (the consequent) in the cart given that the cart already have itemsets X (the antecedent) Minconf: It is the threshold we built to select rules that fall above a minimum confidence level Anti-monotone property: It’s a trait of confidence of rules that helps us filter in a more efficient way. It tells us: Confidence of (A, B, C→ D) ≥ (B, C → A, D) ≥ (C → A, B, D). Because the numerator is the same for all three, and the denominator keeps increasing (transaction containing D is > transaction containing A and D > transaction containing A, B and D). Thus, the more items we have in X, the higher the confidence. That is to say, we can trim the rules in the way we did for the frequent itemsets (as shown in the graph below) Let’s start with selecting association rules • Start with one of the frequent itemsets. Form rules with only one consequent (Y). For example, for a frequent itemset {A, B, C, D}, the rule will look like this {A, B, C à D}, {B, C, D à A} etc. • Trim the rules with confidence < Minconf • Form new rules using a combination of consequences (Y) from the remaining ones, prune the ones < Minsconf • Repeat until there is only one item left in X (the antecedent) 3. Seek the subset of rules that give the highest lift • What I want to do in Step 3 is to identify subset of rules that give the highest lift. The reason why I hope to calculate lift is that, if we only measure rules using confidence, there’s a big problem. If the consequence is frequent (for example toilet paper), the confidence is always going to be high even if the association between X and Y is weak. By calculating the lift of rules, we take into consider the increase in occurrence of Y. Lift of X to Y: It measures the lift that {X} provides to our confidence for having Y on the cart. It is the probability of having {Y} in the cart given {X} is there, over the probability of having {Y} in the cart 4. Use the selected rules with the highest lift to determine the coupon After the previous three steps, we got a rule, for example, saying: {toilet paper, quilt, pillow} has the highest lift to {sheet}, then we will print out coupon related to sheet on the receipt that has toilet paper, quilt and pillow in the purchase list. References [1] Anisha Garg (2018, Sep 17). Complete guide to Association Rules (2/2) Retrieved December 4, 2019, from https://towardsdatascience.com/complete-guide-to-association-rules-2-2- c92072b56c84 [2] Anisha Garg (2018, Sep 3). Complete guide to Association Rules (1/2) Retrieved December 4, 2019, from https://towardsdatascience.com/association-rules-2-aa9a77241654 Problem Statement Home Depot wants to use historical transaction data to generate targeting coupons to increase sales. There are three main things to consider: - Coupons about what the customers bought - What might the customers buy next - From what other categories Data Structure The data will be organized by the transaction level which could be used for models and forecasts. Home Depot will have a great accuracy on goods sold on each transaction and the data will most likely to look like the following: Transaction ID Product ID Product Detail Categor Quantit Unit Price Discount % Total 1 G43 Banana Grocery 3 0.96 0 2.89 1 S356 Skateboard Sports 1 74.28 10% 66.85 2 B125 Beer Beverage 12 1.63 20% 15.65 The same transaction ID could have different products and totals because each transaction could have multiple items. Association Rule Mining The main purpose of association rule mining is to discover strong measures of relationships in databases. This is a very common practice where POS system data is used to discover regularities between products. The most classic example is the discovery of a strong association between beer and diapers. The transaction data will be aggregated to a matrix only involving item names and its associated transaction ID. Each row will contain 1 if the item is present in the transaction, or 0 if it is absent. The data will look something like this: (for simplicity sake assume only these 5 items could be discounted by a coupon) Transaction ID pl wood saw rebar ruler screws 1 1 1 0 0 0 2 0 0 1 0 0 3 0 0 0 1 1 4 1 1 1 0 0 5 0 1 0 0 0 Then an association rule for this data set is rebar, saw => plywood , meaning that customers buying rebar and saw also bought plywood. For Home Depot s matrix dataset, association rules need to be supported by hundreds or thousands (threshold determined by exports) of examples to make it a rule. Rules for Home Depot s case means that customers who buy a particular itemset X of items are most likely interested in the Y item(s) as well. Additionally, an itemset is defined as a combination of two or more items in the dataset. For itemsets, there are mathematical formulas to calculate the: ¾ S o How often the itemset appears in the dataset ¾ Confidence How often a rule is found to be true ¾ Lif The test for independence: if 1 then independent and rule is useless. <1 means negative association and >1 means the rule is dependent therefore has strong implication of the rule ¾ Con ic ion ratio of the frequency in an itemset X occurs without Y ¾ R le Po e Fac o intensity of positive relationship of items in the rule With the aforementioned concepts, we can use expert advice to set a constraint for the minimum support and minimum confidence to filter for only the top association rules. Out of those rules, we can check the lift (the larger the better), the conviction (the smaller the better) and rule power factor (the larger the better). By rank ordering the top-ranking association rules by support or confidence, we could employ coupon strategies to boost revenue for Home Depot. For coupon distribution, the top ranking association rule could be for example screws, ruler => plywood with high support and high confidence, we could offer screw coupons for customers who buy ruler or ruler coupons to those who buy screws as well as offer combination of screws and ruler buyers plywood coupons. If the accuracy of the rules needs to be tested, we can split the data into training and testing. Associate rules will be mined from the training set and tested on the test set to see the accuracy. Lastly, it is advised to give out coupons first in smaller scales as an experiment using the associate rules and test if the coupon strategy indeed increased revenue. Supplementar Solutions If we are unable to match a strong association rule with a purchase, we could either: 1) Give a coupon based on seasonality or time series trends 2) Give no coupons if no good trends match or if the customer is unlikely to return (could be proven if a local store asks for zip code and customer gives a zip code outside a set radius) For the first option based on seasonality, we can explore historic data and see what items are most bought in a particular time of the month or year. Based on this, we can give out coupons on items that is going to up trend in sales in advance. It is important to note that this solution might need to only employed on items that have a high margin. Additionally, it is best to conduct an experiment first to confirm the actual boost in revenue and compare it with past data when the coupon was not being given out (some items will sell regardless if discounts are present, so therefore we need to ensure that these items are either excluded or more quantities of them need to be sold to accommodate the loss on profit from discounts.) Additionally, any trendy items that are up trending in sale also could be in included in this supplementary solution. We need to perform time series models to forecast the demand of these items. This strategy also requires an experiment stage where boost of sales is confirmed in a smaller scale before full deployment. Class 2: Consumer Data, Target Marketing, Segmentation, Customer Value What data is available about consumers and where is it? How to evaluate and use a target marketing model Marketing segmentation – what it is and how to use it How to build a customer value measure 1 Homework 1: Prospect Solicitation 2 I’m a senior manager at a large bank. I oversee about 10 products that we provide to our customers. I have about 10 million credit card accounts, but only about 100,000 of these also have an installment loan with us. I’d like to do a solicitation campaign to try to get people who are currently noncustomers (prospects) to sign up for an installment loan. 2 Consumer Data Generally Available 3 Data in your company - data about your customers: Mostly behavioral data How/when customers came to you How they behave on existing accounts (behavioral data) This data is fairly accurate Data outside of your company - data about your customers and prospects: Mostly demographic, psychographic data Age, income, gender, occupation, education, marital status, race, presence of children, household size, spending categories, interests… This data is approximate, estimated, inferred, modeled… Comes from many sources: warranty registrations, magazine subscriptions, census bureau, property records, other public records, surveys, professional licenses, online cookies, surveys, social sites Some Available U.S. Data 4 Consumer demo/psychographics Estimates at consumer level (about 250 million adults) Age, gender, income, education, occupation, marital status… Personal interests: travel, shopping, health, electronics,… Census bureau Zip code level summaries. ~40,000 zip codes (ZCTAs) Population, land area, % races, distributions of age, gender, children, income, home value… Public records Home prices, court records, criminal records, sex offenders… Zillow Home values, home properties, past sales… Financial markets Histories of stocks, bonds, derivatives… Social network data PII (name, date of birth…), friends, interests, images… Pretty good discussion of and list of data selling companies: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwj7g-erpKPsAhXlCjQIHcA0Bi8QFjAAegQIAxAC&url=https%3A%2F%2Fwww.fastcompany.com%2F90310803%2Fhere-are-the-data-brokers-quietly-buying-and-selling-your-personal-information&usg=AOvVaw0UuqxSI8S7MYrnv0xmtY_M or just search for consumer data for purchase fastcompany.com Show Infutor 5 Business Problem: Decide Whom to Solicit Businesses make many offers to many consumers to buy their products. Making these offers costs the business money. Would like to narrow down the list of people to make offers to in order to lower costs. Don’t make offers to people who are unlikely to accept them. Solution – A target marketing score that predicts the likelihood a consumer will accept a product offer. Target marketing scores are used to rank-order prospects for marketing offers. Sort by the score and make offers only to the top scoring people, who have the highest likelihood or accepting. Saves substantial marketing costs. 6 Cumulative Lift over random Response score bin How to Use a Target Marketing Model Build a binary classification model to predict who will respond to an offer (respond/don’t respond) Score a large population of prospects for the offer Rank order the prospect list by the response score Offer the product to the top of the list Penetrate the list as deeply as the financials support: Maximize expected profit 7 Expected profit = Expected revenue – Contact cost Average revenue x probability of response Cost Expected revenue Expected profit List penetration % Offer to top ~25% of the ranked prospects Show Target Marketing Notebook 8 Citibank Zodiac Story Asked to build a target marketing model for credit insurance Asked to build models for 4 additional products Citi’s existing “Zodiac” system: rotate through 12 products, one each month. They said they have a problem that they can only offer 12 products because of this Zodiac system. Assignment: come up with a new system that can offer unlimited number of products. 9 Build a separate model independently for each product (column) Each model predicts the expected profit if we offer that product to that customer 10 Each number is the expected profitability for that customer for that product offer Many millions Many 10’s Model for Product A Model for Product B Model for Product E Model for Product D Model for Product C Model for Product G Model for Product F Separate, distinct, independent models How We Framed/Solved the Problem Next, Choose Which Offer For Each Customer First, cross off any disallowed offers (red x’s) Then run a profit optimization algorithm to select one offer for each customer (green circle) while fulfilling constraints. This requires a complex constrained optimization algorithm. 11 Many millions Many 10’s Must offer at least 50,000 Must offer between 100,000 and 400,000 Cannot offer more than 300,000 Divide entities (customers, products, households…) into distinct groups based on some criteria in order to make different actions for the different segments. We typically use a small to moderate number of segments because we want to take customized actions with each segment. Typically a few to a dozen or so segments. Example customer segmentation: Each customer is in one particular segment. We might have different communications, offers and/or strategies between segments. How do we decide how to divide? A business expert uses his judgement and experience, or We use a machine learning algorithm, typically clustering or a decision tree Segmentation for Marketing Purposes 12 Income Age Golden Eagles Grey Havens Steady Success Blue Neckware Rising Stars Algorithms For Marketing Segmentation 13 Decision trees (e.g., CART) (typically supervised): Carves up independent variable space x into boxes based on similarity of a dependent variable y. Metric of closeness is typically similarity of y. K means (typically unsupervised): Finds natural groupings of points (entities) in independent variable space x. Metric of closeness is typically Euclidean distance in x space, thus need to make sure all dimensions are properly scaled. Hierarchical Clustering Algorithms (HCA): Iteratively divides/aggregates points into groups based on nearness. Could be top-down or bottom up. Needs a metric of closeness, could be anything. After we finish the segmentation process we have divided our things (customers, products…) into separate groups Now we want to describe the unique and differentiated characteristics of the different segments. Make a table and calculate the averages of important characteristics Can divide by averages to easily see relative differences 14 How to Describe the Marketing Segments Younger Lower income Lower value Older Higher income High value Demographic Profiles of PA Population 15 Examine the Differences of Characteristics Across the Segments Demographic characteristics 12 segments Yellow is above average Green is below average Average Example slide from a large credit card company’s segmentation project 15 Segment Descriptions and Names Segment 1: “Diamonds in the Rough” Age (18+, avg: 49), Income (< $35K, avg: $25K), BCI (< $3500, avg: $1700) Low income across all age groups. Very high response rates. Single and younger, fresh faces and working class who could be new to the credit world. Regardless, this group is credit hungry and possesses the youngest average revolving age on trades across all the segments. These “Diamonds in the Rough” strive to establish their place and could have the most potential to bloom into valuable customers. They are not likely to be home owners, so probably are not bogged down yet by mortgage payments. This group is the least likely to have opened a bank card recently. Because of this, these fresh faces are highly responsive but somewhat riskier. Segment 2: “Golden Years” Age (49+, avg: 61), Income ($35K-$100K, avg: $64K), BCI (< $3500, avg: $2100) Middle income and older with very low revolving balances. These are older couples who may be close to retirement. They have more time on their hands to entertain themselves and their grandchildren. Their income will not increase with their growing age, yet these individuals tend to respond favorably to new credit offers. They are not likely to have opened a bank card recently so they tend to be more aware of and possibly seek out “good deals”. * Averages Based on Demographic Profiles for PA and SC 16 Example slide from a large credit card company’s segmentation project 16 Customer Value Measures (CVM) 17 What is it: Measure the value of each customer to my business Why: Use for diagnosis, analysis, understanding of who brings what value, differentiated treatments, prospect targeting Generally, Customer Value Measures are a measurement formula, combining known data values, with perhaps some models How: Basic form: CVM = profit from each customer = revenue – cost Sometimes add in “expert factors”, driven primarily by marketing people rather than financial One Simple Way to Make a Customer Value Measure 18 Start with a base value: Base value = profit from that customer = revenue – cost Use whatever data is available to measure revenue and cost at the customer level. Do the best you can. We generally don’t include fixed costs, that are the same for each customer (facilities, corporate overhead…). Only include revenue and cost explicitly for that customer. Interview the people requesting the measure. Are there any other possibly important factors? Add in expert factors as multiplicative enhancements: CVM = base value x f1 x f2 x f3… Can use smoothing formula for the expert factors: This formula gives up to a 50% boost depending on the value of “field”. Function to Smoothly Transition Between Values 19 Frequently we want a value to smoothly transition from one number to another. Can use a logistic formula: “Smoothed” value Two transition parameters: nmid is the value of n where the smoothed value is halfway between Ylow and Yhigh c is a measure of how quickly it transitions Smoothing counter n Could be integer or continuous Yhigh Ylow nmid 2c Different parameter choices make different shapes nmid = 30 c = .1 nmid = 50 c = 10 nmid = 20 c = 3 Can make a step function with a low value of c The value smoothly transitions between Ylow and Yhigh as the counter n increases Homework 2: ATT New Business 20 ATT is a large telecommunications company and they have really good data about phone calls globally. They would like to build new business services around the nice data assets that they have, similar to a data broker. One beautifully rich data set they have is Call Detail Records (CDRs). They would like to build a data broker service around this internal data. (google: call detail records). There are privacy restrictions around data that they can sell, but they can derive new data fields from their rich proprietary data. If averaged at a large enough geographical region (zip9, zip5, zip3?) these derived fields can be sold. They see a company called Claritas and others that make a lot of money selling highly descriptive consumer segments (google: Claritas PRIZM segments). An ATT executive want to build such a data broker business, selling such demographic-like segments built using the CDRs. Let’s start with the U.S. only. Design a process to build PRIZM-like segments using CDR records. 20 Product AProduct BProduct CProduct DProduct EProduct FProduct G Customer 16456415533562 Customer 22323435314246 Customer 315425464268384 Customer 4635763776441 Customer 536523129742325 Customer 64383975253524 Customer 72729151542246 Customer 841363734367373 Customer 952415915195226 Customer 1013274764244135 Customer 112294235422584 Sheet1 Product A Product B Product C Product D Product E Product F Product G … Customer 1 64 5 64 15 53 35 62 Customer 2 23 23 43 53 14 24 6 Customer 3 15 42 54 64 26 83 84 Customer 4 6 35 76 37 7 64 41 Customer 5 36 52 31 29 74 23 25 Customer 6 43 83 9 75 25 35 24 Customer 7 27 29 15 15 42 24 6 Customer 8 41 36 37 34 36 73 73 Customer 9 52 41 59 15 19 52 26 Customer 10 13 27 47 64 24 41 35 Customer 11 2 29 42 35 42 25 84 … Sheet2 Sheet3 Income Age Golden Eagles Grey Havens Steady Success Blue Neckware Rising Stars Income Age Golden Eagles Grey Havens Steady Success Blue Neckware Rising Stars Segment 1 Segment 2 Segment 3 Segment 4 … Average Age 62 37 25 46 … 42.5 Income 120,000 96,000 56,000 74,000 … 86,500 Monthly Spend 62 45 18 24 … 37.25 Customer Value 7.4 4.2 1.6 3.2 … 4.1 … … … … … … … Segment 1Segment 2Segment 3Segment 4…Average Age62372546…42.5 Income120,00096,00056,00074,000…86,500 Monthly Spend62451824…37.25 Customer Value7.44.21.63.2…4.1 … ……………… Segment 1 Segment 2 Segment 3 Segment 4 … Average Age 1.46 0.87 0.59 1.08 … 1 Income 1.39 1.11 0.65 0.86 … 1 Monthly Spend 1.66 1.21 0.48 0.64 … 1 Customer Value 1.80 1.02 0.39 0.78 … 1 … … … … … … … Segment 1Segment 2Segment 3Segment 4…Average Age1.460.870.591.08…1 Income1.391.110.650.86…1 Monthly Spend1.661.210.480.64…1 Customer Value1.801.020.390.78…1 … ……………… DIRGDYSRDSKLDLPFSGASACACWOPMGTACBHWTALL Age 48614053535252535253495152 Household Income $25,562$64,791$61,800$124,525$105,942$55,889$63,461$128,793$86,589$107,386$26,487$55,768$86,380 Gender (Male) 49.9%48.6%49.0%47.0%47.5%48.6%48.1%46.8%47.3%47.2%48.4%49.2%47.8% Married 32.7%57.0%50.1%67.1%70.7%60.2%61.4%77.3%0.0%100.0%38.6%55.2%65.3% # of children 0.160.370.310.440.520.370.430.610.330.670.220.380.48 1 Child 5.21%9.05%7.20%8.89%10.14%10.30%10.44%10.83%4.99%13.76%6.62%9.68%10.16% 2 Children 1.49%2.37%2.14%2.15%2.76%3.34%3.46%3.07%1.19%4.82%2.09%3.07%3.21% 3+ Children 2.77%7.82%6.52%10.21%12.11%6.58%8.48%14.70%8.53%14.42%3.62%7.56%10.52% Missing 90.53%80.77%84.14%78.75%74.99%79.80%77.62%71.40%85.28%67.00%87.66%79.70%76.10% Home value <$150K 77.6%67.3%66.0%34.1%37.7%72.8%49.8%22.3%24.7%20.2%65.2%64.8%34.6% $150-$250K 13.9%25.4%25.6%39.3%40.5%21.5%35.9%41.8%33.3%35.4%20.8%26.0%34.6% $250K+ 8.6%7.3%8.5%26.6%21.8%5.7%14.3%35.9%42.0%44.4%14.1%9.2%30.8% Wealth Highest 20% 5.2%9.0%10.6%26.9%27.7%12.8%23.5%44.9%47.7%52.8%12.0%14.0%34.9% High 20% 11.5%19.9%21.5%28.9%31.1%23.1%28.9%31.0%25.4%24.7%19.3%25.2%25.4% Middle 20% 19.7%24.8%25.1%22.9%23.1%27.0%24.2%15.8%14.8%13.2%24.1%27.0%18.8% Low 20% 28.9%25.5%23.9%14.4%13.3%24.2%16.6%6.6%8.7%7.0%26.8%22.6%13.4% Lowest 20% 34.7%20.8%18.9%7.0%4.8%12.9%6.9%1.7%3.4%2.3%17.8%11.2%7.4% Education College graduate 19.6%21.4%20.8%28.6%31.3%24.1%31.8%40.5%42.2%46.9%25.8%26.0%37.2% Some college 27.0%30.5%29.4%31.8%29.4%26.9%26.6%24.8%22.3%20.0%28.0%30.2%24.5% High school graduate 48.5%43.3%44.8%34.0%34.4%44.4%36.8%29.1%29.3%27.3%41.5%39.7%33.0% LOR 61091111109118116910 Dwelling (single) 61.3%80.2%74.6%82.9%89.3%86.9%88.3%93.0%84.1%96.4%73.2%83.2%87.9% Occupation Professional 23.8%27.8%27.7%34.0%35.3%29.9%35.2%38.7%36.9%40.6%29.8%32.2%36.6% Admin/management 8.9%11.2%10.9%14.2%14.4%12.0%14.1%16.7%14.6%18.3%11.1%12.4%15.6% Sales/service 8.0%6.0%6.1%6.0%6.2%6.0%6.6%6.5%7.9%7.1%7.9%6.5%6.8% Clerical/white collar 12.4%12.1%12.1%9.9%9.9%11.2%9.5%8.6%8.7%6.6%11.0%11.1%8.6% Blue Collar 22.9%24.6%23.6%16.4%16.4%23.3%17.2%11.5%9.9%9.5%18.2%20.8%14.0% Others 24.2%18.2%19.6%19.5%17.8%17.7%17.5%18.0%22.0%17.8%22.0%17.0%18.3% Affluence High 10.6%20.4%26.3%42.4%43.6%21.7%35.5%60.8%60.3%61.9%18.2%30.1%45.6% Medium 45.1%47.1%45.9%43.3%44.0%49.7%46.1%32.7%30.2%29.6%49.0%46.7%38.2% Low 44.3%32.6%27.8%14.4%12.3%28.6%18.4%6.5%9.5%8.5%32.8%23.2%16.2% %Pop 4.95%5.48%4.56%1.70%11.01%3.27%11.70%7.03%11.52%31.02%3.26%4.51%100.00% %Response 0.59%0.42%0.40%0.30%0.23%0.27%0.24%0.18%0.21%0.14%0.41%0.34%0.25% %Book 0.33%0.27%0.24%0.21%0.18%0.21%0.18%0.14%0.16%0.11%0.27%0.24%0.17% f1(field) = 1 + 1.5 � 1 1 + e�(field�fieldmid)/c < l a t e x i t s h a 1 _ b a s e 6 4 = " o J V 5 6 x 8 1 r d v d b E w Q y U E f V R f Y 8 d A = " > A A A C S 3 i c b V D L S 8 M w H E 7 n e 7 6 q H r 0 E N 0 E R Z y O K e h B E L x 4 V n A r b L G n 2 6 x Z M 2 p K k w i j 9 / 7 x 4 8 e Y / 4 c W D I h 5 M t x 3 m 4 w e B L 9 8 j j y 9 I B N f G 8 1 6 c 0 t j 4 x O T U 9 E x 5 d m 5 + Y d F d W r 7 W c a o Y 1 F k s Y n U b U A 2 C R 1 A 3 3 A i 4 T R R Q G Q i 4 C e 7 P C v 3 m A Z T m c X R l e g m 0 J O 1 E P O S M G k v 5 b l C t h j 7 Z y J p K 4 p C D a O e b + B g T v I U z U t v H 2 5 g 0 Y 5 s f M I U J 8 r t s e 9 R v P S M 7 v 4 8 l L 8 7 Z w S z P q 1 X f r X g 1 r z / 4 L y B D U E H D u f D d 5 2 Y 7 Z q m E y D B B t W 4 Q L z G t j C r D m Y C 8 3 E w 1 J J T d 0 w 4 0 L I y o B N 3 K + l 3 k e N 0 y b R z G y q 7 I 4 D 4 7 m s i o 1 L o n A + u U 1 H T 1 b 6 0 g / 9 M a q Q k P W x m P k t R A x A Y X h a n A J s Z F s b j N F T A j e h Z Q p r h 9 K 2 Z d q i g z t v 6 y L Y H 8 / v J f c L 1 b I 3 u 1 o 8 v d y s n p s I 5 p t I r W 0 A Y i 6 A C d o H N 0 g e q I o U f 0 i t 7 R h / P k v D m f z t f A W n K G m R X 0 Y 0 o T 3 + E H r j Q = < / l a t e x i t > Value = Ylow + Yhigh � Ylow 1 + e�(n�nmid)/c <latexit sha1_base64="jZkk48A5rkKp6gru5QJXUklwLsg=">AAACO3icbVC7TiMxFPWwPMMrQEljkSCBUMJMKNgGKWIbyoBIACVh5HFuEgs/RraHVTSa/9qGn6DbZpstQIiWHidMwetIls49517b90QxZ8b6/l9v6sf0zOzc/EJhcWl5ZbW4tt4yKtEUmlRxpS8jYoAzCU3LLIfLWAMREYeL6ObX2L+4BW2Ykud2FENXkIFkfUaJdVJYPCuX044WuEV4Ahk+wlfhpObqd7aX5sWQDYZZ5Z3TUe5OHOzBdVrZkRUZpoL1st19mmXlclgs+VV/AvyVBDkpoRyNsHjf6SmaCJCWcmJMO/Bj202JtoxyyAqdxEBM6A0ZQNtRSQSYbjrZPcPbTunhvtLuSIsn6vuJlAhjRiJynYLYofnsjcXvvHZi+z+7KZNxYkHSt4f6CcdW4XGQuMc0UMtHjhCqmfsrpkOiCbUu7oILIfi88lfSqlWDg2rttFaqH+dxzKNNtIV2UIAOUR2doAZqIor+oH/oAT16d95/78l7fmud8vKZDfQB3ssrGe+s7A==</latexit> 0 0.5 1 1.5 2 2.5 0 10 20 30 40 50 60 70 80 90 100 0 0.5 1 1.5 2 2.5 0102030405060708090100 0 0.5 1 1.5 2 2.5 0 20 40 60 80 100 0 0.5 1 1.5 2 2.5 0 20406080100 0 0.5 1 1.5 2 2.5 0 20 40 60 80 100 0 0.5 1 1.5 2 2.5 0 20406080100 0 0.5 1 1.5 2 2.5 0 20 40 60 80 100 0 0.5 1 1.5 2 2.5 0 20406080100