OData with MS Excel

These instructions are intended to guide you through the process of connecting to your CX Survey respondent data (responses and scores) using the OData Connector.

Connect to your data using MS Excel

Use all the features of Excel to analyze your CX Survey respondent data by using the OData Connector to make a direct connection.

Obtain your credentials first by following the instructions under the OData Connector section.

🚧

Excel 2010-2013 Prerequisites

For Excel 2013 or earlier versions, you must install the Power Query add-in, which can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=39379

After installation, be sure to use the Power Query tab (not the Data tab) when following these instructions. Details can be found here: https://support.office.microsoft.com/en-us/article/connect-to-an-odata-feed-power-query-4441a94d-9392-488a-a6a9-739b6d2ad500

Logging In

Once you have your token (password), do the following:

  1. In Excel, go to the Data tab (Power Query tab for Excel 2010-2013) and select Get Data > From Other Sources > From OData Feed.
530530
  1. Enter a URL (Basic) based on your data requirements and click OK. For information on the URLs you can use, see URLs for getting data.

    The URL in the screenshot below returns all data for all CX Surveys (https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures).

624624
  1. You are prompted with the following screen. Choose Basic, and then enter your User Name (Predictive Experience Login ID) and Password (token).
  2. Select the level as seen in the following screen, and then click Connect.
877877

Importing Question Response Data into Excel

  1. Open a blank worksheet in Excel.
  2. Go to the Data tab at the top and select Get Data > From Other Sources > From OData Feed.
  3. When the following prompt displays, paste in the desired URL using a Measure Key of your choice.
877877
  1. Click OK to import the data.
    A screen displays similar to the following image. Do NOT load, but instead click Edit (or Transform Data in older versions of Excel).
13141314
  1. Once the Power Query editor has loaded, select the columns that you would like to keep and remove the rest. Removing unwanted columns is recommended as it enhances the performance of OData queries by limiting the data that is retrieved and transformed. Here is an example keeping the following columns:
    • Respondentid
    • Responsetime
    • QuestionLabel
    • answerValues

For a description of all columns, see Data Column Definitions.

  1. With the columns selected, click Remove Columns > Remove Other Columns.
13211321
  1. With only the desired columns remaining, you have the following options:
  • Rename the query from Query 1 to a more descriptive label.
  • Extract Values from the answerValues. You can optionally choose to Expand to New Rows, particularly useful to perform additional operations, if your data has multiple answer choices per question.
  • Change responseTime to Data Time format (Optional).
12801280
  1. When you extract values, you are asked to select a delimiter. In this example, we selected the Comma delimiter. You can select any delimiter from the list.
486486

Pivot the Response Data

  1. On the Transform tab in Excel, select the questionLabel column and click Pivot Column.
12771277
  1. You are prompted with a screen to select the Values Column. Choose answerValue. From the Advanced options menu, select Don't Aggregate.
697697
**Note:** Once you click OK to these options, it may take some time for the data to be transformed.

The data set contains one respondent per row and contains values for the columns you selected to keep. In our example, we chose to keep the following columns, hence each respondent row contains data for these columns only:

  • Respondentid
  • Responsetime
  • QuestionLabel
  • answerValues

There is more you can do to fine-tune this data set, for example, rename column headings, replace values, format response date/time.

12191219
  1. Now click Close and Load to get your data set.
14151415
**TIP:** If you do this again, you can save some steps.

Many of the steps above have been recorded as a script in the Advanced Editor. This means you don't have to go through all of these steps when analyzing a different CX Survey.

You still must:

  1. Open Excel.
  2. Go to the Data tab and click Get Data.
  3. Enter the URL string for "responses" but change the Measure Key: https://api.foresee.com/v1/bi/cxmeasure/odata.svc/clients/11596511/Measures(8866805)/Responses.
  4. When the preview of data is loaded and you click EDIT, then you can go into the Advanced Editor and paste the following lines.
598598
let
    Source = OData.Feed("https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures(8866805)/Responses", null, [Implementation="2.0"]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"answerValues", "respondentId", "responseTime", "questionLabel"}),
    #"Extracted Values" = Table.TransformColumns(#"Reordered Columns", {"answerValues", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Values", List.Distinct(#"Extracted Values"[questionLabel]), "questionLabel", "answerValues")
in
    #"Pivoted Column"

Importing Respondent Score Data into Excel 

  1. Using the same worksheet, go to a blank tab. 
  2. Go to the Data tab and select Get Data > From Other Sources > From OData Feed. 
  3. When the following prompt displays, paste the following URL for RespondentsScores (using your Measures Key) and click OK.

https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures(8866805)/RespondentsScores

873873
  1. Once the data is loaded, select the columns that you would like to keep and remove the rest. In this example, we want to keep the following: 
  • Respondentid 
  • Responsetime 
  • latentName 
  • latentScore 
  1. With the columns selected, click the menu of the Remove Columns button and select Remove Other Columns. The result is this:
982982
**TIP:** This may be a good time to make sure both queries are named, such as responses and scores. 
664664
  1. Next, pivot the Score Data: select the latentName column and click Pivot Column on the Transform tab.
906906

The result is a "Custom Extract" with respondent scores having one respondent per row with scores for all elements, SAT, and Outcomes. There is more you can do to fine-tune this data set. For example: 

  • Rename column headings 
  • Replace/format values 
  • Format response date/time 
13921392
Save some steps in the future by using this code in the Advanced Editor.
 
let 
    Source = OData.Feed("https://api.foresee.com/v1/bi/cxmeasure/odata.svc/Measures(8866805)/RespondentsScores"), 
    #"Removed Other Columns" = Table.SelectColumns(Source,{"respondentId", "responseTime", "latentName", "latentScore"}), 
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[latentName]), "latentName", "latentScore") 
in 
    #"Pivoted Column"

Don't close and load yet. 

Working with Both Scores and Responses (Merging Data) 

 
Once you have set up both scores and responses in the same workbook, you must merge them into one table (this allows you to create visualizations using both Sat and responses to CQs).  

Use Merge Queries as New.  

13911391

For Measurement surveys, in most instances, all respondents have been asked the Model Questions (scores) but may not have been asked CQs (responses).

For Engagement Surveys, each respondent is presented with a subset of driver questions. The different subsets are rotated across respondents such that sufficient scores for each element are collected as part of partitioning. For a given respondent, scores are only generated for the questions that appeared in the subset presented to the respondent. If a question is not asked to a respondent, the score will have a Null value for that respondent.

Therefore, it is recommended to perform a left-join of scores to responses based on respondent ID.

775775

Once the new query is created, you must open the merge table and select which columns you would like to use.  

14591459

The result is a "Custom Extract" with Respondent Answers and Scores Together.

Now you can close and load.

14311431