Spreadsheets & Planners

[Cuchuilain]

In @Dansak’s recent post about endings, it transpired that some of you were using spreadsheets to keep track of your stories. Why the hell wasn’t I doing this? I'm into that!

and…, after about 3 days down the spreadsheet rabbit-hole, I’ve started wondering how the rest of you are doing it?

I’ve started with 1 tab per story that I’m working on, 1 row per chapter therein, and a column for each of the number of views, likes, and when it was written. Then I Color-coded for where I thought a chapter could be usefully added.

Then I thought, maybe I should have a sheet for each character in a story with all their attributes, motivations, major actions etc.

Then I started wondering how you keep the spreadsheet up to date. Would you schedule a curl script to keep the numbers right and have a macro import them?

Then I just stared at the screen for a while and thought help!!!

So for the organized among us, what do you use for your tracking and planning and how do you do it?

 

[pwizdelf]

and…, after about 3 days down the spreadsheet rabbit-hole, I’ve started wondering how the rest of you are doing it?

If it's any consolation my answer for this is, pretty clumsily actually considering how in my "real" life I live and breathe Excel. That said I do have plenty of thoughts. Most of my scripting stuff has been VBA and oriented to ebook formatting or other bulk cleanup operations in Word (since I hate Word and don't want to do anything by hand in it that I don't have to). Stuff like replacing inline formatting with named styles for the ebook import, updating and cleaning up named styles so they're consistent in every doc, shit like that.

In Excel most of my efforts have started and ended with, chapter listing with current wordcount and brief notes. Zilch for automation.

I like the idea of being able to scrape a directory of Word files and refresh/pick up metadata.

Solving problems like this is part of my day job bread and butter so it's pretty ridiculous that I haven't created a better solution for this. I think it's because I'd rather be writing than doing stuff that feels like work shit? Dunno.

Then I just stared at the screen for a while and thought help!!!

Y'know, honestly I'm not always even sure spreadsheets are the right solution for this. Then I run into indecision based on, I'd love to share whatever I come up with, with people, but if it's predicated on something that costs or isn't easily accessible then what good does that do them? I've been thinking about this recently too and I think with the amount of shit I have in play right now I like the idea of feeling like I have this at least partially wrangled.

So at work usually the first thing I do is determine needs and wants for the solution so we draw up what must happen, what constraints we know we have, and priorities for the various requirements. What if we kinda did that?

I think for me, just to get off the blank page, things I want to do generally (spreadsheets probly-definitely not sufficient for all of these):

• Track wordcount and not have to update that manually because human error.
  
• Reflect structure the same way
  
• Have a way to maintain an at-a-glance summary of chapter but also of general thread so I can roughly remember on my crazybranching stories just
• Bonus for anything that actually stores full text (plus formatting if possible) and is therefore searchable or that I can paste into chapters on CHYOA
  • Fields for tags so I can tag consistently and copy/paste to CHYOA, that'd be cool
• Track characters and track which ones appear in what chapters.
  
• Track themes or specific things I want to remember for future callback or other reasons, track unresolved "stuff" for a given thread.
  
• Not eat a lot of time having to maintain and do planning shit (I think this is my biggest pitfall).

So I'm just tossing some thoughts out while I wait on an import to finish loading for work - but the more I think on this the more I'm wondering if an Access database would be my best bet. That has me back in inaccessible/technically more difficult shit that is harder to share with other people. Bleh. I had an etsy shop once though and I used Access to maintain my designs and listing descriptions in addition to published listing links.

 

[Zeebop]

Okay, so the way Lois Lane's Night Out is organized is that there's a threshold on Likes.

For the first two options (Blaze and Lex Luthor) I'd do two options for each chapter through chapter 8, and after that point when the likes hit a certain threshold (5 likes) I'd write a sequel chapter, and when likes hit a second threshold (originally 10 likes, now 15 likes) I'd add another. 20 chapters was the "End" depth, so the thresholds increased: originally 10 for 1 sequel and 20 for 2 sequels, now 15 for 1 and 25 for 2.

