Getting (Days Visited) in CSV User List Report

feature
1

Hi,

There is the column (Days Visited) that we see on the link:

https://LINK/admin/users/2/USERNAME

image

It would be nice if we can have this column in the CSV user list file generated from:
Admin Panel => Users Tab => “Export” Button

3 Likes
2

Not what you’re hoping to hear, but …

The User export file contains fields from the User table.
The “days_visited” field is from the User_Stats table.

It would be “anti-normalization” to have the same identical data more than once (eg. if the field were to be in both tables). IIRC what you see are the results of a JOIN query that does use the User table, but also other tables related to a user.

That said, with the Data Explorer plugin you should have no problem running such a JOIN query that generates an export file with that field included.

3 Likes
3

Not quite precise, the CSV file includes columns likes_given and likes_received which are also in the same table user_stats:

--
-- Name: user_stats; Type: TABLE; Schema: public; Owner: discourse
--

CREATE TABLE public.user_stats (
    user_id integer NOT NULL,
    topics_entered integer DEFAULT 0 NOT NULL,
    time_read integer DEFAULT 0 NOT NULL,
    days_visited integer DEFAULT 0 NOT NULL,
    posts_read_count integer DEFAULT 0 NOT NULL,
    likes_given integer DEFAULT 0 NOT NULL,
    likes_received integer DEFAULT 0 NOT NULL,
    topic_reply_count integer DEFAULT 0 NOT NULL,
    new_since timestamp without time zone NOT NULL,
    read_faq timestamp without time zone,
    first_post_created_at timestamp without time zone,
    post_count integer DEFAULT 0 NOT NULL,
    topic_count integer DEFAULT 0 NOT NULL,
    bounce_score double precision DEFAULT 0 NOT NULL,
    reset_bounce_score_after timestamp without time zone
);

From schema.sql

3 Likes
4

Thanks, right you are. It appears there is more than I remember having worked with before.

https://github.com/discourse/discourse/blob/master/app/jobs/regular/export_csv_file.rb#L11-L20

3 Likes
5

Thanks for pointing out the related file :smile:

Since topics_entered does exist in get_base_user_array function just as like other columns… does this seem to be a bug?

1 Like
6

There are fields I have been interested in that weren’t in the export (as of ±1-2 yr. ago). I assumed it was intentional and not a bug, but never checked further. The availability of exports became Admin-only, and no longer having real data to work with difficulty increased, interest waned, and a few heavy doses of RL ended that path.

Without looking a wild guess is that if not changed in core, a plugin could easily get values from tables and add them to the CSV export. But I think the Data Explorer plugin is a good candidate for a custom export.

My current interest for the most part has since moved from hacking of Admin-only core files to putting together bespoke userscripts.

Maybe with enough consensus the current export can be changed?

3 Likes
7

I’ve written the query required for that:

SELECT
    u.username_lower AS "username",
    stats.days_visited
FROM users u
LEFT JOIN user_stats stats ON stats.user_id = u.id
ORDER BY u.id

Though such data is actually useful in the user list report to get some insight.

Thanks @Mittineague :rose:

5 Likes