Looker Studio Dashboard Usage Intelligence: Automated Metadata Sync via Colab Enterprise

Executive Summary

AstraPay utilizes dozens of Looker Studio dashboards to support product, operational, and financial decisions daily. Over time, new dashboards were created organically across multiple departments. However, we faced a major challenge: a complete lack of visibility into report adoption. We had no insight into which dashboards were actively accessed by leadership, who the primary viewers were, or which reports were stale yet continued to trigger scheduled background BigQuery queries.

To address this, I designed and built an automated Looker Studio Dashboard Usage Intelligence tracking system. The system captures dashboard interaction logs in real-time via a GCP Cloud Logging Sink, enriches the raw Report hash IDs utilizing the Looker Studio API inside Google Colab Enterprise, and visualizes the aggregated statistics in a comprehensive internal adoption dashboard.

---

Log Ingestion & Metadata Synchronization Architecture

The log collection and metadata reconciliation pipeline is designed to run asynchronously in the background, ensuring no performance impact on dashboard loading times for the end-users:

sequenceDiagram
    autonumber
    actor User as User / Management
    participant LS as Looker Studio Report
    participant CL as GCP Cloud Logging Sink
    participant BQ as BigQuery Tables
    participant Colab as Google Colab Enterprise
    
    User->>LS: Opens / Interacts with Report
    LS->>CL: Triggers automatic audit log entry
    CL->>BQ: Streams logs in real-time to log_table (contains Report ID & User Email)
    
    Note over Colab: Scheduled Daily Execution (Daily Cron)
    Colab->>LS: Request metadata details via Looker Studio API
    LS-->>Colab: Return Metadata (Report Title, Creator, Create Date)
    Colab->>BQ: Syncs data to metadata_dim_table (Upsert)
    
    Note over BQ: JOIN log_table with metadata_dim_table
    BQ-->>LS: Serves interactive usage intelligence dashboard

---

Python Automation in Colab Enterprise

Raw GCP audit logs only contain unique Report hash IDs (e.g., 0a1b2c3d-4e5f-6a7b-8c9d-0e1f2a3b4c5d) rather than human-readable report names. To resolve this, I wrote a Python automation script in Google Colab Enterprise that runs on a daily schedule to fetch the actual report metadata using the Looker Studio API.

Here is the Python script that extracts report metadata and writes it to a structured BigQuery dimension table:

import os
import time
from google.cloud import bigquery
from googleapiclient.discovery import build
import google.auth

# Initialize Google Credentials & BigQuery Client
credentials, project = google.auth.default()
bq_client = bigquery.Client(credentials=credentials, project=project)

# Initialize Looker Studio API Client
# (Using Looker Studio REST API v1)
looker_studio_service = build('lookerstudio', 'v1', credentials=credentials)

def sync_report_metadata():
    # 1. Fetch unique Report IDs detected in BigQuery access logs
    query_distinct_ids = """
        SELECT DISTINCT report_id 
        FROM `prd-data-ap.audit_logs.looker_access_raw`
        WHERE report_id IS NOT NULL
    """
    query_job = bq_client.query(query_distinct_ids)
    rows = query_job.result()
    report_ids = [row.report_id for row in rows]
    
    metadata_records = []
    
    # 2. Call Looker Studio API to fetch metadata details per Report ID
    for rid in report_ids:
        try:
            # Call lookerstudio.reports.get endpoint
            report = looker_studio_service.reports().get(name=f"reports/{rid}").execute()
            
            metadata_records.append({
                "report_id": rid,
                "title": report.get("title", "Untitled Report"),
                "creator": report.get("creatorEmail", "unknown@astrapay.com"),
                "created_time": report.get("createTime"),
                "last_updated_time": report.get("updateTime"),
                "sync_timestamp": time.time()
            })
            # Add short delay to comply with API rate limiting
            time.sleep(0.2)
        except Exception as e:
            print(f"Failed to fetch metadata for ID: {rid}. Error: {str(e)}")
            
    # 3. Write/Update metadata records to the BigQuery Dimension Table
    if metadata_records:
        table_id = "prd-data-ap.audit_logs.looker_reports_metadata"
        job_config = bigquery.LoadJobConfig(
            write_disposition="WRITE_TRUNCATE" # Daily overwrite of dimension schema
        )
        load_job = bq_client.load_table_from_json(
            metadata_records, table_id, job_config=job_config
        )
        load_job.result() # Wait for job completion
        print(f"Successfully synchronized {len(metadata_records)} report metadata items to BigQuery.")

if __name__ == "__main__":
    sync_report_metadata()

---

BigQuery SQL Data Modeling

Once the dimension metadata table and the raw access logs fact table are collected in BigQuery, I compiled them into a unified view. This view serves as the primary data source for the Looker Studio adoption dashboard:

SELECT
  log.timestamp AS access_time,
  DATE(log.timestamp, "Asia/Jakarta") AS access_date,
  log.user_email AS viewer_email,
  log.report_id,
  -- Join with dimension table compiled via Colab API sync
  COALESCE(meta.title, "Unregistered / Deleted Report") AS report_title,
  meta.creator AS report_owner,
  DATE(TIMESTAMP(meta.created_time), "Asia/Jakarta") AS report_created_date,
  -- Flag external domains for security audit
  IF(REGEXP_CONTAINS(log.user_email, r'@astrapay\.com$'), 'Internal', 'External') AS user_type
FROM
  `prd-data-ap.audit_logs.looker_access_raw` log
LEFT JOIN
  `prd-data-ap.audit_logs.looker_reports_metadata` meta
ON
  log.report_id = meta.report_id
WHERE
  -- Limit historical lookback to minimize query scan cost
  log.timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY);

---

Business Impact & Cost Savings

Deploying the Looker Studio Dashboard Usage Intelligence framework produced clear, actionable benefits:

• Decommissioning Stale Reports: Identified that 32% of dashboards active on GCP had not been accessed by a single user in the past 90 days. These reports were safely archived.
• BigQuery Compute Cost Reduction: Archiving stale reports directly disabled their associated background scheduled queries and scheduled data extract refreshes. This saved terabytes of query scanning volume daily, lowering GCP operational expenditure.
• Security & Compliance Auditing: Enabled data platform administrators to monitor in real-time if dashboards containing sensitive financial metrics were shared externally beyond the @astrapay.com domain.