You can easily export your registry data to conduct further analysis or to re-organize the data for reports, briefs, and more. In this section, we will not only go over the steps to export your data, but also share the ways other PEER groups have worked with the data outside of PEER. Please keep in mind that you will need expertise or analytical platforms to conduct in-depth analysis. Here, we only introduce basic analysis such as calculating the percentage of participants who reported they are female v. male v. other, or re-organizing the data to calculate the average time it took participants to complete their surveys. Lastly, we will discuss best practices to store and share your data to maintain participant privacy.

How the Data Export Works

By default, the export feature is available to all your administrative users (provided you have not unchecked this function for them in the 'Users and Permissions'). Therefore, if you are working with a recommended researcher and have invited them to have administrative privileges in the registry, they can easily export the data without you having to send them an export. In contrast, if you do not wish to give your researchers full access, you can easily export the data, clean it up as you wish, and send the export to others. Groups may want to do this if they do not want researchers to see participants' contact information, for example. Regardless of what option you pursue, be sure to read the section below on storing and sharing your data.

Any administrator who exports the data can view the following data:

1. Survey Responses

If a participant has selected 'Allow' in the 'Export/Link' column of their privacy settings, then administrators who export can see participants' survey data under the “Survey Responses” tab. If a participant said, “Ask Me” or 'Deny' for the “Export/Link”, then their survey data will appear as “Restricted”.

2. Contact information

If a participant has also selected 'Allow' in the 'Contact Information' column of their privacy settings, then administrators who export can see the contact information in the "Contact Information" tab. If a participant said, “Ask Me” or “Deny” for the “Export/Link”, then their survey data will also appear as “Restricted”.

For participants who chose “Ask Me” for the “Get Contact” feature, you can request permission to view and export contact information by selecting the “Request consent from all” button at the very top of the Kendo Grid (has a small gold key in front of the button). However, we ask that you hold off in doing this because we have a few tweaks to make on how participants receive this notification. Please contact the PEER team with additional questions.

How to Export

You can export your data from two different places in the administrator portal. The first option gives you more options when it comes to exporting your data such as choosing the export type, specific date ranges, and specific participants. In contrast, the second method is a great method if you want to quickly export all your data.

In Search Registry Data

1. Navigate to 'Search Registry Data', select ‘Search Participant Data', and choose the survey(s) for which you wish to view participants.

2. Decide whether or not you want to export all participants or a specific group of participants. If you wish to choose only a specific group of participants, use the filter buttons at the column headers and check the checkboxes to the left of the participants' foreign keys. If you wish to export all participants, go to step 3.

3. Scroll to the bottom of the participant data table, which is also known as a Kendo grid, to find the drop-down menu at the bottom of the table.

PEER has three export options. You can export the data as an excel sheet, as a table, or as a PDF. Groups often find it easier to view their participant data by exporting it to Excel to easily view all the columns in the table, since it is difficult to manipulate the columns in the administrative portal.

If you wish to export the data as an excel, select the 'Export user data' option.
If you wish to export the data as a table, select the 'Export Kendo grid' option.
If you wish to export the data or as a PDF, select the 'Print all to PDF' option.

Screen%20Shot%202017-08-04%20at%209.41.03%20AM.png

4. Be sure to click on the 'Selected' button if you checked off a specific group of participants to export. If you wish to export all, leave the selection at its default.

5. Press Submit to the right of the drop-down menu.

6. If you selected the 'Export user data' option, you will see a final menu of export options. Make the selections you wish. If you wish to specify a date range, you will have to go back to the 'Search Survey Data' view and make sure that the default dates entered are the dates you want for your export. Otherwise, selecting 'All data' will export all the data you have in your registry from the beginning of time.

Screen%20Shot%202017-08-04%20at%209.40.14%20AM.png

7. You should receive a pop-up notification directing you to the 'My Exports' section of the left-hand portal menu once your export is complete. In the 'My Exports' section, click on the word 'Download' underneath the word 'Complete' to download your data.

In Your Portal Menu

1. Navigate to 'My Portals' < '[Your portal name]' < 'Export Data'

2. Please ignore the first option to choose whether to download the data from the Live or Demo environment. PEER does not store data from demo accounts, nor is it available for export.

3. Select from the remaining options, and click 'Export File.'

4. You should receive a pop-up notification directing you to the 'My Exports' section of the left-hand portal menu once your export is complete. In the 'My Exports' section, click on the word 'Download' underneath the word 'Complete' to download your data.

Storing and Saving Registry Data

TBD

Ways to Work with Registry Data in Excel

Pivot Tables for Basic Counting

A pivot table is a function in Excel that allows you to re-arrange – or pivot – the data in multiple ways so you can view and work with your data more dynamically, rather than having to manually re-format your data.

Creating a pivot table for your PEER data is a useful way to do basic counting for your data. For example, if you want to count the total number of participants who answered a specific question.

We recommend using pivot tables for data with less than 2,000 participants, otherwise the data becomes more difficult to work with and load when creating a pivot table. If you also would like to do a more in-depth analysis, you will need to bring in experts who have statistical knowledge and/or use a statistical analytics software like Tableau.

