Tự động hóa & Machine Learning: Tự động dự đoán doanh thu từ dữ liệu trong SQL Server bằng mô hình Python
Tự động dự đoán doanh thu từ dữ liệu trong SQL Server bằng mô hình Python
Giới thiệu
Trong thời đại số hóa hiện nay, việc dự đoán doanh thu dựa trên dữ liệu lịch sử đã trở thành một nhu cầu thiết yếu của các doanh nghiệp. Bằng cách kết hợp sức mạnh của SQL Server để lưu trữ dữ liệu và Python để xây dựng các mô hình Machine Learning, chúng ta có thể tạo ra một hệ thống dự đoán doanh thu tự động, cung cấp những thông tin giá trị cho việc ra quyết định kinh doanh.
Bài viết này sẽ hướng dẫn chi tiết cách xây dựng một hệ thống dự đoán doanh thu tự động bằng cách kết hợp SQL Server và các mô hình Machine Learning trong Python.
Tổng quan về quy trình
Quy trình dự đoán doanh thu tự động bao gồm các bước chính sau:
- Thu thập và chuẩn bị dữ liệu từ SQL Server
- Phân tích và tiền xử lý dữ liệu với Python
- Xây dựng và huấn luyện mô hình Machine Learning
- Đánh giá và tối ưu hóa mô hình
- Triển khai mô hình để dự đoán tự động
1. Thu thập và chuẩn bị dữ liệu từ SQL Server
Cấu trúc cơ sở dữ liệu
Đầu tiên, chúng ta cần có một cơ sở dữ liệu chứa thông tin về doanh thu lịch sử:
-- Tạo bảng dữ liệu doanh thu
CREATE TABLE DuLieuDoanhThu (
ID INT PRIMARY KEY IDENTITY(1,1),
NgayThang DATE NOT NULL,
SanPham NVARCHAR(100) NOT NULL,
DanhMuc NVARCHAR(50) NOT NULL,
KhuVuc NVARCHAR(50) NOT NULL,
Kenh NVARCHAR(50) NOT NULL,
SoLuong INT NOT NULL,
DonGia DECIMAL(18,2) NOT NULL,
DoanhThu DECIMAL(18,2) NOT NULL,
ChiPhiMarketing DECIMAL(18,2) NULL,
ChiPhiVanChuyen DECIMAL(18,2) NULL,
NgayTao DATETIME DEFAULT GETDATE()
);
Truy xuất dữ liệu từ SQL Server
-- Stored procedure để lấy dữ liệu huấn luyện mô hình
CREATE PROCEDURE sp_LayDuLieuHuanLuyen
@TuNgay DATE,
@DenNgay DATE
AS
BEGIN
SELECT
YEAR(NgayThang) AS Nam,
MONTH(NgayThang) AS Thang,
DAY(NgayThang) AS Ngay,
DATEPART(dw, NgayThang) AS ThuTrongTuan,
DanhMuc,
KhuVuc,
Kenh,
SUM(DoanhThu) AS TongDoanhThu,
SUM(SoLuong) AS TongSoLuong,
SUM(ChiPhiMarketing) AS TongChiPhiMarketing,
AVG(DonGia) AS GiaTrungBinh
FROM
DuLieuDoanhThu
WHERE
NgayThang BETWEEN @TuNgay AND @DenNgay
GROUP BY
YEAR(NgayThang),
MONTH(NgayThang),
DAY(NgayThang),
DATEPART(dw, NgayThang),
DanhMuc,
KhuVuc,
Kenh
ORDER BY
Nam, Thang, Ngay;
END;
2. Phân tích và tiền xử lý dữ liệu với Python
import pandas as pd
import numpy as np
import pyodbc
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.model_selection import train_test_split
# Kết nối đến SQL Server
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserver;DATABASE=yourdatabase;UID=youruser;PWD=yourpassword')
# Lấy dữ liệu từ SQL Server
query = """
EXEC sp_LayDuLieuHuanLuyen @TuNgay = '2022-01-01', @DenNgay = '2023-12-31'
"""
df = pd.read_sql(query, conn)
# Kiểm tra giá trị thiếu
print(f"Số lượng giá trị thiếu trong mỗi cột:\n{df.isnull().sum()}")
# Xử lý giá trị thiếu
df['TongChiPhiMarketing'].fillna(df['TongChiPhiMarketing'].mean(), inplace=True)
# Tạo thêm các đặc trưng (feature engineering)
df['NgayLe'] = 0 # Có thể cập nhật bằng danh sách các ngày lễ
df['TyLeChiPhiMarketing'] = df['TongChiPhiMarketing'] / df['TongDoanhThu']
df['MuaVu'] = df['Thang'].apply(lambda x: 'Xuan' if x in [1, 2, 3] else
'Ha' if x in [4, 5, 6] else
'Thu' if x in [7, 8, 9] else 'Dong')
# Mã hóa các biến phân loại (one-hot encoding)
categorical_cols = ['DanhMuc', 'KhuVuc', 'Kenh', 'MuaVu']
encoder = OneHotEncoder(sparse=False, drop='first')
encoded_features = encoder.fit_transform(df[categorical_cols])
encoded_feature_names = encoder.get_feature_names_out(categorical_cols)
# Kết hợp các đặc trưng đã mã hóa với dữ liệu gốc
encoded_df = pd.DataFrame(encoded_features, columns=encoded_feature_names)
df_final = pd.concat([df.drop(categorical_cols, axis=1).reset_index(drop=True),
encoded_df.reset_index(drop=True)], axis=1)
# Phân chia dữ liệu thành tập huấn luyện và tập kiểm tra
X = df_final.drop('TongDoanhThu', axis=1)
y = df_final['TongDoanhThu']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# Chuẩn hóa dữ liệu
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)
3. Xây dựng và huấn luyện mô hình Machine Learning
from sklearn.linear_model import LinearRegression, Ridge, Lasso
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor
from sklearn.svm import SVR
from sklearn.metrics import mean_squared_error, r2_score, mean_absolute_error
import joblib
# Huấn luyện mô hình hồi quy tuyến tính
linear_model = LinearRegression()
linear_model.fit(X_train_scaled, y_train)
# Huấn luyện mô hình Random Forest
rf_model = RandomForestRegressor(n_estimators=100, random_state=42)
rf_model.fit(X_train_scaled, y_train)
# Huấn luyện mô hình Gradient Boosting
gb_model = GradientBoostingRegressor(n_estimators=100, learning_rate=0.1, random_state=42)
gb_model.fit(X_train_scaled, y_train)
# Dictionary để lưu các mô hình
models = {
'Linear Regression': linear_model,
'Random Forest': rf_model,
'Gradient Boosting': gb_model
}
# Đánh giá các mô hình trên dữ liệu kiểm tra
for name, model in models.items():
y_pred = model.predict(X_test_scaled)
mse = mean_squared_error(y_test, y_pred)
rmse = np.sqrt(mse)
mae = mean_absolute_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)
print(f"Mô hình: {name}")
print(f" - MSE: {mse:.2f}")
print(f" - RMSE: {rmse:.2f}")
print(f" - MAE: {mae:.2f}")
print(f" - R^2: {r2:.4f}")
print("")
# Lưu mô hình tốt nhất (giả sử là Gradient Boosting)
best_model = models['Gradient Boosting']
joblib.dump(best_model, 'best_model.pkl')
joblib.dump(scaler, 'scaler.pkl')
joblib.dump(encoder, 'encoder.pkl')
4. Đánh giá và tối ưu hóa mô hình
from sklearn.model_selection import GridSearchCV
import matplotlib.pyplot as plt
# Tối ưu hóa siêu tham số của mô hình Gradient Boosting
param_grid = {
'n_estimators': [50, 100, 200],
'learning_rate': [0.01, 0.05, 0.1, 0.2],
'max_depth': [3, 5, 7],
'min_samples_split': [2, 5, 10]
}
grid_search = GridSearchCV(
estimator=GradientBoostingRegressor(random_state=42),
param_grid=param_grid,
cv=5,
scoring='neg_mean_squared_error',
n_jobs=-1
)
grid_search.fit(X_train_scaled, y_train)
print(f"Tham số tốt nhất: {grid_search.best_params_}")
print(f"MSE tốt nhất: {-grid_search.best_score_:.2f}")
# Lưu mô hình tối ưu
optimized_model = grid_search.best_estimator_
joblib.dump(optimized_model, 'optimized_model.pkl')
# Phân tích mức độ quan trọng của các đặc trưng
feature_importance = optimized_model.feature_importances_
sorted_idx = np.argsort(feature_importance)
plt.figure(figsize=(10, 8))
plt.barh(range(len(sorted_idx)), feature_importance[sorted_idx], align='center')
plt.yticks(range(len(sorted_idx)), np.array(X.columns)[sorted_idx])
plt.title('Mức độ quan trọng của các đặc trưng')
plt.savefig('feature_importance.png')
# Vẽ biểu đồ so sánh giữa giá trị thực tế và dự đoán
y_pred = optimized_model.predict(X_test_scaled)
plt.figure(figsize=(10, 6))
plt.scatter(y_test, y_pred, alpha=0.5)
plt.plot([y_test.min(), y_test.max()], [y_test.min(), y_test.max()], 'r--')
plt.xlabel('Doanh thu thực tế')
plt.ylabel('Doanh thu dự đoán')
plt.title('So sánh giữa doanh thu thực tế và dự đoán')
plt.savefig('actual_vs_predicted.png')
5. Triển khai mô hình để dự đoán tự động
Sau khi đã huấn luyện và tối ưu hóa mô hình, chúng ta cần tạo một ứng dụng để tự động dự đoán doanh thu. Dưới đây là một ví dụ về cách tạo một dịch vụ dự đoán tự động:
import pandas as pd
import joblib
import pyodbc
import numpy as np
from datetime import datetime, timedelta
import schedule
import time
def predict_revenue():
# Kết nối đến SQL Server
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserver;DATABASE=yourdatabase;UID=youruser;PWD=yourpassword')
cursor = conn.cursor()
# Lấy dữ liệu mới nhất để dự đoán
query_historical = """
EXEC sp_LayDuLieuHuanLuyen @TuNgay = DATEADD(DAY, -90, GETDATE()), @DenNgay = GETDATE()
"""
df_historical = pd.read_sql(query_historical, conn)
# Tạo dữ liệu cho dự đoán 30 ngày tiếp theo
today = datetime.now()
prediction_dates = [today + timedelta(days=i) for i in range(1, 31)]
# Tạo DataFrame cho dự đoán
prediction_data = []
for date in prediction_dates:
# Lấy dữ liệu trung bình của các ngày tương tự trong quá khứ
similar_days_query = f"""
SELECT AVG(TongDoanhThu) as AvgRevenue, AVG(TongSoLuong) as AvgQuantity,
AVG(TongChiPhiMarketing) as AvgMarketing, AVG(GiaTrungBinh) as AvgPrice
FROM DuLieuDoanhThu
WHERE DATEPART(dw, NgayThang) = {date.weekday() + 1}
AND MONTH(NgayThang) = {date.month}
"""
avg_data = pd.read_sql(similar_days_query, conn)
# Thêm vào dữ liệu dự đoán
for category in df_historical['DanhMuc'].unique():
for region in df_historical['KhuVuc'].unique():
for channel in df_historical['Kenh'].unique():
prediction_data.append({
'Nam': date.year,
'Thang': date.month,
'Ngay': date.day,
'ThuTrongTuan': date.weekday() + 1,
'DanhMuc': category,
'KhuVuc': region,
'Kenh': channel,
'TongSoLuong': avg_data['AvgQuantity'].values[0],
'TongChiPhiMarketing': avg_data['AvgMarketing'].values[0],
'GiaTrungBinh': avg_data['AvgPrice'].values[0],
'NgayLe': 0, # Cập nhật nếu là ngày lễ
'TyLeChiPhiMarketing': avg_data['AvgMarketing'].values[0] / max(1, avg_data['AvgRevenue'].values[0]),
'MuaVu': 'Xuan' if date.month in [1, 2, 3] else
'Ha' if date.month in [4, 5, 6] else
'Thu' if date.month in [7, 8, 9] else 'Dong'
})
df_prediction = pd.DataFrame(prediction_data)
# Tải mô hình và các công cụ xử lý dữ liệu
model = joblib.load('optimized_model.pkl')
scaler = joblib.load('scaler.pkl')
encoder = joblib.load('encoder.pkl')
# Xử lý dữ liệu dự đoán giống như dữ liệu huấn luyện
categorical_cols = ['DanhMuc', 'KhuVuc', 'Kenh', 'MuaVu']
encoded_features = encoder.transform(df_prediction[categorical_cols])
encoded_feature_names = encoder.get_feature_names_out(categorical_cols)
encoded_df = pd.DataFrame(encoded_features, columns=encoded_feature_names)
df_final = pd.concat([df_prediction.drop(categorical_cols, axis=1).reset_index(drop=True),
encoded_df.reset_index(drop=True)], axis=1)
# Chuẩn hóa dữ liệu
X_pred = scaler.transform(df_final)
# Dự đoán
y_pred = model.predict(X_pred)
# Gắn kết quả dự đoán vào dữ liệu
df_prediction['DoanhThuDuDoan'] = y_pred
# Lưu kết quả dự đoán vào SQL Server
for index, row in df_prediction.iterrows():
insert_query = f"""
INSERT INTO DuLieuDuDoanDoanhThu (
NgayDuDoan, NgayThang, DanhMuc, KhuVuc, Kenh, DoanhThuDuDoan
) VALUES (
GETDATE(),
'{row['Nam']}-{row['Thang']}-{row['Ngay']}',
'{row['DanhMuc']}',
'{row['KhuVuc']}',
'{row['Kenh']}',
{row['DoanhThuDuDoan']}
)
"""
cursor.execute(insert_query)
conn.commit()
conn.close()
print(f"Dự đoán doanh thu hoàn tất vào: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
# Lập lịch chạy dự đoán mỗi tuần
schedule.every().monday.at("01:00").do(predict_revenue)
# Chạy một lần để kiểm tra
predict_revenue()
# Vòng lặp chờ lịch
while True:
schedule.run_pending()
time.sleep(60)
6. Tạo báo cáo tự động với kết quả dự đoán
Để trình bày kết quả dự đoán một cách trực quan, chúng ta có thể sử dụng thư viện matplotlib và seaborn để tạo các biểu đồ và sau đó gửi báo cáo qua email:
import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import pyodbc
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
def generate_forecast_report():
# Kết nối đến SQL Server
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserver;DATABASE=yourdatabase;UID=youruser;PWD=yourpassword')
# Lấy dữ liệu dự đoán gần nhất
query = """
SELECT
NgayThang, DanhMuc, KhuVuc, Kenh, DoanhThuDuDoan
FROM
DuLieuDuDoanDoanhThu
WHERE
NgayDuDoan = (SELECT MAX(NgayDuDoan) FROM DuLieuDuDoanDoanhThu)
ORDER BY
NgayThang
"""
df = pd.read_sql(query, conn)
# Tổng hợp dữ liệu theo ngày
daily_forecast = df.groupby('NgayThang')['DoanhThuDuDoan'].sum().reset_index()
# Tổng hợp dữ liệu theo danh mục
category_forecast = df.groupby('DanhMuc')['DoanhThuDuDoan'].sum().reset_index()
# Tổng hợp dữ liệu theo khu vực
region_forecast = df.groupby('KhuVuc')['DoanhThuDuDoan'].sum().reset_index()
# Tạo biểu đồ dự báo theo thời gian
plt.figure(figsize=(12, 6))
plt.plot(daily_forecast['NgayThang'], daily_forecast['DoanhThuDuDoan'], marker='o')
plt.title('Dự báo doanh thu trong 30 ngày tới')
plt.xlabel('Ngày')
plt.ylabel('Doanh thu dự báo')
plt.grid(True)
plt.tight_layout()
plt.savefig('daily_forecast.png')
# Tạo biểu đồ dự báo theo danh mục
plt.figure(figsize=(10, 6))
sns.barplot(x='DanhMuc', y='DoanhThuDuDoan', data=category_forecast)
plt.title('Dự báo doanh thu theo danh mục sản phẩm')
plt.xlabel('Danh mục')
plt.ylabel('Tổng doanh thu dự báo')
plt.xticks(rotation=45)
plt.tight_layout()
plt.savefig('category_forecast.png')
# Tạo biểu đồ dự báo theo khu vực
plt.figure(figsize=(10, 6))
sns.barplot(x='KhuVuc', y='DoanhThuDuDoan', data=region_forecast)
plt.title('Dự báo doanh thu theo khu vực')
plt.xlabel('Khu vực')
plt.ylabel('Tổng doanh thu dự báo')
plt.tight_layout()
plt.savefig('region_forecast.png')
# Gửi báo cáo qua email
send_report_email()
print("Báo cáo dự báo đã được tạo và gửi thành công.")
def send_report_email():
# Cấu hình email
sender_email = "your_email@gmail.com"
receiver_email = "recipient@company.com"
password = "your_password"
# Tạo tin nhắn
msg = MIMEMultipart()
msg['Subject'] = f'Báo cáo dự báo doanh thu - {datetime.now().strftime("%Y-%m-%d")}'
msg['From'] = sender_email
msg['To'] = receiver_email
# Thêm nội dung
text = '''
Kính gửi Ban lãnh đạo,
Đính kèm là báo cáo dự báo doanh thu 30 ngày tới được tạo tự động bởi hệ thống ML của chúng ta.
Các điểm chính:
- Dự báo doanh thu tổng cộng trong 30 ngày tới
- Phân tích doanh thu theo danh mục sản phẩm
- Phân tích doanh thu theo khu vực
Xem file đính kèm để biết chi tiết.
Trân trọng,
Hệ thống dự báo tự động
'''
msg.attach(MIMEText(text, 'plain'))
# Đính kèm hình ảnh
for image_file in ['daily_forecast.png', 'category_forecast.png', 'region_forecast.png']:
with open(image_file, 'rb') as fp:
img = MIMEImage(fp.read())
img.add_header('Content-Disposition', 'attachment', filename=image_file)
msg.attach(img)
# Gửi email
with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
server.login(sender_email, password)
server.send_message(msg)
# Chạy báo cáo
generate_forecast_report()
Kết luận
Kết hợp SQL Server và các mô hình Machine Learning trong Python tạo nên một hệ thống dự đoán doanh thu mạnh mẽ, có khả năng tự động hóa cao. Hệ thống này không chỉ giúp doanh nghiệp dự đoán doanh số trong tương lai mà còn cung cấp những thông tin chi tiết về xu hướng doanh thu theo nhiều chiều khác nhau như danh mục sản phẩm, khu vực địa lý, kênh bán hàng.
Với việc tự động hóa toàn bộ quy trình từ thu thập dữ liệu, huấn luyện mô hình, đến tạo báo cáo, doanh nghiệp có thể tiết kiệm thời gian và nguồn lực đáng kể, đồng thời nâng cao khả năng ra quyết định dựa trên dữ liệu.
Bằng cách liên tục cải thiện và tối ưu hóa mô hình qua thời gian, doanh nghiệp sẽ có được một công cụ dự báo ngày càng chính xác, hỗ trợ đắc lực cho việc lập kế hoạch kinh doanh và chiến lược phát triển trong tương lai. 1