Discourse Data Explorer

Summary

:discourse2: Summary Discourse Data Explorer allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.
:hammer_and_wrench: Repository Link https://github.com/discourse/discourse-data-explorer
:open_book: Install Guide How to install plugins in Discourse

Features

After installing the plugin, head to /admin/plugins .

Click on the “Settings” button, and turn on data explorer enabled, then return and reload the page.
There should now be a tab in the stacked nav called “Data Explorer”. Select it or head to /admin/plugins/explorer , then use the + , Import or Run Query buttons to get started.

Configuration

Default Queries

On a fresh install, the data explorer now ships with a bunch of useful queries that can help you draw insights from your forum’s activity. Open any query and click Run Query to try it out.

Here's what the stock queries look like.

Note: Default queries are picked up from this file: discourse-data-explorer/queries.rb at main · discourse/discourse-data-explorer · GitHub

If you have an awesome query that you want everyone to have, make a PR just like this one.

Default queries cannot be edited, feel free to copy the sql and paste it into a new query if you’d like to modify them.

Add your own queries

For an example, let’s import one that I wrote. Download this file, then open the import dialog and select the file to be uploaded or paste the query in the text box. Click Import to save it and then click Run Query to see the results.

most-common-likers.dcquery (1).json (442 Bytes)

Looks like all of the Discourse developers like each other quite a bit :heart:

I don’t know any SQL, where do I find more queries?

Our community has got you covered! :wink:
Import as many queries as you like from here: What cool data explorer queries have you come up with?

Writing Queries

When you click any of the Edit buttons on a query view, or start a new Explorer Query, you are presented with a screen like the one below:

The top two boxes are the name and description of the query. The name is what you’re selecting in the dropdown box, and the description should be used for things like explaining what to put in the query parameters or to describe what data is being queried.

The left pane is where you write the SQL query. Minor syntax highlighting and checking is provided.

Declaring Parameters in your query

Lets modify our example by using a parameter instead of hard-coding values. If you declare a variable as shown below, a text field will show up where you can input different values! Try it out!

de6


-- [params]
-- int :limit = 100
WITH pairs AS (
SELECT p.user_id liked, pa.user_id liker
FROM post_actions pa
LEFT JOIN posts p ON p.id = pa.post_id
WHERE post_action_type_id = 2
)
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs
GROUP BY liked, liker
ORDER BY count DESC
LIMIT :limit

There are several available kinds of parameters to use in your query, some with specialized input. Any of these parameter types can be prefixed with null to allow an empty input.

  • int, bigint, double - Shows a number input, becomes a numeric value. int is restricted to 32-bit numbers, while bigint can be larger.
  • int_list - Enter comma-separated integers, becomes comma-separated integers in the query. (See List Parameters below.)
  • post_id, topic_id, category_id, group_id, badge_id - Numeric input; ensures the specified post, topic, category, group, or badge exists on the forum before running the query to prevent mistakes.
  • boolean - Shows a checkbox, or a dropdown if null boolean is used instead.
  • string - Freeform textbox, becomes a text value.
  • string_list - Comma-separated freeform text. (See List Parameters below.)
  • time, date, datetime - Textbox that is string-casted to the appropriate type in the query. No client-side validation (yet!).
  • user_id, user_list - Shows the Discourse user selector box, and becomes the numeric user ID or a comma-separated list of the numeric user IDs. (See List Parameters below.)

Using list parameters

Three of the above parameter types are “lists”. Special care must be taken when using these to avoid syntax errors. Here is an example of correctly using a list parameter:

-- [params]
-- user_list :the_user_ids
SELECT SUM(length(bio_raw))
FROM user_profiles
WHERE user_id IN (:the_user_ids)

Searching in your queries list

If you’ve accumulated too many awesome queries, no problem! The search bar can help you filter through titles and descriptions.

Allow moderators or custom groups to run a query

You can add groups (including moderators) to a query to grant them access to it:

After saving, you can find it from the Groups page for that particular group on the Reports tab:

Click any query to open it and then the Run button to see the results.

Limitations

  • If there are more than 1000 results, Run Query will return only the first 1000.
  • Clicking Export will let you download the results with a much higher limit of 10,000.

CHANGELOG

TODO

