Data Explorer Plugin

The Data Explorer plugin allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.

Usage

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.

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 master · 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

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 all the results with a much higher limit.

Installation

If you’re on a hosted Discourse forum and the data explorer doesn’t show up at /admin/plugins, email us at team@discourse.org to ask us to add it for you.

For self-hosted discourse forums, follow the Install A Plugin guide using https://github.com/discourse/discourse-data-explorer.git .

69 Likes
Daily graph of user time online
Visual forum statistics
What cool data explorer queries have you come up with?
Network chart of your forum - data visualization
Extracting email addresses for a group
Visual map of users
Can I see in the statistics which post received the most likes
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 Narrative Bot Data Explorer Queries :robot:
Where to find User ID information
Exporting all Topic titles and IDs?
How to measure active users?
Creating Point/Reward System for users
Discourse view file update does not reflect in browser
How to look up a user based on a user_id?
Create report for amount of interactions per user
External Data Analysis
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?
How to get all posts a user interacted with?
How can I email all recently active users?
[Paid] Data explorer queries for ticketing system use case
Retort - a reaction-style plugin for Discourse
Direct connect to the Discourse Database
How do I clear Warnings?
Can I make a plug-ins to view a certain group of people?
See what categories new users (& other trust levels) post in most
Can I access my Discourse forum database?
Counting and reporting post views for commercial forum users (so we can charge them per-view)
Top user list public
How do I audit votes for fraudulent behavior
How do I get a list of all users from the API?
Tags: how can admin see who is following what tags?
Tracking referrals? Encouraging users to invite others?
Display Data Explorer query results in WordPress (with TwigAnything)
Database's Data Model?
How do I add users to a group?
Discourse disk space limits per user?
Estimated timeline on a project?
Report on User Fields?
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
Importing from phpBB3
Retrieving user information via REST API
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
Discourse - What do admins see on the Analytics dashboard?
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?
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?
How to search/filter untagged topics
TeamSpeak Usergroups Sync
Use tags to duplicate images?
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
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
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 Create Polls
How to edit sign up form?
Filter posts with images (gallery view)
Tracking unique contributors
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?
Query to find out who marked the solution as solved
Deleted user with rails console u.delete -> email still "taken"
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?
Beginners Guide to Install Discourse on Ubuntu for Development
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
Redesign of use profile
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?