# Data & reporting **URL:** https://meta.discourse.org/c/community-building/data-reporting/148.md?page=10 [Latest](https://meta.discourse.org/latest.md) · [Categories](https://meta.discourse.org/categories.md) · [Tags](https://meta.discourse.org/tags.md) **Page:** 11 --- ## [Run a query based on a timeframe](https://meta.discourse.org/t/run-a-query-based-on-a-timeframe/269161)
**Author:** [@cokestroke](https://meta.discourse.org/u/cokestroke)\ **Replies:** 2\ **Last updated:** [June 21, 2023, 2:18pm UTC](https://meta.discourse.org/t/run-a-query-based-on-a-timeframe/269161 "2023-06-21T14:18:55Z")
Hi all, just writing a query to find out who hasn’t been seen in the last year on the forum so wanted to ask what the syntax would be. tried last\_seen\_at \> 2022:06:21 and got an error --- ## [What rails command can give me 'User-ID' from a username/user-email?](https://meta.discourse.org/t/what-rails-command-can-give-me-user-id-from-a-username-user-email/268784)
**Author:** [@Bathinda](https://meta.discourse.org/u/Bathinda)\ **Replies:** 2\ **Last updated:** [June 18, 2023, 7:40am UTC](https://meta.discourse.org/t/what-rails-command-can-give-me-user-id-from-a-username-user-email/268784 "2023-06-18T07:40:26Z")
Pls tell the way or command to be used to find the ‘USER\_ID’ of some particular user. I wish to use that user id in a ‘Data Explorer’ query. --- ## [Create list of links to all soft-deleted posts](https://meta.discourse.org/t/create-list-of-links-to-all-soft-deleted-posts/267928)
**Author:** [@Jonathan5](https://meta.discourse.org/u/Jonathan5)\ **Replies:** 2\ **Last updated:** [June 12, 2023, 8:49am UTC](https://meta.discourse.org/t/create-list-of-links-to-all-soft-deleted-posts/267928 "2023-06-12T08:49:24Z")
Continuing the discussion from Introducing permanently delete post functionality: Is it possible to create a list of links to all soft-deleted posts? Also, I’d be interested to find out how much space they take up in … --- ## [How to filter topics by user group](https://meta.discourse.org/t/how-to-filter-topics-by-user-group/266582)
**Author:** [@kenjdarhan](https://meta.discourse.org/u/kenjdarhan)\ **Replies:** 7\ **Last updated:** [June 6, 2023, 6:41am UTC](https://meta.discourse.org/t/how-to-filter-topics-by-user-group/266582 "2023-06-06T06:41:29Z")
Is there any way that I can filter the existing topics on the forum based on which user group the author of the latest reply belong to? For example, after I define a group of users as “clients”, I want to filter all the… --- ## [Month over month top traffic sources report?](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933)
**Author:** [@nachocardozop](https://meta.discourse.org/u/nachocardozop)\ **Replies:** 3\ **Last updated:** [June 4, 2023, 5:23am UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933 "2023-06-04T05:23:58Z")
Hello, I’m trying to generate a month-over-month top traffic sources report. It would be cool to see how our sources changed each month, all within one graph/table. Is there any way to get this data? Currently I can on… --- ## [Pageviews metric](https://meta.discourse.org/t/pageviews-metric/263657)
**Author:** [@SophieRice1](https://meta.discourse.org/u/SophieRice1)\ **Replies:** 1\ **Last updated:** [June 2, 2023, 11:43am UTC](https://meta.discourse.org/t/pageviews-metric/263657 "2023-06-02T11:43:08Z")
Hello, Can anyone provide any detail around the ‘Pageviews’ metric on the Admin dashboard? As in what a ‘Page view’ actually is? Ours seems to be very high (for logged in users) so I’m wondering if viewing each reply in… --- ## [Difference between reply and response in user\_actions](https://meta.discourse.org/t/difference-between-reply-and-response-in-user-actions/266531)
**Author:** [@gassim](https://meta.discourse.org/u/gassim)\ **Replies:** 1\ **Last updated:** [May 29, 2023, 2:40pm UTC](https://meta.discourse.org/t/difference-between-reply-and-response-in-user-actions/266531 "2023-05-29T14:40:07Z")
In the user\_actions table, two of the action\_types are ‘reply’ and ‘response.’ Does anyone know the exact difference? You can find these by running a SQL query in the Data Explorer: SELECT \* FROM user\_actions WHERE (… --- ## [Data Explorer - Vote count](https://meta.discourse.org/t/data-explorer-vote-count/266441)
**Author:** [@tyf](https://meta.discourse.org/u/tyf)\ **Replies:** 2\ **Last updated:** [May 28, 2023, 3:58pm UTC](https://meta.discourse.org/t/data-explorer-vote-count/266441 "2023-05-28T15:58:21Z")
Hi all! I’m using the Data Explorer plugin and attempting to get topics within our Feedback category - I can achieve this fine with the following query: SELECT t.id AS topic\_id, t.title AS topic\_title, t.ex… --- ## [Can we export like record wrt to Users?](https://meta.discourse.org/t/can-we-export-like-record-wrt-to-users/263638)
**Author:** [@Anamika](https://meta.discourse.org/u/Anamika)\ **Replies:** 3\ **Last updated:** [May 28, 2023, 10:00am UTC](https://meta.discourse.org/t/can-we-export-like-record-wrt-to-users/263638 "2023-05-28T10:00:48Z")
How can I export the data for users like wrt to a post or discussion? The information required will include user ID, post or topic id and the like. --- ## [Why are user counts ~30% higher in GA4 vs. GA Universal Analytics?](https://meta.discourse.org/t/why-are-user-counts-30-higher-in-ga4-vs-ga-universal-analytics/266231)
**Author:** [@alancaster](https://meta.discourse.org/u/alancaster)\ **Replies:** 1\ **Last updated:** [May 25, 2023, 6:06pm UTC](https://meta.discourse.org/t/why-are-user-counts-30-higher-in-ga4-vs-ga-universal-analytics/266231 "2023-05-25T18:06:36Z")
We have a website that has Google Tag Manager (GTM) installed on it. We have a pageview tag that sends data to GA Universal Analytics, and separate pageview/configuration tag that sends data to GA4. Both tags use the exa… --- ## [What query to award a badge for liking or replying to a particular topic?](https://meta.discourse.org/t/what-query-to-award-a-badge-for-liking-or-replying-to-a-particular-topic/265926)
**Author:** [@sok777](https://meta.discourse.org/u/sok777)\ **Replies:** 1\ **Last updated:** [May 23, 2023, 3:37pm UTC](https://meta.discourse.org/t/what-query-to-award-a-badge-for-liking-or-replying-to-a-particular-topic/265926 "2023-05-23T15:37:44Z")
Is there a way to write a query to award a badge to all users who like a certain post/topic or reply to it? --- ## [An extra badge based on already granted badges number?](https://meta.discourse.org/t/an-extra-badge-based-on-already-granted-badges-number/178425)
**Author:** [@cosdesign](https://meta.discourse.org/u/cosdesign)\ **Replies:** 1\ **Last updated:** [May 23, 2023, 1:48pm UTC](https://meta.discourse.org/t/an-extra-badge-based-on-already-granted-badges-number/178425 "2023-05-23T13:48:50Z")
Hi. I was wondering if we could create a special badge for users already being granted with 30+ badges ( excluding system and discobot ). So far this is the closest query I’ve found but is not for this use. --- ## [Group assignments are stored by group name. How to handle group renaming?](https://meta.discourse.org/t/group-assignments-are-stored-by-group-name-how-to-handle-group-renaming/264573)
**Author:** [@ganncamp](https://meta.discourse.org/u/ganncamp)\ **Replies:** 7\ **Last updated:** [May 16, 2023, 6:08pm UTC](https://meta.discourse.org/t/group-assignments-are-stored-by-group-name-how-to-handle-group-renaming/264573 "2023-05-16T18:08:33Z")
We assign some threads to groups, and then the groups (theoretically) assign the threads to individuals for handling. We’ve created a report to show time (duration) to reassignment ▶ report to show time to reassignment T… --- ## [Exporting / Extracting Community Health Data](https://meta.discourse.org/t/exporting-extracting-community-health-data/263988)
**Author:** [@oneonetwothree](https://meta.discourse.org/u/oneonetwothree)\ **Replies:** 3\ **Last updated:** [May 8, 2023, 11:31pm UTC](https://meta.discourse.org/t/exporting-extracting-community-health-data/263988 "2023-05-08T23:31:19Z")
Is it possible to easily access the pre-computed Community Health data in a programmatic way (API, etc.)? Would love to be able to utilize the data without deriving the methodology used to calculate each and risking inco… --- ## [List of users with locked trust level due to group membership?](https://meta.discourse.org/t/list-of-users-with-locked-trust-level-due-to-group-membership/62455)
**Author:** [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)\ **Replies:** 17\ **Last updated:** [April 30, 2023, 3:16pm UTC](https://meta.discourse.org/t/list-of-users-with-locked-trust-level-due-to-group-membership/62455 "2023-04-30T15:16:16Z")
I was just investigating an issue with trust levels, and realized that a handful of moderators had their trust level locked and I don’t know why. Does this happen automatically when you promote someone to moderator or sh… --- ## [Who read posts at a certain time?](https://meta.discourse.org/t/who-read-posts-at-a-certain-time/248876)
**Author:** [@annekritchie](https://meta.discourse.org/u/annekritchie)\ **Replies:** 2\ **Last updated:** [April 29, 2023, 11:28am UTC](https://meta.discourse.org/t/who-read-posts-at-a-certain-time/248876 "2023-04-29T11:28:45Z")
I’m trying to determine which member could have read a certain post at a particular time of day. Is that possible? What if we have email to members available? I’m not sure if Discourse can track emails, but am curious if… --- ## [Topics stats of Solved/Unsolved topics, inc. 'time to solution'](https://meta.discourse.org/t/topics-stats-of-solved-unsolved-topics-inc-time-to-solution/275393)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 22, 2023, 12:36pm UTC](https://meta.discourse.org/t/topics-stats-of-solved-unsolved-topics-inc-time-to-solution/275393 "2023-04-22T12:36:53Z")
:warning: DRAFT This query would give solved/unsolved, the first reply | first replier | hours to first reply, as well as the solution post | solver | hours to solution (with category and ‘topic created since’ parameter… --- ## [Is there a SQL query to show how many people have used the new user tips?](https://meta.discourse.org/t/is-there-a-sql-query-to-show-how-many-people-have-used-the-new-user-tips/283096)
**Author:** [@Jagster](https://meta.discourse.org/u/Jagster)\ **Replies:** 4\ **Last updated:** [April 20, 2023, 7:05pm UTC](https://meta.discourse.org/t/is-there-a-sql-query-to-show-how-many-people-have-used-the-new-user-tips/283096 "2023-04-20T19:05:49Z")
Do we have any SQL-query or something to find out how many have used new user tips? Because if most of new users don’t use it we don’t know if it works, or if it useful as it is. --- ## [Who has marked a particular topic as Solved](https://meta.discourse.org/t/who-has-marked-a-particular-topic-as-solved/261876)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 16, 2023, 7:06pm UTC](https://meta.discourse.org/t/who-has-marked-a-particular-topic-as-solved/261876 "2023-04-16T19:06:41Z")
To look up who has marked a particular topic as Solved: -- \[params\] -- topic\_id :topic\_id SELECT target\_topic\_id, acting\_user\_id, created\_at::date FROM user\_actions WHERE target\_topic\_id = :topic\_id AND ac… --- ## [List of Solved stats per-user](https://meta.discourse.org/t/list-of-solved-stats-per-user/275391)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 16, 2023, 4:05pm UTC](https://meta.discourse.org/t/list-of-solved-stats-per-user/275391 "2023-04-16T16:05:00Z")
To produce a list with stats per-user, with a variable for time interval (defaults to 1 year): -- \[params\] -- string :interval = 1 year SELECT ua.acting\_user\_id, COUNT(CASE t.user\_id WHEN ua.acting\_user\_id T… --- ## [List each individual "solved" event](https://meta.discourse.org/t/list-each-individual-solved-event/275390)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 16, 2023, 3:10pm UTC](https://meta.discourse.org/t/list-each-individual-solved-event/275390 "2023-04-16T15:10:00Z")
To list each individual “solved” event you can use a query something like this: SELECT acting\_user\_id, target\_topic\_id AS topic\_id, target\_post\_id, created\_at::date FROM user\_actions WHERE action\_type=15… --- ## [Topics Voted On](https://meta.discourse.org/t/topics-voted-on/275399)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 16, 2023, 10:30am UTC](https://meta.discourse.org/t/topics-voted-on/275399 "2023-04-16T10:30:00Z")
Topics Voted On Returns the topic(s) and creation date of current and archived votes for a given user -- \[params\] -- user\_id :user SELECT tvv.topic\_id, tvv.created\_at::date AS "Voted On", CASE WHEN tvv.ar… --- ## [Vote Count Summary (per user) - selected user(s)](https://meta.discourse.org/t/vote-count-summary-per-user-selected-user-s/275397)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 16, 2023, 8:30am UTC](https://meta.discourse.org/t/vote-count-summary-per-user-selected-user-s/275397 "2023-04-16T08:30:00Z")
Vote Count Summary (per user) - selected user(s) -- \[params\] -- user\_list :users SELECT tvv.user\_id, SUM(CASE when tvv.archive = FALSE THEN 1 ELSE 0 END) AS "Current Votes Cast", SUM(CASE when tvv.archive … --- ## [Vote Count Summary (per user) - all users](https://meta.discourse.org/t/vote-count-summary-per-user-all-users/275394)
**Author:** [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\ **Replies:** 0\ **Last updated:** [April 15, 2023, 8:30am UTC](https://meta.discourse.org/t/vote-count-summary-per-user-all-users/275394 "2023-04-15T08:30:00Z")
Vote Count Summary (per user) - all users The following query gives the number of current votes, the number of archived votes, and the combined total, per user. SELECT tvv.user\_id, SUM(CASE when tvv.archive = FAL… --- ## [Can I change the threshold of the Solved badges?](https://meta.discourse.org/t/can-i-change-the-threshold-of-the-solved-badges/261877)
**Author:** [@profcaju](https://meta.discourse.org/u/profcaju)\ **Replies:** 3\ **Last updated:** [April 14, 2023, 6:05am UTC](https://meta.discourse.org/t/can-i-change-the-threshold-of-the-solved-badges/261877 "2023-04-14T06:05:40Z")
Is there a way to change the thresholds for the badges gave by this plugin? Or create new badges? --- ## [Get count of recent flags in a particular category](https://meta.discourse.org/t/get-count-of-recent-flags-in-a-particular-category/260411)
**Author:** [@tpsReports](https://meta.discourse.org/u/tpsReports)\ **Replies:** 3\ **Last updated:** [April 3, 2023, 8:44pm UTC](https://meta.discourse.org/t/get-count-of-recent-flags-in-a-particular-category/260411 "2023-04-03T20:44:06Z")
Hi all :grinning: I am working on adding a plugin to my Discourse instance that determines whether a certain number of flags were recently made in a category. It would ideally return the count of flags given in a catego… --- ## [Download all data in SQL](https://meta.discourse.org/t/download-all-data-in-sql/259608)
**Author:** [@Luiz\_men](https://meta.discourse.org/u/Luiz_men)\ **Replies:** 3\ **Last updated:** [March 27, 2023, 7:50pm UTC](https://meta.discourse.org/t/download-all-data-in-sql/259608 "2023-03-27T19:50:00Z")
Hi! I would like to know how I can download all my community data in SQL format. I searched here in the community but I didn’t find a post that talks exactly about this. --- ## [How-to export list of all topics in csv?](https://meta.discourse.org/t/how-to-export-list-of-all-topics-in-csv/259349)
**Author:** [@lvictor](https://meta.discourse.org/u/lvictor)\ **Replies:** 2\ **Last updated:** [March 25, 2023, 3:31pm UTC](https://meta.discourse.org/t/how-to-export-list-of-all-topics-in-csv/259349 "2023-03-25T15:31:29Z")
Haven’t found an answer yet for this – I’m looking to do a forum wide reorganization of categories. For recordkeeping and planning the reorg, is there a way to export a list of all topics on my forum into a CSV file? --- ## [Exclude users from analytics](https://meta.discourse.org/t/exclude-users-from-analytics/259155)
**Author:** [@NoamH](https://meta.discourse.org/u/NoamH)\ **Replies:** 1\ **Last updated:** [March 24, 2023, 9:36am UTC](https://meta.discourse.org/t/exclude-users-from-analytics/259155 "2023-03-24T09:36:40Z")
Is it possible to exclude groups like the admin group or specific users from the dashboard reports? --- ## [Question for fellow Data people here](https://meta.discourse.org/t/question-for-fellow-data-people-here/258382)
**Author:** [@jeremiahworkman](https://meta.discourse.org/u/jeremiahworkman)\ **Replies:** 1\ **Last updated:** [March 20, 2023, 12:59pm UTC](https://meta.discourse.org/t/question-for-fellow-data-people-here/258382 "2023-03-20T12:59:26Z")
I’m looking for documentation/logic on how the metrics in the admin section of our Discourse site are defined (users of different trust levels, views, webcrawler views specifically). I’m a data engineer whose company(Bu… [Previous page](https://meta.discourse.org/c/community-building/data-reporting/148.md?page=9) [Next page](https://meta.discourse.org/c/community-building/data-reporting/148.md?page=11)