Looking to find the number of content shares in the last month

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 (
    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?

1 Like

What do you get if you temporarily change the count(id) to counting distinct post_ids?

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

1 Like

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.

Yeah… makes sense, though again still not making sense that 80k clicks in one month are from shared content (anonymous or not).