Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
Create a report for each member of Congress that our lobbying team would use to show the profile of our company assets within that member’s district or state.
Describe the business challenge or problem you needed to solve
We had been contracting with a vendor to provide a report for our lobbying team that would show how many of our radio stations were heard in a Congressional member’s district or state. The report cost us $20,000 per year and it was updated quarterly. There were no maps.
I was initially asked to simply provide the counts of OOH inventory to add to the report. However, at some point I was asked if I could replicate the analysis that determined which radio stations could be heard in the district.
Once I solved that, I was asked if there was a way that I could replicate the entire report. I had never used the Alteryx reporting tools, but I said I would give it a shot.
Describe your working solution
I used Alteryx Designer to create data, format the report, and output the PDFs. I used the Scheduler running on Alteryx Server to automate the report creation each month.
* YXDB files of the AM and FM radio coverage areas
* Dataset of all of the US Congressional Members
YXDB file of our OOH inventory
YXDBs of the centroids and attributes of all of the airports and malls in the US
YXDB of current Congressional Districts or States
Excel file stored on intranet server containing the leadership personnel in each office
Table that is stored in a MySQL database on our WAN containing the addresses of all of our radio stations
* Data is downloaded and created using Alteryx at runtime from external websites
Please provide steps for your solution:
I used the List Runner macro from the CReW macro set to sequentially run the following:
[Name for resulting dataset given in brackets]
[FMareas] I used a custom macro to pull the FM radio coverage data zip file from the FCC ftp site. The macro both downloads and unzips the file from the FTP site. I wrote this macro before the Input tool worked with zip files. This text file contains 360 latitude and longitude pairs for each radio antenna which represent the degrees of a polygon around the station antenna. I used the PolyBuild tool to create coverage polygons. I use the same custom macro to pull two other zip files and ingest attribute data from them that I then join to the polygon data. I then output this data to a YXDB file.
Image of the coordinate pairs in the FM contours text file
[AMareas]Each month, our engineers generate a text file based on our AM antennas to create coverage polygons for the AM stations. The text file is updated and is stored on a file server. The transformation process is similar to the one above, except instead of being given coordinate pairs, the text file contains a degree and distance in kilometers as measured from the antenna. I wrote a custom macro named Create Point On Bearing to create the point objects at the specified angle and distance. I then output this data to a YXDB file.
Image of the degree & distance data in the AM contours text file
[Member] I used the Download tool in two separate custom macros to scrape the House and Senate clerk web sites to get current data on the Congressional members. I had to parse the HTML data to convert the information into rows and columns. I also used a custom macro that converts any HTML codes to the equivalent Unicode characters. I then output to two streams of data.
[Districts] YXDBs of Congressional Districts and States created from data downloaded from the US Census website and containing a polygon object. Going forward, Districts refers to either a Congressional District or a State region
[RadioStations] I queried data from a MySQL database that contains the addresses of all of our current radio stations and includes contact information for the leadership. I geocoded them using the US Geocoder tool and then output them to a YXDB file.
[OOHexecs] A custom macro ingests an Excel spreadsheet stored on an intranet server that contains contact information for the leadership for our OOH offices. The macro massages the Excel file to get a consistent format and outputs to a stream of data.
[OOHunits] A YXDB file that is refreshed each night by querying 29 different SQL Server databases using a separate Alteryx process. This contains a point object for each advertising display.
[Employees] A YXDB file stored on an intranet server that contains the home addresses of all of our employees. Quarterly, I update this dataset by geocoding a spreadsheet of addresses using the US Geocoder tool.
[Air and Mall] Two YXDB files containing attributes and point objects for all of the airports and malls where we have advertising units. These are manually updated as needed.
[RadioSpatial] I joined the RadioStations to the FMareas and AMareas polygons so that each radio station had a point object (station office) and a polygon object (coverage area).
[ConressSpatial] I join the Member data to the Districts data so that each record has a polygon object and the attributes of the member.
[OOHSpatial] I joined the OOHexecs and OOHunits data together.
[RadioCongress] Using the Spatial Match tool, I found the RadioSpatial (point and polygon) that intersect each CongressSpatial.
[OOHCongress] Using the Spatial Match tool, I found the OOHSpatial (point and polygon), Air, and Mall that intersect each CongressSpatial
I created a map for each Member that contains layers for OOHCongress, CongressSpatial, and both the station location and coverage polygon from RadioCongress.
I created tables for each Member showing counts of OOHCongress data grouped by type. Also included are the numbers of radio station offices, radio stations heard, and number of employees living in the District.
I created a table listing the callsign of each radio station office and radio station heard within the District.
I created a table listing contact information of leadership personnel in the Radio and OOH offices from the OOHexecs and RadioStation streams.
I used various reporting tools to create snippets for our corporate logo, the Member name and information, and other boilerplate text about our company.
Finally, I combined all of the snippets using many Layout tools into a PDF file for each Member of Congress.
Describe the benefits you have achieved
This process resulted in win-wins across the board: cheaper, faster, fancier, and happier.
Cheaper: We were able to cancel the contract with the vendor saving $20,000/year. With the process being fully automated, there is now no labor involved in the process at all whereas we had been required to query multiple data sources in-house, then bundle the data, and then send it to the vendor.
Faster: We now have a report that is updated monthly with current data. We can customize the process in any way we need and not have to request changes to the vendor’s report.
Fancier: New features added include:
- Map showing our Radio and OOH assets in the District
- Leadership contact information for each office in the District
- Counts of employees living in the District
- Counts of OOH assets by type in the District
Happier: The lobby team loves the new features, frequency, and format!