302 lines
16 KiB
Python
302 lines
16 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Generate email creatives for Everflow offers based on ADAC template structure.
|
|
Each creative gets proper [url], [open], [unsub] placeholders.
|
|
"""
|
|
import psycopg2, base64, json
|
|
|
|
DB = {'host':'localhost','database':'adx_system','user':'admin','password':'admin123'}
|
|
|
|
# Offer creative configs — brand, language, CTA, colors
|
|
OFFER_CONFIGS = {
|
|
# DE
|
|
101116: {
|
|
'brand': 'Techniker Krankenkasse', 'country': 'DE', 'lang': 'de',
|
|
'title': 'TK Gesundheitspaket',
|
|
'headline': 'Ihr persönliches Gesundheitspaket wartet!',
|
|
'body': 'Sie wurden ausgewählt, um ein exklusives Gesundheitspaket der Techniker Krankenkasse zu erhalten. Beantworten Sie ein paar kurze Fragen zu Ihren Gesundheitsbedürfnissen.',
|
|
'cta': 'Jetzt sichern!', 'color': '#0066CC',
|
|
'subjects': ['Ihr TK Gesundheitspaket wartet','Exklusiv: Kostenloses Gesundheitspaket','TK: Ihr persönliches Angebot','Wichtig: Ihr Gesundheitspaket','Jetzt sichern: TK Gesundheitsangebot'],
|
|
'from_names': ['TK Gesundheitsservice','Techniker Krankenkasse','TK Mitgliederservice','Gesundheit DE','TK Kundenservice']
|
|
},
|
|
# FR
|
|
100037: {
|
|
'brand': 'SHEIN', 'country': 'FR', 'lang': 'fr',
|
|
'title': 'SHEIN Mystery Box',
|
|
'headline': 'Votre Mystery Box SHEIN vous attend!',
|
|
'body': 'Félicitations! Vous avez été sélectionné(e) pour recevoir une Mystery Box SHEIN exclusive. Répondez à quelques questions pour la réclamer.',
|
|
'cta': 'Réclamer maintenant!', 'color': '#E74C3C',
|
|
'subjects': ['Votre Mystery Box SHEIN','Exclusif: Box SHEIN gratuite','SHEIN: Votre cadeau attend','Surprise SHEIN pour vous','Dernière chance: Mystery Box'],
|
|
'from_names': ['SHEIN France','SHEIN Rewards','Box SHEIN','SHEIN Exclusif','Mode SHEIN']
|
|
},
|
|
100733: {
|
|
'brand': 'Leroy Merlin', 'country': 'FR', 'lang': 'fr',
|
|
'title': 'Leroy Merlin Dexter',
|
|
'headline': 'Votre coffret Dexter offert par Leroy Merlin!',
|
|
'body': 'Vous avez été sélectionné pour recevoir un coffret outils Dexter. Répondez à notre enquête sur votre expérience Leroy Merlin.',
|
|
'cta': 'Obtenir mon coffret!', 'color': '#78BE20',
|
|
'subjects': ['Votre coffret Dexter offert','Leroy Merlin: Cadeau exclusif','Coffret outils gratuit','Leroy Merlin vous remercie','Enquête: Gagnez un coffret Dexter'],
|
|
'from_names': ['Leroy Merlin','Leroy Merlin France','Bricolage Maison','Leroy Merlin Rewards','Service Client LM']
|
|
},
|
|
101055: {
|
|
'brand': 'Vinci Autoroutes', 'country': 'FR', 'lang': 'fr',
|
|
'title': 'Kit Urgence Auto Vinci',
|
|
'headline': 'Votre kit de sécurité automobile gratuit!',
|
|
'body': "Vinci Autoroutes vous offre un kit d'urgence automobile complet. Répondez à quelques questions sur vos habitudes de conduite.",
|
|
'cta': 'Réclamer mon kit!', 'color': '#1A237E',
|
|
'subjects': ["Kit urgence auto gratuit","Vinci: Votre kit sécurité","Offre spéciale automobilistes","Kit d'urgence offert","Vinci Autoroutes: Cadeau exclusif"],
|
|
'from_names': ['Vinci Autoroutes','Sécurité Auto FR','Vinci Service','Auto Sécurité','Vinci Récompenses']
|
|
},
|
|
101935: {
|
|
'brand': 'Nocibé / Lancôme', 'country': 'FR', 'lang': 'fr',
|
|
'title': 'Coffret Beauté Lancôme',
|
|
'headline': 'Votre coffret beauté Lancôme vous attend!',
|
|
'body': "Nocibé vous offre un coffret beauté Lancôme exclusif. Répondez à quelques questions sur vos préférences beauté.",
|
|
'cta': 'Obtenir mon coffret!', 'color': '#8E24AA',
|
|
'subjects': ['Coffret Lancôme offert','Nocibé: Votre cadeau beauté','Exclusif: Coffret Lancôme gratuit','Beauté: Votre coffret attend','Nocibé vous gâte'],
|
|
'from_names': ['Nocibé Beauté','Lancôme Paris','Nocibé Rewards','Beauté Exclusive','Nocibé Service']
|
|
},
|
|
# UK
|
|
100876: {
|
|
'brand': 'ALDI', 'country': 'UK', 'lang': 'en',
|
|
'title': 'ALDI Gift Card',
|
|
'headline': 'Your £200 ALDI Gift Card is waiting!',
|
|
'body': 'Congratulations! You have been selected to receive a £200 ALDI gift card. Complete a quick quiz about your shopping habits to claim your reward.',
|
|
'cta': 'Claim Now!', 'color': '#00457C',
|
|
'subjects': ['Your ALDI Gift Card awaits','Exclusive: £200 ALDI reward','ALDI: Claim your gift card','Quick quiz = £200 reward','Your ALDI reward is ready'],
|
|
'from_names': ['ALDI Rewards','ALDI UK','Shopping Rewards UK','ALDI Gift Cards','ALDI Customer Service']
|
|
},
|
|
101860: {
|
|
'brand': 'Aviva', 'country': 'UK', 'lang': 'en',
|
|
'title': 'Aviva Fire Safety Kit',
|
|
'headline': 'Your free fire safety kit from Aviva!',
|
|
'body': 'Aviva is offering you a complimentary fire safety kit for your home. Answer a few questions about your home safety needs.',
|
|
'cta': 'Get Your Kit!', 'color': '#FFCC00',
|
|
'subjects': ['Free fire safety kit','Aviva: Your safety kit awaits','Home safety kit - free','Aviva fire safety offer','Protect your home - free kit'],
|
|
'from_names': ['Aviva UK','Aviva Home Safety','Home Protection UK','Aviva Customer Care','Safety First UK']
|
|
},
|
|
# IT
|
|
100564: {
|
|
'brand': 'Lidl / Parkside', 'country': 'IT', 'lang': 'it',
|
|
'title': 'Set Utensili Parkside',
|
|
'headline': 'Il tuo set utensili Parkside gratuito!',
|
|
'body': 'Sei stato selezionato per ricevere un set di utensili Parkside 3 pezzi da Lidl. Rispondi a poche domande sulla tua esperienza.',
|
|
'cta': 'Ottieni ora!', 'color': '#0050AA',
|
|
'subjects': ['Set Parkside gratuito','Lidl: Il tuo regalo','Set utensili omaggio','Lidl ti premia','Offerta esclusiva Parkside'],
|
|
'from_names': ['Lidl Italia','Parkside Italia','Lidl Premi','Lidl Servizio Clienti','Offerte Lidl']
|
|
},
|
|
101032: {
|
|
'brand': 'Telepass', 'country': 'IT', 'lang': 'it',
|
|
'title': 'Kit Emergenza Auto Telepass',
|
|
'headline': 'Il tuo kit emergenza auto gratuito!',
|
|
'body': "Telepass ti offre un kit di emergenza auto completo. Rispondi a poche domande sulle tue abitudini di guida.",
|
|
'cta': 'Richiedi il kit!', 'color': '#0033A0',
|
|
'subjects': ['Kit emergenza auto gratis','Telepass: Il tuo kit sicurezza','Offerta automobilisti','Kit emergenza omaggio','Telepass ti premia'],
|
|
'from_names': ['Telepass','Sicurezza Auto IT','Telepass Service','Auto Sicurezza','Telepass Premi']
|
|
},
|
|
# ES
|
|
100392: {
|
|
'brand': 'Lidl / Silvercrest', 'country': 'ES', 'lang': 'es',
|
|
'title': 'Aspiradora Silvercrest',
|
|
'headline': '¡Tu aspiradora Silvercrest gratis!',
|
|
'body': 'Has sido seleccionado para recibir una aspiradora Silvercrest de Lidl. Responde a unas breves preguntas sobre tu experiencia.',
|
|
'cta': '¡Consíguela ahora!', 'color': '#0050AA',
|
|
'subjects': ['Aspiradora Silvercrest gratis','Lidl: Tu regalo exclusivo','Oferta especial Silvercrest','Lidl te premia','Tu aspiradora te espera'],
|
|
'from_names': ['Lidl España','Silvercrest ES','Lidl Premios','Lidl Atención','Ofertas Lidl']
|
|
},
|
|
100046: {
|
|
'brand': 'SHEIN', 'country': 'ES', 'lang': 'es',
|
|
'title': 'SHEIN Mystery Box',
|
|
'headline': '¡Tu Mystery Box SHEIN te espera!',
|
|
'body': '¡Enhorabuena! Has sido seleccionado para recibir una Mystery Box SHEIN exclusiva. Responde unas preguntas rápidas.',
|
|
'cta': '¡Reclama ahora!', 'color': '#E74C3C',
|
|
'subjects': ['Tu Mystery Box SHEIN','Exclusivo: Box SHEIN gratis','SHEIN: Tu regalo espera','Sorpresa SHEIN para ti','Última oportunidad: Mystery Box'],
|
|
'from_names': ['SHEIN España','SHEIN Rewards','Box SHEIN','SHEIN Exclusivo','Moda SHEIN']
|
|
},
|
|
# AU
|
|
100538: {
|
|
'brand': 'JB Hi-Fi / Bose', 'country': 'AU', 'lang': 'en',
|
|
'title': 'Bose QuietComfort',
|
|
'headline': 'Your Bose QuietComfort headphones are waiting!',
|
|
'body': 'You have been selected to receive Bose QuietComfort headphones from JB Hi-Fi. Complete a quick survey about your audio preferences.',
|
|
'cta': 'Claim Yours!', 'color': '#E31937',
|
|
'subjects': ['Bose headphones waiting for you','JB Hi-Fi: Your exclusive reward','Free Bose QuietComfort','Audio reward from JB Hi-Fi','Claim your Bose headphones'],
|
|
'from_names': ['JB Hi-Fi','JB Hi-Fi Rewards','Audio Rewards AU','JB Hi-Fi Customer','JB Exclusive']
|
|
},
|
|
# CA
|
|
101761: {
|
|
'brand': 'Tim Hortons', 'country': 'CA', 'lang': 'en',
|
|
'title': 'Yeti Tumbler Tim Hortons',
|
|
'headline': 'Your free Yeti Tumbler from Tim Hortons!',
|
|
'body': "Tim Hortons is offering you a complimentary Yeti 42oz Tumbler. Answer a quick survey about your coffee preferences.",
|
|
'cta': 'Get Your Tumbler!', 'color': '#C8102E',
|
|
'subjects': ['Free Yeti Tumbler','Tim Hortons: Your gift awaits','Exclusive Yeti offer','Tim Hortons rewards you','Claim your Yeti Tumbler'],
|
|
'from_names': ['Tim Hortons','Tim Hortons Rewards','Coffee Rewards CA','Tim Hortons Gift','Tims Exclusive']
|
|
},
|
|
# US
|
|
101779: {
|
|
'brand': 'AAA', 'country': 'US', 'lang': 'en',
|
|
'title': 'AAA Car Emergency Kit',
|
|
'headline': 'Your free car emergency kit from AAA!',
|
|
'body': 'AAA is offering you a complimentary car emergency kit. Answer a few questions about your roadside safety habits.',
|
|
'cta': 'Get Your Kit!', 'color': '#003399',
|
|
'subjects': ['Free AAA emergency kit','AAA: Your safety kit awaits','Car emergency kit - free','Roadside safety offer','AAA rewards members'],
|
|
'from_names': ['AAA Roadside','AAA Auto Club','AAA Safety','Auto Emergency US','AAA Member Services']
|
|
},
|
|
101859: {
|
|
'brand': 'Walmart / Blackstone', 'country': 'US', 'lang': 'en',
|
|
'title': 'Blackstone Griddle',
|
|
'headline': 'Your Blackstone Outdoor Griddle awaits!',
|
|
'body': 'You have been selected to receive a Blackstone Outdoor Griddle from Walmart. Complete a short survey about your grilling preferences.',
|
|
'cta': 'Claim Now!', 'color': '#0071CE',
|
|
'subjects': ['Free Blackstone Griddle','Walmart: Your exclusive reward','Outdoor griddle giveaway','Walmart rewards you','Claim your Blackstone'],
|
|
'from_names': ['Walmart Rewards','Walmart US','Grill Rewards','Walmart Exclusive','Walmart Customer']
|
|
},
|
|
101920: {
|
|
'brand': 'Sephora / Lancôme', 'country': 'US', 'lang': 'en',
|
|
'title': 'Lancôme Beauty Set',
|
|
'headline': 'Your exclusive Lancôme beauty set is waiting!',
|
|
'body': 'Sephora is offering you a complimentary Lancôme beauty set. Answer a quick survey about your beauty preferences.',
|
|
'cta': 'Get Your Set!', 'color': '#8E24AA',
|
|
'subjects': ['Free Lancôme beauty set','Sephora: Your beauty gift','Exclusive Lancôme offer','Beauty set from Sephora','Claim your Lancôme set'],
|
|
'from_names': ['Sephora Beauty','Sephora Rewards','Lancôme US','Beauty Rewards','Sephora Exclusive']
|
|
},
|
|
}
|
|
|
|
def build_creative_html(cfg):
|
|
"""Generate email HTML from config using ADAC-style template"""
|
|
color = cfg['color']
|
|
return f'''<!DOCTYPE html>
|
|
<html>
|
|
<head>
|
|
<meta name="viewport" content="width=device-width" />
|
|
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
|
|
<title>{cfg['title']}</title>
|
|
<style>
|
|
img {{ border: none; max-width: 100%; }}
|
|
body {{ font-family: Arial, sans-serif; font-size: 14px; line-height: 1.5; margin: 0; padding: 0; background-color: #f4f4f4; }}
|
|
.container {{ max-width: 580px; margin: 0 auto; padding: 20px; }}
|
|
.content {{ background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }}
|
|
.header {{ background-color: {color}; padding: 25px 20px; text-align: center; }}
|
|
.header h1 {{ color: #ffffff; font-size: 22px; margin: 0; font-weight: bold; }}
|
|
.body-content {{ padding: 25px 30px; }}
|
|
.body-content h2 {{ color: #333; font-size: 20px; margin: 0 0 15px; font-weight: bold; }}
|
|
.body-content p {{ color: #555; font-size: 15px; margin: 0 0 20px; }}
|
|
.cta-btn {{ display: block; text-align: center; margin: 25px auto; }}
|
|
.cta-btn a {{ background-color: {color}; color: #ffffff; padding: 14px 40px; text-decoration: none; border-radius: 6px; font-size: 18px; font-weight: bold; display: inline-block; }}
|
|
.footer {{ text-align: center; padding: 15px; font-size: 11px; color: #999; }}
|
|
.footer a {{ color: #888; text-decoration: underline; }}
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<table width="100%" cellpadding="0" cellspacing="0">
|
|
<tr><td align="center">
|
|
<div class="container">
|
|
<div class="content">
|
|
<div class="header">
|
|
<h1>{cfg['brand']}</h1>
|
|
</div>
|
|
<div class="body-content">
|
|
<h2>{cfg['headline']}</h2>
|
|
<p>{cfg['body']}</p>
|
|
<div class="cta-btn">
|
|
<a href="[url]">{cfg['cta']}</a>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="footer">
|
|
<a href="[unsub]">Unsubscribe</a>
|
|
</div>
|
|
</div>
|
|
</td></tr>
|
|
</table>
|
|
<img src="[open]" width="1" height="1" style="display:none;" alt="" />
|
|
</body>
|
|
</html>'''
|
|
|
|
|
|
def main():
|
|
conn = psycopg2.connect(**DB)
|
|
cur = conn.cursor()
|
|
|
|
total_cr = 0
|
|
total_subj = 0
|
|
total_fn = 0
|
|
|
|
for noid, cfg in OFFER_CONFIGS.items():
|
|
# Find DB offer_id by production_id
|
|
cur.execute("SELECT id FROM affiliate.offers WHERE production_id = %s", (str(noid),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
print(f" SKIP #{noid} {cfg['brand']}: not in DB")
|
|
continue
|
|
offer_id = row[0]
|
|
|
|
# Check existing creatives
|
|
cur.execute("SELECT COUNT(*) FROM affiliate.creatives WHERE offer_id = %s", (offer_id,))
|
|
existing = cur.fetchone()[0]
|
|
if existing > 0:
|
|
print(f" SKIP #{offer_id} {cfg['brand']}: already has {existing} creatives")
|
|
continue
|
|
|
|
# Generate 3 creative variants
|
|
html = build_creative_html(cfg)
|
|
html_b64 = base64.b64encode(html.encode()).decode()
|
|
|
|
cur.execute("SELECT COALESCE(MAX(id),0) FROM affiliate.creatives")
|
|
max_id = cur.fetchone()[0]
|
|
|
|
for i in range(3):
|
|
cid = max_id + 1 + i
|
|
cur.execute(
|
|
"INSERT INTO affiliate.creatives (id, status, affiliate_network_id, offer_id, name, value, created_by, created_date, quality_score, has_tracking_placeholders, last_validated) "
|
|
"VALUES (%s, 'Activated', 6, %s, %s, %s, 'ai_generator', CURRENT_DATE, 10, true, NOW())",
|
|
(cid, offer_id, f"{cfg['brand']}_v{i+1}", html_b64))
|
|
max_id = cid
|
|
total_cr += 1
|
|
|
|
# Add subjects
|
|
cur.execute("SELECT COALESCE(MAX(id),0) FROM affiliate.subjects")
|
|
max_sid = cur.fetchone()[0]
|
|
for j, subj in enumerate(cfg['subjects']):
|
|
sid = max_sid + 1 + j
|
|
cur.execute(
|
|
"INSERT INTO affiliate.subjects (id, status, affiliate_network_id, offer_id, name, value, created_by, created_date) "
|
|
"VALUES (%s, 'Activated', 6, %s, %s, %s, 'ai_generator', CURRENT_DATE)",
|
|
(sid, offer_id, f"{cfg['brand']}_s{j+1}", subj))
|
|
max_sid = sid
|
|
total_subj += 1
|
|
|
|
# Add from_names
|
|
cur.execute("SELECT COALESCE(MAX(id),0) FROM affiliate.from_names")
|
|
max_fid = cur.fetchone()[0]
|
|
for k, fn in enumerate(cfg['from_names']):
|
|
fid = max_fid + 1 + k
|
|
cur.execute(
|
|
"INSERT INTO affiliate.from_names (id, status, affiliate_network_id, offer_id, name, value, created_by, created_date) "
|
|
"VALUES (%s, 'Activated', 6, %s, %s, %s, 'ai_generator', CURRENT_DATE)",
|
|
(fid, offer_id, f"{cfg['brand']}_fn{k+1}", fn))
|
|
max_fid = fid
|
|
total_fn += 1
|
|
|
|
# Update brain_offer_config
|
|
cur.execute(
|
|
"UPDATE admin.brain_offer_config SET good_creatives = 3, is_approved = true WHERE offer_id = %s",
|
|
(offer_id,))
|
|
|
|
print(f" OK #{offer_id} [{cfg['country']}] {cfg['brand']}: 3 creatives, {len(cfg['subjects'])} subjects, {len(cfg['from_names'])} from_names")
|
|
|
|
conn.commit()
|
|
print(f"\nTOTAL: {total_cr} creatives, {total_subj} subjects, {total_fn} from_names generated")
|
|
|
|
# Show final state
|
|
cur.execute("SELECT COUNT(*) FROM admin.brain_offer_config WHERE is_approved = true AND good_creatives > 0")
|
|
approved = cur.fetchone()[0]
|
|
print(f"Approved offers ready to send: {approved}")
|
|
|
|
cur.close(); conn.close()
|
|
|
|
if __name__ == '__main__':
|
|
main()
|
|
|