DEV Community

Cover image for YouTube Comments Autoresponder: The Good, Bad, and Ugly
MindsDB Team for MindsDB

Posted on • Updated on • Originally published at mindsdb.com

YouTube Comments Autoresponder: The Good, Bad, and Ugly

How to analyze comment sentiment and react automatically using AI

Today we're digging into a crucial topic for anyone who’s creating content for YouTube: How to analyze and respond to video comments quickly, using the sentiment in those comments.

This tutorial is also handy for anyone who's running a YouTube channel and wants to grow engagement.

Who cares about comments?

We all know the comments section on YouTube is where the real magic happens. That’s where you have a chance to engage with your audience, hear their honest opinions, and build loyalty over time.

Yes, nice comments are awesome. But the not-so-nice comments are important too. And they’re the ones you need to respond to the fastest.

An ideal system would work something like the below.

Image description

If you’re serious about growing your channel, you’ll want a deeper understanding of your audience. Analyzing comment sentiment gives a good gauge of the overall mood of your viewers—which helps you tailor your content.

Truly, this could be a game-changer. Let's jump right in.

Part 1: The tools for the job

For this project, we’re using a platform called MindsDB—the platform for building custom AI. It basically connects real-time data—like tons of YouTube comments— to AI models. It lets companies prototype and deploy AI-powered enterprise solutions quickly and at scale.

In this case, it acts as an orchestration layer where we bring everything together, using AI to analyze the sentiment in the comments. Today we’ll be using models from OpenAI, which makes ChatGPT. Then we’ll set up some MindsDB automations and alerts—those will come from Slack—to help with instant responses.

Here's what the workflow will look like in the end.

Image description

Part 2: How to get everything set up

Step 1. Connect YouTube to MindsDB

To connect your YouTube account to MindsDB and be able to set up a chatbot, you need to first generate OAuth2 credentials in the Google Cloud Console and use it to connect YouTube to MindsDB.

Find detailed instructions in our documentation here.

Let’s name the connection mindsdb_youtube_oauth, which is referred to in the following tutorial.

CREATE DATABASE mindsdb_youtube_oauth
WITH ENGINE = 'youtube',
PARAMETERS = {
    "credentials_file": "/path-to-creds-file/credentials.json"
    -- alternatively, use the credentials_url parameter
};
Enter fullscreen mode Exit fullscreen mode

Once you connect YouTube to MindsDB successfully using the above statement, the mindsdb_youtube_oauth connection comes with the following tables:

  • The comments table stores all data related to the comments left by the viewers, including when was the comment added, who added the comment, and the comment’s content.

  • The videos table stores all data related to the videos, including title, description, and transcript.

Next, we'll use OpenAI models to analyze sentiment of the comments and generate replies.

Step 2. Use an OpenAI model to analyze the sentiment of YouTube comments

Before you can deploy OpenAI models in MindsDB, you should create an engine, providing an OpenAI API key as below.

CREATE ML_ENGINE openai_engine
FROM openai
USING
    api_key = 'sk-xxx';
Enter fullscreen mode Exit fullscreen mode

Now you can plug in any of OpenAI’s models into MindsDB using the CREATE MODEL statement and this engine. Let’s create a model that analyzes the sentiment of the comments.

CREATE MODEL yt_sentiment_classifier
PREDICT sentiment
USING
    engine = 'openai_engine',
    model_name = 'gpt-4',
    prompt_template = 'describe the sentiment of the comments
                        strictly as "positive", "spam", or "negative".
                        "I love this video":positive
                        "It is not a helpful content":negative
                        "{{comment}}.":';
Enter fullscreen mode Exit fullscreen mode

This model takes the "comment" value (from the comments table provided by the mindsdb_youtube_oauth connection created in Step 1) as input and gives the sentiment value as output.

Join the comments table (from mindsdb_youtube_oauth) with the model to analyze the sentiment of the comments. Provide the channel_id value in the WHERE clause.

SELECT c.comment, m.sentiment
FROM mindsdb_youtube_oauth.comments AS c
JOIN yt_sentiment_classifier AS m
WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A";
Enter fullscreen mode Exit fullscreen mode

Here is sample output:

