Nach der Anzahl der Content-Shares im letzten Monat suchen

Gemeinschaft Daten & Berichterstattung
1

Ich arbeite an einigen Abfragen für das Management, und eine davon betrifft die Anzahl geteilter Inhalte, also wenn jemand einen Link teilt und dieser geklickt wird. Hier ist mein Ansatz, aber er liefert eine Zahl, die die Plausibilitätsprüfung nicht besteht – 80.000. Übersehe ich etwas?

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

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

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).