# User stats wrong/not up to date

**URL:** <https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429>\
**Category:** Data & reporting\
**Created:** [December 17, 2016, 4:22pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429 "2016-12-17T16:22:58Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![steko](https://avatars.discourse-cdn.com/v4/letter/s/c77e96/32.png) [@steko](https://meta.discourse.org/u/steko)\
**Post date:** [December 17, 2016, 4:22pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/1 "2016-12-17T16:22:58Z")

</div>

I noticed something’s off with the stats of the _Users_ page at my small forum (it’s really small, yes):

[https://discorsi.openarchaeology.eu/users/](https://discorsi.openarchaeology.eu/users/)

If I’m not mistaken, various numbers shown for the default view (last week) and other views (last month) are wrong or not up to date, showing actions (likes given and received, messages) that do not reflect the actual activity of those users.

The view for last year seems correct though. I’m not sure there is a way to force a reset of the stats?

---

<div class="post-metadata">

**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\
**Post date:** [December 17, 2016, 5:56pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/2 "2016-12-17T17:56:25Z")

</div>

See /sidekiq/scheduler

And search for “stats”.

---

<div class="post-metadata">

**Author:** ![steko](https://avatars.discourse-cdn.com/v4/letter/s/c77e96/32.png) [@steko](https://meta.discourse.org/u/steko)\
**Post date:** [December 17, 2016, 6:19pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/3 "2016-12-17T18:19:35Z")

</div>

Mhh, I see just 3 items:

```plaintext
Jobs::DashboardStats 11 minutes ago OK 189ms in 18 minutes 	
Jobs::AboutStats 5 minutes ago OK 72ms in 23 minutes 	
...
Jobs::CategoryStats 17 hours ago OK 258ms in 7 hours 	

```

All seem to be working correctly.

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [December 17, 2016, 9:00pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/4 "2016-12-17T21:00:23Z")

</div>

I’m currently digging around a Users pages post count issue,

> [@Are post read counts per user accurate?](https://meta.discourse.org/t/are-post-read-counts-per-user-accurate/54096/10):
>
> I had SkipGuy1 (née ScrollGuy1) use a topics replies columns “jump button” to go directly to the last post of a couple of never before entered 38 posts topics. His post count in the Users page went up 76 posts, his profile post count went up by 2 Chasing down the elusive posts read value was an adventure. The Users page gets the posts\_read value from directory\_items directory\_items gets the value from user\_visits The value in users\_visits is set from user.rb user.rb gets the value from top…

A lot of it is over my head and I’ve run into to some road blocks, but I am getting things narrowed down some.

If I see anything that could be a problem with data in addition to the code related to post count I’ll be sure to take note of those too.

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [December 18, 2016, 3:51am UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/5 "2016-12-18T03:51:19Z")

</div>

One thing interesting I noticed is that directory\_item.rb has

```plaintext
all: 1,
yearly: 2,
monthly: 3,
weekly: 4,
daily: 5,
quarterly: 6

```

Damn enums! Aarrgghh!

I would intuitively think that quarterly would be between yearly and monthly.

I know if i was writing code involving period\_types I would be prone to making a mistake because of that.

I don’t know if that has anything to do with your problem but I suspect it very well may.  
… back to the code …

---

<div class="post-metadata">

**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\
**Post date:** [December 18, 2016, 2:47pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/6 "2016-12-18T14:47:20Z")

</div>

I would guess that quarterly got added after daily and there was no way to re number everything.

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [December 18, 2016, 10:27pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/7 "2016-12-18T22:27:31Z")

</div>

If you have [Data Explorer](https://meta.discourse.org/t/32566?silent=true) this query might help you to analyse results.  
It’s dirty and not easy to interpret, but it “works”

Note - the directory items “all” uses the user stats table

```plaintext
--[params]
-- string :user_name = eviltrout
-- int :results_limit = 60

WITH us AS ( SELECT user_stats.user_id 
        , user_stats.likes_received AS us_lr 
        , user_stats.likes_given AS us_lg 
        , user_stats.topics_entered AS us_te 
        , user_stats.topic_count AS us_tc 
        , user_stats.post_count AS us_pc 
        , user_stats.days_visited AS us_dv 
        , user_stats.posts_read_count AS us_pr
      FROM user_stats 
      )
, di AS ( SELECT directory_items.user_id 
        , directory_items.likes_received AS di_lr 
        , directory_items.likes_given AS di_lg 
        , directory_items.topics_entered AS di_te 
        , directory_items.topic_count AS di_tc 
        , directory_items.post_count AS di_pc 
        , directory_items.days_visited AS di_dv 
        , directory_items.posts_read AS di_pr
        , directory_items.period_type AS di_pt 
      FROM directory_items 
      )
, u AS ( SELECT users.id
        , users.username AS u_u 
        , users.last_seen_at AS u_ls 
      FROM users 
      )
SELECT 
    us.user_id 
    , us.us_lr 
    , di.di_lr 
    , us.us_lg 
    , di.di_lg 
    , us.us_te 
    , di.di_te 
    , us.us_tc 
    , di.di_tc 
    , us.us_pc 
    , di.di_pc 
    , us.us_dv 
    , di.di_dv 
    , us.us_pr 
    , di.di_pr 
    , CASE WHEN di.di_pt = 1 THEN CURRENT_DATE - interval '1000 days' 
           WHEN di.di_pt = 2 THEN CURRENT_DATE - interval '365 days' 
           WHEN di.di_pt = 3 THEN CURRENT_DATE - interval '30 days' 
           WHEN di.di_pt = 4 THEN CURRENT_DATE - interval '7 days' 
           WHEN di.di_pt = 5 THEN CURRENT_DATE - interval '1 day' 
           WHEN di.di_pt = 6 THEN CURRENT_DATE - interval '90 days' 
           ELSE CURRENT_DATE END AS di_di_pt 
    , CASE WHEN (CASE WHEN di.di_pt = 1 THEN CURRENT_DATE - interval '1000 days' 
                WHEN di.di_pt = 2 THEN CURRENT_DATE - interval '365 days' 
                WHEN di.di_pt = 3 THEN CURRENT_DATE - interval '30 days' 
                WHEN di.di_pt = 4 THEN CURRENT_DATE - interval '7 days' 
                WHEN di.di_pt = 5 THEN CURRENT_DATE - interval '1 day' 
                WHEN di.di_pt = 6 THEN CURRENT_DATE - interval '90 days' 
                ELSE CURRENT_DATE END) < u.u_ls THEN '<' 
           WHEN (CASE WHEN di.di_pt = 1 THEN CURRENT_DATE - interval '1000 days' 
                WHEN di.di_pt = 2 THEN CURRENT_DATE - interval '365 days' 
                WHEN di.di_pt = 3 THEN CURRENT_DATE - interval '30 days' 
                WHEN di.di_pt = 4 THEN CURRENT_DATE - interval '7 days' 
                WHEN di.di_pt = 5 THEN CURRENT_DATE - interval '1 day' 
                WHEN di.di_pt = 6 THEN CURRENT_DATE - interval '90 days' 
                ELSE CURRENT_DATE END) > u.u_ls THEN '>' 
           ELSE '=' END AS di_u 
    , date_trunc('day', u.u_ls) AS u_u_ls 
FROM us, di, u  
WHERE u.u_u ILIKE CONCAT('%', :user_name, '%') 
AND di.user_id = us.user_id 
AND u.id = di.user_id 
ORDER BY us.user_id, di_di_pt 
LIMIT :results_limit 

```

the member name can’t be empty or it won’t run the query  
“limit” should be in multiples of 6

EDIT  
If you would rather import than copy-paste

[users-page-stats.dcquery.json](https://global.discourse-cdn.com/meta/original/3X/d/9/d98d0dd6b51bcb47d58545a19cab561aaca20386.json) (3.3 KB)

---

<div class="post-metadata">

**Author:** ![steko](https://avatars.discourse-cdn.com/v4/letter/s/c77e96/32.png) [@steko](https://meta.discourse.org/u/steko)\
**Post date:** [December 19, 2016, 9:39pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/8 "2016-12-19T21:39:26Z")

</div>

Thanks a lot for providing this query. As you wrote it’s not easy to interpret but I’m confident that the results returned by the query match those seen in the users page, especially looking at a few users that have been inactive for many months now.

Is it possible that the case when `u_u_ls > di_di_pt` results in wrong stats?

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [December 19, 2016, 11:08pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/9 "2016-12-19T23:08:47Z")

</div>

> [@steko](#):
>
> Is it possible that the case when u\_u\_ls \> di\_di\_pt results in wrong stats?

Except for the Users “all” that uses the user\_stats table, the other periods pull from other tables.

Because the Users page gets updated by a sidekiq job that AFAIK runs once daily, there could be a difference between when the query is run and whether or not the sidekiq job has run.

I don’t test for either on my localhost, so it might be that members that visit via the API or email get or not get counted as having been “seen”

As best as I can tell I don’t think it is a problem if the users last seen is greater than the directory item period, but it could be when stats (other than Likes received) are present in directory\_items (the Users page) in a period more recent than when the member was last seen.

That is, the di\_di\_pt \> u\_u\_ls is more likely to indicate a discrepancy than a di\_di\_pt \< u\_u\_ls is

---

<div class="post-metadata">

**Author:** ![wolftune](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/wolftune/32/103569_2.png) [@wolftune](https://meta.discourse.org/u/wolftune)\
**Post date:** [March 30, 2018, 3:59am UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/10 "2018-03-30T03:59:50Z")

</div>

I’m wondering if there’s just a delay or what, but I visited our users list just now, and “All Time” shows 1 less total users than “Today”. There’s a new user who just joined, and they show in “Today” but not in any other time-frame of the user list view… seems buggy, but maybe it just needs time to update…

UPDATE: seems so far to have just resolved itself with some delay

---

<div class="post-metadata">

**Author:** ![Mark\_Schmucker](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mark_schmucker/32/124810_2.png) [@Mark\_Schmucker](https://meta.discourse.org/u/Mark_Schmucker)\
**Post date:** [December 12, 2019, 10:21pm UTC](https://meta.discourse.org/t/user-stats-wrong-not-up-to-date/54429/11 "2019-12-12T22:21:35Z")

</div>

My monthly stats show almost no activity for the past two days, which is wrong. The other periods (week, quarter, and year) work properly. I’ve seen this behavior a few times, and it resolves itself eventually.
