Gathering the Data: RPA in Action

The first step of my project was to fetch the data from Billboard's Year End Hot 100 list for that. At first I thought it would be easy to gather the information from billboard’s website, but the page was not structured in a way that I could easily change the year and fetch the whole list, so I used Wikipedia instead to fetch the list.

Wikipedia provided a table with all the songs, but I had to investigate each year at a time (meaning a lot of copy and paste in a repetitive task...). But repetitive tasks are the most suitable processes for automation, and that was what I did: I used UI Path to create a robot that will retrieve Billboard's Year End Hot 100 from 1980 to 2019 and increment the information with results from Google.

It was interesting to build the robot, because I had to overcome some situations that I had not foresaw. I had no problems gathering the list from Wikipedia, my problems started when I was getting the additional information from Google. The full list has 4000 rows, which means that the robot would have to make all these searches on Google, and this volume raised a flag on the search engine and Google detected it was a robot!

To salve the problem I modified the robot to check for the captcha message and verify it when just the checkbox was enabled, but when it required human action I had to create a logic and use VPN to change the IP address and continue. It consumed some time than expected, but at the end I managed to go through all the rows and update my list. At the end, 1200 lines was still not fully completed.

So, my next step was to create another robot, this time it would go through Wikipedia to fetch the details. No captcha issue there, but I could not get all the information that I needed. I was almost stating to improve my robot when I started to think about Spotify and Deezer, and how they should have an API that could bring all the information that I needed. I searched the web and found the website http://organizeyourmusic.playlistmachinery.com that gives song details based on a Spotify playlist. The only thing that I had to do was to split the list 10 different parts, because the page shows the top 400 song, but I wanted all, and to create a robot to extract the data and update the Excel spreadsheet.

I'm still cleaning the data and the main point I want to share with you is that if you or your company don't have a structured way to gather the data, you will spend most of your time preparing and cleaning it, before you start to build visualizations and analysis.