Danny created Fresh Segments, a digital marketing agency that helps other businesses analyse trends in online ad click behaviour for their unique customer base.
Clients share their customer lists with the Fresh Segments team who then aggregate interest metrics and generate a single dataset worth of metrics for further analysis.
In particular - the composition and rankings for different interests are provided for each client showing the proportion of their customer list who interacted with online assets related to each interest for each month.
Danny has asked for your assistance to analyse aggregated metrics for an example client and provide some high level insights about the customer list and their interests.
For this case study there is a total of 2 datasets which you will need to use to solve the questions.
This table contains information about aggregated interest metrics for a specific major client of Fresh Segments which makes up a large proportion of their customer base.
Each record in this table represents the performance of a specific
interest_id based on the client’s customer base interest measured through clicks and interactions with specific targeted advertising content.
For example - let’s interpret the first row of the
interest_metrics table together:
In July 2018, the
composition metric is 11.89, meaning that 11.89% of the client’s customer list interacted with the interest
interest_id = 32486 - we can link
interest_id to a separate mapping table to find the segment name called “Vacation Rental Accommodation Researchers”
index_value is 6.19, means that the
composition value is 6.19x the average composition value for all Fresh Segments clients’ customer for this particular interest in the month of July 2018.
percentage_ranking relates to the order of
index_value records in each month year.
This mapping table links the
interest_id with their relevant interest information. You will need to join this table onto the previous
interest_details table to obtain the
interest_name as well as any details about the summary information.
|1||Fitness Enthusiasts||Consumers using fitness tracking apps and websites.||2016-05-26 14:57:59||2018-05-23 11:30:12|
|2||Gamers||Consumers researching game reviews and cheat codes.||2016-05-26 14:57:59||2018-05-23 11:30:12|
|3||Car Enthusiasts||Readers of automotive news and car reviews.||2016-05-26 14:57:59||2018-05-23 11:30:12|
|4||Luxury Retail Researchers||Consumers researching luxury product reviews and gift ideas.||2016-05-26 14:57:59||2018-05-23 11:30:12|
|5||Brides & Wedding Planners||People researching wedding ideas and vendors.||2016-05-26 14:57:59||2018-05-23 11:30:12|
|6||Vacation Planners||Consumers reading reviews of vacation destinations and accommodations.||2016-05-26 14:57:59||2018-05-23 11:30:13|
|7||Motorcycle Enthusiasts||Readers of motorcycle news and reviews.||2016-05-26 14:57:59||2018-05-23 11:30:13|
|8||Business News Readers||Readers of online business news content.||2016-05-26 14:57:59||2018-05-23 11:30:12|
|12||Thrift Store Shoppers||Consumers shopping online for clothing at thrift stores and researching locations.||2016-05-26 14:57:59||2018-03-16 13:14:00|
|13||Advertising Professionals||People who read advertising industry news.||2016-05-26 14:57:59||2018-05-23 11:30:12|
Interactive SQL Instance
You can use the embedded DB Fiddle below to easily access these example datasets - this interactive session has everything you need to start solving these questions using SQL.
You can click on the
Edit on DB Fiddle link on the top right hand corner of the embedded session below and it will take you to a fully functional SQL editor where you can write your own queries to analyse the data.
You can feel free to choose any SQL dialect you’d like to use, the existing Fiddle is using PostgreSQL 13 as default.
Serious SQL students will have access to the same relevant schema SQL and example solutions which they can use with their Docker setup from within the course player!
Case Study Questions
The following questions can be considered key business questions that are required to be answered for the Fresh Segments team.
Most questions can be answered using a single query however some questions are more open ended and require additional thought and not just a coded solution!
Data Exploration and Cleansing
- Update the
fresh_segments.interest_metricstable by modifying the
month_yearcolumn to be a date data type with the start of the month
- What is count of records in the
month_yearvalue sorted in chronological order (earliest to latest) with the null values appearing first?
- What do you think we should do with these null values in the
- How many
interest_idvalues exist in the
fresh_segments.interest_metricstable but not in the
fresh_segments.interest_maptable? What about the other way around?
- Summarise the
idvalues in the
fresh_segments.interest_mapby its total record count in this table
- What sort of table join should we perform for our analysis and why? Check your logic by checking the rows where
interest_id = 21246in your joined output and include all columns from
fresh_segments.interest_metricsand all columns from
fresh_segments.interest_mapexcept from the
- Are there any records in your joined table where the
month_yearvalue is before the
created_atvalue from the
fresh_segments.interest_maptable? Do you think these values are valid and why?
- Which interests have been present in all
month_yeardates in our dataset?
- Using this same
total_monthsmeasure - calculate the cumulative percentage of all records starting at 14 months - which
total_monthsvalue passes the 90% cumulative percentage value?
- If we were to remove all
interest_idvalues which are lower than the
total_monthsvalue we found in the previous question - how many total data points would we be removing?
- Does this decision make sense to remove these data points from a business perspective? Use an example where there are all 14 months present to a removed
interestexample for your arguments - think about what it means to have less months present from a segment perspective.
- After removing these interests - how many unique interests are there for each month?
- Using our filtered dataset by removing the interests with less than 6 months worth of data, which are the top 10 and bottom 10 interests which have the largest composition values in any
month_year? Only use the maximum composition value for each interest but you must keep the corresponding
- Which 5 interests had the lowest average
- Which 5 interests had the largest standard deviation in their
- For the 5 interests found in the previous question - what was minimum and maximum
percentile_rankingvalues for each interest and its corresponding
year_monthvalue? Can you describe what is happening for these 5 interests?
- How would you describe our customers in this segment based off their composition and ranking values? What sort of products or services should we show to these customers and what should we avoid?
index_value is a measure which can be used to reverse calculate the average composition for Fresh Segments’ clients.
Average composition can be calculated by dividing the
composition column by the
index_value column rounded to 2 decimal places.
- What is the top 10 interests by the average composition for each month?
- For all of these top 10 interests - which interest appears the most often?
- What is the average of the average composition for the top 10 interests for each month?
- What is the 3 month rolling average of the max average composition value from September 2018 to August 2019 and include the previous top ranking interests in the same output shown below.
- Provide a possible reason why the max average composition might change from month to month? Could it signal something is not quite right with the overall business model for Fresh Segments?
Required output for question 4:
|2018-09-01||Work Comes First Travelers||8.26||7.61||Las Vegas Trip Planners: 7.21||Las Vegas Trip Planners: 7.36|
|2018-10-01||Work Comes First Travelers||9.14||8.20||Work Comes First Travelers: 8.26||Las Vegas Trip Planners: 7.21|
|2018-11-01||Work Comes First Travelers||8.28||8.56||Work Comes First Travelers: 9.14||Work Comes First Travelers: 8.26|
|2018-12-01||Work Comes First Travelers||8.31||8.58||Work Comes First Travelers: 8.28||Work Comes First Travelers: 9.14|
|2019-01-01||Work Comes First Travelers||7.66||8.08||Work Comes First Travelers: 8.31||Work Comes First Travelers: 8.28|
|2019-02-01||Work Comes First Travelers||7.66||7.88||Work Comes First Travelers: 7.66||Work Comes First Travelers: 8.31|
|2019-03-01||Alabama Trip Planners||6.54||7.29||Work Comes First Travelers: 7.66||Work Comes First Travelers: 7.66|
|2019-04-01||Solar Energy Researchers||6.28||6.83||Alabama Trip Planners: 6.54||Work Comes First Travelers: 7.66|
|2019-05-01||Readers of Honduran Content||4.41||5.74||Solar Energy Researchers: 6.28||Alabama Trip Planners: 6.54|
|2019-06-01||Las Vegas Trip Planners||2.77||4.49||Readers of Honduran Content: 4.41||Solar Energy Researchers: 6.28|
|2019-07-01||Las Vegas Trip Planners||2.82||3.33||Las Vegas Trip Planners: 2.77||Readers of Honduran Content: 4.41|
|2019-08-01||Cosmetics and Beauty Shoppers||2.73||2.77||Las Vegas Trip Planners: 2.82||Las Vegas Trip Planners: 2.77|
You have probably come across this concept of customer segments or marketing segments in your everyday life, maybe without you even noticing it!
Segments or audiences are super popular in the digital marketing space and using these interests or traits of customers is a mainstay of massive businesses like Google, Facebook, Instagram, LinkedIn and other social media where there are targeted advertising.
Traditional businesses such as this client for Fresh Segments usually upload their customer emails or matched cookies into various digital marketing systems in order to generate some sort of match, usually using some machine learning methods, to other similar customers with the same interests.
Hopefully this case study helps you think about how these index metrics and compositions can be used for digital marketing!
If you’d like to see the official code solutions and explanations for this case study and a whole lot more, please consider joining me for the Serious SQL course - you’ll get access to all course materials and I’m on hand to answer all of your additional SQL questions directly!
Serious SQL students get access to complete solutions for each case study, the solutions will be made available inside the 8 Week SQL Challenge section of the course player as soon as they are ready!
Serious SQL is currently on sale for $29 and $19 for students from the standard price of $39 and $29, but only for a limited time so don’t miss out!
This section will be updated in the future with any community member solutions with a link to their respective GitHub repos!
If you found any of these 8 Week SQL Challenge case studies helpful or valuable - please consider supporting me on Ko-fi!
The 8 Week SQL Challenge is proudly brought to you Danny Ma and the Data With Danny virtual data apprenticeship program.
If you would like to contribute to the 8 Week SQL Challenge website or volunteer as a data community mentor - please get in touch with me anytime. I always enjoy meeting amazing people who have the same drive and motivation to help others!
You can reach me directly in the official Slack Channel or find me on social media:
From your friendly data mentor, Danny :)