Run queries

Adobe Experience Platform use Query Service From the Adobe 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 Adobe 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 Adobe defined functions. SQL wasn’t built for experience analysis, so Adobe decide to create a number of Adobe defined functions to make it possible to use SQL to better understand experiences. The first two Adobe defined functions we’ll use are the Sessionization function and the Next Previous function. The Sessionization function, it produces the visit groupings found with Adobe 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 Adobe Experience Platform in near real time from any environment, and perform flexible home channel queries right at the heart of Adobe Experience Platform. Analysts can send their insights back to Adobe 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.