The discussion revolves around querying the number of content shares in the last month. jerdog initially shares a query that yields an implausible result of 80k shares. Mittineague suggests verifying the numbers by querying the topics/posts tables and questions the definition of a “share” (e.g., whether multiple clicks on the same post count as one or multiple shares).
After re-examining the query, jerdog finds a more reasonable number of 5k shares by counting distinct post IDs. However, the original 80k number still seems high, implying that each shared link was clicked 16 times on average. Mittineague suspects that the 80k number may include anonymous (not logged-in) visits, which are often higher than logged-in visits. jerdog remains skeptical about the 80k clicks from shared content, even considering anonymous visits.
I am working on a few queries for management, and one they want to know relates to the number of shared content, i.e. someone shares a link and it’s clicked. Here’s what I created but it’s giving me a number that doesn’t pass the plausibility check - 80k. Am I missing something?
-- [params]
-- string :interval = 1 month
-- string :trunc = month
with t as (
select
date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) as start,
date_trunc(:trunc, CURRENT_TIMESTAMP) as end
)
SELECT count(id) as "Total"
FROM incoming_links i, t
WHERE i.created_at between t.start and t.end
If you query the topics / posts tables do the numbers match up?
Maybe you’re not asking the right question? eg. if there is a link to a post and it gets clicked 100 times, does that count as 1 (the same post) or 100 (the clicks)
No, wanting to know how many times shared content gets viewed (clicked) in a month - and I am not buying that in a month where I have only about 300 new topics created (through either the share a link to this post option or the timestamp link - intentional activities) I am having 80k visits from that content. It certainly seems high unless it’s including crawler content?
Right - so that’s a more reasonable number (5k) but I am now trying to rationalise what I am seeing… So correct me if I am wrong here:
80k (counting each click from a share)
5k (counting the number of posts shared)
Both of course are distinctly different, and I get that, but the 80k number still doesn’t make sense to me. If I read that correctly, that means that links which were shared (5k) got clicked 80k times in the past month, incidentally accounting for ~20% of all our pageviews, and 40 times more than our user visits. That is the stat which doesn’t make sense to me as I am not seeing that behavior
I’m almost certain the 80K must be including anon (not logged in registered members) visits. Not necessarily bot crawl visits, which AFAIK, are not counted.
I don’t know about other forums, but at SitePoint the anon numbers are consistently higher then the logged in numbers.