Alexandra_Diehl is seeking to access historical/temporal data about pageviews per topic, including date and daily count. sam suggests creating a stock report in Data Explorer, using the topic_views table, which stores data at a daily level. However, sam notes that a refactor is needed to simplify the data and provide more accurate results.
Alexandra_Diehl clarifies that she wants to understand how specific events, such as an influential user replying to a post, affect pageviews. tshenry provides a temporary solution using a SQL query that can be used in the Data Explorer UI. The query allows specifying a date and returns the topic ID, title, and number of first-time views on that day.
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.