Using SPSS to Calculate Response Rates Blog By Steven Fink, EvansAnalytics

In my previous blog, I showed you how to calculate response rates using SPSS by a subgroup.  As some of you probably noticed, a Total row was missing.  That’s because it’s a little more complicated, but not difficult to program.  Of course, one can perform this calculation in EXCEL, but SPSS can also perform this task better, faster (just like Steven Austin, 6 million $ man!).  To create this Total information, we’ll be using the AGGREGATE command and Merge files.

Below are 10 responses from three departments.

First, we’ll Aggregate the file by Department (/Break=Dept) and create a new dataset, called One.

DATASET DECLARE One.

AGGREGATE

  /OUTFILE='One'

  /BREAK=Dept

  /Respondents=N.

Making sure this new dataset is the Active Dataset, create a population variable, representing the total number of employees in each department.

DATASET ACTIVATE One.

If (dept=1) Population=20.

If (dept=2) Population=10.

If (dept=3) Population=15.

Exe.

Now, create another dataset which contains the sum of the Population and Respondents. Notice the /Break command.

DATASET DECLARE Two.

AGGREGATE

  /OUTFILE='Two'

  /BREAK=

  /Respondents=SUM(Respondents)

  /Population=SUM(Population).

Using the Compute command, create a variable called Dept with a value of 4, representing all respondents. (We’ll add the label later.)

Compute Dept=4.

Exe.    

The Two dataset will look like this:

Next, merge the two files together.  The active dataset is the first Aggregate file created and we are appending the one line dataset called Two.

DATASET ACTIVATE One.

ADD FILES /FILE=*

  /FILE='Two'.

Exe.

Now, we’ll add the Value label for the value of 4.  (Use Add Value Label or else the labels of the first three departments will have no label!) 

Add Value labels

       Dept

            4 'Overall'.

Exe.

Next, calculate the response rate by dividing the number of respondents by the population and multiplying times 100.

Compute Resp_Rate=(Respondents/Population)*100.

Exe.

            Optional code to format Population:

Format Population (F4.0).

Your final data file, called One, will look like this.

Now, using Customer Table, generate the final results.

CTABLES

  /VLABELS VARIABLES=Dept Respondents Population Resp_Rate DISPLAY=LABEL

  /TABLE Dept [C] BY Population [S][MEAN] + Respondents [S][MEAN] + Resp_Rate [S][MEAN 'Response Rate'   F40.2]

  /SLABELS VISIBLE=NO

  /CATEGORIES VARIABLES=Dept ORDER=A KEY=VALUE EMPTY=INCLUDE

  /TITLES

    TITLE='Response Rates by Department and Overall'.

While this dataset was very small, the exact same program will work on datasets comprising hundreds or

thousands of records.  And, to think, writing and executing this program code didn’t cost us 6 million dollars!

Leveraging Text Analytics to Help Answer Your Business Questions, by Dawn Marie Evans, Evans Analytics

Even though the word “Analytics” has exploded everywhere on the business scene, this field is really still in its infancy.  One of the problems with the word is that “Analytics” means different things to different people.  For example, when talking about “Google Analytics,” this generally means web foot-traffic, represented in counts, charts, frequencies, etc.  For statisticians and data miners, “Analytics” refers to taking data, whether it is financial records, customer data, behavioral data, etc. and building predictive models – models that tell us about likely future behavior – that are not just descriptive of past or current phenomena but predictive of future phenomena:  The purpose is to develop a model to answer important and actionable business questions.

“Analytics” may also refer to using open-ended fields – or textual data to create categories which can be joined back to structured data sets through a technique known as National Language Processing (NLP).  It is important to point out that these methods are sensitive to the context.  For example, if the word that is being viewed is “football,” the algorithms that are applied are able to determine if the word is being used in a negative or positive or even neutral way, such as, “He hates football, “(negative) versus, “They were excited about the football game” (positive).  During the process, the analyst, just as with structured data, makes many important choices along the way.

