Gtmhub allows you to build insights using both R and SQL. When building insights with SQL the same concepts apply as with R insights.

The Gtmhub data warehouse is based on PosgreSQL so any functions that are supported out of the box on PosgreSQL are also supported by Gtmhub's insight engine.

"Hello, World!" insight

As with R insights, the main components of an insight are data, algorithm and user interface. To create a SQL insight use the same steps as with writing R insights only change the algorithm language from the insight editor. The language switch is located right beneath the algorithm editing area.

Insight user interface

We will use the following HTML markup for our SQL insight.

<div class='title'>{{ data.message }}</div>

The insight widgets are based on AngularJS so when the insight server responds with the calculated data you the widget will bind the response to the user interface.

Insight algorithm

For the algorithm we will use a simple select statement to select a text from the server and return it as a named column.

SELECT 'Hello, World!' as message

Executing the insight produces the following result.

SQL insights deep dive

Key concepts

The Gtmhub insight engine serialises the result from your SQL queries to JSON and binds it to the data object in each insight's scope. When your SQL insights return a single row of data with an arbitrary number of columns the data will be returned as a single JSON object. 

SELECT COUNT(*) as "count"
FROM users

The above query will return the data as follows:

{count: 1604}

When your are working with collections of data, the result will be serialised to a JSON array and returned as a child element of data object. In this case your query result will be in data.default .

SELECT 
    COUNT(*) as "count",
    issuetypename as "type"
FROM jira_issues

The result of this query will be as follows:

[{"count":179,"type":"Bug"},{"count":399,"type":"Story"},{"count":50,"type":"Task"},{"count":230,"type":"Sub-task"},{"count":49,"type":"Epic"}]

Creating an actual insight with metrics

To proceed with this example we will need some real data. The simplest way is to install a demo masterplan, which provides data. The demo masterplan creates three entities: Sales Opportunities, Sales Activities and Employees. These entities are populated with real data from a demo instance of MySql.

For the next example we will use the Sales Opportunities sample data from the demo masterplan. Our goal is to calculate the amount of opportunities in stage won. The SQL for this insight can be found below:

SELECT SUM(amount) as total_won
FROM sales_opp
WHERE lower(stage) = 'won'

The next step is to define the user interface for our insight. Since the goal is to create an insight with metrics that can be used in Dynamic Key results we will have to use the <metric> directive from Gtmhub. To get a single value insight metric you can use one of the predefined HTML templates. In the insight editor click on HTML template button and select Super Simple. This will give you the necessary boilerplate HTML, all you need to do is make sure that you reference the total_won field from insight response. Here is the resulting HTML:

<div class="title o-5 mb-2">Won Opps</div>
<div class="title-xlg positive">
    <metric field-name="total_won" name="Amount of won opps">{{ data.total_won | currency : $ }}</metric>
</div>

The metric element has two properties that must be set:

  • field-name instructs your Dynamic Key Results which field from insight response to use when calculating your KR progress.
  • name serves as a default title for KRs created from this metric.

Creating insights with visualisations

In this case you can use the insights engine to build powerful data visualisations. The caveat here is that you cannot use the resulting insights as Dynamic KRs. In the following example we will display the distribution of opportunities based on their status in a pie chart. Here is the code for the insight algorithm:

SELECT 
    SUM(amount) as stage_amount,
    stage
FROM sales_opp
WHERE lower(stage) NOT IN ('won', 'lost')
GROUP BY stage

For the visualisation we will use a pie chart. Gtmhub supports a number of charting libraries, and you can find boilerplate markup for them in the HTML snippets section of the insight editor. In this example we will use dimple

<div class="title-xlg">Opportunity stages</div>
<div class="subtle-text mb-4 bb pb-4">Total amount of opprotunities in each pipeline stage</div>
<!-- Chart container -->
<div id="opp-stages-pie"></div>
<script>
requirejs(['d3', 'dimple'], function (d3, dimple) {
  var svg = dimple.newSvg("#opp-stages-pie", 590, 400);
  var data = angular.element($("#opp-stages-pie")).scope().data.default;
  var stagesChart = new dimple.chart(svg, data);
      stagesChart.setBounds(20, 20, 460, 360)
      stagesChart.addMeasureAxis("p", "stage_amount");
      stagesChart.addSeries("stage", dimple.plot.pie);
      stagesChart.addLegend(500, 20, 90, 300, "left");
      stagesChart.draw();
})
</script>

The above markup produces the following insight view.

Finally if you need to filter your data based on some input parameters from your insight boards be sure to check out this article: Insights date range filters.

Did this answer your question?