Today it occurred to me to wonder what is the average number of days between posting chapters of my various fics. I whipped up some SQL to go against my relational database, and found the answer -- just 3.63 days! Wow, I post a lot!

Here's the SQL:

SELECT
   AVG("t"."next_chapter_date" - "t"."Chapter_date") AS avg_days_between
FROM (
   SELECT DISTINCT
      "Fact_Chapter"."Chapter_date"::date,
      LEAD("Fact_Chapter"."Chapter_date"::date) OVER (ORDER BY "Fact_Chapter"."Chapter_date") AS next_chapter_date
   FROM "Fact_Chapter"
   WHERE "Fact_Chapter"."Chapter_date" > '2023-02-01'
      AND "Fact_Chapter"."Chapter_date" <= now()
   ORDER BY "Fact_Chapter"."Chapter_date"
) AS "t"
WHERE "t"."Chapter_date" <> "t"."next_chapter_date"
   AND "t"."next_chapter_date" IS NOT NULL;

I needed to limit the dates in the subquery to start in February 2023, when I started posting "for real." (Later, I posted some backdates fics from YEARS earlier, but they were messing up the average.)

I also needed to limit the dates to only things posted before "now," for the same reason -- because I have a bunch of words dated to 12/31/9999, which really mess with the average if they're included.

One final refinement to fit my use case! I had multiple chapters of fics all posted on the same day, which was really bringing down my average. So I ruled out instances where the Chapter_date was equal to the next_chapter_date, and ran it without that.

Yesterday and today I spent a couple of hours getting my relational database back into sync. Somehow, I was 5 kudos off -- but it wasn't as simple as just missing a single day when 5 guests had left kudos on a single work. Oh, no. There were 14 different works that were slightly off -- one too many here, one too few there. Some of the discrepancies were in guest kudos counts, but others were in named kudos-leavers.

So I laid it all out in a spreadsheet, then went through systematically and resolved the discrepancies. In some cases, I'm certain that I mistakenly attributed a kudos to work "45" when it should have been work "42" -- those numbers matched up exactly. In other cases it was a bit more involved to untangle what the kudos log should have in it.

One interesting thing was that as I went through the list of kudos-leavers on a few of these works, I found some renames. That is, I had them logged as one username back when they left their kudos, but now their username is different. (I know it's the same user because of where it sits in the chronological list of kudos-leavers on my fic.) So I performed several renames in my database, although of course I'll never be able to get them all. It's very possible that someone could leave a kudos, I could log it, then they could change their name and leave another kudos on a different work, and I could log that too, thinking it's a different user. I would never discover the discrepancy unless I happened to be comparing the list of kudos-leavers against my database, which I'm not going to do for over 7K kudos, haha! So me catching those and correcting them will probably be the exception, not the rule. Then again, most people don't change their usernames.

Anyway! No one cares about this but me, but I'm writing it down anyway because I love my database. I'm regularly dumping all four tables to Excel and saving them to cloud storage, along with my SQL queries, in case something awful happens to this laptop and I lose it. That way, I could still reconstruct the database on another platform.

I would venture to guess that I'm one of the only people in the world who has built a relational database just to track her AO3 kudos and wordcounts. That makes me a giant in my field! (a giant nerd, and proud of it!)

I queried my database to find the top kudos-leavers on my fics, and here they are!

Coming in at #1 is the amazing Kiayla, who has left kudos on 48 of my 105 works, or 46% of them! I originally found Kiayla's work in the Naruto fandom a couple of years back and left her several kudos and comments on some of her stuff in the KakaIru ship, and I guess she followed me back, haha. But she doesn't kudos only my KakaIru fics -- her taste is more eclectic. That's great, because so is mine!

Two users are currently tied for second place -- Annvian and yongsanhotguy99, each at 28 kudos. They are both primarily interested in my Gingerpilot fics, of which I have written 26, and then they've branched out a bit more. Annvian left kudos on those twenty-six, and also on my silly piece of Poe Dameron 'art,' "Vote Poe Dameron!", and one of my original works, "How the Light Gets In." For their part, yongsanhotguy99 left kudos on those twenty-six, and also on my two Kylux fics, "Sharp as a Knife" and "Upper Hand."

Two users tied for third place -- sfvamp and irrationalgame, at 20 kudos each. I'm Discord friends with irrationalgame, who loves Gingerpilot probably even more than I do (and that's saying something!). Her boundless enthusiasm for generating new ideas has sparked inspiration in me more than once! We're in a Discord server together that seems to have started out as mostly about Kylux, but that also has a dedicated Gingerpilot channel, and other related content. It's extremely active, with multiple posts across multiple channels every day, and I really feel that I have made some friends there!

