How to easily track OKRs in Google Sheets

OKRs (objectives and key results) is an actionable framework for setting and tracking company goals. At kemb – a digital marketing agency, we’ve been using OKRs since 2014 and we’re convinced that they can help companies not only to perform better but also to increase employee satisfaction, since they help everyone work toward the same goals.

Over time, we’ve implemented a process that helps us discuss our OKRs on a weekly basis without having to manually track all inputs. In this article, we’ll share our setup and provide you with the means to integrate OKRs seamlessly into your company workflow.

TL;DR: OKRs are awesome! After reading this article you’ll be able to set up your OKR tracking in no time based on our template and Supermetrics for Google Sheets or Supermetrics for Excel.

1. What are OKRs and why are they important

(Psst! If you’re familiar with OKRs, you can skip this section and jump directly into the next one.)

OKRs (objectives and key results) are a means of managing teams and companies. They were introduced at Intel by Andy Grove and they’re based on management methods such as SMART goals and management by objectives (MBO). The topic got greater recognition when Google adopted OKRs in the late 90s. Finally, John Doerr opened the topic to an even bigger audience with his book “Measure what matters”, which has been the base for most modern adaptations of OKRs.

OKRs can be seen as a method to break down and align goals between teams and individuals for a certain time period, most commonly a quarter. In this regard, they work well together with the company vision and mission.

Based on realistic goals for a quarter, OKRs are formulated at the company level. Objectives are higher goals like “Become the number one provider for X in our home market” or “Become the most attractive employer for software developers in Munich”, which is more of an ideal than a measurable goal.

Each objective is broken down into two to four key results, which are measurable goals that are quantitative in nature. This means that their completion can be tracked in percentages over the quarter. Examples would be “Reach 1M € in revenue in the home market” or “Receive 25 unsolicited applications in Q1”. For a growing library of OKRs for different departments, see some of our OKR examples.

The wider process includes aligning the OKRs of all teams to see whether there are conflicts of interest. If any contradicting goals are found, they need to be solved before the beginning of the quarter to make sure that the whole company is working towards the same goals. No teams should be blocked by other teams and there should be no misaligned goals that contradict one another.

OKRs should be formulated as outcomes rather than outputs. This means that the tactics and activities required for achieving each goal should not be predetermined but instead, there should be flexibility. OKRs should also be reasonable in that they should take everyday operational tasks into consideration. Otherwise, they’ll be nearly impossible to achieve.

Over the course of the quarter, you should organize regular check-ups to see whether you’re on track to reach your OKRs. If your team has fallen behind, these check-ups are a great opportunity to discuss how to change course.

When it comes to personal OKRs, we regularly include personal development goals in addition to business goals. This way, the framework can also be used for people management.

Now that you’re familiar with the framework, how do you make sure that you won’t have to spend hours every week on keeping track of your OKRs? Well, here’s how we do it at kemb.

2. How to automate your OKRs using Google Sheets

Like many of you, we love to automate tedious processes. Manually collecting numbers for our OKRs is one of those tasks. We’re aware that sometimes there’s no way to automate data imports, and sometimes manually adding entries is the only way. However, for all other cases, we highly recommend automating your imports.

We suggest discussing your OKRs at least once a week. And that’s why we’ve created an easy template that tracks your progress week by week. Now it’s up to you to automate your data collection. That can be easily set up with these five simple steps.

1. Setting SMART Goals

When setting your goals, make sure you set SMART goals. In this case the “M” for measurable is important when it comes to automation. We have noticed that using OKR automation challenges you to set easily measurable key results.

2. Where do you get your data from?

Once you know which metrics you need to track your key results, it’s time to check if you can automatically import these values using Supermetrics queries. You can find a list with all sources here.

3. Write your import using Supermetrics queries

We highly suggest using an import tab in which you collect all of your raw data, before importing it into your OKR overview. In our template we use a weekly import in which we fetch our desired data using the date range “year to date” and split the desired data by “year & week (Mon–Sun)”.

4. Link you cells up

After we have successfully imported the data we’re after, we need to link up the cells in the overview to get our scores.

5. Set up an automated refresh and email

Once your key results are imported and you’ve linked your actuals with your targets, it’s time to set up the weekly refresh. That ensures all your key results automatically get updated once a week. To create even more visibility, you can schedule automated update emails to your team members. 

This process might seem a little complex at first. however, after having done this exercise once, we can see the following advantages:

1. Large chunks of time saved, as Supermetrics automatically pulls the right data for us. 
2. More awareness and a better understanding of both team and personal OKRs amongst teams.
3. No more copy/paste errors.
4. No more weekly meetings with missing numbers.

Pick the Supermetrics plan that offers the connectors you need.