So when I first set up the spreadsheet, there was a page for each depth of chapter (i.e. all the chapter 8s are on page 8), there are links to the chapter in one column, likes in the second column, and bookmarks are tracked in the third column as asterisks. The idea being that I could sort the columns (first by likes, then by bookmarks) and immediately see which chapter I needed to write a sequel to.

To aid in this, I used a color-coded system: chapters that hadn't yet hit the threshold are left white, chapters that hit the threshold and need a new chapter are marked green, those who have one option written are yellow, those who need a second chapter written are orange, and those with two (or more!) sequels are marked red.

Because I can't see bookmarks on chapters I haven't written myself, chapters from other authors are marked with their name (e.g. Unknown7's chapters are marked "Unknown7" in the bookmark column). I have to check those manually, since I don't get notices when people like their chapters, to see when and if they get a sequel.

I've finally reached the point where I'm actively working to write endings for branches that never hit the threshold for continuation. To remind myself when this happens, I've got another column where I mark an "x" next to premature ending chapters, starting from Chapter 18 and working backward. When subsequent likes let a chapter hit the threshold past where I'm doing the premature endings (currently working my way through Chapter 15s), I mark the new chapter with a "y" in the same column, to distinguish it. When I finally finish up all the endings, I'll go back and do the same for the "y" chapters...and then, hypothetically, the story will be done.

So...that's the system. The deeper the story got, the more pages I added. It works okay, the main drawback being that it doesn't track the connections between different branches - that is, I didn't preserve the relationships between chapters. If I want to know what chapters preceded a given chapter 8, for example, I have to load up the story map. (Yes, there are ways around this, but there are currently 6,146 chapters and I am not going back and adding all those links in).

 

[Zeebop]

Writing it out like that seems more complicated than it is. The basic fundamentals are:

1) Most popular branches get new chapters soonest.

2) Likes and bookmarks are used to determine which chapter gets a new option.

3) Threshold on when a new chapter is triggered and how many new chapters I spin off of an old one.

Back before the pandemic when I was writing 6-8 chapters a day, the spreadsheet was very useful. Then...um...several people stuck in lockdown decided to do an archive binge. And I would have up to 600 notifications a day. Which meant I spent a lot of time updating the spreadsheet instead of writing.

 

[Cuchuilain]

@Zeebop - that is seriously disciplined, but I like the idea that the readers need to engage enough to make a following chapter happen. I'll need to think of how I select my thresholds though as I find views/likes tend to fall away the deeper into a story I get

My observation is that if a story has X chapters and I am on chapter Y. It is likely to have something like (X-Y)+n likes where n is a small constant dependent on how good an idea the story was in the first place. e.g. Say I have a linear story with 40 chapters, and its relatively well received, so has a n-constant of say 6. Therefore Chapter 1 will have 46 likes, Chapter 2 will have 45 etc, down to Chapter 40 having just 6 likes. At that point it doesn't look like Chapter 40 deserves a follow-up, but if I write a further 5 chapters it will pass the threshold and deserve it. The only problem is that now Chapter 45 doesn't deserve one.

BTW I'm really admiring your work rate. 6-8 chapters a day? Wow. and I'm already thinking I better start on this year's Halloween story soon if I want to get it done on time.

@pwizdelf - yeah, I'd like to crack the nut regarding the automating of the like-count update etc and if I do I would like to share it too.I think your bullet point list pretty much captures the spec of what I'd like to get from this too. You may be right about spreadsheets not being the best option here. I have a bit of a phobia of databases though - my natural inclination would be to pull the data into another set of webpages that includes data telling you what needs worked on the most. I'm still swinging back and forth on what's the solution, but you both have actually really helped focus this. The first thing to get straight is the requirement. So, I think your list and Zeebop's threshold alerts are the first to consider.

 

[Zeebop]

@Zeebop - that is seriously disciplined, but I like the idea that the readers need to engage enough to make a following chapter happen. I'll need to think of how I select my thresholds though as I find views/likes tend to fall away the deeper into a story I get

Views are always going to be heavier on the earlier chapters, and often there's a bias toward older chapters or longer/more established branches. That said, the way the thresholds are set up it's not hard for a branch to go from dead to getting several new chapters if interest suddenly picks up - while other branches languish for weeks, months, or years without a single like. Such is life.