Next up is UtopiaPlanitia at 18 kudos, and then Saklani and MaudeZbornak, both at 17. All three of those people are friends through a different Discord server that's mostly about Gingerpilot. We have exchanged fics with each other, and sometimes, even physical cards and small gifts mailed to each other's homes for Christmas! It's pretty great to "know" people around the world through fandom. Sadly, that server seems to be winding down. People post less and less frequently. I keep hoping that it will revitalize, but at this point, far more activity happens on the first Discord server.

I deeply appreciate everyone who has left me a kudos, comment, bookmark, and/or subscription. I don't write "for" these tokens of appreciation, but gosh darn it seeing kudos and comments come in is VERY motivational for me to want to write more. If I were in a vacuum, writing just for my own self with no one else's feedback, I don't think I could have produced a fraction of my real-life output.

Kudos and comments (especially comments) really are writing fuel! :)

I had left things for over a month, but last night I finally sat down and did all the data work to catch up my relational database to real life. My kudos count now matches EXACTLY!!!

Seriously, it's really thrilling to query SELECT SUM("Kudos_count") FROM "Fact_Kudos"; and have the answer MATCH what AO3's webpage says it is! :)

I also caught up on entering the flurry of new works and chapters that I created recently while I was trying to get to 100 works by 2/25. The wordcount doesn't quite match there, because the announcement emails with the wordcounts in them are one thing, but sometimes I edit after the fact and then the words don't match for multi-chapter works (AO3 doesn't show wordcounts by chapter, only overall). So for those cases, I need to adjust my "Word Count Adjustment Chapter" entries for each affected work, and I haven't done that yet.

It's not super important, so I'm not too bothered about it.

Recently I added a couple of new columns to my Dim_Work table: Work_reveal_date, to go with Work_create_date; Work_blogged_about (a BOOLEAN column to show whether I've posted a "story blurb" entry about it here or not); and Work_rating (Gen, Teen, Mature, or Explicit).

I've been pondering creating a Fandom table and linking each work to that through a key, but I'd need a bridge table to handle works with more than one fandom, and so far I haven't felt like doing the work.

Same situation for creating a Ship table and linking each work to that through a key. I'd need a bridge table because one work can have more than one ship.

For now, the database does what I want. It allows me to chart my kudos and wordcounts posted (and now, wordcounts revealed!) over time, in ways that AO3 does not. Plus I can easily track users who have kudosed more than one work. My top kudos-er has left a kudos on 48 separate works of mine, and my #2 fan has left works on 23! Wow, I should send those guys a fruit basket or write them a cute little one-shot or something! :)

AO3 neglected to send me my kudos emails on January 16 and 17, which I noticed right away because I am a huge nerd. However, I'm able to recover what my kudos would have been on those two days, although I don't know exactly which people left kudos on which day.

I made a note of how many kudos each piece had as of 8 this morning, and the difference between each total and what my database thought it should be. Then I went into each piece's kudos list and made a note of (1) the usernames I saw that weren't in my database already, and (2) the count of guest kudos at that time.

After tonight's kudos email goes out, I'll be able to rectify the discrepancies. I will split the kudos evenly between the 16th and the 17th, since there's no way of telling anything more specific.

I did submit a question to the AO3 volunteer mod team asking if they have an API so I don't have to rely on their emails anymore, but I highly doubt they have one, or I would have found out about it when I was researching this project in the first place!

I thought I'd write a post on the relational database that I set up to track my kudos and works over time. I chose to focus only on kudos and works (including wordcount) because I can reliably get information on those pieces, via AO3 emails. I cannot get reliably timed info on Bookmarks, Subscriptions, or Hits, so I'm not even trying to track those. As for Comments, I considered adding them, but I've been getting comments for 20 months now, split over two email accounts, and pulling those out into a new table would be a project I haven't wanted to take on!

So for now, I only have four tables in a very bare-bones setup.
( Read more... )