Some examples of the most common connectors we’ve used to easily automate OKRs.

Marketing performance:

• Google Analytics
• Google Ads
• LinkedIn Ads
• Facebook Ads

We’ve used these for measuring key results like:

• Grow organic website visits from X to Y
• Increase conversion rate from LinkedIn campaigns by 10% from 2.5  to 2.75
• Double the leads from partner webinars from 20 to 40 

We’ve collected an even longer list of OKR examples split by departments. We created this to help you get new ideas and angles on how to track your objectives and key results per department.  

3. How to build an OKR dashboard

Now that we know the basics of OKRs and have discussed how to track your key results, it’s time to build your own OKR dashboards.

As explained earlier, we suggest setting OKRs on three levels:

1. Company OKRs, combining the overall company goals into digestible and clear OKRs.
2. Team OKRs, a subset of objectives that a team is responsible for and which feed into the overall company OKRs.
3. Personal OKRs, these are optional, however, a great tool for empowering employees to go further. We also suggest mixing subsets of the team OKRs in combination with personal development OKRs.

In our template, we’ve created overviews for all three of the above, where team and company are in one template, and personal OKRs have their own template.

3.1 Getting started

Enough talk, let’s dive right in. We’ll start with the team/company OKRs.

Step 1: Copy this Google Sheet

The first thing you’re going to do is make a copy of this OKR Template as a Google Sheet, so click File —> “Make a copy”.

This new sheet you just copied will be your OKR master file. Use this as the file where you collect all your OKRs. 

Step 2: Duplicate the template tabs for each of your teams and name them. 

Click on the tabs at the bottom, and select duplicate. By always working with a duplicate you make sure you never end up changing or removing the OKRs of your team members.

Step 3: Set the date

Double click on cells B2 & D2, and select the first and the last days of the quarter. We do this to calculate how many days there are within the quarter.

Now the basic setup for your sheet is done, and we’re ready to add your objectives and key results. 

3.2 Setting your objectives & key results

Now we’re getting to the fun part where we add our objectives and key results. Keep in mind that your key results are the elements that make up your objective.

Lets use a simple example to start with. Let’s say our objective is to “Double the number of sales for our company compared to last quarter”. In order to get there, we have defined our first key results as “We want to grow our organic website traffic to 7,000 visitors”.

So we fill in our objective and key results in column B. 

3.3 Fetching your data

Next, we need to set up the Supermetrics query which gives us our weekly organic website visits. If you’ve not already done it, now would be the time to install the Supermetrics add-on for Google Sheets.

In your top navigation in Google Sheets, click on “Add-ons” >  “Get add-ons” > search for Supermetrics and hit install. 

If you already have the Supermetrics add-on installed, launch the sidebar.

Now our sidebar is launched we open the “Data Import – Team/Company” tab and click on cell B8 and start composing our basic query to pull in our weekly organic website visits.

So for our sample query we select the following elements:

Data source	Google Analytics
Select view	Select the view you normally use for your reporting
Select dates	We use year to date to make sure all our data is added week by week, giving us some historical data that makes it easier to validate and match. (You could also use other date settings, but this will require you to Index/Match your weeks to our OKR overviews)
Select metrics	Sessions
Split by	Year & Week (Mon–Sun)
Segment	Here you can use the predefined “Organic traffic” segment
Filter	If you want to import any other segments, which are not defined under the Segment tab, you can use Filters to narrow down your data.
Options	Make sure you tick on “Replace blank metric values with zeros”

Now click “Get Data to Table”, and your data should be correctly pulled in. After your data is imported, we suggest checking your imported numbers against the source to validate that your query is correct.

Repeat this process to collect all the data you need to measure your OKRs. Keep in mind that you might be able to import data for multiple key results from the same source using the “Select Metrics” and “Split By” functions. 

3.4 Link your goals & your actuals

Once you have imported all the data into the “Data Import – Team/Company” tab, we can match the actuals with your key results. To do this with our example, open the “Team/Company (make a copy)” tab and go to cell E11.

Here we write the following formula in order to 1. Import our values, and 2. To score them against our objective.

=’Data Import – Team/Company’!B9/7000

   Match the correct weekly value    /    your key result value

This simple formula should give you your current progress based on the actuals vs. your goal.

Now we want to make sure that the data gets displayed for year week by adding two elements:

First we copy the formula from cell E11 to F11

1. Adding up the cells of the previous week, this we do by adding +
=((‘Data Import – Team/Company’!B9/7000)+D11))

2. Make sure that cells that lie in the future do not show any data. This we do by adding an “if”-clause formula that checks if the date is on the future
=IF(E$9>=TODAY();“”;((‘Data Import – Team/Company’!B9/7000)+D11))

