Project Overview

Cinema admission dashboards rely on Filled Seats data to monitor audience movement and ticket sales performance. This data was collected through a scraping process that updated admission numbers periodically. However, in some situations, dashboard numbers could appear to drop, remain stagnant, or look inconsistent with expected performance.

One possible cause was not actual audience behavior, but a data collection issue. If the latest scraped update happened too far before the actual showtime, the dashboard might not reflect the most recent filled seat condition. To investigate this, I created a BigQuery query that compares the gap between scheduled movie showtime and the latest successful scraping update.

Problem Statement

The main challenge was to determine whether an unusual admission pattern in the dashboard was caused by actual movie performance or by scraping update issues. Several conditions needed to be monitored:

• Admission data appeared stagnant or decreasing: Dashboard numbers sometimes looked unnatural and needed validation before being interpreted as real performance decline.
• Last update was not always close to showtime: Filled seats may not have been updated near the actual showtime, causing underreported admission values.
• Large showtime volume created scraping pressure: When the number of showtimes was very high, the scraping process could experience delays or incomplete updates.
• Issues could vary by exhibitor or city: Some problems might occur only for specific exhibitors, cities, or time zones.
• Timezone differences complicated comparison: Showtimes across Indonesia needed to be standardized into WIB to make the comparison easier.

Solution

I created a BigQuery query that transforms screening data into a monitoring table. The query calculates the time gap between each showtime and its latest update, converts local showtimes into WIB, and aggregates showtime volume across date, city, and exhibitor levels.

Monitoring Logic

The query was designed to answer several practical validation questions:

• Which movie is being checked?
• Which cities and exhibitors are affected?
• How many showtimes exist on the selected date?
• When was the latest successful update for each showtime?
• How far is the gap between showtime and last update?
• Is the gap large enough to indicate a possible scraping issue?

Query Workflow

Key Monitoring Metrics

The output query included several fields to support validation and debugging:

How to Interpret the Gap

The gap between showtime and last update helps indicate whether the admission data is likely to be fresh or potentially outdated.

Selected SQL Logic

The monitoring logic was implemented in BigQuery using Common Table Expressions. The query extracts screening data, standardizes timezone information, converts local showtimes into WIB, calculates update gaps, and produces monitoring fields for debugging admission data issues.

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

WITH base AS (
  SELECT
    movie_title,
    CITY,
    Exhibitor,
    Cinema_Name,
    show_date,
    Show_Time,
    last_update
  FROM `masked-gcp-project.masked_dataset.Fact_Screening`
  WHERE movie_title = 'TARGET_MOVIE'
    AND show_date IS NOT NULL
    AND Show_Time IS NOT NULL
),

timezone_mapping AS (
  SELECT
    *,
    CASE
      WHEN UPPER(CITY) IN ('BALI', 'MAKASSAR', 'MANADO', 'BALIKPAPAN') THEN 'WITA'
      WHEN UPPER(CITY) IN ('JAYAPURA', 'SORONG', 'AMBON') THEN 'WIT'
      ELSE 'WIB'
    END AS Zona_Waktu
  FROM base
),

showtime_local AS (
  SELECT
    *,
    DATETIME(DATE(show_date), Show_Time) AS Showtime_Datetime_Local
  FROM timezone_mapping
),

showtime_wib AS (
  SELECT
    *,
    CASE
      WHEN Zona_Waktu = 'WITA' THEN DATETIME_SUB(Showtime_Datetime_Local, INTERVAL 1 HOUR)
      WHEN Zona_Waktu = 'WIT' THEN DATETIME_SUB(Showtime_Datetime_Local, INTERVAL 2 HOUR)
      ELSE Showtime_Datetime_Local
    END AS Showtime_Datetime_WIB
  FROM showtime_local
),

final_monitoring AS (
  SELECT
    DATE(Showtime_Datetime_Local) AS Tanggal_Tayang_Local,
    DATE(Showtime_Datetime_WIB) AS Tanggal_Tayang_WIB,
    CITY,
    Zona_Waktu,
    Exhibitor,
    Cinema_Name,
    Show_Time,
    Showtime_Datetime_Local,
    Showtime_Datetime_WIB,
    MAX(last_update) AS Last_Update_Per_Showtime,

    DATETIME_DIFF(Showtime_Datetime_Local, MAX(last_update), HOUR)
      AS Gap_Jam_Showtime_Local_vs_LastUpdate,

    DATETIME_DIFF(Showtime_Datetime_Local, MAX(last_update), MINUTE)
      AS Gap_Menit_Showtime_Local_vs_LastUpdate,

    DATETIME_DIFF(Showtime_Datetime_WIB, MAX(last_update), HOUR)
      AS Gap_Jam_Showtime_WIB_vs_LastUpdate,

    DATETIME_DIFF(Showtime_Datetime_WIB, MAX(last_update), MINUTE)
      AS Gap_Menit_Showtime_WIB_vs_LastUpdate,

    COUNT(*) AS Jumlah_Showtime_Per_Jam

  FROM showtime_wib
  GROUP BY
    Tanggal_Tayang_Local,
    Tanggal_Tayang_WIB,
    CITY,
    Zona_Waktu,
    Exhibitor,
    Cinema_Name,
    Show_Time,
    Showtime_Datetime_Local,
    Showtime_Datetime_WIB
)

SELECT *
FROM final_monitoring
ORDER BY
  Tanggal_Tayang_WIB,
  Exhibitor,
  CITY,
  Showtime_Datetime_WIB;

Output & Impact

The final output was a monitoring table that helped validate whether unusual admission movement in the dashboard was caused by actual performance changes or by possible scraping update issues.

• Created a BigQuery-based monitoring query for admission data quality validation.
• Measured the gap between scheduled showtime and latest scraping update.
• Standardized local showtimes into WIB for easier comparison.
• Helped detect potential scraping delays when showtime volume was high.
• Supported debugging by exhibitor, city, date, and timezone.
• Improved the ability to distinguish actual admission trends from data collection issues.
• Provided a foundation for future dashboard-based monitoring or alerting.

Tools & Technologies

Key Learnings

This project helped me understand that dashboard numbers should not always be interpreted at face value. When data comes from automated scraping processes, unusual movements in the dashboard may be caused by data freshness issues, failed updates, or incomplete scraping rather than actual business performance.

I also learned the importance of building validation queries that help explain whether a data issue is caused by a specific exhibitor, city, timezone, or high showtime volume. This kind of monitoring is essential for maintaining trust in dashboards and supporting more accurate decision-making.

Another key learning was the importance of timezone standardization. Since movie showtimes occur across different Indonesian time zones, converting local showtimes into WIB made the comparison with last update timestamps easier and more consistent.