Сегментация клиентской базы

Скрипт для сегментации клиентской базы на множество категорий в зависимости от сумм, периодичности покупок и множества личных показателей (более 100 параметров)

Customer Segmentation Analysis

Python tool for customer lifecycle segmentation and churn analysis using SQL sales history.

Description

This project analyzes customer activity based on historical purchase data and automatically segments customers by lifecycle stage.

The script processes sales history, identifies customer behavior patterns and creates segmentation reports for CRM and marketing analytics.

Important: This project requires access to a configured SQL database with customer transaction history. It cannot be executed without replacing database integration with your own data source.

Business Goal

The main objective is to understand customer lifecycle and improve retention strategy.

The analysis helps answer:

• How many customers are active?
• Which customers are new?
• Which customers returned after inactivity?
• How many customers are at risk of churn?
• Which customers are already lost?

Customer Segments

The model classifies customers into:

Active customers

• New customers
• Regular customers
• Returned customers

Churn group

• Sleeping customers
• Lost customers

Features

• SQL database connection
• Sales history extraction
• Customer phone normalization
• Retail customer segmentation
• Service customer segmentation
• Combined company-wide segmentation
• Customer lifecycle classification
• Churn detection
• Excel report generation
• SQL database update
• Telegram execution notifications
• Parallel execution with threading

Tech Stack

• Python
• pandas
• SQLAlchemy
• PyMySQL
• mysql-connector-python
• numpy
• tqdm
• pyTelegramBotAPI
• openpyxl
• python-dotenv

How It Works

1. Loads customer transaction history from SQL:

   • retail sales
   • service sales

2. Cleans and standardizes customer phone numbers

3. Defines activity periods:

   • last 6 months
   • previous 6 months
   • older history

4. Classifies customers by behavior

5. Generates customer segments:

   • new
   • regular
   • returned
   • sleeping
   • lost

6. Creates Excel reports

7. Updates SQL segmentation tables

Input

SQL database containing:

• Customer identifiers
• Purchase dates
• Sales channels

Output

Excel reports:

• Retail customer segments
• Service customer segments
• Full company segmentation
• Segment summary statistics

SQL tables:

• segments_parts
• segments_sto
• segments_company

Use Case

This project can be used for:

• CRM analytics
• customer retention analysis
• churn prevention
• loyalty strategy
• marketing segmentation