Fuwanovel "Like" Stats

[Abyssal Monkey]

This seems to be more popular than I thought. Anyone know a good software to create stats.fuwanovel.net with?

 

Say I export a weekly set of data from the forums in CSV or JSON or whatever that we can feed to that software for custom chart creation based upon it?

Sadly, not.  I mean it's probably possible to use a google spreadsheet and do some things with that, but other than that I don't really have any experience in that side of things 

 

If you do decide to export the data in some format, be sure to include CoC posts as well, people can get hyper inflated likes by only posting in those threads too, not to mention just in general the amount of postage that happens there.

 

Personally, I'm pretty sure its just a gimmick Flutterz put up on a whim, so all the fun will die out in probably a week max.

 

For all the people too lazy to sort the data on their own, here is the automated spreadsheet I just created. It currently gives likes given and likes received, and likes recieved and given in what threads.  Will probably add more in a little bit.

 

Now If I could only get the damn arrayformula function to work in google sheets everything would be dandy. For some reason

=Arrayformula(countifs(Fulllikes!E:E,M2:M,Fulllikes!H:H,$C$1))

doesn't work. I want to drop the countifs function from M2 to the bottom of the sheet instead of having to drag for thousands of cells  

 

Edit: Lol... The 2m cell limit is really restricting 

[Roach]

Likes Given:

Anyone with 2 or less likes wasn't included.

[Zakamutt]

Well, nobody seems to be a huge fan of me in particular, but a decent lot of people have liked one of my posts. Embiggened charts a bit because I am a 1337 hax0r (not really though, it lets you drag the corner even when you're not supposed to).

 

Also, 31 of my likes are for posts not picked up by the crawler (I think I received a few since it was run so maybe ~28 actually).

[Abyssal Monkey]

Well, nobody seems to be a huge fan of me in particular, but a decent lot of people have liked one of my posts. Embiggened charts a bit because I am a 1337 hax0r (not really though, it lets you drag the corner even when you're not supposed to).

 

Also, 31 of my likes are for posts not picked up by the crawler (I think I received a few since it was run so maybe ~28 actually).

That's end user, not the actual sheet.  Refresh the page and it's back to normal.

 

Anyway, we can now see that 4% of your likes come from your 2000 post thread:

 

I'm thinking about adding in a general stats page about Thread specific statistics, like overall thread like hoarding and specific thread like counts, but because the specification of threads is a pain in the ass, I'm pretty much not tempted too.  Maybe if Nay actually makes stats.fuwa he can integrate a button on the thread page that sorts it or something, but as it stands they are too long/convoluted.

[Nayleen]

Here's the data directly from the source, because I can:

 

https://docs.google.com/spreadsheets/d/1_ROR8EgiFY2ILjPogR05vKzKarBnqdSsPzVIozA8Pcc/edit?usp=sharing

 

As you can see I've excluded some forums, mainly staff discussions.

One big, ugly SQL query to fetch that data, btw:

SELECT
	f.id AS forum_id,
	f.`name` AS forum_name,
	t.tid AS topic_id,
	t.title AS topic_title,
	p.pid AS post_id,
	r.member_id AS giver_id,
	m1.members_display_name AS giver_name,
	p.author_id AS receiver_id,
	m2.members_display_name AS receiver_name,
	r.rep_date AS "timestamp",
	FROM_UNIXTIME(r.rep_date, '%Y-%m-%d %H:%i:%s') AS "date"

FROM fuwaforum_reputation_index r

INNER JOIN fuwaforum_members m1 ON (r.member_id = m1.member_id)
INNER JOIN fuwaforum_posts p ON (r.type_id = p.pid)
INNER JOIN fuwaforum_topics t ON (p.topic_id = t.tid)
INNER JOIN fuwaforum_forums f ON (t.forum_id = f.id)
INNER JOIN fuwaforum_members m2 ON (p.author_id = m2.member_id)

WHERE /* only positive ratings in forums */ (r.rep_rating > 0 AND r.app = 'forums')

AND /* not in one of the hidden or staff forums */ f.id NOT IN
	(15, 75, 76, 80, 82, 83, 84, 89, 90, 91, 92, 93, 95, 96, 105, 106, 107, 108, 112, 115, 116, 117, 119, 120, 122)

ORDER BY rep_date ASC;

Also, that inconsistency with IPB's field names.

[Flutterz]

So pretty much the same stuff I already got.