Universal Gift Card Infrastructure

Universal Gift Card Infrastructure Flowchart

( click here to view high resolution )

Overview

        A universal gift card infrastructure is a system that provides a seamless gift card purchasing and redemption experience between eCommerce and Brick & Mortar stores. Since most eCommerce platforms have already implemented their own gift card system, marrying the Brick & Mortar system with this existing infrastructure will offer the most seamless flow for customers. My company uses Shopify as its eComm POS, so I created an auxiliary app in Java that utilizes Shopify’s existing eComm gift card infrastructure, acting as a universal liaison for the two platforms. There are a few important implications of this approach:

  • Gift Card generation can be handled through Shopify’s system
  • The online gift card experience will remain unchanged for customers
  • All gift card transaction history can be accessed via Shopify
  • There are two records for each gift card: one in Shopify, one in Airtable

        I chose the cloud-based SaaS Airtable to fulfill the role of both back-end database and front-end interface because of the simple and intuitive experience it will provide to customers, employees, and future maintainers of the app (I have developed and equipped my company with our own Airtable SDK). The service provides three key features that are used in the infrastructure: Input Forms, Queryable Databases, and an API. Forms allow data to be input intuitively by employees and customers without ever accessing the actual database. The database itself will house the information, and the API will be the means of communication between Shopify, Airtable, and Klaviyo (the system used to email customers).
        The concept of the app is to maintain and reconcile two different records of each card between Shopify and Airtable. If a card is created in Shopify’s native system, it will be created in the Airtable database. If a card is requested in the Airtable database, it will be created in both the Airtable and Shopify system. This is what allows the online experience to remain unchanged; the Shopify gift card system is still operational. While the Shopify database exists to provide a seamless online flow, Airtable’s functionality is more convenient and robust and so it will act as the primary database. Security is an important concern when it comes to Gift Cards, and all forward-facing gift card codes in the system are masked. All communications to the stores and customers (besides a purchase confirmation, of course) also utilize the masked version of the code.
        The flow of my database can be seen in the flowchart above, and is best described as a three-pronged approach built for maximum efficiency; there is a spectrum of time-sensitivity that must be accounted for. When a customer walks into a Brick & Mortar store and wants to redeem a Gift Card, it is imperative that my app process their request as soon as possible to ensure the customer and employee are not left waiting. A customer using the website to inquire about their gift card balance, however, sits lower on the prioritization hierarchy. The app achieves this by handling different types of requests in three different threads every 15 seconds: Store Transaction Handling, Shopify Purchase Handling, and Shopify Redemption Handling. There are also additional threads (such as the Shopify Card Balance Adjuster) that are one-offs activated whenever necessary. If a company orders 50 Gift Cards for their employees right before a customer tries to redeem a gift card at the store, the customer does not have to wait for the 50 cards to be processed before their redemption is processed.

Prong 1: Store Transaction Handling

        The matter of utmost priority is Store Redemption Handling. If your customer is trying to purchase something, they have invested trust in the company and we cannot take that for granted. Upon execution, my app will collect all unprocessed redemptions input by the stores via form input from our Airtable database. The Gift Card code for each redemption is cleaned up (inputters often don’t follow formatting instructions) and analyzed. There are four avenues that might occur: Gift Card Code Format Incorrect, Gift Card Code not Found, Insufficient Balance, and Successful Redemption. For the three problematic results, the store that input the redemption will be emailed with information relevant to their issue. In the case that the card was identified and the balance was enough to fulfill the redemption value, a thread is initiated to adjust the card’s balance in the Shopify platform and the program moves on to adjust the Airtable balance. After doing so, it emails the store that requested the redemption and informs them of its success, also providing balance information about the card. The app will also link the redemption request record to the actual card record for intuitive logging.         After all Store Redemption Requests have been handled, the program can now move to lower priority tasks. It collects all creation requests from the stores and, if validated, creates the cards. Upon success, the app uses the Klaviyo API to create a new metric with the customer’s information and code, which will then be sent by Klaviyo to the customer (I decided that Klaviyo’s email servers were more reliable than our own email servers for the customer-facing content). Additionally, if the Gift Card was purchased specifically as a gift for that person, the purchaser will receive confirmation that it was sent to their email. This gives them a chance to triple check the email and ensure it was sent to the right person. Finally, the store receives confirmation of the delivery. Here are examples of the emails sent:

Gift Card Recipient Email (Gifted)
Gift Card Purchaser Email (Gifted)

        The final task of the Store Handling prong is to process Balance Checks. The prong on which this process occurs doesn’t really matter, since it is so low priority. Customers can access a secure form on our website where they input their Gift Card Code and their email (since Gift Cards are often handed to other physically, we cannot simply use the stored email of the “Owner”), and a similar process to the redemption process occurs. After all balance checks have been handled, the Store Handling prong concludes.

Prong 2: Shopify Purchase Handling

        The only task of the Shopify Purchase Handling thread is to identify gift cards ordered on shopify, delivering them to the customer and creating them in the Airtable database. A graphQL query is made to shopify for all orders with our Gift Card SKUs that haven’t been tagged as processed. Whenever a card is identified and processed, a new thread will be created to tag the processed order so that it will not be processed again. Additionally, the app creates its own record of this order in an Airtable database that future unprocessed purchases will be cross-referenced with, because I have noticed some oddities in Airtable’s GraphQL system, particularly when it comes to querying. The system sometimes returned items to me that were clearly identified as tagged on its back-end, so the app always double checks against its own history.

Prong 3: Shopify Redemption Handling

        This prong is virtually identical to the Shopify Purchase Handling prong, only this time it’s for redemptions. One caveat is that all used cards are cross-referenced with the database before proceeding, as historical cards that existed before the univeral infrastructure do not need any processing (since they exist solely as Shopify and are still online-only cards).

Logging

        It’s very important to include extensive logging and paper trails when dealing with the finances of customers; all actions performed by the app are logged and stored in a Receipts table. API/ERP actions are also logged on their respective platforms, but they are unified here in Airtable for easy viewing.

Universal Gift Card Logging

While the app has never failed to send an email, it’s certainly an important point of failure. The app will attempt to resend upon failure, retrying with a longer wait each time, but if all attempts fail the issue will be noted in the logs. I’ve set automations on Airtable to alert me if any important part of the process is unsuccessful. The second most likely failure point is in updating the app’s status.

Universal Gift Card Status Base

Since the app consults with this status page to ensure it does not run while the previous process is still running, a potential issue could occur where the app fails to update the status back to “Offline”. In this case, the status would remain as “Executing” indefinitely, and the app would not move past the status check. The app is efficient enough that we can implement a short maximum processing time, and if this time is exceeded, an Airtable automation will automatically set the status back to offline. This will allow the app to resume standard processing after a connectivity issue with our server or Airtable causes the app to terminate without successfully updating the status back to “Offline”.

Conclusion

        This infrastructure is a simple and effective way of unifying Gift Cards between eCommerce and Brick & Mortar platforms if they exist independently. There are many benefits of building such an app in house, such as customizability, security of data, and lowered cost. While not currently implemented, the company this was built for has expressed interest in thematic Gift Cards in the future, such as Birthday and Wedding cards. The app interfaces with Klaviyo and templates we’ve built in order to send customer-facing emails, so the implementation of this feature would be as simple as designing the templates and including the template type in the post data from the app. While the flows are fairly complex, the code is written with simplicity in mind and includes documentation along the way to ensure that any future maintainers of the app will be able to address requests as they arise.