Joining classification_results table to other tables support request

Data & reporting
, ,
1

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

Please support

2

I think this is dependant on the target_type column. For instance, if it’s got a target_type of ‘post’ then the target_id is a post_id

1 Like
3

Thanks, that helps a lot.

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.

4

I’m not sure comments are processed by AI sentiment. :thinking: 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
5

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?

6

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: