Archive for Business Intelligence – Page 3

A Beginner’s Guide to Analysis with R

Many associations want to do more advanced analytics projects using R — a programming language used for statistics — but are not sure how to start.
Before starting this kind of analysis, you need to define the goal. It is best to make this a S.M.A.R.T. goal, which means it is Specific, Measurable, Attainable, Relevant, and Time-Bound.
define-your-goalThe detailed S.M.A.R.T. goal will become your dependent variable, which is what you are trying to measure in your analysis. Here’s what a transformed basic goal looks like:
Basic goal: Increase membership retention.
S.M.A.R.T. goal: Determine what program changes will increase next year’s membership retention for first-year members by 10 percent, compared to the two previous years.
After defining the dependent variable, you need to determine the independent variables you are measuring. These are the factors you think may be influencing whether you reach your detailed goal. In this type of analysis, you will have multiple independent variables. In fact, the more independent variables, the better.
As you analyze the data, you will be able to narrow down the independent variables to those that have the highest impact on your goal. For example:

  • Dependent variable
    • Renewal (Did the member renew or not?)
  • Independent variables
    • Participation in chapter events
    • Is the member at a university
    • Participation in committees
    • Gender
    • Age
    • Workplace type and size
    • Location
    • Number and type of events attended

After determining your goal and what may be influencing it, you need to figure out what pool of data you will examine to look for answers. For our example, we would need to start with first-year members who could renew.
However, you may need to filter your data more. For example, if you know that there was a huge change in the renewal process in middle of the year, you may want to remove people who joined before then. Or maybe you have free memberships that automatically renewed each year, so these people should not be included in your pool.
Later in the blog, we will talk about preparing your data and how to run and interpret descriptive statistics in R.

Tableau 9.3: Smarter Version Control and Better Use of Color

Tableau 9.3 was released last week and has several exciting new features. These are a few of my favorites.

  1. Versioning.  In previous editions, when you published a workbook or data source to Tableau Server with the same name as something that already existed, you got a message asking if you’d like to overwrite the older file. “Of course I would,” I’d always say. But there are certainly times when seeing the previous version would have been helpful. Perhaps it would have also avoided some internal strife when a co-worker saved over my amazing workbook.versioning
  2. Publish data source flow. In this version, there is a newly designed dialog box to aid in publishing your data source more quickly. The most frequently used settings and options are available on the same screen. While seemingly small, what I am excited about is the option to “Update workbook to use the published data source.” This means that you can explore your data in Tableau Desktop while connected to the data source you just published. Previously, you had to publish the data source, connect to the data source from Tableau Server, then go through a replace data source process to connect to the published data source.
  3. Excluding totals from coloring. Totals, subtotals and grand totals can be excluded from color-encoding. What does that mean? Previously, if you wanted to do shading based on a count or sum and also wanted to show totals, the darkest color was always the total. This made it easy to overlook the items with the highest totals. Leaving the totals blank, the eye is drawn to 2014 female registrants instead of the grand total.totals coloring - oldtotals coloring - new
  4. Sheet colors. I love colors and I love organization. I’ve been using colors on sheet tabs for some time in Tableau Desktop to organize sheets based on data sources, the audiences or dashboards. Now, coloring is available on Tableau Server as well.sheet colors

Why Your Data Needs a Clean Bill of Health

