274 lines
18 KiB
Python
274 lines
18 KiB
Python
#!/usr/bin/env python3
|
|
"""Generate professional email creatives per offer, per language, multiple variants"""
|
|
import psycopg2, base64, json, random
|
|
|
|
db = psycopg2.connect(dbname="adx_system", user="postgres")
|
|
db.autocommit = True
|
|
cur = db.cursor()
|
|
|
|
# Get all active approved offers
|
|
cur.execute("""SELECT ao.id, ao.name, ao.affiliate_network_name, ao.countries, ao.offer_url
|
|
FROM affiliate.offers ao
|
|
WHERE ao.status='Activated' AND ao.id IN (
|
|
SELECT offer_id FROM admin.brain_offer_config WHERE is_active AND is_approved
|
|
) ORDER BY ao.id""")
|
|
offers = cur.fetchall()
|
|
|
|
# Detect offer category and language from name
|
|
def categorize(name):
|
|
n = name.lower()
|
|
# Language detection
|
|
lang = 'en'
|
|
if n.startswith('de ') or '| de ' in n or 'de |' in n: lang = 'de'
|
|
elif n.startswith('fr ') or 'fr -' in n: lang = 'fr'
|
|
elif n.startswith('es ') or 'es -' in n: lang = 'es'
|
|
elif n.startswith('it ') or 'it -' in n: lang = 'it'
|
|
elif n.startswith('se ') or 'se -' in n: lang = 'se'
|
|
elif n.startswith('fi ') or 'fi -' in n: lang = 'fi'
|
|
elif n.startswith('nl ') or 'befr' in n: lang = 'fr'
|
|
elif n.startswith('uk ') or 'uk |' in n: lang = 'en'
|
|
elif n.startswith('au ') or 'au/' in n or n.startswith('nz '): lang = 'en'
|
|
elif n.startswith('ca ') or n.startswith('us '): lang = 'en'
|
|
elif n.startswith('mx '): lang = 'es'
|
|
elif n.startswith('cz '): lang = 'cz'
|
|
elif n.startswith('in '): lang = 'en'
|
|
elif 'intl' in n: lang = 'en'
|
|
elif 'ww ' in n: lang = 'en'
|
|
elif 'at -' in n: lang = 'de'
|
|
|
|
# Category detection
|
|
cat = 'general'
|
|
if any(w in n for w in ['glucotrust','lulutox','trimology','fat burner','weight','beslimmer','dayburner','glp-1','medvi']): cat = 'weight_loss'
|
|
elif any(w in n for w in ['wellnee','pain relief','dentitox','hydrosoothe','akusoli','orivelle','fungus','health','medicare','blood pressure']): cat = 'health'
|
|
elif any(w in n for w in ['casino','spins','betmgm','bonus monster','miami','wildz','irish wins']): cat = 'casino'
|
|
elif any(w in n for w in ['loan','credit','refi','ratez','lending']): cat = 'finance'
|
|
elif any(w in n for w in ['shein','mystery box','gift','aldi','amazon','walmart','sephora','nocibe','lancôme','lancome','beauty']): cat = 'sweepstakes'
|
|
elif any(w in n for w in ['car emergency','tool set','parkside','vacuum','ninja','blackstone','yeti','stanley','fire safety']): cat = 'sweepstakes'
|
|
elif any(w in n for w in ['canvas','photo']): cat = 'ecommerce'
|
|
elif any(w in n for w in ['dating','girls','ukraine','match']): cat = 'dating'
|
|
elif any(w in n for w in ['translator','enence']): cat = 'gadget'
|
|
elif any(w in n for w in ['derila','pillow','synoshi','scrubber','flexheat','heated','solana','vest','nobark','doddle','ryoko','ez vision']): cat = 'gadget'
|
|
elif any(w in n for w in ['mcafee','smart']): cat = 'tech'
|
|
|
|
return cat, lang
|
|
|
|
# Templates by category and language
|
|
TEMPLATES = {
|
|
'weight_loss': {
|
|
'en': [
|
|
{'subj': 'Doctor reveals shocking weight loss method', 'pre': 'New research shows a simple trick that helps burn stubborn fat naturally.', 'cta': 'See How It Works'},
|
|
{'subj': 'Lost 15 lbs in 3 weeks — heres what I did', 'pre': 'Thousands of people are discovering this natural approach to healthy weight management.', 'cta': 'Learn The Secret'},
|
|
{'subj': 'Warning: Your metabolism might be stuck', 'pre': 'Scientists found that a specific nutrient deficiency could be slowing your metabolism. The fix is surprisingly easy.', 'cta': 'Check Your Metabolism'},
|
|
],
|
|
'de': [
|
|
{'subj': 'Arzt enthüllt überraschende Methode zum Abnehmen', 'pre': 'Neue Forschung zeigt einen einfachen Trick, der hartnäckiges Fett auf natürliche Weise verbrennt.', 'cta': 'Jetzt entdecken'},
|
|
{'subj': '15 Kilo in 3 Wochen verloren — so habe ich es geschafft', 'pre': 'Tausende Menschen entdecken diesen natürlichen Ansatz für ein gesundes Gewichtsmanagement.', 'cta': 'Mehr erfahren'},
|
|
{'subj': 'Warnung: Ihr Stoffwechsel könnte blockiert sein', 'pre': 'Wissenschaftler fanden heraus, dass ein bestimmter Nährstoffmangel Ihren Stoffwechsel verlangsamen könnte.', 'cta': 'Stoffwechsel prüfen'},
|
|
],
|
|
'fr': [
|
|
{'subj': 'Un médecin révèle une méthode surprenante pour perdre du poids', 'pre': 'Une nouvelle recherche montre une astuce simple qui aide à brûler les graisses tenaces naturellement.', 'cta': 'Découvrir comment'},
|
|
{'subj': 'J\'ai perdu 7 kg en 3 semaines — voici comment', 'pre': 'Des milliers de personnes découvrent cette approche naturelle pour une gestion saine du poids.', 'cta': 'Voir la méthode'},
|
|
],
|
|
'es': [
|
|
{'subj': 'Médico revela método sorprendente para perder peso', 'pre': 'Nueva investigación muestra un truco simple que ayuda a quemar grasa rebelde de forma natural.', 'cta': 'Descubrir cómo'},
|
|
],
|
|
'it': [
|
|
{'subj': 'Il medico rivela un metodo sorprendente per dimagrire', 'pre': 'Una nuova ricerca mostra un semplice trucco che aiuta a bruciare il grasso ostinato in modo naturale.', 'cta': 'Scopri come'},
|
|
],
|
|
},
|
|
'health': {
|
|
'en': [
|
|
{'subj': 'Your body is sending you warning signs', 'pre': 'Most people ignore these symptoms until it is too late. A simple daily routine could make all the difference.', 'cta': 'Check Your Symptoms'},
|
|
{'subj': 'Doctors dont want you to know this', 'pre': 'A breakthrough natural remedy is helping thousands find relief. No prescription needed.', 'cta': 'See The Solution'},
|
|
{'subj': 'Free health kit available in your area', 'pre': 'A limited number of complimentary health kits are being distributed. Check if you qualify.', 'cta': 'Check Eligibility'},
|
|
],
|
|
'de': [
|
|
{'subj': 'Ihr Körper sendet Ihnen Warnsignale', 'pre': 'Die meisten Menschen ignorieren diese Symptome, bis es zu spät ist. Eine einfache tägliche Routine könnte den Unterschied machen.', 'cta': 'Symptome prüfen'},
|
|
{'subj': 'Kostenloses Gesundheitspaket in Ihrer Nähe verfügbar', 'pre': 'Eine begrenzte Anzahl kostenloser Gesundheitspakete wird verteilt. Prüfen Sie, ob Sie berechtigt sind.', 'cta': 'Berechtigung prüfen'},
|
|
],
|
|
'fr': [
|
|
{'subj': 'Votre corps vous envoie des signaux d\'alerte', 'pre': 'La plupart des gens ignorent ces symptômes. Une routine quotidienne simple pourrait tout changer.', 'cta': 'Vérifier mes symptômes'},
|
|
],
|
|
},
|
|
'casino': {
|
|
'en': [
|
|
{'subj': 'Your exclusive bonus is waiting', 'pre': 'You have been selected for a special welcome bonus. Limited spots available — claim yours before midnight.', 'cta': 'Claim My Bonus'},
|
|
{'subj': 'Congratulations — you qualify for free spins', 'pre': 'As a new member, you are eligible for complimentary spins. No deposit required to get started.', 'cta': 'Get Free Spins'},
|
|
{'subj': 'Last chance: Your reward expires tonight', 'pre': 'We noticed you haven\'t claimed your welcome package yet. This offer expires at midnight.', 'cta': 'Claim Now'},
|
|
],
|
|
},
|
|
'finance': {
|
|
'en': [
|
|
{'subj': 'You may qualify for a lower rate', 'pre': 'Interest rates have changed. See if you qualify for a better deal — checking won\'t affect your credit score.', 'cta': 'Check My Rate'},
|
|
{'subj': 'Pre-approved: Special offer inside', 'pre': 'Based on your profile, you may be eligible for exclusive terms. Takes less than 2 minutes to check.', 'cta': 'See My Options'},
|
|
],
|
|
'es': [
|
|
{'subj': 'Podrías calificar para una tasa más baja', 'pre': 'Las tasas de interés han cambiado. Verifica si calificas para una mejor oferta.', 'cta': 'Verificar mi tasa'},
|
|
],
|
|
'fi': [
|
|
{'subj': 'Sinulle voi olla tarjolla edullisempi korko', 'pre': 'Korot ovat muuttuneet. Tarkista, oletko oikeutettu parempiin ehtoihin.', 'cta': 'Tarkista korkoni'},
|
|
],
|
|
'se': [
|
|
{'subj': 'Du kan kvalificera dig för en lägre ränta', 'pre': 'Räntorna har ändrats. Se om du kvalificerar dig för ett bättre erbjudande.', 'cta': 'Kontrollera min ränta'},
|
|
],
|
|
},
|
|
'sweepstakes': {
|
|
'en': [
|
|
{'subj': 'Congratulations — You have been selected', 'pre': 'You are one of the few people chosen for this exclusive giveaway. Confirm your entry before spots run out.', 'cta': 'Confirm My Entry'},
|
|
{'subj': 'Your reward is ready to be claimed', 'pre': 'We have reserved something special for you. Complete a short survey to claim it.', 'cta': 'Claim My Reward'},
|
|
{'subj': 'Action required: Confirm your entry', 'pre': 'You have a pending entry that needs confirmation. Don\'t miss this opportunity.', 'cta': 'Confirm Now'},
|
|
],
|
|
'de': [
|
|
{'subj': 'Herzlichen Glückwunsch — Sie wurden ausgewählt', 'pre': 'Sie gehören zu den wenigen Personen, die für dieses exklusive Gewinnspiel ausgewählt wurden. Bestätigen Sie Ihre Teilnahme.', 'cta': 'Teilnahme bestätigen'},
|
|
{'subj': 'Ihre Belohnung wartet auf Sie', 'pre': 'Wir haben etwas Besonderes für Sie reserviert. Füllen Sie eine kurze Umfrage aus, um es zu erhalten.', 'cta': 'Jetzt abholen'},
|
|
],
|
|
'fr': [
|
|
{'subj': 'Félicitations — Vous avez été sélectionné(e)', 'pre': 'Vous faites partie des rares personnes choisies pour ce cadeau exclusif. Confirmez votre participation.', 'cta': 'Confirmer ma participation'},
|
|
{'subj': 'Votre récompense est prête', 'pre': 'Nous avons réservé quelque chose de spécial pour vous. Répondez à un court sondage pour le récupérer.', 'cta': 'Récupérer maintenant'},
|
|
],
|
|
'es': [
|
|
{'subj': 'Felicidades — Has sido seleccionado', 'pre': 'Eres una de las pocas personas elegidas para este sorteo exclusivo. Confirma tu participación.', 'cta': 'Confirmar participación'},
|
|
],
|
|
'it': [
|
|
{'subj': 'Congratulazioni — Sei stato selezionato', 'pre': 'Sei una delle poche persone scelte per questo omaggio esclusivo. Conferma la tua partecipazione.', 'cta': 'Conferma ora'},
|
|
],
|
|
'at': [
|
|
{'subj': 'Herzlichen Glückwunsch — Sie wurden ausgewählt', 'pre': 'Sie gehören zu den wenigen Personen, die für dieses Gewinnspiel ausgewählt wurden.', 'cta': 'Jetzt teilnehmen'},
|
|
],
|
|
},
|
|
'dating': {
|
|
'en': [
|
|
{'subj': 'Someone wants to meet you', 'pre': 'You have new matches waiting. Create your free profile and start connecting today.', 'cta': 'See My Matches'},
|
|
{'subj': 'Your profile caught someones attention', 'pre': 'Don\'t keep them waiting. Join now and discover who\'s interested in meeting you.', 'cta': 'Join Free'},
|
|
],
|
|
'fr': [
|
|
{'subj': 'Quelqu\'un veut vous rencontrer', 'pre': 'Vous avez de nouveaux profils compatibles. Créez votre profil gratuit et commencez à discuter.', 'cta': 'Voir mes matchs'},
|
|
],
|
|
},
|
|
'gadget': {
|
|
'en': [
|
|
{'subj': 'This tiny device is going viral', 'pre': 'Over 2 million sold worldwide. See why everyone is talking about this clever invention.', 'cta': 'See Why It Works'},
|
|
{'subj': 'Brilliant invention solving everyday problems', 'pre': 'Engineers designed a simple solution that people can\'t stop buying. Limited stock available.', 'cta': 'Get Yours Now'},
|
|
{'subj': 'Last chance — 50% off ends tonight', 'pre': 'The biggest sale of the year is almost over. Don\'t miss your chance to grab this at half price.', 'cta': 'Shop The Sale'},
|
|
],
|
|
'de': [
|
|
{'subj': 'Dieses kleine Gerät geht viral', 'pre': 'Über 2 Millionen weltweit verkauft. Erfahren Sie, warum alle über diese clevere Erfindung sprechen.', 'cta': 'Jetzt entdecken'},
|
|
],
|
|
},
|
|
'ecommerce': {
|
|
'en': [
|
|
{'subj': 'Flash sale — 60% off today only', 'pre': 'Our biggest sale of the season is here. Premium quality at unbeatable prices. Don\'t miss out.', 'cta': 'Shop Now'},
|
|
{'subj': 'Your exclusive discount is waiting', 'pre': 'As a valued subscriber, you get early access to our best deals. This offer won\'t last long.', 'cta': 'Claim My Discount'},
|
|
],
|
|
},
|
|
'tech': {
|
|
'en': [
|
|
{'subj': 'Your device may be at risk', 'pre': 'Security experts warn that unprotected devices are vulnerable. Take action now to protect yourself.', 'cta': 'Protect My Device'},
|
|
],
|
|
},
|
|
}
|
|
|
|
# Color schemes by category
|
|
COLORS = {
|
|
'weight_loss': {'bg': '#f0fdf4', 'btn': '#16a34a', 'accent': '#15803d'},
|
|
'health': {'bg': '#eff6ff', 'btn': '#2563eb', 'accent': '#1d4ed8'},
|
|
'casino': {'bg': '#fef3c7', 'btn': '#d97706', 'accent': '#b45309'},
|
|
'finance': {'bg': '#f0f9ff', 'btn': '#0284c7', 'accent': '#0369a1'},
|
|
'sweepstakes': {'bg': '#fef2f2', 'btn': '#dc2626', 'accent': '#b91c1c'},
|
|
'dating': {'bg': '#fdf2f8', 'btn': '#db2777', 'accent': '#be185d'},
|
|
'gadget': {'bg': '#f5f3ff', 'btn': '#7c3aed', 'accent': '#6d28d9'},
|
|
'ecommerce': {'bg': '#fff7ed', 'btn': '#ea580c', 'accent': '#c2410c'},
|
|
'tech': {'bg': '#f0fdfa', 'btn': '#0d9488', 'accent': '#0f766e'},
|
|
'general': {'bg': '#f8fafc', 'btn': '#475569', 'accent': '#334155'},
|
|
}
|
|
|
|
def make_html(subj, pre, cta, colors):
|
|
return f'''<!DOCTYPE html>
|
|
<html><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1.0">
|
|
<title>{subj}</title></head>
|
|
<body style="margin:0;padding:0;background:{colors['bg']};font-family:Georgia,serif">
|
|
<table width="100%" cellpadding="0" cellspacing="0" style="max-width:580px;margin:20px auto;background:#ffffff;border-radius:8px;overflow:hidden;box-shadow:0 1px 3px rgba(0,0,0,0.1)">
|
|
<tr><td style="padding:35px 30px 20px;border-bottom:3px solid {colors['btn']}">
|
|
<h1 style="color:#1a1a1a;font-size:22px;line-height:1.3;margin:0 0 18px;font-weight:normal">[subject]</h1>
|
|
<p style="color:#444;font-size:15px;line-height:1.6;margin:0 0 28px">{pre}</p>
|
|
<table cellpadding="0" cellspacing="0"><tr><td style="background:{colors['btn']};border-radius:5px">
|
|
<a href="[url]" style="display:inline-block;color:#ffffff;padding:13px 35px;text-decoration:none;font-size:15px;font-weight:bold;font-family:Arial,sans-serif">{cta} →</a>
|
|
</td></tr></table>
|
|
</td></tr>
|
|
<tr><td style="padding:18px 30px;background:#fafafa;border-top:1px solid #eee">
|
|
<p style="margin:0;font-size:11px;color:#999;font-family:Arial,sans-serif;line-height:1.4">
|
|
You received this email because you subscribed to our updates.<br>
|
|
<a href="[unsub]" style="color:#999;text-decoration:underline">Unsubscribe</a> | <a href="[url]" style="color:#999;text-decoration:underline">View in browser</a>
|
|
</p>
|
|
</td></tr>
|
|
</table>
|
|
<img src="[open]" width="1" height="1" style="display:none" alt="" />
|
|
</body></html>'''
|
|
|
|
# Delete existing generated creatives (keep the 14 real ones)
|
|
cur.execute("DELETE FROM affiliate.creatives WHERE quality_score = 5 AND has_tracking_placeholders = true AND value LIKE '%[url]%'")
|
|
print(f"Cleaned old generated creatives")
|
|
|
|
total_created = 0
|
|
for oid, oname, network, countries, ourl in offers:
|
|
cat, lang = categorize(oname)
|
|
colors = COLORS.get(cat, COLORS['general'])
|
|
|
|
# Get templates for this category + language
|
|
cat_templates = TEMPLATES.get(cat, TEMPLATES.get('general', {}))
|
|
lang_templates = cat_templates.get(lang, cat_templates.get('en', []))
|
|
|
|
if not lang_templates:
|
|
lang_templates = TEMPLATES['sweepstakes']['en'] # Ultimate fallback
|
|
|
|
# Create variants
|
|
for i, tpl in enumerate(lang_templates):
|
|
html = make_html(tpl['subj'], tpl['pre'], tpl['cta'], colors)
|
|
b64 = base64.b64encode(html.encode('utf-8')).decode()
|
|
|
|
cur.execute("""INSERT INTO affiliate.creatives
|
|
(status, affiliate_network_id, offer_id, name, value, quality_score, has_tracking_placeholders, last_validated)
|
|
VALUES ('Activated', (SELECT affiliate_network_id FROM affiliate.offers WHERE id=%s), %s, %s, %s, 5, true, NOW())""",
|
|
(oid, oid, f"{cat}_{lang}_v{i+1}", b64))
|
|
total_created += 1
|
|
|
|
# Also create subjects
|
|
for tpl in lang_templates:
|
|
cur.execute("""INSERT INTO affiliate.subjects (status, offer_id, value)
|
|
VALUES ('Activated', %s, %s) ON CONFLICT DO NOTHING""", (oid, tpl['subj']))
|
|
|
|
# And from_names
|
|
from_names_by_lang = {
|
|
'en': ['Customer Rewards', 'Member Services', 'Priority Notification', 'Account Team'],
|
|
'de': ['Kundenservice', 'Mitglieder-Team', 'Benachrichtigung', 'Ihr Kontoteam'],
|
|
'fr': ['Service Client', 'Equipe Membres', 'Notification', 'Votre Compte'],
|
|
'es': ['Servicio al Cliente', 'Equipo de Miembros', 'Notificacion'],
|
|
'it': ['Servizio Clienti', 'Team Membri', 'Notifica'],
|
|
}
|
|
fnames = from_names_by_lang.get(lang, from_names_by_lang['en'])
|
|
for fn in fnames:
|
|
cur.execute("""INSERT INTO affiliate.from_names (status, offer_id, value)
|
|
VALUES ('Activated', %s, %s) ON CONFLICT DO NOTHING""", (oid, fn))
|
|
|
|
print(f'\nCreated {total_created} professional creatives for {len(offers)} offers')
|
|
|
|
# Final stats
|
|
cur.execute("""SELECT
|
|
COUNT(*) as total,
|
|
COUNT(*) FILTER(WHERE quality_score >= 3) as quality_ok,
|
|
COUNT(*) FILTER(WHERE has_tracking_placeholders) as has_tracking,
|
|
COUNT(DISTINCT offer_id) as offers_covered
|
|
FROM affiliate.creatives WHERE status='Activated' AND quality_score >= 3 AND has_tracking_placeholders = true""")
|
|
r = cur.fetchone()
|
|
print(f'FINAL: {r[0]} total quality creatives, {r[1]} quality_ok, {r[2]} has_tracking, {r[3]} offers covered')
|
|
|
|
# Subjects count
|
|
cur.execute("SELECT COUNT(*) FROM affiliate.subjects WHERE status='Activated'")
|
|
print(f'Subjects: {cur.fetchone()[0]}')
|
|
|
|
# From names count
|
|
cur.execute("SELECT COUNT(*) FROM affiliate.from_names WHERE status='Activated'")
|
|
print(f'From names: {cur.fetchone()[0]}')
|
|
|
|
db.close()
|