1pip install pandas openpyxl jinja2 sqlite3

1first_name,last_name,company,role,industry,common_ground,pain_point,linkedin_url
2Sarah,Johnson,TechCorp,VP of Sales,SaaS,Posted about sales automation,Lead qualification taking too long,linkedin.com/in/sarahjohnson
3Michael,Chen,DataFlow,CTO,Data Analytics,Shared article on Python,Manual report generation,linkedin.com/in/michaelchen

1import pandas as pd
2from jinja2 import Template
3import sqlite3
4from datetime import datetime, timedelta
5import os
6
7class LinkedInOutreachManager:
8    def __init__(self, db_path='data/outreach.db'):
9        self.db_path = db_path
10        self.conn = sqlite3.connect(db_path)
11        self.setup_database()
12    
13    def setup_database(self):
14        """Create database tables for tracking"""
15        cursor = self.conn.cursor()
16        
17        cursor.execute('''
18            CREATE TABLE IF NOT EXISTS prospects (
19                id INTEGER PRIMARY KEY AUTOINCREMENT,
20                first_name TEXT,
21                last_name TEXT,
22                company TEXT,
23                role TEXT,
24                industry TEXT,
25                common_ground TEXT,
26                pain_point TEXT,
27                linkedin_url TEXT UNIQUE,
28                added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
29            )
30        ''')
31        
32        cursor.execute('''
33            CREATE TABLE IF NOT EXISTS outreach (
34                id INTEGER PRIMARY KEY AUTOINCREMENT,
35                prospect_id INTEGER,
36                message_type TEXT,
37                message_text TEXT,
38                sent_date TIMESTAMP,
39                status TEXT,
40                response_date TIMESTAMP,
41                notes TEXT,
42                FOREIGN KEY (prospect_id) REFERENCES prospects (id)
43            )
44        ''')
45        
46        self.conn.commit()
47    
48    def import_prospects(self, csv_path):
49        """Import prospects from CSV to database"""
50        df = pd.read_csv(csv_path)
51        df.to_sql('prospects', self.conn, if_exists='append', index=False)
52        print(f"Imported {len(df)} prospects")
53    
54    def generate_message(self, template_path, prospect_data):
55        """Generate personalized message from template"""
56        with open(template_path, 'r') as f:
57            template = Template(f.read())
58        
59        # Add additional computed fields
60        prospect_data['solution'] = self.get_solution_for_pain_point(
61            prospect_data.get('pain_point', '')
62        )
63        prospect_data['topic'] = self.extract_topic(
64            prospect_data.get('common_ground', '')
65        )
66        
67        return template.render(**prospect_data)
68    
69    def get_solution_for_pain_point(self, pain_point):
70        """Map pain points to solutions"""
71        solutions = {
72            'lead qualification': 'streamline their lead qualification process',
73            'manual report': 'automate their reporting workflows',
74            'data entry': 'eliminate manual data entry',
75            'follow-up': 'automate their follow-up sequences'
76        }
77        
78        for key, value in solutions.items():
79            if key in pain_point.lower():
80                return value
81        
82        return 'improve their team\'s efficiency'
83    
84    def extract_topic(self, common_ground):
85        """Extract topic from common ground"""
86        if 'automation' in common_ground.lower():
87            return 'workflow automation'
88        elif 'python' in common_ground.lower():
89            return 'Python automation strategies'
90        elif 'sales' in common_ground.lower():
91            return 'sales optimization'
92        else:
93            return 'productivity improvements'
94    
95    def generate_batch_messages(self, template_name, limit=None, status_filter=None):
96        """Generate messages for multiple prospects"""
97        query = "SELECT * FROM prospects"
98        
99        if status_filter:
100            query += f" WHERE id NOT IN (SELECT prospect_id FROM outreach WHERE status='{status_filter}')"
101        
102        if limit:
103            query += f" LIMIT {limit}"
104        
105        df = pd.read_sql_query(query, self.conn)
106        
107        messages = []
108        template_path = f"templates/{template_name}.txt"
109        
110        for _, prospect in df.iterrows():
111            try:
112                message = self.generate_message(template_path, prospect.to_dict())
113                messages.append({
114                    'prospect_id': prospect['id'],
115                    'name': f"{prospect['first_name']} {prospect['last_name']}",
116                    'company': prospect['company'],
117                    'linkedin_url': prospect['linkedin_url'],
118                    'message': message,
119                    'generated_date': datetime.now().strftime('%Y-%m-%d %H:%M:%S')
120                })
121            except Exception as e:
122                print(f"Error generating message for {prospect['first_name']}: {e}")
123        
124        return pd.DataFrame(messages)
125    
126    def save_messages_to_csv(self, messages_df, output_path):
127        """Save generated messages to CSV"""
128        messages_df.to_csv(output_path, index=False)
129        print(f"Saved {len(messages_df)} messages to {output_path}")
130    
131    def log_outreach(self, prospect_id, message_type, message_text, status='generated'):
132        """Log outreach attempt"""
133        cursor = self.conn.cursor()
134        cursor.execute('''
135            INSERT INTO outreach (prospect_id, message_type, message_text, status, sent_date)
136            VALUES (?, ?, ?, ?, ?)
137        ''', (prospect_id, message_type, message_text, status, datetime.now()))
138        self.conn.commit()
139    
140    def update_outreach_status(self, prospect_id, message_type, new_status, notes=None):
141        """Update status of outreach (e.g., sent, accepted, responded)"""
142        cursor = self.conn.cursor()
143        
144        if new_status == 'responded':
145            cursor.execute('''
146                UPDATE outreach 
147                SET status=?, response_date=?, notes=?
148                WHERE prospect_id=? AND message_type=?
149            ''', (new_status, datetime.now(), notes, prospect_id, message_type))
150        else:
151            cursor.execute('''
152                UPDATE outreach 
153                SET status=?, notes=?
154                WHERE prospect_id=? AND message_type=?
155            ''', (new_status, notes, prospect_id, message_type))
156        
157        self.conn.commit()
158    
159    def get_performance_report(self):
160        """Generate performance analytics"""
161        query = '''
162            SELECT 
163                message_type,
164                COUNT(*) as total_sent,
165                SUM(CASE WHEN status='accepted' THEN 1 ELSE 0 END) as accepted,
166                SUM(CASE WHEN status='responded' THEN 1 ELSE 0 END) as responded,
167                AVG(CASE WHEN response_date IS NOT NULL 
168                    THEN julianday(response_date) - julianday(sent_date) 
169                    ELSE NULL END) as avg_response_days
170            FROM outreach
171            WHERE status != 'generated'
172            GROUP BY message_type
173        '''
174        
175        return pd.read_sql_query(query, self.conn)
176    
177    def get_followup_candidates(self, days_since_connection=3):
178        """Find prospects who accepted but haven't received follow-up"""
179        query = f'''
180            SELECT p.*, o.sent_date as connection_date
181            FROM prospects p
182            JOIN outreach o ON p.id = o.prospect_id
183            WHERE o.message_type = 'connection_request'
184            AND o.status = 'accepted'
185            AND julianday('now') - julianday(o.sent_date) >= {days_since_connection}
186            AND p.id NOT IN (
187                SELECT prospect_id FROM outreach 
188                WHERE message_type = 'followup_1'
189            )
190        '''
191        
192        return pd.read_sql_query(query, self.conn)
193    
194    def close(self):
195        """Close database connection"""
196        self.conn.close()
197
198
199# Example usage
200if __name__ == "__main__":
201    manager = LinkedInOutreachManager()
202    
203    # Import prospects from CSV
204    manager.import_prospects('data/prospects.csv')
205    
206    # Generate connection requests for first 10 prospects
207    connection_messages = manager.generate_batch_messages(
208        template_name='connection_request',
209        limit=10
210    )
211    
212    # Save to CSV for review
213    manager.save_messages_to_csv(
214        connection_messages,
215        'outputs/connection_requests.csv'
216    )
217    
218    # After manually sending and tracking responses, update status
219    # manager.update_outreach_status(prospect_id=1, message_type='connection_request', new_status='accepted')
220    
221    # Generate follow-ups for prospects who accepted 3+ days ago
222    followup_candidates = manager.get_followup_candidates(days_since_connection=3)
223    print(f"\n{len(followup_candidates)} prospects ready for follow-up")
224    
225    # Get performance report
226    performance = manager.get_performance_report()
227    print("\nPerformance Report:")
228    print(performance)
229    
230    manager.close()

