Writer attrition: 1/3 per year · 4:49pm Nov 16th, 2014
Convert dates to Unix time:
$ perl -e 'use Time::Local; $x = timelocal(0,0,0,1,0,2013); print "$x\n"'
1357016400 [Jan 1 2013, in seconds since 1970]
$ perl -e 'use Time::Local; $x = timelocal(0,0,0,1,0,2014); print "$x\n"'
1388552400 [Jan 1 2014]
Number of people who've written since Jan. 1 2013:
sqlite> select count(distinct(uid)) from story where date_modified > 1357016400;
17082
Number of people who've written since Jan 1 2013 and wrote more than one story:
sqlite> select count(distinct(u)) from (select uid as u, id as id1 from story where date_modified > 1357016400 and exists (select id as id2 from story where uid = u and id2 <> id1));
8823
Number of people who wrote since Jan. 1 2014:
sqlite> select count(distinct(uid)) from story where date_modified > 1388552400;
10098
Number of people who've written since Jan 1 2014 and wrote more than one story:
sqlite> select count(distinct(u)) from (select uid as u, id as id1 from story where date_modified > 1388552400 and exists (select id as id2 from story where uid = u and id2 <> id1));
5947
("date_modified" is the date the last chapter was created, or maybe submitted or approved. It doesn't change when you edit a chapter.)
Fraction of writers who've written more than one story for fimfiction who wrote in 2013 but not 2014:
(8823 - 5947) / 8823 = .326
Writer half-life, in years y: (1 - .326)^y = .5, y = (ln .5) / (ln (1 - .326)) = 1.76
Also: Will you please agree that SQL is a stupid, stupid language?
How horribly depressing.
Is there any way to figure out reader attrition?
????
What's this for?
I'd also be curious how the numbers change if you take out all the authors that just wrote one story and then stopped. What's the attrition rate of the multi-story authors?
2598493 I was just working on that, but SQL is a horrible language.
2598493
Look at that! The change is utterly miraculous, isn't it?
EDIT: I came back because I dropped my keys. Here they are, on the keyboard, where I left them, silly me.
Wow! Look at all those authors who came back to write at hearing about the 2/3rds attrition rate!
2598604 Well, of course there are keys on your keyboard. It wouldn't work very well without them, would it?
What about going back further, to 2011 and 2012?
Not to complicate matters, but I will point out that some of the people writing since January 2014 who have not written more than one story will go on to write more than one story (that is, they are entering the fandom and have only published their first story so far.)
That being said, short of some kind of breakthrough that brings a huge influx of people (like one of the movies being widely distributed and a huge success, or the show being moved to a network with a wider distribution like Cartoon Network or Nickelodeon,) fandom is probably on the downward slope in terms of sheer numbers, across the board.
Hm. Are you getting a statistical falloff possibly due to people who are working on stories but have not published them until they're all the way done? Seems there might be more of that, at least with me. (Royal Exam is 80% done, but I'm not publishing until 100, Night Mares is 100% done waiting on cover art from Muffin. I've probably got 30 chapters of various stories on hold for editing or completion. Makes for much better stories.)
Well, I suppose I would count as one of the attrited writers, but I'm still here, still doing editing work for a couple people, and still swearing up down and sideways that I haven't abandoned the one story.
2599286
That's totally me too, save for editing. Or participating. Or doing anything much save dropping by, writing and erasing massive portions of my underwhelming overdue story chapters, and um... that's about it.
Math is humbling!
What about new authors, who weren't here in 2013 but have written more than one story in 2014?
You find that SQL is pretty good once you've tried all the alternatives.
2600206 No, man. I've used SNePS and Prolog. Now those are powerful query languages. But SPARQL is acceptable, IIRC.
I like query languages to take logical propositions as queries. Logic was designed, carefully, over many decades, by many people, to concisely express queries. SQL was not. SQL does some things well, and the ORDER BY / GROUP BY stuff is nice, and it's fast, but it just doesn't have the power of logic.
Fascinating, and a bit scary... It'd be interesting to see a deeper breakdown of the activity level of authors that disappeared, as well as the ones that stuck around.
I taught myself some SQL at my last job. I definitely don't have the chops to code up my own database but I can find my way around a complex select statement against some joined tables. That's the only relational language I know, and for the most part I liked my encounters with it. I'm probably just insane.
2600211
If those languages are more to your liking, I wonder why you find yourself using SQL. Is it just because there's more tooling?
An aside on perl command line fu:
-E acts just like -e, except it enables all optional features. Then you can save a couple keystrokes by using say instead of print.
-M*module* is the same as 'use *module*;', which is a few less keystrokes.
$ perl -e 'use Time::Local; $x = timelocal(0,0,0,1,0,2013); print "$x\n"'
becomes
$ perl -MTime::Local -E 'say timelocal 0,0,0,1,0,2013'
2600247
Yes. And more stability, reliability, & speed. And I've had to learn how to do everything with SQL.
I did not know that. Or I'd forgotten. Thanks.
2599062
Sort of. The fandom peaked in December 2013, at least according to Google Trends; at present, it is at about the same level as it was in the summer of 2013. If you look at the site statistics, there was a fairly sizable decline in the number of stories submitted since August of this year, though the trend may have started as early as July.
That being said, it is complicated by the fact that the number of comments is relatively stable and the number of ratings has actually gone up from years prior, with 2014 having a higher number of stories rated per week than in previous years.
So it is somewhat hard to tell for sure; it may just be the result of fragmentation, or it may be a true, long-term decline as the result of season 4 (interestingly, contrary to what people might have thought, the fandom increased significantly after Twilight got her wings; I was part of that rush).
We'll see if the trend reverses itself or not in the long term; Season 5 comes out next year, and MA Larson will be back, so...
I'm not terribly surprised by the loss of a third of the writers on the site per year, though it does mean you'd need several thousand writers each year just to break even. I suspect at the moment we are in something of a decline judging by the site statistics, though.
Clearly this means I need to start publishing 80 stories per week.
2600211
There is a database language called SPARQL? I was not aware.
Also, something something Twilight SPARQL something. You were all thinking it.
2600206 SQL does have nifty built-ins for sorting and displaying data that logical query languages don't, I'll admit that. Which is better depends on whether you're generating reports and doing very basic inventory-like stuff (SQL is more convenient), or doing research, machine learning, or modeling (logic is more powerful). SQL encourages people to think in simple patterns, and you usually have to write a bunch of code to do something with the data after you've retrieved it. I remember one case where dozens of SQL queries plus 20,000 lines of code could have been replaced with one or two pages of Prolog.
2602042
I was.
All of my actual questions are overruled by the urgent one: there's a public hook into an SQL server with FIMFiction site data?
Or is this super special friend-of-Knighty-fu?
2603783 Super special friend-of-Knighty. Heh. No. I think the only way Knighty has ever responded to any of my attempts to contact him was to reply to a comment once and tell me I was stupid.
Click on "New stories". Set "list view". Save that page. Now write a script that downloads that page, parses the story data out of it, parses the target of the "Next Page" link, sleeps for a few seconds, loads that page, and repeats until there is no "Next Page". That's about 1000 pages and 40000 stories. (It used to be a lot fewer pages, before the recent site update that puts less info on each page. Especially the new "watchers/watching" pages, which are now useless.)
It doesn't get the 'mature' stories, for which you have to set a "view_mature" cookie attached to your script's "browser".
2604961
Ah, I see, you created and populated a database yourself. So sql access to that information requires super-special-friend-of-Bad-Horse-fu.
Sounds like there are some interesting statistics to pull out of that raw data …
(Also, it's an interesting contrast comparing your stats against the site's own statistics for all published stories. If yours shows a peak and fall and FIMFic's stats show continued growth, this suggests that Mature stories are making up a lot more of the recent difference, which suggests some interesting things about the different tastes of the core of the fandom vs. the casual fandom.)
2600211 Yay Prolog!