Caught my database up to real life!
Jul. 23rd, 2024 10:20 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
artemisdartI was keeping a little relational database on my local machine to track any kudos I receive, when I start new works, when I add chapters to new works, how many words I posted... things like that. I'm populating the database by hand, mostly using the emails that I get from AO3, since I'm subscribed to myself, lol.
Anyway, when my last laptop died, I couldn't manage to move the database to my new machine. I could go on and on about the technical difficulties, but let's not go there. Suffice it to say that I was pretty cast down, thinking I had lost all that great data. It makes it difficult to do trending over time... le sigh!
I complained about it in the "A Ship Awakens: Star Wars" Discord server, and lo and behold -- pretty much as soon as I posted in #vents to complain about it, I managed to solve my own problem. (I just had to keep the old dying laptop on long enough to query each table and download as .csv, then upload those to Google docs... from which location they are now accessible to my new laptop!)
I downloaded the software and created a new database and imported the data (cleaning up a few things along the way that I had thought better of after the first time). But then it was about 5 months out of date.
So I spent time over the past 5 days or so getting it all caught up with the new works and chapters I have posted, the word counts of each chapter, and all the kudos. As of about half an hour ago, it's all caught up, and I'm so pleased!
Now I can answer questions like --
etc. etc. etc. Yay!
SO. NEXT, I need a good front end for all of this. I researched a bit, months ago, but I couldn't find anything that was free, that would connect to my local postgresql database, and that had the tools I wanted. Irksome! I think I'll have to settle for Excel, which makes me sad, because Excel is not visualization software. But any port in a storm, I suppose.
I managed to get an Excel workbook to connect to my postgresql and bring in the data in the form of tables. After that -- my expertise runs out. I'm a Power BI and Tableau kind of person -- I've never had to build charts and graphs in Excel, and quite honestly it suuuuuucks for this, in my opinion. So I'm over here trying to figure out the simplest things, like how to insert an effing bar chart that does what I want. This shouldn't be so difficult!!!
All of this is a lovely distraction from actually writing, of course... ;) (But sometimes I'm just not brimming with creativity. That's when I turn to these more analytical distractions.)
Anyway, when my last laptop died, I couldn't manage to move the database to my new machine. I could go on and on about the technical difficulties, but let's not go there. Suffice it to say that I was pretty cast down, thinking I had lost all that great data. It makes it difficult to do trending over time... le sigh!
I complained about it in the "A Ship Awakens: Star Wars" Discord server, and lo and behold -- pretty much as soon as I posted in #vents to complain about it, I managed to solve my own problem. (I just had to keep the old dying laptop on long enough to query each table and download as .csv, then upload those to Google docs... from which location they are now accessible to my new laptop!)
I downloaded the software and created a new database and imported the data (cleaning up a few things along the way that I had thought better of after the first time). But then it was about 5 months out of date.
So I spent time over the past 5 days or so getting it all caught up with the new works and chapters I have posted, the word counts of each chapter, and all the kudos. As of about half an hour ago, it's all caught up, and I'm so pleased!
Now I can answer questions like --
- What's my best day of the week to receive kudos?
- Do I get more or fewer kudoses on weekends?
- Do the kudos I get correlate more strongly to the number of new works, or the sheer overall wordcount I'm posting?
- What were the kudos totals for each of my works on Day 10 of their existence?
- How many people have left kudos on more than one work? More than three? More than five?
- How many people have left kudos across different fandoms / ships?
- How many challenges have I participated in so far? How many kudos have I received for each challenge?
- Most / least popular ships, regardless of the number of works?
- Most / least popular challenges?
etc. etc. etc. Yay!
SO. NEXT, I need a good front end for all of this. I researched a bit, months ago, but I couldn't find anything that was free, that would connect to my local postgresql database, and that had the tools I wanted. Irksome! I think I'll have to settle for Excel, which makes me sad, because Excel is not visualization software. But any port in a storm, I suppose.
I managed to get an Excel workbook to connect to my postgresql and bring in the data in the form of tables. After that -- my expertise runs out. I'm a Power BI and Tableau kind of person -- I've never had to build charts and graphs in Excel, and quite honestly it suuuuuucks for this, in my opinion. So I'm over here trying to figure out the simplest things, like how to insert an effing bar chart that does what I want. This shouldn't be so difficult!!!
All of this is a lovely distraction from actually writing, of course... ;) (But sometimes I'm just not brimming with creativity. That's when I turn to these more analytical distractions.)