One of the questions I am frequently asked is what type of textual data can be analyzed?  The answer is almost any type of data and very large datasets are desirable.  Examples of these datasets include streaming data (RSS) feeds from the web, Twitter feeds, blogs, PDF documents, open-end questions on surveys.  Analyzing these datasets can be very labor-intensive and time-consuming.  We are in an age where information has become overwhelming; processing and analyzing such information may be difficult, non-standardized, and expensive.  Text analytics/text mining is a standardized, less expensive approach to glean competitive intelligence and to acquire a better understanding of the voice of customers.  Using a data mining stream one can continuously run it, and refresh it to find new and important results at regular intervals.

What does it take to have a text analytics model built? Evans Analytics uses SPSS Modeler, which has a set of premier text analytics tools. SPSS Modeler comes with libraries already built-into the software.  A library is a pre-defined set of sensitive terms and algorithms that can identify and categorize words and phrases. These libraries are a great place to start with a new project. 

Many clients will request that an analyst take the project a step or two further. The next step would be for  the analyst to build custom libraries – specifically developed for the industry, the company, or the project that is analyzed so that the most relevant terms are developed.  These libraries may be saved and be reused, as needed. 

Some clients may just want simple counts.  For example, a client may only want to know a percentage of customers who preferred product X to product Y or a higher percentage of customers provided more positive comments than negative comments about a particular service.  Other clients may request  newly created categories to join back to other structured data, and then predictive modeling or customer segmentation. They may also want to know that customers who preferred product X were also more likely to live in a specific region, be in a certain age range, and also drive a minivan!  Text Analytics becomes more powerful when added to other data to examine whether differences occur by subgroup.

So, how can you leverage text analytics for your business?  Do you have competitors who are blogging or Tweeting or are there news or RSS feeds that are out there as competitive intelligence, but you haven’t gleaned the important information from them that you should be leveraging?  Do you have open ends in surveys that have overwhelmed you, but you know that important information can be extracted? Do you have research that has previously been handled through qualitative methods, but you think it would be stronger if it was analyzed and joined with your structured data?  If you have answered yes to one of these questions, you have a strong case to consider text analytics!

In my next installation, I will explain how to bring previously constructed categories into SPSS Modeler and re-use old qualitative research in a quantitative way.

Trainer Tip: Using SPSS to Calculate Response Rates Blog By Steven Fink, Evans Analytics

Part 1:

You are managing a large data collection project and your director asked you to provide weekly response rates reports by subgroup, such as department, customer type, region, etc.  Many researchers would input result into EXCEL, write a formula, and format the results.  Skip these extra steps and do it all in SPSS!  Here’s how.

Below is 10 record dataset from three departments. (The survey responses are not provided.)

ID	Dept	Dept_name
1	1	Sales
2	1	Sales
3	2	Marketing
4	2	Marketing
5	2	Marketing
6	3	HR
7	3	HR
8	3	HR
9	3	HR
10	3	HR

Using an IF statement, create a population variable, representing the total number of employees in each department.  (Below is the syntax code; one can use the GUI to perform the task.)

If (dept=1) Population=20.

If (dept=2) Population=10.

If (dept=3) Population=15.

Exe.

2. Using the AGGREGATE command, create another variable to calculate the total number of respondents for each department.  This new variable will be part of the dataset. (Note the MODE=ADDVARIABLES command).

AGGREGATE

  /OUTFILE=* MODE=ADDVARIABLES

  /BREAK=Dept

  /Respondents=N.

< Next, calculate the response rate by dividing the number of respondents by the population and multiplying times 100.

Compute Resp_Rate=(Respondents/Population)*100.

Exe.

            Optional code to format Population:

Format Population (F4.0).

Your data file will look like this:

ID	Dept	Dept_name	Population	Respondents	Resp_Rate
1	1	Sales	20	2	10.00
2	1	Sales	20	2	10.00
3	2	Marketing	10	3	30.00
4	2	Marketing	10	3	30.00
5	2	Marketing	10	3	30.00
6	3	HR	15	5	33.33
7	3	HR	15	5	33.33
8	3	HR	15	5	33.33
9	3	HR	15	5	33.33
10	3	HR	15	5	33.33

