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.
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.
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.
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.
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.
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.
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.
Using these results, we designed the detail report which groups by “CategoricalQuestion” and “CategoricalAnswer”.
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