Past Analytics Excellence Awards

Author: Alexandra Wiegel, Tax Business Intelligence Analyst 
Company: Comcast Corp

Awards Category: Best Business ROI

 

A Corporate Tax Department is not typically associated with a Business Intelligence team sleekly manipulating and mining large data sources for insights.  Alteryx has allowed our Tax Business Intelligence team to provide incredibly useful insight to several branches of our larger Tax Department. Today, almost all of our data is in Excel or csv format and so data organization, manipulation and analysis have previously been accomplished within the confines of Excel, with the occasional Tableau for visualization. Alteryx has given us the ability to analyze, organize, and manipulate very large amounts of data from multiple sources.  Alteryx is exactly what we need to solve our colleague’s problems.

Describe the problem you needed to solve

Several weeks ago we were approached about using Alteryx to do a discovery project that would hopefully provide our colleagues further insight into the application of tax codes to customer bills. Currently, our Sales Tax Team uses two different methods to apply taxes to two of our main products respectively. The first method is to apply Tax Codes to customer bill records and then run those codes through software that generates and applies taxes to each record. The second method is more home-grown and appears to be leading to less consistent taxability on this side of our business.

 

Given that we sell services across the entire country, we wanted to explore standardization across all our markets. So, our Sales Tax team tasked us with creating a workflow that would compare the two different methods and develop a plan towards the goal of standardization and the effect it would have on every customer’s bills.

 

Describe the working solution

Our original source file was a customer level report where the records were each item (products, fees, taxes, etc.) on a customer’s bill for every customer in a given location. As it goes with data projects, our first task was to cleanse, organize, and append the data to make it uniform.

 

 

The next step was to add in the data from several data sources that we would ultimately need in order to show the different buckets of customers according to the monetary changes of their bills. Since these sources were all formatted differently and there was often no unique identifier we could use to join new data sources to our original report. Hence, we had to create a method to ensure we did not create duplicate records when using the join function. We ended up using this process multiple times (pictured below)

 

 

And so, the workflow followed. We added tax descriptions, new codes, and other information. We added calculated fields to determine the amount of tax that should be owed by each customer today, based on our current coding methods.

 

 

After we had layered in all the extra data that we would need to create our buckets, we distinguished between the two lines of business and add in the logic to determine which codes, at present, are taxable.

 

 

For the side of our business whose taxability is determine by software, you will notice that the logic is relatively simple. We added in our tax codes using the same joining method as we did above and then used a single join to a table that lists the taxable codes.

 

 

For the side of our business whose taxability is determine by using our home-grown method, you can see below that the logic is more complicated. Currently, the tax codes for this line of business are listed in such a way that requires us to parse a field and stack the resulting records in order to isolate individual codes. Once we have done this we can then apply the taxability portion. We then have to use this as a lookup for the actual record in order to determine if a record contains within the code column a tax code that has been marked as taxable. Or in other words, to apply our home-grown taxability logic is complicated, time consuming, and leaves much room for error.

 

 

Once we stacked all this data back together we joined it with the new tax code table. This will give us the new codes so that the software can be used for both lines of business. Once we know these new codes, we can simulate the process of the software and determine which of the new codes will be taxable.

 

 

Knowing whether or not codes are taxable helps us hypothesize about how problematic a geographic location may end up being for our team, but it does not tell us the dollar amount of taxes that will be changing. To know this we must output files that will be run through the real software.

 

Hence, once we have completed the above data manipulation, cleansing, and organization, we extract the data that we want to have run through the software and reformat the records to match the necessary format for the software recognition.

 

 

We created the above two macros to reformat the columns in order to simply this extensive workflow. Pictured below is the top macro. The difference between the two resides in the first select tool where we have specified different fields to be output.

 

 

After the reformatting, we output the files and send them to the software team.

 

 

When the data is returned to us, we will be able to determine the current amount of tax that is being charged to each customer as well the amount that will be charged once the codes are remapped. The difference between these two will then become our buckets of customers and our Vice President can begin to understand how the code changes will affect our customer’s bills.

 

Describe the benefits you have achieved

Although this project took several weeks to build in Alteryx, it was well worth the time invested as we will be able to utilize it for any other locations. We have gained incredible efficiency in acquiring insight on this standardization project using Alteryx. Another benefit we have seen in Alteryx is the flexibility to make minor changes to our workflow which has helped us easily customize for different locations. All of the various Alteryx tools have made it possible for the Tax Business Intelligence team to assist the Tax Department in accomplishing large data discovery projects such as this.

 

Further, we have begun creating an Alteryx app that can be run by anyone in our Tax Department. This frees up the Tax Business Intelligence team to work on other important projects that are high priority.

