Data Explorer Plugin

data-explorer
official

(Kane York) #1

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 .


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 view file update does not reflect in browser
How to look up a user based on a user_id?
Creating Point/Reward System for users
How to measure active users?
Get infromation from data explorer and put on user card
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
Discourse Narrative Bot Data Explorer Queries :robot:
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?
What cool badge queries have you come up with?
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
Users reporting lots of 502 errors when attempting to post due to "max consecutive replies" check
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?
Exporting data-- is it possible?
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
Poll: Export poll results in CSV for poll creator?
Database's Data Model?
Retort - a reaction-style plugin for Discourse
How do I see Facebook signups?
External Data Analysis
How to check the uniqueness of users in "hot link" badge?
API search list user by array of user names
About the data-explorer category
Retrieving user information via REST API
Importing from phpBB3
Discourse disk space limits per user?
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
What would you like to see on your Discourse 2.0 Admin Dashboard?
How do I clear Warnings?
Bug with [code]anything[/code] on a single line by itself
(Erlend Sogge Heggen) #4

105 posts were split to a new topic: Data Explorer - Legacy replies


(Jeff Atwood) #80

16 posts were split to a new topic: Strange problem with Data Explorer


Strange problem with Data Explorer
(Jeff Atwood) #84

A post was split to a new topic: Find old topics with images?


(Joshua Rosenfeld) #86

3 posts were merged into an existing topic: Admin Statistics Report


Strange problem with Data Explorer
(Erlend Sogge Heggen) #109

A post was split to a new topic: Improve UX of drop-down


(Erlend Sogge Heggen) #110

A post was split to a new topic: Add alphabetic ordering


(Erlend Sogge Heggen) #111

7 posts were split to a new topic: Access to query data from API endpoints


(Erlend Sogge Heggen) #126

8 posts were split to a new topic: Make data explorer available to moderators


(Erlend Sogge Heggen) #134

9 posts were split to a new topic: Download result of queries into Google Spreadsheet


(Erlend Sogge Heggen) #135

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


(AstonJ) #144

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.


(LT) #145

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


(Kane York) #146

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.


(Coin-coin le Canapin) #148

Hi,

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


(Rishabh Nambiar) #149

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

(Chris Beach) #151

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.