BTW I'm really admiring your work rate. 6-8 chapters a day? Wow. and I'm already thinking I better start on this year's Halloween story soon if I want to get it done on time.

Well, that was during the height of the pandemic when I was in lockdown. These days I'm lucky if I can manage 2-3 - mostly because the Ending chapters are usually longer and require more work. But I churned out all 40 chapters of BAD SEX in a month, while still doing LLNO every day.

 

[Cuchuilain]

I'm still bewildered by your productivity.

I've attached a sample file (Summary.txt) where I'm trying to organize a short story. If you save it and rename it as html then stick it in a browser, could you let me know what you think.Its not very automated at the moment, and obviously the descriptive text needs to be entered manually but I think this sort of thing maybe ticks some of the boxes. Not sure what to do about a larger story with lots of branches though. Perhaps it would need multiple html files with links between them, It may still be too unwieldy, but I thought I'd put it out there for comment anyway.

@pwizdelf I think the main thing its missing is wordcount? I'll have a think about that. Maybe there's a widget somewhere for that. As regards scanning, I usually keep a backup directory where I keep a copy of each html file once I've uploaded them, so I can scan that directory if need be.

 

[Zeebop]

@Cuchuilain With a straight linear story, a table works fine for chapter summaries or keywords - I always kick myself for not having some little symbol or note that describes what state of undress Lois is in for any given chapter - you would not believe how many times I've had to go back to see whether I'd gotten her bra and panties off yet before writing a sex scene.

When you get into branches, however, is when spreadsheets tend to show their worth. You could treat each branch as a separate page, if that makes it easier to keep track, for example.

 

[pwizdelf]

I always kick myself for not having some little symbol or note that describes what state of undress Lois is in for any given chapter - you would not believe how many times I've had to go back to see whether I'd gotten her bra and panties off yet before writing a sex scene.

THISSSSSS

 

[JWtts]