You’ve found the data you want to analyze and have brought it into a data mart. You know how to use visualization software to turn the data into informative pictures. You know the kind of things you want to find out from your data.
Even so, you’re not quite ready to go forward with your analysis.
Before you get started breaking your data down, you need to understand what you have and get it into good shape. Otherwise, you won’t be analyzing anything. You’ll just be sitting in front of your computer, frustrated that your visualizations aren’t telling much of a story.
This is called data cleansing. And though it may seem like an obstacle on the road to turning your data into information, you won’t be able to perform an analysis without doing it.
Here are some steps to follow in order to make sure your data is ready to go.

  • Figure out the fields in your data. What is contained in each row and are there any problems with it? Each entry should have some sort of unique key associated with it. Is the key duplicated anywhere? Are there many fields that were left empty? And is there even any data there? Duplicated keys and items with too many nulls can cause headaches in your analysis. Likewise, a database that may have been started years ago but never received updates probably isn’t worth the effort to try to analyze. You can help prevent problems of unexpected missing values by adding terms like “unknown” or “not applicable” in their place. This way, you won’t miss out on counting a record because data is missing, and you’ll have more context to analyze what is there.
  • How is your data formatted? This is one place where you need to be a stickler for detail. Are all dates written the same, or does your data source interchangeably use “March 21, 2016” and “3-21-16”? Are addresses and zip codes all done the same? Are there any problems with values – like birth dates of “1900-01-01,” quantities of “999999,” and zip codes of “00000”? If items don’t make sense or aren’t consistently entered in a way that your visualization software can recognize, the data is worthless for your analysis. You won’t be able to make use of date or geographical visualization functions, and your results will be thrown off by obviously incorrect outliers.
  • Can you validate the data? While you may work hard to get the data into a format where you can build better visualizations and tell a better story, you should first make sure that the data you’re getting is correct. If you’re analyzing membership data, it makes sense to talk to someone from that department who should know the basics of what the data says. Were there really 325 new members added in January? If the raw data doesn’t make sense to the people who are most familiar with it, you should go back and correct the problem before moving forward to more analysis. Hidden issues like counting the number of records instead of distinct values, reporting that is based on outdated data snapshots and changes in historical business processes can make reconciling the data mart information challenging. Also, processes to change data may not universally be reflected in the data. Scripts written to change individual member status values might not align with the data ranges of orders and subscriptions.
  • Is data consistently and correctly updated? Even if you do everything to fix your data in the data mart, technical processes are not always perfect. You need to monitor the data that comes into the data mart for consistency and correctness. You should maintain log tables that let you know when the data has updated and show you any problems that were encountered. You should also make use of visualization and analysis techniques to monitor ongoing data quality by tracking potential issues, like incorrect record amounts and missing values. It is important to measure accuracy on a regular basis, making sure that all changes are coming through.

The Smart Way to Measure Email Effectiveness

Have you ever wondered what happens after someone clicks a link from an email that directs her to your website? With a little attention to the links you use, Google Analytics can help you find out. Here’s how to do it.

  1. Start out with the URL Builder tool.
  2. In the URL Builder form’s first box, Website URL, enter the page to which you want to bring the user. This could be an event registration page, a special membership page, or the remainder of an article that follows a teaser in your email.
  3. campaign_link_results

  4. Enter the Campaign Source. This is to help you identify where the click originated. You can just type in “email,” but you may want to be more specific. You can categorize these emails into types, like breakingnews, newsletter, or eventpromo.
  5. Enter the Campaign Medium. This is the way the click got to the site. For email, it will most likely be “email,” though you can change it to refer to different kinds of online paid advertisements.
  6. Skip down to the bottom of the form and enter the Campaign Name. This can be anything you want it to be, but it makes sense to come up with a sensible format. When you are analyzing the effectiveness of your email campaigns, this field will identify which specific email a click came from.
  7. If you are using paid keywords, go back to the Campaign Term field and enter them here.
  8. If you are doing an A/B test, with some recipients receiving a different variation of the email or different places to click that lead to the same URL, add that to Campaign Content. This will let you see which link the click comes from — like whether a user got to your homepage through clicking on your logo or a text link.
  9. Click the Generate URL button at the bottom of the page.

Your new URL will look something like this:
While this is a long URL, it includes all the items you filled out in that form. Each one of them is separated by &utm_. This URL comes from a click on the header field of an email referred to as “blog” in the “newsletterdemo” category.

Finding the Cost of Doing Nothing

It’s likely that every association has multiple challenges and opportunities that can be addressed with data analytics and uncovering all of the costs of those situations is a powerful way to evaluate whether it’s time to take action.
We recommend quantifying the cost of the current situation — which includes direct, indirect and lost opportunity costs — before looking for solutions. That way, you are set up to succeed.
TrustAdditionally, it is easy to quantify the value received at the end of an analytics engagement because the costs of not having it were identified in advance.
Here are a few of the questions that we recommend asking before you start:

  1. What are the primary drivers for data analytics? Common answers are:
    • To have a single version of the truth and increase accuracy and confidence.
    • To arrive at faster answers to business questions and reduce delays.
    • To see a 360-degree view of members and prospects and obtain a better understanding.
    • To allow self-service for association staff which gives them more control and increases satisfaction.
  2. Are there specific pressing problems or opportunities this will address for your association? Can accessing and understanding the data help achieve any of the organization’s strategic objectives? For example, some clients have a strategic objective to increase membership by 10 percent, or increase conference or seminar participation by a fifth.
  3. How does this rank in the organization’s priority list?
  4. What roles within the organization will use data analytics? What are they using now? What do they struggle with?
  5. Who will sponsor the initiative? The most successful implementations have the type of people who drive excitement and adoption in common:
    • Full executive support and other key decision makers who understand the goals.
    • A strong internal leader who owns the project and to whom it is a priority.
    • Other internal champions who will be using the solution.