comment sentiment
This video explains the subject so well. positive
Subscribe to this channel asap. spam
It didn’t help me understand the subject. negative

Step 3. Use an OpenAI model to generate replies to YouTube comments

Create a model to generate replies to comments. Here the model will utilize the comment content (from the comments table) and transcript of the video (from the videos table) to come up with a reply.

CREATE MODEL yt_reply_model
PREDICT reply
USING
    engine = 'openai_engine',
    model_name = 'gpt-4',
    prompt_template = 'briefly respond to the youtube comment "{{comment}}";
                       as a context, use the video transcript "{{transcript}}"';
Enter fullscreen mode Exit fullscreen mode

This model takes the "comment" value (from the comments table) and the "transcript" value (from the videos table) as input and gives the reply value as output.

Join the comments and videos tables on their common column being the video_id column. Then, join it with the sentiment classifier model and the reply generator model. This is how you can accomplish the chaining of models in MindsDB.

(Before executing the below command, add at least one new comment to the channel, so that it can fulfill the LAST keyword condition.)

SELECT c.comment AS comment,
       m1.sentiment AS sentiment,
       m2.reply AS reply
FROM mindsdb_youtube_oauth.comments c
LEFT JOIN mindsdb_youtube_oauth.videos v
ON c.video_id = v.video_id
JOIN yt_sentiment_classifier AS m1
JOIN yt_reply_model AS m2
WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
AND v.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
AND c.published_at > LAST;
Enter fullscreen mode Exit fullscreen mode

Please note that for long videos the transcript will be long and that may result in the OpenAI token limit being exceeded. Alternatively, to pass long transcript values in the prompt to the model, you can use the RAG feature of MindsDB.

Here is sample output:

comment sentiment reply
This video explains the subject so well. positive Thank you for your comment. We’re glad you enjoyed the video.
It didn’t help me understand the subject. negative Thank you for your comment. Please let us know your feedback on what could be improved.

The above query joins the comments and videos tables on their common column, which is the video_id column. The reason for joining these tables is to provide input to both the models—the sentiment classifier model requires the comment column from the comments table and the reply generator model requires additionally the transcript column from the videos table.

Next, the two models are joined using the custom JOIN statement that does not require any ON clause condition, because each model is provided with the required input from the data tables. You can select the predictions made by the models as shown in the SELECT clause.

Finally, we define the channel_id value in the WHERE clause. The condition that uses the LAST keyword on the published_at column enables you to fetch only the recently added comments.

Now that the sentiment of comments is analyzed and replies are generated, let’s add some automation.

Step 4. Post replies to positive comments

This is the first component of the job that we’ll create in Step 7.

Here is how you can insert replies to recent comments whose sentiment has been classified as positive:

(Before executing the below command, add at least one new (positive) comment to the channel, so that it can fulfill the LAST keyword condition.)

 INSERT INTO mindsdb_youtube_oauth.comments (comment_id, reply)
  SELECT c.comment_id AS comment_id,
         m2.reply AS reply
  FROM mindsdb_youtube_oauth.comments c
  LEFT JOIN mindsdb_youtube_oauth.videos v
  ON c.video_id = v.video_id
  JOIN yt_sentiment_classifier AS m1
  JOIN yt_reply_model AS m2
  WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
  AND v.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
  AND c.published_at > LAST
  AND m1.sentiment = 'positive';
Enter fullscreen mode Exit fullscreen mode

The LAST keyword ensures that only the newly added comments are returned by the query.

Step 5. Notify about spam comments

This is the second component of the job that we’ll create in Step 7.

Let’s automate notifications to Slack about spam comments. A notification will include all details about the comment, its sentiment (predicted by the model in Step 2), and a sample reply (generated by the model in Step 3).

Set up your Slack channel and connect it to MindsDB following this instruction.

CREATE DATABASE yt_slack
WITH
  ENGINE = 'slack',
  PARAMETERS = {
      "token": "xoxb-xxx"
    };
Enter fullscreen mode Exit fullscreen mode

Test the connection by inserting a message into the Slack channel.

INSERT INTO yt_slack.channels (channel, text)
VALUES("channel-name", "Test message.");
Enter fullscreen mode Exit fullscreen mode