1import openai
2
3class AIPersonalizer:
4    def __init__(self, api_key):
5        openai.api_key = api_key
6    
7    def generate_custom_opener(self, prospect_data):
8        """Use AI to create highly personalized opening line"""
9        prompt = f"""
10        Create a personalized, natural opening line for a LinkedIn connection request.
11        
12        Prospect info:
13        - Name: {prospect_data['first_name']}
14        - Role: {prospect_data['role']} at {prospect_data['company']}
15        - Recent activity: {prospect_data['common_ground']}
16        - Their challenge: {prospect_data['pain_point']}
17        
18        Requirements:
19        - Be genuine and conversational (not salesy)
20        - Reference their recent activity specifically
21        - Keep it under 40 words
22        - Don't mention you're selling anything
23        
24        Opening line:
25        """
26        
27        response = openai.ChatCompletion.create(
28            model="gpt-4",
29            messages=[{"role": "user", "content": prompt}],
30            temperature=0.7,
31            max_tokens=100
32        )
33        
34        return response.choices[0].message.content.strip()

1def daily_outreach_workflow():
2    """Automated daily workflow"""
3    manager = LinkedInOutreachManager()
4    
5    print("=== LinkedIn Outreach Daily Workflow ===\n")
6    
7    # 1. Generate new connection requests (stay under 20/day)
8    new_messages = manager.generate_batch_messages(
9        template_name='connection_request',
10        limit=15,  # Conservative daily limit
11        status_filter='sent'  # Exclude already contacted
12    )
13    
14    print(f"✓ Generated {len(new_messages)} new connection requests")
15    manager.save_messages_to_csv(new_messages, f'outputs/daily_{datetime.now().strftime("%Y%m%d")}.csv')
16    
17    # 2. Identify follow-up opportunities
18    followups = manager.get_followup_candidates(days_since_connection=3)
19    print(f"✓ {len(followups)} prospects ready for follow-up")
20    
21    if len(followups) > 0:
22        followup_messages = manager.generate_batch_messages(
23            template_name='followup_1',
24            limit=10
25        )
26        manager.save_messages_to_csv(followup_messages, f'outputs/followups_{datetime.now().strftime("%Y%m%d")}.csv')
27    
28    # 3. Generate performance report
29    performance = manager.get_performance_report()
30    print("\n=== Weekly Performance ===")
31    print(performance.to_string(index=False))
32    
33    # 4. Calculate metrics
34    if len(performance) > 0:
35        total_sent = performance['total_sent'].sum()
36        total_accepted = performance['accepted'].sum()
37        total_responded = performance['responded'].sum()
38        
39        if total_sent > 0:
40            acceptance_rate = (total_accepted / total_sent) * 100
41            response_rate = (total_responded / total_accepted) * 100 if total_accepted > 0 else 0
42            
43            print(f"\nAcceptance Rate: {acceptance_rate:.1f}%")
44            print(f"Response Rate: {response_rate:.1f}%")
45    
46    manager.close()
47    print("\n✓ Daily workflow complete!")
48
49# Run daily workflow
50daily_outreach_workflow()

