371 lines
12 KiB
Python
Executable File
371 lines
12 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""
|
|
PROVIDER PERFORMANCE ANALYZER
|
|
Analyse les performances par:
|
|
- Provider (Hetzner, Scaleway, Huawei...)
|
|
- Region (hel1, fr-par-1...)
|
|
- ISP cible (Gmail, Hotmail, Yahoo...)
|
|
|
|
Identifie les meilleures combinaisons gagnantes
|
|
"""
|
|
|
|
import psycopg2
|
|
import json
|
|
from datetime import datetime, timedelta
|
|
from collections import defaultdict
|
|
|
|
DB_CONFIG = {
|
|
'host': 'localhost',
|
|
'database': 'adx_system',
|
|
'user': 'admin',
|
|
'password': 'admin123'
|
|
}
|
|
|
|
def get_db():
|
|
return psycopg2.connect(**DB_CONFIG)
|
|
|
|
def detect_provider_from_ip(ip):
|
|
"""Detect provider and region from IP"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
ip_prefix = '.'.join(ip.split('.')[:2]) + '.'
|
|
|
|
cur.execute("""
|
|
SELECT provider_name, region_code
|
|
FROM admin.ip_ranges
|
|
WHERE %s LIKE ip_prefix || '%%'
|
|
LIMIT 1
|
|
""", (ip,))
|
|
|
|
result = cur.fetchone()
|
|
conn.close()
|
|
|
|
if result:
|
|
return result[0], result[1]
|
|
|
|
# Fallback: check provider_regions
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
cur.execute("""
|
|
SELECT provider_name, region_code
|
|
FROM admin.provider_regions
|
|
WHERE %s LIKE ip_range_prefix || '%%'
|
|
LIMIT 1
|
|
""", (ip,))
|
|
|
|
result = cur.fetchone()
|
|
conn.close()
|
|
|
|
return result if result else ('unknown', 'unknown')
|
|
|
|
def analyze_provider_performance(days=14):
|
|
"""Analyze performance by provider+region → ISP"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
# Get server performance with provider info
|
|
cur.execute("""
|
|
SELECT
|
|
s.provider_name,
|
|
s.region,
|
|
s.ip_address,
|
|
s.total_sent,
|
|
s.total_delivered,
|
|
s.total_bounced,
|
|
s.is_blacklisted,
|
|
s.started_at,
|
|
CASE WHEN s.status = 'burned' THEN s.destroyed_at ELSE NULL END as burned_at,
|
|
s.inbox_rate,
|
|
s.spam_rate
|
|
FROM admin.servers s
|
|
WHERE s.started_at > NOW() - INTERVAL '%s days'
|
|
""", (days,))
|
|
|
|
servers = cur.fetchall()
|
|
|
|
# Aggregate by provider+region
|
|
provider_stats = defaultdict(lambda: {
|
|
'servers': 0,
|
|
'burned': 0,
|
|
'total_sent': 0,
|
|
'total_inbox': 0,
|
|
'total_spam': 0,
|
|
'lifetimes': [],
|
|
'emails_before_burn': []
|
|
})
|
|
|
|
for row in servers:
|
|
provider, region, ip, sent, delivered, bounced, blacklisted, started, burned, inbox_rate, spam_rate = row
|
|
|
|
if not provider:
|
|
provider, region = detect_provider_from_ip(str(ip))
|
|
|
|
key = (provider, region or 'unknown')
|
|
provider_stats[key]['servers'] += 1
|
|
provider_stats[key]['total_sent'] += sent or 0
|
|
|
|
if blacklisted or burned:
|
|
provider_stats[key]['burned'] += 1
|
|
if started and burned:
|
|
lifetime = (burned - started).total_seconds() / 3600
|
|
provider_stats[key]['lifetimes'].append(lifetime)
|
|
if sent:
|
|
provider_stats[key]['emails_before_burn'].append(sent)
|
|
|
|
# Get detailed ISP performance from seed_tracking
|
|
cur.execute("""
|
|
SELECT
|
|
s.provider_name,
|
|
s.region,
|
|
st.seed_isp,
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN st.inbox_placement = 'inbox' THEN 1 ELSE 0 END) as inbox,
|
|
SUM(CASE WHEN st.inbox_placement = 'spam' THEN 1 ELSE 0 END) as spam,
|
|
SUM(CASE WHEN st.bounced THEN 1 ELSE 0 END) as bounced
|
|
FROM admin.seed_tracking st
|
|
JOIN admin.servers s ON s.ip_address::text = ANY(
|
|
SELECT unnest(string_to_array(st.sender_email, '@'))
|
|
)
|
|
WHERE st.sent_at > NOW() - INTERVAL '%s days'
|
|
GROUP BY s.provider_name, s.region, st.seed_isp
|
|
""", (days,))
|
|
|
|
# Alternative: direct tracking analysis
|
|
cur.execute("""
|
|
SELECT
|
|
seed_isp,
|
|
COUNT(*) as total,
|
|
SUM(CASE WHEN inbox_placement = 'inbox' THEN 1 ELSE 0 END) as inbox,
|
|
SUM(CASE WHEN inbox_placement = 'spam' THEN 1 ELSE 0 END) as spam
|
|
FROM admin.seed_tracking
|
|
WHERE sent_at > NOW() - INTERVAL '%s days'
|
|
GROUP BY seed_isp
|
|
HAVING COUNT(*) >= 10
|
|
""", (days,))
|
|
|
|
isp_global_stats = {row[0]: {'total': row[1], 'inbox': row[2], 'spam': row[3]}
|
|
for row in cur.fetchall()}
|
|
|
|
conn.close()
|
|
return provider_stats, isp_global_stats
|
|
|
|
def update_provider_regions(provider_stats):
|
|
"""Update provider_regions with performance data"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
for (provider, region), stats in provider_stats.items():
|
|
if provider == 'unknown':
|
|
continue
|
|
|
|
burn_rate = (stats['burned'] / stats['servers'] * 100) if stats['servers'] > 0 else 0
|
|
avg_lifetime = sum(stats['lifetimes']) / len(stats['lifetimes']) if stats['lifetimes'] else 0
|
|
avg_emails_burn = sum(stats['emails_before_burn']) / len(stats['emails_before_burn']) if stats['emails_before_burn'] else 0
|
|
|
|
# Performance score (lower burn rate = better)
|
|
performance_score = max(0, 100 - burn_rate * 2)
|
|
|
|
cur.execute("""
|
|
UPDATE admin.provider_regions SET
|
|
total_servers_created = %s,
|
|
total_servers_burned = %s,
|
|
burn_rate = %s,
|
|
total_emails_sent = %s,
|
|
avg_lifetime_hours = %s,
|
|
avg_emails_before_burn = %s,
|
|
performance_score = %s,
|
|
last_updated = NOW()
|
|
WHERE provider_name = %s AND region_code = %s
|
|
""", (
|
|
stats['servers'], stats['burned'], burn_rate,
|
|
stats['total_sent'], avg_lifetime, avg_emails_burn,
|
|
performance_score, provider, region
|
|
))
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def identify_winning_combinations():
|
|
"""Identify best provider+region for each ISP"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
isps = ['gmail', 'hotmail', 'yahoo', 'aol', 'icloud']
|
|
|
|
for isp in isps:
|
|
# Get best performing providers for this ISP
|
|
cur.execute("""
|
|
SELECT
|
|
provider_name, region_code,
|
|
inbox_rate, total_sent, performance_score
|
|
FROM admin.provider_isp_performance
|
|
WHERE target_isp = %s AND total_sent >= 50
|
|
ORDER BY inbox_rate DESC, performance_score DESC
|
|
LIMIT 5
|
|
""", (isp,))
|
|
|
|
results = cur.fetchall()
|
|
|
|
if results:
|
|
best = results[0]
|
|
alternatives = [{'provider': r[0], 'region': r[1], 'inbox_rate': float(r[2] or 0)}
|
|
for r in results[1:]]
|
|
|
|
# Calculate confidence based on sample size
|
|
confidence = min(100, best[3] / 10) if best[3] else 0
|
|
|
|
cur.execute("""
|
|
INSERT INTO admin.winning_combinations
|
|
(target_isp, target_country, best_provider, best_region,
|
|
inbox_rate, total_tested, confidence_score, alternatives)
|
|
VALUES (%s, 'US', %s, %s, %s, %s, %s, %s)
|
|
ON CONFLICT (target_isp, target_country) DO UPDATE SET
|
|
best_provider = EXCLUDED.best_provider,
|
|
best_region = EXCLUDED.best_region,
|
|
inbox_rate = EXCLUDED.inbox_rate,
|
|
total_tested = EXCLUDED.total_tested,
|
|
confidence_score = EXCLUDED.confidence_score,
|
|
alternatives = EXCLUDED.alternatives,
|
|
last_updated = NOW()
|
|
""", (isp, best[0], best[1], best[2], best[3], confidence, json.dumps(alternatives)))
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def show_report():
|
|
"""Display performance report"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
print("=" * 80)
|
|
print("🏆 PROVIDER PERFORMANCE REPORT")
|
|
print(f"Time: {datetime.now()}")
|
|
print("=" * 80)
|
|
|
|
# Provider rankings
|
|
print("\n📊 PROVIDER RANKINGS (by burn rate):")
|
|
print("-" * 80)
|
|
cur.execute("""
|
|
SELECT provider_name, region_code, country_code,
|
|
total_servers_created, burn_rate, avg_lifetime_hours,
|
|
avg_emails_before_burn, performance_score, cost_per_hour
|
|
FROM admin.provider_regions
|
|
WHERE total_servers_created > 0
|
|
ORDER BY burn_rate ASC, performance_score DESC
|
|
LIMIT 15
|
|
""")
|
|
|
|
print(f"{'Provider':<12} {'Region':<12} {'Servers':>8} {'Burn%':>7} {'Avg Life':>10} {'Avg K-send':>10} {'Score':>6} {'$/hr':>6}")
|
|
print("-" * 80)
|
|
|
|
for row in cur.fetchall():
|
|
provider, region, country, servers, burn, lifetime, emails, score, cost = row
|
|
emails_k = (emails or 0) / 1000
|
|
print(f"{provider:<12} {region:<12} {servers:>8} {burn or 0:>6.1f}% {lifetime or 0:>9.1f}h {emails_k:>9.1f}K {score or 0:>6.0f} ${cost or 0:>.3f}")
|
|
|
|
# Winning combinations
|
|
print("\n🎯 WINNING COMBINATIONS (Provider+Region → ISP):")
|
|
print("-" * 80)
|
|
cur.execute("""
|
|
SELECT target_isp, best_provider, best_region, inbox_rate,
|
|
total_tested, confidence_score
|
|
FROM admin.winning_combinations
|
|
ORDER BY target_isp
|
|
""")
|
|
|
|
print(f"{'ISP':<12} {'Best Provider':<15} {'Region':<15} {'Inbox%':>8} {'Tests':>8} {'Conf%':>6}")
|
|
print("-" * 80)
|
|
|
|
for row in cur.fetchall():
|
|
isp, provider, region, inbox, tests, conf = row
|
|
print(f"{isp:<12} {provider or '-':<15} {region or '-':<15} {inbox or 0:>7.1f}% {tests or 0:>8} {conf or 0:>5.0f}%")
|
|
|
|
# Recommendations
|
|
print("\n💡 RECOMMENDATIONS:")
|
|
print("-" * 80)
|
|
|
|
cur.execute("""
|
|
SELECT provider_name, region_code, burn_rate, cost_per_hour
|
|
FROM admin.provider_regions
|
|
WHERE total_servers_created >= 3
|
|
ORDER BY burn_rate ASC, cost_per_hour ASC
|
|
LIMIT 3
|
|
""")
|
|
|
|
print("Best overall (low burn, low cost):")
|
|
for i, row in enumerate(cur.fetchall(), 1):
|
|
print(f" {i}. {row[0]} / {row[1]} - Burn: {row[2] or 0:.1f}%, Cost: ${row[3] or 0:.3f}/hr")
|
|
|
|
conn.close()
|
|
|
|
def get_best_provider_for_isp(target_isp, target_country='US'):
|
|
"""Get recommended provider for specific ISP"""
|
|
conn = get_db()
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("""
|
|
SELECT best_provider, best_region, inbox_rate, confidence_score, alternatives
|
|
FROM admin.winning_combinations
|
|
WHERE target_isp = %s AND (target_country = %s OR target_country IS NULL)
|
|
""", (target_isp, target_country))
|
|
|
|
result = cur.fetchone()
|
|
conn.close()
|
|
|
|
if result:
|
|
return {
|
|
'provider': result[0],
|
|
'region': result[1],
|
|
'inbox_rate': result[2],
|
|
'confidence': result[3],
|
|
'alternatives': json.loads(result[4]) if result[4] else []
|
|
}
|
|
|
|
# Fallback to best overall
|
|
return {
|
|
'provider': 'hetzner',
|
|
'region': 'hel1',
|
|
'inbox_rate': None,
|
|
'confidence': 0,
|
|
'alternatives': []
|
|
}
|
|
|
|
def main():
|
|
import sys
|
|
|
|
if len(sys.argv) > 1:
|
|
cmd = sys.argv[1]
|
|
|
|
if cmd == 'best':
|
|
isp = sys.argv[2] if len(sys.argv) > 2 else 'gmail'
|
|
result = get_best_provider_for_isp(isp)
|
|
print(f"\nBest provider for {isp}:")
|
|
print(f" Provider: {result['provider']}")
|
|
print(f" Region: {result['region']}")
|
|
print(f" Inbox Rate: {result['inbox_rate']}%")
|
|
print(f" Confidence: {result['confidence']}%")
|
|
if result['alternatives']:
|
|
print(f" Alternatives: {result['alternatives']}")
|
|
return
|
|
|
|
print("Analyzing provider performance...")
|
|
|
|
# Analyze
|
|
provider_stats, isp_stats = analyze_provider_performance(days=14)
|
|
print(f"Found {len(provider_stats)} provider+region combinations")
|
|
|
|
# Update database
|
|
update_provider_regions(provider_stats)
|
|
|
|
# Identify winners
|
|
identify_winning_combinations()
|
|
|
|
# Show report
|
|
show_report()
|
|
|
|
print("\n✅ Analysis complete!")
|
|
|
|
if __name__ == '__main__':
|
|
main()
|