ENRICH 101: Behavior-Based Personalization with Data Distiller: A Movie Genre Case Study

Scenario

The story starts with a US company called GitFlix, a new startup, that has been able to identify its list of users and their favorite movie genres. As a GitFlix marketer, your goal is to figure out the top genres that are popular by State and for each such combination, create a list of emails to run a campaign against.

One of the key learnings I want you to take away from this tutorial is that more than any tool or any concept such as segmentation or targeting, your understanding of data is key to unlocking value. Audiences are fluid because trends are everchanging. How you track the world and its tastes is through data. How that data is collected, managed, curated, and deployed responsibly is the ultimate act of providing great customer experience and service.

Prerequisites

Download & Setup DBVisualizer by Follow the instructions here:

Download the following file locally to your machine.

You need to also ingest CSV Files into Adobe Experience Platform by following the instructions here:

Explore the Dataset

1. Let us write the simplest query to understand what

select * from movie_data;sql

1. Let us count the number of records on the dataset. _id is a key that is unique and non-repeating that can be used to count the number of records. You should get 1000 in the result.

select count(distinct id) from movie_data;

1. Since email is the primary identifier for the customers in the list, let us now find if the distinct values of the emails match the record number.

select count(distinct email) from movie_data;

The result you should get is 976. This means a couple of things:

1. There are records that have emails as NULLs that need to be removed as they simply cannot be targeted. Note that COUNT with DISTINCT clause will not count all the NULLs as one unique value. This can happen if there were data quality issues upstream or the fact that such a record was created without requiring an email address at some point in time. We do not really know the cause of that issue.

2. There are records that have the same email associated with them. This could happen if we allow our system to register multiple users on the same email address. If that is so, we could simply aggregate the movie genre information across these records i.e. give them all equal weight.

3. There is another way to extract the same information using a relatively new feature in Data Distiller:

DROP TABLE IF EXISTS movie_stats;
ANALYZE TABLE movie_data COMPUTE STATISTICS as movie_stats;
SELECT * FROM movie_stats;

The results look like this:

Most of the mathematical statistics do not show up as the datatype is of string type. But take a look at the approximate uniques. It gives you a sense of the cardinality of the various dimensions. The nullCount of24 for email shows that there are 24 records that do not have this ID. As an exercise, I still do this manually writing SQL below but just be aware that this approach also exists. And if you are wondering why I had to write two commands to get the statistics, this is because Data Distiller conforms to the PostgresSQL syntax.

Count and Filter out NULL Identity Records

1. Let us count the number of records that have the email field as NULL

select count(COALESCE(email, 'unknown'))-count(distinct email) AS number_null_values from movie_data;

COALESCE takes all the records that have email values as NULL and converts them into the string specified i.e. "unknown". COUNT on this coalesced field will count duplicate instances of non-null values in the system i.e. 1000 records. This number needs to be subtracted from the unique non-null values which will equal 24.

1. To filter out the records with email values as NULL, we have:

select * from movie_data WHERE email!= '';

Identify if Duplicate Identity Records Exist

1. Let us count the number of records that have a non-NULL email field but have duplicate emails

select COUNT (DISTINCT id)-COUNT(DISTINCT email) AS Duplicate_Values from (select * from movie_data WHERE email!= '');

First, we filter the dataset of all the NULLs and then we run COUNT DISTINCT on the id and the email fields to see if they are in line. The answer you should get here is 0 meaning that they are indeed unique.

Movie Genre Popularity by State

1. We first group by State and movie genres without splitting the movie genres apart

select State, movie_genres, COUNT(DISTINCT email) AS CUSTOMER_COUNT from movie_data 
WHERE email!= ''
GROUP BY State, movie_genres
ORDER BY CUSTOMER_COUNT DESC

The results should look like this:

1. We still have results such as Comedy|Drama that are counted separately from Comedy and Drama. We need to be able to add customers that have these joint movie genres to the audiences by state and movie genre. For that, I need to be able to use a regular expression function to turn the movie_genres field into an array and then use the EXPLODE command to make a row per every genre value.

First, we will split at the pipe separator and then explode the strings:

SELECT State, email, explode(split(movie_genres, '\\|', -1)) AS movie_genres from movie_data 
WHERE email!= '';

The results look like this:

1. Remember, that we are giving equal credit to a customer for every genre that they are associated with. With that assumption, let us do a count by state for all the genres and we should see that the numbers are accurate for state and movie genre.

SELECT State, movie_genres, COUNT(email) as CUSTOMER_COUNT FROM (SELECT State, email, explode(split(movie_genres, '\\|', -1)) AS movie_genres from movie_data 
WHERE email!= '')
GROUP BY State, movie_genres
ORDER BY CUSTOMER_COUNT DESC

The results look like this:

Email List for State by Movie Genre Targeting

1. Let us create an array of emails for each of these combinations:

SELECT State, movie_genres, COUNT(email) AS CUSTOMER_COUNT, array_agg(email) as email_list FROM (SELECT State, email, explode(split(movie_genres, '\\|', -1)) AS movie_genres from movie_data 
WHERE email!= '')
GROUP BY State, movie_genres
ORDER BY CUSTOMER_COUNT DESC

The results look like this:

1. Since the campaigns have to be run by State and by movie genre, we need the resort to this by State column

SELECT State, movie_genres, COUNT(email) AS CUSTOMER_COUNT, array_agg(email) as email_list FROM (SELECT State, email, explode(split(movie_genres, '\\|', -1)) AS movie_genres from movie_data 
WHERE email!= '')
GROUP BY State, movie_genres
ORDER BY State, CUSTOMER_COUNT DESC