Surveys in specific Date Range

Cartergan

Good morning, 

I currently have a dataset being created from our Qualtrics connector that counts the number of surveys and averages scores from specifi questions in a given time period. The time period we go off of would be between the Last Day of the Quarter (Period End) and 180 days before the Last Day of the Quarter (Period Begin.) Below is my code I am using in the ETL process:

SELECT 
   `Agent` ,
    U.`Position`,
    U.`BranchDescription`,
    (ADDDATE(`LastDayOfQuarter`, -180)) AS MinDate, 
    (`LastDayOfQuarter`) AS MaxDate,
    COUNT(DISTINCT `Response ID`) as SurveyCount,
    AVG(CASE WHEN `Question ID` = 'Q1_2' THEN `Answer Value` END) AS EngagedScore, 
    AVG(CASE WHEN `Question ID` = 'Q1_3' THEN `Answer Value` END) AS KnowledgableScore, 
    AVG(CASE WHEN `Question ID` = 'Q1_6' THEN `Answer Value` END) AS ValuedScore
FROM `qualtrics_responses`

LEFT JOIN `date` D
ON (D.`Date`) = (DATE(`End Date`))

LEFT JOIN `users` U 
ON U.`UserID` = `Agent ID`

WHERE `Agent` IS NOT NULL 
AND DATE(`End Date`) <= (`LastDayOfQuarter`) 
AND DATE(`End Date`) >= (ADDDATE(`LastDayOfQuarter`, -180))

GROUP BY `Agent`,  (ADDDATE(`LastDayOfQuarter`, -180))

I have run into an issue where if the Agent does not have any surveys this year, my query is not creating the results for the current range of 10/03/2019 - 03/31/2020. I have tried a few different ways starting with my Date table but cannot seem to figure out a way that works. I know the issue stems from the way I am joining to my date table, but again, I have hit a wall and am hoping to find some answers here. Thank you in advance!

MarkSnodgrass

@Cartergan 

The first thing that jumps out to me is that you need to change your table listing in your FROM clause. In order to get all of the dates from the dates table, that table needs to be listed first. Your FROM and LEFT JOIN syntax should look like this:

FROM `date` D
LEFT JOIN  `qualtrics_responses`
ON (D.`Date`) = (DATE(`End Date`))

See if that does the trick for you.

Cartergan

@MarkSnodgrass 

Unforunatley that didn't work. I beleive the main problem is that since the end date (date survey received) is being joined with date, since there have been no surveys for an agent in 2020, the most recent PeriodEnd date it is pulling is December 31, 2019. 

 

I'm wondering if I should make some sort of CASE statement checking that the end date is between the period I am looking at, and if so to then count it? 

GrantSmith

@Cartergan -

 