How Analyzing Social Media is Like Walking Across Bridges

How do your customers connect with one another? Social media mixed with a historic mathematical theory can help you find those patterns and bridge gaps.
Combining social media with other external data can help your association use a range of personal interaction and engagement to move toward a more customer-focused approach. Analyzing social networks is often done through the mathematical concepts of graph theory and network theory, showing relationships between individuals. Richard Brath and David Jonker described using these concepts for business in their book.
Using technical analysis helps you identify people who are “connectors” and link to several groups, “influencers” who help groups form, and cliques that would otherwise be difficult to detect. Setting people up as a simple shape and line graph can be understood by:

  • Counting incoming and outgoing links between people.
  • Looking at the density of direct connections.
  • Examining the shortest and longest paths between people.
  • Considering how far the shapes are from one another.
  • Seeing how people tend to cluster together.

Graph analysis comes just from activities and does not make use of other attributes, like demographics. You can supplement this analysis with other data that you have. You can also use this kind of analysis to identify customers similar to the ones you found through social network analysis. It may be interesting to study things like how members’ interaction levels differ from those of non-members.
To bring social media data together with your other customer information, it should all come together in a data mart. In addition, you can also introduce text analytics to provide additional context. Different social media platforms make data available through application programming interfaces (APIs), which all have their own technical integration options and data scopes.
While social media analysis has been getting more popular recently, the graph theory that is used to break it down is centuries old. Mathematician Leonhard Euler famously used this theory in the 16th century to solve the “Seven Bridges of Königsberg” problem, devising a way to walk through a city while crossing each of its seven bridges only once.
While crossing physical bridges may not be on your list of priorities, social network and graph analysis can help cross several metaphoric bridges in your association, including:

  • Segmenting customers: Coming up with similar people, based on links and attributes.
  • Analyzing influence: Finding people with large numbers of connections and activity.
  • Analyzing the market basket: Figuring out items that are commonly purchased.
  • Finding general correlations: Seeing relationships between people, products, events and other things.
  • Website visit analysis: Determine which webpages are the most popular.

Visualization techniques can communicate the message in social network data through node size, node color, link weight, link colors, and labels. You can use a combination of visualization choices in Tableau to tell social network stories.

How to Jumpstart an Analytics Initiative Through Strategy and Discovery

Recently I wrote about improving adoption, a critical topic for associations looking to maximize results and ROI for their analytics initiatives. But what about associations that have not made the investment yet? What is the best way forward?
A great way to build support, articulate desired outcomes, and develop a collaborative plan for success is through strategy and discovery. In this process, you assess your current state and the desired future state, perform a gap analysis, and devise a plan of action.
Philadelphia PA 1990Current State
The big question: What is your association doing now with analytics? Ask smaller questions, like these:

  • What data is currently collected? How is it used?
  • Does the current staff have the required analytical mindset and capabilities?
  • Is there a process in place for data management and governance?
  • What is the maturity level of your current infrastructure?
  • Is an analytics effort supported by executives and business area leaders?

This assessment reveals areas for improvement – direct and indirect – that can be addressed with analytics. These include lost revenue and opportunities, loss of confidence in the data, inefficiencies, lost staff time and effort, and even damage to reputation.
Future State
The big question: What are your desired outcomes? These may include:

  • Eliminating the information bottleneck created by relying on the IT department to create and update manual reports.
  • Standardizing KPIs to monitor the health of the association.
  • Achieving measurable improvement in specific areas that impact the strategic objectives, such as:
    • Enabling a 360° view of individuals and organizations across all major business areas.
    • Improving engagement with members and prospects.
    • Segmenting member and customer populations based on demographics, explicit and implicit interests.
    • Identifying opportunities to target new audiences or at-risk members.
  • Building an association-wide culture of using analytics. Staff members are able to get the information they need to perform their jobs more efficiently without relying on IT. They’re able to have a conversation with data.

