Cx_experience_Analysis_recomendations (06) - Govarthan-Boopalan/Customer_Behaviour_Analysis GitHub Wiki

This script analyzes customer experience data from a MySQL database to generate actionable recommendations. It identifies low-rated products, analyzes customer sentiment, tracks low-repeat buyers, and aggregates common complaints.

Table of Contents

  1. Script Overview
  2. Flowchart
  3. Code Explanation
  4. Usage
  5. Improvement Suggestions

Script Overview

Purpose

  • Connects to a MySQL database to fetch customer experience data.
  • Uses TextBlob for sentiment analysis on negative reviews.
  • Generates recommendations for product improvement and customer retention.
  • Saves results to a text file.

Key Features

  • Sentiment Analysis: Quantifies dissatisfaction in negative reviews.
  • Low Retention Detection: Identifies one-time purchasers.
  • Complaint Aggregation: Highlights recurring issues.
  • Automated Reporting: Outputs structured recommendations.

Flowchart

Start
  │
  ├── Connect to MySQL Database
  │
  ├── Execute 4 Predefined SQL Queries
  │   ├── 1. Fetch Negative Reviews
  │   ├── 2. Fetch Lowest-Rated Products
  │   ├── 3. Identify One-Time Buyers
  │   └── 4. Aggregate Common Complaints
  │
  ├── Analyze Data
  │   ├── Calculate Review Sentiment (TextBlob)
  │   ├── List Low-Rated Products
  │   ├── Count Low-Repeat Customers
  │   └── Categorize Frequent Complaints
  │
  ├── Generate Recommendations
  │
  ├── Save to ../results/customer_experience_recommendations.txt
  │
  └── End

Code Explanation

Imports

import mysql.connector  # MySQL database interaction
import pandas as pd     # Data manipulation (not directly used but implied for future extensions)
from textblob import TextBlob  # Sentiment analysis
import os               # File path operations

Database Connection

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="456123",
    database="ShopEasy"
)
cursor = conn.cursor()
  • Uses mysql.connector to establish a connection.
  • cursor executes SQL queries and fetches results.

SQL Queries

Stored in a dictionary for modularity:

queries = {
    "negative_reviews": "SELECT ... WHERE cr.Rating <= 2;",
    "lowest_rated_products": "SELECT ... ORDER BY AvgRating ASC LIMIT 5;",
    "low_repeat_customers": "SELECT ... HAVING PurchaseCount = 1;",
    "common_complaints": "SELECT ... ORDER BY Frequency DESC LIMIT 10;"
}
  1. Negative Reviews: Fetches reviews with ratings ≤ 2.
  2. Lowest-Rated Products: Returns 5 worst-performing products by average rating.
  3. Low-Repeat Customers: Identifies customers with only one purchase.
  4. Common Complaints: Groups frequently mentioned complaints.

Analysis Sections

1️⃣ Sentiment Analysis on Negative Reviews

cursor.execute(queries["negative_reviews"])
reviews = cursor.fetchall()
review_sentiments = [TextBlob(row[3]).sentiment.polarity for row in reviews]
  • TextBlob: Analyzes ReviewText (4th column) to compute polarity scores.
  • Polarity Range: -1 (negative) to 1 (positive).
  • Threshold: Triggers a recommendation if average polarity < -0.2.

2️⃣ Identify Lowest-Rated Products

for product in lowest_products:
    recommendations.append(f"- {product[1]} (Product ID: {product[0]}): Avg Rating {round(product[2],2)}")
  • Lists product names, IDs, and rounded average ratings.

3️⃣ Low-Repeat Customer Alert

recommendations.append(f"{len(low_repeat_customers)} customers made only one purchase...")
  • Suggests loyalty programs for customers with single purchases.

4️⃣ Common Complaints

for complaint in common_complaints:
    recommendations.append(f"- {complaint[0]} (Product: {complaint[1]} | Mentioned {complaint[2]} times)")
  • Formats complaints with frequencies and associated products.

Usage

  1. Prerequisites: 
    pip install mysql-connector-python textblob pandas
python -m textblob.download_corpora  # Download TextBlob NLP data
  2. Run the Script: 
    python 06_Cx_experience_Analysis_recomendations.py
  3. Output:
    • Recommendations saved to ../results/customer_experience_recommendations.txt.

Improvement Suggestions

  1. Error Handling:
    • Add try...except blocks for database connection failures.
    • Handle empty query results gracefully.
  2. Parameterization:
    • Use environment variables for database credentials.
    • Make sentiment threshold (-0.2) configurable.
  3. Enhanced Sentiment Analysis:
    • Add subjectivity analysis (TextBlob.sentiment.subjectivity).
    • Categorize complaints into themes (e.g., "shipping", "quality").
  4. Email Integration:
    • Automatically email recommendations to stakeholders.
  5. Dynamic Filters:
    • Allow users to adjust query limits (e.g., LIMIT 5 → LIMIT 10 via CLI arguments).

Conclusion

This script provides a systematic way to derive customer experience insights from raw SQL data. By combining sentiment analysis with business logic, it transforms database records into actionable strategies. The modular query design allows easy adaptation to new use cases, such as adding seasonal metrics or expanding product analysis.