Why has my report been running with the same results for months now? I’m running a report based on activity since the community’s inception. The community has been increasingly active this whole time. For example, one user shows in the query results as having 271 topics viewed, while their profile summary says 1.2k topics viewed.

Having a quick run of the query here on Meta the statistics do update when I change the ‘duration’ value, so it doesn’t appear to be getting stuck anywhere. I’ve also run it for a 90 day duration and checked it against a new-ish user whose main activity has been within that time, and their stats marry up with their summary. Is there something else I could check to see if I can replicate?

Maybe you could clarify something for me. I am trying to run a query starting 439 days ago (looking at the signup date of one specific user as a test) and choosing the duration date as the same number of days. I was under the impression this would include all their participation data since their signup. However, the data does not reflect that. How would I achieve that?

It also looks like a User Participation Statistics query from 439 days ago until now only includes members who were already registered and active at that time. It does not include participation data of members who registered after and were active within that time frame. Can someone help me customize the report to create one that captures the data I need? SQL is something I know only at a surface level.

Looking at the report, it seems to be in visits order with the highest at the top - so anyone who joined up midway through the time window you select will be somewhere down the list (and the visible list is limited to 1000, though I think you can get 10,000 if you export the results as a CSV). We can come up with a custom query though. :+1: What data are you looking for exactly?

[image] JammyDodger: I think you can get 10,000 if you export the results as a CSV We don’t even have that many members…yet, so all good there. [image] JammyDodger: We can come up with a custom query though. *yay * [image] JammyDodger: What data are you looking for exactly? One thing that would be helpful to clarify, is how each column is calculated. There are discrepancies between the CSV and what’s on a member’s Profile Summary page (note: we’re only comparing those two different sets of data where the query dates match the lifecycle of the member’s enrollment). participation stats for all members with 1 or more visits, regardless of when during the query period they joined stats are pulled at each month’s end to show the accumulative participation since the launch of our new community. We thought, given the :from_days_ago and :duration_days time frame of the query, that was what data it had been producing.

[image] michellefs: One thing that would be helpful to clarify, is how each column is calculated. There are discrepancies between the CSV and what’s on a member’s Profile Summary page (note: we’re only comparing those two different sets of data where the query dates match the lifecycle of the member’s enrollment). I’ve been having a explore of it this morning to see if I can find out more. Mine times out on me when I try and go back 498 days (to my start date :slight_smile:), but I have managed to get one for a year so I can roughly compare it to the User Stats. :+1: (give or take a few hours worth of data) [image] [image] There is a difference for posts_created, topics_created, likes_given and likes_received between the two - and taking a closer look at the SQL it seems the User Participation Statistics query doesn’t exclude deleted posts, whispers, or PMs, which may account for it. The topics_viewed and posts_viewed seems pretty accurate for both though. :+1: I’m not sure why some of your users aren’t showing up? The only exclusionary criteria I think I see is that they must have read more than 0 posts in the time window - which should include pretty much everybody, apart from staged users. pr AS ( SELECT user_id, COUNT(1) AS visits, SUM(posts_read) AS posts_read FROM user_visits, t WHERE posts_read > 0 AND visited_at > t.START AND visited_at < t.END GROUP BY user_id I’ll keep digging and see if I can come up with more. :slight_smile: :+1:

My discrepancies are the reverse: the query numbers are smaller, not larger. [image] When I enter 441 in :from_days_ago and 441 in :duration_days I get these (below). Compared to this report for the same timeframe (at least…for the highlighted user). [image]

:woman_facepalming:t4: I realized that a few minutes before your response, hahaha. Thank you for your patience. … I changed :from_days_ago to 0 and everything in life makes sense!! :partying_face: It appears my brain was working chronologically with regard to what :duration_days meant. I thought that :from_days_ago was the first day where the query would pull data, then continue to do so, working chronologically in time (meaning data from 441 days ago, 440 days ago, 439 days ago, and so forth until present time). It appears it pulls data in reverse chronological order. Got it!

Ha. No worries. :slightly_smiling_face: I learnt quite a bit from studying that report so it was still pretty useful. :slightly_smiling_face:

Inconsistent stats from the "User Participation Statistics" query

Community Building Data & reporting

JammyDodger November 16, 2022, 5:16pm 10

I think you need it to be 0 and 441 if you want to have the window from today going back 441 days?

Topic		Replies	Views
User participation Data & reporting sql-query	0	236	November 17, 2017
User_stats with a proper time stamp Data & reporting sql-query	7	1235	March 2, 2020
Participation Histograms Data & reporting sql-query	0	182	November 17, 2017
Add "named group" as a scope for the "User Participation Statistics" script in data explorer Marketplace	0	739	April 15, 2019
Stats for group members Data & reporting sql-query	4	354	July 18, 2018

Inconsistent stats from the "User Participation Statistics" query

Related topics