Data Analysis Using SQL and Excel, 2nd Edition

CHAPTER 8 Customer Purchases and Other Repeated Events

 

Subscription-type customer relationships have well-defined starts and stops. This chapter moves from these types of relationships to those defined by multiple events that take place over time, such as purchases and website visits, donations and handset upgrades. Such relationships do not necessarily have a definite end, because any particular event could be the customer’s last, or it could be just another in a long series of events.

Repeated events require correctly assigning the same customer to events that happen at different times and perhaps through different channels. Sometimes we are lucky and customers identify themselves, perhaps by using an account. Identification of individuals can still be challenging. Consider the example of Amazon.com and a family account. The purchase behavior—and resulting recommendations—might combine a teenage daughter’s music preferences with her mother’s technical purchases with a pre-teen son’s choice of games.

Disambiguating individuals within one account poses one problem; identifying the same customer over time is another. When no account is available, fancy algorithms might match customers to transactions using name matching and address matching, credit card numbers, email address, and browser cookies and other information. This chapter looks at how SQL can help facilitate building and evaluating such techniques.

Sometimes, events occur so frequently that they actually represent ...