How I Automated the Creation of 200 Product Listings on Squarespace Using Python and GPT

When tasked with the tedious job of creating 200 product descriptions for Squarespace, I found myself dreading the repetition and inefficiency of doing it manually. Instead, I turned to automation using Python and OpenAI's GPT model to streamline this process.

Here's a step-by-step guide on how I automated the creation of product listings, including Python code snippets and useful lessons learned along the way.

The Challenge

My goal was to create 200 product listings on Squarespace for a collection of automation services. These products needed detailed descriptions, formatted consistently, and optimized for SEO. Writing each description by hand would take a considerable amount of time, which led me to consider automating the task with Python and GPT-4.

Step 1: Setting Up the Project

First, I set up my environment with Python and installed the necessary packages, including the OpenAI Python API client and Pandas for handling data.

The following libraries were essential for the automation:

• OpenAI: Used to generate product descriptions using GPT.

• Pandas: To read from an Excel file and write results to a CSV file.

• OS: To handle file paths and check the existence of files.

import openai
import pandas as pd
import os
import time

Step 2: Loading Data from Excel

The product data, including automation task names, descriptions, pricing, and keywords, was stored in an Excel sheet. I loaded the data using Pandas, specifying the file path.

# Load the data
input_file_path = 'C:/Users/Zenbook/Documents/automation_services_pricing_template.xlsx'
output_file_path = 'C:/Users/Zenbook/Documents/enhanced_automation_services_squarespace.csv'

if not os.path.exists(input_file_path):
    print(f"Error: Input file '{input_file_path}' not found.")
    exit()

automation_data = pd.read_excel(input_file_path)

Step 3: Connecting to OpenAI's API

To generate descriptions, I needed access to OpenAI's API. I used the gpt-3.5-turbo model for this purpose. The API key was stored in the script, though ideally, you would use a more secure method, such as environment variables.

# Initialize OpenAI API (replace 'your_api_key_here' with your actual OpenAI API key)
openai.api_key = 'your_api_key_here'

Step 4: Creating a Detailed Template for Product Descriptions

To generate high-quality descriptions, I devised a structured template for each product. This template included sections like "What We Do," "What the Automation Is," "How It Works," "Benefits to Customers," and more. The prompt was designed to encourage GPT to produce engaging, informative text while including specific keywords for SEO purposes.

prompt = (
    f"Write a detailed and engaging product description for an automation task called '{row['Automation Name']}'. "
    f"This task involves {row['Description']}. Include the following sections: \n"
    f"1. What We Do: Describe what the task is and exactly what it achieves for the customer.\n"
    f"2. What the Automation Is: Explain the automation and how it works in detail.\n"
    f"3. How It Works: Provide specific implementation steps and processes involved.\n"
    f"4. Benefits to Customers: Highlight the value and benefits that this automation brings to customers.\n"
    f"5. Time Allocation: Describe how we spend our time in achieving this task.\n"
    f"6. Deliverables: List the detailed deliverables the customer will receive.\n"
    f"Include the following keywords for SEO: {row['Keywords']}.\n"
    f"Make sure the description is formatted in a clear, easy-to-read manner, with bullet points or numbered lists where appropriate."
)

Step 5: Generating Product Descriptions with GPT

To generate the descriptions, I iterated over each row in the Excel file and used OpenAI's ChatCompletion API to produce content. I implemented retry logic to handle potential API errors, ensuring the script was robust.

retries = 3
for attempt in range(retries):
    try:
        response = openai.ChatCompletion.create(
            model="gpt-3.5-turbo",
            messages=[
                {"role": "system", "content": "You are a helpful assistant."},
                {"role": "user", "content": prompt}
            ],
            max_tokens=500
        )
        enhanced_description = response['choices'][0]['message']['content'].strip()
        if enhanced_description:
            break
    except Exception as e:
        print(f"Error generating content for row {index}, attempt {attempt + 1}: {e}")
        enhanced_description = ""
        time.sleep(2)  # Wait before retrying

if not enhanced_description:
    print(f"Failed to generate content for row {index} after {retries} attempts.")
else:
    print(f"Successfully generated content for row {index}.")

Step 6: Adding Descriptions to the Dataframe and Exporting

Once all descriptions were generated, I added them back to the original dataset and saved the final output as a CSV file, which was suitable for importing into Squarespace.

