Unleashing the Power of Query Service in Adobe Experience Platform for Complex Data Calculations

[#1]

In the ever-evolving landscape of customer experience management, businesses often face the challenge of performing intricate calculations on event data to extract meaningful insights. One such scenario our customer wants was determining the average booking price of a customer over the last 10 years. While the concept of calculating an average may seem straightforward, our clients present us with a unique and challenging interpretation.

Challenges:

1. Unique Average Calculation:
   • Calculating the average involves more than just dividing the sum of prices by the total number of bookings. Instead, it requires dividing the sum of prices by the number of unique years in which bookings were made. For instance, if a customer made 3 bookings in 2021 and 7 in 2022, the average should be calculated by dividing the sum of prices by 2 (the number of unique years).
2. Data Deduplication:
   • The same booking ID can be updated multiple times. For example, a customer might initially book a service for $1000 and later decide to upgrade it by calling the customer service, increasing the price to $1500.

In a cursory glance, leveraging the computed attributes feature in Adobe Experience Platform (AEP) might seem like a viable solution. However, this feature falls short when it comes to handling complex calculations, limiting its functionality.

How Can We Achieve This?

There are various approaches to tackle this challenge:

1. Performing Calculations Outside AEP:
   • Conduct the average calculation in the source system and then ingest the processed data into AEP.
2. Middleware Integration:
   • Implement a middleware solution to perform complex calculations outside AEP and subsequently feed the processed data into the platform.
3. Leveraging Scheduled Queries in AEP:
   • Utilize AEP's query service for executing complex calculations within the platform itself.

While the first two options are preferred when external handling is feasible, there are situations where external processing is not an option. In such cases, leveraging AEP's query service becomes imperative.

Unveiling the Power of Query Service:

Handling Data Deduplication:

To address the challenge of identifying the correct value for a booking with multiple updates, we employ the deduplication concept provided by the query service. The deduplication concept allows us to deduplicate data based on a certain attribute and order it as needed.

SELECT *, ROW_NUMBER() OVER (PARTITION BY _customer.booking.bookingid ORDER BY timestamp DESC) AS id_dup_num FROM customer_booking_dataset

This query retrieves all data from the booking table along with a row number ordered by timestamp. Using a WHERE clause (id_dup_num = 1), we can easily select the latest entry.

Identifying Data from the Last 10 Years:

Once we have the latest data, we filter out entries from the last 10 years using the following WHERE clause:

EXTRACT(year FROM _customer.booking.tripdate) >= YEAR(CURRENT_DATE) - 10

Calculating Unique Years:

Finally, we employ the COUNT and DISTINCT functions to determine the number of unique years when the booking data was recorded.

This comprehensive query allows us to handle both challenges seamlessly within the AEP query service, providing a powerful tool for those comfortable with writing queries.

In conclusion, as businesses navigate the complexities of customer data calculations, the AEP query service emerges as a robust solution, offering the flexibility to perform intricate computations within the platform itself. By understanding and harnessing the capabilities of query service, businesses can elevate their data analytics and deliver more refined insights into customer behavior.

References

https://experienceleague.adobe.com/docs/experience-platform/query/home.html?lang=en

https://experienceleague.adobe.com/docs/experience-platform/query/key-concepts/deduplication.html?la... 

[#2]

This is great insight @arpan-garg. I use these functions on a regular basis when querying data.