The data provided was under form of Excel sheets and it included:
- The kinds of litter categories collected by the users;
- Check-ins information (kind of litter collected, location, app platform used, timestamp, userid);
- Check-ins on Blue Flag beaches;
- Check-ins for the Street Clean initiative.
Considering the timeframe available, it was necessary to focus on a single initiative. The one with more data available was surely the Beach Clean campaign.
The first step of the data analysis was converting the location coordinates into actual addresses: this was possible through a Reverse Geocoding script
to be used on Google Sheets. The data was therefore imported in Google Sheets and the script run.
Having run the script, I could easily obtain an overview of check-in locations through the "COUNTIF" function in Excel. The same function was used to extract usage by platform and by month.
The kinds of litter collected couldn't be counted with the same method
because of how the Excel sheet was structured. In fact, for every litter log,
there were two separate columns: one indicating the type of litter collected,
and one indicating how many instances of that litter type were recorded for that single log.
Therefore, if I had run the "COUNTIF" function on the litter type column immediately,
Excel would have counted every litter type just once,
even if more items of that type were collected (because the information about the amount
of items was registered in another column). The solution for this was using a script
to duplicate every column as many times as the value in the "CollectedCount" column, and only THEN running the
COUNTIF function on the LitterTypeId column.
The procedure allowed me to extract information about the litter collected by users:
Finally, to visually display the checkin locations, all the coordinates were imported on Google Maps: