Product Analysis - Social Media Case Study (Looker)

Introduction

In this case study, I was provided with a .csv file that consisted of consumer behavior data from a social media company over a 3-month period in 2021. The product that this company offers is a mobile app where users can engage with various influencers and purchase promoted products. More specifically, the data included the following fields:

• period – calendar month for which the data is reported

• influencer_id – unique identifier for an influencer

• retailer_id – unique identifier for a retailer

• user_id – unique identifier for a consumer

• orders – number of orders placed

• gmv – the value of the orders placed (in USD)

• gmc – the commission paid by the retailer for the orders (in USD)

• clicks – the number of clicks

• influencer_class – the yearly cohort an influencer belongs to

• user_cohort – the sign-up date for a consumer

My task was to provide the leadership of this company with consumer trends and insights pertaining to engagement, retention, and conversion. As a challenge, I decided to complete this study using Looker Studio, which provided a great opportunity to learn and use a new tool!

Analysis & Insights

I created a Looker dashboard consisting of 3 pages - Consumer Engagement, User Growth & Retention, and Orders & Conversion.

For consumer engagement, I discovered that both Monthly Active Users (MAU) and Engagement (Clicks) decreased in the 3-month period from March to May 2021 - MAU decreased by 14% and Engagement decreased by 21%. With such a small window of time, it is difficult to conclude the severity of this downward trend and if it continued in the following months. I would recommend continued monitoring and increasing the sample size to get a better understanding of the historical context and trend.

In addition to consumer engagement, I also looked into the most engaging influencers and retailers. I found that 3 influencers account for over 57% of clicks, and that 3 retailers generate 51% of clicks. This is critical information for a product team to understand - from here, a product team could examine those specific influencers/retailers to get a better sense of why their content drives so much engagement.

When looking at user growth and retention, I found that these metrics also experienced a decline within the 3-month period. New users dropped by 55%, and steadily declined with the exception of a sharp rise in new users on April 9th. App stickiness, or the measurement of how many users returned to using the app, experienced a decrease of 7.76%. Finally, I discovered that only 31.7% of new users in March were still engaged with the app in April.

While a decrease in new users and user retention is never a desired outcome, with only 3 months of data I cannot sufficiently say whether this trend self-corrected or user growth continued to decline. Additionally, a conclusion cannot be drawn without understanding the historical trends of the business. There is a possibility that the company experienced a large surge in new users in March, and the decrease seen here is a correction to the baseline, or that growth always decreases as the season transitions from winter to spring. Further analysis would need to be done to truly understand the implications of the trend seen in this study.

The company registered 36,875 number of orders and $3.22M in revenue during this 3-month period. The number of orders and revenue both experienced some decline, however, with orders decreasing by 24% and revenue by 25% from March to April. Although these metrics decreased, the good news is that the order conversion rate stayed consistent around 3.7%. This means that whatever external factors were causing engagement to drop during these months, the likelihood of a user ultimately purchasing something once they do engage is steady. Finally, I discovered that out of the total user base, 7% of users made at least 1 purchase, while 0.9% of total users made multiple purchases. It would be very helpful for the product team to do an additional study and try to better understand the customer demographics and/or behaviors of that group to discover what type of user is more likely to make multiple purchases.