Import Competitor Prices

What is this?

If you shop your competitors and keep the results in a spreadsheet, you can load those prices straight into Margin Master. The prices land in the Competitor 1 through Competitor 5 fields, where they behave like any other price level: you can display them as columns, compare against them in the selection boxes, and set future prices from them in a pricing strategy.

You can load one competitor or all five, from a single file.

Where to find it: Data ▸ Import Competitor Prices…

Walk through it in the app: guide 2018 — Importing competitor prices. Margin Master can walk you through these steps live: turn on Training / Guide Mode from the Help menu, then type 2018 into the Lessons & Guides box — or ask Margin Master support to run it with you.

Before you start

Prices are matched by SKU and applied to every store in your database. If a SKU in your spreadsheet does not exist in Margin Master, that row is skipped and noted in the log.

Important — re-import after each rebuild. Competitor prices live in your main tables. Anything that rebuilds those tables clears them: a POS data import, a cloud vendor sync, or a manual Rebuild Main Tables. Keep your spreadsheet and re-import it afterwards.

Competitor 1–5 may not start empty. On some point of sale systems — Epicor and Paladin among them — your POS supplies its own price levels into these fields, so every item can already have a "Competitor 1" price before you import anything. See Replace or Add below, and check the counts the import window shows you.

File format

The first row of your file must be a header row. Margin Master reads the header names to work out which column is which, so the order of your columns does not matter and any column it does not recognize is simply ignored.

Recognized column names

You need the SKU column and at least one of the Comp columns. Everything else is optional.

Capitalization, spaces and underscores are ignored, so sku, SKU, Comp1, comp 1 and COMP_1 all work. Anything else — including the display name "Competitor 1" — is not recognized, so use the short names above.

Example

Here Notes is ignored, Comp1 and Comp3 are loaded, and Comp2, Comp4 and Comp5 are left alone. The blank cells are skipped, so SKU 1234567 keeps whatever Competitor 3 price it already had.

Getting the format right

Click Download Template in the Import Competitor Prices window. It saves an Excel file with the correct header row already in place, plus an Instructions sheet, so you can fill in your prices without worrying about the layout.

Accepted file types

.xlsx, .xls, .xlsm, .csv and .txt (semicolon-, pipe- and tab-delimited text files also work).

How values are read

Ignored never means zero. If a cell is blank or unreadable, Margin Master leaves whatever price that SKU already had.

Duplicate SKUs: if the same SKU appears twice, the last row in the file wins. Both are noted in the log.

Replace or Add

Once your file is loaded, the import window shows one line for each competitor column it found, with a choice:

Next to each choice Margin Master tells you how many items already carry a price for that field, for example "101,391 of 106,565 items currently have a Competitor 1 price". That number is what the choice is for.

When to use Replace

• Your POS already fills the field and you want it to hold only your own prices. This is the usual case on Epicor and Paladin. Replace is what makes a filter like "Competitor 1 is greater than 0" return only your items instead of the whole catalog.
• You are re-loading a shop report and last month's prices should not linger on items you no longer track.

When to use Add

• You are building a field up from several files — one vendor at a time, one department at a time. Import the first file with Replace to start clean, then each later file with Add.
• You are topping up prices for a handful of SKUs and everything else should stay as it is.

Replace asks you to confirm before it runs, and tells you how many values it is about to erase. Erased values are not recoverable from within Margin Master — but if they came from your POS, your next POS import brings them back.

Choices are made per column. You can Replace Comp1 and Add to Comp2 in the same import.

Importing

1. Open Data ▸ Import Competitor Prices…
2. Click Download Template if you want a correctly formatted file to start from.
3. Click Browse… and pick your file.
4. Margin Master reads the file and shows you what it found — how many SKUs, which competitor columns it recognized, which columns it ignored, and how many values it could not read. Nothing has been written yet at this point.
5. Pick Replace or Add for each column (see Replace or Add).
6. If it looks right, click Import. Replace asks you to confirm first.
7. When it finishes you get a summary, and Open Log shows the full detail.

If the header row is missing a SKU column, has no Comp columns at all, or contains a duplicate SKU or Comp column, Margin Master tells you and does not import anything.

The summary tells you how many SKUs were applied, for example "Imported 276 SKU(s) — 248 applied across 3 main table(s). 28 SKU(s) are not in this database." SKUs that are in no main table are listed individually in the log.

