Data Analysis Using SQL and Excel

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 web site visits, donations and clicks. 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 chain of events.

The biggest challenge with repeated events is correctly assigning events to the same customer. Sometimes we are lucky and customers identify themselves using an account. Even in this case, identification can be challenging or confusing. 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 customers within one account is a challenge; identifying the same customer over time is another. When there is no associated account, fancy algorithms match customers to transactions using name matching and address matching (and sometimes other information). This chapter looks at how SQL can help facilitate building and evaluating such techniques.

Sometimes, the events occur so frequently that they actually represent subscription‐like behaviors. For instance, prescription data consists of drug purchases. Tracking ...