Aggregated click number on forum click counter

feature
1

Hey,
So I know that in the forum there is a click counter for every post’s link.
Is there a way to see the total number of clicks for the whole forum? (Other than going manually through each and every post and counting them).

Thanks!

2

There’s the “Last 30 days” at
Admin -> Dashboard

2 Likes
3

That’s not quite the same thing, I think Tiffany is asking about outgoing clicks. Those are incoming links.

We don’t have a global “most clicked link across all topics on the site”, but the data is present in the database and would probably be easy enough to query out.

1 Like
4

Thank you Jeff, this is exactly what I meant.

Is there a place where I can see how to find/ query this data in the database?

5

I believe what you’re interested in is the topic_links table (in /db/structure.sql)

CREATE TABLE topic_links (
    id integer NOT NULL,
    topic_id integer NOT NULL,
    post_id integer,
    user_id integer NOT NULL,
    url character varying(500) NOT NULL,
    domain character varying(100) NOT NULL,
    internal boolean DEFAULT false NOT NULL,
    link_topic_id integer,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    reflection boolean DEFAULT false,
    clicks integer DEFAULT 0 NOT NULL,
    link_post_id integer,
    title character varying(255),
    crawled_at timestamp without time zone,
    quote boolean DEFAULT false NOT NULL
);

In the Rails console you could do something like
TopicLink.order("clicks DESC").limit(1)
which gives this from my localhost Discourse

=> [#<TopicLink id: 2, topic_id: 18, post_id: 18, user_id: 2, url: "/faq", domai
n: "localhost:4000", internal: true, link_topic_id: nil, created_at: "2013-01-29
 05:25:59", updated_at: "2013-01-29 05:25:59", reflection: false, clicks: 1, lin
k_post_id: nil, title: nil, crawled_at: nil, quote: false>]
2 Likes
6

Ah, notice in the previous internal: true that should be in the query for false.

I did some testing on my localhost Discourse and got this from the same query as previous so this is the table you want

=> [#<TopicLink id: 80, topic_id: 86, post_id: 128, user_id: 27, url: "http://co
mmunity.sitepoint.com/", domain: "community.sitepoint.com", internal: false, lin
k_topic_id: nil, created_at: "2015-03-27 18:14:34", updated_at: "2015-03-27 18:1
4:34", reflection: false, clicks: 4, link_post_id: nil, title: nil, crawled_at:
nil, quote: false>]
3 Likes
7

I will try that, thank you so much!!