Community Sentiment and Toxicity queries

Data & reporting
,
1

For those wondering how to view the classification results database for the Community Sentiment and Toxicity modules, this can be done using the Data Explorer plugin, and the classification_results table.

This is useful for seeing how the AI plugin is functioning on your site and classifying posts.

AI Sentiment

SELECT target_id as post_id,
model_used,
classification->'negative' as negative,
classification->'neutral' as neutral,
classification->'positive' as positive
from classification_results
WHERE model_used = 'sentiment'
order by id desc

09cb357d6c2799a50b88c9051c47f9529525bd9f_2_690x119

AI Emotion:

SELECT target_id as post_id,
model_used,
classification->'neutral' as neutral,
classification->'sadness' as sadness,
classification->'surprise' as surprise,
classification->'fear' as fear,
classification->'anger' as anger,
classification->'joy' as joy,
classification->'disgust' as disgust
from classification_results
WHERE model_used = 'emotion'
order by id desc

image

AI Toxicity:

SELECT target_id as post_id,
classification->'toxicity' as toxicity,
classification->'severe_toxicity' as severe_toxicity,
classification->'obscene' as obscene,
classification->'identity_attack' as identity_attack,
classification->'insult' as insult,
classification->'threat' as threat,
classification->'sexual_explicit' as sexual_explicit
From classification_results
WHERE classification_type = 'toxicity'
order by id desc

image

8 Likes
2

Is there a way to add a filter to this query to get the posts from a particular thread? Right now, it seems it is getting posts across the entire Community, which is nice, but also cumbersome to find a particular post/thread you are looking for, not to mention you will run into the row limit for this.

2 Likes
3

Hi @Samantha_O :wave:

Does this work for specific topics? you will have to specify the topic ID in the params.

-- [params]
-- int :topic_id = 

SELECT cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.topic_id = :topic_id
ORDER BY cr.id DESC
6 Likes
4 
-- [params]
-- topic_id :topic_id

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
ORDER BY p.id

Ahh you pipped me to it. :slight_smile:

But here’s one for a specific post as well just to not feel like a wasted reply:

-- [params]
-- topic_id :topic_id
-- int :post_number

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
  AND p.post_number = :post_number

The topic_id and post_number can both be found in the URL, so it’s quite user-friendly.

Topic_id:

https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4

Post_number:

https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4

5 Likes
5

I think this should target category id:

-- [params]
-- int :category_id = 

SELECT cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
JOIN topics t ON t.id = p.topic_id
WHERE cr.model_used = 'sentiment'
AND t.category_id = :category_id
ORDER BY cr.id DESC

category_id

5 Likes
6

Thank you both! When I run these, it doesn’t find older topics/topic ids. I wonder - does the sentiment analysis only happen on new posts?

edited for clarity

2 Likes
7

@JammyDodger would know better, but yes I think the sentiment analysis works on new posts. I think in order to get it to use the old posts would likely require some rails commands to process them through the sentiment model, and into the classification_results table (where the new posts’ analyses results are).

3 Likes
8

It works on new posts once it’s turned on. :+1: I’m not sure if there is a backfill option for it?

4 Likes
10

Only new posts from now. We can consider backfilling that information after we start reporting on the data and we validate it.

6 Likes
11

Would one of you (or another brilliant query-ist) be able to help me fine-tune this a bit? I would like to have params for dates and those dates would correspond to the post date. The end goal here would be to be able to look at changes over time while waiting to see if the dashboard can do that in the future :crossed_fingers:

12

Hi Samantha,

If you wanted to refine those queries to include start and end date parameters, you could use a query like this:

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

SELECT 
p.created_at,
cr.target_id as post_id,
cr.model_used,
cr.classification->'negative' as negative,
cr.classification->'neutral' as neutral,
cr.classification->'positive' as positive
FROM classification_results cr
JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
AND p.created_at BETWEEN :start_date AND :end_date
ORDER BY cr.id DESC

Example Results:

created_at post model_used negative neutral positive
2023-11-08T21:21:23.913Z post_id sentiment 58 38 2

The important part added here is joining the posts table with the classification_results table so that we can add:

AND p.created_at BETWEEN :start_date AND :end_date

To the WHERE statement to filter the posts by their created_at date.

Since the emotion and toxicity classification queries are similar, you could modify those queries with this addition to include start and end date parameters as well. :slightly_smiling_face:

4 Likes
13
4 Likes
14

Adding this one here as well:

2 Likes