4  Now, using Custom Table, generate the final results.

CTABLES

  /VLABELS VARIABLES=Dept Respondents Population Resp_Rate DISPLAY=LABEL

  /TABLE Dept [C] BY Population [S][MEAN] + Respondents [S][MEAN] + Resp_Rate [S][MEAN 'Response Rate'  F40.2]

  /SLABELS VISIBLE=NO

  /CATEGORIES VARIABLES=Dept ORDER=A KEY=VALUE EMPTY=INCLUDE

  /TITLES

    TITLE='Response Rates by Department'.

The Custom Tables output looks like this:

Remember to save the Syntax code!  You can run this code and generate the results every week.  No inputting results into EXCEL, with 100% accuracy.

The next blog will show you how to create a total row for all respondents.  (You can’t just add a Total in Custom Tables!) 

From Survey Questions to Business Applications By Dawn Marie Evans & Steven J. Fink

*This is a re-post from Statistics & Analytics Consultants Blog

As a manager you have important business questions you need answered – and with the explosion of analytics, managers are expected to use the data to drive decisions.  Buzzwords like “Voice of the Customer,” “Customer Segmentation,” “Competitive Intelligence,” and “Business Intelligence are bandied about – but how can you nail down a definitive methodology to answer your important question?

One tool for gaining access to the voice of your customers, employees, or population of interest, is a survey. How do you know when it is time to launch a survey?  The short answer to this is when the available data that you have on hand (generally within your company’s databases) fall short in answering your most pressing business questions.  Why hire an expert?  Because if not properly constructed or sampled, the survey most likely will yield results that will either tell you very little of importance, cannot be joined back to your own data with confidence, or may not be representative of your population of interest.  You want to have confidence in the tool itself and in the results that it yields.

Below are two business case examples where surveys have been used to answer important business questions.  You may find these of interest within your own business context:

Customer Segmentation for an Online Company

Working with a company whose products were sold exclusively online, they had a database of customer records on hand.  However, this information was incomplete regarding certain attitudinal information, as well as behavioral information as to how customers were shopping with competitors – both online and in-store.  Launching a survey to a large sample of customers allowed us to gain insight into attitudes and behaviors of customers.  Using a clustering technique, customers were segmented into several key segments that had very different characteristics, based on attitudes, shopping preferences, demographics, etc.

Using principal components analysis, the survey was then reduced to just a few main questions.  When future customers registered on the site and answered these few questions, along with key demographics, they were placed into one of the segments where they would receive targeted marketing messages. This survey helped to answer business questions of: Who are our customers?  What are their motivations for shopping with us?  What are their buying behaviors by segment and demographics? Who are the major competitors by segments?  From here, the marketing department was able to develop the creative messages targeted specifically to each segment.

What Does a Survey Have to Do With Your Salary?

In another key application, an association requested the administration of an annual Compensation Survey to collect data from their members about how much they earn, how much extra they receive in cash bonuses, and deferred compensation.  Survey results may be disaggregated by level of education, position, region of the country, academic vs. non-academic, public vs. private, etc.  Associations may also examine trend data of their members over 2, 3, or 5 years.  In asking such sensitive information of workers, it is important to hire those who are skilled at constructing surveys in such a way that respondents are likely to follow through to the end of the survey.  If you start with questions that are too sensitive early on – or too complex, it is unlikely that those taking the survey will finish.  It is also important this be done by evaluators external to a person’s place of business – there needs to be a buffer, a sense of safety in answering questions that may be attitudinal with regards to their work, salary, work environment, and so forth.

Who uses this information?  Human Resources departments use this information to figure out how much to offer prospective employees or to determine whether their employees are in line with industry practices.   Similarly, prospective employees may use this information to know how much they can expect to earn.  Current employees may also use this information to compare their compensation to their peers. 

So, the next time you want to know whether you are being paid fairly, go to an association website to compare how much you could be earning.  Where did they get this information? From a survey, of course! 

If you have an important business question, and you current data cannot provide all the answers, ask Evans Analytics at info@evansanalytics to design and analyze a survey for you.