I want to create a batch segment in CDP based on a custom event value aggregated from all page visits.

For example, each user gets a number of points for visiting a specific page and these points are saved in CDP as a part of the event extension object:

const eventData = {
    channel: "WEB",
    currency: "EUR",
    pointOfSale: "TestPOS",
    language: "EN",
    page: "home"

  const extensionData = {
    customKey: 10

  await engage.pageView(eventData, extensionData);

Now I need to configure a batch segment that will include website visitors with a total sum of points from all events and sessions greater than 100.

How can this scenario be achieved in Sitecore CDP?

1 Answer 1


Even though aggregate functions are not available in the Batch Segmentation UI, this is something you can achieve with SQL queries in the advanced mode.

You can prepare a basic filter with you custom field through the UI and switch to the advanced mode:

Batch Segmentation UI

Then update the auto-generated SQL query by adding an aggregate function as required: Advanced mode

Here is an example of query that selects customers with the sum of all their points greater than 100:

select g.meta_ref as guest_ref
from (select s.meta_guest_ref 
from tenant_sandbox.events as e
inner join tenant_sandbox.sessions as s
on e.meta_session_ref = s.meta_ref
where upper(e.type) = upper('VIEW') and (cast(e.ext['customKey'] as varchar) != '' )
group by s.meta_guest_ref
having sum(cast(e.ext['customKey'] as double)) > 100.0) as o //aggregate function
inner join tenant_sandbox.guests as g
on o.meta_guest_ref = g.meta_ref 
group by g.meta_ref

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.