Overview

This example works with real data. It works with a database entity that has a list of all monthly sales. These entities are populated with real data from a Google Sheet that you create and connect. The SQL query sums the amount of the deals that are won and displays it in an insight.

In this example, you do the following:

  1. Create the data
    You first create a Google Sheet, then a data entity, and finally connect them.
  2. Create the insight
    You create the insight that will display the total revenue for the month
  3. Write the SQL query
    In the SQL query you define a local variable total_won that sums the amounts of all total sales coming from deals that are won.
  4. Define the user interface
    Because the goal of this example, is to create an insight with metrics that you can later use as dynamic key results, you must use the <metric> directive from Gtmhub.
    For more information, see Gtmhub insight directives.
  5. Add the insight to an insightboard
    You do this to be able to see the created insight on your board.

Create the data

This example uses the Monthly sale opportunities sample data. To create this data source, perform the following:

  1. Create a Google Sheet and connect it to Gtmhub.
    For more information, see Example: Connect a Google sheet.
  2. Create a custom data entity to use in the insight.
    For more information, see Example: Create custom data entities.
  3. Map the created entity to the Google Sheet data source
    For more information, see Example: Map a custom entity to a data source.

Create the insight

To create an SQL insight, perform the following:

  1. In the navigation pane, expand Insights and click Insightboards.
  2. In the toolbar, click Manage insights.
  3. Expand Create new insight and click Use SQL expert editor.
  4. In Title, enter Total monthly sales
  5. In Description, enter The sum of all won deals for the month
  6. Click Create insight.
    The SQL expert editor appears.

Write the SQL query

In the SQL editor, enter the following query:

SELECT SUM(sale_rev) as total_won
FROM sales_monthly
WHERE lower(sale_stage) = 'won'

Define the user interface

To get a single value insight metric, use one of the predefined HTML templates.

To do this, perform the following:

  1. In the bottom of the editor, click HTML templates.
  2. Click Super simple.
  3. Use the total_won field from the insight response, by modifying the template in the following way:
<div class="title o-5 mb-2">Monthly revenue</div>
<div class="title-xlg positive">
<metric field-name="total_won" name="Amount of won deals">
{{ 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 key result progress.
  • name
    Serves as a default title for the dynamic key result created from this metric.

To preview the insight, click Execute.

The following appears:


Add the insight to an insightboard

Perform the following:

  1. In the navigation pane, expand Insights and click Insightboards.
  2. Click the insightboard where you want to add the Total monthly sales insight.
  3. In the upper-right corner, expand the menu.
  4. Click Add insights.
  5. Select the Total monthly sales and click Add selected.
  6. Click Save changes.

Next steps

You can use the response of this insight to create a dynamic key result.
For more information, see Example: Create a dynamic key result.

Did this answer your question?