Проверка клиентской базы на новизну

Скрипт принимает на вход excel файл и определяет откуда пришёл клиент и когда (сначала по всей глубине баз за 5 лет, затем по вашим диапазонам). Добавив сводную таблицу к результатам подсчёта можно решить множество задач от определения "возраста" клиента до конверсий и сегментаций.

Customer History Validator

Python tool for checking customer history and identifying customer origin using SQL and Excel data.

Description

This project validates customer lists by comparing them with historical data from multiple business sources.

The script determines whether a customer is new or already exists in company databases and identifies the first interaction channel.

It helps analyze customer acquisition sources and validate marketing or sales databases.

Business Goal

The main objective is to understand customer history and determine where and when each customer first appeared.

The analysis helps answer questions:

• Is this a new or existing customer?
• Where did the customer first appear?
• Which channel generated the customer?

Features

• SQL database connection
• Excel file processing
• Phone number cleaning and normalization
• Customer matching across multiple databases
• First interaction detection
• Last interaction detection
• Sales history analysis
• CRM history analysis
• Call history analysis
• Automatic Excel report generation
• Telegram execution notifications

Tech Stack

• Python
• pandas
• SQLAlchemy
• PyMySQL
• pyTelegramBotAPI
• tqdm
• openpyxl
• python-dotenv

How It Works

1. Loads customer list from Excel file
2. Extracts historical data from SQL database:
   • retail sales
   • service sales
   • call history
   • CRM leads
3. Cleans and standardizes phone numbers
4. Matches customers by phone number
5. Finds first and last customer interactions
6. Defines customer source:
   • imported database
   • direct visit
   • phone call
   • CRM request
7. Adds analytical sheets to the Excel file
8. Sends Telegram notification after completion

Example / Demo

Input

Excel file containing:

• Customer phone numbers
• Start date
• End date

Output

Excel report with:

• First customer appearance date
• First customer source
• Last retail visit
• Last service visit
• Last call
• Last CRM request

Use Case

This project can be used for:

• CRM analytics
• marketing database validation
• customer lifecycle analysis
• sales attribution