THE INTRODUCTION

Note: this is the exploratory analysis section of this project. I recommend reviewing the retention analysis before moving forward. Cohort retention analysis

Data Source: Pet Food Customers Order Online . This dataset was downloaded from Kaggle.

Project Objective: Through Cohort retention analysis, James discovered that only 59% of customers are retained through the 4th month, and there has been a sharp drop in new customers. We will explore audience insights using SQL in this section to help James in optimizing his marketing strategies.

THE AUDIENCE ANALYSIS BEGIN

1. Overall Stats

▶️There were a total of 49,040 orders, and these orders were placed by 11,168 unique customers.

2. Customers with Multiple Pets

▶️1,672, or 15% of customers with multiple pets.

3. Distribution of pet breed sizes?

▶️Diverse! 32% small breeds, 30% medium size, and 22% large breeds.

4. Is there a connection between pet health issues and customer buying habits?

▶️Potentially! On average, customers with allergic pets placed 5.0 orders, 19% more than those without allergies. Customers with pets having health issues placed 4.69 orders on average, 14.5% higher compared to those without health issues.

Also, 2,332 (20%) have pets with allergies, and 5,609 (50%) have pets with health issues.

Pet w/ AllergenPet w/ Health Issue

5. Avg. days between consecutive orders?

▶️25 days!

6. Active Subscription?

▶️67% of orders were made by customers with an active subscription.

7. Which food tier do customers without active subscriptions typically purchase?

▶️They prefer the Super-Premium tier! This is slightly higher compared to the customers with active subscriptions.

8. Which sign-up promotion is working?

▶️The 'Search Generic' promotion**** drove the highest orders! The second most successful was "Digital Display" followed by "Refer a Friend" promotions.

THE OVERALL TRENDS & OPPORTUNITIES

1.Customer Diversity

Since the opening of the pet store, 11,168 unique customers have placed a total of 49,040 orders through March 30, 2020. Interestingly, 15% of these customers have registered multiple pets. Additionally, the distribution of pet breed sizes is diverse (32% small breeds, 30% medium-sized breeds, and 22% large breeds).

Opportunity: Focus on targeted marketing efforts, catering to the specific needs of pet owners based on their breed preferences. For example, market a bulk product to these customers with multiple pets, highlighting a cost-effective approach to pet care.

2. Sales Trend

Monthly order counts displayed a steady increase over the course of a year. However, this growth started to decline in February 2020 after peaking in January 2020. March 2020 saw the lowest percentage of new customers, potentially due to a surge in new customer acquisition in the preceding months. In fact, the average order count in the three months leading up to March was 151% higher compared to the previous monthly average.

Opportunity: Introduce and actively promote new product lines to attract untapped customer segments.

3. Pet Health Connection

Customers with pets that have allergies or health issues tend to place more orders on average compared to those without such issues, suggesting a potential correlation between pet health and customer buying habits.

Opportunity: Prioritize promoting food products for pets with health issues over those with allergies, given the higher prevalence of health issues (50%).

4. Repeat Purchase Window

The average time between consecutive orders is 25 days.

Opportunity: Implement an automated reminder system to prompt customers to place orders after 20 days to encourage repeat purchase and customer retention.

5. Food Tier Preference

Among all orders, 58% fall into the Super-Premium tier. Interestingly, customers without active subscriptions showed an even stronger preference for the Super-Premium tier, representing over 59% of total orders, which is 2% higher compared to customers with active subscriptions.

Opportunity: Promote premium-tier food products to non-subscription customers. Offering a 10% discount on premium-tier food for customers who subscribe could incentive conversions and subscriptions.

6. Subscription Status

Out of all orders, 67% were made by customers with active subscriptions, leaving 33% without.

7. Promotion Success

The "Search Generic" promotion drove the highest number of orders, totaling 1,916, which is 23% of all orders. The second most successful promotion was "Digital Display" followed by "Refer a Friend."

Opportunity: Further invest in and optimize the "Search Generic" promotion. Additionally, analyzing the strategies and success factors from "Search Generic" promotion and applying them to other promotions.

___

Thank you so much for reading!

Your feedback matters. Feel free to drop a comment or question. 😀