Tag Dusty Products in Retail

josephmckeownjosephmckeown Posts: 35Moderator, Lightspeed Staff moderator
Sometimes Analytics may be used to prepare data for using in Lightspeed. This section of discussions touches on ways to use existing data to take further actions.



Credit where Credit is due.


As I mentioned in the discussion on best practices, asking for help is one of the best ways to find the best approach for calculations. This process of turning Dusty Inventory into a Pricing Rule is the result of a collaborative effort with my colleague Bryn Harris. 



Let's say we're looking at our Dusty Inventory report, but we want to take some actions within Retail to discount the prices or add the Products to a Price Rule.

We could use Calculations to either Define a new Price, or to add a tag to all the Products to quickly identify them in Retail for further actions. 

In this discussion, we'll look at creating tags for adding to Products in Retail.

Step One

Creating the Tag


How you define tags in Retail depends on what their desired workflow application is. I may want to search Retail for all Products that are "dusty" or I may want to find products that were dusty at a certain time. Let's start by creating a default "dusty" tag on the Dusty Inventory Report...

We can start with a simple version of the tag with no calculations, just the word "dusty" with a comma in between quotation marks...


this way, when I save my calculation, I get the simple "Dusty," tag on every product...


but let's say we also want some more detail in this tag, perhaps the date when the product is considered dusty...

Tags must be one string of characters with no spaces, let's say that today is October 22nd, 2018. A good detailed tag might be "dusty20181022" or "dusty2018oct22"

From our cell-based functions, we could use the "Concat" function to create a tag with the word "dusty", and then a calculation to create the date content in the tag.

Again, let's start just by creating the word "dusty" between quotation marks (this time, without the comma"


So that our report looks like...


and then, from our date-based functions, we could also add the "now" function to start creating the date components of the tag...


when we add "now", it returns the complete time-stamp...


which may be a bit much for our tag. So, also from the date-based functions, we could use the functions, extract_years, extract_month, and extract_days to pull just the content we're looking for...

So we start by entering just the formulae...


copying the calculation for "now"


and then pasting it between the brackets in the respective extract functions...


not forgetting to label everything...


So when we save, our report looks like...



and following our best practices, let's save the work that we have done here so far...


Now the dates are best if they are in sequence, so it's a good idea to create the dates as a number. To do this let's multiply the extracted year by 10,000, and multiply the extracted month by 100. Starting with the base formulae...


then copy+pasting the earlier functions...


Now when we save, the year, month and date...



can be added as one number. Pulling the three sources...


...copying...


...pasting...


..and fixing embarrassing errors, like extracting the hour and calling it the day...


...finally creating...


...so that when we save...



...we get one number, YYYYMMDD that will work whether the month or day has one digit or two...

And let's save our work so far As New...


Now a lot of the starter calculations here are kind of redundant, I'm going to remove "now"...


and all the "extract" functions...


So we just have these three calculations...


So now, we just need one final calculation to create the final tag: dusty+20181022

We can use "Concat" to do this.

Starting with the function and brackets...


...then copying the content, first "dusty"

and pasting...


...then our date number, copying...





...and pasting...


So now when we save...


...we get one tag combining the date and the dusty status.

Once again, let's save as new...


Now the Retail Import tool will let us import-add multiple tags onto products

First, if the tags are separated by commas, and secondly, if the title of the column is "Add Tags"

So we could add in our "Concat" function the first "dusty" tag, eg..


Let's also swap out the Product Description...


for the System ID


This will make mapping our new tags to our Retail products much easier


Finally, let's get rid of all but the last calculation...


So that all we have remaining is the System ID, and the new tags


and once again let's save as new...


Finally in Analytics, we can download our file...


...as a CSV...


Step Two

A bit of data prep


To make the export file something that works in Retail, there are a few adjustments we want to make. To do this, I'm going to open up my CSV in Excel...


First, we want to get rid of the line numbers in column A. So let's select Column A...


...right-click or control-click, and delete...


also, our System IDs have shown up in Scientific notation. To fix this, let's select them all in Column A


...right-click or control-click, and select "format cells"


and we're going to save as a number with zero decimal places...

So our data now looks like...


And it's ready to import!

Step Three

Importing the Tag


Now, as per the Import tools, we can navigate in Retail to Inventory> Import Products


start a new Import...


...select our new file...



...and ask Retail only to update existing products...


If our data is prepared properly, we will get a preview showing us how the mapping will take place...



...and we can import our sheet...


...so that the new tags are added to the products in Retail.

We can test this now, if we go to Inventory> Item Search...


We can search for products by tag...

...and see our results...


Now we can do the same in Quick Edit Items, in Price Rules, in Reporting, and in other Retail tools!

9940

Calculations Home
Joseph McKeown
Product Specialists, Post Sales
Lightspeed HQ
Sign In or Register to comment.