Dubai Basketball¶

Overview¶

This pipeline tracks SERP visibility, publisher data, and sentiment analysis for Dubai Basketball. It ingests data via App Scripts into BigQuery, transforms it using Scheduled Queries, and visualizes KPIs in Looker Studio.

Pipeline Status

Schedule: Daily (Various times, see Triggers)
Owner: Data Team
Primary Source: Google App Scripts & APIs
Destination: BigQuery (dubai-basketball-472511)

Architecture¶

        flowchart TD
    subgraph Sources
        A[SERP APIs]
        B[Publisher APIs]
        C[Search Volume]
    end
    
    subgraph Ingestion
        D[Google App Scripts]
    end
    
    subgraph Warehouse
        E[(BigQuery: 'test')]
        F[(BigQuery: 'main')]
    end
    
    subgraph Visualization
        G[Looker Studio: Main]
        H[Looker Studio: Sentiment]
    end
    
    A --> D
    B --> D
    C --> D
    D --> E
    E --> F
    F --> G
    F --> H
    
    style D fill:#ffecb3
    style F fill:#c8e6c9

Quick Links¶

Resource	Link
App Scripts	Google Script Project
BigQuery	GCP Console
Main Dashboard	Looker Studio Report
Sentiment Dash	Looker Studio Report

Data Pipeline Layers¶

1. Extract Layer (App Scripts)¶

Raw data ingestion from external APIs.

Script Name	Target Dataset	Target Table	Frequency
SERP Data	`test`	`dubai_visibility`	Daily
MENA SERP Data	`test`	`dubai_visibility`	Daily
Publishers Data	`test`	`publishers_site_data2`	Daily
MENA Pub Data	`test`	`publishers_site_data2`	Daily
Search Volume	`main`	`search-volume`	Monthly

Note

There are other scripts in the AppScript folder used for backfilling data. Only use these manually when required.

2. Transform Layer (BigQuery Scheduled Queries)¶

Data cleaning and aggregation logic.

Query Name	Dataset	Output Table	Frequency
`Visibility_LS`	`main`	`dubai_visibility_ls`	Daily
`Dubai_sentiment`	`main`	`sentiment tables`	Daily
`bubble_sv_growth`	`main`	`Playstation_Positions_Master`	Daily
`bubble_sentiment_growth_char`	`main`	`Playstation_Positions_Master`	Daily

Visualization Layer¶

Dashboard Tables & Charts¶

Table Name	Charts	Dataset Source	Notes
`dubai_visibility_ls`	2+2	`master`	Replaced old tables
`publishers_sentiment_analysis2`	4	`search_volumes`	Monthly update
`dubai_sentiment_analysis`	1	`master`	—
`bubble_growth_sentiment_analysis`	6	`master`	—
`reddit_basketball_posts`	2	`master`	Weekly update

Key Filters¶

-- Logic applied in Looker Studio
CASE 
    WHEN Filter_Name = 'rm_classiclink' THEN Exclude WHERE SERPFeature = 'classicLink'
    WHEN Filter_Name = 'Playstation Sites Only' THEN Include WHERE Site CONTAINS 'playstation.com'
    WHEN Filter_Name = 'Competitors Only' THEN Exclude WHERE Site CONTAINS 'playstation.com'
END

Monitoring & Triggers¶

Competitor Data Triggers¶

Dataset	Table	Trigger Time (IST)
`test`	`mainPAA`	10:00 AM
`test`	`mainPosPublishers`	10:00 AM
`test`	`mainVisMENA`	09:00 AM

Warning

Troubleshooting Missing Data Check: If you have 101 data for all positions and the daily API date check shows tracking was not completed for yesterday, please visit the BreakFix tab in the main spreadsheet immediately.