Case Study - 99.75% faster dashboard in two weeks

Overview

The client is a domestic cosmetics company that is truly customer-oriented. They develop all their products based on surveys and interviews with thousands of people. Instead of focusing on attracting one-time customers through marketing, they use customer retention rates as a key metric and are steadily growing.

To focus on customer needs, the client has been conducting customer analytics from a very early stage. One of their critical tasks has been cohort analysis.

Performing a monthly, product-specific, and channel-specific cohort analysis is a complicated task. The client had to manually execute SQL commands as there was no automation script. They then had to download the results to Excel and create pivot tables. This process accumulated multiple Excel files every month, consuming a lot of time.

In the client's case, essential attributes for cohort analysis, like product_id, channel_id, customer_id, and purchase_date, were spread across multiple tables. Therefore, they had to use the join statement twice in their queries, which took around 70 seconds to execute.

Before starting the project, the Da Vinci team fine-tuned the client's existing SQL queries, primarily through simple optimizations like setting up indexes and selecting only necessary columns. This optimization reduced query time by 83%, bringing it down to around 10 seconds.

We then created tables specifically for cohort analysis, such as cohort_by_channel, cohort_by_product, and cohort_by_product_channel, among others. This further reduced the query time by 99.75% to just 0.175 seconds. We also made it possible to run the queries directly in Tableau, eliminating the need for manual Excel work.

However, the task wasn't complete. These new tables required daily, weekly, or monthly updates. While one approach could be to modify the client's server code to accumulate purchase data in the new tables, we chose not to do this, in line with a consultancy approach.

The Da Vinci team focused on meeting the client's business needs without writing additional server code. We set up a separate server for cron jobs and executed shell scripts to perform large-scale data uploads at fixed intervals.

In less than a month, the Da Vinci team resolved a longstanding issue for the client, who likely felt a dramatic improvement due to the lack of an internal development team. The successful outcome was gratifying for us, and we were delighted to deliver a solution that made the client say, "Wow!"

Until next time,

Dongeun

What we did