This example assumes that you have a Google sheet with sales data. 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, and connect it to Gtmhub.

  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
    If you want to allow creation of automated key results from an insightboard you must use the <metric> component from Gtmhub.
    For more information, see Gtmhub insight components.

  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. The Google Sheet should have a column named "sale rev" with numbers.

  2. Connect the Google Sheet to Gtmhub
    For more information, see Example: Connect a Google sheet.

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

  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 }}

The metric component has two properties that must be set:

  • field-name
    Instructs your automated key results which field from insight SQL to use when calculating your key result progress.

  • name
    Serves as a default title for the automated key result created from this metric.

To preview the insight, click Execute.

The following appears (the number will vary depending on the numbers you use):

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 automated key result.
For more information, see Example: Create a automated key result.

Did this answer your question?