Accelerate your thought leadership by contributing to our blog. Join our community of experts now!
Marketing, sales, HR, accounting, finance, research and development. All of these departments in your company have specific goals to achieve and use KPIs to track their progress towards those goals.
Every so often, team leaders will forward these KPIs to you so that you can track the overall performance of your business. According to our report on the state of business reporting, almost half of the companies set their goals for 1 to 5 metrics.
Analyzing the data separately for each department is immensely time-consuming, which is why we use Google Sheets to create KPI reports.
Google Sheets dashboards and KPI reports provide you with a comprehensive overview of the most important metrics from each department, all in one place. With these reports, you will have an insight into which parts of your business are doing great and which may be problematic.
Also, by using visualization tools in Google Sheets, you will be able to present the most valuable data to your key shareholders in an understandable way.
In this guide, we are going to teach you how to build a great KPI report in Google Sheets that you can use to impress the highest-ranking members in your company.
KPIs are metrics that provide you with insight into how your company is performing. They are commonly used for determining whether business goals are progressing in the right way.
In most cases, your department leaders will share the most important KPIs in their area to show you how their team is performing.
However, keeping track of all the significant KPIs from each department is extremely hard without the help of certain tools.
This is why many executives create KPI reports in Google Sheets.
Google Sheets provides a relatively easy way for you to visualize technical data. It includes the common spreadsheet calculations and formulas that you can use for reporting and analysis.
A good KPI report can help you and your teams acquire actionable insights into the performance of each part of the company. You will have a clearer overview of your business performances and you will gain an insight into which areas seem to be causing issues.
By tracking the metrics in your KPI report, you can assess the success of your strategies and the overall health of your business.
Using KPI reports is one of the best ways to gain perspective over the various departments in your business.
By highlighting all the key metrics in a single project dashboard, you will have access to all the significant data that you will need to make informed decisions.
The best thing is – KPI reports can be extremely flexible.
You can either choose to create a KPI report that covers all aspects of your business (marketing, sales, accounting, HR, etc.) or a specific report that only tracks the performance of a particular department.
However, no matter which KPI report you need, you will have to go through the grueling creation process first.
Creating a KPI report can be extremely frustrating, even for those that have years of experience.
But don’t worry, we have prepared a step-by-step guide that will help you go through it with ease.
Follow these few steps to create a comprehensive KPI report:
The first thing you need to do is set a data source for your Google sheet database.
This can either be done through a Google Form that you already use for gathering data or a CSV file from your analytics tool.
However, we recommend that you create a database manually since it’s the best way to later automate data generation in your Google Sheet.
As an example, we will create a ‘monthly marketing expenses’ database. Next, we will add a ‘Budget’ column that we will use to track spending trends.
In case you are using a complex tool like Google Analytics to gather data in your campaign performance, you will need to integrate certain GSheet functions to make the analysis process easier.
These are some of the functions you will need:
After creating a database, your next step will be to develop a chart.
Do this by selecting all the data in your spreadsheet and clicking ‘Insert’. Next, select ‘Chart’.
The ‘Chart’ editor option allows you to create a pie chart, bar graph, or any other type of line graph that you want to integrate.
Additionally, you can edit graph/chart details such as axis title, chart height, color theme, and many more.
Related: How to Create A Bar Graph (and more) in Google Sheets
Time for the most important step – formatting your chart into an understandable full-overview dashboard.
Once you are finished with the ‘Chart’ editor, select the chart area and move it away from the data table.
Next, click the ‘three dots’ symbol located in the right corner of the chart area.
You will see two options: ‘Publish it’ and ‘Move to own sheet’.
By clicking on ‘Publish’, the Google Sheet will provide you with a link that you can send to other users. In the ‘Permissions’ option, you can also restrict certain users from entering the link.
In case the published chart is running a bit slow, you can edit the data to see how long it will take for the chart area to be updated.
With the ‘Move to own sheet’ option, you can refresh the data much quicker.
Although, you will have to switch between tabs to edit the data and adapt it to the chart.
By following these steps above, you will be able to create a few different charts and then place them in a separate tab.
With this kind of data visualization, you can compare different sets of metrics in one place.
While Google Sheets have long been the epitome of project management tools, we can’t say that’s the case nowadays.
Technology is constantly evolving and with new management tools being released every few months, Google Sheets simply isn’t the best and most practical alternative anymore.
Here are just some of the limitations of using Google Sheets for dashboards.
If you have gone through the creation process, then you have already experienced having to manually input every single piece of information.
This is because Google Sheets has no automation features.
Sure, you can use a Google Apps Script or modify Google Sheets to fasten up the process, but this isn’t really the easier alternative. Simply learning how to do it will take ages.
Once your projects are up and running, updating a Google Sheet dashboard manually will become incredibly tiresome.
While there are project management tools that support Google Sheet integration or Google spreadsheet implementation, it’s an extremely rare occurrence.
Most of the time, Google Sheets will only be compactable with the Google Workspace.
This may not seem like that big of a deal, but once you need certain data integrated into your project tool, you will see how big of a hassle it actually is.
The third limitation of Google Sheets is that it simply lacks some typical day-to-day functions.
Here are some of the things that GSheets can’t cover:
All of these functions are important for growing businesses and if you aren’t using powerful project management software, you won’t get very far with Google Sheets.
As we said, with project management tools constantly evolving, Google Sheets simply can’t keep up with the pace with the new features.
With limited automation, integration, and comprehensiveness, creating Google Sheet dashboards will inevitably take up too much of your valuable time.
This is why advanced project management tools such as Databox are considered to be a much better alternative.
With Databox automation features, you will be able to quickly pull out any useful data into your dashboard and then visualize it at a click of a button. This provides you with a comprehensive and understandable overview of your key metrics.
Not only that, with visualized data, you will have an easier time sharing your performances with the rest of the team.
Want to give it a try? Here’s a quick video tutorial of how it works.
For a more in-depth walk-through, as well as specific use cases for getting started with the integration, continue reading below.
In the Databox web app, navigate to Databoards and create a new Databoard. In the left-hand panel, switch to the ‘Google Sheets’ source.
Once you connect your spreadsheet, you can create your first custom metric.
The Google Sheets integration is powered by Databox’s Metric Builder tool. Metric Builder provides a user interface (UI) where non-technical users can visualize their data just by making selections in the interface. With it, there is no need to learn a new query language or even write a line of code.
So, anyone on your team can use Metric Builder to extract custom metrics from your spreadsheets in just a few clicks.
Here is a full explanation of each of the form fields that will help you build a custom metric:
You can enter a name for your metric. Alternatively, leave it blank and skip to the next fields. Once you choose a Value from your spreadsheet, we will automatically populate that Value’s row or column header as the custom metric name.
The first step is to select a column, row, or cell range that holds the numerical values you want to visualize. Put your cursor in the Value form field, then make a selection directly on the spreadsheet below by highlighting the cells in the spreadsheet with your mouse.
You can also enter a cell range manually by using the ‘A1 notation.’ (If you are unfamiliar with that concept, you can read more about ‘A!’ notation here).
Values must be numerical values, not strings. You can use currencies or other units with numeric values and we will recognize them. In fact, Google Sheets does a good job of automatically formatting the cells, so you shouldn’t need to worry about formatting them inside Databox.
The next step is to select a column, row, or cell range with dates (timestamps). Again, when this input is selected in the Databox Metric Builder interface, you can make a selection directly on the spreadsheet below it by highlighting the cells you want to visualize. Values and Dates must have corresponding values, so make sure you select a similar range of cells. Databox accepts any ‘Date’ or ‘Date time’ that is formatted inside Google Sheets.
To ensure that all cells are formatted correctly, you can select them and go under “Format > Number > Date” within your Google Sheet. If you have hours added please select “Date time” format. If your Date cells are formatted as Strings in Google Sheets (not recommended), we will assume your data is the US date if in the format (Month/Day/Year) and if no day is specified (only Month/Year), we will store values on the first of each month.
If you have additional information in your spreadsheet, for example, Country of purchase, Referer information, Browser, Product name, etc., you can reference cells or ranges for Dimensions by using ‘A1′ notation or by selecting it directly on the spreadsheet preview below the Metric Builder form.
Each metric can support up to one Dimension range. To visualize multiple Dimensions, you must create another custom metric.
Read more about Format and Scale settings in Databox in this article.
This is the last setting that is important for the correct visualization of your data.
The data type describes the data you’re pulling into Databox and depends on the use-case and kind of values. There are three data types that are described right in the interface. You can learn more about Data types in our help documentation. If you are not sure whether you’ve selected the right data type, you can use the live preview as a way to determine whether your data is being summarized correctly.
On the right side of the Metric Builder form, there is a live preview of your data, which also gives you an option to switch between visualization types like line graphs, pie charts, and numbers. We show only the appropriate visualization for the data you’ve selected.
Once the query is saved, your new metric will appear in the left-hand side panel (under this Google Sheets connection) and you can easily drag and drop it right into your dashboard.
Voila, that’s it!
In addition to visualizing data in a Databoard, you can also use your Google Sheets custom metrics in Alerts, Scorecards, and in Data Calculations.
There is no “right way” to build a Google Sheet dashboard.
However, there are certain best practices that can take you a few steps closer to developing your perfect Google Sheet dashboard.
Here are some that you might find useful.
While adding visuals to your dashboard can be fun, you shouldn’t overdo it. Too much visualized data may backfire on you and distract your key shareholders from what’s important.
Pick formats that can represent your data accordingly and use them to highlight the numbers in the dashboard that you think are most important.
Don’t use flashy color schemes. Instead, pick some neutral colors that aren’t too distracting but still do a good job of highlighting your KPIs.
You should figure out how to connect your charts/graphs in a way that they tell a specific story.
Simply displaying numbers can be confusing and your shareholders will often be short on time to analyze the meaning behind each KPI. If you are able to tell a story with your visualizations, you and your team will have more time to discuss future strategies.
Once you create your dashboard, you should be able to filter your data upon KPI variables.
By using filters, you can analyze data from specific sources and filter the dashboard metrics that fit the criterion.
In case you need additional help understanding Google Sheets, you can read our help articles.
We describe all the Metric Builder fields and provide additional examples.
Sometimes, you may run into certain issues such as missing some pieces of data.
The best way to figure out the problem is by checking out the Query log.
Follow these few steps:
It happens that users accidentally change the spreadsheet by moving rows and columns. By checking the ‘Log’, you will have a clear picture of what happened.
When referring to Values, Dates, or Dimensions, you should select a whole column or whole row, rather than fixed ranges.
That way, once you add new rows and columns, the data will automatically be recognized and your custom queries won’t need to be changed.
Discrepancies typically occur if you have previously selected the wrong ‘Data Type’ in the Metric Builder main interface.
Read this article to learn more.
Always make sure you use the ‘Date’ or ‘Date Time’ to correctly format the dates in your spreadsheet.
Similarly, you can use the ‘Numbers’ or ‘Currency’ options to ensure the correctness of your values.
The best way to define the correct format is by selecting the cells in your Google Sheet and opening ‘Format’ > ‘Number’ > ‘…’.
If you notice data from December is being stored for November, that means there is a problem in the timezone settings.
Before connecting your spreadsheet, you should check the timezone settings in your Google Sheets (under ‘File > Spreadsheet settings… > Time Zone’).
If you are using Databox, then the GSheet time zone should match the one in your Databox account (you can check it under ‘My Profile’).
Do you need a place where you can track the most important metrics from your KPI reports?
Databox customizable dashboards can help.
By using our pre-built dashboards, you will be able to integrate all of your most important data from Google Sheets into one comprehensive report. Plus, with only a few clicks, you can visualize the data and make it more understandable to other high-ranking members of your company.
Here are some of our best examples.
Making informed financial decisions comes from understanding your numbers. The Financial Health Report Example helps you with this.
Some of the things you will learn are:
With all of these questions answered, you will have a better insight into your company’s profitability.
All of your key financial data can be compiled into a single Google Sheet where you can also integrate information from the other business systems that your company uses.
Once the Sheet is set up, you can connect it to this comprehensive dashboard and view all of the key metrics in one place.
No matter how big or small your business is, keeping track of all the different departments (sales, marketing, accounting, etc.) can be overwhelming.
That is why we use the Sector Report Example.
Although you probably have team leaders whose responsibility is to meet their team goals, as an executive, you want to be kept in the loop of how things are progressing.
However, creating a specific report for each department takes up too much of your valuable time and just isn’t worth it.
By using a comprehensive dashboard such as the Sector Report, you will be able to integrate the most important KPIs from each of your departments in one place.
You will also be able to visualize all of these metrics and make the performance indicators understandable to the high-ranking members in the company.
The most valuable asset of any business is the people working there.
And even if you have the most reliable employees, you will still want to track their progress in achieving business goals.
You can do this by using the HR Report Example.
This customizable dashboard allows you to collect employee engagement data through which you will gain an insight into what’s going on among the ranks.
The best thing is, you can use it to create KPI dashboards that monitor employee sentiment and high-priority issues.
When you have this information, you can discuss it with your team leaders and create better employee engagement plans for the future.
Now let’s see a few examples of how you can build a sales report in Databox from your Google Sheets data.
Let’s take a look at this spreadsheet.
It’s a log of successful purchases for an imaginary online book shop. It includes the date and time (timestamp) of each purchase (column A), product name (B), revenue of each sale in US Dollars currency (C), referer (D), customer name (E), country of residence (F) and email (G).
We can make many custom metrics out of this spreadsheet.
To get the Revenue metric, select column C (the whole column “C:C”) for Values and for timestamps, select the whole column “A:A” for Dates.
For this kind of example, the correct Data Type is the ‘Unprocessed data (event value)’. This Data Type supports cells populated with event values for the specified Date/Timestamp and for longer Date Ranges (i.e. This Month, This Quarter, etc.), with multiple events, data will be aggregated and the total will be displayed for the time period.
The example spreadsheet includes more data than just revenue, of course.
So, let’s also create a new metric with ‘Country’ dimension (column F), to see how my revenue is distributed across different countries. To create the ‘Revenue by Country’ metric, the query would look like the screen grab below, where I chose column “F:F” as the Dimension value:
In this case, we switched to the ‘Pie’ visualization because I’m interested in the total distribution in 2018 by country.
One custom metric can use only one dimension. But, if you want to view revenue by different dimensions (besides country), create a new query with a different dimension selected and leave the Values as column C and Dates as column A.
Another common use-case for Google Sheets is when a company manually enters key performance indicators (KPIs) into a spreadsheet each month. As an example, take a look at this spreadsheet.
It’s a spreadsheet template with seven KPIs for an imaginary company.
It includes the months (row 1), sessions (row 2), new customers for each month (row 3), a calculated conversion rate (row 4), monthly revenue in US Dollars currency (row 5), average revenue per customer (row 6), expenses in US Dollars (row 7) and profit (row 8).
(As an aside, the conversion rate is calculated inside this Google Sheet. I could have also set up this calculation inside Databox with the use of the Data Calculations feature.)
Let’s make a Databoard from this worksheet…
To get the Expenses metric, let’s select the row 7 (the whole row “7:7”) for Values and months (the first row “1:1”) for Dates.
For Dates, we have entered Months in the spreadsheet, but these are actually the first day of each Month.
For this example, the correct Data Type is the ‘Daily values (processed data)’. This Data Type supports cells populated with daily (or in this case monthly) values for the specified Date/Timestamp.
In Databox, the latest updated value will be displayed (in case you had multiple columns with the same date, but different times.). For longer Date Ranges (i.e. This Year, This Quarter, etc.), data from each day will be aggregated.
In our example, monthly data will be stored in the first of each month. To have more granular data, you could store daily values in your spreadsheet, too.
Google Sheets are a useful tool for editing, organizing, and later analyzing the most important KPIs in your business.
Similar to other spreadsheets, GSheets also include built-in formulas, pivot tables, and conditional formatting.
But, even with the built-in charts that you can use for visualization, combining the charts into a comprehensive dashboard is pretty much mission impossible.
Having to manually update your GSheet each time you gather enough data is simply not worth the time or the nerves anymore.
Luckily, Databox has your back.
You can integrate your Google Sheets with our custom dashboard software to create a comprehensive report that includes insightful visualizations of your key metrics.
Combining multiple data sources in one place is one of the main advantages of Databox. By connecting data from your Google Sheets, you can create custom dashboards that showcase the most important KPIs from each of your departments.
Telling the story behind your data has never been easier.
Want to create a KPI report but you are short on time? Sign up here for our free setup service.
Hey, we’re Databox.Our mission is to help businesses save time and grow faster. Click here to see our platform in action.
Are you maximizing your business potential? Stop guessing and start comparing with companies like yours.
At Databox, we’re obsessed with helping companies more easily monitor, analyze, and report their results. Whether it’s the resources we put into building and maintaining integrations with 100+ popular marketing tools, enabling customizability of charts, dashboards, and reports, or building functionality to make analysis, benchmarking, and forecasting easier, we’re constantly trying to find ways to help our customers save time and deliver better results.
Filip Stojanovic is a content writer who studies Business and Political Sciences. Also, I am a huge tennis enthusiast. Although my dream is to win a Grand Slam, working as a content writer is also interesting.
Get practical strategies that drive consistent growth