# Add the descriptions to the original dataframe
automation_data['Enhanced Description'] = descriptions

# Save the DataFrame with descriptions as a CSV file
automation_data.to_csv(output_file_path, index=False)

print(f"Enhanced descriptions generated and saved successfully to {output_file_path}.")

Challenges Faced and Lessons Learned

• API Errors: The OpenAI API occasionally threw errors due to rate limits or other transient issues. To mitigate this, I added retry logic with a short delay to improve resilience.

• Formatting Issues: Initial runs of the script sometimes produced descriptions that were less structured than desired. By refining the prompt and explicitly specifying the formatting structure, I was able to produce consistently well-formatted descriptions.

• Model Compatibility: During testing, I encountered errors about using the wrong model endpoint (e.g., gpt-4 instead of gpt-3.5-turbo). This was corrected by ensuring the proper model and endpoint were being used.

Conclusion

Automating the creation of product listings with Python and OpenAI saved me countless hours. By developing a structured approach to generating product descriptions and ensuring consistency in content quality, I was able to effectively create 200 detailed, SEO-optimized product listings for Squarespace.

Full Script

Here's the full Python script used for generating the product descriptions:

import openai
import pandas as pd
import os
import time

# Load the data
input_file_path = 'C:/Users/Zenbook/Documents/automation_services_pricing_template.xlsx'
description_output_file_path = 'C:/Users/Zenbook/Documents/automation_services_descriptions.csv'

# Check if input file exists
if not os.path.exists(input_file_path):
    print(f"Error: Input file '{input_file_path}' not found.")
    exit()

# Load the Excel data
automation_data = pd.read_excel(input_file_path)

# Initialize OpenAI API (replace 'your_api_key_here' with your actual OpenAI API key)
openai.api_key = 'your_api_key_here'

# List to store descriptions
descriptions = []

# Iterate over each row and generate enhanced content
for index, row in automation_data.iterrows():
    # Generate enhanced task description using GPT-4
    prompt = (
        f"Write a detailed and engaging product description for an automation task called '{row['Automation Name']}'. "
        f"This task involves {row['Description']}. Include the following sections: \n"
        f"1. What We Do: Describe what the task is and exactly what it achieves for the customer.\n"
        f"2. What the Automation Is: Explain the automation and how it works in detail.\n"
        f"3. How It Works: Provide specific implementation steps and processes involved.\n"
        f"4. Benefits to Customers: Highlight the value and benefits that this automation brings to customers.\n"
        f"5. Time Allocation: Describe how we spend our time in achieving this task.\n"
        f"6. Deliverables: List the detailed deliverables the customer will receive.\n"
        f"Include the following keywords for SEO: {row['Keywords']}.\n"
        f"Make sure the description is formatted in a clear, easy-to-read manner, with bullet points or numbered lists where appropriate."
    )

    retries = 3
    for attempt in range(retries):
        try:
            response = openai.ChatCompletion.create(
                model="gpt-3.5-turbo",
                messages=[
                    {"role": "system", "content": "You are a helpful assistant."},
                    {"role": "user", "content": prompt}
                ],
                max_tokens=500
            )
            enhanced_description = response['choices'][0]['message']['content'].strip()
            if enhanced_description:
                break
        except Exception as e:
            print(f"Error generating content for row {index}, attempt {attempt + 1}: {e}")
            enhanced_description = ""
            time.sleep(2)  # Wait before retrying

    if not enhanced_description:
        print(f"Failed to generate content for row {index} after {retries} attempts.")
    else:
        print(f"Successfully generated content for row {index}.")

    descriptions.append(enhanced_description)

# Add the descriptions to the original dataframe
automation_data['Enhanced Description'] = descriptions

# Save the DataFrame with descriptions as a CSV file
automation_data.to_csv(description_output_file_path, index=False)

print(f"Enhanced descriptions generated and saved successfully to {description_output_file_path}.")

I hope this guide inspires you to leverage Python and GPT for your own automation tasks, saving you time and making content creation easier!

Automation not only boosts efficiency but also ensures consistent quality across all your outputs. Whether you're working on product listings, blog posts, or any other repetitive content creation task, leveraging tools like Python and GPT can make a significant difference in the quality of your work and the amount of time saved.