visualization/README.md

# 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 |