Scoring Framework

City & Cinema Demand Index Framework

Demand scoring framework combining historical performance, film-specific demand, benchmark metrics, coverage, and smoothing logic.

Project Overview

The objective of this project was to create a more objective and data-driven method for identifying high-potential cities and cinemas for movie promotion and special screening planning. Instead of relying only on intuition or a single performance metric, the scoring system was designed to evaluate demand using multiple indicators such as filled seats, occupancy rate, show count, number of cinemas, historical performance, and film-specific performance.

The project was developed in two stages. The first stage focused on building a daily City and Cinema Demand Intensity Index for performance monitoring and visualization. The second stage expanded the logic into a special screening recommendation query in BigQuery, where historical base performance and selected-film demand were combined to generate final ranking scores.

Business Problem

Selecting the right city and cinema for a special screening or promotional strategy is not always straightforward. A city with high total admissions may not always be the most relevant location for a specific movie, while a smaller cinema may show strong demand for certain movie types. Therefore, a scoring model was needed to support decision-making with a more balanced and measurable approach.

• Single metrics can be misleading: Occupancy rate alone may overrate small cinemas with limited seats, while ignoring large cinemas that generate high absolute admission volume.
• City and cinema performance varies by movie: A location that performs well historically may not always be the best match for a specific movie segment.
• Manual location selection can be subjective: Without a scoring framework, recommendations may rely too heavily on assumptions.
• Data inconsistencies can affect ranking: Variations in city names, cinema names, and exhibitor labels need to be standardized before scoring.
• Film-specific demand needs to be considered: Historical performance should be combined with demand patterns from selected comparable movies.

Solution

I developed a demand scoring approach that combines multiple normalized metrics into a final index. The scoring model evaluates both city-level and cinema-level performance using benchmark-based normalization, weighted scoring, coverage adjustment, and smoothing logic.

Demand Index Methodology

The demand index was designed to evaluate city and cinema performance using a combination of absolute demand, screening efficiency, and show accessibility. The goal was not only to find locations with high occupancy rate, but also to identify locations that consistently contribute large admission volume, receive strong show allocation, and remain relevant for the selected movie profile.

The methodology was divided into two major layers:

• General Performance Score: measures how strong a city or cinema is based on historical admission performance.
• Film-Specific Performance Score: measures how relevant a city or cinema is to a selected group of comparable movies.

General City & Cinema Performance Index

The general performance index was created to measure whether a city or cinema is fundamentally strong based on historical screening data. This index uses three main components: Volume Filled Seats, Survival Index, and Accessibility Index.

1. Volume Filled Seats

The first component measures the absolute audience contribution of a city or cinema. Large cinemas and metropolitan cities may have lower occupancy percentages because they provide a very large number of seats, but they can still contribute the largest number of actual tickets sold.

This is important because relying only on occupancy rate can create a percentage illusion. A small cinema with 80% occupancy may look stronger in percentage terms, but a large cinema with lower occupancy may still generate significantly higher total admission volume.

City Volume Ratio =
  Total Filled Seats City A / P95 Total Filled Seats National

Cinema Volume Ratio =
  Filled Seats Cinema A / P95 Filled Seats National

2. Survival Index

The second component measures whether a city or cinema is efficient enough to maintain show allocation. Even if a cinema generates high absolute ticket volume, a very low occupancy rate may indicate that many seats remain empty. In practice, this can increase the risk of showtime reduction because the screen is not being used efficiently.

Therefore, the survival index is used to measure how safe a city or cinema is from potential showtime cuts by comparing its occupancy rate against a national median benchmark.

City Survival Index =
  City Occupancy Rate / Median National City Occupancy Rate

Cinema Survival Index =
  Cinema Occupancy Rate / Median National Cinema Occupancy Rate

3. Accessibility Index

The third component measures show availability. A high number of showtimes indicates that the movie has strong access and visibility in a city or cinema. From a consumer perspective, more showtimes mean more flexibility to choose a convenient schedule. From an exhibitor perspective, high show allocation can also be interpreted as an organic validation of market demand.

The accessibility index compares the number of shows in a city or cinema against the national P95 show benchmark.

City Accessibility Index =
  Total Show Count City A / P95 Total Show Count National

Cinema Accessibility Index =
  Show Count Cinema A / P95 Show Count National

Final General Performance Formula

The final general performance index combines the three components using a weighted score. In this version, volume is given the largest weight because absolute ticket contribution is the most important factor for identifying large and commercially important locations.

