Automated SMS Survey System - Data Collection & Reporting

[MarkEugene9000]

Name: Mark Gauzens

Title: Director, Research Innovation

Company: WestGroup Research

 

Overview of Use Case

WestGroup Research adopted Alteryx with a primary goal of speeding up the time it takes to complete repetitive data tasks. As a data-oriented company with several salaried staff working full time on nothing but data processing, the opportunity seemed huge, Not only could it increase efficiency but it could boost the quality of work being done throughout the company, without the need for intensive programming-language-training or 3rd-party-outsourcing.

Indeed, we have seen our use of Alteryx increase dramatically since implementation into increasingly complex systems and processes, the latest of which is a self-contained SMS (text message) survey apparatus for one of our largest clients. By self-contained, we mean that Alteryx is the workhorse connecting all data-related processes from beginning to end for this particular project. The project director does not have to log into any other platforms outside of Alteryx in order to execute the project tasks, because Alteryx is so effectively integrated with said platforms.

For this use case, we use Alteryx to:

1. Consume data files from 30 of our client's subcontractors
2. Parse all data received on a record-by-record basis and mark each with one of several disposition codes based on several logic rules
3. Add in dispo'd records to a master data set
4. Randomly sample eligible records to be sent to our data collection platform via API call (sends 2-way SMS survey)
5. Periodically pull down newly collected SMS response data and add it to a master response data set
6. Update Tableau data extracts on our Tableau Server for reporting
    

Describe the business challenge or problem you needed to solve

Our client came to us requesting a customer feedback solution that they wanted to implement among their 30 subcontractors for a particular service (hospital network - medical transports). A SMS survey methodology was selected and we knew that the only way to build this without needing an army of data processors and budget-busting man hours was to utilize Alteryx. 

The primary project challenges from a business perspective included:

1. How to efficientlyabsorb data from 30 different groups on a regular basis, potentially from groups who would be providing it each in a different format than others?
2. How to efficientlyconduct a proper sampling protocol and send surveys to those records for 30 different groups?
3. How to efficientlyupdate reporting documents for 30 different groups on a monthly, quarterly, and annual basis?

Prior to Alteryx, this project frankly would not have been something we could have done ourselves. We would have had to hire a 3rd party developer at expensive rates, thereby lowering the possibility we would win the project in the first place. Alteryx put us in a position to win the project in the first place.

Describe your working solution

Using Alteryx Designer, we have developed three (3) core workflows for this project which encompass all of the tasks we would have otherwise had to do in a painstaking, manual fashion. Alteryx is "connected" to a 2-way SMS survey platform via API calls (download tool) and it is also connected to our Tableau Server for back-end reporting.

Here is a step by step process of what happens for this project:

1. A secure network directory is hosted by WestGroup wherein our client's subcontractors can post their data. Data is medical in nature and thus extremely sensitive. Each subcontractor receives a unique set of credentials to access their own folder on the secure directory where they can post files. Currently, we receive files twice per week and are expected to send surveys out twice per week shortly after receiving the new files.

2. Alteryx looks through each subcontractor's directory to search for new .csv files. If it finds one, it parses the new data contents and updates a master data set with new records.

3. Many of the subcontractors provide the data in a slightly different format, so we use Alteryx as-needed to rename and reorder headers and columns to match a uniform template.

4. Alteryx determines if a new record is eligible to receive a survey or not, and throughout the workflow it will figure out:

• Whether a valid phone number was provided for the new record
• Whether that phone number has already received a survey in the recent past (6-month threshold)
• Whether any other exclusionary rules apply to that record such that it should not receive a survey

5. Alteryx randomly sorts eligible records and selects a maximum of 14 per subcontractor to receive the SMS survey (a random sample is completed so we can ensure we are not over-surveying in accordance with the scope of the project contract).

Eventually, after steps 1 through 5 are completed, the new data files for all 30 subcontractors have gone through an overarching process shown below:

6. The project team requested an easy way to keep track of successes/failures every time the data absorption process runs. To that end, we have incorporated a Gmail macro tool into the workflow which summarizes what happened that day and then sends a message to the team from a company-owned Gmail address. This allows them to see how things are going and reach out to any of the 30 subcontractors if an issue has arisen due to bad data, etc.

7. Alteryx sends the appropriate data to the SMS data collection platform via API call, which automatically triggers a SMS survey to those phone numbers. Participants get a text as follows, and can fill in the survey if they desire. 

8. A data pull-down Alteryx workflow was developed to periodically pull down new SMS responses, in addition to information about whether records were delivered or not (valid vs. invalid phone numbers), as this information is important when calculating response-rates.

9. A 3rd and final workflow merges the master response data set with the master disposition data from the front-end to create a comprehensive reporting data set which is formatted in two different ways before being sent to Tableau Data Server.

10. Workbooks hosted on Tableau Server automatically display the most current data as soon as the Alteryx reporting workflow is done. In addition, we call a .bat script using Alteryx to request 30 different .PDF files from Tableau Server as static, company-specific reports. 

Describe the benefits you have achieved

 

We have impressed an important client with the ability to build a widely scaling data collection solution to accommodate 30 of their subcontractors. We have done so in a cost-effective fashion, without the need for advanced programming proficiency or 3rd party assistance.

More generally, we have brought our own data processing skill sets as a company to the leading edge, and the reason we have been able to do it is thanks to Alteryx. This use case is just one of many we are currently thankful to have Alteryx for.

Related Resources

I would like to thank the helpful community forum participants for repeatedly helping us with our various Alteryx use cases.