I created an automation that generates full email broadcasts directly from a Google Sheet. By entering a newsletter topic, this system utilizes Perplexity’s deep research and Claude 3.7 to create compelling content and format it for any email service provider that supports HTML.
This automation allows you to generate entire email broadcasts from a simple Google Sheet. By entering a newsletter topic, the system will pull relevant information from Perplexity’s deep research and use Claude 3.7 to create engaging content. The finalized newsletter can be formatted for any email service provider that supports HTML, making it versatile across various platforms.
Key Features
- Easy integration with Google Sheets for topic management.
- Utilizes Perplexity for in-depth research.
- Generates compelling text using Claude 3.7.
- Compatible with any email provider that supports HTML.
Google Sheets Integration
Integrating Google Sheets into this automation is seamless. Start by setting up a Google Sheet with specific statuses like “Not Started,” “Ready to Generate,” and “Newsletter Generated.” This structure helps the automation identify which newsletters are ready for processing.
Once you’ve entered your newsletter topic, the automation will trigger based on the status set to “Ready to Generate.” From here, it will fetch the relevant data, making the process efficient and straightforward.
Setting Up the Google Sheet
- Create a new Google Sheet.
- Define the headers, including status and topic.
- Use data validation to create a dropdown for status selection.
- Input your newsletter topics into the designated column.
Perplexity Deep Research
Perplexity serves as a powerful tool for gathering insights and data. By connecting to Perplexity’s API, the automation can retrieve up-to-date information relevant to the newsletter topic.
The process begins with creating a chart completion request. You’ll set up a system prompt that instructs Perplexity to summarize facts and figures without introductory text. This focus ensures that the output is directly usable for the email content.
Creating a Deep Research Request
- Set up a connection with the Perplexity API.
- Define the system prompt to guide the type of information you want.
- Specify the newsletter topic for targeted research.
- Run the request to gather insights.
Text Parser
After receiving data from Perplexity, a text parser is essential for cleaning up the output. The parser will strip away any unnecessary tags or introductory phrases that may clutter the final content. This ensures that the information is ready for Claude to format into engaging text.
Implementing the Text Parser
- Set up a text parser module in your automation.
- Define the pattern to remove unwanted text.
- Connect the output from Perplexity to the parser.
- Save and run the parser to see the cleaned output.
Claude 3.7 Sonnet Integration
Claude 3.7 is utilized to transform the cleaned research data into engaging newsletter content. By crafting a well-defined prompt, I can instruct Claude to write in a friendly and approachable tone, making the email more relatable to readers.
The integration involves creating a prompt that specifies the desired structure and tone. This allows Claude to generate text that includes hooks, actionable steps, and a friendly sign-off.
Creating the Claude Prompt
- Establish a connection with the Claude API.
- Define the prompt with specific instructions for content creation.
- Incorporate the cleaned research data as input.
- Run the prompt to generate the newsletter text.
ConvertKit Integration via HTTP
To send the generated newsletter to ConvertKit, I use an HTTP request method. This allows for direct communication with the ConvertKit API, ensuring that the email is sent in the correct format and structure.
After creating an email template in ConvertKit, the next step is to retrieve the template ID and set up the HTTP request with the necessary parameters. This includes the email content, subject line, and any additional metadata required by ConvertKit.
Configuring the HTTP Request
- Retrieve the API key from ConvertKit settings.
- Set up the HTTP request with the appropriate URL and method.
- Include the email template ID and content in the request body.
- Run the request to send the email to ConvertKit.
Email Template Setup
Setting up the email template in ConvertKit is essential for ensuring that the content generated by the automation looks polished and professional. I created a simple email template that serves as the foundation for each broadcast. This template can be customized later, but starting with a clean, clear layout helps maintain consistency.
To set up the template, I navigated to the “Email Templates” section in ConvertKit and clicked on “New Email Template.” I then defined the structure, including placeholders for the subject line, body, and any additional elements like images or call-to-action buttons.
Defining the Template Structure
- Choose a layout that suits your branding.
- Add placeholders for dynamic content like the subject line and body text.
- Incorporate design elements that align with your audience’s preferences.
Sending Email Content
Once the template is ready, I connect the generated content from Claude directly into the template. This step ensures that the email not only contains the right information but is also formatted correctly for delivery.
I use an HTTP request to send the email content to ConvertKit. The request includes the email template ID, the generated HTML body from Claude, and any other necessary metadata like the subject line and preview text.
Setting Up the HTTP Request
- Specify the method as POST.
- Include the API key in the headers for authentication.
- In the body, include the email template ID and the generated content.
HTML Cleaning
To ensure that the email displays correctly, I implemented an HTML cleaning process. This step removes any unwanted characters or tags that may have been inadvertently included in the content generated by Claude.
Using a set variable node, I created a series of replace statements to clean the HTML output. This preprocessing ensures that the content adheres to the expected format and avoids any rendering issues in email clients.
Implementing HTML Cleaning
- Add a set variable module to define the cleaning process.
- Utilize replace statements to remove unwanted characters.
- Pass the cleaned HTML to the HTTP request for sending.
Generating Email Title, Description, and Preview Text
The next step involves crafting the email title, description, and preview text. These elements play a crucial role in increasing open rates and engaging the audience. I created another prompt for Claude to generate these components based on the newsletter content.
By providing Claude with the generated email body, I can instruct it to produce a compelling subject line, a brief description, and a preview text that captures the essence of the email. This ensures that the email stands out in crowded inboxes.
Setting Up the Claude Prompt
- Define the structure of the JSON object to include title, description, and preview text.
- Provide context from the generated email content for targeted responses.
- Utilize response prefilling to ensure Claude begins with the correct format.
Updating Google Sheet Status
After successfully sending the email to ConvertKit, it’s important to update the status in the Google Sheet. This step prevents the automation from reprocessing the same entry and keeps track of which newsletters have been sent out.
I set up a Google Sheets module to update the status column to “Newsletter Generated.” This action helps maintain an organized workflow and allows me to easily monitor the progress of each newsletter.
Configuring the Status Update
- Map the spreadsheet ID from the initial Google Sheets module.
- Select the appropriate sheet and row number for the update.
- Change the status to “Newsletter Generated” to indicate completion.
Auto-Publishing in ConvertKit
For those who want to streamline the process even further, I set up an option for auto-publishing the email broadcast in ConvertKit. This feature allows the emails to be sent out automatically at a specified time, ensuring timely delivery without manual intervention.
To implement this, I added a “send at” field in the HTTP request to ConvertKit. By passing in an ISO date format, I can control when the email is published, making it easy to schedule newsletters in advance.
Configuring Auto-Publishing
- Include the “send at” field in the HTTP request body.
- Pass the formatted date for publication timing.
- Decide if the email should be public or private based on your strategy.
