Replaying feedback into Rant & Rave using the Waterfall API

Sometimes there is an issue with a workflow where feedback stops coming from Waterfall into Rant and Rave and therefore a client will stop receiving feedback. Once the issue is fixed for future feedback, the job of replaying the missing feedback begins. Currently there is not an option in Waterfall to replay this feedback into Rant and Rave and therefore TechSol must manually replay this. The process is:

1. Get all of the data for the affected shortcodes (workflows) from Waterfall from the messageDetails collection between the time range the issue occurred.
2. Extract the valid feedback from all of the data, removing messages that aren’t from the customer and invalid responses from customers (may need to write a script to do this). Save this in a csv and check the responses will be valid, changing things like emojis to Unicode encoding etc.
3. Once you have the data you will first need to check for opt outs as we need to ensure the customers that have requested to be opted out have had that actioned for them. If they haven’t then an SQL script is required to opt them out in R&R.
4. Then we need to replay the feedback into R&R using the Waterfall API. If there is a significant amount of data that needs replaying you may need to write a script to do this. Then check the data has all been replayed correctly, some comments may not look the same so they may need to be changed in the next step,
5. Finally we can run an SQL script to change the created date to the original created date. We need a file that needs at least the recipient and the created date. Then follow the standard historical update process. We can also clean up and feedback items that didn’t populate correctly from the previous step.

Pulling the data from Waterfall
Before you try to grab the data you will need to know what workflows were affected, the time range for the issue. We can then build a MongoDB query.

Using one of the active reporting databases for the correct region we will use the messageDetail collection to grab the data we require. The fields we need are: _id, body, date, solicitationUuid, msisdn, messageType. The query will need to look something like the below:
 

db.messageDetail.find({
    shortCode: {
        $in: [
            DBRef("shortCode", ObjectId("<SHORTCODE1>")),
            DBRef("shortCode", ObjectId("<SHORTCODE2>”)),
        ]
    },
    date: {
        $gte: ISODate("<AFTERTHISDATE>"),
        $lt: ISODate("BEFORETHISDATE")
    },
    solicitationUuid: {$exists: true}
}, {
    "_id": 1,
    "body": 1,
    "date": 1,
    "solicitationUuid" :1,
    "msisdn" : 1,
    "messageType" : 1
});

Then export the data as a csv file and attach it to the ticket.

Extracting the valid feedback

With the csv file you now have we need to get it to a state that it only contains valid feedback. In it’s current state it has messages sent out to recipients that have not had any reply and invalid responses that we do not want to push into R&R. Firstly you should open the csv file and click the mobileNumber column and then format, then click format cells, change the category to Number and decimal places to 0 and then ok.
Inline Image
Then click sort and filter, click custom sort and click Expand the selection and then sort.
Create this rule:
Inline Image
And click OK. Every customer’s interaction should now be grouped together and sorted in date order. The csv should be a lot easier to read and understand. Manually check that they are in order and grouped correctly. Then save this csv as an ordered version of the data dump.

Now we need to extract the valid feedback that would have been passed into R&R if there wasn’t an issue. To do this we need to know how the workflows that had a problem were intended to work. Usually, the first question sent to the customer asks them to provide a score between a range, the customer must provide a value between this range, if they don’t they will be prompted again to provide a score between the given range. Upon entering a score between this range we will have acceptable feedback. If they provide no further answers then this feedback would still go into R&R. The next questions then become more customised to the workflow. They may ask a yes or no question and then for a comment. Clarify with support how the workflows behave as they will most likely understand them better and use the conversations in the ordered csv you created to confirm this behaviour. They will also have an opt out question at the end. If the customer types STOP then they will be opted out. Due to the issue with the workflow they may have not been opted out in R&R so we will need to also collect this data so we can check if they have been opted out and if not we will have to opt them out via a database change.

To do this for Toyota in TS-13208 I used this script: https://bitbucket.org/uplandsoftware/techsol-scripts/src/master/valid_feedback_generator.py
 

Please note that this did not produce the final output as it generated some erroneous records (the yes or no answer wasn’t correct in some cases). I suggest creating a script from scratch because of the difference in workflows but this can be used as a template. Or it could be changed and adapted to be a more generic script that can be used for many workflows.

You can see the valid feedback csv attached on this ticket: https://uplandsoftware.atlassian.net/browse/TS-13208

Opt-Outs

When you have the data there should be an opt out column where the customer has typed STOP to be opted out of all communication with the customer. We need to check if these recipients have been opted out - most likely they have not been. You can filter the opt out column to show only the records with a STOP value to grab the recipients that have requested to be opted out. This can then be used to check the system_solicitation_contacts table. Then write the standard opt out script for any that need updating and inserting.

Replaying the feedback

To replay the feedback into R&R we need to use the Waterfall API, recreating the calls with the valid feedback within the JSON. There is documentation for this here: https://uplandsoftware.atlassian.net/wiki/spaces/CXMEMEA/pages/6058737665/Deployable+Waterfall+Adapter

There are example API calls there to test however we will need to change them a little to test how we want to make the API calls. We will be using the techops project to test creating feedback. Within the techops project there should be a user with the username ts_techops, this is the user you can use to replay feedback. Go into the user management tool and reset the password for this user, also change the email address. The user is how we control what project the feedback gets created in. Then go into the database and look at some solicitations from the Techops Project (ID: 435) you can see them with this query:

SELECT

    *

FROM system_solicitations

WHERE project_id = 435

AND used = 0

ORDER BY created DESC

LIMIT 10

Grab any solicitation’s uuid field as we’ll need it for the Waterfall API test. Then to create feedback we will use the below API call (replacing everything marked to be replaced, the password and the solicitation_uuid):
 

curl \

  --silent --show-error \

  --request POST \

  --header 'Content-Type: application/json' \

  --header "Accept: application/json" \

  --user ts_techops:<REPLACE_WITH_PASSWORD> \

  --data "{ \"fields\": { \"score\": 10, \"comment\": \"TechSol Test\" }, \"solicitation_uuid\": \"<REPLACE_WITH_SOLICITATION_UUID>\" }" \

  https://eu-api.rantandrave.com/waterfall/api/rest/feedback/v1 \

  | jq

These can be run in your local terminal and it should return an rr_id value if it is successful. This is the uuid of the feedback generated. If you look at the solicitation with the solicitation_uuid you chose, the used field’s value should have changed to a value of 1 and looking at the project feedback table with the rr_id provided used as the uuid or the solicitation_uuid used you should be able to see the feedback record. It will also have a verbatim record because the API has triggered the sentiment engine.

To Update an item of feedback we would use the below:

curl \

  --silent --show-error \

  --request PUT \

  --header 'Content-Type: application/json' \

  --header "Accept: application/json" \

  --user ts_techops:<REPLACE_WITH_PASSWORD> \

  --data "{ \"fields\": { \"score\": 1 }, \"rr_id\": \"<VALUE_FROM_THE_FIRST_CALL_ABOVE>\" }" \

  https://eu-api.rantandrave.com/waterfall/api/rest/feedback/v1 \

  | jq

This is a PUT request rather than a POST so should only update a feedback record.

Please note that Unicode characters must be encoded, \u(4 digit number). You should test this.

Create an API user