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.

Restrictions

  • 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 .

56 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?
How to look up a user based on a user_id?
How to measure active users?
External Data Analysis
Discourse view file update does not reflect in browser
Discourse Narrative Bot Data Explorer Queries :robot:
Creating Point/Reward System for users
Can staff be able to see real author of anonymous posts
Retort - a reaction-style plugin for Discourse
How do I see Facebook signups?
How to check the uniqueness of users in "hot link" badge?
About the data-explorer category
Retrieving user information via REST API
Importing from phpBB3
Specify user by external id
Get only top level posts in topic via discourse API
Questions regarding Discourse features
Remove Facebook as login option
How can I email all recently active users?
Tags: how can admin see who is following what tags?
See what categories new users (& other trust levels) post in most
Create report for amount of interactions per user
How do I clear Warnings?
Exporting all Topic titles and IDs?
Is there a Data Explorer for Dummies article somewhere?
Display Data Explorer query results in WordPress (with TwigAnything)
Tracking referrals? Encouraging users to invite others?
Query to find out who marked the solution as solved
Add an Admin subtab for Inactive users
Beginners Guide to Install Discourse on Ubuntu for Development
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?
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
Where to find User ID information
Who messaged the most?
Who messaged the most?
How to open hyperlinks in a new tab
Working with plugin_store_rows with API, Possible?
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?
Monitoring Moderator Activity
See All User Activity As An Admin
What plugins do you use - and why?
TeamSpeak Usergroups Sync
CRM Integration
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
What cool badge queries have you come up with?
How to search/filter untagged topics
Is there a Trust Level Permissions Table?
How to use SQL to query discourse database
How to get all posts a user interacted with?
Estimated timeline on a project?
Logo is not appearing showing broken after upload
Direct connect to the Discourse Database
How do I get a list of all users from the API?
Bug with [code]anything[/code] on a single line by itself
What would you like to see on your Discourse 2.0 Admin Dashboard?
Discourse disk space limits per user?
Poll: Export poll results in CSV for quiz creator?
Users reporting lots of 502 errors when attempting to post due to "max consecutive replies" check
Any way to NOT send emails when a topic category is changed?
Find posts "solved" in specific month
A way to see full list of users granted with a certain badge?
[Paid] Data explorer queries for ticketing system use case
Discourse disk space limits per user?
Questions about moving an existing forum to Discourse
Can I make a plug-ins to view a certain group of people?
Sorting all users by total posts?
Shopify Integration
Possible to run a report for most posts within community?
Which user has the most badges?
Is there a log of user visits?
Can I access Discourse forum Database to use for PowerBI reporting?
The metrics on dashboard, do they include activities from administrators?
Group forum members based on topics viewed?
Likes on a post
Discourse - What do admins see on the Analytics dashboard?
Best Practices on reply-by-email
Get a user object by username or id javascript
Database's Data Model?

Please post replies to the #plugin:data-explorer category.

4 Likes

Hi @riking

Great job on this plugin!

It reminds me of a plugin from the vB world called WebTemplates - it consisted of two parts (WebTemplates and WebQueries), the WebQueries side is basically like your Data Explorer Plugin! The WebTemplates part is where you can create pages to use saved queries from the WebQueries section.

Here’s what the WebTemplates page looks like for a specific page:

Towards the bottom you can specify which queries you want available for that page - then in the fields above you specify the HTML and where the result of those queries would show.

We used this on a vB site to power pages like homepage, news homepage, articles homepage, etc then each news item or each article was simple a forum thread that was styled differently for the first post and subsequent posts (using simple statements like if $post == 1 etc). So the entire site was powered by vB.

I’d love to see something like this for DC. I reckon it could take on the publishing world quite easily!!

Anyway just posting here in case it’s of interest to you.

1 Like

@riking Thank you for this incredible plugin! Is there a way to increase the number of results? We need at least 5000…

Use the download buttons to increase the cap to 1000000 (1 million), or if you need more than that, use a custom program that will process the results as they get returned.

7 Likes

Hi,

When doing a request, there is a string error: [missing {{count}} value]:

Tons of new features were added to the Data Explorer in the last few months: Commits · discourse/discourse-data-explorer · GitHub and I’ve updated the OP to reflect that :rocket:

Here’s a list of the significant changes:

  • List all previous queries on Data Explorer homepage
  • Show who created a query and when it was last run
  • Ship a selection of handpicked queries by default:
    discourse-data-explorer/queries.rb at master · discourse/discourse-data-explorer · GitHub
  • Sort queries by name, username & last_run_at by clicking table headers
  • Show number of results after running a query and increase result limit to 1000
  • Allow clickable post titles in Data Explorer result view
  • Add back button on query view, general interface cleanup, bugfixes
19 Likes

Brilliant work @riking @rishabhn and others. I love the flexibility and insight this tool gives to our admins.

Discourse is technically so far ahead of other forum software competitors it’s not even a race anymore.

5 Likes

So if you ask a song this query, what should I do next? (YES im a total noob )
Let’s say it wants these results to appear in the user’s profile?

For example, how many users each has a like from a VIP group of users

How do I do it? What code should I paste and where?

1 Like

You won’t be able to put anything in the user profile using data explorer plug-in. You will need to create a custom plug-in to accomplish what you are describing I believe.

3 Likes

@riking Am I able to submit queries via the discourse api? If so, how? Thanks!

Yes, Data Explorer queries can be run through the API. I just created a #howto topic for this. Can you have a look at it and let me know if anything in the topic isn’t clear: How to run Data Explorer queries with the Discourse API?

9 Likes

A post was merged into an existing topic: How to run Data Explorer queries with the Discourse API