Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Searching Customer Data using the Query API in the SAP Customer Data Platform

Objective

After completing this lesson, you will be able to construct code on how to utilize the Query API to effectively search and retrieve customer data.

Introduction

In this lesson, you will see what a Query API looks like and how to use APIs to manage both customer and customer activity data within the SAP Customer Data Platform.

The Query API

We can leverage the Query APIs to search, retrieve, and delete customer and customer activity data within the SAP Customer Data Platform.

Use the Query APIs to:

Profile Data	Activity Data
Retrieve customer profile data	Retrieve customer activities data
Delete customer profile data	Retrieve a customer activities overview

The following are some of the Query API endpoints:

	Query API Endpoints
Query All Customers	GET /api/businessunits/{businessUnitId}/views/{viewId}/customers
Query Single Customer	GET /api/businessunits/{businessUnitId}/views/{viewId}/customers/{customerId}
Delete Single Customer	DELETE /api/businessunits/{businessUnitId}/views/{viewId}/customers/{customerId}
Query Activities from a Single Customer	GET /api/businessunits/{businessUnitId}/views/{viewId}/customers/{customerId}/activities

Here is an example of a Query API call:

The above image is an example of a Query API call displaying the query and authentication parameters, along with the JSON response payload. The endpoint URL includes query parameters for query, userKey, secret, and purposesIds.

The labels indicate:

API Authentication for the userKey and secret parameters.
Processing Purposes for the purposesIds parameter.
Profile View ID for the {{ucpViewId}} placeholder in the endpoint URL
Customer ID for the {{customerId}} placeholder in the endpoint URL.
SQL-like Query for the query parameter with the value select * from Orders.
Customer Activity Data points to the JSON response body.

The response body is shown in JSON format, containing customer activity data with fields like docSize, _id, created, updated, and attributes.

The API call returns all the activities (Order in this case) for the specified customer, in the specified customer profile view (Unified Profile in this case), and under the specified Processing Purpose. The RESTful API response is in the form of a JSON object and a successful HTTP status of 200 response will include the above attributes.

To query customer profiles instead of activities, the query string would as shown below, but with the addition of a WHERE clause to narrow down the customer profiles returned.

query = select * from profile

Query Syntax Specification

The SAP Customer Data Platform customer data queries use SQL syntax, although some standard SQL keywords are not supported. For example, the (GROUP BY) SQL clause is unsupported, and using it in the query string will produce an error.

A flowchart illustrating the query syntax specification for a SAP Customer Data Platform query. It starts with the ‘select’ clause, which includes 'field names' and 'mathematical functions'. The next element, 'from', specifies the 'data source name'. It is followed by the 'where' clause, which includes comparison operators such as ‘equal’, ‘not equal’, 'greater than', 'greater than or equal', 'less than', or 'less than or equal, the logical operators 'and' and 'or', and finally 'is null' and 'is not null'. The next clauses are ‘filter’, ‘order by’, and finally ‘start’ and/or ‘limit’.

The SAP Customer Data Platform Query Syntax Specification in a Tree Format.

You must write the query string clauses in the following order to avoid generating an error:

select
from
where
filter
order by
start or/and limit

Here are some examples of query usage:

When querying string values, the query value must be wrapped in double quotes.

SELECT * FROM profile WHERE attributes.name = "John Doe"

When querying non-textual values, for instance an integer, the value must not be wrapped in quotes.

SELECT * FROM profile WHERE attributes.age = 42

Search for profiles that are part of a segment.

Code Snippet

1
SELECT * FROM profile WHERE having(segments, (segments.name = "segment name" and segments.value = "segment value"))

Querying the Customer and Activity Data in the SAP Customer Data Platform

Prerequisites

In this section, you will practice how to:

Locate the Common Query API Parameters
Call the Query API to Search for All Customer Profiles
Call the Query API to Search for a Single Customer Profile
Call the Query API to search for a Single Customer Activity

