It’s safe to say that we all acknowledge how important it is to keep a tab on the information presented in social media sites. A critical challenge for many of us is how to extract the data and generate actionable insights.
There is various social media analysis software that you can use to conduct social monitoring; however, they will cost you. On the other hand, if you can master in data extraction and excel on your own, you will make a head start of others.
Sounds difficult? No! I’m here to help you turn data into a compelling story. The key here is that we need to have enough data where we can draw an informative conclusion, then apply to a series of market strategies.
Preparation: Getting Twitter Data
In this tutorial, all we need is a spreadsheet with a sheer number of data which we can draw an informed conclusion. You probably lack a large amount of data at hand, but this is how a data extraction tool comes into play, that can get tons of data into an easily digestible format. To make things easy for you, I will limit to use only one extraction tool along with Excel.
I will extract data from Twitter as it is available to extract tons of data in the form of an Excel spreadsheet. To get the data, you need to download Octoparse first. If you haven’t used this before, I recommend you to give it a try. They have an awesome feature called scraping template that you can get instant results, including handles, tweets, likes, shares, comments and etc.
To ensure you get some valuable data, you need to understand the tweets. Then we input relevant terms or keywords and wait for Octoparse finish crawling tweets that contain the terms. As soon as you get the results, you export them to an Excel format, and here is the result you should get.
This is a lot of data but it doesn’t mean anything by itself. We need to use a Pivot Table to analyze.
Using Pivot Tables to Analyze Twitter Data
Octoparse already gets the result in a structured format that saves us time and effort to clean up the data. By glancing at the table, we probably can draw a facile conclusion by descending numbers. First, we know that BilelTnn gets the most likes and retweets, yet Priyanka gets the most comments. However, it’s really tough to get a thorough analysis of the landscape.
Luckily, we can use the Pivot Table. As a second step, a Pivot Table is like a SQL implemented in your Excel sheet to summarize a large amount of data. Simply select the data you want to analyze, in this case, I select them all.
Next, click “Insert” from the menu bar and choose “Pivot Table”.
After clicking the button, it will appear a grid with nothing in it. But, don’t worry. We still need a few tweaks to get a final result.
Notice there is a pivot table builder appears on your right side where you need to choose fields to be put in the report. All we have to do is adding publish_date to “Rows” area, since we want to measure values by date. Next, drag content, comments, retweets and likes to “Values” area.
The tables now filled with a count of the number of tweets, replies, retweets and likes by date. When you finish you get a new pivot table that looks like the following:
OK, great! We have just saved hours of work to get an accurate statistical summary of 1000 lines of data using Pivot Tables. Depends on the metrics you want to measure, you can combine different fields to get various results. It’s amazing to see how these numbers can work together and generate some insightful information, especially when you discover something new that you didn’t even intend to. Now, let’s get a leg up and visualize the table we just created for the sake of easy understanding.
Preparing for data analysis
Here comes the fun part that we can turn statistical data sets into meaningful marketing insights. We have data that provides facts about:
- Tweet date
- Tweet Content
- Video and image URLs
- Numbers of tweets
- Numbers of comments
- Numbers of likes
These are a lot of metrics that this survey brought us! We can initiate by asking ourselves a few questions regarding the data above:
1. Is there any clear pattern between the number of tweets and the number of retweets?
It’s reasonable that more tweets will bring more engagements. It clearly shows that tweets numbers have a positive impact on retweets. I’ve also observed that the number of images also have an impact on the retweets. I may spend more time in these metrics and find the key factor that would affect users’ engagement. Then I will take measures in pushing that factor to boost the engagement.
2. What do the stats speak about my marketing campaign?
We gained the most engagement both on Nov.14th 2019 and Jan. 8th 2010. I found that I post infographics on these two days, which leads to a large number of retweets that further impacts on their engagements. Thus, an infographic is an effective method to engage with my audience and I should continue post infographics， in future，to get sustainable growth in engagements.
After all, this is just a simple demonstration of how to leverage data extraction and pivot table to evaluate your marketing campaign. As I mentioned, you can be more creative and combine different metrics together for further in-depth analysis.
Data is the very foundation of a thorough marketing analysis. To analyze the data, first, you need to organize all the information into one Excel spreadsheet.
From there, you can create a pivot table within a few clicks.
The process of extracting web data doesn’t sound as complicated as it seems, especially with an intelligent scraper tool like Octoparse. So does Pivot Table, it can visualize your marketing data straightforwardly that is sufficient during a meeting with your employees.