I been struggling to pull all email just from one segment because if contact is in that segment “56” and also in other different segments then it’ll pull those emails which is driving me crazy. I only want email that was in this one specific segment . Spent most of the day trying to figure out how to filter out so I gave up on this and put it on hold.
Instead, I went back to all my email and added a category to place all those email into and filter it out by using category which seem to be the only way at the moment. So far it worked for me … If anyone know how to filter email using specific segment please do let me know.
Here the python script to output email state into cvs file based a category.
import mysql.connector
import csv
from datetime import datetime
# Database connection details
db_config = {
'user': 'your_username',
'password': 'your_password',
'host': 'your_host',
'database': 'your_database'
}
# Category title to filter by
category_title = 'your_category_title'
# Output CSV file
csv_filename = f"formatted_email_stats_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
try:
# Connect to the database
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor(dictionary=True)
# Step 1: Identify the category_id for the specified category
query_category_id = """
SELECT id FROM categories WHERE title = %s
"""
cursor.execute(query_category_id, (category_title,))
category = cursor.fetchone()
if not category:
print(f"No category found with the title '{category_title}'.")
else:
category_id = category['id']
# Step 2: Identify all emails in the specified category
query_emails = """
SELECT e.id AS email_id, e.subject, DATE_FORMAT(e.date_added, '%m-%d-%Y') AS date_added
FROM emails e
WHERE e.category_id = %s
"""
cursor.execute(query_emails, (category_id,))
emails = cursor.fetchall()
# Check if emails were found
if not emails:
print(f"No emails found in the category '{category_title}'.")
else:
# Step 3: Open CSV file for writing
with open(csv_filename, mode='w', newline='') as file:
writer = csv.writer(file)
writer.writerow(['Email ID', 'Date Added', 'Subject', 'Sent', 'Read', 'Clicks', 'Unsubscribes', 'Bounces'])
for email in emails:
# Step 4: Retrieve stats for each email
query_stats = """
SELECT
COUNT(DISTINCT s.lead_id) AS sent,
SUM(s.is_read) AS isread,
(SELECT COUNT(ph.id) FROM page_hits ph WHERE ph.email_id = s.email_id) AS clicks,
IFNULL((SELECT COUNT(ldnc.lead_id) FROM lead_donotcontact ldnc
WHERE ldnc.channel = 'email' AND ldnc.reason = 1 AND ldnc.channel_id = s.email_id), 0) AS unsubscribes,
IFNULL((SELECT COUNT(ldnc.lead_id) FROM lead_donotcontact ldnc
WHERE ldnc.channel = 'email' AND ldnc.reason = 2 AND ldnc.channel_id = s.email_id), 0) AS bounces
FROM
email_stats s
WHERE
s.email_id = %s
"""
cursor.execute(query_stats, (email['email_id'],))
stats = cursor.fetchone()
# Write the results, including those without stats
if stats:
writer.writerow([
email['email_id'],
email['date_added'],
email['subject'],
stats['sent'],
stats['isread'],
stats['clicks'],
stats['unsubscribes'],
stats['bounces']
])
else:
# Write emails without any stats
writer.writerow([
email['email_id'],
email['date_added'],
email['subject'],
0, # Sent
0, # Read
0, # Clicks
0, # Unsubscribes
0 # Bounces
])
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
conn.close()
