logo

Custom conversions

Custom conversions are available only on enterprise plan
Custom conversions are available only on enterprise plan
These conversions enable you to create conversions by writing an SQL query that uses any raw data present in your Google BigQuery. A common use-case is tracking offline conversions through CRM systems.
Benefits of using custom conversions:
  1. They are created and maintained by our technical team.
  1. Any data that is in your BigQuery can be used to calculate conversions.

Using CRM data

SegmentStream currently supports data sources for the following CRM tools, allowing you to directly load data into the connected Google BigQuery dataset:
  • HubSpot
  • Salesforce
  • Microsoft Dynamics
  • Pipedrive
If you utilise a different CRM tool, you will need to manually export the CRM data to BigQuery. Alternatively, feel free to reach out to your SegmentStream manager for assistance.

Creating a custom conversion

The SQL query results should consist of 3 columns.
If data is unavailable for any required columns, please use NULL as the value, except for the "created" column, which must always contain a valid timestamp.
SQL field name
Type
Description
Required
id
STRING
Id of conversion/order used for deduplication.
client_id
STRING
Google Analytics client ID. If not provided, the conversion will be marked as (not attributed) in the reports.
created
TIMESTAMP
Date and time of the conversion. Required field.
value
FLOAT64
The monetary value of the conversion
currency
STRING
Currency in which the value is reported. 3-letter currency ISO 4217 code, i.e. USD, EUR, GBP. If NULL, project currency will be used.
is_qualified
BOOLEAN
True if conversion reached target status, i.e. status=”complete”.
params
ARRAY<STRUCT< key STRING, value STRING>>
An array of any custom parameters that are passed with each conversion, i.e. key = “city”, value = “London”.
user_id
STRING
User’s ID from the CRM.
For lead generation businesses, collecting the conversion value is highly recommended as it will allow SegmentStream to enhance its ML model.

Collecting geo data from CRM

To improve your market selection options for launching Geo Tests, consider collecting geo location data in your CRM.
When adding geo data from your CRM, include it in the params column as key-value pairs. The key should indicate the geographical scope (city, country, or region), while the value should specify the name of the geo location.

Example

After a user purchases a service on your website, they need to confirm their order when contacted by your call centre staff. Once a call centre operator receives confirmation from a user, they update the status in the CRM system.
To view the users who have confirmed their purchase in the SegmentStream reports, you must load the data from the CRM system into your BigQuery. Subsequently, a custom conversion has to be created with an SQL that will extract conversions from the CRM data, along with a client identifier that will be used to stitch the conversion to the user's website session.
The following code will conduct a daily check on website orders created within the last 30 days. It will mark them as conversions if their statuses in the CRM are set to 'confirmed' or 'paid'. Additionally, it will gather the country, region, and city information of the conversions. This data will be utilized by SegmentStream's Geo Tests module.
sql
SELECT id, userId AS client_id, createdAt AS created, orderValue AS value, 'GBP' AS currency, IF(orderStatus IN ('confirmed', 'paid'), TRUE, FALSE) AS is_qualified, ARRAY(SELECT AS STRUCT * FROM UNNEST(properties) WHERE key IN ('country', 'region', 'city')) AS params FROM `bq-project-id.dataset_id.CRM_order_table_5806956` WHERE BETWEEN DATE_SUB(@start_date, INTERVAL 30 DAY) AND @end_date
Image without caption

BigQuery access issues

If the data from your CRM is exported to a different project than the one specified in the SegmentStream BigQuery settings, you will need to provide the SegmentStream service account email with the BigQuery Data Viewer and BigQuery User roles for that project before setting up custom conversions.