ÃÛ¶¹ÊÓƵ

DocumentationExperience PlatformQuery Service Guide

ÃÛ¶¹ÊÓƵ-defined SQL functions in Query Service

Last update: Fri Jan 06 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • User
  • Developer

ÃÛ¶¹ÊÓƵ-defined functions, herein referred to as ADFs, are prebuilt functions in ÃÛ¶¹ÊÓƵ Experience Platform Query Service that help perform common business-related tasks on Experience Event data. These include functions for Sessionization and Attribution like those found in ÃÛ¶¹ÊÓƵ Analytics.

This document provides information for ÃÛ¶¹ÊÓƵ-defined functions available in Query Service.

NOTE
The Experience Cloud ID (ECID) is also known as MCID and continues to be used in namespaces.

Window functions window-functions

The majority of the business logic requires gathering the touchpoints for a customer and ordering them by time. This support is provided by Spark SQL in the form of window functions. Window functions are part of standard SQL and are supported by many other SQL engines.

A window function updates an aggregation and returns a single item for each row in your ordered subset. The most basic aggregation function is SUM(). SUM() takes your rows and gives you one total. If you instead apply SUM() to a window, turning it into a window function, you receive a cumulative sum with each row.

The majority of the Spark SQL helpers are window functions that update each row in your window, with the state of that row added.

Query syntax

OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
Example
{PARTITION}
A subgroup of rows based on a column or available field.
PARTITION BY endUserIds._experience.mcid.id
{ORDER}
A column or available field used to order the subset or rows.
ORDER BY timestamp
{FRAME}
A subgroup of the rows in a partition.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Sessionization

When you are working with Experience Event data originating from a website, mobile application, interactive voice response system, or any other customer interaction channel, it helps if events can be grouped around a related period of activity. Typically, you have a specific intent driving your activity like researching a product, paying a bill, checking account balance, filling out an application, and so on.

This grouping, or sessionization of data, helps associate the events to uncover more context about the customer experience.

For more information about sessionization in ÃÛ¶¹ÊÓƵ Analytics, see the documentation on context-aware sessions.

Query syntax

