How to easily track OKRs in Google sheets using Supermetrics

1. What are OKRs and why are they important

If you are familiar with OKRs, you can skip this section and directly go to OKRs (objectives and key results) are a means of managing teams and companies. They have been introduced at Intel by Andy Grove based on management methods such as SMART goals and management by objectives (MBO). The topic got greater recognition when Google took over the concept 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 currently in practice.

They have been introduced at Intel by Andy Grove based on management methods such as SMART goals and management by objectives (MBO). The topic got greater recognition when Google took over the concept 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 currently in practice.

OKRs can be seen as a method to break down and align goals for a certain time, most often a quarter, from a company perspective to the individual teams contributing to them as well as the team members on a personal level. In this regard, they work well together with the concept of company vision and mission.

Based on realistic goals for a quarter OKRs are formulated on a company level with objectives being a higher goal like “Become the number one provider for X in our home market” or “Being the most attractive employer for software developers in Munich” which is more of an ideal as a measurable goal. To each objective belong 2 to 4 key results which have to be measurable goals that are ideally quantitative in nature so as to break them down into percentages of achievement over the quarter they are tracked. Examples would be “Reach 1.0 mio € 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 in this articles’ airtable.

The wider process then includes aligning the OKRs of all teams to check whether there are conflicts of interest which need to be alleviated before the quarter so as to have the whole company work towards the same goals without teams being blocked by other teams capacity or even having misaligned goals that contradict each other. Also OKRs should be formulated as output KPIs so that the means of achieving a goal are not predetermined to do list but provide for flexibility in achieving those goals with smart initiatives. OKRs should also be reasonable in a way that they include the everyday operative tasks of the teams and do not come on top making them hard to achieve to begin with.

Over the course of the quarter there are regular alignments if the OKRs are on track and exchange on how to better achieve them if a team is behind. On the level of personal OKRs we regularly include means for personal development next to business goals and thus the tool can also be used for people management. It is important to state that OKRs should be followed in some regard by the book meaning how they are set and formulated and how they interact. On the other hand you should adapt the processes so that they work for your company.

2. How to automate your OKRs using Google sheets

As many of you, we love to automate tedious processes. Manually collecting input numbers for our OKRs is one of those tasks. We are aware that sometimes there is 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 therefore have created an easy template which shows your progress on a weekly basis. Now it’s up to you to automate your data collection. This is easily set up using five simple steps

2.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 in making sure your goals are easily measurable.

2.2 Where do you get your data from?

Once you know which metrics you need in order to track your key results, it is time to check if you can automatically import these values using Supermetrics queries. You can find a list with all sources here (https://supermetrics.com/integrations)

2.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)”.

2.4 Link up your cells

After we successfully imported the data needed in order to track our OKRs, we need to link up the cells in the overview in order to get your scoring.

2.5 Set-up an automated refresh and email

Once your key results are imported and you linked your actuals with your targets, it’s time to set up the weekly refresh. By doing so you make sure that all your key results automatically get updated on a weekly basis. In order to create even more visibility, you can decide to automatically send a weekly update email to your team-members.

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

1. Large amounts of time saved, due to Supermetrics automatically pulling the required data
2. More awareness and a better understanding of both team and personal OKRs amongst teams
3. No more copy and 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 tools we’ve used to easily automate OKRs: Marketing performance: Google Analytics, Google Ads, Linkedin Ads, Facebook ads.

In order to get Keyresults such as:

1. 1. Grow Organic Website visits from X to Y
2. Increase Conversion Rate from LinkedIn Campaigns by 10% from 2.5 to 2.75
3. Double the Leads from partner webinars from 20 to 40

We have collected an even longer list of OKR examples split by departments here. These we created to help you get new ideas and angles on how to track Objective and Key Results per Department. (feel free to share your suggestions, we will add them to the airtable)

3. How to build a OKR dashboards

Now we know the basics of OKRs and have discussed the basic concept of tracking your key results, it’s time to build our own OKR dashboards.

As explained earlier we suggest setting OKRs on three levels:

1. Company OKRs,combining the overall company goals within 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, these are optional, however, a great tool to empower employees to go further. We also suggest mixing subsets of the team OKRs in combination with personal development OKRs.

In our template we have created overviews for all 3 of the above, where team and company are one template, and personal also has its own template.

Getting started

