4.3 Queries, queries, queries... and churn analysis
Query Service - Queries, queries, queries... and churn analysis
Objective
Write queries for data analyses
Write SQL queries combining online, call center and loyalty data available in Adobe Experience Platform
Learn about Adobe Defined Functions
Context
In this exercises you will write queries to analyze product views, product funnels, churn etc.
All queries listed in this chapter will be executed in your PSQL command-line interface. You should copy (CTRL-C) the statement blocks indicated with SQL and paste (CTRL-V)them in the PSQL command-line interface. The Query Result blocks show the pasted SQL statement and the associated query result.
4.3.1 Write basic queries for data analysis
Timestamp
Data captured in Adobe Experience Platform is time stamped. The timestamp attribute allows you to analyze data over time.
How many product views do we have on a daily basis?
SQL
select date_format( timestamp , 'yyyy-MM-dd') AS Day,
count(*) AS productViews
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and eventType = 'commerce.productViews'
group by Day
limit 10;Copy the statement above and execute it in your PSQL command-line interface.
Query Result
Top 5 products viewed
What are the top 5 products viewed?
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
Product Interaction funnel, from viewing to buying
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
Identify visitors with risk to Churn (visit page => Cancel Service)
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
In the next set of queries we will extend the above query, in order to get a complete view on the customers and their behavior that have been visiting the "Cancel Service" page. You will learn how to use the Adobe Defined Function to sessionize information, identify the sequence and timing of events. You will also join datasets together to further enrich and prepare the data for analysis in Microsoft Power BI.
4.3.2 Advanced Queries
The majority of the business logic requires gathering the touch-points 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.
Adobe Defined Functions
Adobe has added a set of Adobe Defined Functions to the standard SQL syntax that allow you to better understand your experience data. In the next couple of queries you will learn about these ADF functions. You can find more information and the complete list in the documentation.
What do people do on the site before reaching the "Cancel Service" page as the 3rd page in a session?
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.
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
How much time do we have before a visitor calls the call center after visiting the "Cancel Service" Page?
To answer this kind of query will we use the TIME_BETWEEN_NEXT_MATCH() Adobe Defined Function.
Time-between previous or next match functions provide a new dimension, which measures the time that has elapsed since a particular incident.
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
And what is the outcome of that contact?
Explain that we are joining datasets together, in this case we join our demo_system_event_dataset_for_website_global_v1_1 with demo_system_event_dataset_for_call_center_global_v1_1. We do this to know the outcome of the call center interaction.
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
What is the loyalty profile of these customers?
In this query we join loyalty data that we have onboarded in Adobe Experience Platform. This allows to enrich the churn analysis with loyalty data.
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
From what region do they visit us?
Lets include the geographical info, like longitude, attitude, city, countrycode, captured by the Adobe Experience Platform in order to get some geographical insights about churning customers.
SQL
Copy the statement above and execute it in your PSQL command-line interface.
Query Result
Call Center Interaction Analysis
In the queries above we only looked at the visitors that ended up contacting the call center in case of service cancellation. We want to take this a bit broader and take into account all call center interaction including (wifi, promo, invoice, complaint and contract).
You will need to edit a query, so let's first open notepad or brackets.
On Windows click "search"-icon (1) in the windows toolbar, type notepad in the "search"-field (2), click (3) the "notepad" result:
On Mac
Copy the following statement to notepad/brackets:
And replace
Do not remove /\* and \*/. Your modified statement in notepad should look like:
Copy your modified statement from notepad into the PSQL command line window and hit enter. You should see the following result in the PSQL command line window:
In the next exercise, you will persist your query (also known as create table as select or CTAS) as a new dataset that you will use in Microsoft Power BI.
Last updated