How to monitor the activity of any Twitter account

Intro

Over the last decade, Twitter has become more and more a smooth, and highly personalized, competitor to traditional media outlets. It allows your business to get in touch with the world, learn the latest trends and listen to potential new customers every day.

Analyzing your engagement level and how your tweets are perceived by your audience is pivotal to understanding your overall performance in the channel. However, browsing the feed is not the easiest path and manually retrieving data is time-consuming.

In this short guide, we’ll walk you through building an easy spreadsheet to list and monitor your Tweets’ performance.

Here is a sneak peek of what you will get:

Twitter analytics glossary

Before getting started, let's briefly go through some definitions. As a business, Twitter offers its community a broad range of actions to engage with your social media activity:

• Follow. Following an account puts it on your feed and allows you to get updated when it posts new content.
• Like. Use it to show appreciation or endorsement of specific posts.
• Retweet. Use it to re-share some content to your follower base, ignite discussions, and support a cause.
• Reply. Through reply, you can directly add a personal comment below any Tweet.
• Share. Use it to share the link to a specific Tweet with anyone outside Twitter.

• Based on these actions, some relevant metrics are computed:

• Impressions. The total number of times a Tweet is displayed in the timeline or search results.
• Engagement. The sum of all interactions a tweet has triggered (mainly likes and retweets, but also replies, expansions, hashtags clicks,...) defines the so-called engagement of a tweet.
• Engagement rate. All engagements divided by the impressions of a Tweet. It represents a measure of how reactive your follower base is.
• Link clickthrough. Total number of times a link in a Tweets has been clicked.

Let's go!

First of all, use our template at the following link Twitter activity dashboard and save it in one of your workspace's folders.

This template provides you with a dashboard to monitor the performance of your activity on Twitter.

Let's reverse-engineer it together.

First of all, you need to enable Rows data integration and connect your Twitter account to Rows. Click on the top modal and then follow the instructions. The typical Twitter Auth page will open: enter your Twitter handle and password, and authorize Rows to retrieve your data.

Now you are back on the spreadsheet, ready to set it up. In cell B1 of the Input table, input any Twitter account name (such as yours, or your competitors'). Cell B2 offers you some options to set the schedule rate, that is the frequency at which Rows will automatically update the dashboard data. Let's pick "every day at 08:00", and turn on tracking, as follows

The current snapshot

The table 'Current snapshot' gives you an overview of the current state of the account, including the following metrics: followers, following, engagement per tweet, and the average engagement rate in the last 7d. Let's go through them one by one:

• Date displays the current day date, through the function TODAY()
• Following displays the number of following of the account, through the function through the function GET_FOLLOWING_TWITTER()
• Followers displays the number of following of the account, through the function through the function GET_FOLLOWERS_TWITTER()
• Ratio is the ratio between the two
• Last 7d engagement rate counts the average tweet engagement (likes and retweets) obtain in the last 7 days, divided by the current number of followers. Let's do a deep dive into it. The dataset used is the Tweets database table on the 'Last tweets' page. At the numerator, it employs SUMIF to sum all likes and retweets that occurred in the last 7 days, i.e. TODAY()-7. Then it divides it by the number of tweets posted in the same period, through COUNTIF(), to obtain an average value. At the denominator, the same GET_FOLLOWERS_TWITTER().

The Tweets database

This table is the source of most of the calculations on the Activity Dashboard page and is generated through the function SEARCH_USER_TWEETS_TWITTER on the Backend page. It retrieves the last 100 tweets from the account, using as a single input the account name, in cell B1 of the Input table, as follows:

1            =SEARCH_USER_TWEETS_TWITTER('Input'!B1)

The data cell in B1 of this page is then rendered into a readable table, through the function Create a table under the ... option.

A panel will open on the right-end side. Here you can pick the table headers, i.e. the data you want to display as columns in the output table and the table destination, here cell A1 of the 'Tweets database' table.

The current table includes the following data:

• Date. The timestamp of the tweet. This field is particularly important because it serves as a key for metrics and summary calculations
  
• Tweet. The post itself
  
• Like. The number of likes
  
• Retweets. The number of retweets
  
• URL. The post link
  
• Year Week. The year and week corresponding to the Date
  
• Is retweet?. A parameter Yes/No indicating whether a tweet is itself a retweet or not. Relevant when computing metrics on the first page.

The Activity summary

This table gives you a daily summary of the activity. It uses as a key the unique values of the field date, taken from the 'Tweets database' table, as follow:

1            =EXPAND(UNIQUE('Tweets database'!A:A),,1)

Here Rows UNIQUE() function is placed as the first argument of the EXPAND() function in order to display the actual values in the column. Column B to E are then using the date as a key and compute the Tweets, Tot likes, Tot retweets and Engagement/Tweet values as conditional SUM() and COUNT() in the 'Tweets database' table. To automate computation, we placed all of them in the table headers via AUTOFILL(). In particular:

• Tweets counts the number of tweets on that day
  
• Like sums the total number of like on that day
  
• Retweets sums the total number of retweets on that day
  
• Engagement/Tweet sums the total number of engagements (likes and retweets) in the day, divided by the number of Tweets in that day.
  

The Follower and following database

You can see this table as a log of all current snapshots generated over time, and updated according to the schedule set in the Input table. It is generated through the UPDATE() in cell B4 of the Backend page, which takes the data - 'Current snapshot'!A1:F2 - and update the destination table - 'Followers and following database'!A1:F1 - whenever the keys - 'Current snapshot'!A1:B1 - change, namely: the date and the account name. This means that, whenever there is an update of the date or the account name, Rows triggers the function and a new record is added to the table.

1=UPDATE('Current snapshot'!A1:F2,'Current snapshot'!A1:B1,'Followers and following database'!A1:F1)

This way, we are creating a log that lets you monitor followers and following growth as long as the trend of the engagement rate over time. To help you visualize trends, you can use the chart on the Dashboard page.

To build them, you need to select the whole source table, here 'Followers and following', and click on Chart under the Insert menu in the toolbar.

The Tweets planner

Finally, use the 'Tweets planner' table to organize and schedule your posting activity, choosing a topic, the basic idea, inputting the release date, and marking it as 'Done' whenever the Tweet is shipped.

Embed into documents

If you are using Notion or any other internal tools that support iframes to build a report, you can embed any table or chart of this spreadsheet in your document. Just click on the ... icon in the top right corner of the table and pick Embed. A modal will open, as follows:

Before embedding any table or chart of a spreadsheet, you need to grant access to it. Follow the yellow message, and toggle on the Share privately option in the Share tab, as follows:

Once the spreadsheet's sharing option has been enabled, you just need to hit Copy URL from the Embed tab and paste the link into your document.

This is how Rows embed looks on Notion:

You might also like