In Phase 1, we talked about the need to figure out what you want to track to determine how to improve you life. Then we discussed the different types of questions you would want to ask (ranking questions and Yes/No questions) and how each can be important to the development of the data you’re looking for. For a more detailed breakdown of Phase 1, click here.
So now that you have some questions you want to ask yourself every day, we’ll start Phase 2 which is building the ecosystem to capture this data. The first step to building out your system is to find a way to capture that data in a consistent and most importantly, actionable way.
Capturing Data
There are many ways you can do it, such as filling out an excel sheet, writing data down in a notebook, or capturing in your phone’s notes app. Honestly, you shouldn’t do any of these things. Instead you should build out these questions in a form and specifically a Google Form. Why? Well many reasons.
- A form allows you to fill out the same thing, hundreds of times; consistency is key.
- You can do it across multiple devices; you only need an internet connection. It also doesn’t require special software to be installed to use it (such as Excel) and is mobile friendly
- You can connect the output of the form (or another words the data) into a sheet that automatically updates each time you submit a new entry (this is really the power of this process)
- Because it’s captured digitally, you don’t have to translate data from a notebook into something else
The following steps will walk you through how to setup a Google Form and then connect it to a Google Sheet. The real power of all of this will come after we set this whole thing up, you start capturing data, and then we connect it in a way that you can gain insight over what the data’s trying to tell you. Trust the process.
Step 1: Create a Google Form
You need to create a Google Form and the first step for that requires you to have a Google account. You can certainly find tutorials online on how to create a free Google account; I’m going to assume you’ve done that already.
Head to Google Forms (link) and then click “start a new form” and click the “Blank form” button. This just helps us start from scratch.
A new Google Form will open and we’ll get to work!
Start by filling out the title of the form (it’ll be important later to know what the name of this form is). You can add to the description under the title if you’d like as a place to remind yourself how to use the form, or don’t put anything at all; take some ownership would you?
There are two main question types I’d suggest using:
- Dropdown (for the “Yes/No” questions)
- Linear Scale (for the ranking questions) – In theory you could use the dropdown for this, but it makes it a bit easier to just use this question type
For this example, I’ve listed a ranking question (life score) as a linear scale and “Did I shower” as my Yes/No question as a dropdown.
As you can see, this is really simple. We don’t need to make anything complex. In fact, complexity is the enemy of this system. When it comes to building out a system, the system is only as good as your worse days, so do yourself a favor and make it stupidly simple.
The last part will be gathering your URL that you can use to fill out the form. To do that, click “Send” button at the top and when the window pops up, move to the middle tab with a link icon. That will provide you the link to take the form. You’ll need this link to take the form every day; you’ll also need this later in the step to automate it.
Step 2: Connect Google Form to Google Sheet
Next, you’ll notice the tab at the top called “Responses.” Going into that tab will give you an overview of the results. That’s great and gives you some basic information but doesn’t allow you to connect everything like we’ll do in later steps; to put it a different way, I’ve never looked at my results here.
The big thing: click that little ‘ol button near the top that says “Link to Sheets.” That’ll create a new spreadsheet where you form answers are stored.
Just go ahead and have it create a new spreadsheet. Once it does, you’ll see a little popup in the bottom left to open the new spreadsheet. The new spreadsheet should look something like this:
Now, every time you fill out the form, the answers will populate in the next available line. Keen observers will notice a new field called “Timestamp” which becomes really important. This automatically captures the date and time you completed the form which eliminates the need to ask that question when building the form. Now, you can capture this data consistently and then view your answers across time without needing to do anything other than answer a few simple questions. Show me a notebook that’ll do that.
Step 3: Make Google Sheet Automated
I need you to hold on for a second. This is going to get a bit messy, but stick with me. Depending on how many ranking questions you have, you might want to have a “total” score (the sum of all of the ranking questions combined). You can make it so that the sum is calculated every day in the spreadsheet without having that be something you answer. For example, let’s say you have three ranking questions that you entered the score to be today:
- Life Score: 7
- Work Score: 8
- Health Score: 6
Maybe you want to sum these together to get a daily score (you probably do, so that you can get a big picture of how everything is going). We can do the math that 7+8+6 = 21. So in this instance your “daily score” is 21. We can set up the Google Sheet so that this is automatically calculated each day.
So if you want a calculated field, create a new column in your spreadsheet all the way at the end of the sheet on the right. So if your spreadsheet questions end at “Column K” I would put the calculated field under “Column L” or possibly even farther away.
One thing we have to understand about Google Forms and Google Sheets is that when a new form submission is added to the sheet, it creates a new row, but IT DOES NOT bring over formulas in the normal way. So we actually have to use an array formula to work correctly.
Setting up an Array Formula
Ok, are you sitting? This gets ugly. Look, there’s no way around this than straight at it. When I’ve developed my calculation field, this is the formula:
=ArrayFormula(IF(ROW(B2:B), IF(LEN(B2:B&C2:C&D2:D&E2:E)>0, B2:B+C2:C+D2:D+E2:E, “”), “”))
It’s actually not as bad as it looks. There are two IF formulas nested in here. You MIGHT be able to copy and change a few fields to make this formula work on your sheet. Let me explain what the formula’s doing:
- Data Locations: I have data in the following locations reflected in the formula
- B2:B, C2:C,D2:D, and E2:E
- These are the columns where my ranking question data is. So you would adjust your data in the formula to be where your ranking questions are. My tracker has four ranking questions, hence why there are four columns that are referenced.
- Each one starts with the column letter (“B”) and the row (“2”) and then the part that says “:B” basically tells it to go on forever.
- IF(ROW) : I have no idea what this is solving for, but it’s probably something to do with making sure that for fields that don’t have answers yet, they are blank and don’t have errors. That’s because this formula ends with (“”) which tells the spreadsheet to display nothing
- IF(LEN(Data locations)>0 says that if the field is greater than “0” then add up the Data Locations, if it’s Not greater than “0” then display blanks.
The big tip for this: use Chat GPT (link). That’s what I did. I explained to it what I wanted to do and it spit out formulas until it got it right. Tell Chat GPT what you want to do in as best descriptive words you can and see what it tells you. Try what it says, and if it doesn’t work, tell Chat GPT and tell it what the outcome was that was wrong and what you wanted it to do instead and then repeat.
You also have to understand how to properly enter Array Formulas into Google Sheets. According to Google: “Pressing Ctrl+Shift+Enter while editing a formula will automatically add” ARRAYFORMULA( to the beginning of the formula.” For more information see Google help guide here.
Step 4: Set up Automated Reminder (optional)
Hey, I’m proud of you. You built a fucking badass spreadsheet with an array formula. You’re basically a coder at this point. So building a little bitty automation is nothing to you now, right? This little step is really important, because it makes ‘lazy you’ successful. Remember how we said we had to build this for the worse you get? Well this step helps out that slug.
We’re going to create a super simple automation on an iPhone. “What about Androids Scott?!” No idea. Google it. I’m sure there’s something you can do there, but I’m an iPhone guy.
Find your “Shortcuts” app. Next you’re going to create a new shortcut.
Click the “+” icon in the top left.
Next we’re going to “add action”
Search for “Open URL” and then select the “Open URLs” option under “Web.”
In the new box under “Open” paste the URL from your Google Form. This was found under the “Send” button step of building the google form. For this illustration I typed in “googleformurl.com” that’s obviously not what will go in yours.
Then click the option of “Stop this Shortcut” to end the shortcut.
That’s the end of creating the shortcut. See, I told you that was SIMPLE. The last part is we have to setup an automation so that the shortcut runs frequently. Realistically, you should just set this up to run the shortcut every day at a certain time. I choose 9PM because I’m usually not doing anything but also still awake; do what fits your schedule.
To create an automation, move the middle icon on the home page of the Shortcuts app where it says Automation. Then in the top right corner hit the “+” sign to create a new automation. We’re then going to select “Time of Day”
Then you’ll navigate the options here to set the automation when you want. I like to select “Run After Confirmation” so that it asks you to run it rather than just randomly opening your browser at the given time.
The last step is to find your shortcut you created in the previous steps. Once you select that, it’ll schedule that shortcut to run at the time you’ve identified in the automation. Did we just open up a whole new world of possibilities. You bet we did!
You’re done!
Look, you just created a form, built a spreadsheet, made some calculations, and automated that shit. The hard part’s over (for now). Now just start capturing some data frequently and consistently. Go live your life and start capturing how you feel. You need data. The more data you have, the better insights you can get back!
Phase 3: Preview
The next big thing is taking all the data you’re capturing and building out a system that will allow you to understand the data way more. We’ll be using Google Looker Studio (formerly called Google Data Studio) to create visualization of the data you collect. With this tool, you’ll be able to connect the data and play around to see how things interact. That’s where the magic is. It allows you to see how your scores interact and things that drive your scores higher or lower. This is living a better life through data, not just feeling.
Does it get a bit more complicated? I mean, technically yeah. But it’s a pretty forgiving system and we already set up the data in a way that should make it pretty easy to start.