General Performance Index =
  (Volume Filled Seats × 0.60) +
  (Survival Index × 0.20) +
  (Accessibility Index × 0.20)

Base Score for Special Screening Recommendation

For the special screening recommendation model, the general performance logic was expanded into a historical base score. The base score identifies cities and cinemas that are consistently strong over time, not only strong for one specific movie.

City Base Score

The city base score is used to identify cities that are historically active, high-volume, and commercially important. It uses several historical indicators from cleaned screening data.

• SUM(Filled_Seats): measures how large the city's total audience volume is.
• COUNT(no_show): measures how often the city is used for screenings.
• COUNT(DISTINCT movie_title): measures how many different movie titles have performed in the city.
• COUNT(DISTINCT week): measures how consistently active the city is over time.
• SUM(Total_Seats): measures the total seat supply available in the city.

city_base =
  1 - EXP(-(
    0.35 × filled / P95(filled) +
    0.25 × show / P95(show) +
    0.20 × title / P95(title) +
    0.10 × week / P95(week) +
    0.10 × seat / P95(seat)
  ))

Cinema Base Score

The cinema base score is used to identify cinemas that are important within their own city. Instead of only using absolute numbers, this score evaluates each cinema's contribution or share relative to the total city performance.

• base_share_fill: filled seats cinema / total filled seats city.
• base_share_show: show count cinema / total show count city.
• base_share_title: title count cinema / total title count city.
• base_share_week: active week count cinema / total active week count city.
• base_share_seat: total seats cinema / total seats city.

cinema_base =
  1 - EXP(-(
    0.35 × share_fill / P95(share_fill) +
    0.25 × share_show / P95(share_show) +
    0.20 × share_title / P95(share_title) +
    0.10 × share_week / P95(share_week) +
    0.10 × share_seat / P95(share_seat)
  ))

Film-Specific Demand Score

After identifying generally strong cities and cinemas, the model evaluates how relevant those locations are to the selected list of comparable movies. This prevents the recommendation from only favoring large locations and helps identify places that are specifically suitable for the movie profile being analyzed.

Film Score at City Level

At the city level, the selected-film score uses filled seats, occupancy rate, show count, and cinema count. Cinema count is included because a city with broader cinema coverage may indicate stronger city-wide demand rather than demand concentrated in only one cinema.

city_film_score =
  1 - EXP(-(
    0.40 × score_fill +
    0.15 × score_or +
    0.15 × score_show +
    0.30 × score_cinema
  ))

Film Score at Cinema Level

At the cinema level, the selected-film score uses filled seats, occupancy rate, and show count. The formula focuses on how strongly each cinema performs for the selected films.

cinema_film_score =
  1 - EXP(-(
    0.50 × score_fill +
    0.20 × score_or +
    0.30 × score_show
  ))

Weighted Average & Coverage Adjustment

The model does not use a simple raw average across selected movies. A simple average can overrate locations that only appear in one movie or unfairly penalize locations that appear across many movies. To solve this, the selected-film score is calculated using a weighted average based on filled seats, then adjusted by film coverage.

Weighted Average Pick Index =
  SUM(film_score_i × filled_i) / SUM(filled_i)

Coverage =
  film_count_available / total_selected_films

Adjusted Pick Index =
  Weighted Average Pick Index × SQRT(Coverage)

The square root coverage adjustment helps balance the score. Locations with incomplete selected-film coverage are adjusted downward, but not punished too aggressively.

Final Recommendation Formula

The final score combines historical strength and selected-film relevance. Historical base performance receives a higher weight because a city or cinema should first be proven strong and active in general. The selected-film score then adjusts the recommendation based on relevance to the movie profile.

City Final Score =
  0.65 × city_base +
  0.35 × city_pick

Cinema Final Score =
  0.65 × cinema_base +
  0.35 × cinema_pick

Selected SQL Logic

The scoring logic was implemented in BigQuery using multiple Common Table Expressions. The query normalized source data, calculated base scores, calculated selected-film scores, adjusted scores using coverage, and generated final city and cinema rankings.

-- Simplified scoring logic for portfolio publication
-- Sensitive project and dataset identifiers are masked.

WITH cfg AS (
  SELECT
    ['MOVIE_A', 'MOVIE_B', 'MOVIE_C'] AS film_list,
    DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH) AS base_start,
    CURRENT_DATE() AS base_end
),

