504 lines
26 KiB
Python
504 lines
26 KiB
Python
"""
|
||
Бизнес аналитика
|
||
"""
|
||
import logging
|
||
from typing import Dict
|
||
|
||
from ..base import OracleDatabase
|
||
|
||
|
||
class BusinessAnalytics(OracleDatabase):
|
||
"""Класс для бизнес аналитики"""
|
||
|
||
async def get_biz_trends_stats(self) -> Dict:
|
||
"""Анализ трендов бизнеса"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Тренд роста пользователей
|
||
user_growth_query = """
|
||
SELECT
|
||
TO_CHAR(created_at, 'YYYY-MM') as month,
|
||
COUNT(*) as new_users,
|
||
COUNT(CASE WHEN is_premium = 1 THEN 1 END) as premium_users
|
||
FROM bot_users
|
||
WHERE created_at >= ADD_MONTHS(SYSDATE, -12)
|
||
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
|
||
ORDER BY TO_CHAR(created_at, 'YYYY-MM')
|
||
"""
|
||
cur.execute(user_growth_query)
|
||
user_growth = cur.fetchall()
|
||
|
||
# Тренд выручки
|
||
revenue_trend_query = """
|
||
SELECT
|
||
TO_CHAR(created_date, 'YYYY-MM') as month,
|
||
COUNT(*) as transactions,
|
||
SUM(payment_amount) as revenue,
|
||
COUNT(DISTINCT user_id) as paying_users
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= ADD_MONTHS(SYSDATE, -12)
|
||
GROUP BY TO_CHAR(created_date, 'YYYY-MM')
|
||
ORDER BY TO_CHAR(created_date, 'YYYY-MM')
|
||
"""
|
||
cur.execute(revenue_trend_query)
|
||
revenue_trend = cur.fetchall()
|
||
|
||
# Тренд использования услуг
|
||
services_trend_query = """
|
||
SELECT
|
||
service_type,
|
||
TO_CHAR(created_date, 'YYYY-MM') as month,
|
||
COUNT(*) as usage_count
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= ADD_MONTHS(SYSDATE, -6)
|
||
GROUP BY service_type, TO_CHAR(created_date, 'YYYY-MM')
|
||
ORDER BY TO_CHAR(created_date, 'YYYY-MM'), service_type
|
||
"""
|
||
cur.execute(services_trend_query)
|
||
services_trend = cur.fetchall()
|
||
|
||
# Тренд конверсии
|
||
conversion_trend_query = """
|
||
SELECT
|
||
TO_CHAR(bu.created_at, 'YYYY-MM') as month,
|
||
COUNT(DISTINCT bu.id) as total_users,
|
||
COUNT(DISTINCT pl.user_id) as converted_users
|
||
FROM bot_users bu
|
||
LEFT JOIN payment_logs pl ON bu.id = pl.user_id AND pl.payment_status = 'completed'
|
||
WHERE bu.created_at >= ADD_MONTHS(SYSDATE, -12)
|
||
GROUP BY TO_CHAR(bu.created_at, 'YYYY-MM')
|
||
ORDER BY TO_CHAR(bu.created_at, 'YYYY-MM')
|
||
"""
|
||
cur.execute(conversion_trend_query)
|
||
conversion_trend = cur.fetchall()
|
||
|
||
return {
|
||
'user_growth_trend': user_growth or [],
|
||
'revenue_trend': revenue_trend or [],
|
||
'services_trend': services_trend or [],
|
||
'conversion_trend': conversion_trend or []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_biz_trends_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
'user_growth_trend': [],
|
||
'revenue_trend': [],
|
||
'services_trend': [],
|
||
'conversion_trend': []
|
||
}
|
||
|
||
return await self._execute_query(_get_stats)
|
||
|
||
async def get_biz_forecasts_stats(self) -> Dict:
|
||
"""Прогнозы развития бизнеса"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Прогноз на основе последних 3 месяцев
|
||
recent_growth_query = """
|
||
SELECT
|
||
TO_CHAR(created_at, 'YYYY-MM') as month,
|
||
COUNT(*) as new_users,
|
||
SUM(total_payments) as revenue
|
||
FROM bot_users
|
||
WHERE created_at >= ADD_MONTHS(SYSDATE, -3)
|
||
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
|
||
ORDER BY TO_CHAR(created_at, 'YYYY-MM') DESC
|
||
"""
|
||
cur.execute(recent_growth_query)
|
||
recent_growth = cur.fetchall()
|
||
|
||
# Сезонность по дням недели
|
||
seasonality_query = """
|
||
SELECT
|
||
TO_CHAR(created_date, 'D') as day_of_week,
|
||
COUNT(*) as transactions,
|
||
AVG(payment_amount) as avg_amount
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= SYSDATE - 30
|
||
GROUP BY TO_CHAR(created_date, 'D')
|
||
ORDER BY TO_CHAR(created_date, 'D')
|
||
"""
|
||
cur.execute(seasonality_query)
|
||
seasonality = cur.fetchall()
|
||
|
||
# Потенциал роста по регионам
|
||
regional_potential_query = """
|
||
SELECT
|
||
language_code,
|
||
COUNT(*) as user_count,
|
||
COUNT(CASE WHEN total_payments > 0 THEN 1 END) as paying_users,
|
||
AVG(total_payments) as avg_revenue
|
||
FROM bot_users
|
||
WHERE is_active = 1
|
||
GROUP BY language_code
|
||
ORDER BY COUNT(*) DESC
|
||
"""
|
||
cur.execute(regional_potential_query)
|
||
regional_potential = cur.fetchall()
|
||
|
||
return {
|
||
'recent_growth': recent_growth or [],
|
||
'seasonality': seasonality or [],
|
||
'regional_potential': regional_potential or []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_biz_forecasts_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
'recent_growth': [],
|
||
'seasonality': [],
|
||
'regional_potential': []
|
||
}
|
||
|
||
return await self._execute_query(_get_stats)
|
||
|
||
async def get_biz_regions_stats(self) -> Dict:
|
||
"""Анализ регионов роста"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Топ регионы по росту
|
||
regions_growth_query = """
|
||
SELECT
|
||
language_code,
|
||
COUNT(CASE WHEN created_at >= SYSDATE - 30 THEN 1 END) as new_month,
|
||
COUNT(CASE WHEN created_at >= SYSDATE - 60 AND created_at < SYSDATE - 30 THEN 1 END) as prev_month,
|
||
COUNT(*) as total_users,
|
||
AVG(total_payments) as avg_revenue,
|
||
COUNT(CASE WHEN total_payments > 0 THEN 1 END) as paying_users
|
||
FROM bot_users
|
||
GROUP BY language_code
|
||
HAVING COUNT(*) >= 5
|
||
ORDER BY COUNT(CASE WHEN created_at >= SYSDATE - 30 THEN 1 END) DESC
|
||
"""
|
||
cur.execute(regions_growth_query)
|
||
regions_growth = cur.fetchall()
|
||
|
||
# Конверсия по регионам
|
||
regional_conversion_query = """
|
||
SELECT
|
||
bu.language_code,
|
||
COUNT(DISTINCT bu.id) as total_users,
|
||
COUNT(DISTINCT pl.user_id) as paying_users,
|
||
COUNT(pl.log_id) as total_transactions,
|
||
SUM(pl.payment_amount) as total_revenue
|
||
FROM bot_users bu
|
||
LEFT JOIN payment_logs pl ON bu.id = pl.user_id AND pl.payment_status = 'completed'
|
||
WHERE bu.is_active = 1
|
||
GROUP BY bu.language_code
|
||
HAVING COUNT(DISTINCT bu.id) >= 3
|
||
ORDER BY COUNT(DISTINCT pl.user_id) DESC
|
||
"""
|
||
cur.execute(regional_conversion_query)
|
||
regional_conversion = cur.fetchall()
|
||
|
||
# Premium распределение по регионам
|
||
premium_distribution_query = """
|
||
SELECT
|
||
language_code,
|
||
COUNT(*) as total_users,
|
||
COUNT(CASE WHEN is_premium = 1 THEN 1 END) as premium_users,
|
||
AVG(CASE WHEN is_premium = 1 THEN total_payments END) as premium_avg_revenue,
|
||
AVG(CASE WHEN is_premium = 0 THEN total_payments END) as regular_avg_revenue
|
||
FROM bot_users
|
||
WHERE is_active = 1
|
||
GROUP BY language_code
|
||
HAVING COUNT(*) >= 5
|
||
ORDER BY COUNT(CASE WHEN is_premium = 1 THEN 1 END) DESC
|
||
"""
|
||
cur.execute(premium_distribution_query)
|
||
premium_distribution = cur.fetchall()
|
||
|
||
return {
|
||
'regions_growth': regions_growth or [],
|
||
'regional_conversion': regional_conversion or [],
|
||
'premium_distribution': premium_distribution or []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_biz_regions_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
'regions_growth': [],
|
||
'regional_conversion': [],
|
||
'premium_distribution': []
|
||
}
|
||
|
||
return await self._execute_query(_get_stats)
|
||
|
||
async def get_biz_monetization_stats(self) -> Dict:
|
||
"""Анализ монетизации"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Воронка монетизации
|
||
monetization_funnel_query = """
|
||
SELECT
|
||
COUNT(*) as total_users,
|
||
COUNT(CASE WHEN interaction_count > 1 THEN 1 END) as engaged_users,
|
||
COUNT(CASE WHEN successful_payments_count > 0 THEN 1 END) as paying_users,
|
||
COUNT(CASE WHEN successful_payments_count > 1 THEN 1 END) as repeat_buyers,
|
||
COUNT(CASE WHEN total_payments > 50 THEN 1 END) as high_value_users
|
||
FROM bot_users
|
||
WHERE is_active = 1
|
||
"""
|
||
cur.execute(monetization_funnel_query)
|
||
funnel_result = cur.fetchone()
|
||
|
||
# Анализ LTV (пожизненная ценность)
|
||
ltv_analysis_query = """
|
||
SELECT
|
||
CASE
|
||
WHEN total_payments = 0 THEN 'No Payment'
|
||
WHEN total_payments <= 10 THEN 'Low Value (≤10)'
|
||
WHEN total_payments <= 50 THEN 'Medium Value (11-50)'
|
||
WHEN total_payments <= 100 THEN 'High Value (51-100)'
|
||
ELSE 'Premium Value (>100)'
|
||
END as user_segment,
|
||
COUNT(*) as user_count,
|
||
AVG(total_payments) as avg_ltv,
|
||
SUM(total_payments) as total_revenue,
|
||
AVG(successful_payments_count) as avg_transactions
|
||
FROM bot_users
|
||
WHERE is_active = 1
|
||
GROUP BY CASE
|
||
WHEN total_payments = 0 THEN 'No Payment'
|
||
WHEN total_payments <= 10 THEN 'Low Value (≤10)'
|
||
WHEN total_payments <= 50 THEN 'Medium Value (11-50)'
|
||
WHEN total_payments <= 100 THEN 'High Value (51-100)'
|
||
ELSE 'Premium Value (>100)'
|
||
END
|
||
ORDER BY AVG(total_payments) DESC
|
||
"""
|
||
cur.execute(ltv_analysis_query)
|
||
ltv_analysis = cur.fetchall()
|
||
|
||
# Анализ прибыльности услуг
|
||
service_profitability_query = """
|
||
SELECT
|
||
service_type,
|
||
COUNT(*) as transaction_count,
|
||
SUM(payment_amount) as total_revenue,
|
||
AVG(payment_amount) as avg_price,
|
||
COUNT(DISTINCT user_id) as unique_users,
|
||
COUNT(CASE WHEN service_status = 'success' THEN 1 END) as successful_transactions
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= SYSDATE - 90
|
||
GROUP BY service_type
|
||
ORDER BY SUM(payment_amount) DESC
|
||
"""
|
||
cur.execute(service_profitability_query)
|
||
service_profitability = cur.fetchall()
|
||
|
||
# Время до первой покупки
|
||
time_to_purchase_query = """
|
||
SELECT
|
||
ROUND(AVG(pl.created_date - bu.created_at), 1) as avg_days_to_purchase,
|
||
COUNT(*) as first_purchases
|
||
FROM bot_users bu
|
||
INNER JOIN (
|
||
SELECT user_id, MIN(created_date) as first_purchase_date
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
GROUP BY user_id
|
||
) first_pl ON bu.id = first_pl.user_id
|
||
INNER JOIN payment_logs pl ON bu.id = pl.user_id AND pl.created_date = first_pl.first_purchase_date
|
||
WHERE bu.created_at >= SYSDATE - 365
|
||
"""
|
||
cur.execute(time_to_purchase_query)
|
||
time_to_purchase = cur.fetchone()
|
||
|
||
return {
|
||
'monetization_funnel': funnel_result,
|
||
'ltv_analysis': ltv_analysis or [],
|
||
'service_profitability': service_profitability or [],
|
||
'time_to_purchase': time_to_purchase
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_biz_monetization_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
'monetization_funnel': None,
|
||
'ltv_analysis': [],
|
||
'service_profitability': [],
|
||
'time_to_purchase': None
|
||
}
|
||
|
||
return await self._execute_query(_get_stats)
|
||
|
||
async def get_biz_optimization_stats(self) -> Dict:
|
||
"""Анализ возможностей оптимизации"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Анализ оттока пользователей
|
||
churn_analysis_query = """
|
||
SELECT
|
||
user_status,
|
||
COUNT(*) as user_count,
|
||
AVG(total_payments) as avg_revenue,
|
||
COUNT(CASE WHEN total_payments > 0 THEN 1 END) as paying_users
|
||
FROM (
|
||
SELECT
|
||
CASE
|
||
WHEN last_interaction_date >= SYSDATE - 7 THEN 'Active (0-7 days)'
|
||
WHEN last_interaction_date >= SYSDATE - 30 THEN 'Recent (8-30 days)'
|
||
WHEN last_interaction_date >= SYSDATE - 90 THEN 'Dormant (31-90 days)'
|
||
ELSE 'Churned (>90 days)'
|
||
END as user_status,
|
||
CASE
|
||
WHEN last_interaction_date >= SYSDATE - 7 THEN 1
|
||
WHEN last_interaction_date >= SYSDATE - 30 THEN 2
|
||
WHEN last_interaction_date >= SYSDATE - 90 THEN 3
|
||
ELSE 4
|
||
END as sort_order,
|
||
total_payments
|
||
FROM bot_users
|
||
WHERE is_active = 1 AND last_interaction_date IS NOT NULL
|
||
) t
|
||
GROUP BY user_status, sort_order
|
||
ORDER BY sort_order
|
||
"""
|
||
cur.execute(churn_analysis_query)
|
||
churn_analysis = cur.fetchall()
|
||
|
||
# Неэффективные запросы
|
||
inefficient_requests_query = """
|
||
SELECT
|
||
service_type,
|
||
COUNT(*) as total_requests,
|
||
COUNT(CASE WHEN service_status = 'no_data' THEN 1 END) as no_data_requests,
|
||
COUNT(CASE WHEN service_status = 'error' THEN 1 END) as error_requests,
|
||
COUNT(CASE WHEN refund_status <> 'no_refund' THEN 1 END) as refunded_requests
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= SYSDATE - 30
|
||
GROUP BY service_type
|
||
ORDER BY service_type
|
||
"""
|
||
cur.execute(inefficient_requests_query)
|
||
inefficient_requests = cur.fetchall()
|
||
|
||
# Пользователи с высоким потенциалом
|
||
high_potential_query = """
|
||
SELECT
|
||
COUNT(CASE WHEN interaction_count >= 5 AND successful_payments_count = 0 THEN 1 END) as engaged_non_buyers,
|
||
COUNT(CASE WHEN successful_payments_count = 1 AND total_payments >= 5 THEN 1 END) as potential_repeat_buyers,
|
||
COUNT(CASE WHEN is_premium = 1 AND total_payments < 20 THEN 1 END) as underperforming_premium,
|
||
COUNT(CASE WHEN total_payments >= 10 AND last_interaction_date < SYSDATE - 30 THEN 1 END) as valuable_dormant
|
||
FROM bot_users
|
||
WHERE is_active = 1
|
||
"""
|
||
cur.execute(high_potential_query)
|
||
high_potential = cur.fetchone()
|
||
|
||
# Анализ ценообразования
|
||
pricing_analysis_query = """
|
||
SELECT
|
||
service_type,
|
||
payment_amount as price,
|
||
COUNT(*) as purchase_count,
|
||
COUNT(CASE WHEN service_status = 'success' THEN 1 END) as successful_count,
|
||
COUNT(CASE WHEN refund_status <> 'no_refund' THEN 1 END) as refund_count
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= SYSDATE - 60
|
||
GROUP BY service_type, payment_amount
|
||
ORDER BY service_type, payment_amount
|
||
"""
|
||
cur.execute(pricing_analysis_query)
|
||
pricing_analysis = cur.fetchall()
|
||
|
||
return {
|
||
'churn_analysis': churn_analysis or [],
|
||
'inefficient_requests': inefficient_requests or [],
|
||
'high_potential': high_potential,
|
||
'pricing_analysis': pricing_analysis or []
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_biz_optimization_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
'churn_analysis': [],
|
||
'inefficient_requests': [],
|
||
'high_potential': None,
|
||
'pricing_analysis': []
|
||
}
|
||
|
||
return await self._execute_query(_get_stats)
|
||
|
||
async def get_biz_recommendations_stats(self) -> Dict:
|
||
"""Автоматические рекомендации для бизнеса"""
|
||
def _get_stats():
|
||
try:
|
||
with self._get_connection() as conn:
|
||
with conn.cursor() as cur:
|
||
# Ключевые метрики для анализа
|
||
key_metrics_query = """
|
||
SELECT
|
||
COUNT(*) as total_users,
|
||
COUNT(CASE WHEN successful_payments_count > 0 THEN 1 END) as paying_users,
|
||
AVG(total_payments) as avg_ltv,
|
||
COUNT(CASE WHEN last_interaction_date < SYSDATE - 30 THEN 1 END) as dormant_users,
|
||
COUNT(CASE WHEN interaction_count >= 5 AND successful_payments_count = 0 THEN 1 END) as engaged_non_buyers,
|
||
COUNT(CASE WHEN successful_payments_count = 1 THEN 1 END) as one_time_buyers
|
||
FROM bot_users
|
||
WHERE is_active = 1
|
||
"""
|
||
cur.execute(key_metrics_query)
|
||
key_metrics = cur.fetchone()
|
||
|
||
# Метрики успешности услуг
|
||
service_success_query = """
|
||
SELECT
|
||
service_type,
|
||
COUNT(*) as total_requests,
|
||
COUNT(CASE WHEN service_status = 'success' THEN 1 END) as successful_requests,
|
||
COUNT(CASE WHEN refund_status <> 'no_refund' THEN 1 END) as refunds,
|
||
AVG(payment_amount) as avg_price
|
||
FROM payment_logs
|
||
WHERE payment_status = 'completed'
|
||
AND created_date >= SYSDATE - 30
|
||
GROUP BY service_type
|
||
"""
|
||
cur.execute(service_success_query)
|
||
service_success = cur.fetchall()
|
||
|
||
# Рост пользователей за последние периоды
|
||
growth_metrics_query = """
|
||
SELECT
|
||
COUNT(CASE WHEN created_at >= SYSDATE - 7 THEN 1 END) as users_last_week,
|
||
COUNT(CASE WHEN created_at >= SYSDATE - 14 AND created_at < SYSDATE - 7 THEN 1 END) as users_prev_week,
|
||
COUNT(CASE WHEN created_at >= SYSDATE - 30 THEN 1 END) as users_last_month,
|
||
COUNT(CASE WHEN created_at >= SYSDATE - 60 AND created_at < SYSDATE - 30 THEN 1 END) as users_prev_month
|
||
FROM bot_users
|
||
"""
|
||
cur.execute(growth_metrics_query)
|
||
growth_metrics = cur.fetchone()
|
||
|
||
return {
|
||
'key_metrics': key_metrics,
|
||
'service_success': service_success or [],
|
||
'growth_metrics': growth_metrics
|
||
}
|
||
except Exception as e:
|
||
logging.error(f"SQL Error in get_biz_recommendations_stats:")
|
||
logging.error(f"Error: {e}")
|
||
return {
|
||
'key_metrics': None,
|
||
'service_success': [],
|
||
'growth_metrics': None
|
||
}
|
||
|
||
return await self._execute_query(_get_stats) |