In a discussion about joining the classification_results table for AI sentiment analysis dashboards, SStrong seeks guidance on how to link target_id to other tables. JammyDodger clarifies that target_id corresponds to the id of the entity specified in target_type—e.g., post maps to posts.id. Michael12 confirms this logic and asks about other types like ‘comment’, but JammyDodger notes sentiment analysis likely applies only to posts currently, suggesting running SELECT target_type FROM classification_results GROUP BY 1 to verify. SStrong confirms only ‘post’ appears and asks whether sentiment scores should be aggregated per topic (since a topic may contain multiple posts). JammyDodger explains each post gets its own sentiment record, possibly per model, and suggests averaging sentiment scores per topic for dashboards—referencing existing examples like Overall Sentiment and Post Emotion. Finally, SStrong reports the overall sentiment query returns no results despite frontend visibility, to which JammyDodger suggests checking if the date range parameters were updated from defaults.
I have a request to create using data explorer dashboards at Category level with certain key metrics that will be automated via automation every month, going to Category moderators so that they can be aware of performance, make business decisions off the back of it.
Sentiment analysis - this has been requested at Category level. I have looked in the classification_results table to understand how to join this, however there’s limited columns and just one called target_id but no idea what that refers to and I only see it in ai related tables
So just to check—if target_type is ‘post’, then I should join classification_results.target_id to posts.id, right?
And if the target_type is something else, like ‘comment’, then I’d join it to the comments table instead? Just want to make sure I’m joining to the right table based on the type.
I’m not sure comments are processed by AI sentiment. Is this a target_type you’re seeing in your data?
I think it may currently be post only, but with a format that allows it to be expanded to other entities in the future. My data set is limited though, so if you could run this query and see what different types you have that would be very useful:
SELECT target_type
FROM classification_results
GROUP BY 1
I can only see post as a target_type. Am I correct in thinking that a post is a multiple of types in it’s on right as well. For example, one topic can have 10 posts in total. So does that mean that the sentiment analysis in this case would be applied 10 times, once to each post of a topic? Meaning if the ask was to create a dashboard which shows the sentiment score per topic by category, another step would be to then understand how many of the posts of a topic even have had sentiment analysis applied, then create an average?
The sentiment analysis is applied at the post level, so each post would have its own record in the classification_results table. Each post would also have a separate record for every model applied to it as well, so depending on which results you’re interested in you’d want to filter out those specific ones for that model.
I think this may depend on which results you’re interested in. The positive/negative sentiments may lend themselves better to that concept. The emotion ones might be better laid out as separate columns per topic.
But you may be able to get some ideas from the existing ones:
Is this a