src AS (
  SELECT
    UPPER(TRIM(movie_title)) AS movie_title,
    UPPER(TRIM(CITY)) AS city,
    UPPER(TRIM(Cinema_Name)) AS cinema_name,
    UPPER(TRIM(Exhibitor)) AS exhibitor,
    Filled_Seats,
    Total_Seats,
    SAFE_DIVIDE(Filled_Seats, Total_Seats) AS occupancy_rate,
    show_date
  FROM `masked-gcp-project.masked_dataset.Fact_Screening`
  WHERE Total_Seats > 0
),

city_base AS (
  SELECT
    city,
    SUM(Filled_Seats) AS base_filled,
    SUM(Total_Seats) AS base_seats,
    COUNT(*) AS base_show,
    COUNT(DISTINCT movie_title) AS base_title_count,
    COUNT(DISTINCT DATE_TRUNC(DATE(show_date), WEEK)) AS base_week_count
  FROM src, cfg
  WHERE DATE(show_date) BETWEEN cfg.base_start AND cfg.base_end
  GROUP BY city
),

city_pick AS (
  SELECT
    city,
    SUM(Filled_Seats) AS pick_filled,
    SUM(Total_Seats) AS pick_seats,
    COUNT(*) AS pick_show,
    COUNT(DISTINCT cinema_name) AS pick_cinema_count,
    COUNT(DISTINCT movie_title) AS film_coverage_count
  FROM src, cfg
  WHERE movie_title IN UNNEST(cfg.film_list)
  GROUP BY city
),

final_city_ranking AS (
  SELECT
    b.city,

    -- Historical base score
    1 - EXP(-(
      0.35 * SAFE_DIVIDE(b.base_filled, MAX(b.base_filled) OVER()) +
      0.25 * SAFE_DIVIDE(b.base_show, MAX(b.base_show) OVER()) +
      0.20 * SAFE_DIVIDE(b.base_title_count, MAX(b.base_title_count) OVER()) +
      0.10 * SAFE_DIVIDE(b.base_week_count, MAX(b.base_week_count) OVER()) +
      0.10 * SAFE_DIVIDE(b.base_seats, MAX(b.base_seats) OVER())
    )) AS city_base_score,

    -- Selected-film demand score with coverage adjustment
    (
      1 - EXP(-(
        0.40 * SAFE_DIVIDE(p.pick_filled, MAX(p.pick_filled) OVER()) +
        0.15 * SAFE_DIVIDE(SAFE_DIVIDE(p.pick_filled, p.pick_seats), 1) +
        0.15 * SAFE_DIVIDE(p.pick_show, MAX(p.pick_show) OVER()) +
        0.30 * SAFE_DIVIDE(p.pick_cinema_count, MAX(p.pick_cinema_count) OVER())
      ))
    ) * SQRT(SAFE_DIVIDE(p.film_coverage_count, ARRAY_LENGTH((SELECT film_list FROM cfg)))) AS city_pick_score

  FROM city_base b
  LEFT JOIN city_pick p
    ON b.city = p.city
)

SELECT
  city,
  city_base_score,
  city_pick_score,
  0.65 * city_base_score + 0.35 * city_pick_score AS city_final_score
FROM final_city_ranking
ORDER BY city_final_score DESC;

Output & Impact

The final output was a ranking system that could support data-driven decision-making for special screening planning and movie distribution strategy. The result provides ranked cities and cinemas based on both historical strength and relevance to selected comparable movies.

• Generated city-level and cinema-level demand rankings.
• Reduced subjectivity in selecting locations for special screenings.
• Combined historical performance with selected-film demand relevance.
• Used benchmark-based normalization to make scores more comparable across locations.
• Applied coverage and smoothing logic to reduce bias from limited data.
• Created a more explainable scoring framework for business users.
• Prepared the scoring output for future integration into Looker Studio dashboards.

Tools & Technologies

Key Learnings

This project helped me understand how analytical scoring models can support real business decisions. A good recommendation system does not always need to start with a complex machine learning model. In many business cases, a transparent and explainable scoring framework can be more practical, easier to validate, and more useful for stakeholders.

I also learned the importance of balancing absolute volume and relative performance. Large cities and cinemas may have strong total admission volume, but smaller locations can still show meaningful demand when evaluated through occupancy rate, show availability, and film-specific relevance.

Another key learning was the importance of coverage and smoothing. Without these adjustments, the model could overrate locations with very limited selected-film data. By applying coverage adjustment and smoothing logic, the ranking became more stable and more reliable for recommendation use cases.