Gtmhub allows you to extend the definition of objectives, key results, tasks, teams, and users by creating custom fields.
For more information, see Create custom fields.
The following article explains how to use these custom fields in an insight.
Access a custom field
The Gtmhub data source contains an entry named
customfields. It contains the custom fields of the following:
GtmhubMetrics- key results
GtmhubMetricSnapshots- key result updates
The customfields record contains all available custom fields with their names and values. The information is kept in a JSON string format.
EXAMPLE: The following is an example of how the
customfields record looks for a user that has two custom field -
region. The values of these two fields for this specific user are Billing owner and EMEA:
Get the value of a custom field
To get the value of a custom field from a JSON string, use the following SQL query:
customfields::json ->> 'field_name'
field_name with the name of the custom field.
EXAMPLE: You want to list all users on your account with their
region. The SQL query should look like the following:
firstname || ' ' || lastname AS name,
WHEN customfields <> '' THEN (customfields::json ->> 'position')
END AS position,
WHEN customfields <> '' THEN (customfields::json ->> 'region')
END AS region
This query returns all users with their name, position, and region.
If the user's position is not specified, the query returns empty string
If the region of the user is not specified, the query returns
RECOMENDATION: We recommend using
CASE statements to ensure the query returns a value when the custom field is not populated.
Filter results by custom field
If you would like to get only records that contain a specific values for a custom field, you can use the following statement:
customfields <> '' AND
customfields::json ->> 'field_name' = 'field_value'
field_name with the name of the custom field and
field_value with the value that you want to filter by.
RECOMMENDATION: If the custom field is not required, we recommend using statement to check that the field is not empty -
customfields <> ''
EXAMPLE: You want to get the names of all users that are part of the EMEA region only. The SQL query should look like the following:
firstname || ' ' || lastname AS name
customfields <> '' AND
customfields::json ->> 'region' = 'EMEA'