Project Overview

The Daily Comparison Admission Dashboard was built to monitor and compare live cinema admission performance across multiple movie titles. The raw data came from scraping outputs stored in separate Google Sheets files for each movie. In order to make the data usable for visualization in Looker Studio, all sources needed to be consolidated into a centralized and reliable data source.

Initially, the data consolidation process relied heavily on Google Sheets formulas such as QUERY and IMPORTRANGE. However, as the volume of live admission data increased, the spreadsheet-based approach became unstable, slow, and difficult to maintain. This created the need for a more scalable data pipeline using BigQuery as the central processing layer.

Business & Technical Problem

The main challenge was to keep the dashboard fast, reliable, and up-to-date while handling multiple live scraping sources. Several technical issues were identified during the development process:

• Google Sheets capacity limitation: The combination of QUERY and IMPORTRANGE became too heavy as the number of rows increased.
• API quota limitation: Connecting Looker Studio directly to a BigQuery View built from external Google Sheets tables caused repeated live queries to Google Drive sources, which could quickly hit request limits.
• Dashboard performance issue: User interactions in Looker Studio triggered repeated queries to external sources, making the dashboard less stable.
• BigQuery billing concern: A refresh schedule that was too frequent could increase query execution volume and potentially raise BigQuery usage costs.

Solution

To solve these issues, I restructured the data source architecture by moving the consolidation process from Google Sheets into BigQuery and introducing a scheduled refresh mechanism. The final approach used a physical BigQuery master table as the main source for Looker Studio instead of querying external Google Sheets sources directly.

Data Architecture

The optimized architecture was designed to reduce dependency on live external sources during dashboard usage. Instead of allowing Looker Studio to repeatedly query multiple Google Sheets through BigQuery External Tables, the data was first consolidated and then materialized into a physical BigQuery table.

Technical Implementation

1. Creating External Tables from Google Sheets

Each scraping output stored in Google Sheets was connected to BigQuery as an External Table. A consistent schema was applied across all sources, including columns such as movie title, cinema name, exhibitor, city, showtime, total seats, filled seats, occupancy rate, show date, last update, and number of shows.

2. Standardizing the Schema

Before combining the data, the schema across all sources had to be aligned. This step was important to prevent mismatch errors during the UNION ALL process and to ensure that all sources could be queried as a single unified dataset.

3. Creating a Consolidated BigQuery View

A consolidated View was created to combine all live admission sources. This View served as the first centralized layer before being materialized into a physical table.

CREATE OR REPLACE VIEW `project.dataset.view_live_admission_all` AS
SELECT * FROM `project.dataset.movie_source_1`
UNION ALL
SELECT * FROM `project.dataset.movie_source_2`
UNION ALL
SELECT * FROM `project.dataset.movie_source_3`;

4. Materializing the View into a Physical Table

To avoid repeated live queries to Google Sheets through external tables, the consolidated View was converted into a physical BigQuery master table. This allowed Looker Studio to read from a stable and faster table source.

CREATE OR REPLACE TABLE `project.dataset.Tabel_Master_6Scraping` AS
SELECT *
FROM `project.dataset.view_live_admission_all`;

5. Automating Refresh with Google Apps Script Triggers

A Google Apps Script was created to automatically execute the BigQuery refresh query using the BigQuery Jobs API. Instead of running the query manually, the script was connected to a time-driven trigger in Google Apps Script, allowing the physical BigQuery master table to be refreshed automatically on a scheduled basis.

This trigger-based automation ensured that the Looker Studio dashboard could continue using updated admission data without manual intervention. The script executed the table refresh process, submitted the query job to BigQuery, and monitored the job status until the refresh was completed.

The refresh interval was adjusted from every 10 minutes to every 30 minutes. This interval was selected to keep the dashboard data reasonably fresh while reducing unnecessary query execution volume and helping control BigQuery billing usage.

Output & Impact

The optimized pipeline improved the reliability and scalability of the dashboard data source. By moving the dashboard source from live external Google Sheets queries to a physical BigQuery table, the dashboard became more stable and less dependent on Google Sheets API availability.

• Centralized multiple scraping sources into a single BigQuery-based data source.
• Reduced dependency on heavy Google Sheets formulas such as QUERY and IMPORTRANGE.
• Minimized the risk of Google Sheets and Google Drive request limit issues.
• Improved dashboard stability by using a physical BigQuery table as the Looker Studio source.
• Automated the refresh process using Google Apps Script.
• Balanced data freshness and cost efficiency by adjusting the refresh schedule.

Tools & Technologies

Key Learnings

This project helped me understand the importance of choosing the right data architecture for dashboard performance. While Google Sheets can be useful for lightweight workflows, it becomes less reliable when the data volume grows and when multiple users interact with dashboards frequently.

By using BigQuery as the central data processing layer, the dashboard source became more scalable, more stable, and easier to maintain. The project also highlighted the importance of balancing real-time needs with cost efficiency, especially when working with scheduled queries and cloud-based data warehouses.