# Data & reporting **URL:** https://meta.discourse.org/c/community-building/data-reporting/148.md?page=24 [Latest](https://meta.discourse.org/latest.md) · [Categories](https://meta.discourse.org/categories.md) · [Tags](https://meta.discourse.org/tags.md) **Page:** 25 --- ## [A badge for being exceptionally relevant?](https://meta.discourse.org/t/a-badge-for-being-exceptionally-relevant/281546)
**Author:** [@tophee](https://meta.discourse.org/u/tophee)\ **Replies:** 0\ **Last updated:** [May 31, 2017, 8:57pm UTC](https://meta.discourse.org/t/a-badge-for-being-exceptionally-relevant/281546 "2017-05-31T20:57:48Z")
Here is a tricky one to figure out for our SQL gurus: a badge for being exceptionally relevant. How to measure this? I suggest calculating the ratio of posts with at least one like or bookmark to the total number of post… --- ## [Discourse Narrative Bot Data Explorer Queries 🤖](https://meta.discourse.org/t/discourse-narrative-bot-data-explorer-queries/63060)
**Author:** [@david](https://meta.discourse.org/u/david)\ **Replies:** 3\ **Last updated:** [May 20, 2017, 1:35pm UTC](https://meta.discourse.org/t/discourse-narrative-bot-data-explorer-queries/63060 "2017-05-20T13:35:13Z")
In the interest of tracking user interaction with @discobot, here are are some data explorer queries. If anyone can think of a way to actually show a percentage progress through the track that would be awesome! But as f… --- ## [Change Refresh Time For Admin Statistics](https://meta.discourse.org/t/change-refresh-time-for-admin-statistics/62692)
**Author:** [@sulliops](https://meta.discourse.org/u/sulliops)\ **Replies:** 4\ **Last updated:** [May 14, 2017, 9:33pm UTC](https://meta.discourse.org/t/change-refresh-time-for-admin-statistics/62692 "2017-05-14T21:33:23Z")
I have my forum working perfectly, but I’m going to eventually monetize the forum (basically renting out categories for a lower price than VPS hosting). My issue is that I need to track page views in real time, but the … --- ## [Is it possible to get a list of topics in a category read by a specific user?](https://meta.discourse.org/t/is-it-possible-to-get-a-list-of-topics-in-a-category-read-by-a-specific-user/61641)
**Author:** [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)\ **Replies:** 4\ **Last updated:** [April 26, 2017, 5:44pm UTC](https://meta.discourse.org/t/is-it-possible-to-get-a-list-of-topics-in-a-category-read-by-a-specific-user/61641 "2017-04-26T17:44:57Z")
I realize this is an oddball request but is it possible, perhaps via a data explorer query, to retrieve a full list of posts in a specific category that has been read by a specific user? Is it possible to create a list o… --- ## [How can I count posts in last month by a specific group of users?](https://meta.discourse.org/t/how-can-i-count-posts-in-last-month-by-a-specific-group-of-users/52489)
**Author:** [@alefattorini](https://meta.discourse.org/u/alefattorini)\ **Replies:** 4\ **Last updated:** [November 9, 2016, 7:14pm UTC](https://meta.discourse.org/t/how-can-i-count-posts-in-last-month-by-a-specific-group-of-users/52489 "2016-11-09T19:14:07Z")
Do you have any query for Data explorer? I need to calculate the impact of my ambassador group --- ## [Email statistics](https://meta.discourse.org/t/email-statistics/52478)
**Author:** [@meglio](https://meta.discourse.org/u/meglio)\ **Replies:** 1\ **Last updated:** [November 5, 2016, 7:00pm UTC](https://meta.discourse.org/t/email-statistics/52478 "2016-11-05T19:00:55Z")
I run into 100k / mo emails limit of a free SparkPost account. To better understand what type of email exactly I have to tackle, I had to write an SQL query which calculates email statistics by email type. Sharing it wi… --- ## [Active users for specific months](https://meta.discourse.org/t/active-users-for-specific-months/51495)
**Author:** [@alehandrof](https://meta.discourse.org/u/alehandrof)\ **Replies:** 5\ **Last updated:** [October 17, 2016, 9:45pm UTC](https://meta.discourse.org/t/active-users-for-specific-months/51495 "2016-10-17T21:45:03Z")
I’m trying to find two pieces of data: How many active users\* did my community have in the month of April 2016 and how many in September 2016. \* Active user = posted at least 1 topic or reply within the time period. Mo… --- ## [A query for users with a custom title?](https://meta.discourse.org/t/a-query-for-users-with-a-custom-title/275004)
**Author:** [@jomaxro](https://meta.discourse.org/u/jomaxro)\ **Replies:** 4\ **Last updated:** [October 15, 2016, 6:07pm UTC](https://meta.discourse.org/t/a-query-for-users-with-a-custom-title/275004 "2016-10-15T18:07:24Z")
Anyone able to provide the query for all users with a custom title (not a badge granted title)? --- ## [How do I make my own badge query?](https://meta.discourse.org/t/how-do-i-make-my-own-badge-query/50946)
**Author:** [@keith1](https://meta.discourse.org/u/keith1)\ **Replies:** 4\ **Last updated:** [October 1, 2016, 5:18pm UTC](https://meta.discourse.org/t/how-do-i-make-my-own-badge-query/50946 "2016-10-01T17:18:20Z")
I’m feeling quite silly for having to ask, but I’m finding tons of great resources for making my own badges, but I can’t figure out where exactly to put all of these badge queries. Can someone just point me in the right… --- ## [Create a "Diary writer" badge](https://meta.discourse.org/t/create-a-diary-writer-badge/50844)
**Author:** [@meglio](https://meta.discourse.org/u/meglio)\ **Replies:** 0\ **Last updated:** [September 29, 2016, 3:05am UTC](https://meta.discourse.org/t/create-a-diary-writer-badge/50844 "2016-09-29T03:05:45Z")
:warning: Badge SQL is disabled by default. See: Enable Badge SQL In our goat farmers forum, people love to write Diaries about anything - a long-term experiment, travelling experience, their new hobby etc. So we dev… --- ## [List of all Members of a Group with Custom Field](https://meta.discourse.org/t/list-of-all-members-of-a-group-with-custom-field/275003)
**Author:** [@fefrei](https://meta.discourse.org/u/fefrei)\ **Replies:** 0\ **Last updated:** [September 15, 2016, 4:11pm UTC](https://meta.discourse.org/t/list-of-all-members-of-a-group-with-custom-field/275003 "2016-09-15T16:11:41Z")
List of all Members of a Group with Custom Field We use this query to get a list of the name and matriculation number of all team members: SELECT users.name, user\_custom\_fields.value as matriculation FROM users JOIN gro… --- ## [Posts created for period](https://meta.discourse.org/t/posts-created-for-period/275138)
**Author:** [@HAWK](https://meta.discourse.org/u/HAWK)\ **Replies:** 0\ **Last updated:** [September 14, 2016, 9:54pm UTC](https://meta.discourse.org/t/posts-created-for-period/275138 "2016-09-14T21:54:25Z")
Posts created for period Got what I need (thanks @meglio) so updating this for future posterity. -- \[params\] -- date :date\_from -- date :date\_to -- int :min\_posts = 1 WITH user\_activity AS ( SELECT p.user\_id, cou… --- ## [Active users in the last 30 days](https://meta.discourse.org/t/active-users-in-the-last-30-days/275140)
**Author:** [@riking](https://meta.discourse.org/u/riking)\ **Replies:** 3\ **Last updated:** [September 5, 2016, 9:47pm UTC](https://meta.discourse.org/t/active-users-in-the-last-30-days/275140 "2016-09-05T21:47:07Z")
Active users in the last 30 days select username from users where last\_posted\_at \> current\_timestamp - interval '30' day --- ## [Absence of Staff Notes in a particular category](https://meta.discourse.org/t/absence-of-staff-notes-in-a-particular-category/275168)
**Author:** [@meglio](https://meta.discourse.org/u/meglio)\ **Replies:** 0\ **Last updated:** [August 30, 2016, 5:57pm UTC](https://meta.discourse.org/t/absence-of-staff-notes-in-a-particular-category/275168 "2016-08-30T17:57:30Z")
Here is a use-case I solved with the query you’ll find below. I have a special category where users create their “personal pages” (for their goat farms). Now we’re creating a map with all these farms placed on it. Once… --- ## ['Seen in last X days' badge?](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735)
**Author:** [@alefattorini](https://meta.discourse.org/u/alefattorini)\ **Replies:** 12\ **Last updated:** [August 26, 2016, 10:56pm UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735 "2016-08-26T22:56:59Z")
What about a badge like “Seen here last 30 days” or “Seen here last 60 days” Clearly, you can lose it in case you haven’t visited discourse recently. --- ## [RGSoC 2016: Visual Forum Analytics Community Discussion](https://meta.discourse.org/t/rgsoc-2016-visual-forum-analytics-community-discussion/46905)
**Author:** [@saintsebastian](https://meta.discourse.org/u/saintsebastian)\ **Replies:** 12\ **Last updated:** [August 18, 2016, 5:04pm UTC](https://meta.discourse.org/t/rgsoc-2016-visual-forum-analytics-community-discussion/46905 "2016-08-18T17:04:42Z")
Hey everyone! I am one of two people who is planing to provide you with informative graphs and charts on user, post etc statistics. We are doing this as our project for Rails Girls Summer of Code and this is our introdu… --- ## [Implementing Google Tag Manager with Discourse](https://meta.discourse.org/t/implementing-google-tag-manager-with-discourse/36861)
**Author:** [@Jenn\_Briden](https://meta.discourse.org/u/Jenn_Briden)\ **Replies:** 23\ **Last updated:** [July 14, 2016, 10:48pm UTC](https://meta.discourse.org/t/implementing-google-tag-manager-with-discourse/36861 "2016-07-14T22:48:08Z")
I can see that there are settings to specify a Universal Analytics tracking code directly through Discourse. However, I’d prefer to use Google Tag Manager (GTM) since there are other settings that we’re using in our GTM … --- ## [Member Uploads](https://meta.discourse.org/t/member-uploads/275001)
**Author:** [@Mittineague](https://meta.discourse.org/u/Mittineague)\ **Replies:** 0\ **Last updated:** [June 20, 2016, 6:14pm UTC](https://meta.discourse.org/t/member-uploads/275001 "2016-06-20T18:14:23Z")
Member Uploads I put together this query to help find members that post a lot of uploads that potentially might lead to a problem. Ordered by total upload weight per member WITH heavy\_uploads AS ( SELECT ( SUM(u… --- ## [Badge not working](https://meta.discourse.org/t/badge-not-working/39426)
**Author:** [@zainab](https://meta.discourse.org/u/zainab)\ **Replies:** 3\ **Last updated:** [June 9, 2016, 4:46pm UTC](https://meta.discourse.org/t/badge-not-working/39426 "2016-06-09T16:46:56Z")
Hi, I’m not sure if I’m posting in the correct category. anyway theres a badge for the forum called help desk and its not working, users get 10 accepted answers yet they dont get a badge here’s the SQL code: SELECT p… --- ## [Off-topic posts that deserved their own topic](https://meta.discourse.org/t/off-topic-posts-that-deserved-their-own-topic/275139)
**Author:** [@Mittineague](https://meta.discourse.org/u/Mittineague)\ **Replies:** 0\ **Last updated:** [May 27, 2016, 3:47am UTC](https://meta.discourse.org/t/off-topic-posts-that-deserved-their-own-topic/275139 "2016-05-27T03:47:44Z")
While working on this I was looking for a way to find topics that were created as a result of a moderator splitting posts into a new topic. (i.e. off-topic posts that deserved their own topic) It wasn’t as easy as I … --- ## ['Reply by email' badge](https://meta.discourse.org/t/reply-by-email-badge/276733)
**Author:** [@lrossouw](https://meta.discourse.org/u/lrossouw)\ **Replies:** 4\ **Last updated:** [May 17, 2016, 8:33am UTC](https://meta.discourse.org/t/reply-by-email-badge/276733 "2016-05-17T08:33:46Z")
Anyone done a reply by email badge? --- ## [Recently Read Topics by User](https://meta.discourse.org/t/recently-read-topics-by-user/275000)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 4, 2016, 5:25am UTC](https://meta.discourse.org/t/recently-read-topics-by-user/275000 "2016-05-04T05:25:02Z")
Recently Read Topics by User Show the topics with that have been opened by a given user in the past N days, sorted by the amount of time the user has spent in that topic. (requested on feverbee) -- \[params\] -- integer :… --- ## [Likes by 'team'](https://meta.discourse.org/t/likes-by-team/274999)
**Author:** [@fefrei](https://meta.discourse.org/u/fefrei)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 6:48pm UTC](https://meta.discourse.org/t/likes-by-team/274999 "2016-05-01T18:48:22Z")
Likes from the team This query assumes there is a group called team, and gives you the likes other users have received, split into likes from the team and likes from others: SELECT pl.user\_id, SUM(pl.team\_likes… --- ## [Active Readers (Past Month)](https://meta.discourse.org/t/active-readers-past-month/275135)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 5:15am UTC](https://meta.discourse.org/t/active-readers-past-month/275135 "2016-05-01T05:15:00Z")
Active Readers (Past Month) Users with the most visits that include reading activity select user\_id, count(1) as visits, sum(posts\_read) as posts\_read from user\_visits where posts\_read \> 0 and visited\_at \> CURR… --- ## [Posts Read Percentiles](https://meta.discourse.org/t/posts-read-percentiles/275134)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 5:00am UTC](https://meta.discourse.org/t/posts-read-percentiles/275134 "2016-05-01T05:00:00Z")
Posts Read Percentiles Number of posts read for users in each percentile with tentiles as ( select posts\_read\_count as read, ntile(10) over (order by posts\_read\_count) as tentile from user\_stats ) sel… --- ## [Active Readers (Since N Days Ago)](https://meta.discourse.org/t/active-readers-since-n-days-ago/275136)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 4:53am UTC](https://meta.discourse.org/t/active-readers-since-n-days-ago/275136 "2016-05-01T04:53:38Z")
Active Readers (Since N Days Ago) Number of users who have read at least 1 post since N days ago with intervals as ( select n as start\_time, CURRENT\_TIMESTAMP as end\_time from generate\_series(CU… --- ## [Posts Read (Daily)](https://meta.discourse.org/t/posts-read-daily/275133)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 4:53am UTC](https://meta.discourse.org/t/posts-read-daily/275133 "2016-05-01T04:53:00Z")
Posts Read (Daily) Total number of new posts read by all users per day SELECT visited\_at as day, count(1) as users, sum(posts\_read) as posts\_read, sum(posts\_read) / count(1) as avg\_posts\_read\_per\_user FROM user\_visits … --- ## [Banner stats](https://meta.discourse.org/t/banner-stats/274998)
**Author:** [@Mittineague](https://meta.discourse.org/u/Mittineague)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 2:55am UTC](https://meta.discourse.org/t/banner-stats/274998 "2016-05-01T02:55:33Z")
Most of mine are “OK, but could be better” For example, this one works, but I don’t like the duplicate sub-query much Banner Stats WITH all\_users AS ( SELECT COUNT(users.id) AS user\_count FROM users WHERE users.… --- ## [Users Last Seen Since (Since N Days Ago)](https://meta.discourse.org/t/users-last-seen-since-since-n-days-ago/275130)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 2:00am UTC](https://meta.discourse.org/t/users-last-seen-since-since-n-days-ago/275130 "2016-05-01T02:00:00Z")
Users Last Seen Since (Since N Days Ago) with intervals as ( select n as start\_time, CURRENT\_TIMESTAMP as end\_time from generate\_series(CURRENT\_TIMESTAMP - INTERVAL '30 days', … --- ## [Users Last Seen Since (Since N Weeks Ago)](https://meta.discourse.org/t/users-last-seen-since-since-n-weeks-ago/275128)
**Author:** [@mcwumbly](https://meta.discourse.org/u/mcwumbly)\ **Replies:** 0\ **Last updated:** [May 1, 2016, 1:45am UTC](https://meta.discourse.org/t/users-last-seen-since-since-n-weeks-ago/275128 "2016-05-01T01:45:00Z")
Users Last Seen Since (Since N Weeks Ago) with intervals as ( select n as start\_time, CURRENT\_TIMESTAMP as end\_time from generate\_series(CURRENT\_TIMESTAMP - INTERVAL '140 days', … [Previous page](https://meta.discourse.org/c/community-building/data-reporting/148.md?page=23) [Next page](https://meta.discourse.org/c/community-building/data-reporting/148.md?page=25)