Query Service

2 min

This tutorial will show some examples of how you can view data within the AEP Datalake using SQL queries. Use cases include:

Data Analysis
Combining data from multiple datasets
Debugging data collection

We recommend using a PSQL client to run queries if you can, this is installed when you install PostgresQL on your local machine. Alternatively, you can use the User Interface within Adobe Experience Platform.

Simple Query

The first example is a simple query to show how many customers you have ingested from your CRM platform per city.

select homeAddress.city, count(*)
from demo_system_profile_dataset_for_crm_global_v1_1 
group by homeAddress.city
order by 2 desc;

Journey Analysis Query

To analyse customer journeys you need to gather the touchpoints for a customer and order them by time. The ability to query the data as sequence is provided by Query Service in the form of window functions. Window functions are part of standard SQL and are supported by many other SQL engines. Adobe has added a set of Adobe Defined Functions to the standard SQL Syntax that allow you to better understand customer experience data.

In this query we will answer, what do people do on the site before reaching the "Home" page, and what do they browse next?

With this query you will discover the first two Adobe Defined Functions SESS_TIMEOUT and NEXT. The SESS_TIMEOUT() reproduces the visit groupings found with Adobe Analytics. It performs a similar time-based grouping, but customizable parameters. NEXT() and PREVIOUS() help you to understand how customers navigate your site.

SELECT
  webPage, 
  webPage_2, 
  webPage_3, 
  webPage_4, 
  count(*) journeys
FROM(
  SELECT webPage, 
    NEXT(webPage, 1, true) OVER(PARTITION BY ecid, session.num ORDER BY timestamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).value AS webPage_2,
    NEXT(webPage, 2, true) OVER(PARTITION BY ecid, session.num ORDER BY timestamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).value AS webPage_3,
    NEXT(webPage, 3, true) OVER(PARTITION BY ecid, session.num ORDER BY timestamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).value AS webPage_4,
    session.depth AS SessionPageDepth 
  FROM (
     SELECT a._adobedemoamericas275.identification.core.ecid as ecid, 
        a.timestamp, web.webPageDetails.name as webPage, 
        SESS_TIMEOUT(timestamp, 60 * 30) OVER (PARTITION BY a._adobedemoamericas275.identification.core.ecid ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
     FROM demo_system_event_dataset_for_website_global_v1_1 a 
     WHERE a._adobedemoamericas275.identification.core.ecid IN (
         SELECT  b._adobedemoamericas275.identification.core.ecid 
         FROM demo_system_event_dataset_for_website_global_v1_1 b 
         WHERE web.webPageDetails.name = 'Home' 
         and b._adobedemoamericas275.identification.core.ecid IS NOT NULL
       ) 
     )
   )
WHERE SessionPageDepth=1 and webpage_3 = 'Home'
GROUP BY webPage, webPage_2, webPage_3, webPage_4 
ORDER BY journeys DESC
LIMIT 10;