Скрипт не принимает excel файлы и работает напрямую с БД. Посчитает конверсии и статусы клиентов (новый, с колёс и т.д.), а также предоставит информацию о первых и последних покупках.
Python tool for analyzing customer conversion from leads and communications into purchases using SQL data.
This project analyzes customer conversion by combining sales data, call history and CRM leads.
The script calculates how effectively customer communications are converted into real purchases and provides metrics for evaluating sales channels.
The analysis is based on SQL database records and automatically generates a structured Excel report.
The main objective is to evaluate customer acquisition efficiency and understand which communication channels generate real sales.
The report helps analyze:
- lead-to-sale conversion
- new and returning customers
- customer acquisition channels
- sales performance by location
- SQL database connection
- Loading data from multiple sources
- Sales history analysis
- CRM leads processing
- Phone number cleaning and normalization
- Customer matching by phone numbers
- New vs returning customer classification
- Lead-to-sale conversion calculation
- Location-based performance analysis
- Excel report generation
- Telegram execution notifications
- Python
- pandas
- SQLAlchemy
- MySQL Connector
- pyTelegramBotAPI
- tqdm
- openpyxl
- Loads sales, calls and CRM data from SQL database
- Cleans and standardizes customer phone numbers
- Matches customers between different data sources
- Defines customer acquisition source:
- direct visit
- phone call
- CRM request
- Identifies new and returning customers
- Calculates conversion metrics
- Generates Excel report with detailed analytics
- Sends Telegram notification after execution
- Sales database
- Call history
- CRM leads
- Customer directory
Generated Excel report containing:
- Total customers
- New customers
- Leads count
- Unique leads
- Lead-to-sale conversion
- Conversion by business unit
- Conversion by location
This project can be used for:
- sales analytics
- CRM analytics
- marketing performance analysis
- customer acquisition optimization