Skip to main content
Custom conversions are available only on the enterprise plan.
Custom 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 the SegmentStream technical team.
  2. Any data in your BigQuery can be used to calculate conversions.

​
Using CRM data

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

​
Creating a custom conversion

The SQL query results should consist of three columns: id, client_id, and created. Additional columns are optional based on available data.
  • The created column must always contain a valid timestamp.
  • For optional columns, assign NULL only if data is occasionally unavailable. If all values for a column are unknown, omit that column from the query.
  • If your query involves a partitioned table (for example, partitioned by date), include a filter on the partition column in the WHERE clause: 
    WHERE date BETWEEN @start_date AND @end_date
SQL field nameTypeDescriptionRequired
idSTRINGID of conversion/order used for deduplicationYes
client_idSTRINGGoogle Analytics client ID. If not provided, the conversion will be marked as (not attributed) in reports.Yes
createdTIMESTAMPDate and time of the conversionYes
valueFLOAT64The monetary value of the conversionNo
currencySTRING3-letter ISO 4217 currency code (USD, EUR, GBP). If NULL, project currency is used.No
is_qualifiedBOOLEANTrue if conversion reached target statusNo
paramsARRAY<STRUCT<key STRING, value STRING>>Array of custom parameters passed with each conversionNo
user_idSTRINGUser’s ID from the CRMNo
For lead generation businesses, collecting the conversion value is recommended as it allows 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, they update the status in the CRM system. To view users who have confirmed their purchase in SegmentStream reports, load the data from the CRM system into BigQuery. Then create a custom conversion with SQL that extracts conversions from the CRM data, along with a client identifier used to stitch the conversion to the user’s website session. The following code runs a daily check on website orders created within the last 30 days. It marks them as conversions if their CRM statuses are “confirmed” or “paid”, and collects the country, region, and city information. 
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
  date BETWEEN DATE_SUB(@start_date, INTERVAL 30 DAY) AND @end_date
Custom conversion SQL configuration

​
Predicted value

You can use a predicted value for your custom conversions. This value is calculated by linking a target conversion’s value to the probability of that conversion occurring. To activate this feature, the user_id fields in both the target custom conversion and in any custom conversion for which you want a predicted value should be based on the same identifier. Also, the CRM data that the custom conversion is built on should contain user-related data — such as geolocation, activities, and any other information particular to that user. This data serves as features for the machine learning model that predicts the value. When you validate the SQL for your custom conversion, you will find an option called “Predicted value.” Choosing this reveals two more fields:
  • Target conversion — shows all eligible conversions for value prediction. To qualify, a custom conversion needs the user_id field and at least one conversion in the past 60 days.
  • Conversion window — sets the maximum timeframe for linking conversions.
Once you save your settings, the ML model calculates the predicted value for each conversion within about 15 minutes.

​
Use case

Consider this scenario: you track two types of conversions. The first is called “Lead,” which occurs when someone submits a form. The second, “Deal Closed,” happens when a lead converts into a deal. Each deal has a unique value. To estimate the value of each lead, create a modified “Lead” conversion. Enhance the SQL by incorporating CRM data features such as the number of times the user has contacted sales, their business country, and their job title. Set the conversion value to “Predicted value,” choose “Deal Closed” as the target conversion, and set the conversion window to 40 days to accommodate the typical duration within which most leads convert into deals. After SegmentStream completes its calculations, you can view the predicted value of each lead based on the value of the deals that have closed. Predicted value configuration

​
Conversions adjustment

Enabling conversions adjustment allows you to specify a time window, in days, during which conversion data in SegmentStream can be updated based on changes detected in your CRM. This keeps SegmentStream data aligned with your CRM records. Conversions adjustment settings

​
BigQuery access issues

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