Enough talk, let’s dive right in. We will start with the team/company OKRs. Step 1: Get your own copy of this Google Sheet. So the first thing you’re going to do is to just 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 downloaded will be your OKR masterfile. Use this as one 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 of changing or removing the OKRs of your team members.

Step 3: Setting the date Doubleclick on the cells B2 & D2, and select the first and the last days of the quarter. This we do in order to calculate how many days there are within your selected quarter.

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

3.2 Setting your Objectives & Key Results

Now we are getting to the 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”, and in order to do so we have defined our first key results as “We want to grow our organic website to 7.000”. So we fill in our objective and key results in column B.

3.3 Fetching the your data

As a next step we need to set-up the supermetrics query which gives us our weekly organic website visits.

If you’ve not already done so, now would be the time to install the Supermetrics addon for Google sheets.

In your top navigation click on “Add-ons” > “Get-Addons” > 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:

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

Repeat this process to collect all data you need in order to measure your OKRs, keep in mind that you could potentially import data for multiple key results from the same source using the “Select Metrics” and “Split By” functions.

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 goal achieved 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. All other parts of the formula you do not have to touch

3.5 Automate your weekly refresh

Our ORK report is now ready for use. The last thing we need to do is to make sure that our report refreshes automatically. Keep in mind that this is a feature only available in the paid versions of Supermetrics.

Alternatively you could decide to manually launch a data import refresh in the free version via “Add-ons” > “Supermetrics” > “Refresh Queries”

Within the paid versions you can use the “Schedule Refresh & Emails” option which can be found under “Add-ons” > “Supermetrics” > “Schedule refresh & emailing”

Here we suggest running your weekly update early Monday morning, to make sure all your data is complete, whilst still making sure that your document is fully updated first thing Monday morning.

You can also setup email automation which will send you an email as soon as your reports have been refreshed. Here we suggest to not use this function to send emails to all employees, as you might want to evaluate the numbers before they are shared and to tick the box “Send email alerts if queries fail on refresh”, as this will also 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 your 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 quarters OKRs.

This then also gives you a clear archive of previous OKRs and achievements.

When you enable the preview mode you will be redirected to the following window

4. Rolling out OKRs within your teams

When you are done structuring your OKRs and having automated the sheets it is time to roll them out within the company and its teams. For us OKRs have three aspects that make them really powerful if a company is willing to reap all the benefits from them:

Visibility

OKRs work best when there is a maximum amount of transparency on them. 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 developing and which measures are used to grow 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 not to judge each other but to better understand what the individual would prioritize in their everyday work. If you are based on Google Drive, Sheets makes sharing really easy as sharing is just a button click away (given you know who gets to see what).

Involvement

OKRs are a great means to activate the whole potential of your company. Get input from the team members for the team OKRs, use the personal OKRs to understand where your employee wants to grow to and support them with time for education and conferences. Talk about the other teams OKRs in your team to give them a better understanding of how the company works and how the teams interact to form a whole. We have seen cases where OKRs were beautifully visualized on screens next to the teams and more often than not that led to discussions with team members from other teams that brought to light some 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 respective colleagues day to day work and how everyone contributes to a shared vision. People feel involved and taken seriously when you celebrate their successes and support them when they should fall short of goals. It is way easier to discuss happenings fact based and blame free when you have a transparency on numbers, a basis for hypotheses why things work out well or don’t and the trust to experiment as the effects 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 setups where we implemented OKRs we saw improvements over time and struggle in the beginning is natural.

. If you are not used to creating OKRs most often you will start off with a couple of input key results that work more like a checklist than goals. Interdependencies between teams are discovered throughout understanding and discussing regularly where there are touchpoints and limits in daily work and for some teams especially in operations like finance and HR it is most often tricky to come up with key results that also reflect the day to day work of accounting or people management.

Especially with OKRs communication is essential. Use the OKRs to align with your team on a regular basis if you are making progress, whether there are limited resources or if there are any blockers.

We made it part of our process to have a quarterly offsite where we block a whole day or two depending on team size to discuss the whole OKRs of the previous and the upcoming quarter. In this offsite we document what went well and what didn’t and give ourselves feedback on the planned OKRs: do we all work towards a shared goal this quarter, did we overload the OKRs, where can we improve?

Most often the initial rounds of OKRs are planned too big and in our experience limiting the scope but excelling in that scope is the main route to go instead of 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 contact us via our Contact Form or if you need support automating your OKRs with supermetrics we are also happy to help.