A few years ago I was working with a Fortune 500 restaurant chain that many of us frequent. What you probably don’t know is how often rats frequent their restaurants too. The chain was trying to reduce the number of pest incidents at the worst offending stores, but was having trouble determining which stores were worst because their data was so messy.
They had vendors who would go to their restaurant sites on a regular basis and do routine checks to list and report issues. Sometimes those issues specifically related to pests, sometimes they were just general structural concerns. When the vendors listed what the incident type was, it was keyed in by hand. The issue is that there are many possible words that might indicate a pest incident. Rodent, mouse, rat, vermin. We needed a way to consolidate all the existing values and also account for any future combinations we weren’t aware of. Here’s a sampling of the types of incidents listed:
What we needed to do was create a calculated field that separated the pest incidents from the other types of incidents. What we did looked something like this:
We used a regular expression function to search a field and whether it contained a variety of values. Notice I also used the LOWER() function so I didn’t need to worry about case sensitivity.
We were then able to use this field as a filter and create a dashboard that helped us narrow in on the worst offenders. From there, calls were made and (rats’) heads rolled.
(Disclaimer: This is all mock data. None of the locations listed here are actual restaurant sites).
Need more help? Please contact us at firstname.lastname@example.org.