3 steps to a strong client-tracking system in Google Sheets
Problem: Too often, organizations try to start from scratch when building a tracker to log a client’s progress or attendance. What you end up with is multiple different sheets with names, phone numbers, emails with slightly different spellings and slightly different counts.
Solution: Following these simple steps below can set up you up for success when tracking client engagement in your programs. This article explains this process within Google Sheets, but Excel works very similarly.
Create 2 or more separate tabs for “Person” data and various “Activity” Data tables (i.e. scores, attendance, enrollment) in your spreadsheet.
Create a “Unique ID” for each “Person” using a concatenation in the “Person” data tab
Create a “Dynamic” Data Validation in your “Activity” Data table to link it to your “Person” data table
Alright! Let’s dive into these steps in more detail.
Step 1: Isolate static data versus dynamic data.
Static data about a client are the aspects about them that very rarely change like… their name, DOB, phone #, email, first language, race, ethnicity…
All of these aspects about a person don’t change every time a person enters your program. For this reason, static data should never be stored/written down every time you make a new tracker for dyanamic data.
Dynamic data about a client is the touch-points or activities a client is associated with in your programs such as enrollment to a specific program, completion of onboarding, attendance, and scoring. This is data about a person that could change day-to-day.
Isolating static data means creating a Tab in Google Sheets that is the “Baseline Static Data table” of all of your clients within your “world” of programs. No matter what activities a client participates in, their name, email, phone, and general demographic info can be stored in the same place.
Step 2: Create a Unique Identifier in your static data table
With the data above about Mikey Mouse, I can now create a unique identier that would reduce the changes of a mix up in case I had two clients named Mikey Mouse.
2.1 By adding an “ID” number or a row number for each client, I create a number that’s unique to each client.
2.2 Insert a new column to the left of the ID number and use a concatenate formula to bring together the ID, First Name, and Last Name into one field. In the example below, the formula is =D3&” “&C3&” - “&B3 which will combine the three elements with spaces in between. When I drag that formula down to the bottom of my sheet, I create an “Auto-ID” that will fill in automatically when I add new data about clients into rows C & D
Step 3: In your “Activity” tabs, create a dynamic data validation “Drop down” to get a pre-set list of clients
Now that you have a unique way of identifying Mikey Mouse, head over to your enrollment or attendance sheet.
3.1 Go the the column where you’d want the names of students enrolled in a particular program (most often Column A) and select into the first open row under the header.
3.2 While selected into that first cell in Column A of your table, go to Data > Data Validation to open up Data Validation settings. Click here to read more about what Data Validation is before continuing!
Click +Add Rule to Open the Data Validation configurations.
First, we will extend the cells that are getting this Data validation applied to them by adding :A to the selected cell. (See screenshot below)
Next, we will switch the Data validation criteria from a Dropdown (normal) to a Dropdown (from a range)
Finally, we hit that 2x2 box in the lower right hand corner to tell Google Sheets where we want to find the range of data. Clicking on 2x2, will open a “Select Data Range box”.
In the background (behind the box), navigate back to your original “People” data table and click the first cell of the Unique IDs. Google will register that cell in the box and then you can add :A (or whatever column letter your column is in) to extend the “Search Range” to all names. Hit “Done” and “Apply to All” to complete.
3.3 Admire your Unique ID dropdown
Congrats! You’ve now created a system where you don’t have to re-type the name of the person more than once. Additionally, from a data quality perspective, all enrollment or tracking data on a specific person will all be linked to their “Person” profile in a way that wasn’t possible before.