Below are instructions on how to create a pivot table to see the number of people who answered a specific question, and calculate the percentage. Please note that Pivot Tables were updated in 2016, therefore, when we refer to “older versions” we are referring to versions that are 2016 or earlier, which have different buttons than versions 2016 or later.

You can also view a video of how to do the count and percentage here.

1. Download your data from PEER for Research, open up the data file in Excel, and delete the instructions at the top of the ‘Survey Responses’ worksheet written by Private Access (Rows 2 to 6). Make sure you don’t delete the row containing the column titles!

2. Before you create your table, check for instances greater than one in your original downloaded data, so that you are not counting participants multiple times. You can do this by selecting on the Column letter above the ‘Instances’ column to highlight all the values in the column, and navigating to the Data tab (next to the Formulas tab in Figure 1 below).

3. Select the ‘Sort’ button, and this will bring up a pop-up asking if you want to expand the current selection; click on ‘continue with current selection’ as displayed in Figure 2, and click ‘Sort.’ Then sort by ‘Largest to Smallest’ in the order column as seen in Figure 3. This will allow you to easily see the foreign key IDs (participants) with multiple instances, so that you can delete them.

4. For older versions of Excel (2016 or earlier), navigate back to the Data tab at the top of your Excel and find the Pivot table wizard. For newer (2016 or later) versions, navigate to the Insert tab and click on the Pivot table wizard.

5. Click on the Pivot table wizard to create a new pivot table, and you should see a pop-up that will help you build your pivot table. If boxes in the field name are already selected, uncheck all of them.

Keep in mind: when you create a new pivot table, it will create the table in a new worksheet. Please refer to the ‘Survey Responses’ worksheet if you would like to view your original data.

6. To create our table, we will need to put the foreign key IDs as our rows. To do this, select the ‘Foreign Key’ in the Field names, and drag and drop it into the Row Labels (older versions) or Row (newer versions) box.

7. The top of the table, or the column headers, is where you put the data that you want to see.

If you have multiple surveys, start by putting the ‘Survey name’ and ‘Survey ID’ in the Columns (for newer versions) or Column Labels (for older versions) box. If you only have one survey, you don’t need to put in these field names.

Drag and drop the following field names to the ‘Columns’ or ‘Column Labels’ box in order:

  • Question ID
  • Question name
  • Sub-question name
  • Response

The sub-question name signifies checkbox and matrices questions.

8. Now you want to fill in your values. Drag and drop the Response field name into the ‘Values’ box. This will show you which participant provided a response compared to those who didn’t.

We want the participants who didn’t provide a response to display a zero rather the default, which will just have an empty cell. This makes it easier to help with data counting. To do this, click on the ‘Options’ button either in the Data tab of your Excel to find the ‘Empty cells as’ section (older versions) or in your new ‘PivotTable Analyze’ tab (newer versions).

9. Keep in mind that you can also count the sub-total responses for survey questions, but for now, we want to turn them off so it’s easier to view.

To turn the sub-totals off, click on the button containing the question mark (older versions) or the info button (newer versions) next to the ‘Sub-question’ field name in your ‘Column’ or ‘Column Labels’ box. This should bring up a pop-up for settings related to sub-totals. Click ‘None’.

10. To look at the total number of responses for a specific question, and click on the drop-down menu next to the Column Labels cell. You can then search for the question using the Question ID. First, unselect ‘Select All’ option underneath the Search bar.

Refer back to your original data file in the ‘Survey Responses’ worksheet (which you can find at the bottom tabs of your Excel sheet) to find the Question ID for the specific question you want to look at. Type in the Question ID into the Search Bar and check the box with the ID number.

11. Now you can see how a participant answered for that specific question. The pivot table automatically calculates the totals for you at the very bottom row of your table.

As an example, if your question was “Do you have a ___ condition” and the response choices were “Yes” or “No”, you should be able to see which participants answered this question, and how they answered the question. If there is a zero across both the “Yes” and “No” columns, then that means the participants did not answer this question, because they selected neither response. If there is a zero in the “No” column, and a number one in the “Yes” column, this means they selected “Yes” for this response.

12. You can also hide certain columns or rows by selecting on the column or row, right click and select ‘Hide’.

13. You can then calculate the percentages of people who answered each question and/or the percentage of people who answered a specific response, you can perform a simple Excel function by using the formula:

=(number for a specific response/total number of responses)*100

The number for a specific response can be found in the ‘Totals’ row that is automatically generated at the bottom of the pivot table. You can then drag the formula across the entire table to easily get the percentages for all the responses for the question.

To find the total number of responses (which is equivalent to the total number of people, because we already erased multiple instances), click on the Column letter above the ‘Foreign Key ID’ column so that it highlights the entire column.
At the very bottom of your Excel, underneath where you can see the tabs with your worksheet, you should see a ‘Count’, which will give you the total number of participants as seen in Figure 5.

Resources

  • Additional resources on how to use pivot tables can be found here.
  • To find more specific instructions on how to use pivot tables for your specific Excel version, you can search “Pivot tables Microsoft (insert year)” in Google.