96 Likes
What cool data explorer queries have you come up with?
Daily graph of user time online
Visual forum statistics
Extracting email addresses for a group
Visual map of users
Can I see in the statistics which post received the most likes
External Data Analysis
Redesign of use profile
How to search/filter untagged topics
Tags: how can admin see who is following what tags?
How do I download a record of top posts or most clicked links?
Can staff be able to see real author of anonymous posts
Discourse view file update does not reflect in browser
Install Discourse on Ubuntu for Development
Create report for amount of interactions per user
Can I access my Discourse forum database?
CSV of all editeds post by moderator
Can I make a plug-ins to view a certain group of people?
How to look up a user based on a user_id?
Where to find User ID information
Discourse Narrative Bot Data Explorer Queries :robot:
How to measure active users?
Retrieving user information via REST API
Exporting all Topic titles and IDs?
How do I clear user warnings?
See what categories new users (& other trust levels) post in most
How can I extract the adminstrative records for a EU GDPR request?
Counting and reporting post views for commercial forum users (so we can charge them per-view)
Top user list public
Legal Tools Plugin
How do I audit votes for fraudulent behavior
How do I get a list of all users from the API?
Tracking referrals? Encouraging users to invite others?
Display Data Explorer query results in WordPress (with TwigAnything)
Database's Data Model?
Filter or create statistics
How do I add users to a group?
Discourse disk space limits per user?
Masonry Image Gallery
Estimated timeline on a project?
Report on User Fields?
Dashboard - Segment number of Posts and Topics by team
Badge image missing issue
Traditional multi level hierarchy vs flat discourse hierarchy
Is there a Trust Level Permissions Table?
Deleting posts by banned users with < 3 posts?
Is there a way to Search by Posts with No Staff replies on it
How to use SQL to query discourse database
Specify user by external id
Migrate a phpBB3 forum to Discourse
How to check the uniqueness of users in "hot link" badge?
Active users per day
What cool data explorer queries have you come up with?
What cool data explorer queries have you come up with?
Questions about moving an existing forum to Discourse
Get a user object by username or id javascript
Best Practices on reply-by-email
Most liked reply
Likes on a post
Group forum members based on topics viewed?
The metrics on dashboard, do they include activities from administrators?
Can I access Discourse forum Database to use for PowerBI reporting?
Is there a log of user visits?
Which user has the most badges?
Find posts "solved" in specific month
Anonymous User Groups
Any way to NOT send emails when a topic category is changed?
Scan IP matched accounts
View/read by user or of user's posts?
After server migration the images in posts aren't downloadable anymore
Bug with [code]anything[/code] on a single line by itself
Logo is not appearing showing broken after upload
Users reporting lots of 502 errors when attempting to post due to "max consecutive replies" check
Wildcard search
What would you like to see on your Discourse 2.0 Admin Dashboard?
TeamSpeak Usergroups Sync
Use tags to duplicate images?
Database Access required for our forum
Updated discourse and now site fails to display
Automatic badge expiration
Looking to switch from Vanilla, and have a few questions
Comment on a question instead of answering it
Making Poll Results Visible After it has been closed
How to create custom reports
Deleted username but can not use that username in other user
Category Remains Muted after Category is available to Everyone
Speak to a Discourse Data Analytic representative
How do I pull a report for a single community member?
Report Links to New Topics by Date?
Excluding Suspended, Silenced and Suspect Users When Exporting
Theme-Component v Plugin: What's the difference
Export topics to Excel
Overview which trust level has which possibilities
Discourse Assign
Post notices (staff notices)
Why are some users greyed out?
How to get reports for just a specific category?
Reports for group message statistics
TimeStamp of Tag
How we put people in a category?
Is it possible to see the dates of all scheduled topics?
Topic Count Per User for Custom Date?
Searching for unassigned topics
Reports by Discourse
How can I get the list of Discourse Topic IDs dynamically
How to edit sign up form?
Poll: Export poll results in CSV for quiz creator?
Tracking unique contributors
New users who visited this month?
Making report on group membership?
Where to find post contents in the database
Searching All Messages as an Administrator
Are Staff and All Groups Included in Dashboard Reporting?
Deleted user with rails console u.delete -> email still "taken"
First Name, Last Name
Identify private message interactions between users (Data Explorer)
User Messages Inbox Error 500
Description of fields returned by Discourse API
Is there a document intruducing the database schema design?
Failed to bootstrap due to out of memory killer
Inviting Feedback for Discourse Wiki ("Morphpedia")
What will happen to my forum if discourse suddenly stopped?
Why aren't warnings easily accessible like suspensions are?
Organization of community Data Explorer queries
Get total list of topics and their view counts from Discourse API
Selecting the Quarter results in custom way
Create a Report on Profile data
Who marked Solution for Topic
More data from Admin reports
Regarding possibility to use Discourse as an alternative to JIRA or Zendesk
Retrieve latest posts by ID
More granular Discourse metrics?
Dump all conversations in a file and structured data
Special role to access dashboard
Best API for All First Posts in a Category
Data Explorer: Posts by Category (Including Subcategories)
Can the period stats use custom time frames to not be limited to only the most recent period?
Tracking who accepted which invite link
Advanced Search: Find posts having staff color
2021: The Year in Review
Filter topics not answered by Staff
How does auto-close topics work?
Can I download a user database and sort by time viewed?
Exclude certain categories from the Trust Levels
Category API request downloads all topics
User Data By Date Rage
Browse the Postgres database
How do I extract raw data from my discourse community site?
Some issues with searching (sorting, searching deleted posts and such)
How to Delete Uploaded Files?
How to show badges in the message list?
How to show badges in the message list?
Error trying to edit a post with an image in (NginX Proxy Manager?)
How to find pageview data for a specific topic?
Badges for X Number of Solutions Created?
Permanent deletion bug
Adding SSO after many users already signed up -- how to migrate them?
How could I get voters from a poll?
Notifications from muted categories when mailing list mode is enabled
API: how to get joined at date in user summary
Edit a user preference for everyone or a subset of users
Create a Network chart of your forum - data visualization
How do I get a report of total topics by subcategory for all subcategories please?
Comment, Question, & Discussion Level Agreement Voting - is there an extension?
Create a poll that others can vote on
SQL query for last created users of the past 30 days
How can I see Survey Results or Responses
Filter posts with images (gallery view)
Query to find out who marked the solution as solved
Possible to run a report for most posts within community?
Add an Admin subtab for Inactive users
Questions regarding Discourse features
How to query an email list of users that are not actived
Shopify Integration
How do I see Facebook signups?
Poll: Export poll results in CSV for quiz creator?
Discourse disk space limits per user?
Sorting all users by total posts?
SQL: The most N used words per user (speak their language!)
User Summary - Most Liked By user attributions are incorrect
Maximum Number of Search Results?
Get only top level posts in topic via discourse API
How to get all the deleted posts for a specific topic
Stats reports on a per-category basis?
Data analytics by complex networks and text mining
Error thrown when importing a query in Data Explorer
Zero search results searching private messages for specific keyword
A way to see full list of users granted with a certain badge?
Who messaged the most?
Who messaged the most?
How to open hyperlinks in a new tab
Run report of digest Unsubscribes
Need log the who downloaded attachments
Poll problem - exact vote numbers needed
Telegram Notifications Plugin (discourse-telegram-notifications)
Get last :heart: via API
Is it possible to keep the votes history of a poll?
How do I check a badge's status midway to it being awarded?
What plugins do you use - and why?
What cool badge queries have you come up with?
Export list of users based on date range
How to get all posts a user interacted with?
How can I email all recently active users?
Raw Function limited to 100
[Paid] Data explorer queries for ticketing system use case
Retort - a reaction-style plugin for Discourse
Direct connect to the Discourse Database