Here is how you can send notifications to Slack about recent comments whose sentiment has been classified as spam:

(Before executing the below command, add at least one new (spam) comment to the channel, so that it can fulfill the LAST keyword condition.)

INSERT INTO yt_slack.channels (channel, text)
    SELECT "channel-name" AS channel,
           concat('Video: https://www.youtube.com/watch?v=', c.video_id, chr(10),
                  'Spam Comment: https://www.youtube.com/watch?v=', c.video_id, 
                        '&lc=', c.comment_id, chr(10),
                  'Author: ', c.display_name, chr(10),
                  'Comment: ', c.comment) AS text
    FROM mindsdb_youtube_oauth.comments c
    LEFT JOIN mindsdb_youtube_oauth.videos v
    ON c.video_id = v.video_id
    JOIN yt_sentiment_classifier AS m
    WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
    AND v.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
    AND c.published_at > LAST
    AND m.sentiment = 'spam';
Enter fullscreen mode Exit fullscreen mode

The LAST keyword ensures that only the newly added comments are returned by the query.

Step 6. Notify about negative comments

This is the third component of the job that we’ll create in Step 7.

Now what about comments with negative sentiment? As these require analysis and thoughtful answers, let’s automate notifications to Slack about negative comments. A notification will include all details about the comment, its sentiment (predicted by the model in Step 2), and a sample reply (generated by the model in Step 3).

Set up your Slack channel and connect it to MindsDB following this instruction.

CREATE DATABASE yt_slack
WITH
  ENGINE = 'slack',
  PARAMETERS = {
      "token": "xoxb-xxx"
    };
Enter fullscreen mode Exit fullscreen mode

Test the connection by inserting a message into the Slack channel.

INSERT INTO yt_slack.channels (channel, text)

VALUES("channel-name", "Test message.");
Enter fullscreen mode Exit fullscreen mode

Here is how you can send notifications to Slack about recent comments whose sentiment has been classified as negative:

(Before executing the below command, add at least one new (negative) comment to the channel, so that it can fulfill the LAST keyword condition.)

INSERT INTO yt_slack.channels (channel, text)
    SELECT "channel-name" AS channel,
           concat('Video: https://www.youtube.com/watch?v=', c.video_id, chr(10),
                  'Negative Comment: https://www.youtube.com/watch?v=', c.video_id, 
                        '&lc=', c.comment_id, chr(10),
                  'Author: ', c.display_name, chr(10),
                  'Comment: ', c.comment, chr(10),
                  'Sample reply: ', m2.reply) AS text
    FROM mindsdb_youtube_oauth.comments c
    LEFT JOIN mindsdb_youtube_oauth.videos v
    ON c.video_id = v.video_id
    JOIN yt_sentiment_classifier AS m
    JOIN yt_reply_model AS m2
    WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
    AND v.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
    AND c.published_at > LAST
    AND m.sentiment = 'negative';
Enter fullscreen mode Exit fullscreen mode

The LAST keyword ensures that only the newly added comments are returned by the query.

Step 7. Automate replies to positive comments and notifications about negative and spam comments

Let’s create a job that will reply to recent positive comments, send notifications about recent negative comments with draft responses, and send notifications about recent spam comments.

To do that, we need to connect a database to MindsDB in order to save the recent comments into a table.

(The reason for doing so is that every run of a query that includes the condition with the LAST keyword returns only the newly added comments, which haven’t been returned in the previous run of such a query. To be able to do different actions for positive and negative comments, we need to save the output of the query with LAST. To save a batch of recently added comments returned by the query with LAST, we need a database table. Learn more about the LAST keyword here )

You can connect a database to MindsDB using the CREATE DATABASE statement and providing specific parameters. Choose one of the supported databases and follow the instructions on how to connect it to MindsDB.

Here we connect a PostgreSQL database as below:

CREATE DATABASE psql_datasource
WITH ENGINE = 'postgres',
PARAMETERS = {
    "host": "host-name-or-ip-address",
    "port": 5432,
    "database": "database_name",
    "schema": "schema_name",
    "user": "postgres",
    "password": "password"
};
Enter fullscreen mode Exit fullscreen mode