1import matplotlib.pyplot as plt
2
3def create_performance_dashboard(manager):
4    """Generate visual analytics"""
5    
6    # Get data
7    query = '''
8        SELECT 
9            date(sent_date) as date,
10            COUNT(*) as messages_sent,
11            SUM(CASE WHEN status='accepted' THEN 1 ELSE 0 END) as accepted,
12            SUM(CASE WHEN status='responded' THEN 1 ELSE 0 END) as responded
13        FROM outreach
14        WHERE status != 'generated'
15        GROUP BY date(sent_date)
16        ORDER BY date
17    '''
18    
19    df = pd.read_sql_query(query, manager.conn)
20    df['date'] = pd.to_datetime(df['date'])
21    
22    # Create visualizations
23    fig, axes = plt.subplots(2, 2, figsize=(15, 10))
24    
25    # Messages sent over time
26    axes[0, 0].plot(df['date'], df['messages_sent'], marker='o')
27    axes[0, 0].set_title('Messages Sent Over Time')
28    axes[0, 0].set_xlabel('Date')
29    axes[0, 0].set_ylabel('Count')
30    
31    # Acceptance rate
32    df['acceptance_rate'] = (df['accepted'] / df['messages_sent'] * 100)
33    axes[0, 1].plot(df['date'], df['acceptance_rate'], marker='o', color='green')
34    axes[0, 1].set_title('Acceptance Rate (%)')
35    axes[0, 1].set_xlabel('Date')
36    axes[0, 1].set_ylabel('Percentage')
37    
38    # Response funnel
39    funnel_data = [df['messages_sent'].sum(), df['accepted'].sum(), df['responded'].sum()]
40    funnel_labels = ['Sent', 'Accepted', 'Responded']
41    axes[1, 0].bar(funnel_labels, funnel_data, color=['blue', 'green', 'orange'])
42    axes[1, 0].set_title('Outreach Funnel')
43    axes[1, 0].set_ylabel('Count')
44    
45    # Cumulative results
46    axes[1, 1].plot(df['date'], df['accepted'].cumsum(), label='Accepted', marker='o')
47    axes[1, 1].plot(df['date'], df['responded'].cumsum(), label='Responded', marker='o')
48    axes[1, 1].set_title('Cumulative Results')
49    axes[1, 1].set_xlabel('Date')
50    axes[1, 1].set_ylabel('Count')
51    axes[1, 1].legend()
52    
53    plt.tight_layout()
54    plt.savefig('outputs/performance_dashboard.png', dpi=300)
55    print("Dashboard saved to outputs/performance_dashboard.png")

