Guest post: Custom Survey Reporting

Merial
The following blog post was written by Ken Champion, Senior Business Analyst for MERIAL IS. Ken was kind enough to assemble this “how-to” post following requests from attendees that watched Courtney Carter’s Idea Conference presentation where she shared how MERIAL is using ClickDimensions. (Watch Courtney’s presentation here.)

 

1. Introduction

As a ClickDimensions user, you hopefully allow a recipient to update their subscription preferences for their emails. However, wouldn’t you like to get some feedback as to WHY they opted-out? Recipient feedback could be vital to tailoring your future messaging or service/product offerings.

At Merial, we use ClickDimensions to send monthly reminders to pet owners to treat their pets with Merial’s family of pet care products. When a consumer opts-out, we want to understand why.

In this article, we’ll show you how we used a ClickDimensions’ survey to get consumers’ reasons for opting-out. We’ll also show you how we developed a custom report to measure those responses.

“Pick one choice” survey questions vs “tell us more” survey questions

As you know, it is easy to count the responses to a “pick one choice” survey question. In ClickDimensions, this might be a question of the “Radio” type. However, a “tell us more” question (“text area” type) allows you to get the detailed and specific consumer response, but you cannot really count/categorize free text responses.

So, wouldn’t it be great to combine these two types of answers into one consolidated response? We did just that, and we’ll show how.

 

2. Connect the Consumer Survey to the Subscription Preference Page

When a Merial consumer clicks “Submit” on our Subscription Preference form, they are automatically taken to a survey form where we ask for their feedback as to why they might be opting out.

Pic1

To enable the automatic launch of the “Reason for Opt-out” survey page, we put its “embed” link into the “post redirect” field within the “Confirmation Text” configuration of the subscription preference form.

Pic2

 

3. “Coupling” the Survey Questions

On our “Reason for Opt-out” survey, we ask two questions. One is a categorized “select one choice” question, and the other is a “tell us more” free text question.

Pic3

The categorized question allows us to accurately measure responses by category, while the “tell us more” gives us the personal and specific feedback.

Here’s where it starts to get tricky. We needed a way to “couple” the category question, e.g. “What is the primary reason for canceling?” with the free text “Feel free to tell us more.” question.

The corresponding configuration of the “Web Content” for the Survey page is hosted by ClickDimensions, so we could not query CRM data to see which two questions were on the survey form. We also did not want to add customizations to the ClickDimensions “Survey Question” entity. So we applied a very low-tech approach of simply appending the phrase “(Detail)” to the “text area” question name.

The “Reason for Opt-Out” survey is one of our surveys, but we also developed a similar two question survey for a consumer support form. The screenshot below shows the four ClickDimensions Survey Questions which constitute the two questions on the two surveys.

Pic4

Then we coded the query within our custom survey report to pair up the “Reason for Out-out” question and the “Reason for Opt-out (Detail)” question into a “couplet”. This gives us the ability to couple the corresponding answers too. If we decide to add more “category/tell us more” question pairs to our surveys, our report will pick them up automatically if we follow our naming rule.

 

4. SQL Queries on the Report

Our custom SQL Server Reporting Services (SSRS) report uses SQL queries rather than Microsoft’s FetchXml to gather the data it needs. Adapt the queries below to get the information for your own report.

4.1    List of Posted Surveys

Our report allows the user to select from a list of Posted Surveys. If you have Posted Surveys which you do not wish to include in your report, adjust your query filter as needed.

Pic5

SELECT
   Distinct SurveyName = CDI_Name
FROM FilteredCDI_PostedSurvey WITH (NOLOCK)
WHERE StateCodeName = ‘Active’
ORDER BY CDI_Name

 

4.2 Total the Categorized Responses

The example query below shows how we sum the categorical responses by each answer.

DECLARE @Input_SurveyName NVARCHAR(100)
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
SET @Input_SurveyName = ‘Consumer Opt-out Reason’
SET @BeginDate = ‘2014/08/01’
SET @EndDate = ‘2014/09/30’