SESS_TIMEOUT({TIMESTAMP}, {EXPIRATION_IN_SECONDS}) OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{TIMESTAMP}
The timestamp field found in the dataset.
{EXPIRATION_IN_SECONDS}
The number of seconds needed between events to qualify the end of the current session and the start of a new session.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT
  endUserIds._experience.mcid.id as id,
  timestamp,
  SESS_TIMEOUT(timestamp, 60 * 30)
    OVER (PARTITION BY endUserIds._experience.mcid.id
        ORDER BY timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS session
FROM experience_events
ORDER BY id, timestamp ASC
LIMIT 10

Results

                id                |       timestamp       |      session
----------------------------------+-----------------------+--------------------
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:55:53.0 | (0,1,true,1)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:56:51.0 | (58,1,false,2)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:57:47.0 | (56,1,false,3)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:58:27.0 | (40,1,false,4)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:59:22.0 | (55,1,false,5)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:16:23.0 | (1361821,2,true,1)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:17:17.0 | (54,2,false,2)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:06.0 | (49,2,false,3)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:39.0 | (33,2,false,4)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:19:10.0 | (31,2,false,5)
(10 rows)

For the sample query given, the results are given in the session column. The session column is made up of the following components:

({TIMESTAMP_DIFF}, {NUM}, {IS_NEW}, {DEPTH})
Parameters
Description
{TIMESTAMP_DIFF}
The difference in time, in seconds, between the current record and the prior record.
{NUM}
A unique session number, starting at 1, for the key defined in the PARTITION BY of the window function.
{IS_NEW}
A boolean used to identify whether a record is the first of a session.
{DEPTH}
The depth of the current record within the session.

SESS_START_IF

This query returns the state of the session for the current row, based on the current timestamp and the expression given and starts a new session with the current row.

Query syntax

SESS_START_IF({TIMESTAMP}, {TEST_EXPRESSION}) OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{TIMESTAMP}
The timestamp field found in the dataset.
{TEST_EXPRESSION}
An expression that you want to check the fields of the data against. For example, application.launches > 0.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT
    endUserIds._experience.mcid.id AS id,
    timestamp,
    IF(application.launches.value > 0, true, false) AS isLaunch,
    SESS_START_IF(timestamp, application.launches.value > 0)
        OVER (PARTITION BY endUserIds._experience.mcid.id
            ORDER BY timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS session
    FROM experience_events
    ORDER BY id, timestamp ASC
    LIMIT 10

Results

                id                |       timestamp       | isLaunch |      session
----------------------------------+-----------------------+----------+--------------------
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:55:53.0 | true     | (0,1,true,1)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:56:51.0 | false    | (58,1,false,2)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:57:47.0 | false    | (56,1,false,3)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:58:27.0 | true     | (40,2,true,1)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:59:22.0 | false    | (55,2,false,2)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:16:23.0 | false    | (1361821,2,false,3)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:17:17.0 | false    | (54,2,false,4)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:06.0 | false    | (49,2,false,5)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:39.0 | false    | (33,2,false,6)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:19:10.0 | false    | (31,2,false,7)
(10 rows)

For the sample query given, the results are given in the session column. The session column is made up of the following components:

({TIMESTAMP_DIFF}, {NUM}, {IS_NEW}, {DEPTH})
Parameters
Description
{TIMESTAMP_DIFF}
The difference in time, in seconds, between the current record and the prior record.
{NUM}
A unique session number, starting at 1, for the key defined in the PARTITION BY of the window function.
{IS_NEW}
A boolean used to identify whether a record is the first of a session.
{DEPTH}
The depth of the current record within the session.

SESS_END_IF

This query returns the state of the session for the current row, based on the current timestamp and the expression given, ends the current session, and starts a new session on the next row.

Query syntax

SESS_END_IF({TIMESTAMP}, {TEST_EXPRESSION}) OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{TIMESTAMP}
The timestamp field found in the dataset.
{TEST_EXPRESSION}
An expression that you want to check the fields of the data against. For example, application.launches > 0.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT
    endUserIds._experience.mcid.id AS id,
    timestamp,
    IF(application.applicationCloses.value > 0 OR application.crashes.value > 0, true, false) AS isExit,
    SESS_END_IF(timestamp, application.applicationCloses.value > 0 OR application.crashes.value > 0)
        OVER (PARTITION BY endUserIds._experience.mcid.id
            ORDER BY timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS session
    FROM experience_events
    ORDER BY id, timestamp ASC
    LIMIT 10

Results

                id                |       timestamp       | isExit   |      session
----------------------------------+-----------------------+----------+--------------------
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:55:53.0 | false    | (0,1,true,1)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:56:51.0 | false    | (58,1,false,2)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:57:47.0 | true     | (56,1,false,3)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:58:27.0 | false    | (40,2,true,1)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-01-18 06:59:22.0 | false    | (55,2,false,2)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:16:23.0 | false    | (1361821,2,false,3)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:17:17.0 | false    | (54,2,false,4)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:06.0 | false    | (49,2,false,5)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:18:39.0 | false    | (33,2,false,6)
 100080F22A45CB40-3A2B7A8E11096B6 | 2018-02-03 01:19:10.0 | false    | (31,2,false,7)
(10 rows)

For the sample query given, the results are given in the session column. The session column is made up of the following components:

({TIMESTAMP_DIFF}, {NUM}, {IS_NEW}, {DEPTH})
Parameters
Description
{TIMESTAMP_DIFF}
The difference in time, in seconds, between the current record and the prior record.
{NUM}
A unique session number, starting at 1, for the key defined in the PARTITION BY of the window function.
{IS_NEW}
A boolean used to identify whether a record is the first of a session.
{DEPTH}
The depth of the current record within the session.

Pathing

Pathing can be used to understand the customer’s depth of engagement, confirm the intended steps of an experience are working as designed, and identify potential pain points impacting the customer.

The following ADFs support establishing pathing views from their previous and next relationships. You’ll be able to create previous pages and next pages, or step through multiple events to create pathing.

Previous page

Determines the previous value of a particular field a defined number of steps away within the window. Notice in the example that the WINDOW function is configured with a frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW setting the ADF to look at the current row and all subsequent rows.

Query syntax

PREVIOUS({KEY}, {SHIFT}, {IGNORE_NULLS}) OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{KEY}
The column or field from the event.
{SHIFT}
(Optional) The number of events away from the current event. By default, the value is 1.
{IGNORE_NULLS}
(Optional) A boolean that indicates if null {KEY} values should be ignored. By default, the value is false.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT endUserIds._experience.mcid.id, timestamp, web.webPageDetails.name
    PREVIOUS(web.webPageDetails.name, 3)
      OVER(PARTITION BY endUserIds._experience.mcid.id
           ORDER BY timestamp
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
      AS previous_page
FROM experience_events
ORDER BY endUserIds._experience.mcid.id, timestamp ASC

Results

                id                 |       timestamp       |                 name                |                    previous_page
-----------------------------------+-----------------------+-------------------------------------+-----------------------------------------------------
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:15:28.0 |                                     |
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:05.0 | Home                                |
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:45.0 | Kids                                | (Home)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 19:22:34.0 |                                     | (Kids)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:12.0 | Home                                |
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:57.0 | Kids                                | (Home)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:03:36.0 | Search Results                      | (Kids)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:04:30.0 | Product Details: Pemmican Power Bar | (Search Results)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:05:27.0 | Shopping Cart: Cart Details         | (Product Details: Pemmican Power Bar)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:06:07.0 | Shopping Cart: Shipping Information | (Shopping Cart: Cart Details)
(10 rows)

For the sample query given, the results are given in the previous_page column. The value within the previous_page column is based on the {KEY} used in the ADF.

Next page

Determines the next value of a particular field a defined number of steps away within the window. Notice in the example that the WINDOW function is configured with a frame of ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING setting the ADF to look at the current row and all subsequent rows.

Query syntax

NEXT({KEY}, {SHIFT}, {IGNORE_NULLS}) OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{KEY}
The column or field from the event.
{SHIFT}
(Optional) The number of events away from the current event. By default, the value is 1.
{IGNORE_NULLS}
(Optional) A boolean that indicates if null {KEY} values should be ignored. By default, the value is false.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT endUserIds._experience.aaid.id, timestamp, web.webPageDetails.name,
    NEXT(web.webPageDetails.name, 1, true)
      OVER(PARTITION BY endUserIds._experience.aaid.id
           ORDER BY timestamp
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
      AS next_page
FROM experience_events
ORDER BY endUserIds._experience.aaid.id, timestamp ASC
LIMIT 10

Results

                id                 |       timestamp       |                name                 |             previous_page
-----------------------------------+-----------------------+-------------------------------------+---------------------------------------
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:15:28.0 |                                     | (Home)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:05.0 | Home                                | (Kids)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 17:53:45.0 | Kids                                | (Home)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 19:22:34.0 |                                     | (Home)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:12.0 | Home                                | (Kids)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:01:57.0 | Kids                                | (Search Results)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:03:36.0 | Search Results                      | (Product Details: Pemmican Power Bar)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:04:30.0 | Product Details: Pemmican Power Bar | (Shopping Cart: Cart Details)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:05:27.0 | Shopping Cart: Cart Details         | (Shopping Cart: Shipping Information)
 457C3510571E5930-69AA721C4CBF9339 | 2017-11-08 20:06:07.0 | Shopping Cart: Shipping Information | (Shopping Cart: Billing Information)
(10 rows)

For the sample query given, the results are given in the previous_page column. The value within the previous_page column is based on the {KEY} used in the ADF.

Time-between

Time-between allows you to explore latent customer behavior within a certain time period before or after an event occurs.

Time-between previous match

This query returns a number representing the unit of time since the previous matching event was seen. If no matching event was found, it returns null.

Query syntax

TIME_BETWEEN_PREVIOUS_MATCH(
    {TIMESTAMP}, {EVENT_DEFINITION}, {TIME_UNIT})
    OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{TIMESTAMP}
A timestamp field found in the dataset populated on all events.
{EVENT_DEFINITION}
The expression to qualify the previous event.
{TIME_UNIT}
The unit of output. Possible value include days, hours, minutes, and seconds. By default, the value is seconds.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT
  page_name,
  SUM (time_between_previous_match) / COUNT(page_name) as average_minutes_since_registration
FROM
(
SELECT
  endUserIds._experience.mcid.id as id,
  timestamp, web.webPageDetails.name as page_name,
  TIME_BETWEEN_PREVIOUS_MATCH(timestamp, web.webPageDetails.name='Account Registration|Confirmation', 'minutes')
    OVER(PARTITION BY endUserIds._experience.mcid.id
       ORDER BY timestamp
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS time_between_previous_match
FROM experience_events
)
WHERE time_between_previous_match IS NOT NULL
GROUP BY page_name
ORDER BY average_minutes_since_registration
LIMIT 10

Results

             page_name             | average_minutes_since_registration
-----------------------------------+------------------------------------
                                   |
 Account Registration|Confirmation |                                0.0
 Seasonal                          |                   5.47029702970297
 Equipment                         |                  6.532110091743119
 Women                             |                  7.287081339712919
 Men                               |                  7.640918580375783
 Product List                      |                  9.387459807073954
 Unlimited Blog|February           |                  9.954545454545455
 Product Details|Buffalo           |                 13.304347826086957
 Unlimited Blog|June               |                  770.4285714285714
(10 rows)

For the sample query given, the results are given in the average_minutes_since_registration column. The value within the average_minutes_since_registration column is the difference in time between the current and previous events. The unit of time was defined previously in the {TIME_UNIT}.

Time-between next match

This query returns a negative number representing the unit of time behind the next matching event. If a matching event is not found, null is returned.

Query syntax

TIME_BETWEEN_NEXT_MATCH({TIMESTAMP}, {EVENT_DEFINITION}, {TIME_UNIT}) OVER ({PARTITION} {ORDER} {FRAME})
Parameter
Description
{TIMESTAMP}
A timestamp field found in the dataset populated on all events.
{EVENT_DEFINITION}
The expression to qualify the next event.
{TIME_UNIT}
(Optional) The unit of output. Possible value include days, hours, minutes, and seconds. By default, the value is seconds.

An explanation of the parameters within the OVER() function can be found in the window functions section.

Example query

SELECT
  page_name,
  SUM (time_between_next_match) / COUNT(page_name) as average_minutes_until_order_confirmation
FROM
(
SELECT
  endUserIds._experience.mcid.id as id,
  timestamp, web.webPageDetails.name as page_name,
  TIME_BETWEEN_NEXT_MATCH(timestamp, web.webPageDetails.name='Shopping Cart|Order Confirmation', 'minutes')
    OVER(PARTITION BY endUserIds._experience.mcid.id
       ORDER BY timestamp
       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    AS time_between_next_match
FROM experience_events
)
WHERE time_between_next_match IS NOT NULL
GROUP BY page_name
ORDER BY average_minutes_until_order_confirmation DESC
LIMIT 10

Results

             page_name             | average_minutes_until_order_confirmation
-----------------------------------+------------------------------------------
 Shopping Cart|Order Confirmation  |                                      0.0
 Men                               |                       -9.465295629820051
 Equipment                         |                       -9.682098765432098
 Product List                      |                       -9.690661478599221
 Women                             |                       -9.759459459459459
 Seasonal                          |                                  -10.295
 Shopping Cart|Order Review        |                      -366.33567364956144
 Unlimited Blog|February           |                       -615.0327868852459
 Shopping Cart|Billing Information |                       -775.6200495367711
 Product Details|Buffalo           |                      -1274.9571428571428
(10 rows)

For the sample query given, the results are given in the average_minutes_until_order_confirmation column. The value within the average_minutes_until_order_confirmation column is the difference in time between the current and next events. The unit of time was defined previously in the {TIME_UNIT}.

Next steps

Using the functions described here, you can write queries to access your own Experience Event datasets using Query Service. For more information about authoring queries in Query Service, see the documentation on creating queries.

Additional resources

The following video shows how to run queries in the ÃÛ¶¹ÊÓƵ Experience Platform interface and in a PSQL client. Additionally, the video also uses examples involving individual properties in an XDM object, using ÃÛ¶¹ÊÓƵ-defined functions, and using CREATE TABLE AS SELECT (CTAS).

Transcript
ÃÛ¶¹ÊÓƵ Experience Platform use Query Service From the ÃÛ¶¹ÊÓƵ Experience Platform UI, let’s navigate to queries. From the query environment, let’s click the create query button. You are now seeing a command line like user interface which allows you to create platform directly by making use of SQL statements. For instance, by typing show tables and clicking the execute button, I’m seeing all platform datasets as a response. One of these datasets is the website interactions EMEA dataset. Let’s create that dataset now.
Let’s start with some basic queries. For instance, I’m interested in knowing from what location customers of La Boutique were visiting product pages. This query will give me some insight in that. Please note the limit of ten. This is for performance reasons. When executing queries, it’s best to limit your responses to just a couple of lines while you’re testing and developing your queries. You can now see a response from Platform containing location information of Belgian cities. This response is a flattened object, and not a single value. The place_context.geo XDM object contains four attributes: schema.latitude, schema.longitude, country, and city. When such an object is declared as a column, it will return the entire object as a string. In order to query individual properties of an XDM object, we have to use the dot notation. To find out more about the structure of an XDM schema, let’s go to schemas in the Platform UI, and open the EMEA website interaction data schema. You can see the place context object alongside many other. By opening the place context object, and going a little deeper, you can see the geo objects, and in there, for instance, country code. The way to get to this property from the query service UI is by specifying this path. The same concept applies also to the schema.latitude property. Let’s go back to the Query Service UI, and optimize the query that we entered a couple of minutes ago by specifying the specific properties of the place context object. And the result is now a lot more readable and understandable.
Next, I’m interested in knowing the amount of products used per day for the last ten days. So I’m selecting the dates and making a count of the number of products to events. Next, I’m interested in having a visitor count by the hour for July 17.
I’m also interested in knowing the amount of visitors on July 16 per country and city.
As an analyst, I also need to visualize the most popular products. In this case, I want to see the five most popular products with the Lisette dress being the clear winner there. So far, we’ve only used the Query Service UI inside ÃÛ¶¹ÊÓƵ Experience Platform to execute these queries, but we can also execute the same queries from an external environment by using PSQL to achieve the same outcome. PSQL is a command line application which requires me to authenticate first. The credentials to authenticate to query service from PSQL can be found in the queries and credentials window. By clicking this icon, I’m copying the full PSQL commands to my clipboard.
Let’s open a terminal window, paste the PSQL commands, and hit enter. I’m now authenticated, and can execute queries here as well.
With the Lisette dress being so popular I’d like to have an overview of the individual EC IDs of the visitors that came to see this product. This query is showing me the first ten EC IDs. I’m also very much interested about the activity funnel on my website. Products used don’t pay money for La Boutique, purchases do. So how does the flow from a product view to an add to cart and purchase look like. The result of this query shows the fallout in the funnel. So far, we’ve been using basic queries to query experience event data. Let’s go a bit deeper now, and use ÃÛ¶¹ÊÓƵ defined functions. SQL wasn’t built for experience analysis, so ÃÛ¶¹ÊÓƵ decide to create a number of ÃÛ¶¹ÊÓƵ defined functions to make it possible to use SQL to better understand experiences. The first two ÃÛ¶¹ÊÓƵ defined functions we’ll use are the Sessionization function and the Next Previous function. The Sessionization function, it produces the visit groupings found with ÃÛ¶¹ÊÓƵ Analytics. It performs a similar time-based grouping, but now has customizable parameters. And Next and Previous help you to understand how customers navigate your site.
For this specific query, I’d like to know what people did on my website before reaching the El Silencio product page as the third page in a session. The result is a table that shows a number of journeys that occurred and how those journeys look like. You can see that the third page visit is always El Silencio, and you can also see the two steps that happened before that, and the step that happened after that. Journeys are session based with the session timeout being set to thirty minutes here. With Sessionization, a number of standard fields are made available. Let’s explore these fields. As you can see, the session structure contains the fields is new, times, time difference, number, and depth. Let’s go back to the query service UI, and continue with another Sessionization example which shows me the visitors’ sessions and page views per day.
As a final query, I’d like to do advanced call center analysis. In this example, were joining three datasets in Platform: experience events data, call center interactions which are sent by the call center to Platform, and we are now combining those datasets with loyalty data. The output gives me a dataset with EC IDs and CRM IDs, location information, feedback from the call center including the topic of the call and the customer feeling, and also loyalty information. This is a really interesting combination of data, and as an analyst I’d like to provide La Boutique management with a visualization that shows this information. To do that, I’m going to use the CTAS functionality available with Query Service. CTAS stands for create table as select, and allows me to write the output of this query back to a dataset in Platform. By going back to the query UI, in the log menu option, I’m seeing an overview of all the queries that have been made. The first one in this list is my last query. By clicking the query, I’m seeing the query overview menu pop up, and I can view the full query that was sent to Query Service. I now want to output the result of this query into a dataset in Platform. To do that I need to click the output dataset button. I’m naming my query call center analysis, and by clicking run query, the process has now started through the HDP API of query service. This process takes a couple of minutes, and once finished will have a new dataset in Platform. By going to datasets in Platform, I can open the call center analysis datasets, and preview the results. By making use of Query Service, analysts can now explore and consume data from ÃÛ¶¹ÊÓƵ Experience Platform in near real time from any environment, and perform flexible home channel queries right at the heart of ÃÛ¶¹ÊÓƵ Experience Platform. Analysts can send their insights back to ÃÛ¶¹ÊÓƵ Experience Platform for activation, and grants can now maximize the usefulness of that data to create and deliver exceptional experiences. With that, you should now be able to set up and use Query Service.
recommendation-more-help
ccf2b369-4031-483f-af63-a93b5ae5e3fb