Posts - Zoo, Aquarium & Garden Buyers Group

Home > Posts

Newsletters

ZAG Newsletter for January 2020

Post author By Kevin Knerr
Post date January 29, 2020

The ZAG News for January 2020 is now available.

ZAG_News-2020-01 Download

Admin Website Design

Order Processing Cycle

Post author By Kevin Knerr
Post date December 3, 2019

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.

Order Placement

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.

Order Processing

Buyer Memberships

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.)

Sponsorships

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.

Obsolete Records

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.

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.

Resources

Reducing Single Use Plastic

Post author By Judy Rancour
Post date November 18, 2019

This was presented at the 2019 ZAG meeting, as well as at the September 2019 AZA meeting.

Reducing-Single-Use-Plastic Download

Admin Website Design

Website Update in Progress

Post author By Kevin Knerr
Post date November 13, 2019

Starting today, I will be migrating the ZAG website to a new theme. While I have done most of the work in a staging site, there may be some issues while the update is in progress.

Executive Summary:

We are moving from an outdated commercial theme. We cannot update this theme unless we purchase a new subscription. Additionally, the commercial theme has several dependencies that have their own subscription costs.

The new theme is based on Twenty Twenty, the new default WordPress theme. Because Twenty Twenty is the new default theme, there are no licensing fees.

After the new theme has been live for a few weeks, I will back up the website and delete all the outdated plugins and themes.

Administrator Notes:

In addition to the substantial reduction in subscription costs, the new theme allows us to remove several plugins from the site. This greatly simplifies troubleshooting and site maintenance.

Another benefit is that the new theme was designed for the Gutenberg block editor, with an eye toward responsive design and Google’s AMP initiative. This allows us to remove several CSS overrides needed to make the previous theme work better on tablets and phones.

The new twenty-twenty-zag child theme only modifies 4 source files. I have included diffs, along with the original versions, in the child theme directories.

There is also a modified source file for The Events Calender list widget.

I will provide more thorough documentation of the code changes in a future website design post.

Website Design

Yes, Virginia, there is a database, but we’re still doing the membership list in a spreadsheet…

Post author By Kevin Knerr
Post date November 7, 2019

Quick links:
    You may think it’s EAV, but it’s not…
    TL;DR – Straight to the SQL queries
    … and into a spreadsheet

When I became the website administrator in November 2018, my first task was to produce an updated buyer list for our sponsors. You would think this should be a relatively easy task because all of the data we need is in the website database.

That’s what I thought. I was wrong.

The most recent buyer list was a CSV exported from WooCommerce Memberships. This was unsatisfactory for two reasons:

The CSV included data that should not be shared publicly, like usernames, that could be used in a security exploit.
It did not include data that should be shared with ZAGs sponsors, like contact information or which institution for which the member is a buyer.

I naively assumed that I should be able to write a simple SQL query to the database, something along the lines of:

SELECT * FROM customers, members
    WHERE customers.id = members.customer_id

Alas, ’twas not to be.

You may think it’s EAV, but it’s not…

I discovered that the WordPress user data is organized into an Entity – Attribute – Value scheme. Except that it isn’t a pure EAV model.

Ideally, an EAV database has three tables:

An entity table consisting of a primary key and the entity value. In this case, I expected to see a user id number, a text username, and possibly the corresponding user email that also permits logging into the site.
An attribute table consisting of a primary key, an attribute name, and possibly a format definition for the attribute or validation constraints.
A value table consisting of its primary key, a foreign key to the entity table to identify the user, a foreign key to the attribute table to identify the data element, and finally, the actual value for that user/attribute combination.

EAV tables tend to be narrow, but long. They are perfect for cases where not every record has the same attributes. A traditional 2-dimensional table such as a spreadsheet would be very wide and have many empty fields. EAV models are compact, and flexible. Adding a new attribute just requires a new record in the attribute table. You don’t rewrite the schema or waste space adding empty fields to every preceding record.

Unfortunately, the WooCommerce contact information isn’t stored in a pure EAV scheme. There is no attribute table: instead the attribute name is stored in the value table itself.

This does have the advantage in that it greatly simplifies the query logic. EAV databases are complex to query because you need to join 3 tables. But working with 2 tables in an e-commerce environment, you can quickly code for each field in a form:

SELECT * from customers
    WHERE userid = n
    AND attribute = "billing_address_1"

On the other hand, excluding the attribute table wastes a lot of space: the attribute name is repeated every time it is used. (For example, the attribute name “billing_address_1” requires 16 characters, while a typical integer index requires only 4 bytes.)

(Granted, I’m showing my age here. I started programming when both RAM and disk space were limited. While you can argue that modern tools make my conservative approach to memory management obsolete, I still feel a great deal of personal satisfaction in efficient, lean code.)

