Setting Up a Simple WooCommerce Product Social Media Post Generator Using LM Studio

Step 1: Install LM Studio

  • Action: Download and install LM Studio
  • Link: LM Studio
  • Install the Mistral Instruct Model
  • Note: Utilize your graphics card to enhance performance. Ensure the server is started post-installation.

Step 2: Export WooCommerce Products to Excel

  • Action: Create a plugin to export random WooCommerce products to an Excel file, including product details and URLs.
  • Note: Customize the exported information based on your requirements.
 * Plugin Name: WooCommerce Catalog Exporter
 * Plugin URI:
 * Description: This plugin exports the WooCommerce catalog to a CSV file.
 * Version: 1.0
 * Author: Pedro Velez
 * Author URI:
 * Text Domain: SmartPost
 * Domain Path: /languages
function wc_catalog_exporter() {
    // Check if WooCommerce is active
    if ( ! class_exists( 'WooCommerce' ) ) {

    // Fetch all products
    $args = array(
        'status' => 'publish',
        'limit' => -1,
        'type' => array( 'simple', 'variable' ), // Fetch simple products and parent variable products
    $products = wc_get_products( $args );

    // Prepare data for CSV
    $csv_data = array();
    foreach ( $products as $product ) {
		// Get product categories and descriptions
		$categories = wp_get_post_terms( $product->get_id(), 'product_cat' );
		$category_names = array();
		$category_descriptions = array();
		foreach ( $categories as $category ) {
			$category_names[] = $category->name;
			$category_descriptions[] = addslashes($category->description);

		// Get min and max prices for variable products
		$min_price = '';
		$max_price = '';
		if ( $product->is_type( 'variable' ) ) {
			$variations = $product->get_available_variations();
			$variation_prices = array();
			foreach ( $variations as $variation ) {
				$variation_obj = wc_get_product( $variation['variation_id'] );
				$variation_prices[] = $variation_obj->get_regular_price();
			$min_price = ! empty( $variation_prices ) ? min( $variation_prices ) : '';
			$max_price = ! empty( $variation_prices ) ? max( $variation_prices ) : '';

		$csv_data[] = array(
			'ID' => $product->get_id(),
			'SKU' => $product->get_sku(),
			'Name' => addslashes($product->get_name()),
			'Description' => $product->get_description(),
			'Image URL' => wp_get_attachment_url( $product->get_image_id() ),
			'Regular Price' => $product->get_regular_price(),
			'Discounted Price' => $product->get_sale_price() ? $product->get_sale_price() : $product->get_regular_price(),
			'Date Created' => $product->get_date_created()->date('Y-m-d H:i:s'),
			'Category Names' => implode( ', ', $category_names ),
			'Category Descriptions' => implode( ', ', $category_descriptions),
			'Product URL' => $product->get_permalink(),
			'Min Price' => $min_price,
			'Max Price' => $max_price,
			'Insert Statement' => "INSERT INTO products_posts (product_id, sku, name, description, image_url, regular_price, discounted_price, date_created, category_names, category_descriptions, product_url, min_price, max_price, post_title, post_body, post_status) VALUES ('{$product->get_id()}', '{$product->get_sku()}', '{$product->get_name()}', '{$product->get_description()}', '" . wp_get_attachment_url($product->get_image_id()) . "', '{$product->get_regular_price()}', '{$product->get_sale_price()}', '{$product->get_date_created()->date('Y-m-d H:i:s')}', '" . implode(',', $category_names) . "', '" . implode(',', $category_descriptions) . "', '{$product->get_permalink()}', '{$min_price}', '{$max_price}', '', '', 'open');"

	// Define CSV file path
		$csv_file = WP_CONTENT_DIR . '/uploads/wc-catalog-export.csv';

		// Open file for writing
		$handle = fopen($csv_file, 'w');

		// Add header row
		if (!empty($csv_data)) {
			fputcsv($handle, array_keys($csv_data[0]));

		// Add data rows
		foreach ($csv_data as $row) {
			fputcsv($handle, $row);

		// Close the file

		// Return CSV file URL
		return content_url('/uploads/wc-catalog-export.csv');

function wc_catalog_exporter_admin_menu() {
        'WC Catalog Exporter',
        'WC Catalog Exporter',
add_action( 'admin_menu', 'wc_catalog_exporter_admin_menu' );

function wc_catalog_exporter_admin_page() {
    if ( isset( $_POST['export_catalog'] ) ) {
        $csv_url = wc_catalog_exporter();
        echo '<p>Catalog exported successfully. <a href="' . esc_url( $csv_url ) . '" target="_blank">Download CSV</a></p>';
    echo '<form method="POST">';
    echo '<input type="submit" name="export_catalog" value="Export Catalog to CSV" class="button button-primary" />';
    echo '</form>';


Step 3: Create a new Table and Insert Exported Data into MySQL Table

  • Action: Insert exported data into a MySQL table which includes extra fields such as post_title, post_body, and status.
					CREATE TABLE IF NOT EXISTS `products_posts` (
  `product_id` int NOT NULL,
  `sku` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `image_url` text,
  `regular_price` decimal(10,2) DEFAULT NULL,
  `discounted_price` decimal(10,2) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `category_names` text,
  `category_descriptions` text,
  `product_url` text,
  `min_price` decimal(10,2) DEFAULT NULL,
  `max_price` decimal(10,2) DEFAULT NULL,
  `post_title` varchar(400) NOT NULL,
  `post_body` text NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'open',
  PRIMARY KEY (`id`),
  KEY `idx_status` (`post_status`)

Step 4: Generate Social Media Post

  • Action: Create a Python script to find posts in “open” status in the new table, and provide context to the AI for generating a post.
  • Prompt Example: Use the prompt provided in the introduction to guide the AI in post generation.
  • Note: Adapt the fixed prompt to suit different products and categories.
					from flask import Flask, request, jsonify
from datetime import datetime
from sqlalchemy import create_engine, MetaData, select, insert, update  
from sqlalchemy.exc import SQLAlchemyError
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
from apscheduler.schedulers.background import BackgroundScheduler
import requests
import logging
import json
import random
import pymysql

SIMILARITY_THRESHOLD = 0.8  # Adjust this value as needed

app = Flask(__name__)

# Configure the database

# Initialize the database
database = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
metadata = MetaData()
Products_Posts = metadata.tables['products_posts']

def home():
    return "Social Media Content Generator API"

def get_ai_response(prompt):
    url = "http://localhost:1234/v1/chat/completions"
    headers = {"Content-Type": "application/json"}
    data = {
        "messages": [{"role": "user", "content": prompt}],
        "stop": ["### Instruction:"],
        "temperature": 0.3,
        "max_tokens": -1,
        "stream": False
        response =, headers=headers, json=data)
        # Check if the response status code is 200 (OK)
        if response.status_code == 200:
            return response.json()  # You might need to extract the actual response text from the JSON response
            # Log the unexpected status code and response text
            app.logger.error(f"Unexpected status code from AI server: {response.status_code}. Response text: {response.text}")
            return None
    except requests.exceptions.RequestException as e:
        # Log the exception
        app.logger.error(f"An error occurred while sending request to AI server: {str(e)}")
        return None

@app.route('/process_posts', methods=['GET'])
def process_posts():
        with database.connect() as conn:
            # Select records with status 'open'
            open_posts_query = select(Products_Posts).where(Products_Posts.c.post_status == 'open')
            open_posts_result = conn.execute(open_posts_query).fetchall()

            processed_posts = 0
            for post in open_posts_result:
                # Assume create_post() returns a tuple (title, body)
                data_to_create = {
                "product_category": post.category_names,  
                "product_url": post.product_url,                
                "use_ai": True,
                "type": "product",
                # Call the create function with the constructed data
                response, status_code = create(data=data_to_create)
                response_data = response.get_json()  
                # post_title, post_body = create(post)  # Call the function to create post

                # Update the current record with the new post_title and post_body
                update_query = (
                    .where( ==
                        post_status='processed'  # Update status to 'processed'
                processed_posts += 1

            return jsonify(message=f"Processed {processed_posts} open posts."), 200
    except Exception as e:
        app.logger.error(f"An error occurred: {e}")
        return jsonify(message="An error occurred while processing the open posts."), 500

@app.route('/create', methods=['POST'])
def create(data=None):"Entering the create function with data: %s", data)

    if not data:
        data = request.get_json()

    with database.connect() as conn:"Entering the Database part")      
        product_query = select(,, Products_Posts.c.category_names, Products_Posts.c.product_url).where( == data['post_id'])
        thepost = data['post_id']"Entering the Post part %s", thepost)
        title_text = ''
        content_text = ''
        for attempt in range(MAX_ATTEMPTS):
            if data.get('use_ai'):
                # Formulate a prompt using the details provided
                # (If it's the first attempt, create the base prompt; else, append a note to it)
                prompt = f"### Instruction:  You are a Marketeer for (QR Awards). QR Awards is a dedicated online gallery focusing on diverse award categories including sports trophies, academic awards, and employee recognition items. They enrich awards by offering a unique service of integrating a photo gallery to any award, making every recognition memorable. Beyond selling a wide array of custom awards and honor plaques through their website, QR Awards adds value by hosting a blog, imparting knowledge on sports history, trophies, and awards. The company is committed to making each honor memorable by providing extensive choices for various occasions, ensuring each award uniquely captures and commemorates achievements. Generate a social media post promoting the {data['product_name']} that belongs to the {data['product_category']} category prioritizing the promotion of product sales. Start the title of the post with 'Title:' and the body with 'Body:'. Keep each post concise and under 140 characters, highlighting the unique photo gallery integration service and the diverse selection of awards and plaques. Include appealing and clear calls-to-action encouraging viewers to explore and make a purchase on the QR Awards website. The Product URL is {data['product_url']}. ###Response: "
                if attempt == 0:
                     prprompt = f"### Instruction:  Please create a different post. ###Response: "
                    prompt += " Note: Please provide a different title and content than the previous attempts."
                # Get AI-generated content
                ai_response = get_ai_response(prompt)
                if not ai_response.get('choices') or not isinstance(ai_response['choices'], list) or not ai_response['choices']:
                    return jsonify(message="AI response is missing 'choices' or 'choices' is not a list or is empty"), 400

                if not ai_response['choices'][0].get('message'):
                    return jsonify(message="AI response is missing 'message' in the first choice"), 400

                if not ai_response['choices'][0]['message'].get('content'):
                    return jsonify(message="AI response is missing 'content' in the message of the first choice"), 400

                data['title'] = ai_response['choices'][0]['message']['content']  # Extracting the AI-generated title
                data['content'] = ai_response['choices'][0]['message']['content']  # Extracting the AI-generated content                
                # Find the start and end indices of the title and content
                title_start = data['title'].find('Title:') + len('Title:')
                body_start = data['content'].find('Body:') + len('Body:')
                title_end = data['title'].find('Body:')          
                # Get the substrings for the title and content using the indices found
                title_text = data['title'][title_start:title_end].strip()
                content_text = data['content'][body_start:].strip()
                data['title'] = title_text
                data['content'] = content_text
      "Title is %s", title_text)
      "Body is %s", content_text)
                # Check if title and content are not None
                if title_text is not None and content_text is not None:
                    # Check if title and content are not empty strings                   
                    if title_text.strip() and content_text.strip():
                        existing_posts_query = select(Products_Posts.c.post_title, Products_Posts.c.post_body)
                        existing_posts_result = conn.execute(existing_posts_query).fetchall()
                        existing_titles = [row[0] for row in existing_posts_result]
                        existing_contents = [row[1] for row in existing_posts_result]
                        if existing_posts_result:
                            # Exclude None values from existing titles and contents
                            existing_titles = [title for title in existing_titles if title is not None]
                            existing_contents = [content for content in existing_contents if content is not None]
                            # Ensure there is at least one valid document to calculate similarities
                            if existing_titles and existing_contents:
                                # Combine existing and new texts for TF-IDF vectorization
                                all_titles = existing_titles + [title_text]  # Use extracted title text here
                                all_contents = existing_contents + [content_text]  # Use extracted content text here

                                # Calculate TF-IDF vectors
                                title_vectorizer = TfidfVectorizer().fit_transform(all_titles)
                                content_vectorizer = TfidfVectorizer().fit_transform(all_contents)
                                # Calculate cosine similarities
                                title_similarities = cosine_similarity(title_vectorizer[-1], title_vectorizer[:-1])
                                content_similarities = cosine_similarity(content_vectorizer[-1], content_vectorizer[:-1])

                                # Log the cosine similarity scores
                      "Title cosine similarities: {title_similarities.max()}")
                      "Content cosine similarities: {content_similarities.max()}")

                                if title_similarities.max() < SIMILARITY_THRESHOLD and content_similarities.max() < SIMILARITY_THRESHOLD:
                                    distinct_content_found = True
                                elif attempt == MAX_ATTEMPTS - 1:
                                    # If the loop completes without breaking (i.e., distinct title and content were not found in 3 attempts)
                                    return jsonify(message="Unable to create a distinct post after 3 attempts"), 400
                                # Handle case where there are no valid documents to calculate similarities
                                # return jsonify(message="Unable to create a distinct post after 3 attempts"), 400
                                distinct_content_found = True
                            # Handle case where title or content is empty string
                            return jsonify(message="AI generated empty title or content"), 400                            
                        # Handle case where title or content is None
                        return jsonify(message="AI generated title or content is None"), 400                          
        if not distinct_content_found:
            return jsonify(message="Unable to create a distinct post after 3 attempts"), 400
        # Check if title_text and content_text are not empty before inserting into the database
        if title_text and content_text:            
            # Insert the new post into the Posts table (This should be outside of the for loop)
            update_query = (
                .where( == thepost)  
            print(str(update_query.compile(compile_kwargs={"literal_binds": True})))
                result = conn.execute(update_query)
                conn.commit()  # Commit the transaction
                return jsonify(message="Post updated successfully", post_id=data['post_id'], title=data['title'], content=data['content']), 200
            except SQLAlchemyError as e:
                app.logger.error(f"Failed to insert record: {str(e)}")
                conn.rollback()  # Rollback the transaction in case of error
                return jsonify(message="An error occurred while updating the post"), 500            
            return jsonify(message="Failed to generate a valid title and content"), 400  
def run_auto_create():
    with app.test_request_context():

scheduler = BackgroundScheduler()
scheduler.add_job(run_auto_create, 'interval', hours=1)

if __name__ == '__main__':

Step 5: Validate Post Uniqueness

  • Action: Use scikit-learn to validate the uniqueness of the generated posts.
  • Link: scikit-learn
  • Note: Ensure that title and content similarities are below the set threshold for uniqueness.

In Action

Final Thoughts:

While this is not a fully finished or polished project, this guide provides a fundamental structure for automating social media post generation for WooCommerce products using LM Studio and other local resources. Although some issues such as loop delays in LM Studio responses might occur, this project offers a robust starting point for small-scale, budget-friendly automation projects. Continue to monitor and refine the process as you go, and expand upon the framework to further meet your business’s unique needs.

This guide provides a blueprint, and by following these steps, you can effectively automate the creation of social media posts for WooCommerce products, thus enhancing your online presence and potentially boosting sales.