Before starting, make sure that you:

Access your SAP Customer Data Platform tenant or the SAP practice system
Create or use an existing Business Unit
Create or use an existing Server Application
Ingest or use an existing customer profile and Order activity
Create or use an existing Processing Purpose

Practice system Options:

SAP practice system

Step 1. Locating the Common Query API Parameters

In this section we will identify common query API parameters used to build REST API calls to SAP Customer Data Platform.

1.1 The very first one is the BASE_URL, which prefixes the Endpoint URL for our REST API calls. For SAP Customer Data Platform this value is https://cdp.eu5.gigya.com/api.

1.2 Next, let’s find the value of the first Query API call parameters, the VIEW_ID . It determines whether you are querying for Unified Profiles or Contextual Customer Profiles. On the SAP Customer Data Platform Console main menu, choose Search under the Customers menu group.

By default, new Business Units will only hold Unified Customer Profile Views.

Note

If you have both Unified and Contextual Customer Profile Views, the system will present you with a dropdown to choose between Unified and Contextual Customer Profile views. Business units created before 15 April 2024 had both views available by default. To help manage costs, business units created after that date only have the Unified Contextual Profile view enabled. If you choose to configure a Contextual Customer Profile view for a new business unit, then you’ll see the dropdown.

1.3 Copy the value of the business-unit path, which we’ll refer to it as BU_ID. In the screenshot below this value appears as 4_iNfbhGDrBGciUrxckeBfJA.

1.4 Copy the value of the viewId parameter, which we’ll refer to it as VIEW_ID. In the screenshot below this value is shown as HAPxPF10AHr1bASCaGU_dQ.

1.5 Back to the SAP Customer Data Platform Console’s main menu, choose Processing Purposes. Then click on the three dots of the Processing Purpose card and choose Edit.

Processing Purposes feature of the SAP Customer Data Platform Console showing a Processing Purpose called ‘tos’ on a card. The Edit option is highlighted

1.6 Copy the last path part of the URL, which we’ll refer to as the PURPOSE_ID. In the screenshot below this value is shown as HFZPPZxxFQUebd3ksLoTaQ.

The Edit Purpose feature of the SAP Customer Data Platform Console shows parts of a Processing Purpose called ‘tos’ on the screen. The purpose id is highlighted at the end of the URL path.

We now have all the common requirements for using the Query APIs: the Base URL, USER_KEY, SECRET_KEY, BU_ID, VIEW_ID, and PURPOSE_ID.

Note: Please refer to Unit 1 Lesson 1 Creating Admin Credentials for API Access in SAP Customer Data Platform on how to supply the values for USER_KEY and SECRET_KEY.

The last two specific Query API Parameters are CUSTOMER_ID (only present when retrieving data from a single customer), and QUERY parameter (when the API call allows custom search).

Result: You have successfully located the Query API Parameters.

Step 2. Calling the Query API to Search for All Customer Profiles

2.1 Let’s now build a SAP Customer Data Platform REST API call that queries all customer profiles. The Endpoint URL template is as follows:

Code Snippet

1
BASE_URL/businessunits/BU_ID/views/VIEW_ID/customers?purposeIds=PURPOSE_ID&query=PROFILES_QUERY&userKey=USER_KEY&secret=SECRET_KEY

2.2 The PROFILES_QUERY placeholder refers to a SQL query that returns all customer profiles:

SELECT * FROM profile.

2.3 Replacing all the placeholders in all caps from the endpoint URL template, we get:

Code Snippet

1
https://cdp.eu5.gigya.com/api/businessunits/4_iNfbhGDrBGciUrxckeBfJA/views/HAPxPF10AHr1bASCaGU_dQ/customers?purposeIds=HFZPPZxxFQUebd3ksLoTaQ&query=SELECT%20%2A%20FROM%20profile&userKey=AI32XTaizCgb&secret=tnhJ6U6NPAiGbAcX2nwu%2F7V%2F3VXp5Cmu

