Rijul_Sahu

October, 2019

Exasol is a high-performance, in-memory massively parallel processing (MPP) database specifically designed for analytics.

You can find more about exasol here, https://www.exasol.com

Description

My clients’ Digital Brand Commerce, Analytics & Operations team wanted some metrics that could provide insights on CRM, and calculate the retention rate of consumers across multiple consumer channels for different time periods (month, quarter, half-year, year, etc.), regions, countries, and brands.

This process included activities like filtering data as per business rules, aggregating data, split that data into different consumer segments, and lastly, calculate retention rate for the required selection.

Challenges

The key challenges faced in this process were:

Multiple database scripts and Excel activities for deriving one set of results.

Excessive manual intervention to change time and region filters.

Heavy files using too much space on the server.

Complex calculations making it a tedious process.

Solution

To reduce the overall time spent by the clients Operations team on manual and time-consuming activities,

I designed, built, and deployed automated Lua scripts (lightweight embeddable scripting language) in Exasol, which generates reports in a few clicks on entering few, required parameters.

The script processes data from different consumer segments, applies business rules for cleaning and aggregating, splits it into different consumer segments, finds useful metrics, and then provides the retention rates.

Benefits

The key benefits of implementing this solution are:

Reduced TAT(turn around time) from 45 minutes to 4 minutes.

Reduced manual intervention up to 98%.

Optimized process.

Reduced ongoing operations cost.