| --- |
| title: Sabian Brand Sentiment Dashboard |
| emoji: 🥁 |
| colorFrom: red |
| colorTo: yellow |
| sdk: streamlit |
| sdk_version: "1.50.0" |
| app_file: app.py |
| pinned: false |
| --- |
| |
|
|
| # Musora Sentiment Analysis Dashboard |
|
|
| A Streamlit dashboard for visualising sentiment analysis results from **social media comments** (Facebook, Instagram, YouTube, Twitter) and the **Musora internal app** across brands (Drumeo, Pianote, Guitareo, Singeo, Musora). |
|
|
| --- |
|
|
| ## Table of Contents |
|
|
| 1. [Project Structure](#project-structure) |
| 2. [How Data Flows](#how-data-flows) |
| 3. [Data Loading Strategy](#data-loading-strategy) |
| 4. [Pages](#pages) |
| 5. [Global Filters & Session State](#global-filters--session-state) |
| 6. [Snowflake Queries](#snowflake-queries) |
| 7. [Adding or Changing Things](#adding-or-changing-things) |
| 8. [Running the App](#running-the-app) |
| 9. [Configuration Reference](#configuration-reference) |
|
|
| --- |
|
|
| ## Project Structure |
|
|
| ``` |
| visualization/ |
| ├── app.py # Entry point — routing, sidebar, session state |
| ├── config/ |
| │ └── viz_config.json # Colors, query strings, dashboard settings |
| ├── data/ |
| │ └── data_loader.py # All Snowflake queries and caching logic |
| ├── utils/ |
| │ ├── data_processor.py # Pandas aggregations (intent dist, content summary, etc.) |
| │ └── metrics.py # KPI calculations (sentiment score, urgency, etc.) |
| ├── components/ |
| │ ├── dashboard.py # Dashboard page renderer |
| │ ├── sentiment_analysis.py # Sentiment Analysis page renderer |
| │ └── reply_required.py # Reply Required page renderer |
| ├── visualizations/ |
| │ ├── sentiment_charts.py # Plotly sentiment chart functions |
| │ ├── distribution_charts.py # Plotly distribution / heatmap / scatter functions |
| │ ├── demographic_charts.py # Plotly demographic chart functions |
| │ └── content_cards.py # Streamlit card components (comment cards, content cards) |
| ├── agents/ |
| │ └── content_summary_agent.py # AI analysis agent (OpenAI) for comment summarisation |
| ├── img/ |
| │ └── musora.png # Sidebar logo |
| └── SnowFlakeConnection.py # Snowflake connection wrapper (Snowpark session) |
| ``` |
|
|
| --- |
|
|
| ## How Data Flows |
|
|
| ``` |
| Snowflake |
| │ |
| ▼ |
| data_loader.py ← Three separate loading modes (see below) |
| │ |
| ├── load_dashboard_data() ──► st.session_state['dashboard_df'] |
| │ └─► app.py sidebar (filter options, counts) |
| │ └─► dashboard.py (all charts) |
| │ |
| ├── load_sa_data() ──► st.session_state['sa_contents'] |
| │ (on-demand, button) st.session_state['sa_comments'] |
| │ └─► sentiment_analysis.py |
| │ |
| └── load_reply_required_data() ► st.session_state['rr_df'] |
| (on-demand, button) └─► reply_required.py |
| ``` |
|
|
| **Key principle:** Data is loaded as little as possible, as late as possible. |
|
|
| - The **Dashboard** uses a lightweight query (no text columns, no content join) cached for 24 hours. |
| - The **Sentiment Analysis** and **Reply Required** pages never load data automatically — they wait for the user to click **Fetch Data**. |
| - All data is stored in `st.session_state` so page navigation and widget interactions do not re-trigger Snowflake queries. |
|
|
| --- |
|
|
| ## Data Loading Strategy |
|
|
| All loading logic lives in **`data/data_loader.py`** (`SentimentDataLoader` class). |
| |
| ### `load_dashboard_data()` |
| - Uses `dashboard_query` from `viz_config.json`. |
| - Fetches only: `comment_sk, content_sk, platform, brand, sentiment_polarity, intent, requires_reply, detected_language, comment_timestamp, processed_at, author_id`. |
| - No text columns, no `DIM_CONTENT` join — significantly faster than the full query. |
| - Also merges demographics data if `demographics_query` is configured. |
| - Cached for **24 hours** (`@st.cache_data(ttl=86400)`). |
| - Called once by `app.py` at startup; result stored in `st.session_state['dashboard_df']`. |
| |
| ### `load_sa_data(platform, brand, top_n, min_comments, sort_by, sentiments, intents, date_range)` |
| - Runs **two** sequential Snowflake queries: |
| 1. **Content aggregation** — groups by `content_sk`, counts per sentiment, computes severity score, returns top N. |
| 2. **Sampled comments** — for the top N `content_sk`s only, fetches up to 50 comments per sentiment group per content (negative, positive, other), using Snowflake `QUALIFY ROW_NUMBER()`. `display_text` is computed in SQL (`CASE WHEN IS_ENGLISH = FALSE AND TRANSLATED_TEXT IS NOT NULL THEN TRANSLATED_TEXT ELSE ORIGINAL_TEXT END`). |
| - Returns a tuple `(contents_df, comments_df)`. |
| - Cached for **24 hours**. |
| - Called only when the user clicks **Fetch Data** on the Sentiment Analysis page. |
|
|
| ### `load_reply_required_data(platforms, brands, date_range)` |
| - Runs a single query filtering `REQUIRES_REPLY = TRUE`. |
| - Dynamically includes/excludes the social media table and musora table based on selected platforms. |
| - `display_text` computed in SQL. |
| - Cached for **24 hours**. |
| - Called only when the user clicks **Fetch Data** on the Reply Required page. |
|
|
| ### Important: SQL Column Qualification |
| Both the social media table (`COMMENT_SENTIMENT_FEATURES`) and the content dimension table (`DIM_CONTENT`) share column names. Any `WHERE` clause inside a query that joins these two tables **must** use the table alias prefix (e.g. `s.PLATFORM`, `s.COMMENT_TIMESTAMP`, `s.CHANNEL_NAME`) to avoid Snowflake `ambiguous column name` errors. The musora table (`MUSORA_COMMENT_SENTIMENT_FEATURES`) has no joins so unqualified column names are fine there. |
|
|
| --- |
|
|
| ## Pages |
|
|
| ### Dashboard (`components/dashboard.py`) |
|
|
| **Receives:** `filtered_df` — the lightweight dashboard dataframe (after optional global filter applied by `app.py`). |
|
|
| **Does not need:** text, translations, content URLs. All charts work purely on aggregated columns (sentiment_polarity, brand, platform, intent, requires_reply, comment_timestamp). |
| |
| **Key sections:** |
| - Summary stats + health indicator |
| - Sentiment distribution (pie + gauge) |
| - Sentiment by brand and platform (stacked + percentage bar charts) |
| - Intent analysis |
| - Brand-Platform heatmap |
| - Reply requirements + urgency breakdown |
| - Demographics (age, timezone, experience level) — only rendered if `author_id` is present and demographics were merged |
|
|
| **To add a new chart:** create the chart function in `visualizations/` and call it from `render_dashboard()`. The function receives `filtered_df`. |
|
|
| --- |
|
|
| ### Sentiment Analysis (`components/sentiment_analysis.py`) |
| |
| **Receives:** `data_loader` instance only (no dataframe). |
|
|
| **Flow:** |
| 1. Reads `st.session_state['dashboard_df']` for filter option lists (platforms, brands, sentiments, intents). |
| 2. Pre-populates platform/brand dropdowns from `st.session_state['global_filters']`. |
| 3. Shows filter controls (platform, brand, sentiment, intent, top_n, min_comments, sort_by). |
| 4. On **Fetch Data** click: calls `data_loader.load_sa_data(...)` and stores results in `st.session_state['sa_contents']` and `['sa_comments']`. |
| 5. Renders content cards, per-content sentiment + intent charts, AI analysis buttons, and sampled comment expanders. |
| |
| **Pagination:** `st.session_state['sentiment_page']` (5 contents per page). Reset on new fetch. |
| |
| **Comments:** Sampled (up to 50 negative + 50 positive + 50 neutral per content). These are already in memory after the fetch — no extra query is needed when the user expands a comment section. |
| |
| **AI Analysis:** Uses `ContentSummaryAgent` (see `agents/`). Results cached in `st.session_state['content_summaries']`. |
| |
| --- |
| |
| ### Reply Required (`components/reply_required.py`) |
|
|
| **Receives:** `data_loader` instance only. |
|
|
| **Flow:** |
| 1. Reads `st.session_state['dashboard_df']` for filter option lists. |
| 2. Pre-populates platform, brand, and date from `st.session_state['global_filters']`. |
| 3. On **Fetch Data** click: calls `data_loader.load_reply_required_data(...)` and stores result in `st.session_state['rr_df']`. |
| 4. Shows urgency breakdown, in-page view filters (priority, platform, brand, intent — applied in Python, no new query), paginated comment cards, and a "Reply by Content" summary. |
|
|
| **Pagination:** `st.session_state['reply_page']` (10 comments per page). Reset on new fetch. |
|
|
| --- |
|
|
| ## Global Filters & Session State |
|
|
| Global filters live in the sidebar (`app.py`) and are stored in `st.session_state['global_filters']` as a dict: |
|
|
| ```python |
| { |
| 'platforms': ['facebook', 'instagram'], # list or [] |
| 'brands': ['drumeo'], |
| 'sentiments': [], |
| 'date_range': (date(2025, 1, 1), date(2025, 12, 31)), # or None |
| } |
| ``` |
|
|
| - **Dashboard:** `app.py` applies global filters to `dashboard_df` using `data_loader.apply_filters()` and passes the result to `render_dashboard()`. |
| - **Sentiment Analysis / Reply Required:** global filters are used to pre-populate their own filter widgets. The actual Snowflake query uses those values when the user clicks Fetch. The pages do **not** receive a pre-filtered dataframe. |
|
|
| ### Full session state key reference |
|
|
| | Key | Set by | Used by | |
| |-----|--------|---------| |
| | `dashboard_df` | `app.py` on startup | sidebar (filter options), dashboard, SA + RR (filter option lists) | |
| | `global_filters` | sidebar "Apply Filters" button | app.py (dashboard filter), SA + RR (pre-populate widgets) | |
| | `filters_applied` | sidebar buttons | app.py (whether to apply filters) | |
| | `sa_contents` | SA fetch button | SA page rendering | |
| | `sa_comments` | SA fetch button | SA page rendering | |
| | `sa_fetch_key` | SA fetch button | SA page (detect stale data) | |
| | `rr_df` | RR fetch button | RR page rendering | |
| | `rr_fetch_key` | RR fetch button | RR page (detect stale data) | |
| | `sentiment_page` | SA page / fetch | SA pagination | |
| | `reply_page` | RR page / fetch | RR pagination | |
| | `content_summaries` | AI analysis buttons | SA AI analysis display | |
|
|
| --- |
|
|
| ## Snowflake Queries |
|
|
| All query strings are either stored in `config/viz_config.json` (static queries) or built dynamically in `data/data_loader.py` (page-specific queries). |
|
|
| ### Static queries (in `viz_config.json`) |
| |
| | Key | Purpose | |
| |-----|---------| |
| | `query` | Full query with all columns (legacy, kept for compatibility) | |
| | `dashboard_query` | Lightweight query — no text, no DIM_CONTENT join | |
| | `demographics_query` | Joins `usora_users` with `preprocessed.users` to get age/timezone/experience | |
| |
| ### Dynamic queries (built in `data_loader.py`) |
|
|
| | Method | Description | |
| |--------|-------------| |
| | `_build_sa_content_query()` | Content aggregation for SA page; filters by platform + brand + date | |
| | `_build_sa_comments_query()` | Sampled comments for SA page; uses `QUALIFY ROW_NUMBER() <= 50` | |
| | `_build_rr_query()` | Reply-required comments; filters by platform/brand/date; conditionally includes social media and/or musora table | |
|
|
| ### Data source tables |
|
|
| | Table | Platform | Notes | |
| |-------|----------|-------| |
| | `SOCIAL_MEDIA_DB.ML_FEATURES.COMMENT_SENTIMENT_FEATURES` | facebook, instagram, youtube, twitter | Needs `LEFT JOIN SOCIAL_MEDIA_DB.CORE.DIM_CONTENT` for `PERMALINK_URL` | |
| | `SOCIAL_MEDIA_DB.ML_FEATURES.MUSORA_COMMENT_SENTIMENT_FEATURES` | musora_app | Has `PERMALINK_URL` and `THUMBNAIL_URL` natively; platform stored as `'musora'`, mapped to `'musora_app'` in queries | |
|
|
| --- |
|
|
| ## Adding or Changing Things |
|
|
| ### Add a new chart to the Dashboard |
| 1. Write the chart function in the appropriate `visualizations/` file. |
| 2. Call it from `render_dashboard()` in `components/dashboard.py`, passing `filtered_df`. |
| 3. The chart function receives a lightweight df — it has no text columns but has all the columns listed in `dashboard_query`. |
|
|
| ### Add a new filter to the Dashboard sidebar |
| 1. Add the widget in `app.py` under the "Global Filters" section. |
| 2. Store the selected value in the `global_filters` dict under `st.session_state`. |
| 3. Pass it to `data_loader.apply_filters()`. |
|
|
| ### Change what the Sentiment Analysis page queries |
| - Edit `_build_sa_content_query()` and/or `_build_sa_comments_query()` in `data_loader.py`. |
| - If you add new columns to the content aggregation result, also update `_process_sa_content_stats()` so they are available in `contents_df`. |
| - If you add new columns to the comments result, update `_process_sa_comments()`. |
|
|
| ### Change what the Reply Required page queries |
| - Edit `_build_rr_query()` in `data_loader.py`. |
| - Remember: all column references inside the social media block (which has a `JOIN`) must be prefixed with `s.` to avoid Snowflake ambiguity errors. |
|
|
| ### Change the cache duration |
| - `@st.cache_data(ttl=86400)` is set on `load_dashboard_data`, `_fetch_sa_data`, `_fetch_rr_data`, and `load_demographics_data`. |
| - Change `86400` (seconds) to the desired TTL, or set `ttl=None` for no expiry. |
| - Users can always force a refresh with the "Reload Data" button in the sidebar (which calls `st.cache_data.clear()` and deletes `st.session_state['dashboard_df']`). |
|
|
| ### Add a new page |
| 1. Create `components/new_page.py` with a `render_new_page(data_loader)` function. |
| 2. Import and add a radio option in `app.py`. |
| 3. If the page needs its own Snowflake data, add a `load_new_page_data()` method to `SentimentDataLoader` following the same pattern as `load_sa_data`. |
|
|
| ### Add a new column to the Dashboard query |
| - Edit `dashboard_query` in `config/viz_config.json`. |
| - Both UNION branches must select the same columns in the same order. |
| - `_process_dashboard_dataframe()` in `data_loader.py` handles basic type casting — add processing there if needed. |
|
|
| --- |
|
|
| ## Running the App |
|
|
| ```bash |
| # From the project root |
| streamlit run visualization/app.py |
| ``` |
|
|
| **Required environment variables** (in `.env` at project root): |
|
|
| ``` |
| SNOWFLAKE_USER |
| SNOWFLAKE_PASSWORD |
| SNOWFLAKE_ACCOUNT |
| SNOWFLAKE_ROLE |
| SNOWFLAKE_DATABASE |
| SNOWFLAKE_WAREHOUSE |
| SNOWFLAKE_SCHEMA |
| ``` |
|
|
| --- |
|
|
| ## Configuration Reference |
|
|
| `config/viz_config.json` controls: |
|
|
| | Section | What it configures | |
| |---------|-------------------| |
| | `color_schemes.sentiment_polarity` | Hex colors for each sentiment level | |
| | `color_schemes.intent` | Hex colors for each intent label | |
| | `color_schemes.platform` | Hex colors for each platform | |
| | `color_schemes.brand` | Hex colors for each brand | |
| | `sentiment_order` | Display order for sentiment categories in charts | |
| | `intent_order` | Display order for intent categories | |
| | `negative_sentiments` | Which sentiment values count as "negative" | |
| | `dashboard.default_date_range_days` | Default date filter window (days) | |
| | `dashboard.max_comments_display` | Max comments shown per pagination page | |
| | `dashboard.chart_height` | Default Plotly chart height | |
| | `dashboard.top_n_contents` | Default top-N for content ranking | |
| | `snowflake.query` | Full query (legacy, all columns) | |
| | `snowflake.dashboard_query` | Lightweight dashboard query (no text columns) | |
| | `snowflake.demographics_query` | Demographics join query | |
| | `demographics.age_groups` | Age bucket definitions (label → [min, max]) | |
| | `demographics.experience_groups` | Experience bucket definitions | |
| | `demographics.top_timezones_count` | How many timezones to show in the geographic chart | |
