Introduction

This data is about a subscription based digital product offering. The offering has annual subscription and monthly subscription. The product also provides daytime support for customers to reach out to a care team that can help with any product related questions and signup/cancellation related queries.

Task

To provide actionable insights, I focused on addressing the following key business challenges:

1. Identifying the product subscription with the highest customer base
2. Analyzing the revenue generated from each product subscription
3. Predict daily call volumes that will help to optimize staffing decisions
4. Distinguish between active and non-active members to enhance customer engagement

Action

To analyze the data effectively, I had to work with three distinct tables: 'customer info' , 'customer cases' and 'product info'.

One of the initial challenges I faced was that the column representing product status ('monthly' or 'annual' subscription) had different names in the customer info and product info tables. I used SQL's ALTER and RENAME commands to ensure both columns had matching names. I then proceeded to use JOIN operation to merge the customer info and product info tables to a single dataset.

To simplify further analysis I created a CTE named 'subs'. Within this CTE, I was able to determine the number of customer per subscription status effectively categorizing them as active or non-active members. The SQL query below illustrates this process:

Now working with the active members, I proceeded to calculate the number of customers per membership status and the revenue generated per year. Still using the joined data set, I constructed a CTE 'price info'. An aspect to note was that the price of the monthly subscription was provided on a monthly basis. To calculate the annual revenue accurately, I multiplied the monthly subscription by 12 to represent the annual rate. The SQL query below illustrates this process:

To assist the business in making informed staffing decisions, a critical aspect was predicting the call volumes. I used the customer cases table. My first step was to ensure the date data was in the correct data type as shown below:I then created a CTE named 'cases per day'. I used AVG (Average) operation to calculate the average number of cases encountered per day which would predict call volumes. The SQL query illustrates this process:

Finally, to gain insights into customer behavior and to aid in staff allocating decisions, I conducted analysis to identify the most frequently used communication channel by customers for raising cases, as well as the common reason behind these cases. This will ensure adequate staffing levels to address customer inquiries and concern. I employed SQL's COUNT operation to perform this analysis. The query below provides a clear illustration:

To summarize, the analysis conducted in this project holds the potential to benefit organizations in their customer care departments. By providing insights into customer behavior, subscription pattern, call volumes and communication channel preference, this analysis will help in making informed decisions about staffing, resource allocation and customer service enhancements.