Tracking topic views history

Data & reporting
1

Hi!
I’ve been searching for a while but couldn’t find anything similar, not even in the cool Data Explorer queries.

I want to see the history of views/visits that a specific topic has received for a period of time.
Ideally, I’d like to have something like this, but for each individual topic:

image
image1354×521 73.9 KB

I don’t mind if it’s just a table day by day or if it can include a graph (I can build that outside Discourse). I’m also really interested in having all visits (user & anon).

This would help me track and analyze topics usage within certain periods of time (in where I expect more or less activity).

Do you know if is there some data explorer query for this?
Thank you in advance! :raised_hands:

1 Like
2

Hi @Ayelen_Rives :wave:

Maybe try this:

-- [params]
-- int :topic_id = 
-- date :start_date = 
-- date :end_date = 

SELECT COUNT(*) AS views
FROM topic_views
WHERE topic_id = :topic_id
AND viewed_at BETWEEN :start_date AND :end_date

you will need to specify the topic id (from the topic URL) and the date range parameters in YYYY-MM-DD format.

2 Likes
3

Hi Lilly!

It’s useful! I’ll make some research in order to separate data day by day, because it sums up all views during period.

Now I know where to begin :raised_hands: :mag_right:
Thanks!!

1 Like
4

Glad that helps you. Let us know if you need more specific queries or more help with it :slight_smile:

5

I’m afraid I have recently found out that the topic_views table only records the very first view of a topic by a user and not one topic view per user/ip per day.

Unfortunately this makes doing a time-based breakdown on views a little skewed, and can only be done on ‘viewed for the first time’.

1 Like
6

Well then the table should be called topic_first_views :sweat_smile: :upside_down_face:

Sorry @Ayelen_Rives , I tried :woman_shrugging:t2: :crying_cat_face:

edit: Found a relevant discussion:

3 Likes
7

What a pity! Agree with Lilly that the table should be called topic_first_views for disambiguation.

Thanks for the info and thanks Lilly for the try! :raised_hands:
I’ll keep looking on that discussion.

3 Likes