• Sam Sesti

What Do Calculated Fields and Felix the Cat Have in Common?

Surely I'm dating myself, but when I was a kid, Felix the Cat was one of my favorite cartoon characters. He was so resourceful, always getting himself into and out of trouble.


As the theme song so aptly captured:


"Whenever he gets into a fix, he reaches into his bag of tricks".

Didn't matter the situation, whatever good old Felix needed, he could find in that bag.


Report Toaster's new Calculated Fields feature is a lot like Felix's bag of tricks. Whenever our customers find themselves in need, we just reach into the calculated fields bag and pull out a solution.

A Little Extra

Report Toaster contains thousands of Shopify data fields (plus many of our own). These fields can be customized, filtered, and sorted in almost endless combinations. But we often get requests for something just a little bit extra. These requests range from simple calculations (like a percentage-based commission) to more complex derivations, like aging buckets. Think of them like Excel formulas taken to another level.


Using Calculated Fields, we can create new fields which can be added to any report. Once on a report, they can be filtered, aggregated, sorted, and exported like any other field.


The possibilites are really only limited by your imagination. So let's cover some of the requests we've received and see what Felix has in his bag.


Calculating Affiliate Fees

Some of our customers pay a fee to affiliates that drive traffic to their store. These fees are typically calculated after taking into account the net profit from a sale, which means deducting items like transaction fees and taxes.


Thankfully, we don't need to go too deep into the bag for this one. We can use some simple arithmetic operators to provide a bottom-line profit number. Multiple that profit by an agreed-upon affiliate percentage (e.g. 30%) and the result is the amount owed to an affiliate for the sale of a product.

Combine this with our Scheduled Reports or Shared Links features and you could easily share a report like this directly with one of your affiliates.


Aging Buckets

Some of our customers don't collect payment upfront for all of their sales. Instead, they rely on invoicing and collecting payment at a later date, which results in receivables that need to be tracked.


One such customer wanted to see their receivables represented in aging buckets (like you might typically show on an Aged Receivables report). Well, we had to go a little deeper into the bag for this one.


First, we need to calculate the number of days the order has aged. If an order is fully paid, the aging days are the difference between the order date and the paid date. If not fully paid, the aging days are the difference between the order date and the current date.


Once the aging days are calculated, we can put the unpaid amounts into buckets.

In this case, the customer wanted them separated into 0-30, 31-60, and 60+:

This can then be used to report aging at any level, looking at orders, customers, or the entire store.


Mapping Staff Names

Many of our customers use the Shopify POS system and want to know which staff member was involved in a sale. Unfortunately, Shopify does not allow app developers API access to the names of a store's staff.


In these cases, we can provide merchants with a list of their staff ids and they can provide us with the corresponding staff names. Using a calculated field, we can then map both of these together and display the data like so:

That's pretty tricky!


Making Tags Useful

Almost all merchants use customer, product, or order tags to enhance their store's capabilities. As we've previously covered here and here, Report Toaster already provides advanced functionality related to tags.


But you ain't seen nothing yet!


Using calculated fields, we can take the information from a tag and turn it directly into data that can be used in reporting.


For example, many of our customers also use the Zapiet app to manage pickup and delivery. Zapiet is a great app and makes the delivery process seamless for shoppers. However, our customers often ask us to help them leverage the resulting data, which gets stored in order tags like so:

As you can see from the screenshot, we created a calculated field that pulls the delivery date from the order tags and moves it into its own field. This field can now be used just like any other field in the app, so you could do things like:

  • Filter to deliveries in the next 3 days.

  • Filter to deliveries on Wednesday of this week.

  • Share a link of today's deliveries with drivers.

  • Text or email a report of tomorrow's deliveries to staff that prepares product for delivery.

That one made us dig a bit deeper into the bag!


Managing Personalization

Many merchants provide some form of personalization for their customers. Whether its a name on a jersey, messaging on a charm bracelet, or special instructions on a meal order, personalization has become a staple feature for online orders.


One of the most common ways for Shopify merchants to provide personalization options on an order is via the Variant Product Options app. This app is very popular, as it allows merchants to break through Shopify's 100 product variant limit and provide additional options on their products.


The app accomplishes this by using an order's Note Attributes and/or Line Item Properties to store any extra data. This is clever and provides amazing functionality, but requires a little bit of extra effort to get those details onto a report.


Let me see what we have in that bag...

Here we are able to use Calculated Fields extract the chosen sport from the Note Attributes field on these particular products. Once extracted, this data can then filtered, put into its own column, and even exported easily via CSV. I think that's our best trick yet.

 

We Welcome a Challenge

Currently, we handle the creation of any calculated fields you might need. All you have to do is sign up for a paid plan of Report Toaster and then contact us at support@cloudlab.com with your request.


Feel free to challenge us! We have already been pleasantly surprised with the requests we've been able to accommodate in the first few weeks, so we can't wait to see what you come up with.

24 views0 comments