Advanced Gift Certificate Management — Setting Expiration Dates on Unused Gift Certificates
Introduction
After reading Marty Zigman’s recent article discussing Best Practices for NetSuite Managed Split Tender Gift Certificate Programs, it reminded me of a recent enhancement that we provided to one of our clients related to gift certificate management. For context, this article is relevant if you need to support a gift card program in NetSuite and need to credit the original purchaser for the unused gift certificate balance(s) on an ongoing basis.
Background
Our client hosts numerous eCommerce “micro-sites” also known as company web stores. Each of these web stores can be tailored uniquely for each customer to offer curated, customized promotional goods that are relevant to each customer’s unique buying audiences. For some of these customers, our client also offers and sells gift certificates to their customers which then can be redeemed by the customer’s buyers (often employees) to purchase promotional goods through a company web store. While NetSuite’s native functionality does a great job facilitating the issuance and redemption of these gift certificates, a challenge arose with regards to the gift certificate balances that are often left unused and need to be closed out.
The Challenge
Using the native NetSuite functionality, these gift cards are not set with an expiration date when they are created. The reason why expiration dates are not set by default is because certain states (determined by billing address) do not allow gift certificates to expire. However, there are situations where our client needs to expire a large list of gift certificates that pertain to one of their customer’s web stores.
Natively within NetSuite, users can update specific gift certificate records via the user-interface to set an expiration date. At the same time, the user could also potentially recognize revenue with the click of a button.
Of course, doing so manually using this approach would be quite tedious. Furthermore, while native NetSuite does provide a mechanism for recognizing gift certificate income for multiple gift certificates at a time, the native NetSuite screen does not provide our client an easy way to differentiate between gift certificates that were issued to their different customers for use on each customer’s unique web store.
Fortunately, our client is able to identify the gift certificate numbers that were generated and issued for each customer web store via their external eCommerce platform. In addition, they are also easily able to determine which ones have been used and which remain unused through their eCommerce platform as well. Periodically, they have a need to be able to expire the gift certificates and credit the original purchaser for the unused gift certificates instead of leaving the balance open in perpetuity.
Furthermore, our client can easily determine the total amount of the unused gift certificates issued through their various company web stores. They just needed a simple way to set the expiration dates on the unused ones. Once the expiration dates were set on these records for a specific company, they could easily use that as the basis for a journal entry.
The Solution
In the early days after our client’s go-live, they initially came to us with a spreadsheet of gift certificate transactions that they were looking to have expired and asked if we could help. Initially, our development team built a simple map/reduce script that could read in a file of gift certificate codes along with the desired expiration date and we would execute our script to run the drill. What we didn’t realize during that period hyper-care after going live is that the client would need to repeat this exercise on an ongoing basis for different customers and web stores going forward. So, after multiple requests to execute our map/reduce to handle this for them, we decided it was time to empower their users so they could handle this for themselves.
To enable their users to manage this process independently, we provided them with a drag & drop upload page that is able to consume either a CSV or Excel formatted file (similar to our MultiFulfillment and Bulk Item Constitution applications). On this page, we also provided their users with a simple template that can be downloaded to ensure that the columns are properly labeled for processing.
Within this simple UI, the user uploads the file of gift certificates to be expired. When the file upload completes, our map/reduce script kicks-off. The script runs in the background to set the expiration dates for the batch of gift certificates. We even provided our client with a special view to monitor the map/reduce to completion.
Once those expiration dates have been set (and typically they are all set with the same expiration date within a given batch), their users can then more easily leverage the native NetSuite screen for recognizing gift certificate revenue. Below is an example, of how our client’s users can easily filter to identify the gift certificates.
Typically, the GL impact of the resultant Journal Entry that was generated from this process will reduce the gift certificates liability (deferred revenue) account while also reducing the A/R related GL account so that they can provide a credit back to the customer.
The (Net)Suite Summary
While NetSuite is frequently recognized as a leading ERP platform, it is also a powerful development platform as well. In my opinion, it does not get nearly enough credit in that regard. While NetSuite provides many great features and functionality out-of-the-box, being able to easily design & implement solutions to address gaps such as the one highlighted in this article is the reason why I believe so strongly in this platform.
If you have a challenging NetSuite problem and believe that we can help with one of our SuiteApps or a custom solution, let’s have a conversation.
Note: This article was originally published on eXtendTech.net