3. Now we can easily copy the cells through the quarter, making sure that as soon as there is a new weekly value available, your sheet will be showing the weekly numbers.

4. You can now also drag down the formulas and update the import and goal values. You don’t have to touch any other parts of the formula.

3.5 Automate your weekly refresh 

 Keep in mind that this is a feature only available in the paid plans of Supermetrics.

Alternatively, you can manually launch a data import refresh in the free version via “Add-ons” > “Supermetrics” > “Refresh queries”.

With the paid versions you can use the “Schedule refresh & emails” option which can be found under “Add-ons” > “Supermetrics” > “Schedule refresh & emailing”.

We’d suggest running your weekly update early on Monday mornings to make sure the data is complete and waiting in your inbox when you get to work.

You can also set up an automation that will send you an email as soon as your reports have been refreshed. We’d suggest that you don’t use this function to send emails to all employees, because you might want to evaluate the numbers before they are shared. You’ll also want to tick the box “Send email alerts if queries fail on refresh”, as this will send you a message if one of your queries fails to run.

3.6 End of quarter, now what?

Having followed all of the above steps, you should now have a fully automated OKR reporting sheet, which you can use on a company, team, and personal level. Once the quarter is over, we suggest fixing all values by “selecting all cells” > “copy” > “paste values only”.

This way, you fix all the values and you can use your data import sheets for your next quarter’s OKRs. This also gives you a clear archive of previous OKRs and achievements. 

4. Rolling out OKRs within your teams

When you’re done structuring your OKRs and automating the spreadsheet, it’s time to roll them out within the company and the teams. For us, OKRs have four aspects that make them really powerful.

Visibility

OKRs work best when there’s a maximum amount of transparency. This does not mean that you need to report EBIT margin and cash flow to every intern but that you design your OKRs in a way that every employee understands where the company is going and which individual measures are taken for the company to get there.

This visibility can be achieved by, for example, making all team and company OKRs accessible to every (full-time) employee. We even had cases where everyone’s personal OKRs were transparent. This wasn’t so that we could judge each other but to better understand what the individual would prioritize in their everyday work. If you’re using Google Drive, Sheets makes sharing really easy.

Involvement

OKRs are a great way to activate the potential of your company. Get input from the team members for the team OKRs, use the personal OKRs to understand where your employees wants to grow to and support them with time for training and conferences.

Talk about the other teams’ OKRs in your team to give people a better understanding of how the company works and how the teams interact. We have seen cases where OKRs have been beautifully visualized on screens next to the teams. That can lead to cross-team discussions that bring about new ideas and process improvements.

Company spirit

Through visibility and involvement you generate a whole new spirit throughout your company. People get a better understanding of their colleagues day to day work and how everyone contributes to a shared vision.

People feel involved and heard when you celebrate their successes and support them when they it looks like they might fall short of goals. It’s way easier to have fact-based and blame-free discussions when you have transparency on numbers, a basis for hypotheses for why things work out or don’t, and the trust to experiment, as the results can be seen in close to real time. We highly believe that OKRs can help every company to make work feel less like work and more like a shared set of goals.

Continuous improvements

OKRs become really powerful in the long run. In most of the cases where we’ve implemented OKRs, we’ve seen improvements over time. Some struggles in the beginning are natural and if you’re not used to setting OKRs, it’s possible that you’ll start off with a couple of input key results that work more like a checklist than a goal.

Interdependencies between teams are discovered by discussing regularly with other teams. As always, communication is key.

Use OKRs to align with your team on a regular basis. Let everyone know if you’re making progress, if there are limited resources, or if there are any blockers. We’ve made it a part of our process to have quarterly offsites where we block a whole day or two to discuss the OKRs of the previous and the upcoming quarters.

During the offsite, we document what went well and what didn’t and give ourselves feedback on the past OKRs: did we all work towards a shared goal this quarter, did we overload the OKRs, where can we improve? Often the initial rounds of OKRs are set too ambitiously. In our experience, limiting the scope but really focusing on nailing a few goals is better than starting 10 initiatives and never finishing one. OKRs should also be about completion.

That concludes our piece on OKRs and how to automate them. If you have any questions feel free to get in touch. If you need support automating your OKRs with Supermetrics, we’re also happy to help.

About the author

Martijn Burgman is Co-founder & Partner at kemb GmbH, a digital consultancy specialized in data-driven marketing.  Having led international marketing teams at two big affiliate platforms for more than 5 years, Martijn and the kemb team have been supporting ecommerce players to grow their sales through the implementation of their digital marketing and analytics strategies since 2016.

Martijn is always happy to help, so don’t hesitate to reach out to him through wearekemb.com.