Business_recomendations (07) - Govarthan-Boopalan/Customer_Behaviour_Analysis GitHub Wiki

This script generates a data-driven PDF business report for ShopEasy, combining SQL insights with strategic recommendations. It features a 2-page layout with actionable insights and supporting data tables.

Table of Contents

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

Script Overview

Key Features

  • Dynamic Recommendations: 5 data-driven business strategies
  • Two-Page PDF Report:
    • Page 1: Executive summary with analysis commentary
    • Page 2: Detailed support tables
  • Automated Styling: Professional formatting with ReportLab
  • MySQL Integration: Real-time data analysis

Data Domains Analyzed

  1. Product Performance
  2. Demographic Targeting
  3. Regional Sales
  4. Marketing ROI
  5. Discount Candidates

Flowchart

Start
  │
  ├── Initialize Database Connection (SQLAlchemy)
  │
  ├── get_dynamic_recommendations()
  │   ├── Execute 5 Analytical Queries
  │   ├── Process Results with Pandas
  │   └── Generate Strategic Recommendations
  │
  ├── create_dynamic_pdf()
  │   ├── Page 1: Title & Actionable Insights
  │   │   ├── Add Styled Headings
  │   │   ├── Insert Recommendations + Analysis
  │   │   └── Add Methodology Footnote
  │   │
  │   └── Page 2: Supporting Data Tables
  │       ├── Execute 3 Additional Queries
  │       ├── Format Tables with Alternating Colors
  │       └── Add Explanatory Captions
  │
  └── Save PDF to ../results/

Code Explanation

Core Components

1. Database Connection

engine = create_engine('mysql+mysqlconnector://root:456123@localhost/ShopEasy')
  • Uses SQLAlchemy for connection pooling and safe query execution

2. PDF Styling Configuration

style_heading = ParagraphStyle(
    'Heading1',
    fontSize=16,
    textColor=colors.darkblue,
    spaceAfter=12
)
  • Defines reusable styles for:
    • Headings (dark blue)
    • Body text
    • Bulleted recommendations
    • Footnotes

Key Functions

get_dynamic_recommendations()

Generates 5 business insights through SQL queries:

  1. Product Performance
SELECT p.ProductName, 
       ROUND((SUM(purchases)*100.0/COUNT(*),2) AS conversion_rate
FROM customer_journey...
  • Identifies best-converting product using purchase-to-browse ratio
  1. Demographic Targeting
CASE WHEN Age < 30 THEN '18-29'...
  • Segments customers by age/gender/location
  • Calculates demographic-specific conversion rates

create_dynamic_pdf()

Constructs PDF layout using ReportLab's Platypus framework:

elements = [
    Paragraph("Title", style_heading),
    Spacer(1,12),
    Table(data, style=table_style),
    PageBreak()
]
  • Uses KeepTogether to prevent content fragmentation
  • Implements alternating row colors for tables:
TableStyle([
    ('BACKGROUND', (0,1), (-1,-1), colors.beige),
    ('GRID', (0,0), (-1,-1), 1, colors.black)
])

SQL Query Breakdown

Discount Candidate Logic

SELECT p.ProductName
FROM products p
LEFT JOIN top_products ON p.ProductID = top_products.ProductID
WHERE top_products.ProductID IS NULL
HAVING avg_rating < 3.5 AND review_count > 5
  1. Excludes top 3 selling products
  2. Filters for underperforming items with:
    • Low ratings (<3.5/5)
    • Significant review volume (>5)

Marketing ROI Calculation

(SUM(purchases)*100.0/SUM(Clicks) AS conversion_rate
  • Measures campaign effectiveness via click-to-purchase ratio

Usage

  1. Prerequisites
pip install pandas sqlalchemy mysql-connector-python reportlab
  1. Execution
python 07_Business_recomendations.py
  1. Output
  • PDF report at: ../results/ShopEasy_Business_Recommendations.pdf

Customization Guide

  1. Modify Queries
    Adjust LIMIT clauses or WHERE conditions in the queries dictionaries

  2. Change Styling
    Edit ParagraphStyle configurations:

style_heading.textColor = colors.red  # Change header color
  1. Add New Sections
    Extend tables list in create_dynamic_pdf() with additional queries

Improvement Suggestions

  1. Parameterization
# Convert to command-line arguments
LIMIT_TOP_PRODUCTS = 5 → config.LIMIT_TOP_PRODUCTS
  1. Enhanced Visualization
from reportlab.graphics.charts.barcharts import VerticalBarChart
# Add conversion rate comparison charts
  1. Email Automation
import smtplib
# Auto-send PDF to stakeholders
  1. Error Resilience
try:
    df = pd.read_sql(query, engine)
except SQLAlchemyError as e:
    logger.error(f"Query failed: {str(e)}")

Conclusion

This script provides a robust framework for transforming database insights into executive-ready reports. By combining SQL analysis with automated PDF generation, it bridges the gap between data teams and business decision-makers. The modular structure allows easy adaptation to new analysis requirements while maintaining professional presentation standards.