How to Use Excel to Develop Data Visualizations for Competitive Analysis Reports
Introduction
Data is everywhere. It's the byproduct of billions of devices measuring, monitoring, collecting, and transmitting digital signals around the world. It's what satellites produce to help meteorologists predict the weather; it's what financial analysts use to determine if a company's stock is a buy, hold, or sell; traffic controllers use it to help airliners take-off and land safely — even the sensors on our mobile phones are collecting data about the amount of light in our offices.
As the number of devices used to capture data has increased, so has the capacity to store, process, and analyze large amounts of data known as Big Data. A key step in the data analysis phase is creating data visualizations, which are graphical representations of datasets that make it easier to see the connections between data points.
In this guide, I'll show you how to create data visualizations in Windows Microsoft Excel 2016 that will be included in a competitive analysis report for a fictitious mobile app development company. We'll also look at how business objectives guide the data strategy and influence design decisions. Finally, I'll walk you through the steps on how to expand a report into a dashboard and share a report using other communications channels.
Defining the Purpose of Your Data Analysis Project
"To raise new questions, new possibilities, to regard old problems from a new angle, requires creative imagination and marks real advance in science."
— Albert Einstein
The same is true in data science. Analysts have the opportunity to help business owners, teams, and customers find answers to questions that advance their knowledge about an issue and contribute to the success of their organizations. The types of questions that guide an analyst's search will vary by industry, company, and position. They can include a company's financial performance, marketing opportunities, competitive analysis, and others. What they all have in common is the need for data visualizations to communicate insights to stakeholders so they can make better business decisions that lead to the attainment of their growth goals.
Scenario
In the following scenario, the data team at App Reach Systems (ARS) has been asked to create a competitive analysis report for the startup's three co-founders, who will present insights from the report to a group of Venture Capitalists (VCs). Here's the background on the company:
ARS has been in business for two years. The startup has received a Series A round of funding, which has helped the company develop COW-O-Bunga Surfboard, a single-player app that challenges gamers to keep cows on their boards at an increasingly COWD-ed California surf spot, where the waves only get bigger with each level. The app has been a modest hit on the Google Play Store app charts, reaching the 23rd spot for the Strategy sub-category for Games.
Now the startup's co-founders are ready to expand their team, but they're in need of capital to take their operation to the next level. VCs have expressed interest in a second round of funding, but they would like to see the company's strategic plan at an upcoming meeting.
The co-founders want to know if developing an iOS version of COW-O-Bunga Surfboard would be a smart move, or if they should follow the advice of the product development team and begin work on a new Games app for the Google Play Store. The untitled, first-person Adventure app would give players the ability to swipe lightning bolts out of thunderstorm clouds towards pirates trying to overtake cargo ships.
After several meetings, the co-founders and data team at ARS agree the following five questions should guide the development of the team's data strategy and competitive analysis report, including data visualizations:
- What are the top 10 apps in the Google Play Store and the Apple iTunes store?
- How does the Adventure sub-category of apps compare with the Strategy sub-category in the Google Play Store?
- How does the Adventure and Strategy sub-categories compare with the same sub-categories in the Apple iTunes Store?
- How much revenue was generated from sales in the top 10 apps on the Play Store compared to the iTunes Store?
- Which app developer has generated the most sales in each of the app stores and what are the amounts?
In this guide, I'll answer the first three questions - the first with a screenshot of the datasets, followed by a data visualization that aims to answer questions 2 and 3.
Preparing Data for Analysis
As with any project, it's critical to use the right tool to get the job done. Fortunately, there are now many mobile and web applications, scripts, and other tools that make it easier than ever to access and transform datasets to prepare for analysis. Depending on your role, you may require sophisticated scientific tools like MATLAB, user-friendly apps like Zoho Analytics, Tableau and Microsoft Power BI, or mainstays Google Sheets and Microsoft Excel.
As with the previously mentioned tools, the Get and Transform (formerly Power Query) feature in the Windows version of Microsoft Excel 2016 makes it easy to load data from files, databases, online services, and other sources.
Please note Get and Transform is not yet available in any version of Excel for Mac.
For structured data, once the data is loaded it is typically cleansed and placed in a data model. You can install Excel add-ins like Fuzzy Look Up for unstructured data. The main advantage of adding Excel Worksheets to a data model is that you can integrate multiple tables for analysis.
Let's take a look at how the app store datasets look before and after they're loaded in Excel.
Apple iTunes Dataset
Google Play Store Dataset
As you can see, each dataset is organized and formatted, but there's a little more work to do to get it ready for prime time. For example, I've created a new fact table and DevID column, which has been added to the iTunes and Play Store Worksheets. So, the final count will be three tables - an iTunes Store table, a Play Store table, and a fact table that joins the stores with the DevID, forming a one-to-many relationship.
Next, I'm going to select the ranges containing the data in each Worksheet and use the keyboard shortcut CTRL + T to turn the columns into tables. You can also use the Get and Transform (formerly Power Query) feature to automatically create tables and create relationships with tables. Again, if you have a Mac, this feature is not currently supported; however, you can use Consolidate to merge multiple sheets by category.
Note: Although Excel can automatically detect relationships between two or more tables, you may still need to manually create relationships. To manually create a relationship, make sure you have the Data Analysis Add-in installed in Excel. Once it's installed, you should see the Power Pivot tab in the Ribbon. It contains an option to Detect table relationships. If you click on Detect and you receive an error, click OK, and then click Add to Data Model. Next, click the Design tab and then click Create Relationship. In addition, if you don't see Power View under the Insert tab, please follow these steps.
Now that I've created a relationship among the three tables, I'll click the Insert tab and use Power Pivot to create a data visualization for the competitive analysis report.
Selecting the Right Visualizations
One of the challenges that Business Intelligence Analysts and other visual communicators face is deciding which type of chart or map to use in their reports. Of course, what you choose will largely depend upon what the data is telling you and how best to communicate your insights to stakeholders. The important thing to keep in mind is the question(s) you're trying to answer for your readers or audience.
I'm going to use a bar chart to answer the ARS co-founders' next two questions on the list. They include:
- How does the Adventure sub-category of apps compare with the Strategy sub-category in the Google Play Store?
- How does the Adventure and Strategy sub-categories compare with the same sub-categories in the Apple iTunes Store?
Check out Venngage's infographic to see how other charts and maps are used for other project objectives.
Designing for Your Audience
The purpose of creating data visualizations is to tell a story about the insights that were discovered during analysis. The important thing to remember is that less is more when it comes to data visualizations. This is especially true for ARS, as the founders will only have seconds to sell the VCs on their strategic positioning using the data visualizations from the competitive analysis report. It can help to create personas of the people who will be viewing your visualizations. This is a technique that's often employed in digital marketing and UX design. A persona includes a list of characteristics that are shared by a particular group. In the case of the VCs, they can be either male or female, hard-working, extremely busy, excellent communicators, and business savvy. Knowing these traits, along with the question(s) you’re answering with your analysis, will help you select the right visualizations with the most effect.
Although there are no rules in picking the fonts, colors, and charts for your dashboards, reports, and slides, I've compiled several best practices from multiple disciplines, including web development, graphic design, typography, color psychology, human psychology, marketing, and user experience design that will help you quickly make creative decisions and communicate more effectively to decision makers.
Here are a few to keep in mind:
- Use stakeholder personas to guide design decisions
- Use a color wheel to select colors that compliment your product or brand
- Use gradient colors to show patterns
- Use seven colors or less in your designs - less is more
- Use complementary, sans serif fonts such as Open Sans
- Use white space (blank part of tiles) generously to help readers navigate
- Use more graphics than words to communicate insights
Publishing Data Visualizations
Now that we have the beginnings of a competitive analysis report in Excel, we can either expand upon the data visualization or share it with stakeholders. For example, if you'd like to create a dashboard that displays other insights from your data analysis, you can import the ARS data model into Microsoft's Power BI business intelligence and analytics tool. Using Power BI, you can publish your report to websites and provide access to your team for review. You can also use OneDrive to upload your report to the cloud and share a link to the report with stakeholders. Excel also makes it easy to email reports either as a Workbook or PDF attachment. Just click the Share button in the Ribbon, create your email, and send.
Conclusion
We use technology to analyze datasets and identify patterns that help our organizations, teams, and customers make strategic decisions that contribute to the attainment of business objectives. In other words, we're data storytellers positioned at the intersection of science and art, where machine learning, artificial intelligence (AI), and humans converge and influence this evolving thing called data science. It's an exciting time to be a part of this growing profession. To see the latest innovations in data visualizations, make sure to check out the annual Information is Beautifu awards for inspiration to create your next data visualization.
Hungry for more? Check out Ben Howard's course Getting Started with Power BI for Business Professionals to learn more about turning raw data into powerful visual reports.