Gtmhub allows you to extend the definition of Objectives, Key Results, Tasks, Teams and Users by creating custom fields. But how to find this information if you want to use in an insight?

Where in Gtmhub Data Source custom fields live?

In our Reference guide you can see that some of the entities have a field called customfields. It is just one per entity and contains all available custom fields with their names and values. The information is kept in a json string format:

{\"position\":\"Billing owner\",\"region\":\"EMEA\"}

This is an example of how customfields record looks for a user that has 2 custom filed named position and region. The values of those 2 fields for this specific user are Billing owner and EMEA.

How to get the value of a custom field?

The SQL query for getting the value of a field from json string is:

customfields::json ->> 'field_name'

where field_name should be replaced with the name of the custom field.

Example

Now, let say you would like to list all users on your account with their name, position and region. The SQL query for such insight would be:

SELECT 
firstname || ' ' || lastname AS name,
CASE
WHEN customfields <> '' THEN (customfields::json ->> 'position')
ELSE ''
END AS position,
CASE
WHEN customfields <> '' THEN (customfields::json ->> 'region')
ELSE 'N/A'
END AS region
FROM gtmhubusers

The query will return all users with their name, position and region. If the user does not have specified its position then the query will return emply string; if the region is not populated - then we will get N/A.

We recommend to always use CASE statement in order to ensure the query will return value when there the custom field is not populated.

How to filter your results by custom field?

If you would like to get only records that contain a specific values for a custom field, then you have to add this statement in the WHERE clause:

WHERE 
customfields <> '' AND
customfields::json ->> 'field_name' = 'field_value'

where field_name should be replaced with the name of the custom field and field_value with the value you are looking to filter by.

As the custom fields may not be mandatory, we recommend to always add a statement to check that the field is not empty ( customfields <> '' )

Example

Let's assume you would like to get only the names of all users part of EMEA region,

In this case the query will be:

SELECT 
firstname || ' ' || lastname AS name
FROM gtmhubusers
WHERE
customfields <> '' AND
customfields::json ->> 'region' = 'EMEA'

Pro tip:

To make your insight more appearing you can use our directives to visualize the users with their pictures.

Did this answer your question?