Hi, everybody,
Please, could you tell me how can I access to history/temporal data about the pageviews per topic?
I tried it using the table top_topics, and topic_views, but none of them contains the complete results set. I need topic, date, and count x day…
Thanks a lot!
Alexandra.
I like this idea for a stock report here in data explorer, @HAWK can you assign this out to a tech advocate?
We still have the topic_views table which has tons of info, so we could just break this down per day.
Can you clarify what you mean by that? You can sort topics by pageviews in the topic list interface. Just click the views column header.
My understanding is that @Alexandra_Diehl wants to see that on Monday 100 people visited the topic and on Wednesday 32 people. We actually store data at that fidelity.
Though we do need a bit of an internal refactor cause we only count any given user once and any given IP once. A simpler rollup table would take up far less storage and give more interesting results.
Hi, Sam!
Thanks so much for your quick response!
Yes, that’s right. I am interested in understanding how specific temporal events affect pageviews. For example, when an influential user replies a post.
It will be fantastic to count with that info and I am very much looking forward to your news.
Cheers!
Hi Alexandra,
Until a refactor takes places, I believe the following is the best we can do:
-- [params]
-- date :view_date = 2019-03-26
SELECT t.id AS topic_id, COUNT(t.id) AS first_time_views
FROM topics t
JOIN topic_views tv ON t.id = tv.topic_id
GROUP BY t.id, viewed_at
HAVING viewed_at = :view_date
ORDER BY first_time_views DESC
You can specify a date using the field in the Data Explorer UI. The first column of the results will show linked topic titles with their reply count in parenthesis. The second column is the number of first time views of the topic on the day you specified.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.