Seeing the prices

Competitor 1 through Competitor 5 are not displayed by default. After a successful import, Margin Master offers to add the columns you just imported to your active column view — click Yes and they appear in the grid, and become available in the price-level selection box.

The column is added to whichever column view is active at that moment, which is not necessarily the view you think of as "yours" — column views and selection box views are separate, and switching one does not switch the other. If the column does not appear where you expected, check which column view is active.

To add or remove them later, use Edit Columns Displayed from the grid header or the View menu.

Using competitor prices

Once loaded, the competitor fields work like any other price level:

• Compare — the selection boxes let you filter on Competitor 1–5 the same way you filter on Retail or Level 1.
• Variance columns — C1CPVar and C1FPVar show how your current and future prices compare to Competitor 1 (and likewise for Competitors 2–5).
• Pricing strategies — a strategy step can set the future price to a competitor price, or use one as the comparison operand in a rule. See Pricing Strategies and Future Pricing.

Note for shared strategies: rules that reference Competitor 1–5 are specific to your own data. They are not portable to another store, and publishing a shared strategy warns you about this.

The log

Every import writes a log, whether or not anything went wrong:

C:\ProgramData\RetailerSoft\MarginMaster\Logs\CompetitorPriceImport_yyyyMMdd_HHmmss.log

It records the file you imported, which header column mapped to which field, whether each column was Replaced or Added to (and how many values were erased), any columns that were ignored, a line for every value that could not be read, and a closing summary of how many rows were updated. Click Open Log on the completion message to view it.

Common questions

Can I import for just one store? Not at present. Prices are matched on SKU and applied to every store in the database.

Do I have to fill in all five competitors? No. Include only the Comp columns you have data for. Columns you leave out are not touched.

What if some of my SKUs are not in Margin Master? They are skipped and listed in the log. The rest of the file still imports. The completion message tells you how many were applied and how many were not found.

My prices disappeared. Anything that rebuilds your main tables clears them — a POS data import, a cloud vendor sync, or a manual rebuild. Re-import your spreadsheet.

Filtering "Competitor 1 greater than 0" returns almost every item. Your POS is filling that field with its own price level, so nearly every item already has a value. Re-import with Replace all existing values and the filter will then return only the SKUs in your file.

Can I clear a competitor price? Yes — import with Replace all existing values and the field is emptied for every item before your file is applied. To clear a single SKU, enter 0 for it; leaving the cell blank means "no change".

Can I load a field from more than one spreadsheet? Yes. Import the first file with Replace to start clean, then each later file with Add to values already there.

Technical details

For support staff.

• The import stages the file into a CompetitorPriceStaging table, then applies it to each live main table and to [Store].Comp1..Comp5.
• The main tables are what matters. They are what the grid, the selection boxes and the pricing strategies read, and they are what "N SKUs applied" and "N SKUs are not in this database" are measured against. [Store] is written too because it genuinely IS the POS table for the BCP import paths (Transact, Paladin, WestLake, Falcon, DIB-with-no-POS). It is not the POS table for Epicor/Compass, Mi9, RockSolid or BisTrack, so its row count says nothing about whether an import worked — on a Compass store [Store] typically holds only a handful of placeholder rows.
• The update uses COALESCE(staging.CompN, target.CompN), which is what makes a blank or unreadable value a no-op rather than a NULL or 0 overwrite. That holds in both modes: Replace first runs UPDATE <table> SET CompN = NULL WHERE CompN IS NOT NULL — unfiltered, so items absent from the file end up with no value — and the COALESCE update then lands the new prices.
• Only the Comp levels that actually carry at least one value are included in the UPDATE.
• The Replace/Add counts come from a single UNION ALL scan of the main tables counting CompN > 0 per level.
• Competitor prices are not durable across a main-table rebuild in any POS path. There is no separate competitor-price table.
• On Epicor/Compass, Comp1–Comp5 are mapped from the POS's own in_price_1..in_price_5 (SqlInsertNewCompassINToMainTable.sql), so all five arrive pre-filled. Paladin supplies them too.
• Comp1 appears in the DIB_NoPos default column view only; Comp2–Comp5 are in no default view. A Comp field only reaches the price-level selection box when it is a column of the active column view.