Pet Supplies
PetMind is a retailer of products for pets. They are based in the United States.
PetMind sells products that are a mix of luxury items and everyday items. Luxury items include toys. Everyday items include food.
The company wants to increase sales by selling more products for some animals repeatedly.
They have been testing this approach for the last year.
They now want a report on how repeat purchases impact sales.
Data
The data is available in the table pet_supplies.
The dataset contains the sales records in the stores last year.
| Column Name | Criteria |
|---|---|
| product_id | Nominal. The unique identifier of the product. Missing values are not possible due to the database structure. |
| category | Nominal. The category of the product, one of 6 values (Housing, Food, Toys, Equipment, Medicine, Accessory). Missing values should be replaced with “Unknown”. |
| animal | Nominal. The type of animal the product is for. One of Dog, Cat, Fish, Bird. Missing values should be replaced with “Unknown”. |
| size | Ordinal. The size of animal the product is for. Small, Medium, Large. Missing values should be replaced with “Unknown”. |
| price | Continuous. The price the product is sold at. Can be any positive value, round to 2 decimal places. Missing values should be replaced with the overall median price. |
| sales | Continuous. The value of all sales of the product in the last year. This can be any positive value, rounded to 2 decimal places. Missing values should be replaced with the overall median sales. |
| rating | Discrete. Customer rating of the product from 1 to 10. Missing values should be replaced with 0. |
| repeat_purchase | Nominal. Whether customers repeatedly buy the product (1) or not (0). Missing values should be removed. |
-- Cleaning the data
SELECT product_id,
CASE WHEN category = '-' THEN 'Unknown' ELSE category END AS category,
animal,
INITCAP(size) AS size,
CASE WHEN price = 'unlisted' THEN 0 ELSE price::numeric END AS price,
sales::numeric,
CASE WHEN rating IS NULL THEN 0 ELSE rating END AS rating,
CASE WHEN repeat_purchase IS NULL THEN 0 ELSE repeat_purchase END AS repeat_purchase
FROM pet_supplies;SELECT animal, repeat_purchase,
ROUND(AVG(sales)) AS avg_sales,
ROUND(MIN(sales)) AS min_sales,
ROUND(MAX(sales)) AS max_sales
FROM pet_supplies
GROUP BY animal, repeat_purchase
ORDER BY animal, repeat_purchase DESC;Average Animal Sales
-- The management team want to focus on efforts in the next year on the most popular pets - cats and dogs - for products that are bought repeatedly.
SELECT product_id,
animal,
sales,
rating
FROM pet_supplies
WHERE animal IN ('Cat', 'Dog')
AND repeat_purchase = 1;Sales vs Rating for Cat and Dog Products
Actionable Insights
-
Cats and Dogs: Concentrate marketing and promotional efforts on high-performing products for cats and dogs that have high sales and ratings. Leverage customer feedback to enhance product features and improve ratings.
-
Bird and Fish Products: While bird and fish products show some variability, focus should be placed on identifying and promoting those with consistent repeat purchases and high sales.
-
Enhance loyalty programs and offer incentives for repeat purchases to boost sales for cat and dog products.