To create a table and insert into it the output of the query with LAST, use this command:

 CREATE OR REPLACE TABLE psql_datasource.recent_comments (
      SELECT *
      FROM mindsdb_youtube_oauth.comments c
      LEFT JOIN mindsdb_youtube_oauth.videos v
      ON c.video_id = v.video_id
      WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
      AND v.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
      AND c.published_at > LAST
);
Enter fullscreen mode Exit fullscreen mode

Now it’s time to create a job that automates steps 4, 5, and 6.

CREATE JOB youtube_chatbot (

  -- save the recently added comments into a table

  CREATE OR REPLACE TABLE psql_datasource.recent_comments (
    SELECT *
    FROM mindsdb_youtube_oauth.comments c
    LEFT JOIN mindsdb_youtube_oauth.videos v
    ON c.video_id = v.video_id
    WHERE c.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
    AND v.channel_id="UC5_wBOLCWath6q1iTgPPD5A"
    AND c.published_at > LAST
  );

  -- automate replies for positive comments (from Step 4)

  INSERT INTO mindsdb_youtube_oauth.comments (comment_id, reply)
    SELECT c.comment_id AS comment_id,
           m2.reply AS reply
    FROM psql_datasource.recent_comments AS c
    JOIN yt_sentiment_classifier AS m
    JOIN yt_reply_model AS m2
    WHERE m.sentiment = 'positive';

   -- automate notifications about spam comments (from Step 5)

   INSERT INTO yt_slack.channels (channel, text)
     SELECT "channel-name" AS channel,
            concat('Video: https://www.youtube.com/watch?v=', c.video_id, chr(10),
                   'Spam Comment: https://www.youtube.com/watch?v=', c.video_id, 
                         '&lc=', c.comment_id, chr(10),
                   'Author: ', c.display_name, chr(10),
                   'Comment: ', c.comment) AS text
     FROM psql_datasource.recent_comments AS c
     JOIN yt_sentiment_classifier AS m
     WHERE m.sentiment = 'spam';

  -- automate notifications about negative comments (from Step 6)

  INSERT INTO yt_slack.channels (channel, text)
    SELECT "channel-name" AS channel,
           concat('Video: https://www.youtube.com/watch?v=', c.video_id, chr(10),
                  'Negative Comment: https://www.youtube.com/watch?v=', c.video_id, 
                        '&lc=', c.comment_id, chr(10),
                  'Author: ', c.display_name, chr(10),
                  'Comment: ', c.comment, chr(10),
                  'Sample reply: ', m2.reply) AS text
    FROM psql_datasource.recent_comments AS c
    JOIN yt_sentiment_classifier AS m
    JOIN yt_reply_model AS m2
    WHERE m.sentiment = 'negative';
)
EVERY 10 minutes;
Enter fullscreen mode Exit fullscreen mode

This job (re)creates the recent_comments table and stores in it only the recently added comments. This table is joined with the models to reply to only positive comments. Next, this table is joined with the sentiment classifier model to send notifications about spam comments. And finally, this table is again joined with both the models to send notifications about negative comments with draft responses.

Please note that for large workloads of comments data, it may be required to increase the YouTube quota.

What a custom YouTube chatbot means

With the above steps, you can connect YouTube to MindsDB to access your channel’s comments, analyze them with the help of AI, and automate replies to the recently added comments (or, send notifications about the recently added comments).

In this tutorial, we've shown how to set up a chatbot for a YouTube channel defined in the WHERE clause while querying the comments and videos table.

You can customize this chatbot to your use case, utilizing other integrations and features provided by MindsDB.

Why analyzing sentiment is so important

Analyzing language in comments to determine whether it’s positive or negative, is gold when it comes to understanding your audience's emotions.

Believe it or not—negative comments, while not fun, are not the enemy. They're an opportunity. They give you valuable feedback that helps you grow as a creator or as a brand.

Some of the most dedicated viewers start as critics.

When you respond quickly and gracefully to their nastiest comments (which shows that you value their feedback), you really can turn critics into loyal fans. Remember, it's not about proving anyone wrong; it's about responding fast (in real-time, which is possible with MindsDB) and building a connection.

Top comments (0)