Analytics Calculations: Define Category + Importing data back into Lightspeed

josephmckeownjosephmckeown Posts: 37Moderator, Lightspeed Staff moderator
edited July 2018 in Reporting & Analytics
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.



One of the most common ways of reading your data in Analytics is by the Product Category. However, what if we have lots of products in Lightspeed that don't have any Category associated with them? Can we find a way to use data that we already have, and then use it to define the Product Categories in Retail?

Yes we can.

Lightspeed has an Import tool that we can use to update up to 1000 products at a time, including their field for Categories and Sub-Categories

A good thing to do first is to create your Categories in Retail

You can find the steps for doing this here:



Step One: Categorizing Products


We can use Analytics to identify Products without Categories in the Uncategorized Inventory report.

Let's start by adding the dimension of Item> IDs> System ID...


This is a good way to identify which products need to be updated when we Import the changes into Lightspeed.

Now, thinking about our IF calculations, we want to find some data in Lightspeed on which to base our Categories. This may not always be possible, but there are a few instances where it may be...

If all the products from the same Vendor should be in the same category,
If all the products from the same Manufacturer should be in the same category,
If all the products from the same Vendor that were received this week should be in the same category,
If all the products that contain the word "Jacket" in their Description should be in the same category,
If all the products that do not contain the word "Sneaker" in their Description should be in the same category,

...and so on.

To create a few examples, let's add Vendor, Manufacturer, and Days Since Received onto the report...



Example One


To start, let's say that everything we get from the Vendor "Hawley"...


goes under the Category "Wheels"... (let's leave the negative instances out for now)


and perhaps the Sub Category of "Aluminum Wheels"...


So that when we save, we'll see...



Ok, so far so good.

Let's save a working version of this...


Example Two


So now, let's say that our Products from the Manufacturer "Wheels Manufacturing" are also in the Top Level Category of "Wheels", but perhaps in Sub Category 1 of "Custom Wheels", we could go back to our first two calculations...


...and add the second IF statement...


So that when we save...



It's looking a bit better...

Example Three


Maybe some of the Quality Bicycle Products are Accessories/Tubes, perhaps the ones that we received in the past 30 days, 



Let's add starter individual IF calculations to identify the products that are from the Vendor "Quality Bicycle Products", and to identify those received in the past 30 days...


So when saving, we see two new columns...


Now, remember from our Logic Functions, the "And" statement...


We'll ask to look at the first argument...


...then the second


so that when saving...



We get a new column highlighting when both criteria are met!

So we can use our newest calculation as an argument in our IF statements for Top Level and Sub Category...


so when saving...




Now the only thing we need on this report are the categories, and because the rules are implicit, we can remove the calculations we built to find the Quality Bicycle Products that were 30 days old or newer...


and when done, let's save our work as new...


Step Two: Building an Import Sheet


Now that we have prepared the Categories and Sub-Categories for importing, we can make a few changes to this document for importing back into Lightspeed.

First, take a look at the article about Importing into Retail

To prepare our data, we want to hide all the columns from visualization except System ID, and the Category and Sub-Category calculations...


So that all we have are the columns for identifying or importing...



So now, we can download the data from Analytics...


I'm going to save mine as Excel...



and we can see our data ready now if we open the Excel file...


(Your System ID may open up in scientific notation, remember that you can format the numbers to be visible as a 12-digit number)

Now, we can follow the steps for Import/Updating Products into Retail!





Calculations Home

Post edited by josephmckeown on
Joseph McKeown
Product Specialists, Post Sales
Lightspeed HQ
Sign In or Register to comment.