If you’re a long-term fan of FME or follow Safe Software on social media, there are probably three things you already know:
- We really like eating our own dog food.
- We like donating to charity/giving back to the local community.
- We’re moving to a brand new office in December 2018.
As anyone who’s moved house before knows, you end up accumulating a lot of stuff, and that’s no different for us at Safe Software! As we don’t want to take anything with us, we’re leaving behind a lot of items.
In order to reduce waste and to raise money for the Surrey Homelessness & Housing Society, it was decided to auction off anything we wouldn’t be taking to the new office.
The one thing we didn’t have was a system to manage the auction – until Dale approached me wondering if FME Server (in this case FME Cloud) would be able to automate the auction for us.
As with most questions beginning with “Can FME do …?”, my first answer was “Yes” followed with furious thinking about how to actually implement it.
Automating an Online Auction: Requirements
The only things I had to start with was:
- A Google Form to collect the bids
- A Google Sheet where the bids would be tracked
- A Google Sheet of inventory
- 7 days to put it all together
The requirements (at least at the beginning of the project) were:
- Instant notifications after placing a bid so that you will know:
- Whether you’re the highest bidder
- If you’re not the highest bidder – what the highest bid is
- If you’re the highest bidder and you get outbid (and what the new highest bid is)
Because we needed instant feedback, none of the existing FME Server Publications met that criteria. My first thought was to have a workspace with a Google Sheets Reader that would ‘poll’ the bid responses, but that would be slow and take longer and longer as the number of bids increased.
Luckily, after a bit of internet detective work, I found Google Apps Scripts. This allowed me to set up Triggers on Google Sheets that could make a HTTP POST to FME Server. This meant every time someone filled in the form to place a bid, the trigger would send a JSON message to FME Server containing the info that was filled out in the form.
Over the 3 weeks that the auction consumed my life, we ended up with a plethora of issues and revisions:
- Duplicate lot numbers meant people could be bidding on the same, but wrong, items.
- To see what the highest bid was, people would have to bid $1.
- Safers were losing track of their bids (and some people starting outbidding themselves).
- The auction must stop at 12 noon exactly.
- People started bidding silly amounts.
Automating an Online Auction: What was achieved with FME
Slack notifications
After getting distracted watching the incoming notifications in real time on FME Server, I thought it might be fun for other people to see the current auction activity. By adding SlackConnector transformers to the workspaces I was able to stream messages for new high bids, unsuccessful bids and any new items that were added to the inventory.
Bid history notifications
Using the Google Forms/Sheets triggers, it was easy to set up a form that would allow Safers to request their winning bids, bid history or both. FME would then collect all of that information and email it back to the requestor.
$5 bid increments
Towards the end of the auction, it was requested to raise the bids to be in multiples of $5 to reduce the number of $1 bids that we had (the money is going to charity after all). Originally I started trying to divide bid values by 5, check if they were integers… Until I realised this would be much easier with a Tester and a regular expression to check if the bid value started or ended with a 5 or 0 (5$|0$)
Duplicate lot number validation
As there ended up being around 800 items in the auction, spread across 4 different tabs in Google Sheets, it was hard to keep track of lot numbers when adding new items. The Google Apps Scripts was adapted to post the lot number to FME whenever a new one was added. FME would then check to see if the lot number already existed. If so, an email notification would go out to correct it. If it was unique, it would get posted to Slack and added to the sqlite database.
12 noon cut-off
As the auction had to stop at exactly 12 noon on Friday 16th, I needed a way of stopping bids being processed as people would still be able to use the Google Form. There was also concern that if lots of people were bidding that the FME Server job queue would get backed up, stopping people’s bids being processed on time. Google Forms adds a Timestamp column to the data, so this was converted to Epoch time and tested against to check that it was less than 1542369601 (12:00:01 on Friday 16th November).
Final PDF reports
Once the auction was over, we needed to let people know which items they’d won, the total amount they owed and some instructions on how to claim the items. This was done as a PDF and emailed out to successful bidders so they would be able to print it out and bring it with the money.
Emails…lots of emails
There were email notifications for:
- Highest bid
- Unsuccessful (or invalid bid)
- If you’ve been outbid
- Auction is over (so invalid bid)
- Bid history
- Winning summary
- Duplicate lot number
Automating an Online Auction: What I Learnt
- Changing the Google Form definition changed the Google Sheets Schema. This ended up being the culprit for a few mysterious breakages to the system.
- FME Server didn’t like sending tens, to hundreds of emails at once. At 11:57:28 FME Server stopped sending emails – meaning bidders had to watch Slack whilst trying to keep an eye on their lot numbers (very stressful – but exciting). There were 126 bids between 11:57:01 and 12:02:29, which meant that 126-252 emails needed to be sent in a very short time span. (This is something I’ve raised with development).
- Reading Google Sheets was a bottleneck. The Auction Bid History service originally took around 20-40 seconds to run, which is fine partway through the auction. However, with emails down and being into the final few seconds of the auction the workspace run time was closer to 2 minutes and wasn’t fast enough (at this point there were over 2000 bids).
- I had a few people raise concerns that there wouldn’t be enough FME Server engines to process the bids quick enough and with jobs queueing that could mean they’d miss the auction deadline. As the Auction Bid Receiver workspace only took 1 second to run, this wasn’t an issue (and we were basing bids from the Google Forms Timestamp, not job processing time). Just in case, I did add a few extra engines but had to remove them due to the sqlite table not being able to handle the concurrency.
- Job Queues are really valuable! I added two queues to FME Server – one for anything related to the auction and one specifically for bid history. I also gave the jobs a high priority so they would get processed ahead of any miscellaneous jobs. As the Bid History Requests were being called via Direct URL, I was able to direct them to the correct job queue by adding &tm_tag=bidhistory to the query parameter string.
Now I get my life back – and a new TV!
Watch the Coders on Couches Drinking Coffee episode below and stay tuned for an FME Knowledge Centre tutorial.
Jennifer Luther Thomas
Jen is the FME Server Technology Expert Team Lead at Safe Software. She enjoys lifting heavy things and being active outdoors. She enjoys these things so much that she’s hoping to qualify for powerlifting nationals 2020!
