Creating Advanced Batch Segments

Introduction

You’ve identified a potential group of users that can be targeted with tailored experiences and content, but how can we group these users into a segment with Sitecore CDP and Personalize? Lucky for us, CDP comes with batch segments, which will help us group our users.

If you’d like to learn more about what batch segments are inside of CDP, and how often they are recomputed, check out Sitecore’s documentation on batch segments.

Scenario

In our example, our foobar organization has a hypothesis that grouping users that are considered frequent visitors, and then personalizing specifically to them, can help us increase conversions.

The criteria for our segment will be:

-        User must have at least 3 sessions

-        Only web sessions are counted

-        User must not be identified in CDP

Creating advanced batch segments

To begin creating a new advanced batch segment inside of Sitecore CDP and Personalize, simply go to your CDP & Personalize dashboard. Then, expand customer data and select “Batch segments”

Now, select “Create Segment” on the top right.

Give your segment a name, and then click “Create”

To support our business requirements, we need to use the Advanced Mode, so let’s switch over to that mode by finding that option at the bottom right of your Batch Segment landing page.

You’ll get a model asking you to confirm, let's select “Yes, switch”

Finally, you should see a panel with a blank page to write SQL. Before we start creating any SQL queries, let's go over how to create valid SQL for Advanced Batch Segments.

Understanding Advanced Batch Segments

Let’s understand how the Advanced Batch Segment SQL queries will work under the hood. What type of SQL engine is it using? How can I view the data structure?

SQL Engine

The persistent data we care about in Sitecore CDP is stored inside S3 buckets. To query the data in the S3 buckets, Sitecore CDP & Personalize allows us to write Amazon Athena SQL queries. If you’re familiar with MySQL or MsSQL, Athena will be very familiar with a few exceptions that can be easy to identify by referencing the Athena syntax documentation.

Data Structure

Writing a SQL query will be nearly impossible if you don’t understand how the underlying data is structured. While there is no easy way to directly view databases, the data that is queried matches the data schema on Sitecore CDP data lake exports.

Snippets

Sitecore CDP & Personalize also includes snippets that can help you make sense of the data and how it can be queried.

Segmenting Unidentified Frequent Users

Now that we have an empty batch segment created and an understanding of how to query the data, let's get to writing the SQL query!

Let’s recall our requirements:

-        User must have at least 3 sessions

-        Only web sessions are counted

-        User must not be identified in CDP

To do this, we’ll query all our guests (users), count the number of web sessions they’ve had, and finally confirm that they are still unidentified (VISITOR).

SELECT g.meta_ref as guest_ref
    FROM (
      SELECT s.meta_guest_ref
      FROM sessions s
      WHERE upper(s.type) = 'WEB'
      GROUP BY s.meta_guest_ref
      HAVING count(*) >= 3

     ) s
  INNER JOIN guests g
    ON s.meta_guest_ref = g.meta_ref
  WHERE upper(g.type) = 'VISITOR'
  GROUP BY g.meta_ref

The new segment should look like the following:

Finally, click save. Then, test the SQL to ensure it is valid and that we get the expected results.

Testing and publishing the batch segment

Our advanced batch segment SQL is complete, but we need to confirm that everything is working as intended.

As the first test, we can open our batch segment, and then select “Run SQL”

It may take a few seconds for the query to complete. Once the query is completed, a results pane will appear.

Note: If your results panel shows zero rows returned, make sure you have valid data and that it has been at least 24 hours since that data was added to CDP.

In our CDP instance, we can see that two results were returned, and we can also view the sample that came back. Clicking on view sample prompts us with a model that has links to the guests that were identified to be part of this segment.

Clicking on the icon to the right of a user will take you directly to the user’s CDP profile. Doing so allows you to confirm that the user has all the data you expect a user to have to be included in the segment.

Another way to test a batch segment that has already been created is by clicking on the batch segment and then selecting “Calculate audience”

Again, this may take a few seconds to complete, but you should see an estimated audience size once it does.

Note: If your calculated audience is zero, make sure you have valid data and that it has been at least 24 hours since that data was added to CDP.

Once you’re satisfied with your testing, simply click on the “Start” button to publish your segment. Now, every 24 hours, Sitecore CDP will re-process all started segments. You can then use these segments for targeting.

Conclusion

Targeting a specific group of users that meet many different criteria can be easy to solve with Sitecore CDP Batch Segments. We gathered the requirements, created the batch segment, changed to advanced mode, dug a little deeper into how CDP data is stored and queried, wrote the SQL required, tested the segment, and finally published it! Now, the new segment can be used for targeting across all of Sitecore CDP and Personalize features.