This topic explains two advanced data reporting queries for analyzing community sentiment and emotions using the Discourse AI plugin’s sentiment analysis capabilities.
Required user level: Administrator
The Discourse AI plugin includes sentiment analysis capabilities that can help you gain deeper understanding of the emotional tone of discussions across your community. This topic discusses two detailed example data explorer queries that leverage these AI capabilities to provide insights into Discourse communities.
- AI Sentiment Per Category and Trust Level Total: A time series analysis that tracks sentiment trends by week within specific categories and trust levels
- AI Emotion Outlier Topics: Identifies discussion topics that trigger significant emotional responses from a Discourse site.
Prerequisites
To use these reports, you need:
- Discourse AI plugin installed and enabled: The Discourse AI plugin must be installed on your instance
- Sentiment analysis enabled: The Sentiment Analysis module must be configured and active
- Data Explorer plugin: Required to run these SQL queries
- Historical sentiment data: Sufficient sentiment-analyzed posts for meaningful results (may require a backfill operation)
AI sentiment models and how they work
Before diving into the reports, it’s helpful to understand what sentiment models are analyzing in your community’s posts:
- Overall sentiment: The cardiffnlp/twitter-roberta-base-sentiment-latest model classifies posts as positive, negative, or neutral
- Emotion detection: The SamLowe/roberta-base-go_emotions model identifies specific emotions in posts, such as joy, sadness, anger, etc.
These models analyze the text of each post and store their classifications in your database, which can then be queried by the Data Explorer plugin.
AI Sentiment Per Category and Trust Level Total report
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-12-31
-- category_id :category_id = 6
-- int :min_trust_level = 0
-- boolean :exclude_staff = false
-- Create a temporary result set that aggregates sentiment metrics by week for the specified category
WITH sentiment_counts AS (
SELECT
c.id as category_id,
c.name as category_name,
-- Group posts by week for time-series analysis
DATE_TRUNC('week', p.created_at) as week_starting,
EXTRACT(YEAR FROM p.created_at) as year,
EXTRACT(WEEK FROM p.created_at) as week_number,
-- Count posts with positive sentiment (threshold > 0.6)
COUNT(CASE WHEN (cr.classification::jsonb->'positive')::float > 0.6 THEN 1
ELSE NULL END) as positive_count,
-- Count posts with negative sentiment (threshold > 0.6)
COUNT(CASE WHEN (cr.classification::jsonb->'negative')::float > 0.6 THEN 1
ELSE NULL END) as negative_count,
-- Count posts with neutral sentiment (both positive and negative <= 0.6)
COUNT(CASE WHEN (cr.classification::jsonb->'positive')::float <= 0.6
AND (cr.classification::jsonb->'negative')::float <= 0.6 THEN 1
ELSE NULL END) as neutral_count,
-- Total number of posts with sentiment analysis
COUNT(*) as total_classifications
FROM classification_results cr
-- Join post data to get creation dates and metadata
JOIN posts p ON p.id = cr.target_id AND cr.target_type = 'Post'
-- Join topic data to filter by category
JOIN topics t ON t.id = p.topic_id
-- Join user data to filter by trust level
JOIN users u ON u.id = p.user_id
-- Join category data to get category name
JOIN categories c ON c.id = t.category_id
WHERE
-- Only include sentiment results from this specific model
cr.model_used = 'cardiffnlp/twitter-roberta-base-sentiment-latest'
-- Only include regular topics (no PMs, etc.)
AND t.archetype = 'regular'
-- Exclude system posts
AND p.user_id > 0
-- Filter by the selected category
AND c.id = :category_id
-- Filter by minimum trust level
AND u.trust_level >= :min_trust_level
-- Exclude staff users if the parameter is checked
AND (:exclude_staff = false OR (u.admin = false AND u.moderator = false))
-- Filter by date range
AND p.created_at BETWEEN :start_date AND :end_date
-- Group by week and category
GROUP BY c.id, c.name, week_starting, year, week_number
)
-- Format the final results for display
SELECT
category_id,
category_name,
-- Convert to Date for cleaner display
week_starting::Date,
-- Format as ISO week notation (YYYY-WXX)
year || '-W' || LPAD(week_number::text, 2, '0') as year_week,
-- Calculate net sentiment (positive minus negative)
positive_count - negative_count as sentiment_balance,
positive_count,
negative_count,
neutral_count,
-- Calculate percentage of positive posts (rounded to 2 decimal places)
ROUND(
(positive_count::float / NULLIF(total_classifications, 0) * 100)::numeric,
2
) as positive_percentage
FROM sentiment_counts
-- Sort chronologically to show sentiment trends over time
ORDER BY week_starting ASC
This report provides a weekly analysis of sentiment trends within a specific category, showing:
- Positive, negative, and neutral post counts for each week
- A sentiment balance calculation (positive posts minus negative posts)
- The percentage of positive posts relative to total analyzed posts
- Filtering by user trust level and option to exclude staff posts
This report is valuable for:
- Tracking community sentiment trends over time in specific categories
- Identifying fluctuations in community mood that might correlate with specific events or changes
- Comparing sentiment across different user segments (by trust level)
- Measuring the impact of moderation interventions on overall community sentiment
Parameters
The query accepts several parameters to customize your analysis:
- Date range: Set start and end dates for your analysis period
- Category: Select which category to analyze
- Minimum trust level: Filter to only include posts from users at or above a specific trust level
- Exclude staff: Option to remove staff posts from the analysis (to focus on regular community members)
Results
The results are presented in a table with each row representing a week of data:
- Category information: ID and name of the analyzed category
- Time periods: Week start date and ISO week notation (YYYY-WXX)
- Sentiment metrics:
- Sentiment balance: The difference between positive and negative posts (positive value indicates overall positive sentiment)
- Positive/negative/neutral counts: The number of posts in each sentiment category
- Positive percentage: The percentage of posts classified as positive
Example Results
| category_name | week_starting | year_week | sentiment_balance | positive_count | negative_count | neutral_count | positive_percentage |
|---|---|---|---|---|---|---|---|
| Product Discussion | 2025-01-06 | 2025-W01 | -8 | 24 | 32 | 145 | 11.94 |
| Product Discussion | 2025-01-13 | 2025-W02 | -11 | 30 | 41 | 210 | 10.68 |
| Product Discussion | 2025-01-20 | 2025-W03 | -9 | 28 | 37 | 220 | 9.82 |
| Product Discussion | 2025-01-27 | 2025-W04 | -13 | 33 | 46 | 260 | 9.74 |
| Product Discussion | 2025-02-03 | 2025-W05 | -15 | 22 | 37 | 180 | 9.21 |
| Product Discussion | 2025-02-10 | 2025-W06 | -6 | 37 | 43 | 195 | 13.45 |
AI Emotion Outlier Topics report
-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-12-31
-- category_id :category_id = 6
-- int :min_trust_level = 1
-- int :emotion_threshold = 10
-- First, create a Common Table Expression (CTE) that aggregates emotional reactions by topic
WITH topic_emotions AS (
SELECT
topics.id AS topic_id, -- Store the topic ID for later joining/filtering
topics.title, -- Include topic title for readable results
topics.created_at::date AS topic_date, -- Store topic creation date
-- For each emotion type, count posts where that emotion exceeds confidence threshold of 0.1
-- The classification_results table stores emotion scores as JSON values
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'admiration')::float > 0.1) AS admiration_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'amusement')::float > 0.1) AS amusement_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'anger')::float > 0.1) AS anger_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'annoyance')::float > 0.1) AS annoyance_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'approval')::float > 0.1) AS approval_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'caring')::float > 0.1) AS caring_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'confusion')::float > 0.1) AS confusion_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'curiosity')::float > 0.1) AS curiosity_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'desire')::float > 0.1) AS desire_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'disappointment')::float > 0.1) AS disappointment_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'disapproval')::float > 0.1) AS disapproval_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'disgust')::float > 0.1) AS disgust_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'embarrassment')::float > 0.1) AS embarrassment_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'excitement')::float > 0.1) AS excitement_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'fear')::float > 0.1) AS fear_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'gratitude')::float > 0.1) AS gratitude_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'grief')::float > 0.1) AS grief_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'joy')::float > 0.1) AS joy_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'love')::float > 0.1) AS love_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'nervousness')::float > 0.1) AS nervousness_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'neutral')::float > 0.1) AS neutral_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'optimism')::float > 0.1) AS optimism_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'pride')::float > 0.1) AS pride_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'realization')::float > 0.1) AS realization_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'relief')::float > 0.1) AS relief_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'remorse')::float > 0.1) AS remorse_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'sadness')::float > 0.1) AS sadness_count,
COUNT(*) FILTER (WHERE (classification_results.classification::jsonb->'surprise')::float > 0.1) AS surprise_count,
-- Calculate total emotional reactions for ranking purposes
COUNT(*) AS total_emotional_reactions
FROM
classification_results
-- Join to posts table to get post metadata and filter deleted posts
INNER JOIN
posts ON posts.id = classification_results.target_id AND
posts.deleted_at IS NULL -- Exclude deleted posts
-- Join to topics table to get topic metadata and filter by topic type/status
INNER JOIN
topics ON topics.id = posts.topic_id AND
topics.archetype = 'regular' AND -- Only include standard topics (not PMs or system messages)
topics.deleted_at IS NULL -- Exclude deleted topics
-- Join to users table to get user trust level for filtering
INNER JOIN
users ON users.id = posts.user_id
WHERE
-- Only include emotion classifications for posts (not other content types)
classification_results.target_type = 'Post' AND
-- Only use results from this specific emotion detection model
classification_results.model_used = 'SamLowe/roberta-base-go_emotions' AND
-- Filter by date range using parameterized values
posts.created_at BETWEEN :start_date AND :end_date AND
-- Filter by the specified category
(topics.category_id = :category_id) AND
-- Only include posts from users with sufficient trust level
(users.trust_level >= :min_trust_level)
-- Group all counts by topic
GROUP BY
topics.id, topics.title, topics.created_at::date
)
-- Main query that formats and filters the aggregated data from the CTE
SELECT
topic_id, -- Display topic ID (will be rendered as a link in Discourse)
--title, -- Display topic title
topic_date, -- Display topic creation date
total_emotional_reactions, -- Show total count of emotions detected
-- Convert array of significant emotions to a formatted string
-- Only emotions that exceed threshold are included, others become NULL and are omitted
-- Each emotion is formatted as "EmotionName(count)"
ARRAY_TO_STRING(ARRAY[
CASE WHEN admiration_count >= :emotion_threshold THEN 'Admiration(' || admiration_count || ')' ELSE NULL END,
CASE WHEN amusement_count >= :emotion_threshold THEN 'Amusement(' || amusement_count || ')' ELSE NULL END,
CASE WHEN anger_count >= :emotion_threshold THEN 'Anger(' || anger_count || ')' ELSE NULL END,
CASE WHEN annoyance_count >= :emotion_threshold THEN 'Annoyance(' || annoyance_count || ')' ELSE NULL END,
CASE WHEN approval_count >= :emotion_threshold THEN 'Approval(' || approval_count || ')' ELSE NULL END,
CASE WHEN caring_count >= :emotion_threshold THEN 'Caring(' || caring_count || ')' ELSE NULL END,
CASE WHEN confusion_count >= :emotion_threshold THEN 'Confusion(' || confusion_count || ')' ELSE NULL END,
CASE WHEN curiosity_count >= :emotion_threshold THEN 'Curiosity(' || curiosity_count || ')' ELSE NULL END,
CASE WHEN desire_count >= :emotion_threshold THEN 'Desire(' || desire_count || ')' ELSE NULL END,
CASE WHEN disappointment_count >= :emotion_threshold THEN 'Disappointment(' || disappointment_count || ')' ELSE NULL END,
CASE WHEN disapproval_count >= :emotion_threshold THEN 'Disapproval(' || disapproval_count || ')' ELSE NULL END,
CASE WHEN disgust_count >= :emotion_threshold THEN 'Disgust(' || disgust_count || ')' ELSE NULL END,
CASE WHEN embarrassment_count >= :emotion_threshold THEN 'Embarrassment(' || embarrassment_count || ')' ELSE NULL END,
CASE WHEN excitement_count >= :emotion_threshold THEN 'Excitement(' || excitement_count || ')' ELSE NULL END,
CASE WHEN fear_count >= :emotion_threshold THEN 'Fear(' || fear_count || ')' ELSE NULL END,
CASE WHEN gratitude_count >= :emotion_threshold THEN 'Gratitude(' || gratitude_count || ')' ELSE NULL END,
CASE WHEN grief_count >= :emotion_threshold THEN 'Grief(' || grief_count || ')' ELSE NULL END,
CASE WHEN joy_count >= :emotion_threshold THEN 'Joy(' || joy_count || ')' ELSE NULL END,
CASE WHEN love_count >= :emotion_threshold THEN 'Love(' || love_count || ')' ELSE NULL END,
CASE WHEN nervousness_count >= :emotion_threshold THEN 'Nervousness(' || nervousness_count || ')' ELSE NULL END,
CASE WHEN optimism_count >= :emotion_threshold THEN 'Optimism(' || optimism_count || ')' ELSE NULL END,
CASE WHEN pride_count >= :emotion_threshold THEN 'Pride(' || pride_count || ')' ELSE NULL END,
CASE WHEN realization_count >= :emotion_threshold THEN 'Realization(' || realization_count || ')' ELSE NULL END,
CASE WHEN relief_count >= :emotion_threshold THEN 'Relief(' || relief_count || ')' ELSE NULL END,
CASE WHEN remorse_count >= :emotion_threshold THEN 'Remorse(' || remorse_count || ')' ELSE NULL END,
CASE WHEN sadness_count >= :emotion_threshold THEN 'Sadness(' || sadness_count || ')' ELSE NULL END,
CASE WHEN surprise_count >= :emotion_threshold THEN 'Surprise(' || surprise_count || ')' ELSE NULL END
], ', ', '') AS significant_emotions -- Join with comma delimiters, empty string if no delimiter needed
FROM
topic_emotions
WHERE
-- Only include topics that have at least one emotion exceeding the threshold
-- This identifies topics with significant emotional impact
(
admiration_count >= :emotion_threshold OR
amusement_count >= :emotion_threshold OR
anger_count >= :emotion_threshold OR
annoyance_count >= :emotion_threshold OR
approval_count >= :emotion_threshold OR
caring_count >= :emotion_threshold OR
confusion_count >= :emotion_threshold OR
curiosity_count >= :emotion_threshold OR
desire_count >= :emotion_threshold OR
disappointment_count >= :emotion_threshold OR
disapproval_count >= :emotion_threshold OR
disgust_count >= :emotion_threshold OR
embarrassment_count >= :emotion_threshold OR
excitement_count >= :emotion_threshold OR
fear_count >= :emotion_threshold OR
gratitude_count >= :emotion_threshold OR
grief_count >= :emotion_threshold OR
joy_count >= :emotion_threshold OR
love_count >= :emotion_threshold OR
nervousness_count >= :emotion_threshold OR
optimism_count >= :emotion_threshold OR
pride_count >= :emotion_threshold OR
realization_count >= :emotion_threshold OR
relief_count >= :emotion_threshold OR
remorse_count >= :emotion_threshold OR
sadness_count >= :emotion_threshold OR
surprise_count >= :emotion_threshold
)
-- Sort results by most emotional reactions first
ORDER BY
total_emotional_reactions DESC
This report identifies topics that have triggered significant emotional responses in your community, based on:
- A count of each type of emotion detected in posts within the topic
- A configurable threshold to determine what constitutes a “significant” emotional response
- Filtering by category, date range, and user trust level
This report helps you:
- Identify potentially problematic discussions that are generating strong negative emotions
- Find highly engaging content that resonates emotionally with your community
- Detect topics that may need moderation attention before they escalate
- Discover content themes that trigger specific emotional responses
- Better understand what drives emotional engagement in your community
Parameters
The query accepts several parameters:
- Date range: Set start and end dates for your analysis period
- Category: Select which category to analyze
- Minimum trust level: Filter to only include posts from users at or above a specific trust level
- Emotion threshold: Set how many instances of an emotion are required to consider it significant
Results
The results show:
- Topic ID: Links directly to the topic (clickable in Data Explorer)
- Topic date: When the topic was created
- Total emotional reactions: The overall count of emotional reactions detected
- Significant emotions: A formatted list of emotions that exceeded your threshold, with their counts shown in parentheses
The emotions detected include a wide range: admiration, amusement, anger, annoyance, approval, caring, confusion, curiosity, desire, disappointment, disapproval, disgust, embarrassment, excitement, fear, gratitude, grief, joy, love, nervousness, neutral, optimism, pride, realization, relief, remorse, sadness, and surprise.
Example Results
| topic | topic_date | total_emotional_reactions | significant_emotions |
|---|---|---|---|
| Feature Request: Increased API Rate Limits | 2025-03-06 | 42 | Approval(15), Confusion(9), Curiosity(7), Gratitude(8) |
| Authentication Error with Third-Party Integration | 2025-01-07 | 33 | Curiosity(6), Gratitude(5), Disapproval(8), Frustration(9) |
| Best Practices for Configuration Settings | 2025-02-16 | 31 | Curiosity(9), Excitement(6), Gratitude(5), Optimism(5) |
| Troubleshooting Database Connection Issues | 2025-01-15 | 29 | Curiosity(7), Confusion(8), Disappointment(6), Frustration(5) |
| Critical Bug in Latest Beta Release | 2025-02-02 | 26 | Confusion(7), Concern(6), Disapproval(5), Urgency(6) |
Practical applications in community management
These reports can enhance your community management workflow in several ways:
- Early intervention: Identify emotionally charged topics that may need moderation before they become problematic
- Content planning: Use insights about what triggers positive emotions to inform your content strategy
- Measuring impact: Assess how policy changes, new features, or events affect community sentiment
- Targeted engagement: Focus staff attention on topics with strong emotional reactions that might benefit from official responses