Rockbuster Stealth Database Analysis

Rockbuster is a video rental company with plans to launch a digital streaming service.  The Rockbuster Management Board wants help answering key business questions to help the launch strategy.  I used PostgreSQL to analyze the data and provide insights. Then I used Tableau to create the visualizations.

Audience

Rockbuster Stealth Management Board

Data

Rockbuster Stealth Database:

Contains tables for payment, customer, rental, inventory, staff, category, rating, etc.

Techniques used for the project:

  • PostgreSQL

  • Cleaning, sorting and filtering data

  • Writing complex SQL queries

  • Merging tables within a database

  • Data visualization and storytelling with Tableau

Key Business Questions:

● Which countries are Rockbuster customers based in?

● Where are customers with a high lifetime value based?

● Do sales figures vary between geographic regions?

● Which movies contributed the most/least to revenue gain?

● What was the average rental duration for all videos?

Example Query

Business Question:

Find the top 10 countries for Rockbuster in terms of customer numbers.

Thought Process:

  1. We need two pieces of information; 1) a count of customers from the customer table and 2) the countries that they live in from the country table.

  2. To join the information from both of those tables, I needed to join the customer table with the address table, then the city table, then the country table.

  3. To get a count by country, I used GROUP BY country to ensure that the count of customers is broken down by country.

  4. Then we are interested in the ten highest, so I used ORDER BY COUNT (customer_id) desc and LIMIT 10 so that the query would only pull the top ten.

I like to label the tables I am working with ordered letters (ex. A = customer, B = address, C = City, D = country) to keep things clear while writing the query.

Which countries are Rockbuster customers based in?

Rockbusters customer base is spread all over the world. Let's take a closer look at the top countries.

Which movie ratings generate the most revenue?

Which movies generated the most revenue? And the Least?

Recommendations:

  1. Push marketing efforts in the top 10 countries to build customer base. Offer referral incentives to existing customers to generate new customers.

  2. Add and promote films that generate more revenue by focusing on more Adult rated movies (PG-13, NC-17, and R).

  3. Remove the least rented movies from inventory to save on licensing costs. Promote top 10 movies in a "Most Viewed" section.

  4. Focus on adding or creating more content for the most popular genres of movies: 1. Sports, 2. Sci-Fi, 3. Animation, 4. Drama, 5. Comedy.