–Total the categorized responses
SELECT
         ShortCategoricalQuestion = SA.cdi_surveyquestionidname
         ,CategoricalQuestion = SA.cdi_question
         ,CategoricalAnswer = IsNull(SA.cdi_value, ‘not answered’)
         ,ResponseCount = COUNT(1)
FROM FilteredCdi_postedsurvey PS WITH (NOLOCK)
INNER JOIN FilteredCdi_surveyanswer SA WITH (NOLOCK)
         ON PS.Cdi_postedsurveyId = SA.cdi_postedsurveyid
INNER JOIN FilteredCDI_SurveyQuestion SQ WITH (NOLOCK)
         ON SA.cdi_surveyquestionid = SQ.cdi_surveyquestionid
WHERE PS.Cdi_name = @Input_SurveyName
         AND PS.CreatedOn BETWEEN @BeginDate AND @EndDate
         AND PS.StateCodeName = ‘Active’
         AND SQ.CDI_TypeName NOT IN (‘Text Area’, ‘Text Box’)
GROUP BY SA.cdi_surveyquestionidname, SA.cdi_question, SA.cdi_value
ORDER BY ResponseCount DESC

 

We can use the results from the above query for our bar chart.

Pic6

 

4.3  Response Details

This SQL retrieves the categorical and detailed answers and combines them as one result record.

–Categorized Answers (CA) and Detailed Answers
SELECT
         CA.*
         ,DetailedAnswer =
                  –Clean up spaces that might have been saved.
                  –Replace nbsp with regular spaces and then trim
                           (SELECT
                           CASE WHEN LEN(LTRIM(REPLACE(SA.cdi_value, CHAR(160), CHAR(32)))) > 0 THEN
                                    LTRIM(LTRIM(REPLACE(SA.cdi_value, CHAR(160), CHAR(32))))
                           ELSE
                                    ‘No detail’
                           END
                  FROM FilteredCdi_surveyanswer SA WITH (NOLOCK)
                  –This where clause couples the “categorical” answer with the “detail” answer.
                  WHERE CA.cdi_postedsurveyid = SA.cdi_postedsurveyid
                           AND SA.cdi_surveyquestionidname = CA.ShortCategoricalQuestion + ‘ (Detail)’)
FROM
         (SELECT
                  PS.CDI_PostedSurveyId
                  ,SurveyAnswerId = CAST(SA.CDI_SurveyAnswerId AS NVARCHAR(50))
                  ,CategoricalQuestion = SA.cdi_question
                  ,ShortCategoricalQuestion = SA.cdi_surveyquestionidname
                  ,CategoricalAnswer = IsNull(SA.cdi_value, ‘not answered’)
                  ,LeadId = CAST(SA.cdi_leadid AS NVARCHAR(50))
                  ,SA.cdi_leadidname
                  ,SA.cdi_accountid
                  ,SA.cdi_accountidname
                  ,SA.createdon
         FROM FilteredCdi_postedsurvey PS WITH (NOLOCK)
         INNER JOIN FilteredCdi_surveyanswer SA WITH (NOLOCK)
                  ON PS.Cdi_postedsurveyId = SA.cdi_postedsurveyid
         INNER JOIN FilteredCDI_SurveyQuestion SQ WITH (NOLOCK)
                  ON SA.cdi_surveyquestionid = SQ.cdi_surveyquestionid
         WHERE PS.Cdi_name = @Input_SurveyName
                  AND PS.CreatedOn BETWEEN @BeginDate AND @EndDate
                  AND PS.StateCodeName = ‘Active’
                  AND SQ.CDI_TypeName NOT IN (‘Text Area’, ‘Text Box’)
         ) CA     –CategoricalAnswer

  

Here is what the important fields of our result set look like. Notice how the “DetailedAnswer” has been coupled to the “CategoricalAnswer” within the same record.

Pic7

 

Using these results, we designed the detail report which groups by “CategoricalQuestion” and “CategoricalAnswer”.

Pic8

 

We hope we’ve inspired you to realize that an opt-out might be a great opportunity to get recipient feedback, and that by pairing a “pick one” survey question with a “tell us more” question, you can get both measurable and detailed responses from your consumers!

 

 

Powered by WPeMatico