Hi ! Trying to install your plugin on a container (bitnami/discourse). But i can’t precompil assets with the command : RAILS_ENV=production bundle exec rake assets:precompile. This fail everytime with the error : NameError: uninitialized constant BaseBookmarkable.
Hope you can help me to understand what’s going on ! Thanks by advance.

1 Like

Hi there, i’m having the exact same issue this morning.
Worth noting, I installed 3 other plugins the exact same way without any error.

@Discourse

@Lexias
Well i found a workaround :

RAILS_ENV=production bundle exec rake plugin:install repo=https://github.com/discourse/discourse-data-explorer
cd "$DISCOURSE_BASE_DIR/plugins/discourse-data-explorer"
git reset --hard 92bdea38b2af1b4e04f4c52d1bd34550d553f66b
cd "$DISCOURSE_BASE_DIR"
RAILS_ENV=production bundle exec rake assets:precompile

Made a git reset to a working version before the BaseBookmarkable commit, it’s now working like a charm.

1 Like

Hey, is it possible to run an update statement with this plugin?

if not is there any other simple way to run an update statement?

The data-explorer is read-only, I’m afraid. If you have server access you could use the rails console, though making changes directly can be risky depending on experience/skill level so it’s recommended to make a backup beforehand.

1 Like

I am trying to get Data Explorer plugin working on a Docker dev install under Windows WSL. I cloned it to the plugins folder and I can see it listed in admin/plugins. I can turn it on and a link then shows up in the stack navigation. However when I click the navigation link I get an ‘internal server error 500 while trying to load /admin/plugins/explorer/queries’.

