DVD Rental Market Insights and Performance Analysis

Introduction

The DVD rental dataset analysis offers an in-depth evaluation of the operational dynamics within a DVD rental business. Utilizing this dataset, the analysis aims to uncover rental trends, customer preferences, film performance metrics, and key financial indicators, which are essential for informed strategic decision-making. By synthesizing data from multiple interconnected tables into a cohesive framework, this analysis facilitates detailed, SQL-driven exploration, enabling the extraction of actionable insights for business optimization.

Strategic Objectives

1. Consolidate data from the three key tables into a cohesive dataset for efficient analysis.
2. Analyze film performance metrics, such as total sales, rental frequency, and average revenue.
3. Examine customer behavior patterns to identify retention rates and average spending.
4. Explore staff performance metrics, such as revenue contributions by staff members.
5. Develop KPIs to evaluate operational efficiency, customer value, and revenue growth.

Key Analysis Questions

1. Which is the top-performing actors in terms of total sales?
2. Which films are rented the most, and how does that affect total rental revenue?
3. Which customers contribute the most to the revenue, and how can we target high-value customers?
4. What are the trends in film rentals across different months, and how does seasonality impact rental demand?
5. How do film categories compare in terms of total sales and their contribution to overall revenue?
6. Which countries generate the highest average payment amounts, and what insights can we draw from this?

Data Preparation

To facilitate the analysis and ensure a cohesive understanding of the DVD rental business, the data from different sources were consolidated into unified tables. This preparation step enables efficient querying and comparison across various datasets, including films, actors, staff, customers, payments, and rentals.

SQL Query for 'merged_actor_film':

1  DROP TABLE IF EXISTS merged_actor_film;
2  CREATE TABLE merged_actor_film AS
3  SELECT DISTINCT
4      a.actor_id,
5      a.actor_name,
6      a.film_info,
7      f.film_id,
8      f.title AS film_title,
9      f.description AS film_description,
10      f.length AS film_length,
11      f.replacement_cost,
12      f.rating,
13      f.rental_duration,
14      f.rental_rate,
15      f.special_features,
16      c.category_name,
17      c.total_sales
18  FROM 
19      actor a
20  JOIN 
21      film_actor fa ON a.actor_id = fa.actor_id
22  JOIN 
23      film f ON fa.film_id = f.film_id
24  JOIN 
25      film_category fc ON f.film_id = fc.film_id
26  JOIN 
27      category c ON fc.category_id = c.category_id;

• Consolidates actor, film, and category data.
• Facilitates analysis of films based on rental rates, sales, and category performance.
• Similarly, the other tables are the merged_staff_payment_rental table aggregates payment and rental data, giving insights into staff performance and revenue generation and the merged_customer table consolidates customer and geographic information, which is essential for understanding customer behavior and regional trends.

Data Analysis and Insights

Key Performance Indicators (KPIs)

1) Total Revenue

SQL Query:

1  SELECT CAST(SUM(payment_amount) AS FLOAT) AS total_revenue
2  FROM merged_staff_payment_rental;

Insight: This KPI calculates the total revenue generated across all transactions, providing a comprehensive view of the business's financial performance. It is a key indicator of the overall success of the DVD rental operation.

2) Average Revenue Per Customer (ARPC)

SQL Query:

1  SELECT CAST(ROUND(SUM(payment_amount) / COUNT(DISTINCT customer_id), 2) AS FLOAT) AS average_revenue_per_customer
2  FROM merged_staff_payment_rental;

Insight: This KPI measures the average revenue generated per customer, helping to identify how much revenue each customer contributes on average. It assists in understanding customer value and optimizing marketing strategies to target high-value customers.

3) Customer Lifetime Value (CLV)

SQL Query:

1  WITH customer_revenue AS (
2      SELECT customer_id, SUM(payment_amount) AS total_revenue
3      FROM merged_staff_payment_rental
4      GROUP BY customer_id
5  ),
6  avg_revenue AS (
7      SELECT AVG(total_revenue) AS avg_revenue_per_customer
8      FROM customer_revenue
9  ),
10  avg_rentals AS (
11      SELECT AVG(rental_count) AS avg_rentals_per_customer
12      FROM (
13          SELECT customer_id, COUNT(*) AS rental_count
14          FROM merged_staff_payment_rental
15          GROUP BY customer_id
16      ) AS rental_data
17  )
18  SELECT CAST(ROUND(ar.avg_revenue_per_customer * arpc.avg_rentals_per_customer, 2) AS FLOAT) AS customer_lifetime_value
19  FROM avg_revenue ar, avg_rentals arpc;