Not sure if it would help for some of the details in tracking Likes, word count, etc. but in terms of trying to plan or just brainstorming different threads (Door #1 or Door #2?), I added the MindMup app/extension to Google Drive just to tinker with some ideas and basic outlining.

Obviously I haven't used it yet for a story, but the few times I've played with story ideas it's helped give me a more visual perspective of the story, the directions it could go, links to other chapters, etc. There's also color-coding which could help in visualizing good-vs-bad decisions, exposition -vs- sex-position chapters, etc.

 

[pwizdelf]

I'm still bewildered by your productivity.

I've attached a sample file (Summary.txt) where I'm trying to organize a short story. If you save it and rename it as html then stick it in a browser, could you let me know what you think.Its not very automated at the moment, and obviously the descriptive text needs to be entered manually but I think this sort of thing maybe ticks some of the boxes. Not sure what to do about a larger story with lots of branches though. Perhaps it would need multiple html files with links between them, It may still be too unwieldy, but I thought I'd put it out there for comment anyway.

@pwizdelf I think the main thing its missing is wordcount? I'll have a think about that. Maybe there's a widget somewhere for that. As regards scanning, I usually keep a backup directory where I keep a copy of each html file once I've uploaded them, so I can scan that directory if need be.

So I've been background-churning on this topic since JW first posted yesterday. I don't know how much closer I am to anything resembling an answer but I did reach a few personal conclusions.

One, @Cuchuilain has awakened me to something I thought I'd never be middle-management enough to genuinely appreciate: dashboard analytics. Hmm.

This is a crappy markup below to illustrate but I think I realized one of the things I'd love is a tool that would help me with this situation:

I worked a long day doing thought intensive shit. I have 75 minutes until I need to go do some responsible adult shit like make food and eat it. So I want to get a little high and jump right in to some fucking or some teen drama. Don't want to waste time picking a thread, re-orienting myself to it, and remember where I left all the stuff. What open places do I currently have and what is the basic lead up to each of these? AKA a menu of points where it makes sense to possibly add a chapter. It would be great to have something that let me basically identify my major story threads in summary clusters that help me remember which thread something is, and basic continuity details. What were they wearing, what sex acts have they done so far, what's their comfort level, who knows what secrets, etc.

Incidentally, "remember where I left all the stuff" is also my biggest hurdle with my other hobby so I guess I'm just disorganized as fuck in my personal life.

I'm going to continue thinking on this. I'm going to be workig on a bunch of system import shit today so maybe I'll use the loading/downtime to try to think more unconstrainedly about what I actually need versus what I think I need. So far @Cuchuilain's HTML dash has me possibly closest to what I think would be useful and I didn't expect that at first. I agree, the threading thing adds an additional layer of challenge.

Anyway, being able to summarize for each unresolved point of continuance like so would be a huge deal:

A: Straight to It Thread: Abby and Pat both smoke a little extra weed just before he reveals what he did to her awful ex boyfriend to get even on her behalf and they have impulsive sex in Pat's room. Ready for new next morning chapter. They talk in bathroom and begin coming to terms with things. Status of variables: morning after pill: acquired and taken, Creampies: 3, Sam status: missed his Friday night text, Sam comes over as planned. Pat unaware that Sam and Abby deflowered each other. Sam not aware they had sex. General vibe, less fumbling, they already kinda went straight to the finish line so there's not much to do other than accept it. Matter of fact kinda attitude at this point, oddly not maybe as conflicted as other threads.

 

[gene.sis]

So when I first set up the spreadsheet, there was a page for each depth of chapter (i.e. all the chapter 8s are on page 8)

Wouldn't it be easier to use the story map order of chapters and add a column for the depth of a chapter?
Then, you could apply a filter for a certain chapter depth you want to work on.
It should also be possible to calculate how many child chapters a chapter has. (The calculation would spot the next chapter with the same depth and then count all chapters with depth +1 between itself and the detected chapter.)

 

[Zeebop]

Wouldn't it be easier to use the story map order of chapters and add a column for the depth of a chapter?
Then, you could apply a filter for a certain chapter depth you want to work on.
It should also be possible to calculate how many child chapters a chapter has. (The calculation would spot the next chapter with the same depth and then count all chapters with depth +1 between itself and the detected chapter.)

There are a lot of things I could have done, but the focus was being able to filter which chapters hit the threshold by chapter depth. For me, at the time, the easiest and simplest way of doing that was multiple pages as I had done - I wasn't really concerned about maintaining the parent-child relationships between chapters (that's what the story map is for!), but about being able to prioritize my writing. I was (and am) less concerned about branch depth than meeting my arbitrary thresholds for "oh, I need to write this now."

Which is to say, I wasn't looking to optimize all the data and relationships. There are absolutely a lot of different ways to enter and organize the information, I was just focused in on what made sense to me at the time.

 

[pwizdelf]

So the last couple weeks on and off I've been working on a sort of dashboard (not beta-ready & wanted to make sure it's allowed and not bad for the site to share something like that or to use a script that could increase page loads on more than just my small scale. Page load volume per each completed runtime would be number of chapters + 2). The primary script lets the user provide a story URL and then it compiles fresh data for that story from the branches stemming from that point and then it writes the results to a new worksheet. Basically, gather data and then reconstruct the story map in an excel table instead of on a website. Was considering adding some KPI type calcs. Views/Likes ratio. Some kind of aging/decay stats maybe.

It's super crude right now and all the user input stuff except the link is direct assignments in my unedited code but ultimately if I were to share I'd probably make a settings sheet to control certain aspects of capture. I have a lot of testing and refinement/code cleanup ahead so it's not like it's some spectacular thing but I was feeling kind of good about it because I FINALLY got it to traverse the nodes correctly today and I was feeling a lot more like there was a point to even messing with it. I thought I'd share some screen shots. Not sure if this is something other people would want to mess with. It's pretty slow. Around .4 seconds per chapter so runtime adds up but it's for sure faster than me copy pasting the story map into Excel and cleaning it up and deleting HTML crap I don't want etc etc.

The one major caveat, since it's loading each page and scraping the source code for the chapter data elements, and my script doesn't have authentication/login, it only sees what a not logged in user would see. AKA for writers who want to analyze their own stories, it won't show the unpublished ones. I had an idea though for another script based on just saving the story map page while logged in and then parsing the saved file. Then maybe that can show the unpublished ones.

Other ideas I had.

