Twitter is a great way to promote your brand, share thoughts and keep updated about the latest trends and insights in the BI industry. It can be a valuable source of traffic for your site and a good way of getting in touch with other Qlikview Professionals. The goal of this series is to show how easy it is to get started with Twitter analytics in QlikView, by providing easy to follow steps. After reading this series you should be well on your way of becoming a true Twitter BI expert.

Part 1 - Introduction to Twitter for QlikView
Part 2 - Twitter Analytics in QlikView – Finding Top Influencers

Setting up a Twitter account

The first thing you will need to do is create a Twitter account though this link. Simply fill in your name, e-mail address and choose a nice Twitter name. The name can be changed afterwards so don’t worry too much about this at this stage. In the second part of the signup process you will be asked to fill in some additional information and create a profile description. Make sure to include some words you would like to be found on, like “QlikView”, “Business Intelligence” or “Business Discovery”. Adding these words will make it easier for like minded people to find you when searching for new profiles.

Reading Tweets about QlikView

After setting up an account you can start reading information people share in the form of “tweets”. Let’s start by searching for tweets about QlikView. Simply type in the word QlikView in the search box and read what people have twittered about QlikView. The results in the screenshot below already show some interesting information about QlikView. Notice that Tweets often contain special symbols used for Twittering. These symbols might initially look a little cryptic but are actually very easy to understand. I will explain more about them in the next section.

Writing our first Tweet

Now that we have set-up our basic account we can get started writing Twitter messages. Messages can only be 140 characters long and often contain special characters like the ‘@’ and ‘#’ symbols. Because of the length restrictions Tweets frequently also contain “shortened URL’s”. Let’s take a look at the following Tweet example. Feel free to copy and paste it into your message box and send it:

Great blogpost by @QlikMetrics: “Twitter Analytics in QlikView” http://bit.ly/p7YZXQ #QlikView

In the tweet above you see two words that contain a special character and a shortened url:

#QlikView
@QlikMetrics

http://bit.ly/p7YZXQ