You'll likely need to do a full outer join of the dates table and a distinct list of your agents to get the possible combinations of agent and dates. You can do this as a transform and then select that table and left join it to your qualtrics_responses table on agent and date. This should populate all possible records even if an agent doesn't have a response on a specific date (although it'll greatly increase your data set size).

 

Depending on how your data is structured you might be able to tweak your join instead to use a COALESCE(`End Date`, CURRENT_DATE) assuming you have records with a null end date for active records.

MarkSnodgrass

@Cartergan Do you mind re-posting your SQL with a table alias for each column? I'm having trouble discerning which columns are from which table and that will make a difference when it comes to your WHERE clause, etc...

Cartergan

@MarkSnodgrass 

Sorry about that! It's a bad habbit I am trying to break. Here is the updated code:

SELECT 
    Q.`Agent` ,
    U.`Position`,
    U.`BranchDescription`,
    (ADDDATE(D.`LastDayOfQuarter`, -180)) AS MinDate, 
    (D.`LastDayOfQuarter`) AS MaxDate,
    COUNT(DISTINCT Q.`Response ID`) as SurveyCount,
    AVG(CASE WHEN Q.`Question ID` = 'Q1_2' THEN Q.`Answer Value` END) AS EngagedScore, 
    AVG(CASE WHEN Q.`Question ID` = 'Q1_3' THEN Q.`Answer Value` END) AS KnowledgableScore, 
    AVG(CASE WHEN Q.`Question ID` = 'Q1_6' THEN Q.`Answer Value` END) AS ValuedScore
FROM `qualtrics_responses` Q

LEFT JOIN `date` D
ON (D.`Date`) = (DATE(Q.`End Date`))

LEFT JOIN `users` U 
ON U.`UserID` = Q.`Agent ID`

WHERE Q.`Agent` IS NOT NULL 
AND DATE(Q.`End Date`) <= (D.`LastDayOfQuarter`) 
AND DATE(Q.`End Date`) >= (ADDDATE(D.`LastDayOfQuarter`, -180))

GROUP BY Q.`Agent`,  (ADDDATE(D.`LastDayOfQuarter`, -180))

---

@GrantSmith I'll give that a try now and let you know if it works!

MarkSnodgrass

Not sure if this will work since I don't have the data to work with, but might get you closer. Basically, I made a subquery that gets all the users and their survey data if they have it. I then left join it to the date table and moved the where clause information to part of the left join criteria. I am also hoping the agent name is in the user table since you can't really pull it from the survey data if they never completed the survey. 

Hope this helps.

 

SELECT 
    UQ.`Agent` ,
    UQ.`Position`,
    UQ.`BranchDescription`,
    (ADDDATE(D.`LastDayOfQuarter`, -180)) AS MinDate, 
    (D.`LastDayOfQuarter`) AS MaxDate,
    COUNT(DISTINCT UQ.`Response ID`) as SurveyCount,
    AVG(CASE WHEN UQ.`Question ID` = 'Q1_2' THEN UQ.`Answer Value` END) AS EngagedScore, 
    AVG(CASE WHEN UQ.`Question ID` = 'Q1_3' THEN UQ.`Answer Value` END) AS KnowledgableScore, 
    AVG(CASE WHEN UQ.`Question ID` = 'Q1_6' THEN UQ.`Answer Value` END) AS ValuedScore
FROM `date` D
LEFT JOIN
(SELECT U.`Agent` , --hoping this column exists for the agent name
    U.`Position`,
    U.`BranchDescription`,
    Q.`Response ID`,
    Q.`Question ID`,
    Q.`Answer Value`,
    Q.`End Date`
    FROM `users` U 
LEFT JOIN `qualtrics_responses` Q
ON U.`UserID` = Q.`Agent ID`
    ) UQ  
ON (D.`Date`) = (DATE(UQ.`End Date`))
AND UQ.`Agent` IS NOT NULL 
AND DATE(UQ.`End Date`) <= (D.`LastDayOfQuarter`) 
AND DATE(UQ.`End Date`) >= (ADDDATE(D.`LastDayOfQuarter`, -180))
GROUP BY UQ.`Agent` ,
    UQ.`Position`,
    UQ.`BranchDescription`,
    (ADDDATE(D.`LastDayOfQuarter`, -180)), 
    (D.`LastDayOfQuarter`)

 

 

jaeW_at_Onyx

Sorry late for joining late in the game...

Question, do you know for a fact that you have EndDates for all your Data?  If you don't have an EndDate for all your data, then any type of JOIN should filter it out (hence I think why you ended up putting Date on the LEFT side.

 

I think what I would do is create a EndDate_clean on the fact table such that:  

SELECT
a.*
coalesce( end_date, date_add(now() )  as EndDate_clean //or similar
FROM
factTable a

This way when you do your LEFT JOIN  on factTable any activity that doesn't have an EndDate gets lumped in with the current quarter.  it also makes it visible in your output dataset, the derived version of the EndDate while retaining the actual version of the EndDate.

 

If you Output this as a 'final dataset', then you can use a FUSION to JOIN your transactions to your date table (on EndDate_clean) and any other relevant tables.  Now you can do aggregations in cards AND it keeps your logic way more transparent to the end users.

 

jae.wilson@domo.com