• Maybe have it identify distinct threads of multiple chapters in the same timeline and their subthreads. Somehow make that where it can be easy for a writer to label different threads and keep track of them.
  
• Pick up the chapter tags too (I meant to but forgot) for potential analysis with stats or something.
• Write chapter content & HTML formatting to a text file in same directory as workbook???
  
• Word usage analysis that excludes pronouns and shit, lets user determine what words they care about
  
• Allow writer to search recently refreshed story text if they're trying to figure out what chapter such and such happened in or check on a detail. Then click directly to its view/edit/whatever link
• Pick up more of the overall story metadata (faves etc) for another dashboard page maybe.
  
• make a link to click on the author from the workbook

Anyway I was feeling pretty happy with it today and thought I'd share some screen caps. My husband I think found it a bit mind blowing that this is what I've been working on so feverishly. A thing that parses ONE story website for purposes of statistical shit I wanted to know after hearing the way you guys plan and make decisions on where to focus your time but without maintaining the data support for that so manually to where it's eating time that could go to writing. Ironic considering I've been sacrificing my writing time for this lately... hah.

 

[Cuchuilain]

Amazing!

 

[zankoo]

Holy shit, this is cool. I'm a nerd about formulas in Excel and Google Sheets, but this goes WAY beyond my abilities.

 

[insertnamehere]

So the last couple weeks on and off I've been working on a sort of dashboard -SNIP-

Impressive, but I have to ask: is that Visual Basic, and if so, why?

 

[pwizdelf]

Impressive, but I have to ask: is that Visual Basic, and if so, why?

"If so, why" BAHAHAHHAHAHAA I kind of wondered if I was going to get asked that.

The main reason is that on paper my background is in finance but before that there was engineering school. In my first staff accountant job fifteen years ago the only development environment I could get on my work computer was the delivered VBA for Office Applications. So I was like, this is better than nothing. As a result the language I have the most comfortable goto solution experience with is VB scripting for office because they don't lock down the Office developer environment in our MS org security policy.

TLDR: Because 3/4 of a software engineering degree and finance employee level technical resource access at work.

 

[pwizdelf]

Adding an idea to my feature brainstorm list based on something I got annoyed about tonight. Something like, for each chapter have a field that captures the last x words or characters of the story contents. User defines x. User maybe also has an option to define an override value for x by chapter ID if they want to maintain a custom table that preserves exactly the pull text they want for each chapter. Sounds fussy to me but maybe I find out otherwise.

Point of this. I want to write a chapter following one and I have the main story beats but I don't remember, were they talking, what was the mood? My notes just say "TJ miller joke kink talk." WTF does that mean. I go back to the chapter and look at the end to see what's the deal. OK great. I see giggles. I see a lull in the conversation. Great. Now I'm closer to getting off the blank page because I'm refreshed on the vibe and what just happened in the last seconds of the chapter.

So I liked the idea of storing this text sample and having it maybe not prominent by default but you can get to it easily to see where you're needing to pick up. Then I'm not having to navigate to a chapter and scroll to the end.

I was thinking, what will I call that field. Ah, keep it simple I thought. "END SNIP." Soon as I typed it, not the winner. It sounds like a bris gone distressingly awry. SNIPPET it is. Know that this particular specimen is an example of the popular heirloom variety red-headed SNIPPET.

I've been working this week on interacting with my recreated story map and interpreting some of the data and getting a sense for what might be useful. I will share when it's a bit less lying in pieces with no discernible structural approach.

Oh! OK, I just thought of something else too. Idea 2 is, I think it'd be cool to have an option that if you have multiple snapshot worksheets already for that story, to have one that compares the two and summarizes what changed. With some variation on a detail drill if I can do it. Stuff like, overall story fact changes (X number of chapters added (link or maybe hover to see list of detail), Y number of chapters deleted, N new authors appeared, Favorites increased by Z. etc etc) and then similar for chapters on their stat changes. KPI changes maybe. Maybe user does a control panel section to specify what they care about.

Jesus. Am I actually doing this shit. Why!

 

[zankoo]

Jesus. Am I actually doing this shit. Why!

Let me know if Jesus gets back to you. I'm curious what he says.