Can I perform Batch Segmentation by aggregate values in Sitecore CDP?

Question

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?

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:

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

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