A common benefit theme amongst Alteryx users is that Alteryx workflows save companies large amounts of time in data manipulation and organization. Moreover, Alteryx has made it possible (where it is impossible in Excel) to handle large and complicated amounts of data and in a very user friendly environment. Alteryx will continue to be a very valuable tool which the Tax Business Intelligence team will use to help transform the Tax department into a more efficient, more powerful, and more unified organization in the coming years.

 

How much time has your organization saved by using Alteryx workflows?

We could never have done this data discovery project without using Alteryx.  It was impossible to create any process within Excel given the quantity and complexity of the data.

 

In other projects, we are able to replicate Excel reconciliation processes that are run annually, quarterly, and monthly in Alteryx.  The Alteryx workflows have saved our Tax Department weeks of manual Excel pivot table work.  Time savings on individual projects can range from a few hours to several weeks.

 

What has this time savings allowed you to do?

The time savings has been invaluable.  The Tax Department staff are now able to free themselves of the repetitive tasks in Excel, obtain more accurate results and spend time doing analysis and understanding the results of the data.  The “smarter” time spent to do analyses will help transform the Tax Department with greater opportunities to further add value to the company.

Author: Aaron Harter (@aaronharter), Media Ops Manager

Company: Quigley-Simpson

 

Awards Category: Best Use of Alteryx Server

 

We leverage our Alteryx Server to design and implement custom apps that allow for any team member at the Agency to benefit from the power of Alteryx, without the programming knowledge necessary to construct a solution on their own.  Analytic apps allow for all employees at Q-S to leverage the capabilities of Alteryx in a fun and easy to use interface.

 

 

Describe the problem you needed to solve 

Any company can own, buy or hold data. Finding creative applications to use data to drive informed decision making and find opportunities in a market is what separates the wheat from the chaff, regardless of industry.

 

Quigley-Simpson is an advertising agency in the highly fragmented media industry and the unique problems include managing rapidly changing marketplaces with dozens of disparate data sets and supporting many teams with varying reporting needs. The Media Operations team has been tasked to implement custom solutions to improve efficiency and make sense out of the big data coming in the agency.

 

Media measurement is highly reliant on quality data sourcing, blending and modeling, and we have been able to use Alteryx as a centralized environment for handling and processing all of this data across many formats. We have worked closely with key stakeholders in each department to automate away all of their "pain points" relating to data and reporting and interacting with our media buying system.

 

Describe the working solution

Some of our apps join our media buy, audience delivery history with our client's first party data and the related third party audience measurement data from Nielsen. Other third party data sources we leverage include Digital and Social Media metrics, GfK MRI demographic and psychographic market research, TIVO TRA set-top box data combined with shopper loyalty data, MediaTools authorizations and strategic planning on the brand level, AdTricity digital feedback on pre-, mid-, and post- roll online video campaigns, and comScore digital metrics for website activity.

 

 

Expediting the processing, summarizing, cross-tabbing and formatting of these data sets has added an element of standardization to our reporting which did not exist previously while improving the speed and accuracy. An app we built for the one of our teams produces over 50 reports, ready for distribution, in less than 3 min, replacing a process that used to take a full day to accomplish.

 

 

Additionally, we are using spatial tools to analyze delivery and performance of pilot Programmatic Television test, which aggregates local market TV inventory to represent a national footprint. Several of our workflows blend and prep data for visualization on our in-house "Data Intelligence Platform" which is powered by Tableau. This is then used by our media planners and buyers to optimize campaigns to meet goals and exceed client expectations.

 

The flexibility to build out apps or dashboards, depending on the needs statement of the end user, has been phenomenal and very well received at the Agency.

 

 

Describe the benefits you have achieved

Now that we are an Alteryx organization, we are replacing all of our outdated processes and procedures with gracefully simple workflows that are propelling the Agency to the forefront of technology and automation. Our report generating apps have improved the accuracy, reliability, and transparency of our reporting. The log processing apps have saved thousands of hours of manual data entry. Now that our workforce has been liberated from these time consuming, monotonous tasks, we are wholly focused on growing our clients' business while better understanding marketplace conditions.

 

Streamlining the workflow processes has allowed for drastically reduced on-boarding times while maintaining data integrity and improving accuracy. It has been a primary goal to give all employees the tools to increase their knowledge base and grow their careers by improving the access to data they use for daily decision making, a goal we are achieving thanks in large part to our Alteryx Server.

 

2016 Alteryx Server app totals (as of 4/22/16):

• Teams using apps = 7
• Number of apps = 44
• 2016 app run count = 1,794
• 2016 time savings = 4,227 hours