Google Sheets: Create Custom Business Dashboards
You have probably learned lots of information so far if you followed along with our series of articles, so it’s time to put most of that knowledge together and start with an advanced task. This time, we are going to explain how to set up dashboards in great detail. You will be able to create a dashboard out of a complete spreadsheet, which is capable of processing huge amounts of data.
Create Custom Business Dashboards in Google Sheets
It’s quite hard, if not even impossible to interpret a large amount of raw data and tell what parts of your business are improving and where to focus your efforts. This is the thing that makes number-based records – like your site traffic, sign-up growth or store sales – frustrating to work with most of the time. You have to find bits of data, figure out what they mean according to other factors and then put it into words.
A much better approach is to create a dashboard where you can turn numbers into clear, understandable insights. Just a spreadsheet and some data integration is needed, and you have everything to build the dashboard that serves you best.
Prepare your spreadsheet skills and start using it together with Google Analytics, a great add-on for Google Sheets. Now, let’s start with learning how to transform your spreadsheet into a resourceful data application.
Build a Customizable Dashboard with the Right Tools
To create a reporting dashboard, there are two things you need: a source of data you want to use in your report and a spreadsheet app. Although you can find a bunch of spreadsheet apps on the web, Google Sheets is by far the best for this purpose. It has the advantage of being online, so you can gather data using web apps such as Google Analytics automatically.
Then, you need to choose a data source like your blog or website to keep track of the traffic statistics in your dashboard. If there is any data you want to analyze, just import it into your spreadsheet and turn it into a dashboard by following these steps.
There are built-in functions, such as =finance() that can help you to import financial data, otherwise just copy the data from your apps straight away. Zapier integrations can be useful as well, because you can log sales, subscribers, shares and even more to your spreadsheet automatically.
What to Include in the Dashboard?
Let’s start building your dashboard by first specifying the metrics that mostly matter. Keep in mind that any data could be tracked, so you can keep track of your new Twitter followers or user comments below your blog posts. The point is to have a reason to track these numbers and interpret their meaning.
It’s important to know how to measure the performance of a website. To successfully track a blog, there are a few core metrics you should take into account:
- The number of users visiting your blog
- How frequently do they read your blog?
- Do they stick around on your blog, or leave right after visiting?
- The amount of traffic that comes from organic search results
- The monthly growth of your site traffic
Here you go, these are the core metrics you should keep an eye on. We will include the more specific metrics in our dashboard later on.
If you want to create a dashboard for a company, there are some different metrics which might be more important like conversion rates from ad campaigns, signup rates, sales and so on. Pick the most important ones and start adding sections to your dashboard to be able to track them.
Get Data for Your Dashboard
Each of the specified metrics you added needs fresh data to work with. The main goal is to collect the data and turn it into a report. First, you are going to make things fully automatic by setting up the spreadsheet to be automatically filled and updated with data.
You will use the Google Analytics Spreadsheet add-on for this purpose. As you connect it to your account, it will start importing real-time data from your site into your spreadsheet. To import other data, there are a few additional options:
- With the import functions in Google Sheets, you can import website and XML data, RSS feeds, and even info from other spreadsheets as we explained in our Spreadsheet CRM article.
- Use Zapier’s Google Sheets integrations to automatically gather data about sales, subscribers and more
- There are a bunch of Pre-built add-ons for Google Sheets that can help you in pulling data in from sources like marketing and ads tools, public databases and others.
- Try the Google Apps Script and start creating your own custom add-ons that imports data for you automatically.
Create a Reporting Dashboard in Google Sheets
It’s time to actually build up your dashboard! Firstly, install the Google Analytics plugin if you haven’t done it already. This is needed for creating our report. Once it’s up, create a new spreadsheet, choose Add-ons from the menu and open the Google Analytics plugin. You will see three options for reports, and this time you will start with the Create new report option.
A new Report panel will open on your right, and there you can add a name to your report and select the particular Google Analytics account, profile and information you want to run the report from. All you need to do is to select the site that is the main subject of your traffic report.
Next, choose the metrics you want to track with your report. You can click the box below your account info, and a list of metrics will show up. There, just select the ones you have specified earlier and hit Create Report.
The add-on will create a new sheet in your spreadsheet named “Report Configuration”, populated with the data gathered by site analytics. This will be the source of data for your dashboard. Although it shows data based on the past seven days, it’s just a default option and you can change the number by writing certain start and end date in the Last N Days row. If you set it to 30 days and compare it to the previous 2 months, you can nicely interpret your month-by-month growth.
Let’s add a new sheet to your spreadsheet for dates, so you can add days in an easily editable way. This way, you can always get dynamic data for your report based on the past month or any specified date you want.
Add a sheet, create a column named This Month and define a Start and an End date for this month and the previous one. Any time you want new reports, simply update the dates or use the ‘today’ or ‘date’ Google Sheets functions and those will automatically grab the current month or date for your report.
Now, open your Report Configuration Sheet and click the cell labeled Start Date. Just enter an = sign, open the Dates Sheet you created and highlight the very first date to the current month. Hit Enter, and it will automatically fill the Report Configuration. Now repeat the process for the end date.
As you have set the start and end dates for the current month, copy and then paste the report so you have one for the previous month’s traffic as well. There is no limit, so you can do this as many times as you need, depending on how many months back you want to track it for the report. For example, track it back three months, which means that you will copy the data into two additional columns. Add the correct dates to each column and give them unique names.
If you have successfully set up the dates for the three reports, it’s time to finally run it. In Google Sheets, click Add-ons > Google Analytics and then Run Report. Google Analytics will instantly add three new sheets. These are all reports based on the metrics you specified and the chosen timeframe, which is one for each month. The title will be what you entered in the respective rows back in the configuration sheet.
The next step is to turn the Google Analytics data you gathered into a dashboard. First, select your original Sheet1 sheet and change its name to Dashboard. If you drag it to the left, that will make it the first tab as you open the spreadsheet.
Adding data and some formatting to your dashboard can be simple and fancy as well, you decide. We will show you how to create a simple version now.
You will start with inserting the metrics you want to see on your dashboard. Next, you will fill it with data like you have done it before with the data field. Select the field for a certain month’s data, hit the = button and then go back to the Sheet to find the corresponding information, highlight the cell and hit Enter. Repeat it for all cells to import the data for your report.
Now you have a dashboard filled with your site traffic’s data based on the past three months. You can add background colors or number formatting to each part to make it nicer or highlight important values. To make it easier to read, turn the metrics into charts. You can do so by highlighting the 3 months of data for the Users first, then go Insert > Charts in the Google Sheets menu. There, you can choose the chart you like, then just click Enter.
Repeat these steps for all your metrics and arrange the charts in your sheet in a way that best suits your needs. Once you are done, type in your monthly growth to your dashboard and format your items with conditional formatting. Then, start adding labels for the monthly growth numbers and apply conditional formatting to the cells where the numbers go. When you create the formula for your monthly growth, you will see that the spreadsheet will automatically format those cells.
Select the desired cell, go to Format > Conditional formatting in the menu and start setting up your conditional formatting criteria. For example, if the value in the cells is less than 0, it will change to red, and for values greater than 0 it will be green. It’s much easier to assess the momentary situation by just looking at the colors.
Setting up the formula is simple. You will populate the cell with a formula starting with =Sum( , then highlight the cell with this month’s users, subtract it from the other cell with the previous month’s visitors by highlighting it and hit Enter. You will get the difference between the two consecutive months with the correct color. Do the same for the Prior Month and your Next Prior month as well.
That’s all, now you have all the tools needed for creating a basic site analytics reporting dashboard. It shows exactly the data you want, and you don’t even need any engineering manpower.
Next, we will continue with the same topic until you finished with your own, fully functioning and nice looking dashboard.
Previously, we started with explaining why dashboards are important for data analysis and why is it a good tool for businesses. We talked about the tools you need to build a dashboard and also wrote a few sentences about each of its parts. Then, we started to dig deeper by explaining how to get data to your dashboard and make your reports using the gathered data. Now we want to continue with the topic and show you the last steps of creating a fully functional, structured dashboard and use it to interpret valuable information for your work.
Add More Metrics to Your Dashboard
Now, as you have a nice basic dashboard, you are able to track nearly any site based on the most important metrics. But if you work with a team, they might want to see more. By taking advantage of the Google Analytics add-on, you can pull in even more data, so you are free to define dimensions to your dashboard and investigate how new visitors found your site.
You can start with going back to the Report Configuration tab where you can set the metrics and add the desired new ones to your dashboard. Go to Add-ons > Google Analytics > Create Report, choose the account, property and profile just like before and proceed to the next section.
There, you can see an option labeled “Dimensions” above the “Create Report” button. Although we ignored this option before, now you will need it for your next report. Dimensions can be defined as extra layers of information for your given metrics. It basically finds where the raw metric comes from.
Let’s investigate where your new users come from, so you can distinguish between people who your site through Google Search and social networks. Add New Users to the “Metrics” field, then look for the Source field in the Dimensions section. Submit by clicking Create Report, then set the date for the desired period of time you want to monitor the data like before. Next, go back to Add-Ons and start running the report again.
A new sheet will appear in your spreadsheet labeled “New Users Sources”. It will contain the data you requested, showing the users according to the source where they found your site.
The next step is to apply this data to your reporting dashboard. Of course, there will be a lot more data generated in this report than in the previous ones, so investigate only the 10 most popular traffic sources where the new visitors come from on the dashboard. Switch back to the New Users Sources tab whenever you want to dig deeper into the sources.
To make the source data look better on your dashboard, select the whole column labeled ga:source inside the Results Breakdown section, cut it and paste it in the column next to ga:newUsers on the right. Another method to copy the data is clicking the cell in the right, then first pressing = and clicking the desired ga:source row, and hitting Enter. You can also clean up the spreadsheet a little bit by hiding the ga:source column.
Let’s begin sorting the columns, so you can finally see the sites that brought you the most users. Select both columns, then go Data > Sort Range, column X,Z > A in the menu, which means that the whole X column will be sorted backwards. A range of sources will appear in the column, starting with the one that has the most new users.
Now, as you sorted the New Users, you just need to make a top 10 out of them by turning them into a chart. Select the first ten sites together with their users, and then insert a Chart from the menu. Make it a pie chart graph, so the data can be visualized more easily.
As you can recognize, something is off here. Your main reporting dashboard doesn’t contain the chart. To solve this problem, click the pie chart and copy it with Ctrl+C or CMD+C. Then paste the graph in your main reporting dashboard and place it where you want. That’s all, now you are able to check where your users come from by simply glancing at your dashboard.
Whenever you want to apply new dates and rerun your report, just repeat the previous steps without the final paste. It’s not necessary, since the chart will automatically update itself in your reporting dashboard. You can actually customize the dashboard to look less like a spreadsheet by making the grid lines disappear between the graphs and charts. It’s simple, all you need to do is to open the View menu and uncheck the gridlines.
In Google Sheets, a dashboard can be customized however you want it to be. You can tweak graph styles, change colors or typeface and even pull in more metrics. The only thing that’s left is to share all this information or create a presentation out of it for your meeting. You can give a link to your spreadsheets to others by clicking Share on your top right and adding people’s email addresses. There is also an option to allow them to edit your spreadsheet.
To share it offline, simply download your spreadsheet in PDF or Excel formats. If you want to publish it on the web instead, just embed it into an internal report or blog post.
Beyond Analytics: How to Pull Data From Any App
There are a bunch of different metrics you could track with your team, while and they are also spread across many tools as well. Usually, your subscribers are in one app, your sales in another one, and the details of your site performance are probably hidden in your internal tools.
There are Google Sheets add-ons available for some of those tools, but for everything else, there is Zapier with 600+ app integrations. It automatically copies event attendees, new form entries, emails, PayPal sales, Twitter mentions and more right into your spreadsheet. In case you have internal apps with a handful of integrations, Zapier has MySQL or Webhooks integrations which might pull that additional data into your spreadsheet.
With Zapier, you can link your dashboard spreadsheet to your app to use the app’s data later in your reports. Start with adding a new sheet to your spreadsheet that will contain the data. Then, add columns for the items you want to track in your report. For example, you could add new columns for the address of your new email subscribers, their signup date, and the form they used to sign up.
Now, use Zapier to create a new Zap, select the application with the desired data and add it as a Trigger. You should always make sure you selected the correct options for your Trigger app. Once that’s done, you will set Google Sheets to be the Action app, then select the Create Spreadsheet Row Action.
There, you need to specify your Sheets account, your dashboard and the new sheet where the new data goes. To finalize, just click the + icon next to each row and select the appropriate data from the original app. Your Zap is complete, so you can turn it on and test it. When a new item is added to your application, such as a sale or subscriber, it will be immediately added to your spreadsheet.
Creating Your Own Dashboards
You can track various types of data and Google Sheets can transform all of them into a neat report. It’s nothing complicated, just the traditional sheet cells with the formulas, yet they still make it much easier to arrange and format your data. You can also use some modern integrations with it to copy sales, stats, subscribers and much more into your spreadsheet automatically. With a little practice, you can also turn that valuable data into a high-quality, professional dashboard with the most important data sets of your team in one place.
Beyond the standard Google Analytics stats, you can track an abundance of important data and insert it into your spreadsheet. Build your own dashboards with the knowledge you learned during our articles, and try to keep it updated whenever you need new data. Sometimes you need to just change the dates for it, but you can also use Zapier integrations to make life easier.
The point is to be fully aware of these things the next time your manager asks for certain stats, so creating reports in PowerPoint will be a thing of the past. However, to build up more detailed dashboards and gather that extra data, and to be able to format it automatically, you will definitely need at least a few Google Sheets add-ons.