Insight request: Vibe analysis

Proposed by: iSpeakNerd.eth

Problem description: understanding a guild/project sentiment is a useful tool for guild/project participants in managing the health of their community. Currently getting a feel for the “vibe” of the communication is a very qualitative, non-scientific process

Suggested solution: Produce a word cloud of the most commonly used words in messages communicated in a particular channel over a particular time period to see what words bubble up to the top + potentially run that text through some sort of “sentiment analysis” library and return the results of the analysis back to the user

 

 

select * from discord_message_sentiment

table contains discord_message_id, message_created_at, sentiment_score, updated_at (ETL update timestamp - audit field)

Can join the table with discord_messages using discord_message_id field to get message content

- you can use ’s logic as case statement in you query to categorize as positive/negative sentiment: > 0.5 = Positive, < -0.5 = Negative

 

—SQL for analysis/insight—

select date_trunc('week',s.message_created_at) message_week, m.channel_name, count(distinct case when s.sentiment_score > 0.5 then s.discord_message_id end) as positive_sentiment_count, count(distinct case when s.sentiment_score < -0.5 then s.discord_message_id end) as negative_sentiment_count, count (distinct s.discord_message_id) as total_messages, cast(count(distinct case when s.sentiment_score > 0.5 then s.discord_message_id end)::real/count (distinct s.discord_message_id)::real as decimal(10,2)) as positive_sentiment_pct, cast(count(distinct case when s.sentiment_score < -0.5 then s.discord_message_id end)::real/count (distinct s.discord_message_id)::real as decimal(10,2)) as negative_sentiment_pct from discord_message_sentiment s join discord_messages m on s.discord_message_id = m.discord_message_id where s.message_created_at >= current_date - 60 -- parameter and m.channel_name = 'DAOLationships' -- parameter group by 1,2 order by 1