Clayoquot Whales is a site maintained by Josie Byington, a scientist who tracks whale movements in and around Clayoquot Sound and Barkley Sound here in BC.
Grey whale baby showing baleen in breeding area of Baja California, Mexico
A couple months ago she reached out to my company for help. She has whale sighting reports coming in every week, and has to manually enter the data for each sighting into the website. It’s not so bad for entering humpback whales one at a time, but she’s got a backlog of 3000 or so grey whale sightings that just live in an Open Access database. They hadn’t set up a system for keeping track of the greys yet.
Josie asked me to set up a front end for the greys similar to the one for humpbacks, but also to give her a way to bulk upload all her sighting data. Of course I jumped at the chance to work on this project, because whales are amazing and it’s not every day that I get a chance to work with them, even indirectly, as a web developer.
I laid out a plan of action, and with Josie’s approval, got to building.
Custom Post Types
I used Generate WP to create two custom post types, Individual and Sighting. Although plugins can be used to generate custom post types, I prefer to hardcode them to save database calls. Generate WP is a great time saver for this.
Next I used Advanced Custom Fields to add a field for each column in Josie’s data.
I created a few test whales and sightings, and used them to copy over the templates from the humpback whales, as seen here and here. Just a few changes were needed to reflect the different data collection methods for each species. Note that as a non-profit working with a tight grant-based budget, they opted to stick with simple styling. Though outdated, it’s completely functional and loads quickly.
Automation
With that framework in place, I added the magic part. I used WP All Import, a somewhat expensive paid plugin that my employer had a license for. In the past I have written bash scripts for importing CSV data into WordPress. Although it can be done, it takes a lot of testing and headaches to work out all of the bugs and edge cases. With that experience, I know for sure that the $199 license fee for WP All Import is 100% value for money.
In WP All Import, your uploaded CSV file is used to populate an example record. You can use their visual interface to create a template to match up database fields to WordPress fields, and save the template for future use, saving time and error potential. This post probably sounds like an advertisement for WP All Import at this point, but honestly, I would not have been able to complete this project for a $1500 budget without it.
Images
As of this writing, March 26th 2020, the full Import Pro package for $199 is necessary, because it includes an ACF add-on. I was able to import most of the fields in Josie’s database without the add-on, except for image fields. Photographs of the whales are obviously important, and the standard import process wouldn’t work.
Here’s how the import works. Josie has a column for images in her data. The url of a photograph is listed – it’s a holding directory on her server where she can upload all the photographs in bulk via FTP. It’s a massive upload, so doing it through a browser will not work. The URL is something like https://clayoquotwhales/wp-content/uploads/gwid/L_xxxxxx.jpg.
When you upload a picture to WordPress, it gets a unique database ID and an URL that is more like https://clayoquotwhales/wp-content/uploads/2020/03/L_xxxxxx.jpg.
To properly handle images the WordPress way and have them work correctly in templates, they need that unique ID and URL. The standard WP All Import simply saves the database URL as text – it doesn’t download the image and create a new media record in WordPress. For that, you need the ACF add-on. And it works perfectly.
Whale and Sighting Relationships
Again, when you create a new item in WordPress, WordPress assigns it a unique ID, and that is used to identify and manipulate the record. So each whale imported has a unique ID assigned by WordPress – but that ID is not known in advance by Josie, and so it is not present in each sighting record. Instead, the whale is identified in each sighting by a number assigned by Josie, which looks like CSG9999. Since the CSG number is also present in each whale record, I wrote a script that searches for whales by CSG number and creates the relationship between the sighting and the whale ID, as WordPress metadata. This script runs at the click of a button, and the user needs to do that manually after each data import.
Result
Once complete, the system allows Josie to import over 3000 rows of data in under 20 minutes. The final cost of the project was around $1800, which would have paid for 90 hours, or two and a half weeks, of manual data entry, meaning that the software should pay for itself in well under a year.
If you have a similar error-prone and time-consuming data entry job that you would like to automate, get in touch with me and we’ll see if I can help.