Have you been wondering how to measure the impact of different brands, persons, or organizations on Twitter? Now, you can. DataChant has released a new Power BI app on Microsoft AppSource that allows you to search for any Twitter handle, hashtag, brand, or mentioned keywords and analyze their impact over time.
In the instructions below, you will learn how to install the Power BI app, and leverage no-code Excel and Power Automate services to track and store Twitter search results over time and apply any search terms you wish.
Take a look at this online version of the app that analyzes the 3 main products of Microsoft Power Platform: @MSPowerBI, @MSPowerApps & @MSPowerAutomate. You can view the report on a full screen here.
To install the app, you need to have a Power BI Pro license and permission to install Power BI Template Apps on the Microsoft Power BI service in your organization. Learn more about Power BI Template Apps here.
To refresh the app with any Twitter account or search terms, you will need a Power Automate account with permissions to use the Twitter trigger. If you don’t have a Power Automate account, you can still use the app for learning purposes, without the ability to modify the search terms.
Note: The app doesn’t require a Twitter developer account. All you need in order to run new searches on Power Automate is a Twitter account to connect Power Automate flow to Twitter. The API will not return historical data but will aggregate the search results moving forward, so you better get started and set up this app before you lose important tweets.
Initial App Installation
Go to Microsoft AppSource, and click this link to install the Twitter Dashboard Power BI app. Once installed, you will see the new app under your Apps view on the Power BI service. Click the Twitter Dashboard tile.
Explore the different pages of the app bly clicking the pages on the left sidebar. For detailed instructions keep reading this document.
Once you are ready to try out the app on your own search keywords, proceed to the next section. Note:
How to collect Twitter data for the app
The Twitter Dashboard Power BI app consists of three elements and can be configured in less then 10 minutes to collect and aggregate Twitter search results:
- An Excel template file will be used to store the Twitter search results.
- A Power Automate package that you will import and configure to search anything on Twitter and stores it on the Excel workbook in Step 1.
- The Power BI report, that you’ll configure to import the data from your Excel workbook in Step 1 and can be refreshed automatically.
Let’s start with the Excel template file. Download the TwitterData.xlsx file from here (If you cannot download Excel files directly, you can download it as a Zip file from here and extract the Excel workbook). Save the file to your OneDrive for Business or on a SharePoint online site.
As you can see, the file has an empty table, called Tweets.
Note: If you are not allowed to download Excel files or Zip files from the Internet, you can create this file on your own. Make sure you name the table Tweets, and use these exact column names.
After you save the file on OneDrive for Business or SharePoint online, follow the instructions here to get the file path for the Excel file. Please make sure you follow the instructions to get the path. Save this path. You will soon need to enter it as a parameter in the Twitter Dashboard Power BI settings.
Download the TwitterSearch.zip from here. Do not extract the zip content. This is the Power Automate package you will need. Now, let’s create the Power Automate flow. Open your web browser and go to https://flow.microsoft.com. Select My flows and click Import.
Click Upload and select the file TwitterSearch.zip
In Review Package Content, Search Tweets, click Create as new. On the right pane, you can rename the flow by editing Resource name and select Save.
Under Related resources, select the first resource (Excel Online (Business) Connection), and click Select during import. On the right pane, you may see existing connections you already have in Power Automate that are used to connect to Excel Online. You can select one of them, and click Save. If you don’t have an existing connection, the next short section will describe how you can create a new connection.
How to create a new Excel Online (Business) Connection
If you don’t have an existing Excel Online (Business) Connection follow this section, otherwise, skip to the next section. Click Create new (as shown in the previous screen). Make sure you keep the Import package page open.
On the new browser tab that is opened, click New connection. Select OneDrive for Business, and click Create.
Now, move back to the Import Package browser tab. Select the new connection, and enter save.
Setting up Twitter connection
Go to the Twitter Connection and click Select during import. If you already have the Twitter connection, select it and click Save. Otherwise, click Create new.
On the search bar on the top right corner, enter Twitter, and then select Twitter under Connections > New connection.
In the Twitter dialog box, click Create.
Enter your Twitter credentials and click Authorize app.
Back in the Import package tab, select the new Twitter connection and click Save.
Click Import, and then click Open flow.
Configuring the Search Tweets flow
After you imported the TweetSearch.zip package, it’s time to edit the flow and tune it up to search your keywords and connect it with the TwitterData.xlsx Excel workbook.
In Edit mode of the Power Automate flow, click on the Twitter trigger to expand it.
Enter your search phrase. For example #PowerBI OR “Power BI” OR from:@MSPowerBI will return new tweets with #PowerBI as the hashtag, or the text “Power BI” or tweets that are originated from @MSPowerBI account.
Learn more about the relevant search phrases here. You will need to scroll down the page until you reach the section below.
Note: If you would like to use multiple searches and keep their results separately in the Twitter Dashboard app, you can create multiple Power Automate flows and connect them into the same Excel workbook. You will learn later in this document.
Expand the Initialize variable action, and enter an account or search topic name under Value. You can enter any value here. This is just the name of this flow search results. It will show up as an Account in the dashboard.
Next, expand the Check if Retweet action, and then expand the Add a row into Tweets table. Set up Location, Document Library, and File to open the TwitterData.xlsx file you saved on OneDrive for Business or SharePoint online.
Note, that you will need to locate TwitterData.xlsx file by clicking the folder control and then the right arrow icon in the file exploration dialog box to navigate through subfolders if needed.
That’s it. You can now save the flow and turn it on.
If you wish to track multiple search topics that will be shown as multiple accounts in the Twitter dashboard app, you can click Save As in the Search Tweets flow to duplicate it. In the new flow, update the flow trigger with the new search phrases and account name by entering the value in Initialize variable step. Make sure you don’t change the TwitterData.xlsx file in the new flow. You will need all flows to store data into the same Excel file in order to allow you to compare results across the different accounts / search topics.
Now, when you are ready to collect new tweets, let’s move back to the Power BI Twitter Dashboard app and configure it to load your Excel file.
Note: It is recommended you move to the next step only after you aggregated enough search results in TwitterData.xlsx. Otherwise, the Power BI Twitter Dashboard will have no data to show.
Setting up the Power BI app
After you install the Twitter Dashboard, you will find it in two locations of the Power BI portal. For your consumers, the Twitter Dashboard will be available under the Apps section. For your report authors, the Power BI report will be available under the Workspaces section on the Twitter Dashboard workspace.
When you access the app from the Apps section of the Power BI service you will notice the Connect your data link. Click on it.
In Workbook Path, enter the path you obtained when you saved the TwitterData.xlsx file.
Keep the Subscription Email as none. This will allow you to load from the Excel up to 500 records and two accounts. Later on, when your Excel workbook reaches more than 500 records or you want to run it on more than two accounts, you can subscribe to the premium service here and lift the limits. After you subscribe, enter the subscription email address in Subscription Email.
In the Connect to Twitter Dashboard, set the Authentication method to OAuth2 and Privacy level to Organizational. Then click Sign in and continue.
In the second page of Connect to Twitter Dashboard, set the Authentication method to Anonymous and Privacy level to Organizational. Then click Sign in and continue.
Note: The URL that is used here collects the Subscription Email parameter to determine if you have the Premium subscription and unblock the refresh limitations. The app doesn’t send any other data to this URL.
The Twitter Dashboard report pages
On the Summary page, you can explore brands, companies, persons and topics by the following main metrics:
- Mentions (The tweets that were found in the search results)
- Retweets (How many retweets made to these mentions)
- Influencers (how many people mentioned the search keywords)
- Impact (A calculation that combines both mentions, retweets, and the number of followers and statuses count of the influencers).
All the visuals are interactive and contain contextual tooltips with extra information. You can also apply Drillthrough on some of the visuals and move to the next page to learn more about a specific user. Some of the tables in the Twitter Dashboard allow you to click on the users’ pictures and navigate to their profile on Twitter. You also have the Tweet Link hyperlinks that can take you to the specific Tweets on Twitter.
On the Influencer Analysis page, you can explore the most impactful users by the number of mentions, statuses, followers, or combined impact. When you right-click on a specific user in the scatter chart, you can select Drilltrhough and move to a new report page with more details on the user.
On the Geography page, you can explore the influencers by their location, and identify top impactful locations.
On the Keywords page, you can analyze the most impactful keywords from the influencers’ tweets or profile descriptions.
Basic vs. Premium Versions
The Twitter dashboard is a free app, but it is limited to 500 search results and up to two accounts (search topics). If you want to analyze more data or more accounts, you can upgrade to the premium version, by subscribing here. Once you subscribe, you can enter the subscription email as a paramater.
Edit Scheduled Refresh, and Email Subscription
Go to the Twitter Dashboard workspace, and click the elipsis of the Dataset entity, then select Settings as shown here:
If you subscribed to the premium version, expand the Parameters section and enter the same email you used to subscribe in the Subscription Email parameter.
You can now schedule the refresh of the Twitter Dashboard, as shown below.
With both free and premium versions, the report layout can be edited, and the data can be imported or connected live via Excel and Power BI to build your own reporting tools.
If you have any questions about the Twitter Dashboard or need help, contact us here.