By looking at the top 20..50 words used by a user, I hope to identify who cares about what the most, and then use the information to engage them in right topics and ask them right questions for better motivation. What tables / columns would be useful for this task? I suspect I should play with po…

So, here it is: “Speak their language” SQL query This query fetches 60 lexemes that are most frequently used by users in their messages from last 90 days, per user. [画像] Notes users are ordered by the number of words they posted in the last 90 days, most active posters first only those messages…

Well done and thanks @meglio , interesting results across a few Discourse instances. I turned this into a parametrised query for Data Explorer ( @riking awesome job on this plugin!) most-used-words-per-user.dcquery (1).json (1.8 KB) -- [params] -- int :past_x_days = 90 -- int :top_x_words = 60 -- i…

A really cool query, but I’ve noticed some unexpected results. Words from the Category and Topic tiles are being included in top_words, 1 time for each post made in the topic. In other words, if the category is “ABC” and the topic is “XYZ” if a member makes 25 posts in various topics in the ABC ca…

I don’t extract words on my own - I use topic search data vector generated in PostgreSQL that Discourse builds for search purposes.

I’ve been messing with queries using posts.raw and posts.cooked but it’s tricky business to not include words in quotes, image tags etc. i.e. non-posted words - Note, this is a very buggy example regexp_matches(posts.raw, '\[quote(?:[^]])*\][^[]*\[\/quote]', 'g') I’m thinking it might be easier to…

So, do you think the words from category name and title are automatically added to post_search_data ? Hm that’s not good. Yes we should extract them.

[画像] meglio: the words from category name and title are automatically added to post_search_data? As an example this for the default “Staff About” search_data 'admin':11 'categori':2,17 'discuss':5 'moder':13 'privat':1 'staff':4,16,18 'topic':6 'visibl':9 raw_data Private category for st…

So, I can’t see the “about” word in the data (Staff About).

Yes, there are a few words that I guess aren’t included because they aren’t considered helpful as search terms. I can see the logic for not including “for”, “are”, “only”, “to”, “and”, “the” and the like. But I am a bit surprised “about” isn’t considered a word that might be searched for

List of stop words here for reference: https://apt-browse.org/browse/ubuntu/trusty/main/i386/postgresql-9.3/9.3.4-1/file/usr/share/postgresql/9.3/tsearch_data/english.stop The idea is that these words appear in “most” English “texts” regardless of subject.

@Mittineague , back to your example, I still can’t understand where is the issue. There are only 3 indexes for the word staff: 'staff':4,16,18 They can all be found in the text: Private category for staff discussions. Topics are only visible to admins and moderators. About the Staff category Sta…

SQL: The most N used words per user (speak their language!)

コミュニティデータとレポート

meglio (Anton) 2016 年 5 月 19 日午前 6:06 19

Just in case you’d like to show the query results in wordpress:

トピック		返信	表示
Understanding what our users are posting about Data & reporting sql-query	9	334	2024 年 6 月 20 日
SQL query in Data Explorer to pull latest topics? Data & reporting sql-query	10	2899	2017 年 6 月 6 日
Top X posters in a given timeframe Data & reporting sql-query	3	207	2024 年 5 月 28 日
See what categories new users (& other trust levels) post in most Data & reporting sql-query	3	1907	2018 年 2 月 13 日
User-posted code blocks in a category Data & reporting sql-query	3	286	2024 年 5 月 13 日

SQL: The most N used words per user (speak their language!)

関連トピック