Common ID between Retail and eCom?

VintageWineGuyVintageWineGuy Member Posts: 120 ✭

Am I missing something, or is their really no common ID between Retail and eCom? I am using omnichannel (both Retail and eCom), and am trying to do a couple things including build a data warehouse, set up some processing to batch sync and update some data, and realizing there is no exposed system key that is common between the two?

Am I missing something?

If I am not, what have others done to maintain continuity between data? I don't have UPC/EANs on all my items, not all of my items have the same names (we edit them in eCom to optimize them). What have others done?


  • tpbolatpbola Member Posts: 15 ✭

    I'm trying to figure this out as well. Chat support advised there was not a unique identifier shared between eCom and Retail. That seems crazy to me. The more business I do through "OMNICHANNEL" the more I am finding a need to sync these databases.

  • DanielBrodskyDanielBrodsky Member Posts: 8

    In retail I apply the systemID to customsku. This translates to the article code in eCom. When querying from both Retail and eCom I join systemID with article code. Hope that helps!


  • VintageWineGuyVintageWineGuy Member Posts: 120 ✭

    @DanielBrodsky I am doing the same now. I added a call to my nightly batch that copies the systemID to the customSKU for any items I missed so I don't have to manually Copy/Paste. And I am pulling all my data out of LS (both eCom and Retail) in to a separate database so I can do reporting, queries, automation, etc.

  • gareth_egareth_e Member Posts: 30

    @VintageWineGuy what tools or platforms have you had success with for connecting to the APIs and pulling, editing/reporting/using, pushing data? I've tried a few but have had a few issues just curious as to what's worked for you? Much appreciated thanks

  • VintageWineGuyVintageWineGuy Member Posts: 120 ✭

    My workflow is certainly not best practice but based on what I know and can do easily/cheaply.

    I use Python to write scripts to pull data from the API.

    I use Talend to process the JSON I get from the API and push it to a MySQL DB. I typically extract ALL the data from the key things in Lightspeed like Customer, Sales, Items, etc. and have tables I just drop and rewrite with the current data. I haven't gotten around to trying to do incremental updates, so just drop and refresh everything.

    I use PowerBI Desktop to connect to MySQL and do reports.

    It takes me maybe 15-30 minutes to manually click through each step and typically do it once or twice a month for reporting or whatever else.

Sign In or Register to comment.