The hashtag (#) in front of QlikView means that the writer of the tweet wants to share this message with readers of the “QlikView” twitter feed. Adding hashtags makes it easy for others to follow interesting news targeted towards a specific topic. The @QlikMetrics means the tweet is directed to the user with the account QlikMetrics (that is me!). Adding the ‘@’ tag will make your tweet appear in my personal twitter feed as can be seen in the screenshot below. Don’t misuse this technique as I already get complaints at my work that I Twitter too much . On a more serious note, using tags is a great way to reach out to the right audience for your messages and trigger influential people to join conversations.

Because Tweets can only have 140 characters it is common practice to use free services as bitly to shorten URL’s. Simply fill in the original URL of the blogpost “http://qlikmetrics.com/2011/08/introduction-twitter-for-qlikview/” in the text box and push the “Shorten” button. As can be seen in the second screenshot bitly automatically creates a shortened URL. If you click on the following Shortened URL you will automatically be redirected to the original source: http://bit.ly/p7YZXQ.

Building a network of QlikView followers

One of the first things you want to do is start building a network of people that are interested in the same topic, in our case Qlikview. A good way to start is following people that tweet about QlikView or have brand related terms in their profile like “Business Discovery” or “Data Visualisation”. Click on the “Who to Follow” Button in the upper right corner of the screen and fill in the word “QlikView”. In the results you see a list of people that are related to “QlikView”. This shows directly why it is good practice to include some of the keywords related to your topic of interest in your profile. After clicking on the “Follow” button behind an account you will we able to read what this person Twitters about in your personal TimeLine, on your Home page.

A person will automatically receive a notification when somebody follows them and many people will frequently follow back. This allows you to quickly build a network of like minded people. If you actively tweet and engage yourself in meaningful conversations, or “retweet” other people’s messages people will quickly notice you and start following you. It is good practice to retweet somebody’s message if you really like it. Besides that people appreciate it, it will also help you to increase your visibility on Twitter. If you feel the process of searching and adding people takes too much time you can consider automating part of the process. I added some tips and tricks in the next section on how this can be done.

Advanced Twitter Tips & Tricks

In this final section I would like to share some advanced “Tips and Tricks” that can be useful to jumpstart your social media analytics efforts. After reading them
you will be fully prepared to start with the “Twitter Analytics for QlikView” dashboard in the next blog post.

Social sharing buttons

Twitter is all about conversation, sharing and spreading content. To make it as easy as possible to share content make sure your website or blog contains social sharing buttons like the “Tweet” button in the upper left side of this blog post, or the buttons below in the screen. If you click on the “Tweet” button a small popup window will appear which allows you to share the content of this blog on Twitter. Social sharing buttons are the most user-friendly way of share interesting content with others. When finished reading this post do not forget to share it with others as well!

Twitter automation – building followers

Building up a network of followers can be a daunting task. Luckily there are good ways of automating this process, or automate others common Twitter tasks like scheduling tweets, sending thank you mails and/or automatically unfollow users that do not follow back. Please do NOT miss-use these techniques as some people do. If you happen to see a Twitter account with hundreds of followers without ever sending a single Tweet, than you now understand how they do this. I recommend using “Twitter automation” for freeing up time to write good tweets, read news and build valuable conversations with like minded people. Automatically following and unfollowing people can quickly help to build a large follower list, but when miss-used it can also add “noise” and “spam” to otherwise interesting Tweets. However, when used in the correct way it can save you tons of time, allowing you to focus on meaningfull Twitter tasks.

In the screenshot below you can see the tool I use called TweetAdder. In the image you see a green button with “Automation On” that has been activated. When actived TweetAdder will automatically start searching for people that have Twittered about the topics specified in the “Automated Searched” list. In the example below this lists starts with the keywords: “qlikview”, “#measure” etc.

TweetAdder can be configured to automatically follow or unfollow people that do not follow back within a specific time period. By running TweetAdder in the background it will automatically build a list of followers on your topic of interest, without having to spend any time on searching for accounts online. Together with other power features this tool can really boost your effeciency and follower numbers in a short time. If you are interested in TweetAdder I highly reommend watching some of the turorial video’s on their site to get an impression of what the tool can do for you.

Now that we have covered some of the Twitter basics we can dive into Twitter Analytics. In the next serie called Twitter Analytics – Finding top Influencers I will introduce you to Twitter Analytics with QlikView by showing how to find influential users in an industry and measure two important social media KPI’s: the number of brand “Promotors” and “Detractors”.

Part 1 - Introduction to Twitter for QlikView

Part 2 - Twitter Analytics – Finding top Influencers

The main focus areas of this post will be:

• Finding top influencers
• Measuring a person’s Influence with “Klout” scores
• Measuring brand “Promoters” and “Detractors” trends
• A list of influential people to add on Twitter

To analyse influence we will use the influence tab on the QlikView Twitter Analytics dashboard as shown in the screenshot below. If you click on the dashboard you can see the top brand “Promoters” and “Detractors”, plus influencer’s trends for the BI industry based on two weeks of data collected from Twitter. We will discuss the details of the graphs in the following sections.

Finding top QlikView influencers

One of the key elements in social media analytics is understanding who the top influencers in an industry are and start building meaningful relations with them. Marshall Sponder, author of the book “Social Media Analytics: Effective Tools for Building, Intrepreting, and Using Metrics” describes successful influence by quoting Mike Arauz:

“The most successful influencers are those who have a found way to channel their popularity and reputation into collective actions…”.

Top influencers are people that can help you build or damage a brand and spread your brand message. Top influencers do not necessarily have to be in your industry, but can very well be closely related to it. Stephen Few for example, authority in the data visualisation industry, can be considered a top influencer closely related to QlikView. Stephen Few recommending or mentioning a product in a blog post, tweet or book, as he did with one of QlikView’s competitors in his book “Now You See It: Simple Visualization Techniques for Quantitative Analysis“, can have a large effect on your website traffic or business.

A popular measure of Influence: Klout

According to Marshall Sponder it can be tempting to define “influence” as a combination of signals that can easily be measured such as number of tweets, retweets, followers, lists, etc. However, when looking closely to these metrics, none of them accurately define the ability to channel collective action. An extra challenge with measuring influence is topicality. While a person might be influential in a specific area, this does not necessarily have to translate to influence in other areas.

To get an indication of a person’s influence we will use “Klout“, provided by www.klout.com. Klout is a popular metric that can be used to measure a person’s influence on different social channels as Facebook, Twitter, LinkedIn, Youtube and more. In the screenshot below you can see the graph showing the most influential people for the keyword “QlikView” based on two weeks of Twitter data, sorted on Klout. At the time of writing Klout can be used as a good indicator of influence on social channels even though it is not recommended to use it as industry standard yet. The reason is there are still to many ways you can influence Klout scores. As Klout is continuously working on improving their algorithm and actively develops it’s platform, it can however give a good indication.

If you are interested in my personal Klout score you can access this through the following url on Klout.com: http://klout.com/QlikMetrics.

From influence to Brand Advocacy

A person’s influence or Klout does not necessarily explain whether or not the influence is positive or negative. To differentiate between positive or negative influence in the Dashboard we can use two different sentiment API’s provided by QVSource. In the screenshot below you can see two graphs. The graph on top shows the “Most Influential Brand Promoters” and the graph below shows the “Most Influential Brand Detractors” for BI vendors sorted on Klout influence.

In the screenshot you can see that each person has an “Sentiment” index, showing the avarage sentiment of Tweets. “Sentiment analysis” is an advanced technique that can be used to determine how positive or negative each message is, ranging from minus seven to plus seven for very negative or positive messages. The tweets in the dashboard have been run through an external sentiment API directly in the QlikView load script using the QVSource sentiment connectors (examples can be found in the dashboard). The average sentiment of a person can be calculated by taking the average sentiment of all Tweets.

To measure brand “Promoters” and “Detractors” as shown in the graph above we have used the following criteria:

• A Person that has Tweeted more the 5 times for a brand (like “QlikView”)
• Has a Klout score of 25 or higher
• Has an average positive sentiment for “Promoters” or negative sentiment for “Detractors”

To get an impression of total differences between “Brand Promoters vs Detractors” for BI vendors we can use the following chart from the dashboard (see screenshot below). Do remember this chart only contains two weeks of data so it would be too early to draw any statistical conclusions, nevertheless we already see some interesting trends. If you would like to create similar graphs for your company, or client’s data, change the company name and search terms in the configuration file of the Dashboard and reload the data. Hopefully you will be able to see interesting insights for your industry.

Actively engaging with people on Twitter

Getting an understanding of Sentiment or Brand advocacy trends can be a good first step in building a measurement framework for social media analysis. The second step is finding ways to positively influence these trends. Now even though designing social media campaigns is out of the scope for this blog post there are already things you can start doing. By actively responding to negative Tweets, answer support questions or stimulate brand advocacy through ReTweeting and/or responding to positive Tweets, you should already be able to reinforce a positive brand experience. In the dashboard you can select a person to get an overview of all his Tweets. In the screenshot below for example you can view all my personal Tweets containing the keyword QlikView for my account QlikMetrics. Notice that the Tweets contain a direct link to Twitter which will allow you to respond.

A List of interesting people to follow

To round up this post I would like to share a list of people who I consider interesting to follow on Twitter, or are influential on the topics QlikView or social media analytics. Do not forget to add your own Twitter account in the comment section of this blog when leaving a message!

1. QlikView: Official Twitter account of QlikView
2. donalddotfarmer: QlikView product advocate
3. JohnLovett: Author of ”Social Media Analytics Secrets (2011)”. Influential in Web Analytics
4. QVSource: QlikTech development partner and creator of QVSource – the QlikView API Connector
5. EricaDriver: Product Marketing Manager at QlikTech and active Twitterer
6. QlikMetrics: That’s me obviously!
7. TiqView: QlikView partner in Germany that developed “SentiVal”: http://bit.ly/l6IDaJ
8. Ungvall: Business Discovery advocate and active Twitterer/Blogger
9. WebMetricsGuru: Author of “Social Media Analytics” (2011) – social media analytics authority.

If you like to get started with social media analytics for your company, or provide social media analytics services to clients, feel free to drop me a mail or contact me through my LinkedIn account. Also consider joining the early adapter program for QVSource.

In the following post I will start focusing on “Facebook for Pages Analytics in QlikView”.

Facebook Friends Analyser

The Facebook Friend Analyser Dashboard can be downloaded from QVSource. This is a really great dashboard allowing you to analyse Facebook friends in ways you didn’t knew was possible. You can analyse “Friends”, “Groups”, “Likes”, “Sentiment”, “Check-ins” and cross segment the data using dimensions as “Relationship”, “Gender” and more. What makes this dashboard truly unique is that it offers some really good learning opportunities for advanced techniques such as integrating Google Maps, Social Media Measurement, Sentiment Analysis and Geocoding in QlikView Dashboards. The code behind the file is easy to understand and with some minor modifications you can start applying these techniques to your own dashboards. At the time of writing the latest version includes a Google map which uses friend “locations” that are converted to latitude/longitude with the Yahoo Placemaker API. To run these examples make sure to run the latest version of QVSource and follow the instructions on their Wiki.

Twitter Sentiment Analysis

This is a really awesome Twitter Sentiment Analysis Dashboard developed by TiQ Solutions. It shows how social media streams can be analysed and visualized using QlikView. It can analyse a person’s emotions from published tweets, status messages or online comments. Unfortunately at the time of writing the code behind the file was not available from the demo app so you will have to contact TiQ Solutions for more info. Alternatively you can download the Twitter Dashboard Example from QVSource and rebuild the sentiment analysis part. QVSource offers two easy to use Sentiment Analysis API connectors which can be applied to any data source. As a starting point you can use the Facebook Friend dashboard above. Make sure to follow the steps on their Wiki and within no time you will be able to apply advanced sentiment analyses techniques on Twitter or any other data source.

Salesforce CRM Dashboard

Understanding how to load and analyse CRM data is a critical skill for any analyst, QlikView or BI developer. The great thing about Salesforce is that it is a cloud based solution that’s offers a Free Salesforce developer account for testing purposes. The developer account contains dummy data which can be used to populate the Qlikview Salesfoce dashboard. Before you can use this dashboard you will first need to install the QlikView-Salesforce Connector. I recommend reading the following blogpost to get started. Some extra tips. Do not forget to register your account on the QlikTech site otherwise the Connector will not appear in the download section. Also make sure to follow the instructions in the Salesforce dashboard on how to add the Salesforce security token to your password.

Whats new in QlikView 10

“Whats new in QlikView 10” is a QlikView dashboard example developed by QlikTech showing the latest features of QlikView 10. What makes this Dashboard so great is that is contains some really good chart examples that can be used in your own Dashboards. When I started developing in QlikView I downloaded this dashboard, replaced the code behind the file with my own code and re-enabled all the charts. A good example is the “Regional Scorecard” in the middle of the screenshot. It shows an overview with: Sales this year vs previous year, percentage difference, sparklines for trending and conditional formatting showing when KPI’s are off target (highlighted in red). The overview uses set analysis functions to compare budget against target (the grey-red dots). Finally the dashboard demonstrates how to use spark lines or formulas in standard selection fields in the top and left side of the dashboard. A lot of good educational stuff.

IDC Survey

What is so great about this Dashboard is that it shows how to include qualitative survey data in quantitative dashboards. Combining quantitative and qualitative data opens up a whole new range of analytical possibilities. In the book Performance Dashboards: Measuring, Monitoring, and Managing Your Business the author describes how Cisco is using a Performance Dashboard to monitor over 50 qualitative metrics that impact revenue. In Cisco they use regression analysis to predict the degree to which targeted improvements in internal processes improve customer experiences and satisfaction, which in turn, will drive increase in revenue. Also within the field of Web Analytics there has been a lot of discussion about Voice of Customer data supplementing quantitative website data to get deeper insights. There are some good free online survey tools like 4Q and Survey Monkey that allow you to run on-exit surveys on your website in order to automatically collect customer feedback data (like the ones they sometimes use on the QlikTeck site). Especially 4Q is very good. By asking 4 simple questions they can measure important KPI’s as Task Completion Ratio and Overall Satisfaction scores.

Financial Controller Dashboard

The Financial Controller Dashboard is a great Dashboard with a very good business like design. No distractions in the form of fancy colours or graphs, but clear communication of the most important KPI’s and data. What caught my attention in this QlikView example is the “Profit and Loss Overview” in the upper left side of the screen, containing performance metrics “Revenue”, “Cost of Goods”, “Gross Profit” etc. What is special about this chart is that the “KPI names” are actually loaded as a dimension, allowing them to be used in a single chart element. In most QlikView dashboards these types of overviews are built manually with Text charts. I will leave it as homework to examine the load script behind the file and understand how this example is built.

Linked-in Contacts Analyser

This LinkedIn QlikView example might not offer direct business insights in terms of KPIs or key metrics yet, but it is really cool. Given how fast LinkedIn, a professional social networking site, is growing I am confident that analysing LinkedIn data will become an integral part of social media analytics. Being a LinkedIn fan it is also fun to play around with the Dashboard and slice and dice the data. If you have managed to get this far reading this blog post, than don’t forget to add me to your LinkedIn network! I am always looking for opportunities to extend my professional network in the area of BI, QlikView and Web Analytics. Here is my public LinkedIn profile and Twitter account. Just drop me a personal invitation referring to this blog post and I will be happy to add you!

QVCook Book and Set Analysis

The final two applications are not real dashboards but offer real good learning opportunities. The first application is the QlikView cookbook developed by Rob Wunderlich. This application contains excellent “how to” examples for common QlikView tasks, like incremental loads, interval match etc. The final application contains good information about set analysis for time periods and shows how to flag time periods in a load script. Set analysis is an advanced QlikView topic and having some good code examples is always useful!

I hoped you enjoyed the read! Are there any Dashboards you believe I forgot or should have added?

Setting up the primary key.

In the example above you see a Qlikview Dashboard pulling data through two different sources, Google Analytics and CRM data. In order to produce this type of holistic view of our channels we first need to create a (primary) key between the different data sets. Once the key exists Qlikview will automatically associate the data.

The first step is enabling visitor id tracking in the Google Analytics reports (see screenshot below). For a detailed description of the technique presented in this post please read the orginal post written by Justin Cutroni. The drop down boxed below contains a modified Google Analytics script that will extract a unique ID from the Google Analytics cookies and make this available in the Google Analytics reports and Google Analytics API. in order to enable visitor ID tracking replace your original Google Analytics tracking script on your site with the code below.

Make sure to also rename the  ’UA-10246298-10′ in the code with your own Google Analytics account number. I attached a screenshot here showing you where you can find this number.

Please note that the technique above is not against the privacy policies of Google Analytics as we only use information that is already available in Google Analytics. I do however recommend to cleary state in your privacy policy on your site that your track user bahaviour and might connect historical web site data to registrations.

Pass key through to CRM system

Now we have enabled a visitor ID in the Google Analytics interface we can pass it through to our CRM system. The basic idea is that we will pass the Google Analytics visitor ID stored in the cookie through a hidden field in the lead regitration form. A more detailed description of this technique can be found in the original blogpost UPDATED: Integrating Google Analytics with a CRM written by Justin Cutroni. The code below is a shortened version of Cutronu’s technique which only passes the visitor ID through to our CRM system. The reason is that with the visitor ID we can pull any type of data through the Google Analytics API in retrospect.

<form method="POST" name='contactform' onSubmit="populateHiddenFields(this);">
<input type='hidden' name='visitorid' />
<input type='submit' value='Show GA Info' />
</form>

Click the button see it in action:

Create QlikView script with QVSource

Now that we have set-up the primary key we can start pulling data from the two data sources in our QlikView dashboard. The tool we will use for pulling data from Google Analytics is QVSource. QVSource is a powerfull QlikView add-on allowing you to pull data from a wide variety of sources like, Linked-in, Facebook, Twitter and more. If you haven’t worked with QVSource before I recommend watching the video called Google Analytics Online Offline Data Integration with Qlikview to get a better understanding of how it works.

In our example we will pull the following data from Google Analytics: visitor ID’s (stored in ga:customVarValue5), keyword and source data. This will allow us to measure which keywords and external traffic sources people use to find our site that drive most offline sales.

The next step is to copy and paste the data feed URL in the Connector Settings tab in the QVSource application and go to the Connector Data. Here you can generate the QlikView load statement for your QlikView Dashboard.

Once the Qlikview code is generated copy and paste it into the QlikView dashboard and reload the data. In this simple example below we can see that QlikView has succesfully loaded all Google Analytics data. No programming required. For each visitor ID we can see which source and keyword the visitor has used, how many pages they viewed and how many times they visited the site.

Next Steps

After finishing the last step you will have the Google Analytics data extraction set-up for your QlikView dashboard. The next steps is obviously mapping in sales data from other data sources and start building the actual dashboard. Below is a screenshot of a QlikView dashboard I am currently working on, where I have mapped in offline sales data from a different data source.

Challenges

The examples in this post give a good high-level overview of the most common way to integrate website data with QlikView. Google Analytics however also has limitations.

When looking closly to the second last screenshot you might have noticed that some visitors in Table Box are shown multiple times. The visitor with id 794364472 for example has both been referred through Linked-in, as well as the keyword “Qlikmetrics”. The later represents a normal search path where visitors can visit a site multiple times during a time period. In the field of Web Analytics this often leads to discussions about campaign attribution, for example should we attribute the sale to the first source or the second? In the dashboard above I have used QlikViews interval match to map the sales to the last know search. This technique is called last campaign attribution. To read how challanging these types of attribution models can become I highly recommend reading the following blogpost from Kevin Hillstrom, author of MineThatData.com called Crutchfiled: An Attribution Nightmare.

Other challanges are that Google Analytics only allows you to pull seven dimensions at a time with an API request. This will inevitably lead to challanges building larger datasets for example when adding more dimensions like, visitor browser, geographical location, campaigns etc. When discussing these type of challanges it might be good to mention that Enterprise Digital Marketing Analytics vendors as Webtrends and Omniture offer more powerfull Data Warehousing possiblities. Some providers even allow you to pull raw web data files directly into your Datawarehouse through FTP.

In the following blogposts I am planning to tackle some of the challanges mentioned above.

The goal of the first video is to introduce you to QlikView as a business intelligence and data visualisation tool. In the next video we will go one step further and start adding sales data from a back-office application. This will allow us to create a holistic view of our data, for example measuring sales from a keyword to an offline purchase.

QlikView Data Visualisation.

The tool used in this demo is called Qlikview. QlikView is a leading BI tool which allows you to build awesome dashboard and data visualisations. It can be downloaded at: www.qlikview.com

QVSource.

QVSource is a powerfull QlikView plug-in that allows you to download data from a wide variety of sources directly into your dashboard. Sources include Facebook, Linked-In, Google Analytics, Twitter and many more. It is currently in beta and you can get access through : www.qvsource.com

Download Files.

The files used in the demo can be downloaded through the following URLs:

QlikView-Google Analytics Demo.qvw
templattitude.xls