This article shows you how you can integrate Twitter with Slack and SalesForce for customer and reputation social media monitoring. Monitoring customer feedback and comments on social media platforms can become critical for business reputation management and customer support. While existing platforms can provide turnkey social media monitoring, few can provide deep integration options within your existing technology landscape. This blog demonstrates how you can capture tweets and integrate with Slack and SalesForce using the Enzo platform to show how a customer monitoring platform could be built using a SQL-driven implementation approach.

Solution Overview

The proposed solution captures live tweets into a SQL Server database table as an intermediate step using Enzo Data Sync, a technology available as part of Enzo Server. Changes in SQL Server in turn trigger downstream integration logic written in the SQL language for simple processing. The ability to process integration logic using SQL is the glue that makes this solution simple to build and evolve over time.

Enzo Data Sync is a Change Data Capture (CDC) technology that allows you to monitor virtually any source system, including Twitter, and forward events/changes to SQL Server. Enzo Data Sync provides a number of advanced capabilities, such as append-only changes, Upsert and Delete events, and requires no coding.

Enzo Data Sync essentially emulates an event-driven architecture by listening to changes in source systems, and forwards detected changes to SQL Server. For more information on Enzo Data Sync, see the Data Sync Overview.

To react to specific tweets, you then create one or more triggers on the target database table, and write SQL commands to take action on the tweets. For example, you could update a SalesForce customer record, and send an alert to a Slack Channel when detecting negative tweets. Using this technique, it becomes easy to trigger actions to virtually any platform through Enzo.

Capturing Tweets in SQL Server

The first part of the solution involves capturing tweets from the Twitter timeline, and store them in a SQL Server table. This is done without a single line of code, leveraging the SQLServer adapter. The table will be created automatically by Enzo the first time.

A Data Sync job is configured to monitor tweets using an initial filter, such as the name of a company and/or a Twitter handle. This initial filter reduces the number of tweets saved into the target database table. In this example, the Data Sync job captures tweets that mention the Miami Airport handle (@iflymia).

SELECT TOP 1000 * FROM Twitter.timeline WHERE filter='@iflymia'

We can run this Data Sync on a schedule, every 10 minutes for example, so as to avoid Twitter request throttling. Once captured, the tweets are added to the target SQL Server table automatically, as shown below.

Reacting to Tweets

The easiest way to react to tweets at this point is to create a SQL Server Trigger called every time a new set of Tweets are added to the table. This can be done in two ways:

• Implement an actual SQL Server Trigger on the Twitter.MIA table
• Create a SQL Job that tracks the latest tweet processed, and process every newly received tweet

These two approaches offer greater flexibility by allowing the SQL code to filter further which messages should be forwarded, and by applying additional conditional logic if desired. For example, a SQL Trigger could call an external Cognitive Service to perform a sentiment analysis, as discussed in Calling Azure Cognitive Services from SQL Server.

Assuming the target table is called TWITTER.MIA, the following trigger would be fired for each Tweet inserted into the table:

CREATE TRIGGER TR_MIA_INSERT 
ON TWITTER.MIA
AFTER INSERT
AS
	DECLARE @ID int, @Text varchar(4000), @screenName nvarchar(100)
	SELECT id, [text], user_screen_name FROM Inserted
	OPEN TWEET_CURSOR;
	FETCH NEXT FROM TWEET_CURSOR INTO @ID, @Text, @screenName

	WHILE @FETCH_STATUS = 0
	BEGIN
	  IF (@Text LIKE '%covid%')
	  BEGIN
	    -- Send message to SLACK
	    DECLARE @msg varchar(4000), @xml varchar(4000)

 	    SET @msg = 'User ' + @screenName + ' posted a tweet on Covid: ' + @text
	    SET @xml = '' + @screenName + '' + @text + ''

	    -- Send Slack message
	    EXEC [localhost,9550].bsc.Slack.PostMessage @msg, '154846361'   
	    -- Save to SalesForce Custom Table 'UserMessages'
	    EXEC [localhost,9550].bsc.SalesForce.InsertRaw 'UserMessages', @xml

	  END
	  FETCH NEXT FROM TWEET_CURSOR INTO @ID, @Text, @screenName
	END;
	CLOSE TWEET_CURSOR;
	DEALLOCATE TWEET_CURSOR;

The above trigger loops on all incoming tweets; Enzo Data Sync will insert multiple records as a single operation for performance reasons, so the code implements a loop to process each new tweet. In this sample code, a call to Slack and SalesForce is made if the tweet contains the text 'covid'. The power of the SQL language allows us to build integration logic easily and call other systems quickly. In addition, the above code is short and simple to maintain, requiring only basic understanding of the SQL language. For more information on how to configure and use the Slack adapter, see Slack Integration with SQL Server.

Other techniques are possible, such as calling Stored Procedures instead of placing the entire logic in a trigger. It is also possible to call Enzo Async methods to minimize the performance impact of calling external systems. See the Async documentation for more information.

Conclusion

This blog shows you how you can easily program against the Enzo platform, by leveraging the power of the SQL language, and implement business and IT process automation. The ability to encapsulate business processes as simple SQL commands makes the logic easy to review, test, and check-in as regular code. We also discussed how the Enzo Data Sync technology simplifies monitoring changes in source systems, including Twitter feeds, allowing organizations to implement an event-driven architecture.

To try Enzo at no charge, download the latest edition of Enzo Server on our website.