Query Editor UI

9 min

description: Getting Started with Data Distiller

Query Editor #query-editor

Data Distiller provides a user interface that can be used to write and execute queries, view previously executed queries, and access queries saved by users within your organization. The Query Editor enables you to write and execute queries without using an external client.

To start this exercise click on Queries in the left side navigation then click on Create Query.

Let's test and make sure that we are connected to run a query.

Type in the following query:

SELECT COUNT(_adobeamericaspot5.personDetails.loyaltyDetails.loyaltyLevel)
FROM crm_fsi;

Then run the query. You should get a count of 5000 in the results tab.

Now that we have confirmed that we are all connected let's begin the exercise. You have been tasked with joining customer profile data with event data that are stored in 2 separate datasets in AEP and need to use Data Distiller to join this data into a single dataset.

You will run a query to grab information from a CRM Profile by grabing the customers favorite city and state to travel to, if they have a reward card, and will filter the data by gold customers.

SELECT _adobeamericaspot5.memberInfo.branchCity as branchCity,
    _adobeamericaspot5.memberInfo.branchState as branchState,
    _adobeamericaspot5.memberInfo.hasRewardCard as hasRewardCard
FROM crm_fsi
WHERE _adobeamericaspot5.personDetails.loyaltyDetails.loyaltyLevel LIKE '%Gold%';

Now that you have selected some fields from CRM Profile dataset, you are now going to run a join query to collect some data from a booking dataset. Both datasets have a shared identity with a Loyalty ID where you will be able to make the join with.

From the Bookings Dataset you will be collecting if confirmed, date, number of nights, points, and nightly rate.

SELECT crm._adobeamericaspot5.memberInfo.branchCity as branchCity,
    crm._adobeamericaspot5.memberInfo.branchState as branchState,
    crm._adobeamericaspot5.memberInfo.hasRewardCard as hasRewardCard,
		b._adobeamericaspot5.bankingInfo.mortgage as mortgage, 
		b._adobeamericaspot5.bankingInfo.date as `date`, 
		b._adobeamericaspot5.bankingInfo.numOfAccts as numOfAccts, 
		b._adobeamericaspot5.bankingInfo.points as points, 
		b._adobeamericaspot5.bankingInfo.balance as balance 
FROM crm_fsi crm
JOIN banking_2022_2023 b 
ON crm._adobeamericaspot5.personDetails.loyaltyDetails.loyaltyId = b._adobeamericaspot5.bankingInfo.loyaltyId;

Next, you will need to expand the query to include a calculation for total based the rate and the number of nights the person will be staying.

SELECT crm._adobeamericaspot5.memberInfo.branchCity as branchCity,
    crm._adobeamericaspot5.memberInfo.branchState as branchState,
    crm._adobeamericaspot5.memberInfo.hasRewardCard as hasRewardCard,
		b._adobeamericaspot5.bankingInfo.mortgage as mortgage, 
		b._adobeamericaspot5.bankingInfo.date as `date`, 
		b._adobeamericaspot5.bankingInfo.numOfAccts as numOfAccts, 
		b._adobeamericaspot5.bankingInfo.points as points, 
		b._adobeamericaspot5.bankingInfo.balance as balance, 
		b._adobeamericaspot5.bankingInfo.balance * b._adobeamericaspot5.bankingInfo.numOfAccts as Total
FROM crm_fsi crm
JOIN banking_2022_2023 b 
ON crm._adobeamericaspot5.personDetails.loyaltyDetails.loyaltyId = b._adobeamericaspot5.bankingInfo.loyaltyId;

Not that we have joined the two datasets together and we have a calculated total price for the bookings, let's start to expand the query to complete a cart abandonment use case and create a new table with those rows of data.

The overall goal will be to create query that will look back 7 days from today and get all the rows that have not confirmed a booking. We will accomplish this by adding a WHERE statement in our query.

