#!/usr/bin/env python3 """ Clay API Setup Script - PHT South Africa Outreach Automates Clay table creation and enrichment setup. Run after importing Google Sheet manually in Clay UI. """ import requests import json import sys # API Keys CLAY_API_KEY = "f5f46266f50887c25f53" APOLLO_API_KEY = "xss1Wu8lX4jTxHjEZdTJyg" HUNTER_API_KEY = "ad94c47afbcd3c27f46fe04480d32f72fd72bbdf" CLAY_BASE_URL = "https://api.clay.com/v1" headers = { "Authorization": f"Bearer {CLAY_API_KEY}", "Content-Type": "application/json" } def create_table(name, google_sheet_id): """Create new Clay table from Google Sheet""" payload = { "name": name, "source": { "type": "google_sheets", "spreadsheet_id": google_sheet_id } } response = requests.post( f"{CLAY_BASE_URL}/tables", headers=headers, json=payload ) if response.status_code == 201: table = response.json() print(f"✓ Table created: {table['id']}") return table['id'] else: print(f"✗ Failed to create table: {response.text}") return None def add_enrichment(table_id, enrichment_config): """Add enrichment column to table""" response = requests.post( f"{CLAY_BASE_URL}/tables/{table_id}/columns", headers=headers, json=enrichment_config ) if response.status_code == 201: column = response.json() print(f"✓ Added column: {column['name']}") return column['id'] else: print(f"✗ Failed to add column: {response.text}") return None def setup_enrichments(table_id): """Configure all enrichment columns""" enrichments = [ # 1. Cold Room Count (GPT Web Scrape) { "name": "Cold Room Count", "type": "ai_scrape", "config": { "provider": "openai", "model": "gpt-4", "input_field": "Company Website", "prompt": """Visit this website and find information about their cold storage facilities. Look for: - Number of cold rooms - Number of CA (controlled atmosphere) rooms - Total storage capacity in rooms Return ONLY a number if found (e.g., "50" if they have 50 rooms). If no specific number is mentioned, return "Unknown". If the website doesn't load, return "Error".""" } }, # 2. Fruit Type (GPT Web Scrape) { "name": "Fruit Type", "type": "ai_scrape", "config": { "provider": "openai", "model": "gpt-4", "input_field": "Company Website", "prompt": """Visit this website and identify what types of fruit they store, pack, or export. Return ONLY the fruit types as a comma-separated list. Focus on: apples, citrus, pears, grapes, stone fruit, kiwis, avocados. Example outputs: - "apples, pears" - "citrus" - "grapes, stone fruit" If unclear, return "Unknown".""" } }, # 3. Qualified? (Formula) { "name": "Qualified", "type": "formula", "config": { "formula": """ if ( ({{Cold Room Count}} >= 10 || {{Cold Room Count}} === "Unknown") && ({{Fruit Type}}.includes("apples") || {{Fruit Type}}.includes("citrus") || {{Fruit Type}}.includes("pears")) ) { return "YES"; } else { return "NO"; } """ } }, # 4. Apollo People Search { "name": "Contact", "type": "apollo_people", "config": { "api_key": APOLLO_API_KEY, "company_name": "{{Company Name}}", "job_titles": [ "Quality Manager", "Operations Manager", "General Manager", "Cold Storage Manager", "Technical Manager" ], "seniority": ["manager", "director", "vp"], "max_results": 3 } }, # 5. Email Validation { "name": "Email Valid", "type": "email_validation", "config": { "email": "{{Contact Email}}" } }, # 6. Website Copy Scrape { "name": "Website Copy", "type": "http_scrape", "config": { "url": "{{Company Website}}/about", "extract": "text", "max_chars": 1000 } }, # 7. Pain Points (GPT Analysis) { "name": "Pain Points", "type": "ai_completion", "config": { "provider": "openai", "model": "gpt-4", "prompt": """You are analyzing a fruit storage company's website. Website copy: {{Website Copy}} Fruits they handle: {{Fruit Type}} Location: South Africa Task: Identify 1-2 specific pain points this company likely faces related to: - Post-harvest quality control - Export compliance (especially to EU) - Ethylene management - Multi-variety storage challenges - Ripening consistency Be specific and practical. Reference details from their website if mentioned. Format: 2 bullet points max. Example: - Exports to EU require strict ethylene monitoring for citrus - Managing ripening across 50+ rooms creates consistency issues""" } }, # 8. Email Subject (GPT) { "name": "Email Subject", "type": "ai_completion", "config": { "provider": "openai", "model": "gpt-4", "prompt": """Write 1 email subject line for {{Contact Name}} at {{Company Name}}. Context: - They store {{Fruit Type}} in South Africa - Pain points: {{Pain Points}} - We help companies like Zespri (NZ) and Costa (AU) monitor ethylene Rules: - 4-7 words maximum - Curious and helpful, NOT salesy - NO "Quick question" or "Touching base" - Reference their specific situation Examples: - "Export rejections at [Company]?" - "How Zespri cut citrus waste 40%" - "Ethylene spikes in CA storage?" Return ONLY the subject line, no quotes.""" } }, # 9. Email Body (GPT) { "name": "Email Body", "type": "ai_completion", "config": { "provider": "openai", "model": "gpt-4", "prompt": """Write a personalized cold email from Jonny Shannon to {{Contact Name}}, {{Contact Title}} at {{Company Name}}. Context: - They store {{Fruit Type}} in South Africa - Likely challenges: {{Pain Points}} - We help companies like Zespri (NZ kiwis), Costa Group (AU apples), and Wonderful Citrus (USA) monitor ethylene in real-time - Our product: Atmos - wireless ethylene sensors for cold rooms - Benefit: Catch ripening issues before fruit ships, reduce rejections Email structure: 1. Personal observation about their business (1 sentence) 2. Mention relevant challenge they likely face (1 sentence) 3. How we help similar company (1 sentence, name-drop Zespri or Costa) 4. Soft CTA: offer to send quick video walkthrough Tone: - Helpful and genuine, not salesy - Brief (50-70 words total) - NO hype words: "revolutionize", "game-changer", "cutting-edge" - Conversational, like you're messaging a colleague End with: "I recorded a quick video showing how [similar company] uses it. Want me to send it over?" Sign off: "Jonny" (no last name, keep casual) Return ONLY the email body text.""" } }, # 10. LinkedIn Message (GPT) { "name": "LinkedIn Message", "type": "ai_completion", "config": { "provider": "openai", "model": "gpt-4", "prompt": """Write a casual LinkedIn connection request message to {{Contact Name}}. Context: - They work at {{Company Name}} as {{Contact Title}} - Company stores {{Fruit Type}} in South Africa - We work with fruit exporters on post-harvest tech Rules: - Maximum 250 characters (LinkedIn limit) - Casual and friendly, NO pitch - Just want to connect Example: "Hey Sarah — saw you're managing quality at FreshPack. We work with a few citrus exporters in SA. Would be great to connect!" Return ONLY the message text, no quotes.""" } } ] for enrichment in enrichments: add_enrichment(table_id, enrichment) print(f" → {enrichment['name']} configured") def main(): print("=" * 60) print("CLAY TABLE SETUP - PHT SOUTH AFRICA") print("=" * 60) # South Africa Google Sheet google_sheet_id = "1km6raUNDLyru8VLrqLv69lS1sfA5jgMQJhN9c1dlXTQ" print("\n[1/2] Creating table from Google Sheet...") table_id = create_table("PHT South Africa Prospects", google_sheet_id) if not table_id: print("\n✗ Failed to create table. Check API key and try again.") sys.exit(1) print(f"\n[2/2] Setting up enrichments...") setup_enrichments(table_id) print("\n" + "=" * 60) print("✓ SETUP COMPLETE!") print("=" * 60) print(f"\nTable ID: {table_id}") print(f"View in Clay: https://app.clay.com/tables/{table_id}") print("\nNext steps:") print("1. Go to Clay and verify table imported correctly") print("2. Run enrichments on 5 test rows") print("3. Review output quality") print("4. Run on all qualified rows") print("5. Set up daily automation (6am NZDT)") if __name__ == "__main__": main()