How I Created an Automatic Writing Tracker for an Online Entrepreneur
I created an Automatic Writing Tracker for an entrepreneur called Primoz Bozic (www.primozbozic.com). Part of Primoz’ business is helping entrepreneurs write more effectively and produce more content to help grow their business.
He had an existing Writing Tracker spreadsheet that helped his readers keep track of how much they were writing per day, per week and per month. It also included some helpful stats like:
⦁ Total Writing Time
⦁ Average Session Length
⦁ Total Word Count
⦁ Average Words per Hour
⦁ Average Energy
⦁ Average Focus
But he felt that it was a bit “clunky” to use and thought there was a better way to automate and present the data. He also wanted help creating auto-generating graphs that could provide his readers with helpful analytics that gives them deeper insights into their writing.
This post will go through the process of how I brought Primoz from:
We’re going to cover a few things:
⦁ How the tracker was at the beginning
⦁ The process of creating the tracker
People who know what they want their spreadsheets to do, but aren’t sure how to make it happen, wonder how I’m able to create my spreadsheets.
The process is quite simple:
Fortunately, Primoz didn’t have to worry about my well-being because I handled the creation of the automated tracker.
Original Writing Tracker
Primoz had already created the basic structure of the tracker when I started working with him. He had 4 tabs on the spreadsheet:
⦁ Tracker Tab
⦁ Daily Stats
⦁ Weekly Stats
⦁ Monthly Stats
As it was originally, it was a pretty good tracker already and his readers were finding it useful. But he felt that something more could be done with it, and that’s where I could help him.
Lets go through each tab and see how his tracker functioned originally.
This tab allowed users to record each writing session they had. There were formulas in here, but I noticed that some of the things the users needed to input could be automated.
Every time the user would have a writing session, they would input: date, day of the week, session number, document name, start time, end time, energy, focus, words start and words end.
This Tracker tab is the only tab the user needs to input data into, on both the original and new version of the tracker. All of the other tabs refer to the data entered here, so it’s really the core of the spreadsheet.
Daily, Weekly and Monthly Stats
These tabs summarised the data on the Tracker Tab at daily, weekly and monthly views. This allowed the user to see how they’re performing over time.
The main thing I noticed on the original version was that there were lots of cells visible that weren’t being used on each of the tabs. I thought it could look a bit neater.
Also, if a user started half way through the year, that meant on the daily, weekly and monthly tabs that there would be loads of blank cells at the top – for the period the user didn’t input any data. For example, if they started in June then the daily, weekly and monthly tabs would show January to May at the top but they would have no data in them.
The Process of Creating the Tracker
I have three main steps in helping people create their dream spreadsheets:
1. Idea Creation
I will go through each step and provide more detail of how I was able to help Primoz build his new Writing Tracker.
The “ideas phase” is about getting to know the project more, and coming up with ideas.
It starts with a client telling me a few things about it:
⦁ What the spreadsheet is and how it’s used
⦁ Any current issues with it (if they have an existing spreadsheet)
⦁ What cool new features they would like
Firstly, I needed to understand how Primoz got to the stage where he was currently at, so I had to create what he had done so far in my own spreadsheet.
This got me more familiar with the project, and it gave me some time to think of some potential ideas:
It’s always good to get a second pair of eyes on a project and to hear what ideas they have – it might be something you never thought about.
So, I thought about some cool features, and sent them over to Primoz to see what he thought.
We went back and forth a few more times and he told me which ideas he liked, which he didn’t, and some new ideas he had thought of.
At this point we had made some really good progress on the project. I enjoy this “idea” stage, because it’s where the project gets created before I have really started work on it.
Once Primoz and I had got to the end our “idea producing” stage, I then needed to convert walls of text into a more structured and easy-to-read format.
This gives us both a summary of all the features that are going to be added, any current issues to fix, and any concerns / specific details about new features.
It’s now much easier for Primoz to see what his tracker is going to contain, and lets him do a final check before I make a start to see if there’s anything that needs changing.
When I first start working on building a spreadsheet, I need to prioritise which sections need to be completed first.
In a spreadsheet, there is often a “core” where all of the automation, analysis and other tabs are referring to.
The beginning stage of production is building this core, and it’s important to get feedback from the client once it is completed. This is because if there are any changes to make to the core, it’s much easier to do it before more advanced features have been built around it.
Tracker and Stats
In Primoz’ Writing Tracker, the core was the “tracker” tab, and the daily / weekly / monthly stats summarise that data. All of the ideas we came up with, all of the extra features that were eventually added to the spreadsheet, revolved around this starting point.
It can also be useful to test some of the advanced features on a really small scale at this stage. If things don’t work as expected, it’s easier to add additional things into the core that lets you build those advanced features around it later on. Rather than having to go back to the work you did at the beginning and make changes.
Because Primoz had an existing spreadsheet and he had some issues with it, I built the core of the spreadsheet and sent him an update a few days after starting:
With this first progress update, I had rearranged the tracker tab to make it a bit more user-friendly when inputting data. Rather than having blank cells mixed in with filled in data at the end of a session, a user would have a “start” input and an “end” input. I also improved the way the tracker tab fed into the summary tabs.
Here you can see that data has only been entered up until March 26th 2019. With this progress update, the weekly stats only show data up until the last date that data was entered. It also worked for the first date that data was entered for – which means the summary is auto-generating and doesn’t have to be set up for a full year, like it originally did.
The dates that have been entered into the tracker in this example are from 02/21/2019 to 03/26/2019. You can see this in the below weekly summary:
This auto-generating summary feature was also built for the daily and monthly summary tabs.
I like to involve my clients with the development process, so it’s created exactly for their needs. Sometimes issues come up and I can think of a couple of solutions and the impact each solution would have, so I send these details across to my client. I like my client to tell me which option they like best, or at least be involved in or aware of the decision. Or other times, there isn’t an issue but there is a choice of a few preferences.
Once I had completed the core of the spreadsheet, in this case the tracker, I sent it over to Primoz so he could test it and make sure he was happy with it.
Analysis and Graphs
Now that I had completed tracker and stats tabs, I could move onto doing the analysis and graphs.
In trying to improve and being efficient with the way you spend your time, it becomes clear that what works for one person might not work for you. A lot of life is being willing to experiment and seeing what works for you… so that you build up a “portfolio” of optimised practices for yourself. And it’s the same with finding the best writing practices for yourself.
The analysis and graphs parts of this tracker takes the guess work out of what works for you, and is based on all of the writing data you have inputted – and gives you interesting insights about your writing.
Because I had already planned all of the analysis at the beginning and structured it in a table, I just had to go through each graph and create it. Although I wouldn’t be designing a spreadsheet if I didn’t run into a couple of problems along the way.
Primoz wanted the graphs to be auto-generating so I needed to create these with this in mind. Once I had completed the graphs, the spreadsheet was really beginning to take form.
The analysis graphs allows the user to discover interesting insights about their writing. It has 4 columns: words per hour, total word count, focus, energy. Each one of these columns is what we’re interested in analysing, and there are various things we analyse them against: day of the week, time of writing, session length, number of sessions, energy and focus.
So if you wonder “What time of the day is my words per hour highest?” you can have a look on this tab and see all of your words per hour for each time of day.
It’s important to realise that when you don’t have much data inputted, you could draw incorrect conclusions from these graphs. For example, if you have only got 3 different times of day to go by after a week of writing… you might think that 4pm is your best time to write. But after 2 months of data, you might see that 4pm is actually only your 5th best writing time.
I would recommend waiting until you have at least 6 weeks of data before you look at the analysis graphs to be able to optimise your writing. Once you have sufficient data, you can begin making changes to your writing habits.
You might notice that a certain day of the week is your highest words per hour, and you might schedule that as your main “writing day” for a few weeks. Run an experiment and see what effect it has!
The real power in these analysis graphs comes when you have 6 months+ worth of data and it has plenty of data to draw from. It will really paint an accurate picture of what your best writing habits could be. And you can keep fine-tuning your best writing habits over time using the Writing Tracker.
Sometimes it’s nice to take a step back and look at your biggest achievements. The leaderboards allows you to do this. You can see for the current week, the current month and for the year what your best session was. It finds what the best session is based on: word count, words per hour and session length.
You might even look at what made that session so great. Was it a certain type of document? Was your energy through the roof?
Or it could serve as motivation. If you’re not having a great writing week and feeling a bit down, you can look at your leaderboards to see what you are capable of. And work on beating your “top score”!
As Primoz and I were seeing the tracker and its various tabs take form, we got various new ideas which we thought would be cool to build in.
In addition to the analysis graphs, Primoz also wanted to add in some totals graphs. This lets you zoom out and see the big picture of how you’ve been doing.
It has three levels of perspective: days (last 30 days), weeks (all data) and months (all data). And lets you track: total word count, average words per hour, total writing time and number of sessions.
With a quick look at these graphs you can understand exactly how you’ve been performing recently.
The document stats gives a summary of the data for each document. Similar to the daily / weekly / monthly stats, but instead of grouping by time periods – it groups by the document name instead.
This could allow you to see which types of documents you write really easily and which ones you struggle more with.
The top document section is on the “leaderboards” tab and shows you the top 5 documents of all time for words per hour and total word count.
This is a quick way of seeing your largest documents and which documents you’ve wrote quickest.
Daily Writing Stats
As you are using the tracker each day, it’s useful to know how you’re doing on that day so far. This is helpful if you often write during multiple sessions throughout the day as it gives you a quick summary.
The writing streak on the tracker tab counts the number of days in a row you have currently wrote for. If you miss a day, this number will reset.
Also on the leaderboards tab, there is a longest writing streak which shows you the longest number of days in a row you have wrote for.
Primoz also wanted conditional formatting completing to be able to see from the colour of the cell how objectively “good” data is.
He wanted 5 colours, where the progression is from “worst” to “best”: Red > Orange > Yellow > Light Green > Green.
Here you can see it in action on the tracker tab.
Because the data is summarised at different levels, this meant that the ranges were different on some of the tabs. For example, a good total word count for “day level” would be different to what a good total word count for “month level” would be.
I also included a table that shows all of the different ranges that the conditional formatting is based on. If you’re curious how close you are to the next range, you can have a look at this tab.
And that’s it!
That’s how I helped Primoz with his tracker.
Judging by the number of times he said “awesome” and “amazing” in an email to me, I think he is pretty happy with how it turned out:
⦁ This is AWESOME man. You have no idea how excited I am about this spreadsheet. It’s amazing what you’re doing with it!
⦁ Woah man this is amazing! I love the pivots! Those stats are incredible.
⦁ So far, it looks AWESOME and so exciting to analyze my writing!
⦁ Man, I’m SO excited about this tracker. It’s insane. I always dreamed of having a tracker like this, and it’s amazing to see it come to life!!
I love helping people take a vision of how they want something to be, and working with them to bring it into reality.
For me it’s satisfying to take a step back and see the spreadsheet working magically, and see the progress that the spreadsheet has made.
Don't want to miss out on future content?
Subscribe to my email list
Have a spreadsheet in mind?
Let me take care of it for you