1import requests
2
3def sync_to_crm(prospect_data):
4    """Add accepted connections to CRM"""
5    crm_api_url = "https://api.hubspot.com/contacts/v1/contact"
6    headers = {"Authorization": f"Bearer {CRM_API_KEY}"}
7    
8    payload = {
9        "properties": [
10            {"property": "firstname", "value": prospect_data['first_name']},
11            {"property": "lastname", "value": prospect_data['last_name']},
12            {"property": "company", "value": prospect_data['company']},
13            {"property": "lifecyclestage", "value": "lead"}
14        ]
15    }
16    
17    response = requests.post(crm_api_url, json=payload, headers=headers)
18    return response.status_code == 200

1def analyze_response(response_text):
2    """Use AI to categorize prospect responses"""
3    prompt = f"""
4    Categorize this LinkedIn message response:
5    
6    "{response_text}"
7    
8    Category (choose one):
9    - interested (wants to talk)
10    - not_interested (polite decline)
11    - more_info (asking questions)
12    - out_of_office (unavailable)
13    
14    Category:
15    """
16    
17    # Use OpenAI to categorize
18    # Return category for automated routing

1from datetime import datetime, timedelta
2
3def generate_meeting_times():
4    """Suggest 3 meeting times in prospect's timezone"""
5    base_time = datetime.now() + timedelta(days=2)
6    times = []
7    
8    for i in range(3):
9        meeting_time = base_time + timedelta(days=i)
10        meeting_time = meeting_time.replace(hour=10, minute=0)  # 10 AM
11        times.append(meeting_time.strftime("%A, %B %d at %I:%M %p"))
12    
13    return times