Getting There
Once the current situation and future goals are clearly understood and documented, it’s possible to create a simple analytics roadmap. In other words, the plan of action for advancing from now to the desired future state.  Developing the roadmap involves performing a gap analysis, plus digging deeper into these three areas:

  • Organizational: Structure, business rules and workflow, staff capabilities and availability
  • Technical: IT environment and architecture, infrastructure, governance
  • Data: Complete assessment of all data sources (including those that will be added) for content, structure and integration complexity.

Where do we start?
Although an analytics initiative is not a project — it’s a process of ongoing, continuous improvement — it is still important to show measurable progress quickly. Without it, you risk losing the momentum gained through the collaborative strategy and discovery process. A simple prioritization exercise will help identify areas with the best “bang for the buck.”
The roadmap should begin with areas that offer high business impact with relatively low integration complexity. Often, this means starting with membership data from your AMS, which is some of the highest quality and easily accessible data.
Priority Matrix V2_Blog
All analytics projects should begin with a strategy and discovery process.  It is wise investment of time and resources that greatly increases the probability of lasting success for your analytics initiative. A properly conducted strategy and discovery phase will identify, clarify, and prioritize your association’s business areas for analysis, gain consensus on Strategic Objectives that can be advanced with data and solidify understanding of data sources, business rules, organizational structure, and culture in order to provide an accurate analytics roadmap with budget and timeline.

How to Pare Down Your Wardrobe — and Your Dashboards

As spring approaches, people start thinking about cleaning out their closets and decluttering their lives so they can start the season feeling renewed. You should take advantage of that mindset to get rid of some of your dashboards.

Clothes for Donation

Photo by Eric Mesa

When I go through my closet, I normally make three piles of clothes. Things I know I wear that are in good shape go in the “keep” pile. Things I haven’t touched in years or things that don’t fit go straight into the “toss” pile. When I find things about which I can’t make quick gut decision, I put them in a “re-evaluate” pile. I go through those in the end to determine whether I should keep them.
The process for cleaning up your dashboards is actually very similar.

  • It gets worn all the time. Similar to those clothes that you reach for a regular basis, those dashboards that get a lot of views are definite keepers. On Tableau Server, you can track traffic to see which dashboards get the most usage.
  • You’d buy it again. Maybe there are some dashboards that are not getting a ton of views, but you’d make again if you were starting work with the data today. Those dashboards still have value, and maybe the target audience just doesn’t realize they exist. Keep these dashboards and try to find a way to market them internally so they can deliver the value you know they have.
  • It’s your best special-occasion outfit. Everyone has fancy outfits that only get worn once or twice a year. You will likely have that kind of dashboard, like ones that are created for quarterly board meetings. Save those so you don’t have to re-invent just before the next meeting.


  • You have too many. Sometimes you end up with items that are practically identical. Do you really need four knee-length black sheath dresses? You may have dashboards that serve almost identical purposes. Choose the best and get rid of the rest.
  • You don’t know what it does. I had a thing in my kitchen. I don’t know why I bought it, how it works, or what it is even called. It just took up space. You probably have some dashboards that were created for a reason that no one can recall. Those are just cluttering up your site, so toss them.
  • It is beyond repair. Sometimes you have dashboards that started out simple, then got pork-barreled into unrecognizable messes. You may have spent a lot of time on them, but if they are no longer useful, it is time to start fresh on new dashboards that can handle those questions.

Sometimes you end up with some dashboards that don’t clearly fit in the “toss” or “keep” piles. You will want to put those in the re-evaluate pile. Give yourself a limited time — like six months — to see if they are used. If no one touches these dashboards, or they are confusing to your staff, it is time to toss or redo these visualizations.

How Creating Dashboards is Like Opening Christmas Gifts

