Podcast: Play in new window | Download
Organizing the layout of your data is critical when attempting to do analysis.
Acquiring the best data set possible can be a very elusive agent. When it comes to finding out what is needed to select the best assets, identify problems, or just to view the results of a campaign, I look at it from the approach of an intelligence agency.
Read any book on espionage or spy recruitment and you will find there is a need to identify the most tell-tale identifier for the job. This requires deciding what questions need to be answered and how to collect the data so it provides the correct facts needed for analysis.
So let’s approach this data analysis thing CIA style. The first thing we need to do is identify the target. I’m going to choose one right out of the news. Let’s consider the future Alien Embassy to be built in the Central Asian country of Kazakhstan (no joke — click here to see for yourself.)
Now, we probably want to collect demographics so we can recruit suitable spies to help us track the progress of their efforts. Who are the ideal targets? In order to decide, we need to aggregate information about the population, perhaps similar to what I found collected in CIA World Fact Book (yes, these things are real, I couldn’t possibly make it up). Demographics such as: 69% of the population of Kazakhstan is between the ages of 16-64, with slightly more males than females, and 95% of the country speaks Russian as its primary language.
So what kind of tools could we have possibly used if we were the CIA? Well, whether you’re recruiting spies or not, Excel can be an excellent analysis tool.
How could we have laid out our data so we can take advantage of an application such as Excel?
Let’s look at the setup of the raw data first. Knowing that we want to group out results by age, gender, and language to find the best targets, we would need to have a column to store each piece of info for each individual.
For Example:
- Column A: might contain the potential spy’s name
- Column B: their address
- Column C: their birth date
- Column D: their current age
- Column E: their gender
- Column F: their spoken language
Now that we have laid out the structure, we can start collecting information from the field for analysis.
Once the data has been collected, we can either use the very convoluted method of writing formulas to summarize, or we can just use a Pivot Table.
Pivot Tables tend to be much faster and are easy to rearrange as more questions during analysis present themselves.
The main key for a Pivot Table to be effective is to lay out your data as rows of consecutive records. Like having the data of one potential spy contained in one row and the next candidate in the next row and so on.
Now that you have your data layed out properly, you can analyze the different groups in alternate ways just by dragging and dropping them. Here are the links for:
to help instruct you on manipulating your assets.
So who do you feel would make the best spy for the Alien Embassy project? Please post your comment below.
And if you would like to harness the “Power of the Pivot” but this podcast just didn’t cover the breadth of your analysis needs, then take this week’s featured class titled “Summarizing Data“. A schedule can be found by following the link to the class schedule in this article.
So until next time, guide your assets well and send your ambassadors reaching for the stars.
Music courtesy of Clayton & Fulcrum
