This post will help to document the order processing cycle for the ZAG website. Unless you have an insatiable interest in data procedures or are following in my footsteps, you can safely ignore the rest of this post.
For the purposes of this post, I assume you have at least an introductory familiarity with email, spreadsheets, and the WordPress dashboard. I usually work with 2 side-by-side browser windows: one for email and one for WordPress. Opening the master spreadsheet in a half-height window makes it easy to copy and verify between the order and the spreadsheet.
In the instructions below, I usually open links in new tabs. This is not strictly necessary, but it helps me to be able to switch views in the middle of processing without losing my place or any incomplete work.
Please note that some tab or field names may change in future plugin updates.
After a buyer or sponsor completes an order, the WooCommerce plugin sends an email to ZAG Board members and the website administrator.
- If the order is for a buyer membership, no action can be taken without ZAG Board approval. (They will reply to the original order email, so I usually filter by the order number to work on one email batch at a time.)
- If a sponsorship order has been paid via PayPal, you can process it immediately.
- If the order is for a sponsorship that will be paid via check or money order, no action can be taken until payment is confirmed.
On the WordPress dashboard, you will find orders under WooCommerce. There are four status flags of interest:
- Processing – New buyer orders and sponsor orders with completed online payment have the status, “Processing”.
- On hold – Sponsor orders with check payment are automatically given the status “On hold”. You can manually apply this status to buyer orders if there is some issue to be resolved.
- Completed – Once you finish processing an order, manually apply the “Completed” status to it.
- Cancelled – If an order cannot be fulfilled, we cancel it manually. The most common reason for cancellation is that the order was placed by someone who is not a buyer at a qualifying institution.
- Find the order under WooCommerce > Orders
- Right-click on the order number and name, e.g. “#1234 J Doe”, to open it in a new tab.
- Check the master list for possible matching entries. If the user id number is the same, we only need to update the existing record in the spreadsheet. If the user id is different, we will need to migrate the records and delete the obsolete record from WordPress.
- Copy (or verify) the contact information from the order to the master spreadsheet. (Columns H:R)
- Verify the URL for the Buyer’s institution and copy it to column S (without an http prefix).
- For column T, set an appropriate category (usually Zoo, Aquarium, Garden, or some combination).
- On the “Edit order” tab, right-click on the “Profile ->” link to open the user profile in a new tab.
- Copy (or verify) the information from the user profile to the master spreadsheet
- Column A, user_id, can be found in the browser address bar
- Columns C and D, user_name and member_email, can be found in the user profile form
- Scroll down to the “User Memberships” section of the “Edit User” tab and click on either the existing membership link or the “Add a Membership Manually” link.
- Set the “Plan:” to “Buyer – Free”
- Verify the expiration date is correct. Buyer memberships are a 2-year term. (I expire all memberships on the last day of the month.)
- Save the changes and verify the saved record.
- Update the master spreadsheet
- Column E, membership, should be “Buyer”
- Status, should be “active”
- Expiration should match the date on the WordPress membership record.
- Close the “Edit User Membership” tab
- Refresh the “Edit Order” tab. There should now be an active membership. Change the status to “Completed” and click “Update.
- Verify the information and close the “Edit Order” tab.
- Delete all the emails referring to that order. (Or archive them for an appropriate period.)
- Update Column B, “FLAG”, with text to identify the record status. (I usually use “update YY-MM-DD”, then change it to “Buyer CSV YYMM” after I publish a new Buyers list.)
The steps to add a sponsorship are nearly identical to those for handling buyers with the following additions or exceptions:
- Sponsorships are only processed upon confirmation of payment. PayPal payment details are shown at the top of the order record in WooCommerce. Payments via check or money order must be confirmed by the ZAG Treasurer.
- Sponsorships are for a 1-year term. Gold Sponsorships are for a 3-year term. (Buyers are a 2-year term.)
- Sponsors may need website access for multiple team members. (More details below…)
- Sponsors are also updated on the Sponsors page.
Updating the ZAG Sponsors page
Once the record is completed on the spreadsheet, copy the formula from another sponsor in column U. Here is the current formula for a sponsor record in row 8:
=CONCATENATE("<p><strong>",H8,"</strong><br><em>",T8,"</em><br>",Q8,"<br><a rel=",CHAR(34),"noreferrer noopener",CHAR(34)," aria-label=",CHAR(34),S8," (opens in a new tab)",CHAR(34),"
Beginning in January 2020, the sponsor formula will become:
=CONCATENATE("<p><strong>",H8,"</strong><br><em>",T8,"</em><br>",Q8,"<br><a rel=",CHAR(34),"noreferrer noopener",CHAR(34)," aria-label=",CHAR(34),S8," (opens in a new tab)",CHAR(34)," href=",CHAR(34),"http://",S8,CHAR(34)," target=",CHAR(34),"_blank",CHAR(34),">",S8,"</a><br><em>Sponosr through ",TEXT(G8,"mmm YYYY"),"</em></p>")
Edit the ZAG Sponsors page and add a block in the correct location. The page lists sponsors alphabetically in two lists: Gold Level and Annual. In the new block:
- Type any character
- Use the block menu to “Edit as HTML”
- Select and copy the cell with the link formula for the sponsor
- Select the block text, e.g. “<p>x</p>”
- Paste the copied link formula. (This should paste the formula results. If not, you may need to do a “Paste > Special > Values” first in your spreadsheet.)
- Backspace to remove the extra carriage return from the block
- Use the block menu to “Edit Visually”
This will give you a consistently formatted record for the sponsors with a website link that opens in a new tab.
Occasionally, someone will create a new record for themselves. Since email addresses are attached to the user ids, this can only happen if there is a new email address or there was a typo in the original record. This happens often enough that I always check for other buyer records from the same institution.
If there is an existing record in the spreadsheet, I update that row with the new information, including the new user record number in column A. In WordPress Users, I delete the record that isn’t needed, setting the “Attribute all content to:” selection to the user id we’re keeping.
Secondary Sponsor Records
Sponsors may have multiple team members who need access to the ZAG website. To handle these with the fewest complications, I’ve created a “secondary” membership without a WooCommerce product. This has several benefits:
- We don’t flood the order database with extra orders that have to be cancelled because the sponsorship was paid under a different user id.
- We don’t flood the order database with requests for “free” sponsor memberships. (Given that we already have prospective sponsors choosing the free buyer membership, imagine the confusion if our shop also displayed this free secondary sponsorship.)
- In the spreadsheet, we have a flag that there are other records attached to a given sponsor.
For secondary memberships, you can create the records manually from a list provided by the sponsor. You could also ask them to create a free buyer membership order, but fulfill it with a secondary membership instead.
The key issues here are:
- Always use the same expiration date for all the user ids attached to a given sponsor. This is a bit more complex, now that sponsorships aren’t restricted to calendar years. (Also, remember that a Gold Level Sponsorship lasts for 3 years.)
- On the spreadsheet, use “SECONDARY” instead of “active” as the status flag in column F.
- When processing a renewal, always check for possible secondary memberships and update their expiration dates as well. After I process the order, I reply with a list of all the user ids I’v updated and request confirmation for any changes that may be needed.
Summarized Process Steps
- Verify that the order should be processed (payment received, ZAG Board approval)
- Check for possible duplicates or secondary records
- Copy information from the order and user profile to the spreadsheet
- Update (or add) the appropriate membership with an appropriate expiration date.
- Refresh the order and flag it complete.
- Process any deletions or updates of related records.