Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Maveryx Success Stories

Learn how Alteryx customers transform their organizations using data and analytics.
STORIES WANTED

Showcase your achievements in the Maveryx Community by submitting a Success Story now!

SUBMISSION INSTRUCTIONS

Identifying and Reporting Missing Tax IDs in 20 seconds

AlteryxAdvocacy
Alteryx
Alteryx
Name: Michael V Barone
Title: Data Scientist
Company: Paychex
logo.png
 
Overview of Use Case

We pay and file employment related taxes and tax returns for approximately 500,000 employers across the country. Including local municipalities, this accounts for thousands of tax agencies.  Each agency can assign their own tax ID number.  Using this tax ID number when making payments and filing returns is essential to employers' accounts being properly credited and kept in good standing with the taxing authorities. It is imperative to identify our clients with missing tax ID numbers, and send correspondence to our branch locations so they can obtain and enter the proper tax ID number for each taxing agency.

 
Describe the business challenge or problem you needed to solve

With data being stored in clustered environments, hours were being spent sorting through several reports (one per cluster) that identified clients with missing tax IDs. More hours were then spent splitting these clients out into separate spreadsheets based on our locations that serviced them. And then even more hours were spent compiling and emailing these spreadsheets out to the various locations. 

 
Describe your working solution

Create a repeatable app that can be run on-demand by the applicable business unit via Alteryx Gallery. The app would require no inputs (parameters) other than the “push of a button”.

 

THE MODULE

Step I

  1. Union all the reports (CSVs) in one input tool using a wildcard
  2. Perform data manipulation
  3. Left (outer) join to service center file

Capturar1.PNG

 

 

Step II

  1. Bring in (join) location email addressesCapturar2.PNG

     

Step III

  1. Using a Formula Tool, update the “Email_To” field dynamically, based on the DSC field.
  2. In the same Formula Tool, create a text string for the subject field, for each email, that will dynamically insert the location number.
  3. In the same Formula Tool, create a text string for each record, that will detail the spreadsheet that particular record will go on; there will be multiple spreadsheets (one per location/center).

Capturar3.PNG

 


Step IV

  1. Output the spreadsheets to a storage location for internal purposes.
  2. Email the locations/service centers their spreadsheets.


Capturar4.PNG

 

 

THE APP

Capturar5.PNG

 

Describe the benefits you have achieved

The process that once took about 5 hours to complete, now takes about 20 seconds from start (compiling the reports) to finish (emailing every effected location with a list of their missing tax ID clients). The app has eliminated all manual work involved with the process and can be run on-demand by the business unit.

 
Comments
papalow
8 - Asteroid

I like the way you documented the solution.  Thanks for sharing your use case.

mbarone
16 - Nebula
16 - Nebula

You're welcome @papalow - glad you enjoyed it!