Verizon - RMS Unefi Integration

BDSMktg was contracted by Verizon to execute Break Fix work in retail locations. As part of this program Verizon requested an integration between InsightCenter and RMS's Unefi data collection systems. This article is a documentation of the process to be carried out for each project to support this integration.

Process Overview

The data integration between ic and Unefi is being done through the transfer of .csv files using a Unefi hosted FTP site.

The following steps are followed for each Verizon program:

A program file is generated by Unefi and placed in an outgoing folder on their FTP site
The program file is downloaded by an IT process, reviewed.
The program is setup in an ic database to track the program ID and program data collected
Workbook sent to Account team for program details and Items verification
Items for the program are auto created in a process by the IT team to ensure consistency.
Account Team sets up call form using the correct ic Library question to denote completion
Program is launched and program execution is exported to Unefi on a agreed upon intervals by IT backend process.

1-3 (IT) Program File from RMS/Downloaded/Project Setup

Steps 1 though 3 of the process are completed by IT for setup of the project.

Unefi starts the process by placing a new Program file in a folder on their FTP site. This file contains the setup information for the program.
A new program is setup to track this data in IT
The program file is downloaded and the quality reviewed by IT.

RMS Program File

Format

The program .csv file from RMS is named with the following format:

ProjectID_ProjectName_YYYY-MM-DD.xlsx (ex: 72_TEST_2020-05-01.xlsx).

Column Definitions

The program file from RMS has the following columns:

Store ID – internal unique store id at Unefi
Channel ID – Unefi internal chain ID. 1 for Best Buy, 2 for Target
Store Number – chain store number
Fixture ID - ID of the fixture in Unefi
PS_ID - PS_ID is a Unefi internal ID that is specific for each fixture in each location. Basically there should be no duplicate entries in this filed. Same fixture have different ID in different location. Appears to be per SKU/FixtureID combination
Fixture Name – textual name of the fixture in Unefi
Fixture Zone – zone of the store in which the display is placed
Spot On - the position identifier of where the SKU is to be placed on the fixture
Title – textual name of the sku
Version Code – sku id in Unefi
Status – status of the program work on this display at this store
Order Date – date/time a product was ordered to replace one that had an issue.
ImageLinks – All image URLs of the particular display are concatenated into 1 comma separated value.

4 (IT/Account Team) Workbook Provided/Feedback Requested

Upon receipt of a project setup file from RMS, Data Services will provide the Account Team with an Excel workbook containing details and action items drawn from the file's contents.

Information required to be provided by the Account Team is indicated below. Each of the sections below is a tab on the workbook.

Call Form Info

A new call form is needed for every project. The Account team will provide Data Services with the the following information about the call form setup.

Column Name

Comment

CallFormID

CallFormName

Program File Review

This tab includes an exact copy of the program file sent to BDS from RMS.

It is recommend the Account Team review it and use it for their setup. The call form/Items/authorizations setup much match the file for the data to be transmitted to RMS electronically.

Column Name

Comment

Store ID

Channel ID

Store Number

Fixture ID

PS_ID

Spot On

Fixture Name

Fixture Zone

Title

Version Code

Status

Order Date

SKU - Dupe by Title

A tab listing SKUs with the same [Title] but different [Version Code].

It is recommended the Account Team provide a unique ItemNameBase (on the SKUs Requiring ItemNameBase tab) to more uniquely differential between anything listed here.

Column Name

Comment

Shared Title

Version Code

SKU - Dupe by Version Code

A tab of SKUs with the same [Version Code] but different [Title].

This will prove problematic because if they are the same how will the team or field reps differentiate between them.

Any entries here would need to be discussed with Verizon by the Account Team and fixed by RMS.

Column Name

Comment

Shared Version Code

Title

SKUs Requiring ItemNameBase

A tab listing SKUs not yet in any program that the Account Team will need to provide base item names and ItemCategoryIDs for. Although optional, it is suggested a more "friendly" Item Name be created here for obscure SKUs' names to be more meaningful to the rep.

ColumnName

Comment

Title

Version Code

ItemNameBase

base used for the instances of each Item to be created

ItemCategoryID

ItemCategoryID to which to assign all instance of the SKU created in dbo.tblItem

Store Issues

A tab listing Stores that either is:

Mapped to a store in our store list but is indicated as “closed”, or
Not yet in our store list.

The account team will need to submit store requests to add the store or update the status of a store to resolve any issues listed here.

ColumnName

Comment

Store ID

Channel ID

Store Number

BDSStoreID

will only be populated should a store be mapped to our store list but indicated as “closed”

Issue

indicates the issue, such as the store being closed or not in our store list

Workbook Returned to Data Services

Once all required information has been filled out and issues resolved, this workbook is to be returned to Data Services to continue program setup for data export.

5 (IT) Item Creation

Items will need to be created and maintained by Data Services for these projects. This is to ensure proper mapping and data integrity.

Because Items (aka: SKUs) can exist multiple times on a single fixture, Items will be created for that SKU for the maximum occurrence count on an individual fixture.

The convention is [ItemBaseName] + “ [Position - n]”, where n is the position identifier as provided by RMS in the Spot On column of the file. For example Large Graphic [Position - 1], Large Graphic [Position - 2], Large Graphic [Position - 3] represent three instances of the same SKU in three different positions on that fixture. This is required for proper mapping to the program setup file and subsequent export.

6 (Account Team) Call Forms Question Setup

Call form creation can be done at the same time as the review of the items. Item/Question associations however cannot be started until the items are created.

These are the requirements for call form setup:

Call Form Name – no special requirements
Question Set – no special requirements
Task – no special requirements
Grid Questions – must textually match fixture name from RMS program file
- For example: “BBY Experience Large”
Item Questions – must use specific library questions
- Library Question ID - 1489 - Upon Departure was the SKU compliant per Verizon's instructions?
  - Library Question Block - UNEFI
  - Item Question level
  - Choices
    - Yes, compliant upon arrival
    - Yes, made compliant during visit
    - No, unable to locate signing
    - No, Signing damaged
    - No, store refusal
    - No, Fixture not compatible to place signing
    - Not needed in this fixture ( This option is only used for Multiple Hotspots SKU's)
      
      Exported response derived from Library Question Answer Choice text with the convention “No, “… or “Yes, “…
- Library Question ID - 1491 (image)
  - - Below questions are only related to "BestBuy"

- - Below questions are only related to "Costco"

7 (Account Team) Program Execution (Special Handling for Multiple Hotspot SKU's)

For Fixture's that have multiple SKU's at the same position, only one of the SKU should have the complaint question answered, all other SKU's should have the choice "Not needed in this fixture" entered by the rep. Account team as part of their QC process should make sure that rep has correctly handled such SKU's.