Revamped Report: Cheaper, Faster, Fancier, and Happier!

[Hollingsworth]

Name: John Hollingsworth

Title: GIS Manager

Company: Clear Channel Outdoor

Collaborators: none

 

Overview of Use Case

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.

Data:

1. * YXDB files of the AM and FM radio coverage areas
2. * Dataset of all of the US Congressional Members
3. YXDB file of our OOH inventory
4. YXDBs of the centroids and attributes of all of the airports and malls in the US
5. YXDB of current Congressional Districts or States
6. Excel file stored on intranet server containing the leadership personnel in each office
7. 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:

DATA

[Name for resulting dataset given in brackets]

1. [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

2. [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

3. [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.

4. [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

5. [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.

6. [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.

7. [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.

8. [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.

9. [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.

ANALYSIS

10. [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).

11. [ConressSpatial] I join the Member data to the Districts data so that each record has a polygon object and the attributes of the member.

12. [OOHSpatial] I joined the OOHexecs and OOHunits data together.

13. [RadioCongress] Using the Spatial Match tool, I found the RadioSpatial (point and polygon) that intersect each CongressSpatial.

14. [OOHCongress] Using the Spatial Match tool, I found the OOHSpatial (point and polygon), Air, and Mall that intersect each CongressSpatial

15. I created a map for each Member that contains layers for OOHCongress, CongressSpatial, and both the station location and coverage polygon from RadioCongress.

16. 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.
    
    

 REPORTING

17. I created a table listing the callsign of each radio station office and radio station heard within the District.

18. I created a table listing contact information of leadership personnel in the Radio and OOH offices from the OOHexecs and RadioStation streams.

19. I used various reporting tools to create snippets for our corporate logo, the Member name and information, and other boilerplate text about our company.

20. 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!

Related Resources

Link to before and after versions of the report

[collin_pace]

Cool use case John!