Movie Rental Analysis for Rockbuster Stealth.

  • Background.

    Rockbuster Stealth LLC is a movie rental company seeking to use its existing movie licenses to launch an online video rental service, in order to stay competitive with companies like Netflix and Amazon Prime. To achieve this goal, the company must understand its current business, from product insights, market information and more.

  • Context.

    This project was completed as part of the CareerFoundry's curriculum.

    Topics covered were relational databases, SQL, data querying, filtering data, cleaning and summarizing data, joining tables, subqueries, and common table expressions.

    This knowledge was used to solve business questions for Rockbuster Stealth LLC, a fictional movie rental company.

  • Tools.

    SQL - Standard Query Language, which is used to access information stored in databases.

    Tableau - software for data visualization.

    DbVisualizer - a tool that creates a schema of databases, among other capabilities.

    pg4Admin - a platform that allows for Open Source SQL administration and development.

    PostgreSQL Database - an Open-Source database management system.

The Path to Answering Business Questions.

 

  1. Loaded data into the PostgresSQL Database, a relational database management system.

    • Allowed the Rockbuster records (ex: customer and product records) to be accessed with SQL.

Image by Mike van den Bos.

 

Click picture to open schema in a new window.

2. Extracted a schema of the Rockbuster database using DbVisualizer.

  • Created a visualization of how the tables in the Rockbuster database are connected to one another.

  • Used schema to determine how information could be queried when values located across multiple tables in one query.

 

3. Queried data records needed to answer business questions using SQL and pg4Admin.

  • Evaluated how to optimize queries (ex: which joins/columns/rows to use), to save on costs.

  • Pulled data needed for visualizations.

Query to return top ten countries with highest revenue.

 

Visualization used in business manager presentation.

4. Developed a presentation for business managers.

  • Determined which visualizations (created with Tableau) should be used to best communicate findings.

  • Decided to include appendices for those interested in learning further details.

 

5. Created documentation and a data dictionary for technical colleagues.

Image by Christopher Gower.

Key Findings.

  1. Rockbuster Stealth LLC customers are located in 108 countries; there is an international customer base.

  2. Sales figures do vary depending on the region. The top ten countries bringing in revenue are as follows: India, China, US, Japan, Mexico, Brazil, Russian Federation, Phillippines, and Turkey.

  3. The amounts of revenue each film is contributing to revenue is available here.

  4. 275 films in the Rockbuster Stealth database have not contributed any revenue.

  5. The average rental duration for videos is five days.

  6. There is only a three-month difference between the oldest and most recent payment dates. All accounts were created in 2006. Ideally, more information should be requested on this database.

Image by Agence Olloweb.

Deliverables.

 Next Step.

While the above information fulfills the requirements of the project, the next step would be making recommendations on how the online video rental service should be structured.