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.
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.
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.
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:
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 = '
' -- 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; ' + @screenName + ' ' + @text + '
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.
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.
Over 50 adapters are available