User Behavior Study for a Social E-Commerce Platform

User Behavior Study for a Social E-Commerce Platform

Customer to Customer model is growing fast with e-commerce platforms where sellers may be required to pay some amount and buyer can buy it without paying anything. E-Commerce website brings the seller and buyer to the same platform. Analyzing the user's database will lead to understanding the business perspective.

Objective: To analyze the ecommerce database to understand the behaviour of the users in terms of business.

1. Creating a new schema ecommerce

CREATE DATABASE ecommerce;
USE ecommerce;

2. Importing .csv file users_data into MySQL

Right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next ->

Choose options : Create a new table , select delete if exist -> Next -> Next

3. Structure of data

DESCRIBE users_data;

4. Selecting first 100 rows of the database

SELECT * FROM users_data LIMIT 100;

5. Checking for distinct values in table for field country and language

SELECT COUNT(DISTINCT(country)), COUNT(DISTINCT(language)) FROM users_data;

Ans: For country there are 200 distinct values and for language there are 5 distinct values

6. Checking if male or female users are having maximum followers

SELECT gender, SUM(socialNbFollowers) FROM users_data GROUP BY gender;

Ans: Female users have maximum followers with 254395 followers as compared to Male users with 74748 followers

7. Calculating the total users those who
a. Uses Profile Picture in their Profile
b. Uses Application for Ecommerce platform
c. Uses Android app
d. Uses ios app

SELECT 'Description', 'Total Users'
UNION SELECT 'Has Profile Picture', COUNT(hasProfilePicture) FROM users_data WHERE hasProfilePicture = 'TRUE'
UNION SELECT 'Has any App', COUNT(hasAnyApp) FROM users_data WHERE hasAnyApp = 'TRUE'
UNION SELECT 'Has Andriod App', COUNT(hasAndroidApp) FROM users_data WHERE hasAndroidApp = 'TRUE'
UNION SELECT 'Has ios App', COUNT(hasIosApp) FROM users_data WHERE hasIosApp = 'TRUE';

8. Displaying the total number of buyers for each country having bought at least 1 product in descending order

SELECT * FROM users_data;
SELECT country, COUNT(productsBought) AS Buyers FROM users_data WHERE productsBought > 0
GROUP BY country ORDER BY Buyers DESC;

9. Displaying the total number of sellers for each country having sold at least 1 product in ascending order

SELECT country, COUNT(productsSold) AS Sellers FROM users_data WHERE productsSold > 0
GROUP BY country ORDER BY Sellers ASC;

10. Displaying the names of top 10 countries having maximum products pass rate

SELECT country, COUNT(productsPassRate) AS PassR FROM users_data WHERE productsPassRate > 0
GROUP BY country ORDER BY PassR DESC LIMIT 10;

11. Calculating the number of users on an ecommerce platform for different language choices.

SELECT language,COUNT(DISTINCT identifierHash) FROM users_data GROUP BY language;

12. Checking if female users choose to putting the product in a wishlist or to like socially on an ecommerce platform.

SELECT 'Description', 'No. of units' UNION
SELECT 'Products Social Liked', SUM(socialProductsLiked) FROM users_data WHERE gender = 'F' UNION
SELECT 'Products wishlisted', SUM(productsWished) wishlisted FROM users_data WHERE gender = 'F';

Ans: Female users prefer to like a product socially on an ecommerce platform rather than to put the product in a wishlist

13. Checking if male users choose being sellers or buyers

SELECT 'Description', 'No. of people' UNION
SELECT 'Sellers(Male)', COUNT(productsListed) FROM users_data WHERE gender = 'M' AND productsListed > 0 UNION
SELECT 'Buyers(Male)', COUNT(productsBought) FROM users_data WHERE gender = 'M' AND productsBought > 0;

Ans: Male users prefer to be buyers than sellers

14. Checking which country is having maximum number of buyers

SELECT country, COUNT(productsBought) AS Buyers FROM users_data WHERE productsBought > 0
GROUP BY country ORDER BY Buyers DESC LIMIT 1;

Ans: France is having maximum number of buyers

15. Listing the name of 10 countries having zero number of sellers

SELECT country, SUM(productsListed) AS Zerolist FROM users_data
GROUP BY country ORDER BY Zerolist LIMIT 10;

16. Displaying the record of top 110 users who have used ecommerce platform recently

SELECT identifierHash, daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;

17. Calculating the number of female users those who have not logged in since last 100 days

SELECT COUNT(identifierHash) AS Femaleusers FROM users_data WHERE gender = 'F' AND daysSinceLastLogin > 100;

Ans: 68217 Female users have not logged in since last 100 days.

18. Displaying the number of female users of each country at ecommerce platform

SELECT country, COUNT(identifierHash) AS Femaleusers FROM users_data WHERE gender = 'F' GROUP BY country;

19. Displaying the number of male users of each country at ecommerce platform

SELECT country, COUNT(identifierHash) AS Maleusers FROM users_data WHERE gender = 'M' GROUP BY country;

20. Calculating the average number of products sold and bought on ecommerce platform by male users for each country

SELECT country, AVG(productsSold), AVG(productsBought) FROM users_data WHERE gender = 'M' GROUP BY country;