Definitely. It’s the only way you’ll be able to search across your staff notes.
Here’s the query that you’ll need:
WITH psr AS (
SELECT regexp_matches(plugin_store_rows.key, '[0-9]+') AS psr_key_value
, plugin_store_rows.plugin_name AS psr_plugin_name
, plugin_store_rows.value AS psr_value
users.id = CAST (array_to_string(psr.psr_key_value, '') AS int)
psr.psr_plugin_name LIKE 'staff_notes'
psr.psr_value LIKE '%[staff_note_goes_here]%'
The most powerful use of Data Explorer that I’ve discovered is for finding out what people are reading (so I can help target the right resources and send them their way). For that I use this query (courtesy of @mcwumbly):
-- integer :user = 1
-- integer :since_days_ago = 7
with topic_timing as (
select user_id, topic_id, sum(msecs) / 1000 as seconds
where user_id = :user
group by user_id, topic_id
from topic_views tv
left join topic_timing tt
on tv.topic_id = tt.topic_id
and tv.user_id = tt.user_id
where tv.user_id = :user
and viewed_at + :since_days_ago > CURRENT_TIMESTAMP
order by seconds desc
These are the things that I measure and where I source the info:
new (not return) visitors to the entire site (Google Analytics)
- new registrations to the community for the period (I download all members from /admin/users/list/new and dump them into a spreadsheet which I then sort by created_at to pull out the members for that period only)
- newcomer to registration conversion rate (2. divided by 1.)
new members that made a contribution (I re-sort the above spreadsheet by post_count)
- % new members that made a contribution (4. divided by 2.)
- Active members (I get this from platform community-analytics.com but you could get it from /users if you sort by Replies)
- Total members (this is really just a vanity metric but you can get it from the Discourse dashboard)
- Total posts less mine (Total comes from the Dashboard, mine comes from /users)
Does that help?