How to Combine Vendor Central Sales, Inventory, and Traffic Reports Into One ASIN View

You have three files on your desktop: a Sales export, an Inventory export, and a Traffic export, all pulled for the same weeks. The question you actually need answered sits across all three. Which ASINs are getting glance views and converting, but are about to run short on stock? No single report can tell you that, because Vendor Central gives you each one as its own download, and putting them side by side is left to you.

This guide picks up where the guide to exporting Vendor Central reports leaves off. If you don't have the files yet, start there and name them the way that piece suggests so you can tell at a glance which view, granularity, and date range each one holds. For what each report contains and when it refreshes, the Vendor Central report definitions guide is the reference. Everything below assumes the three files are open in front of you.

Pick ASIN as the Join Key, and Check It Is Really the Same ASIN

All three reports are reported at ASIN level, so ASIN is the column that ties them together. Before you write a single formula, make sure the ASIN column looks identical in every file, because a formatting difference makes a lookup miss without showing any error.

1. Open each file and find the ASIN column. Note its column letter in each one, because you'll need those letters when you write the lookups.
2. Set the ASIN column to Text format in every file. A spreadsheet that decides a column is numeric can reformat values that look like numbers, and a reformatted key never matches its twin in the other file.
3. Trim stray spaces. Run =TRIM() over the column, or use Find and Replace, so that "B0XXXXXXX1 " and "B0XXXXXXX1" stop being two different ASINs.
4. Check for duplicates. Within one file and one period, each ASIN should appear once. If an ASIN appears several times, the file holds more than one period (daily rows, for example), and you need to settle granularity before joining. More on that below.

The Join, Two Ways

Choose a base file first. Sales is the usual choice, since the ASINs with sales activity are the ones most questions start from. Every other file gets pulled onto the base file's ASIN list.

XLOOKUP, for a one-off

In the Sales file, add one new column for each Inventory or Traffic figure you want to bring over. Each column gets one XLOOKUP. With ASIN in column A of your Sales sheet and the Inventory file pasted into a sheet named Inventory, a formula looks like this (the column letters are placeholders for wherever your file puts them):

=XLOOKUP($A2, Inventory!$A:$A, Inventory!$D:$D, "not in inventory file")

The fourth argument matters most. Without it, an ASIN missing from the Inventory file shows up as #N/A, which is easy to filter away without thinking. With it, every mismatch shows up as readable text, and you can count them. Use a different fallback string for each source file ("not in traffic file") so you always know which file was missing the row. XLOOKUP works the same way in Excel and in Google Sheets.

Power Query, for a weekly routine

If you plan to do this every week, rebuilding the formulas becomes the slow part, and Power Query in Excel lets you build the join once and rerun it.

1. Load each file as its own query from the Data tab, using Get Data and then From Text/CSV.
2. In the Power Query editor, set the ASIN column's data type to Text in all three queries.
3. Select the Sales query and choose Merge Queries. Pick Inventory as the second table, match on ASIN in both, and set the join kind to Left Outer so every Sales ASIN is kept.
4. Expand the merged column and tick only the Inventory fields you need.
5. Repeat the merge with the Traffic query.
6. Close and load the result to a new sheet.

Next week, drop the new exports into the same folder, update the file paths under Data source settings, and refresh. The join, the column choices, and the data types all carry over.

Where the Join Breaks

A join can run without a single error and still be wrong, so when the result looks off, check these five problems first.

Distributor view does not match across files

Sales and Inventory each carry a Sourcing or Manufacturing distributor view, and Traffic has no such setting. Sourcing view covers the ASINs Amazon bought from your vendor code. Manufacturing view covers every ASIN under your brand, whoever supplied it, and the two views produce different ASIN lists and different totals for the same catalog. In Amazon's Vendor Analytics Dataset, Sourcing view also excludes traffic altogether, so a Sourcing-scoped file and your traffic figures won't share one ASIN list.

In practice, a Sales file pulled in Sourcing view will have a narrower ASIN list than your Traffic file, and you should expect unmatched rows on both sides. Check the view on every file before you assume the join is broken. The Retail Analytics walkthrough covers where that setting lives.

Granularity does not match

Sales and Traffic can be pulled at daily granularity, but Inventory Health tracks stock position on a weekly update cycle. If you join a daily Sales and Traffic file to Inventory, you get one inventory figure repeated across seven daily rows, or no match at all, depending on how the rows are keyed. The cleaner path is to pull Sales and Traffic at weekly granularity for the same weeks as your Inventory file, so every file holds one row per ASIN per week. If you need daily Sales detail for another purpose, keep it in a separate sheet.

An ASIN appears in one file and not another

A catalog change, an ASIN with no activity in the period, or the distributor view gap above can each leave an ASIN in one export with no partner in another. The fallback strings from the XLOOKUP step help here: filter each lookup column for its "not in" text and read the list. A join built from the Sales file only shows you Sales ASINs, so run one reverse lookup from the Traffic and Inventory files as well. An ASIN with glance views but no sales row is worth a closer look, and so is one with inventory on hand and no traffic.

A readable product name may not come with the data

Whether each export carries a product title column depends on the report and the columns you had showing when you downloaded, so check the headers in your own files before you build a sheet that someone else will read. If the name is missing from your base file, bring it across from whichever file does carry it with one more XLOOKUP, the same way you brought over the Inventory fields.

Currency and unit columns look alike

Shipped COGS is a currency amount, while Shipped Units and on-hand units are counts, and a formula that divides the wrong pair still returns a number that looks plausible. The same trap applies if you sell in more than one marketplace and join a file in one currency to a file pulled in another. When you bring a column across, rename it with its unit, such as "Shipped COGS (USD)" or "On-Hand (units)", so a mismatched pair is easy to spot when you write the next formula.

Two Columns Worth Adding Once the Join Exists

With a clean, one-row-per-ASIN table, two metrics are a formula away.

Weeks of Coverage is total inventory divided by shipped units for the week. It is a true cross-report figure, with the numerator from Inventory and the denominator from Sales, and Vendor Central doesn't surface it anywhere in its own reports, which is the main reason to build the join in the first place. The Weeks of Coverage glossary entry also covers the variant that adds open PO quantity to the numerator.

Unit Cost is shipped COGS divided by shipped units, falling back to on-hand cost divided by on-hand units when shipped COGS is blank for the period. Each input pair comes from a single report, so this one doesn't need the join, but once the sheet exists it's one more column. See the Unit Cost glossary entry for the exact definition.

Guard both formulas against a zero denominator with IFERROR, or an ASIN that shipped nothing in the week will fill the column with errors.

What Vendor Central Will Not Give You

Everything above has to happen again next week. You export three files, check the distributor view on each, confirm the granularity lines up, refresh the lookups, and scan the unmatched rows to see whether the ASIN list has drifted. Power Query shortens the refresh, but it still depends on someone pulling the right files with the right settings every week. This is the missing join the export guide mentioned, and Vendor Central has no setting that does it for you.

InsightLeap does this join continuously. It brings your Vendor Central, Advertising, and Amazon.com product data together automatically, so product-level sales, inventory, and purchase order data already sit against each ASIN without a weekly rebuild. The features page covers what it tracks.

If you are doing it by hand, here's what to do this week: pull all three reports at weekly granularity, in the same distributor view where the setting exists, over the same weeks, and build the Power Query version once. After that, next week's refresh is mostly a matter of swapping in the new files, and the unmatched-row check is the part that still needs your eyes.