Can anyone offer advice on where to go next? Are there file permissions that need to be set in the dev mode? Is there some voodoo that I need to do after copying it to the plugin folder? Is there something that I need to add to config/setting.yml?

Any help would be welcome.

@DonH @VladChad I had the same problem, you can try this solution:

Dumb question - that ceiling is looming closer for one of our forums. Can we exceed it without forking the plugin?

It looks like there is a hidden setting for the default 1000 display limit, allowing it to go up to 10,000. But I don’t need that, and 1000 makes a lot of sense for the UI within Data Explorer.

However, a spreadsheet can handle a lot more. What I want to do is be able to Export to a higher limit (say 20,000). Is that doable?

I am coincidentally tonight also up against this 10,000 record limit. Is there a way to breach it? I need another order of magnitude.

A way to test would be to enter the container, edit /var/www/discourse/plugins/discourse-data-explorer/plugin.rb and change QUERY_RESULT_MAX_LIMIT = 10000, do a sv restart unicorn and see what happens. Of course there are reasons not to allow such huge queries; if you’re confident of your infrastructure being able to handle it, or don’t mind a bit of instability, this could work.

If the world doesn’t explode, then you could add some stuff to app.yml that would modify that file after the plugins get cloned at every rebuild. It would probably conflict with upgrading with docker_manager. (You can look at some other templates the modify files to figure out what some stuff might be.)

@DonH if you need that query a lot and it’s just one then you might want a plugin that would deliver it somehow. It could, say, write a file in chunks that you could retrieve via some path.

1 Like

Thanks for this advice, Jay. I have indeed thought about writing a plugin but also that it should be doable from the rails console as for the various bulk operations but I don’t know rails and that anyway appears to require triggering mbedded functions. That would create the same overwrite problem unless the jobs were committed to core.

I’ve been managing so far with the Data Explorer on the down side and (very careful) psql on the up side but I would much rather do things by the book, fate being what it is.

I should probably be explicit about what I’m up to.

I run a forum that is in it’s third or fourth manifestation having been through Phorum and phpBB software in my hands and something else before I got to it. The subject matter is narrow and the user base is tiny but the content has carried forward at each move and represents a lot of institutional knowledge. Discourse, with it’s categorization, tagging and interface features seemed like a great solution to knowledge accessibility.

So I’ve ported the forum which was never categorized or tagged in it’s previous incarnations. Rather than wade through 100K posts/8K threads I have been using some natural language processing software to help with the categorizations and tagging. I then update the topics, categories and topic_tag tables directly being, as I said, quite careful.

The process is still ongoing but I have a stable work flow and can easily finish up with the tools at hand. Moving forward, though, there will be regular periodic updates to wrap in new categorizations and tags which may or may not be different than the prior data. So you can see the need.

Obviously the Data Explorer is one-way flow but it’s been very convenient. I can overcome the size limit by doing batches and, now, by raising the limit setting so thanks for that.

Is it possible to automate the plugin to export the data every month to a google sheet?

Export data? I don’t think so. But you could very likely write a Google Script that runs monthly to import data. Could also use something like Zapier in middle between Discourse and Google Sheets.

1 Like

Would the google script have access to all user data and custom user fields? I would have thought that some of that would be protected.

I guess i can just press the button myself each month.

You’d need to give the Google Script an API Key. You can restrict that API Key to only be allowed to run data explorer queries, and you can further restrict it to only be able to run specific queries. You are correct, Google Script (or anything external) cannot simply run a data explorer query.

Check out /admin/api/keys/new and the “Granular” scope level. Scroll down to see the data explorer scope. See docs.discourse.org for details on using the API.

3 Likes

Great thanks, i’ll work through that

2 Likes