When I get the opportunity to develop visualizations for a client, I feel it’s a little like Christmas. A present has been handed to me.
With Christmas gifts, everyone has different ways of figuring out how to open them, determine what’s inside, and what to do with them. Similarly, everyone has different ways of building visualizations.
Most of the time when I am building visualizations, I am given data and questions that the client wants answered. Much of the time, these include things like how many registrants they have, or what their membership retention rate is. Other times, I want to find the story the data is telling me.
Usually, I both answer the client’s questions and provide a story. Here is my general process, blending creative and analytical skills.
Tabluau data calculations

  1. Explore dimensions and measures.
    • First, I need to figure out what the data is about. I usually start with just one dimension, such as a date field or location, and one measure, such as sales or person count.
    • I keep the chart types simple — line graphs and bar charts.
    • I can get lost exploring sometimes, so I like to time box this step. Just like brainstorming, I find that I get diminishing returns as time goes on.
    • I do not delete anything. The chart might be ugly or might not seem interesting by itself, but it may prove helpful later.
  2. Look for possible relationships.
    • I start pulling in multiple dimensions. This is where I love to use Tableau’s “Show Me” feature. I’m not looking to win style points here; I’m still just exploring. The “Show Me” feature allows me to very quickly (as in seconds) explore all the dimensions. Association data usually has a lot of dimensions: Age, gender, area of interest, individual type, organization type, member type, certifications.
    • With most of these baseline visualizations, I don’t see an interesting story. By exploring and asking more questions, however, I usually find something that peaks my interest.
    • Again, I keep most of my work and move to the next step.
  3. Put it all together.
    • Now that I have a pretty good feel for my data and relationships, I start pulling them together on a dashboard. Because I did a lot of high-level exploring in the first two steps, now I feel like establishing a guide of what I want to convey helps me focus. I use my experience, knowledge of visualization and dashboard best practices and Tableau knowledge. Sometimes I go to the Tableau Public gallery for inspiration.
    • I do things like add dashboard actions, create calculated fields, modify chart types, establish dashboard layout, change colors, edit font styles, and tweak tooltips. This is the step that takes the most time, but is also the most rewarding because it makes the story come to life.
    • I remind myself of the value each tweak is adding and am also aware that some of my work will change. For example, font styles and tooltips can really add polish to your dashboard, but it doesn’t make sense to spend a lot of time on them if they might change in the next iteration.
  4. Phone a friend and iterate.
    • I love getting feedback from my colleagues and clients. Feedback can include comments about what may be confusing, what takes too much time to understand, color choices, or possible other effective chart types. Not everyone will agree on what makes the best visualizations, and I don’t take action on all of the suggestions, but I end up with better results when I get feedback.
  5. Avoid tweak-itis.
    • Because I am so detail oriented, I can get stuck in the weeds. I can modify fonts and tooltips all day or develop numerous calculated fields to make something look just right. With each tweak, I have to consider the value it brings.

Journalism vs. Analytics: Finding the Story

Journalism and data analytics are similar in more ways than one. In both cases, you need critical thinking skills, intellectual curiosity, and a passion for uncovering the truth. Here are four ways journalism and data analysis are similar:

  1. Questions, questions, questions. Journalists are tasked with getting to the bottom of what’s going on. To do that, they ask lots of questions, specifically who, what, where, when, why and how. Data analysts also start with questions. What does the client want to know? What are the variables that are being studied? How are they related?
  2. Facts and details are vital. In my former career, I spent many sleepless nights worrying about whether I spelled a name wrong. I made more frantic telephone calls than I’d like to admit to chase down last-minute details to complete a story. And I constantly had other people in the newsroom reading headlines over my shoulder before they were published on the homepage. We all rely on journalists to ensure that the news is as correct as possible. In data analysis, you also need to make sure that you’re working with information that is as correct as possible. Errors in a data set, like items being coded incorrectly or “California,” “CA” and “Calif.” being counted as three different locations, will put your analysis on the wrong track and impact your results.
  3. People are at the heart of everything. Many different sources supply the facts that make a news story, but interviews with people bring the story to life. A story about the Iowa caucuses that is solely based on the results may have the correct information, but it will lack depth and meaning. It’s the same way with data analysis. While analysts spend quite a lot of quality time with numbers and computers, their work doesn’t mean much without talking to people about how those numbers affect them. Stakeholders in a data engagement indicate what is important, as well as reveal details that can make the numbers more interesting and meaningful.
  4. It’s all about telling a story that is informative and easy to understand. News stories should be written using language and sentence construction that could be understood by a fourth-grader. Journalists use short sentences and plain – yet descriptive – language to make the complex topics they write about accessible to everyone. The same principle applies directly to data analysis. Yes, the work behind it is very complicated. The average person may not understand programming and data queries. Thinking about standard deviations, regression analysis and medians may make people who haven’t done complex math in years queasy. However, the data story itself can be told in a very simple fashion. Interactive visualizations cut through all of the complexities and reveal the meaning behind the data in a way anyone can understand.