Dashboards for business: three examples of practical use

Present-day web analytics tools allow you to keep an eye on almost every action visitors take on your website. With that said,wouldn’t you agree that simply sending user actions to Google Analytics is not nearly enough? The burning question you need to answer is: How can you gain actionable insight into your business performances from all the data you’ve collected?

In this article we share several examples of business dashboards that can be updated in real time, helping you make data-driven decisions. Such reports can be useful for marketing specialists, program developers, content managers and analysts in Ecommerce projects.

Table of contents

• Collecting data
• Processing data
• Building reports:
• — Marketing reporst
• — Conversion rate reports
• — Reports for IT department

With these dashboards, you can answer a number of sensitive questions, such as:

• How to determine the efficiency of online advertising campaigns with account of offline orders?
• Why does the conversion rate go down? Is it an advertising problem or a website problem?
• Is everything working properly on the website after the JQuery updates?

To offer a solution, we have considered the following wishes and wants:

1. Make no additional implementations or purchases.
2. Take the data from Google Analytics and use the familiar Google Sheets.
3. Bring together data from different systems in reports, with automatic (manual, if needed) import.
4. Export data in CSV/JSON format, in any amount and at any time.
5. Secure the data. Preferably encircled by an alligator-infested moat.

First things first, let’s start with talking about how to organize data collection and processing, what Google BigQuery is and how to create pivot tables in Google Sheets.

1. Collecting data

Google BigQuery is the main tool our customers choose for combining and processing data. This isn’t by accident. Data can be uploaded to Google BigQuery from any service in the relevant format and mode, and you don’t have to worry about having enough space on the server.

In case you haven’t worked with Google BigQuery before, this service allows you to process epic amounts of data with SQL-like queries. You only pay for the amount of data stored and processed. You also get a 300 USD credit to spend over 60 days, and a free 1 TB of data processed per month.

To bring all the data together, you can use OWOX BI Pipeline. It allows for sending unsampled data from Google Analytics directly to Google BigQuery in real time. The data from other services (CRM, email services, call tracking services etc.) can be imported as well, either automatically or manually.

2. Processing data

To transfer the data from Google BigQuery to Google Sheets, we use OWOX BI Pipeline. With the add-on, not only can you process data with SQL queries, but also save the queries for future use.

Oh yes, you will, of course, need to use SQL to build queries. But there are no restrictions on the number of metrics and the structure of reports. In addition, all the data you get is complete and unsampled.

Don’t worry too much about SQL anyway. Developers only create queries once, and subsequently the data in Google Sheets is updated automatically or on demand:

3. Building reports

According to our experience, it’s more convenient to build reports using pivot tables and conditional formatting.

3.1. Marketing

The following table compares the main indicators of advertising campaigns — the number of sessions, revenue and ROAS over the weeks.

• The level of segmentation (source, medium or campaign) can be easily changed.
• ROAS is calculated with account of fulfilled orders, margin on goods and orders in brick-and-mortar stores. For example, if a buyer used a loyalty card (or any other form of identification).

Please note that, unlike number of sessions or revenue, the ROAS indicator should be added as a Calculation Field, otherwise it will be incorrectly calculated when changing the depth of segmentation.

3.2. Measuring conversion rate

The key micro conversions, such as adding to the cart, proceeding to checkout, and confirmation of an order, can be used to monitor the efficiency of a website. We recommend that you divide them by page types and browser, and then stick to a few simple rules to easily support and update reports on the micro conversions:

• One document — one department;
• One metric — one sheet (and one SQL query).

If you need to combine several metrics for a pivot table, you can do it in this way:

Take a look at the next example. Let’s say the conversion rate has dropped. Use the report to check whether this is due to the website as a whole, or to a particular page. Thus, you will see that the conversion rate went down mostly on specific product pages, and especially on Firefox. Developers made a mistake when updating jQuery, and you managed to quickly detect and locate the mistake thanks to the dashboard.

• You can analyze micro conversions with segmentation by every page type and browser.
• You get relevant data that updates up to every 5 minutes.

Note that if there’s not enough data for comparisons, this data is automatically filtered and does not distort the valid data.

3.3. IT

It’s also useful to track some technical indicators. For example:

1. Server response time.
2. Page load time.
3. Share of JavaScript errors per session.

It will be much faster and easier to pinpoint the cause if you also segment these metrics by page type and browser.

We should also note that measuring these parameters is performed «from the user’s point of view.» This is why such data is more representative than that obtained from server monitoring tools (e.g. Zabbix or Munin).

Bottom line

So, where are the alligators? They are in Google data centers, fiercely guarding the data.