When I signed up to Primoz Bozic’s email list last month, I wasn’t expecting to have a video call with him a month later.
Our communication started with an introduction email, and I heard that Primoz needed help improving a spreadsheet – and I was happy to help.
Part of Primoz’ business is being able to help entrepreneurs write more effectively and produce more content to help grow their business.
He had a 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:
[image: wanting to improve / further automate his writing tracker > “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!!”]
We’re going to cover a few things:
⦁ How the tracker was at the beginning
⦁ The process of creating the tracker
⦁ An overview of the new tracker
⦁ How to use 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:
[image: Start > Hours spent banging my head against my desk in frustration > Finished product]
Fortunately, Primoz didn’t have to worry about my well-being because I handled the creation of the automated tracker.
Primoz 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:
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.
I’m going to give an overview of how his tracker functioned originally.
[image: old tracker]
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.
[image: old daily, old weekly, old monthly]
These tabs summarised the data on the Tracker tab at day, week and month views. This allows 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:
I will go through each step and provide more detail of how I was able to help Primoz build his upgraded Writing Tracker.
The “understand” 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 myself.
This got me more familiar with the project, and it gave me some time to think of some potential ideas.
[Image: Screenshot of email, me sending my ideas to Primoz]
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.
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.
[Image: Screenshot of my ideas, and Primoz comments > my structured summary]
At this stage, I will also try and set expectations and establish a deadline. Sometimes people have a specific date that something needs to be completed by, and other times it’s more open-ended. Either way, I will work with my client’s needs and we will agree on an estimated completion time frame.
We will also discuss my hourly rate, I will estimate the time it will take to complete the project and we will agree on a quote. Once all the boring documents have been sent / authorised… we can then begin work on creating the project!
When I first start working, I need to prioritise which sections needs 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 often 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 that 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 and also addressed his issues.
[Image: screenshot of me sending draft tracker, and addressing initial issues]
I 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.
[Image: screenshot of the tracker tab, without conditional formatting]
Here you can see that only data from January 25th to February 5th has been filled out. The weekly stats only show data from the first time data is entered, rather than from January 1st like the original tracker (because it had to be set up for a full year). You can see this below:
[Image: screenshot of weekly stats, without conditional formatting]
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, and I like my client to tell me which option they like best. Or other times, there isn’t an issue but there is a choice of a few preferences.
Here you can see I sent over a few questions to Primoz as I progress through the spreadsheet:
[Image: screenshot of questions at core stage]
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. I also explained some reasoning behind why I created the spreadsheet the way that I did.
[Image: screenshot of me sending completed core with reasoning]
Analysis and Graphs
Now that I had completed tracker and stats tabs, I could move onto doing the analysis and graphs.
It’s a part of life 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 refers to all of the writing data you have inputted.
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 one 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. This involved creating pivot tables that referred to the data, and then my graphs referred to the pivot tables I created. But who wants to look at ugly pivot tables when graphs demonstrate the analytics much easier? So, I hid the tab with the pivot tables in so that the graphs would auto-generate magically.
Once I had completed the graphs, the spreadsheet was really beginning to take form.
[Image: screenshot of analysis graphs]
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 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.
I then moved onto creating the leaderboards tab.
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.
[Image: screenshot of leaderboards table, without conditional formatting]
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 even 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.
[Image: screenshot of totals graphs]
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.
[Image: screenshot of document stats, without conditional formatting]
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 the top 5 documents of all time for words per hour and total word count.
[Image: screenshot of top document, without conditional formatting]
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 counts 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.
[Image: screenshot of writing streak on tracker tab and leaderboards, without conditional formatting]
Also, on the leaderboards it shows you what your highest writing streak has been so far.
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.
[Image: screenshot of tracker tab with conditional formatting]
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.
[Image: screenshot of formatting rules 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 comparing how the spreadsheet was at the beginning with how it is now.
[Image: all tabs of original tracker] > [Image: all tabs of new tracker]