This is the code and presentation from the second part of a case I did for a summer internship at ARC consulting. See instructions below.
I got an offer but declined as I accepted an offer for another summer internship. I really enjoyed doing this case however.
Here is a link to the first part: https://github.com/lisaelsi/internship-case
The instructions that were given:
Final Case Instructions
You’ve received a corrected csv file with data containing a set of fictive Monthly Recurring Revenue (MRR) movements for a subscription service. Your client has identified additional data resources which lead to the following changes in the dataset: • Located "Gender" data on customers that were previously labeled as "Other" •Located "Referral_Type" data on customer that previously had missing data •Removed mislabled transaction types from the dataset_
Please spend 2-3 hours completing an analysis and a presentation of the results to a client. Your client asks that you provide them with insights into the customer base and customer behaviors that can be used to increase revenue. You should use Python, SQL, or R to explore the data.
Once you’re done, we want you to send the underlying query or code for your analysis as well as a visual presentation (pdf. ppt or similar) of your results, with the insights/conclusion that you have drawn. In the next interview, you will present your findingsv(15-20 minutes) and we will thereafter have an open discussion about the case. Note that there is not one correct answer - there are many possible analyses that can be performed and many ways to display the data. You decide what you think is most important. You should present to us as if it was a presentation to management.
Please spend no more than between 2 to 3 hours on this, and we would like to have your underlying query or code and presentation by Sunday April 2nd 11:59 CEST.
Description about the data set:
Monthly subscription fees and/or subscription changes are always billed on the first of the month.
This dataset only contains transactional data related to plan start, plan change, and plan churn events
Transaction Types initial - Only the MRR at the moment a lead converts into a paid customer (for the first time) is counted.
Upgrade - Any increase in the MRR of an existing customer, e.g. an increase in quantity, upgrade to a higher plan.
Reduction - Any decrease in MRR. e.g. downgrade to a lower plan, or a discount being added
Churn - The MRR at the time a customer cancels (or fails to renew) their subscription