Custom conversions

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:

They are created and maintained by our technical team.

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 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 (e.g., partitioned by date), you must include a filter on the partition column in the WHERE clause. For example:

WHERE date BETWEEN @start_date AND @end_date

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.	✅
`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 utilised 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

Predicted value

You can utilise 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 both in the target custom conversion and in any custom conversion for which you want a predicted value based on the target 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 will serve as features for the machine learning model that will predict the value.

When you validate the SQL for your custom conversion, you'll find an option called "Predicted value." Choosing this reveals two more fields.

The "Target conversion" field 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.

You can set the maximum timeframe for linking conversions by adjusting the "Conversion window."

Once you've saved your settings, our ML model will calculate the predicted value for each conversion within about 15 minutes.

Use case

Consider this scenario: We 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, we create a modified “Lead” conversion. We enhance the SQL of this conversion by incorporating CRM data features, such as the number of times the user has contacted our sales team, the country where their business is located, and their job title. We then 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, we can view the predicted value of each lead, which is based on the value of the deals that have closed.

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 setting ensures that the data in SegmentStream remains aligned with your CRM records, accurately reflecting conversion values.

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.