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…

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 catego…

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) 19.Май.2016 06:06:41 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	327	20.06.2024
SQL query in Data Explorer to pull latest topics? Data & reporting sql-query	11	2879	21.11.2022
Top X posters in a given timeframe Data & reporting sql-query	3	192	28.05.2024
See what categories new users (& other trust levels) post in most Data & reporting sql-query	3	1900	13.02.2018
User-posted code blocks in a category Data & reporting sql-query	3	270	13.05.2024

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

Связанные темы