Insight: Customer Lifetime Value (CLV) helps estimate the total revenue a business can expect from a customer over the entire period of their relationship. This KPI is essential for understanding customer retention and the long-term value each customer brings to the business.

Detailed Analyses

Top-Performing Actors by Total Sales

SQL Query:

1  SELECT actor_name, CAST(SUM(total_sales) AS FLOAT) AS total_sales
2  FROM merged_actor_film
3  GROUP BY actor_name
4  ORDER BY total_sales DESC
5  LIMIT 10;

Insight: Identifies the actors whose films contribute the most to overall sales, enabling strategic promotion of high-performing talent.

Most Rented Films

SQL Query:

1  SELECT film_title, CAST(COUNT(*) AS FLOAT) AS rental_count
2  FROM merged_staff_payment_rental sr
3  JOIN merged_actor_film af ON sr.film_id = af.film_id
4  GROUP BY film_title
5  ORDER BY rental_count DESC
6  LIMIT 10;

Insight: Highlights the most rented films, revealing customer preferences and popular titles that can inform future inventory decisions.

Revenue by Rental Year

SQL Query:

1  SELECT EXTRACT(YEAR FROM rental_date) AS rental_year, CAST(SUM(payment_amount) AS FLOAT) AS total_revenue
2  FROM merged_staff_payment_rental
3  GROUP BY rental_year
4  ORDER BY total_revenue DESC;

Insight: Tracks revenue trends over the years, identifying peak periods and aiding in strategic planning.

Revenue Contribution by Staff

SQL Query:

1  SELECT staff_name, CAST(SUM(payment_amount) AS FLOAT) AS revenue_generated
2  FROM merged_staff_payment_rental 
3  GROUP BY staff_name
4  ORDER BY revenue_generated DESC;

Insight: Measures individual staff contributions to total revenue, providing a basis for performance evaluations and incentive programs.

Films Generating the Most Repeat Rentals

SQL Query:

1  SELECT af.film_title, CAST(COUNT(*) AS FLOAT) AS repeat_rentals
2  FROM merged_actor_film af
3  JOIN merged_staff_payment_rental sp ON af.film_id = sp.film_id
4  GROUP BY af.film_title
5  HAVING COUNT(*) > 1
6  ORDER BY repeat_rentals DESC
7  LIMIT 15;

Insight: Pinpoints titles with high repeat rentals, reflecting customer loyalty and enduring popularity.

Key Insights

1. Total revenue is $61,312.04, with an average revenue of $102.36 per customer and a customer lifetime value of $2,494.17, highlighting the importance of customer retention for maximizing long-term profitability.

2. Titanic Boondock and Telemark Heartbreakers lead with 264 rentals each. Top customers like Eleanor Hunt ($211.55) and Karl Seal ($208.58) significantly drive revenue. Susan Davis tops actor sales with $204,853, while staff members Jon Stephens ($31,059.92) and Mike Hillyer ($30,252.12) contribute the most to revenue.

3. Jon Stephens and Mike Hillyer generated $31,059.92 and $30,252.12, respectively. 2005 revenue was $60,797.86, while 2006 dropped to $514.18. Rentals of 6 days earned the most ($3,488.99). Telemark Heartbreakers and Titanic Boondock led with 264 repeat rentals each.

4. Top actors by rentals: Susan Davis (750) and Gina Degeneres (685). Crusade Honey had the longest rental (175.8 hours). Travel and Games have the highest rental rates ($3.30). July (6,713) and August (5,686) are peak rental months.

5. Top countries by payment: Nepal ($5.52), French Guiana ($4.89). Most active customers: Eleanor Hunt (22/month), Crystal Ford (21/month). Sports lead in sales (10.22%), followed by Foreign (7.4%).

Conclusion

The DVD rental analysis highlights critical operational and financial insights essential for driving strategic decision-making. By leveraging consolidated data and targeted SQL queries, this study provides a comprehensive view of performance metrics across films, customers, and staff.

Key findings include:

• Revenue Drivers: Top-performing films, actors, and rental trends across years reveal high-impact assets that should be prioritized for future inventory and marketing efforts.
• Customer Insights: Analysis of high-value customers and regional payment trends enables targeted marketing strategies and personalized service offerings to enhance retention and maximize revenue.
• Operational Efficiency: Staff performance metrics and revenue contributions provide a foundation for evaluating operational effectiveness and designing incentive programs.
• Market Dynamics: Seasonal rental trends and repeat rental patterns emphasize opportunities to refine promotional activities and optimize inventory during peak demand periods.

By utilizing these insights, the DVD rental business can implement data-driven strategies to boost revenue, enhance customer satisfaction, and maintain a competitive edge in the market.