SELECT crm._adobeamericaspot5.memberInfo.branchCity as branchCity,
    crm._adobeamericaspot5.memberInfo.branchState as branchState,
    crm._adobeamericaspot5.memberInfo.hasRewardCard as hasRewardCard,
		b._adobeamericaspot5.bankingInfo.mortgage as mortgage, 
		b._adobeamericaspot5.bankingInfo.date as `date`, 
		b._adobeamericaspot5.bankingInfo.numOfAccts as numOfAccts, 
		b._adobeamericaspot5.bankingInfo.points as points, 
		b._adobeamericaspot5.bankingInfo.balance as balance, 
		b._adobeamericaspot5.bankingInfo.balance * b._adobeamericaspot5.bankingInfo.numOfAccts as Total
FROM crm_fsi crm
JOIN banking_2022_2023 b 
ON crm._adobeamericaspot5.personDetails.loyaltyDetails.loyaltyId = b._adobeamericaspot5.bankingInfo.loyaltyId
WHERE b.timestamp BETWEEN '2023-07-10' AND '2023-07-17'
ORDER BY Total DESC;

You have created a query that will show everyone that has booked regardless of confirmation status. You need to add an AND statement to the WHERE condition to add the logic for only including people that have not confirmed.

SELECT crm._adobeamericaspot5.memberInfo.branchCity as branchCity,
    crm._adobeamericaspot5.memberInfo.branchState as branchState,
    crm._adobeamericaspot5.memberInfo.hasRewardCard as hasRewardCard,
		b._adobeamericaspot5.bankingInfo.mortgage as mortgage, 
		b._adobeamericaspot5.bankingInfo.date as `date`, 
		b._adobeamericaspot5.bankingInfo.numOfAccts as numOfAccts, 
		b._adobeamericaspot5.bankingInfo.points as points, 
		b._adobeamericaspot5.bankingInfo.balance as balance, 
		b._adobeamericaspot5.bankingInfo.balance * b._adobeamericaspot5.bankingInfo.numOfAccts as Total
FROM crm_fsi crm
JOIN banking_2022_2023 b 
ON crm._adobeamericaspot5.personDetails.loyaltyDetails.loyaltyId = b._adobeamericaspot5.bankingInfo.loyaltyId
WHERE b.timestamp BETWEEN '2023-07-10' AND '2023-07-17'
AND b._adobeamericaspot5.bankingInfo.mortgage = FALSE 
ORDER BY Total DESC;

Now that you have the complete list of people that have not confirmed a booking for the last 7 days we can now use an Adobe Defined function to create a table from the select statement that you have created.

CREATE TABLE notmortgage AS 
	SELECT crm._adobeamericaspot5.memberInfo.branchCity as branchCity,
			crm._adobeamericaspot5.memberInfo.branchState as branchState,
			crm._adobeamericaspot5.memberInfo.hasRewardCard as hasRewardCard,
            crm._adobeamericaspot5.personDetails.loyaltyDetails.loyaltyLevel,
			b._adobeamericaspot5.bankingInfo.mortgage as mortgage, 
			b._adobeamericaspot5.bankingInfo.date as `date`, 
			b._adobeamericaspot5.bankingInfo.numOfAccts as numOfAccts, 
			b._adobeamericaspot5.bankingInfo.points as points, 
			b._adobeamericaspot5.bankingInfo.balance as balance, 
			b._adobeamericaspot5.bankingInfo.balance * b._adobeamericaspot5.bankingInfo.numOfAccts as Total
	FROM crm_fsi crm
	JOIN banking_2022_2023 b 
	ON crm._adobeamericaspot5.personDetails.loyaltyDetails.loyaltyId = b._adobeamericaspot5.bankingInfo.loyaltyId
	WHERE b.timestamp BETWEEN '2023-01-01' AND '2023-11-08'
	AND b._adobeamericaspot5.bankingInfo.mortgage = FALSE 
	ORDER BY Total DESC;

This process will take a few minutes to complete.

The query for this will run quickly, but there are other functions being triggered when you use CTAS from Adobe. After it runs the query, it will create a schema. Once the schema is created it will create a dataset. After the dataset it will trigger a dataflow process to insert the data into the dataset. Then the last step is it will create a one time scheduled query for you to review in the UI.

Congrats you have completed the booking abandonment use case using Data Distiller.