Rockbuster Stealth Global Market Analysis

This project supports data-driven business growth
by identifying global markets using SQL analytics.
A comprehensive dashboard reveals customer trends
and informs strategic decision-making.

Project overview

Rockbuster Stealth LLC is a movie rental company with global ambitions. This project uses SQL to analyze the company’s existing customer data and provides actionable recommendations to support its international expansion strategy.

Objective

 Perform an analysis to help with the launch strategy for the new online video service and to answer management’s business questions.

 

🎯 Goal

Use SQL to extract and interpret business data to:

  • Identify the most promising countries for expansion.

  • Understand customer rental behavior and trends.

  • Provide strategic recommendations to stakeholders.

 

🔧 Tools & Techniques

  • PostgreSQL & DbVisualizer

  • SQL (Joins, Subqueries, Aggregations)

  • Business KPI Development

  • Data Storytelling

 

📊 Key Analysis & Visualizations

Top 10 Countries by Revenue

India (56 customers) and China (53) lead in customer volume, followed by the United States (36) and Japan (32). These countries show high user engagement and are prime targets for global expansion.

Where are customers with a high lifetime value based? 

Eleanor Hunt (South Africa) spent the most at $211.55, followed by Marion Snyder (Brazil) with $194.61 and Rhonda Kennedy (Germany) at $191.62. High-value customers are globally distributed, not limited to a specific region.
SELECT country,
    COUNT(A.customer_id) AS customer_count,
    SUM(amount) AS total_payment
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_id = D.country_id
INNER JOIN payment E ON A.customer_id = E.customer_id
GROUP BY country;
SELECT
  A.first_name,
  A.last_name,
  D.country,
  C.city,
  SUM(E.amount) AS total_amount
FROM payment E
INNER JOIN customer A ON E.customer_id = A.customer_id
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_id = D.country_id
GROUP BY A.customer_id, D.country, C.city
ORDER BY total_amount DESC
LIMIT 10; 
WITH profitable_films_cte AS (
  SELECT A.title,
         SUM(D.amount) AS total_amount
  FROM film A
  INNER JOIN inventory B ON A.film_id = B.film_id
  INNER JOIN rental C ON B.inventory_id = C.inventory_id
  INNER JOIN payment D ON C.rental_id = D.rental_id
  GROUP BY 1
  ORDER BY total_amount DESC
  LIMIT 10
),

film_category_name_cte AS (
  SELECT A.title,
         C.name
  FROM film A
  INNER JOIN film_category B ON A.film_id = B.film_id
  INNER JOIN category C ON B.category_id = C.category_id
  GROUP BY 1, 2
)

SELECT profitable_films_cte.title,
       film_category_name_cte.name AS category,
       total_amount
FROM profitable_films_cte, film_category_name_cte;

 

Which movies/categories contributed the most to revenue gain? 

Telegraph Voyage earned the highest revenue at $215,750, while Sports and Foreign were the most profitable categories, exceeding $135,000 each.

Global Revenue Distribution by Country / Regional sales by country

This filled map uses a stepped red color scale to show total customer payment amounts by country.

• Darker red countries represent higher total revenue • Lighter red countries reflect lower revenue levels
India, China, and Japan lead in total sales, indicating strong market presence. Brazil, Mexico, and Italy show moderate performance. Several countries have low or no sales, possibly due to limited operations or data issues.

📊 Key Findings

  • Top Countries by Rental Volume: Germany, Brazil, and France showed the highest rental volumes.

  • Customer Segmentation: High-value customers were concentrated in urban regions with consistent weekly activity.

  • Film Inventory: Action and Sci-Fi categories performed best across all age groups.

  • Revenue Distribution: India and China led in total revenue, with the U.S. and Japan also performing strongly.

  • Geographic Spread: High-value customers were distributed across five continents, showing global user engagement.

 

💡 Recommendations

 

  • Expand operations in top-ranked countries and cities:
    Focus growth efforts in markets like India, China, and Brazil, where customer engagement and revenue are highest.

  • Localize marketing strategies:
    Use local languages and culturally preferred genres to improve customer connection and increase campaign effectiveness.

  • Streamline inventory around high-performing genres:
    Prioritize Action and Sci-Fi categories which consistently drive high revenue across customer segments.

  • Target high-value urban markets:
    Allocate resources to cities with dense, active user bases where frequent rentals are observed.

  • Explore growth in underperforming regions:
    Investigate markets like Turkey, Indonesia, and others with lower sales but potential for strategic entry or reactivation.

 

Unlock Your Data's Potential

Ready to transform your data into strategic assets? Contact us now to discuss how our expertise, demonstrated through the Rockbuster project, can benefit your business. Enquire today for a personalized consultation!