Query Editor

8 min

description: Getting Started with Data Distiller

Query Editor #query-editor

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 _adobedemoamericas270.memberInfo.mostTraveledCity as favoriteCity,
    _adobedemoamericas270.memberInfo.mostTraveledState as favoriteState,
    _adobedemoamericas270.memberInfo.hasRewardCard as hasRewardCard
FROM crm_travel
WHERE _adobedemoamericas270.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._adobedemoamericas270.memberInfo.mostTraveledCity as favoriteCity,
    crm._adobedemoamericas270.memberInfo.mostTraveledState as favoriteState,
    crm._adobedemoamericas270.memberInfo.hasRewardCard as hasRewardCard,
		b._adobedemoamericas270.bookingInfo.confirmed as confirmed, 
		b._adobedemoamericas270.bookingInfo.date as `date`, 
		b._adobedemoamericas270.bookingInfo.numOfNights as numOfNights, 
		b._adobedemoamericas270.bookingInfo.points as points, 
		b._adobedemoamericas270.bookingInfo.rate as nightlyRate 
FROM crm_travel crm
JOIN bookings_2022_2023 b 
ON crm._adobedemoamericas270.personDetails.loyaltyDetails.loyaltyId = b._adobedemoamericas270.bookingInfo.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.

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._adobedemoamericas270.memberInfo.mostTraveledCity as favoriteCity,
    crm._adobedemoamericas270.memberInfo.mostTraveledState as favoriteState,
    crm._adobedemoamericas270.memberInfo.hasRewardCard as hasRewardCard,
		b._adobedemoamericas270.bookingInfo.confirmed as confirmed, 
		b._adobedemoamericas270.bookingInfo.date as `date`, 
		b._adobedemoamericas270.bookingInfo.numOfNights as numOfNights, 
		b._adobedemoamericas270.bookingInfo.points as points, 
		b._adobedemoamericas270.bookingInfo.rate as nightlyRate, 
		b._adobedemoamericas270.bookingInfo.rate * b._adobedemoamericas270.bookingInfo.numOfNights as Total
FROM crm_travel crm
JOIN bookings_2022_2023 b 
ON crm._adobedemoamericas270.personDetails.loyaltyDetails.loyaltyId = b._adobedemoamericas270.bookingInfo.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._adobedemoamericas270.memberInfo.mostTraveledCity as favoriteCity,
    crm._adobedemoamericas270.memberInfo.mostTraveledState as favoriteState,
    crm._adobedemoamericas270.memberInfo.hasRewardCard as hasRewardCard,
		b._adobedemoamericas270.bookingInfo.confirmed as confirmed, 
		b._adobedemoamericas270.bookingInfo.date as `date`, 
		b._adobedemoamericas270.bookingInfo.numOfNights as numOfNights, 
		b._adobedemoamericas270.bookingInfo.points as points, 
		b._adobedemoamericas270.bookingInfo.rate as nightlyRate, 
		b._adobedemoamericas270.bookingInfo.rate * b._adobedemoamericas270.bookingInfo.numOfNights as Total
FROM crm_travel crm
JOIN bookings_2022_2023 b 
ON crm._adobedemoamericas270.personDetails.loyaltyDetails.loyaltyId = b._adobedemoamericas270.bookingInfo.loyaltyId
WHERE b.timestamp BETWEEN '2023-07-10' AND '2023-07-17'
AND b._adobedemoamericas270.bookingInfo.confirmed = 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 notConfirmed AS 
	SELECT crm._adobedemoamericas270.memberInfo.mostTraveledCity as favoriteCity,
			crm._adobedemoamericas270.memberInfo.mostTraveledState as favoriteState,
			crm._adobedemoamericas270.memberInfo.hasRewardCard as hasRewardCard,
            crm._adobedemoamericas270.personDetails.loyaltyDetails.loyaltyStatus,
			b._adobedemoamericas270.bookingInfo.confirmed as confirmed, 
			b._adobedemoamericas270.bookingInfo.date as `date`, 
			b._adobedemoamericas270.bookingInfo.numOfNights as numOfNights, 
			b._adobedemoamericas270.bookingInfo.points as points, 
			b._adobedemoamericas270.bookingInfo.rate as nightlyRate, 
			b._adobedemoamericas270.bookingInfo.rate * b._adobedemoamericas270.bookingInfo.numOfNights as Total
	FROM crm_travel crm
	JOIN bookings_2022_2023 b 
	ON crm._adobedemoamericas270.personDetails.loyaltyDetails.loyaltyId = b._adobedemoamericas270.bookingInfo.loyaltyId
	WHERE b.timestamp BETWEEN '2023-01-01' AND '2023-07-17'
	AND b._adobedemoamericas270.bookingInfo.confirmed = 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.