Getting (Days Visited) in CSV User List Report

Contribute Feature

AI-generated summary

Getting Days Visited in CSV User List Report

YaserAlnajjar requested the addition of the “Days Visited” column to the CSV user list report generated from the Admin Panel. Mittineague explained that the “Days Visited” field is part of the user_stats table and not the users table, which is why it’s not included in the export. However, YaserAlnajjar pointed out that other columns from user_stats, such as likes_given and likes_received, are included in the export.

Mittineague acknowledged the inconsistency and suggested using the Data Explorer plugin to run a custom query that includes the “Days Visited” field. YaserAlnajjar found the related code in export_csv_file.rb and suspected it might be a bug.

Mittineague suggested that a plugin could be used to add custom fields to the export, or that the Data Explorer plugin could be used to create a custom export. YaserAlnajjar wrote a sample query to include the “Days Visited” field in the export and expressed appreciation for the discussion.

1

Hi,

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

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

image
image446×105 2.04 KB

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.

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