Note

Remember that the HTTP standard requires that you URL-encode each parameter value before inserting them into the URL above.

2.4 After copying this URL to a browser, or using your preferred REST-compliant client application, you can see the JSON payload response containing a list of customer profiles such as the one shown in the image below. Let’s use the opportunity to copy the value of profiles[0]._id and refer to it later in this lesson as CUSTOMER_ID.

Browser showing the JSON payload response to the “query all customer” REST API call. The first customer id is highlighted in the screenshot.

Step 3. Calling the Query API to Search for a Single Customer Profile

3.1 To search for a single customer profile, use the following endpoint URL template:

Code Snippet

1
BASE_URL/businessunits/BU_ID/views/VIEW_ID/customers/CUSTOMER_ID?purposeIds=PURPOSE_ID&userKey=USER_KEY&secret=SECRET_KEY

3.2 After replacing the placeholders in all caps, we get:

Code Snippet

1
https://cdp.eu5.gigya.com/api/businessunits/4_iNfbhGDrBGciUrxckeBfJA/views/HAPxPF10AHr1bASCaGU_dQ/customers/pQOoi2J5hFi5k3d3aEbVtu16kFSziPSv?purposeIds=HFZPPZxxFQUebd3ksLoTaQ&userKey=AI32XTaizCgb&secret=tnhJ6U6NPAiGbAcX2nwu%2F7V%2F3VXp5Cmu

3.3 After opening the above URL in a browser, we are presented with the following JSON payload response, containing the customer profile of a single customer:

Browser showing the JSON payload response to the query of a single customer using a REST API call.

Step 4. Calling the Query API to search for a Single Customer Activity

4.1 To search for a single customer activity, use the following endpoint URL template:

Code Snippet

1
BASE_URL/businessunits/BU_ID/views/VIEW_ID/customers/CUSTOMER_ID/activities?purposeIds=PURPOSE_ID&query=ACTIVITIES_QUERY&userKey=USER_KEY&secret=SECRET_KEY

4.2 This API call is designed to provide activity records for a single customer and activity type. It requires a query referencing a single activity name that returns customer activity records in the JSON payload response. For example, to see all orders from a given customer, we would use the following query parameter value:

SELECT * FROM Orders

4.3 After replacing the placeholders in all caps, we get:

Code Snippet

1
https://cdp.eu5.gigya.com/api/businessunits/4_iNfbhGDrBGciUrxckeBfJA/views/HAPxPF10AHr1bASCaGU_dQ/customers/pQOoi2J5hFi5k3d3aEbVtu16kFSziPSv/activities?purposeIds=HFZPPZxxFQUebd3ksLoTaQ&query=SELECT%20%2A%20FROM%20OrdersuserKey=AI32XTaizCgb&secret=tnhJ6U6NPAiGbAcX2nwu%2F7V%2F3VXp5Cmu

4.4 After opening the above URL in a browser, we are presented with the following JSON payload response containing a single customer profile:

Browser showing the JSON payload response to the query of all orders for a single customer using a REST API call.

Exercise Walkthrough

The following video demonstrates the full sequence of steps given above:

Locating the Common Query API Parameters
Calling the Query API to Search for All Customer Profiles
Calling the Query API to Search for a Single Customer Profile
Calling the Query API to search for a Single Customer Activity

Searching Customer Profiles and Activities using the Query API

Video Summary

In this video we demonstrated the full sequence of steps as detailed in the lesson above. We first located the common query API parameters and built and executed the three query API calls shown in this lesson: search all customer profiles, a single customer profile, and activity records of a single customer.

Lesson Summary

In this lesson, you learned the SAP Customer Data Platform query API and its SQL like syntax specification, how to construct a query API call by specifying the query and authentication parameters to retrieve customer profile and activity data within the SAP Customer Data Platform.

Log in to track your progress & complete quizzes