Skip to main content

Write and run an SQL query in Sitecore CDP

Abstract

How to write and run an SQL query to create a segment (Sitecore CDP).

You can switch to advanced mode to write a complex SQL query if you know SQL and are familiar with the Sitecore CDP data model. We recommend switching to advanced mode if you want to apply regression logic, quartiles, and other advanced segmentation capabilities. Before you write your SQL query, you must start to create a segment in basic mode before you switch to advanced mode.

Write an SQL query to create a segment

To create a segment by writing an SQL query:

  1. In the segment builder, click Advanced Mode. A confirmation window displays.

  2. Click Yes. The SQL editor displays. Sitecore CDP converts the segment you created in basic mode into SQL statements that you can further edit.

    Note

    You must follow the rules for writing SQL queries in the SQL editor as you write your SQL query.

    If you do not want to copy and paste values, fields, or code snippets, you can now run the SQL query.

  3. To copy and paste values and fields, on the Data tab, click the arrow cdp_seg_downarrow.png icon on the entity block to see the available fields and values you can include in the SQL query. Click the copy cdp_seg_copyicon.png icon next to the field or value and paste it into the SQL editor.

    Viewing fields and values on the Data tab.

  4. To copy code snippets, on the Snippets tab, click the entity block, click the Copy link next to the code you want to copy, and paste the code snippet into the SQL editor.

  5. Schedule the segment to be included in your organization's nightly segment build. You must do this to use the segment within Sitecore CDP.

Run an SQL query

After you write an SQL query, you can run the query to return the guest_ref count and view a sample of 10  guest_ref records that are included in the segment.

To run an SQL query:

  1. In the segment builder, click Run. Sitecore CDP displays the rows returned and the time it took to run the query, as shown in the following image:

    Running an SQL query in Sitecore CDP.

  2. Click View sample. The View Execution Sample window displays a maximum of 10 guest_refs that are included in the segment results.

  3. Click the new window cdp_seg_launch.png icon to open the guest profile in a separate browser window.