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

Data & reporting
1

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
2

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)

3

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
4

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

5

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
6

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.

7

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