Analyzing Social Media Data Using Microsoft Power Automate and Arctype
Utilizing the power of Power Automate to scrape Twitter Data and Analyze the Scrapped Tweet with Arctype Software
Knowing what people think about your brand is crucial for most startups or organizations. Getting real-time customer reviews or reactions regarding your products can help satisfy customers better. And it would be best if you could have this data in a database in real-time with little or no cost. One of the best ways to get this information is through social media platforms like Twitter, mainly because the data is freely available to the public. Twitter has over 100 million active users and more than 500 million tweets posted daily. It is also quickly overtaking other microblogging platforms in terms of growth.
However, a dedicated social media reporting platform is not something every team can afford. If you belong to this group, you might want to use Power Automate, a part of Microsoft 365, to get this data and analyze the stored data using Arctype.
In this article, we will utilize Power Automate to scrape Twitter data and analyze the scrapped tweets with Arctype.
Requirement
To follow along with this article, you should have a basic understanding of SQL and Arctype installed locally on your computer. You should also have an Office 365 license. You can use a Microsoft Developer account if you don’t have one.
Nevertheless, this article will guide you through all the processes.
Project Architecture Flow
The architecture outlines what we intend to explore in this article in detail by dividing it into three sections.
In Section A, we will build a Power Automate flow that will keep an eye on our favorite Twitter topics and scrape the data into an Excel Online or SharePoint list. Then we will use the AI-Builder in Power Automate to perform sentiment analysis in real-time. The data will be imported into PostgreSQL in section B. Last but not least, we will use Arctype to analyze the data in section C.
Section A: Setting up Power Automate Flow to Monitor Tweets
Power Automate
Microsoft Power Automate is a web-based service that helps you create automated workflows between your favorite Office 365 apps and services, synchronize files, get notifications, and collect data with little or no coding knowledge.
We are going to use Power Automate to monitor a topic or trend on Twitter. All these are done using what is called an automated workflow in Power Automate.
Creating a Flow to Monitor Tweets
- To start the flow, you first need to sign in to your Microsoft Account, then click on the “App Launcher” at the top right corner, then navigate to the Power Automate icon.
- As soon as you click on the Power Automate icon, a new window will appear that takes you to the Power Automate environment.
- For this article, we will be using the Power Automate Template, which can be found on the left canvas of your browser. Using a template is an excellent approach to learning how to create Flows and rapidly set up your integrations.
- On the search template, type “Twitter”. This will give you several options for what you can use the flow for.
Note, for this process, the data will be saved as an Excel file with a “.xlsx extension”. We will later need to change to CSV to properly import data to PostgreSQL, as we will see later.
Creating an Excel formatted table
We need to store the data gotten from Twitter in an Excel table online.
- For this, you need to go back to your Microsoft Office Account and click on the “App Launcher” again, but this time select Excel then clicks on “New Blank Workbook.”
- We need to include all the information for our column as a header. This will help in
dynamic contentidentification.
User
Time/Date
Tweet
Location
Follower
Retweet_Count
Tweet_ID
Sentiment Result
- Convert the data range to a formatted table by clicking on “Home” and then clicking on “Format as Table”. This will make your data dynamic and auto-fill.
Adding the Positive and Negative Text Sentiment
Let’s head back to the previous flow we created from Power Automate Template.
- Click on the plus
(+)between Twitter and Add a Row into a Table, then select “Add an action”.
- Search for positive and negative text analysis.
Fill the Flow Content
- When a new tweet appears: You can enter a keyword (or multiple using Boolean operators), hashtag, or user ID. This is referred to as the search text.
The main focus is the album “LOVEDAMINI” by Grammy Award winner Burna Boy. The search text keyword will be “#LOVEDAMINI.”
- Set Sentiment Flow: The language for this will be set to English and the text will be Tweet Text from Twitter.
- Add a Row into a Table: We must fill in the following spaces.
Location: OneDrive for Business
Document library: OneDrive
File: /LOVEDAMINI.xlsx
Table: Table1
- Dynamic Content: Dynamic contents in Power Automate are the kinds of variables created by triggers and actions employed in the flow. The fields that pertain to the earlier steps were selectable by the users.
Debugging, Testing, and Saving Your Flow
It’s time to test your Flow now that you’ve linked it to Excel Online and Twitter. This will specify what you want to track.
Debug Flow
Debugging is done in Power Automate by the Flow Checker. By making sure you adhere to best practices while designing flows, Power Automate’s Flow Checker encourages higher-quality flows.
Test
You can either test your flow both manually or automatically. I would advise you to test it manually to see if the flow is working properly.
Switch to Twitter and send a tweet that contains the phrase, hashtag, or username you wish to track. This ought to start the flow. If the flow runs successfully, you should see your Excel table is populated.
Save
Now that you are satisfied with the flow, you can click on the save icon. This will automatically save your flow and it is ready to be used.
Section B: Importing Saved Data into PostgreSQL
PostgreSQL is a relational database management system (RDBMS) that is open-source and free and strongly emphasizes extensibility and conformity. In PostgreSQL, structured objects are utilized in place of documents to store data. It uses standard SQL formatting and syntax.
We also picked it because it is robust, adheres to SQL standard conformance, is open source, and is supported by Arctype.
Downloading the Excel file and saving it as CSV
For the file to be imported into PostgreSQL, we need to save the file in a CSV UTF-8 format (Comma Separated Value).
Create a data table in PostgreSQL
A table is needed in PostgreSQL, which we will use in importing the CSV file we just created.
Follow these steps to create a data table in PostgreSQL
- Write out the following query in your database to create a new table containing all the columns with their data types.
- Import the saved CSV file into the new table you just created. Go through the GIF illustration below.
Section C: Analyzing PostgreSQL Data using Arctype
We have successfully imported the CSV file into our “love_damini” table. We need to connect our database to Arctype and analyze our data effectively.
What is Arctype
Arctype is a quick and simple SQL client for teams and developers that lets you manage databases and visualize data. The software is supported on the Windows, macOS, and Linux operating systems.
Visit the Arctype homepage and install the Arctype app on your device to start using Arctype. To learn more about Arctype, go through the official documentation or the official blog.
Setting up Arctype
Arctype supports connections to various databases such as PostgreSQL, MySQL, and PlanetScale. Support is also an email away if you require assistance.
Creating a Workspace
After you sign in with your email, you will need to create a workspace. A workspace is a set area for a specific project or other related files.
Arctype provides you with two options when creating workspaces:
- Individual: For personal projects and solo developers.
- Team: Enable you to share your organization’s queries, dashboards, and database connections.
Connecting PostgreSQL to Arctype
Next, you will need to select the database. For this project, we will be using PostgreSQL.
To connect Arctype to your PostgreSQL database, you need to fill in all the PostgreSQL credentials.
Ensure you test the connection before saving. If it says “connection successful, “ you can save your credentials.
Import CSV into PostgreSQL using Arctype
Earlier in this article, we imported data into our PostgreSQL using the pgAdmin software. Now, we are going to do the same but this time around directly in Arctype software.
In your table view, at the top left corner, click on the 3 dots and select “import CSV to table”. Navigate to the directory where you saved the CSV file.
A data preview appears after you select the data you want to use. This will show you the top 10 rows of the data for preview. If you are satisfied with it, then you can accept it.
Next, you need to set your data type to the appropriate data schema. If you are OK with all the settings now, click “Import CSV”.
View the entire data table imported by clicking on the “damini” table in the left-hand corner of the Arctype window. You should see the entire table if properly imported.
Query and visualization using Arctype
One of the many advantages of Archetype is it is its easy-to-use interface and the ability to build charts to get quick insights.
Get Top Fan
Top fan is based on users and how many times they tweeted about the topic “#lovedamini”
To create a chart, change the view from “Table” to “Chart”, then drag the column to either the y-axis or x-axis.
Tweet by Location
This is aimed to get the location the users are tweeting from the most.
Top Influencers
This is based on the number of followers a user has.
Number of Tweets by Day
This will give us the count of tweets by day. To achieve this, we have to convert the “timestamp” to date only and use it as a category.
Sentiment Score
We need to detect the amount of positive, negative, and neutral tweets of users.
Key Takeaway
In this article, you learned how to create a flow using Microsoft Power Automate to scrape Twitter data, import the stored data into PostgreSQL, and analyze the data to get insight using Arctype. This is a much more flexible method, especially if you don’t have a dedicated social media reporting platform.
You also learned and saw how Arctype provides you with a platform where you can query, visualize, collaborate, and share queries with others. You can learn more about the modern SQL editor from the official documentation. If you have any questions, don’t hesitate to contact us.