TL;DR – Straight to the SQL queries

In the end, the quickest way to generate the buyer list from the website tables was to use two SQL queries, then manipulate the data offline as needed. Here are the queries, using generic table names for security:

SELECT ID, user_login, user_email, user_url, display_name
    FROM `wp_users`

SELECT user_id,
    MAX(CASE WHEN meta_key = 'billing_company' THEN meta_value END) AS company,
    MAX(CASE WHEN meta_key = 'billing_first_name' THEN meta_value END) AS first_name,
    MAX(CASE WHEN meta_key = 'billing_last_name' THEN meta_value END) AS last_name,
    MAX(CASE WHEN meta_key = 'billing_address_1' THEN meta_value END) AS address_1,
    MAX(CASE WHEN meta_key = 'billing_address_2' THEN meta_value END) AS address_2,
    MAX(CASE WHEN meta_key = 'billing_city' THEN meta_value END) AS city,
    MAX(CASE WHEN meta_key = 'billing_state' THEN meta_value END) AS state,
    MAX(CASE WHEN meta_key = 'billing_postcode' THEN meta_value END) AS postcode,
    MAX(CASE WHEN meta_key = 'billing_country' THEN meta_value END) AS country,
    MAX(CASE WHEN meta_key = 'billing_phone' THEN meta_value END) AS phone,
    MAX(CASE WHEN meta_key = 'billing_email' THEN meta_value END) AS email
    FROM `wp_usermeta`
    GROUP BY user_id

Full disclosure: my database chops were a bit rusty. Fortunately, we live in an age of search engines and I was able to find an example which let me quickly write the above query.

… and into a spreadsheet

With these simple 2-dimensional tables in hand, I was able to quickly create a combined table in a spreadsheet. This spreadsheet is now the master buyer and sponsor list for the ZAG website. There are of course, pros and cons to this process:

Pro

I’ve sanitized and normalized the data elements.
I can filter the table in real time as opposed to writing an ad hoc SQL query in the php admin panel.
Exceptions can be handled without resorting to changing the underlying data tables (and possibly breaking the site).

Con

Every update must be manually transferred to the spreadsheet
Changes made by members outside of the renewal process won’t be transferred to the spreadsheet
Automatic website backups do not include the offline spreadsheet

While it would be easier to let our members update their contact information themselves, the presence of many hands without validation rules guarantees inconsistencies in the presentation of the data.

In the end, for flexibility and data integrity, we’re using an offline spreadsheet to maintain our member and sponsor data.

In the next post, I intend to document the order processing cycle.

Website Design

New Administrator: Start Here

Post author By Kevin Knerr
Post date October 29, 2019

This is the first in a series of posts which will attempt to document the design decisions and administrative processes for the ZAG website. The goal of these posts is to allow a new administrator to pick up where I left off – to make the site self-documenting.

By sharing the thought processes behind these decisions, I hope to help my successors to avoid pitfalls I’ve already encountered and to make choices that are at least as good as what I’ve done, if not better.

Hopefully, other WordPress users might find value in these posts as well.

Website Goals

In any well-designed product, form follows function. In a perfect world, you’d have a prioritized list of goals and desires. So, even though I took over a site that was already in production, let’s attempt to identify the goals for the ZAG website:

To provide online resources for our members and sponsors
To maintain a database of our members and sponsors
To simplify sponsorship by providing online payments
To promote ZAG and its goals to prospective members and sponsors

Constraints

Good, Fast, or Cheap?

Our first constraint is the triad: “Good, fast, or cheap: pick two.”

Given that the ZAG website was already running, my main task in the winter of 2018-2019 was to get up to speed with what was already in place and to process the backlog of memberships and sponsorships. “Fast” was the order of the day. Going forward, however, my choice from the triad is “Good and Cheap”.

I want to minimize costs by using as few paid plug-ins as possible.
I want to minimize complexity by using as few plug-ins as possible.
I want to maximize quality by using well-tested, maintained themes and plug-ins.
At the end of the day, a site that works well and is easy to navigate is far more important than flashy gimmicks.

ZAG Membership Prerequisites

Our next constraints are due to the prerequisites for ZAG membership:

Only buyers from zoos, aquariums, and gardens with self-hosted retail operations are eligible for a free buyer membership. The ZAG Board verifies buyers prior to membership activation on the website.
Many sponsors pay via check or money order. In those cases, sponsorships cannot be activated until confirmation of payment is received from the ZAG Treasurer.

The net result is that we have a complex set of criteria that are not easily reduced to a simple algorithm for automated membership activation.

In the next post, I’ll document the generation of the membership lists.

Newsletters