Converting CUSC-CCREU data to Analysis Friendly (especially Tableau) tables

You should be sure to include any elements on which you wish to do analysis in your sample file. You can specify up to 10 Option fields. I include items such as degree and major, and the student’s faculty. Residence status, athletic status, or any other elements you have in your administrative systems that would be helpful should be included.

Open your data and copy out the columns into a new sheet that you want to include as respondent characteristics. We capture the following. Note that we eventually put this into an SQL table; you don’t have to as you could directly use Excel with the data built out as below. If your institution participated in the annual experiment, you may want to include and EXPERIMENT field as well. Note that the Master field indicates whether the response was included in the CUSC master data – you may or may not care about tracking that but it is useful.

Field	Type	Null	Key	Default	Extra
IDNUM	varchar(13)	NO	PRI
UNIVID	varchar(10)	NO
Length	int(1)	NO
Age	int(2)	NO
Gender	int(1)	NO
Load	int(1)	NO
Degree	varchar(4)	NO
Major	varchar(4)	NO
Faculty	varchar(4)	NO
ResidenceType	varchar(1)	YES
Survey Year	varchar(50)	NO
Athlete	varchar(1)	NO
Master	varchar(1)	NO

One way to gather this is to use Excel’s Power Query. You have to have Data Analysis enabled in Excel to access this tool.

• Once enabled, you can highlight all your data (hint: goto A1 and then use Ctrl-A to select all).
• Then choose the Data tab and From Table/Range in the Get & Transform Data group.
• Then use the Choose Columns button in the Manage Columns group of the Home ribbon to select the columns you want.
• You may need to rename a column or two (right click on the heading to do that) and you may need to transform the data depending on your coding in your sample.
• Then go to the home tab and choose Close and Load to get the table result in your workbook.

Here you want to build a table that includes the following information.

Field	Type	Null	Key	Default	Extra
Variable	varchar(20)	NO	PRI
Question	varchar(255)	NO
Survey Section	varchar(55)	NO
Question Grouping	varchar(255)	YES
Root of Question	varchar(255)	YES

Variable is the column heading in your data workbook. So you highlight and copy all of those columns and, on a new sheet, use Paste Special, and Transpose to put them into a vertical column.

Then you would use the Survey Questions CrossWalk table that is provided with your CUSC reports to determine the question text, the survey section, and the question grouping and root.

Question grouping helps you pull questions together where there are responses to sub sections. For instance, variable act10, act11, and act12 are all questions about how many hours the respondent spends on a particular activity. To help you group these together you could enter “how many hours” as the Question Grouping for each of those.

Root of Question lets you add back in to the variable the particular item being asked about by that variable within the group. To return to my example, act10 has the grouping of “how many hours” and the root of question of “in community service/volunteer activities”. Variable act11 has the same grouping, but a root of question entry of “in scheduled classes, labs, seminars and tutorials”. And act12 has the root of question as “on academic work outside of class time.”

The vast majority of CUSC-CCREU survey questions are multiple choice with a coded value. To allow you to translate those into readable text, you need to have a table like the following.

Field	Type	Null	Key	Default	Extra
Variable	varchar(20)	NO	MUL
Variable Value	varchar(40)	NO	MUL
Variable Value Label	varchar(100)	NO
VarKey	varchar(255)	NO	PRI

The CrossWalk table is again a valuable resource for building this. In general terms, the items in the green boxes that give you the option are assigned a numeric value from 1 to the number of options. For sets less than 9, no response is assigned 9. For larger sets, no response is 99. If you have SPSS, it is quite helpful in extracting these from that copy of the data provided to you.

VarKey is just the combination of the columns Variable and Variable Value and serves as the primary key for this table. This simplifies the process of connecting these in SQL and hence Tableau.

This is the big part of this process. We need to change our spreadsheet from a single row for each respondent with all the variables across the columns to a single row for each response with the variable and value in the columns. What we are heading towards is a table that has this structure.

Field	Type	Null	Key	Default	Extra
IDNUM	varchar(13)	NO	PRI
twt	varchar(20)	NO
Variable	varchar(20)	NO	PRI
Variable Value	varchar(1500)	NO
surveyYear	varchar(4)	NO

You should recognize the first two as columns from your workbook. The variable column will hold what is now in the heading of each column, and the value holds the response. We also add the surveyYear as that allows us to collect multiple years into a single table, facilitating year over year analysis.

Tableau used to provide an Excel add-in to do much of this work for you. Sadly that is no longer available. But you can use Power Query in Excel (you have to enable Data Analysis in Excel to access this.

• Highlight all of your data. (Hint Ctrl-A)
• Choose the Data tab, and click From Table/Range. That should open the Power Query Editor
• Click choose columns, and choose IDNUM, deselect columns up to the first variable column you wish to extract. Go to the end to deselect any other administrative columns. Click OK when done.
• Click on the IDNUM column. Click Transform on the ribbon. Click the drop down beside Unpivot Columns and choose to unpivot selected columns. This will put the variable name in an Attribute column, and the variable value in a Value column.
• Right click the Attribute column heading and rename it to Variable.
• Right click the Value column and rename it Variable Value.
• Click the Add column tab on the ribbon, and then click Custom Column in the General Group.
• Give the new column the name surveyYear and in the formula box enter = “2024” (or whatever year’s data you are importing). Click OK.
• Return to the Home tab, and choose Close and Load. This will open a Table tab on your workbook with the reshaped table.

So now you have a workbook with a table of respondents, a table of responses, a table of variables and a table of variable values. What you do next will depend on your environment and tools. At King’s we then take those tables and put them into SQL tables. We then point Tableau at that data source. You could also point Tableau directly at the Excel workbook as a data source. The structure of the relationships between these tables is as appears below.