How to use Excel to make a Category Filter

Here is a quick tip for getting a list of the most recent categories from your data feed.  This can be used when building a Category Filter or mapping names in the Categories tab of your Product Mapper.

The first step is to get a list of the latest categories from the data feed. i.e.

  1. Go to the “processed” folder, for example: /connections/my-store-connection/runtime/supplier/010-extract/processed
  2. Copy the “category.csv” file and open it in Excel
  3. Remove the SKU and position columns.  This leaves just the category names.
  4. Select all columns and do a “remove duplicates’ action so that it’s down to a unique list
  5. Custom sort by column1,2,3,4

This can then be copied into the Product Mapper “Categories” tab and “Category Filter” tab where you can make edits.

There’s no such thing as FREE shipping

People hate shipping fees.  They rank up there with getting a root canal. A customer will choose a store that claims to have “free shipping”. But is it really free?

Not really.  The shipping fees are embedded into the products.  That way, the checkout can show “$0 shipping”. The customer is still paying for shipping and in some cases, far more than what they would pay if shipping was calculated at checkout.

So how do you embed shipping costs?  These are some of the more common strategies out there.

Some suppliers provide Shipping & Handling costs for each product.  This is the simplest case, you can simply add this to your store price.

Other suppliers will provide a weight-based formula that you can apply to each item.  This allows you to embed the cost by running a calculation for each product.

In some cases a supplier will provide rates for dimensional weight.  They will also provide the length, width, and height of each product in their data feed.  You can use this, along with the dimensional factor, to calculate the shipping.  i.e.  (length × width × height) / (dimensional factor).

Then there is good old algebra.  Perhaps the supplier has really weird, non-standard shipping rules.  In these cases we can use the supplier’s shipping chart and create a formula that approximates the rate for each product.  i.e. store price = weight * factor + minimum shipping fee.  It is a ‘best fit’ calculation where the input is the product weight and the output is the approximated shipping rate.  This type of system requires a bit of give and take.  Some orders will have shipping calculated a bit too low, others too high.  The average will settle to a profitable range.

Which platform should I use for my store?

Shopify, WooCommerce, Magento, 3dCart, eBay, Amazon, SEOCart, Gambio GX3, PrestaShop, BigCommerce, the list goes on.  Let me tell you – they’re not all equal. Picking the right one can make a huge difference in the success of your store.

Let’s start by separating the Web Stores from the Marketplaces. Web Stores have the most freedom because you can control how the store looks, the categories you create, the types of products and images you load, and a ton of other features. Creating a web store is like having your own independent building with your own signs and parking lot out front. You are in control.

Marketplaces, on the other hand, are far more restrictive. They are like opening up a store inside a shopping mall. You’ve got to open when they open, pay rent to the mall owner, and follow their rules. The benefit of being inside a Marketplace is that the traffic is already there. You don’t have to do much work to get people to look at your products. But in return your product prices are pretty much the only differentiation because the Marketplace does everything they can to make every shop the same as every other shop. eBay, Amazon, Wal-Mart, Google, and Newegg all provide online marketplaces where you can sell products. Shop owners who don’t follow the marketplace rules are can find their stores suspended, unable to sell. So it’s best to learn these rules and follow them.

The most successful store owners can master both. By creating a Web Store and Marketplace presence you can build your own brand that people recognize. They might find your shop name in the Marketplace, then go to your store to place their order. It creates a sense of presence.

Today I am going to focus on the different web stores. There are so many choices and I find that most new stores start off with a web store rather than jumping into a marketplace. So this is a good place to focus.

Here are the most important questions to think about when choosing a platform.

How many products do you want in your catalog?

This is the most important one. If you know your store needs to hold millions of products, we can eliminate all of the choices and take a focused look at Magento. It’s currently the only one I have seen that scale. Product data can be partitioned at the database level and Magento indexing provides quick access. Automating a Magento store is also easier because of the database structure.

There are two primary versions – 1.9 and 2.0. It was a big (really big) change from 1.9 to 2.0. The whole front-end changed. When 2.0 was initially released it was very unstable and a lot of users backed away from it. But it has matured now to the point where it is leading over 1.9.

How much do you like technical mumbo jumbo?

If CSV data files and hosting make you feel extremely uncomfortable, it’s best to go with an all-in-one system like Shopify, BigCommerce, or 3dCart. These systems intentionally make life easier by removing the technical complexity. For example, you can have a store set up and running in minutes. The administration is super easy and allows you to create your own products.

However, there is a cost. The more advanced features aren’t there and you won’t be able to load millions of products. But this might not be a problem if you’re just getting started.

Some say that you shouldn’t use Shopify for automotive parts. I disagree. The argument is usually because of the lack of fitment support out of the box. But there are some excellent plugins that can make this work.

WooCommerce is more technically complex than Shopify because you need to host it yourself.  However, it has a clean user interface and is easy to learn.

Magento is perhaps the most technically complex but also provides the best level of scalability, into the millions of products.

Do you want to customize the store?

The all-in-one store platforms provide basic levels of customization.  However if you are looking to Salvatore Dali the heck out of it, you’re better off with WooCommerce or Magento.  Those platforms provide infinitely customizable themes, plugins, database extensions, etc.  There are also healthy pools of freelance talent available for those platforms.

How easy is it to connect other services to the store?

Some platforms are built for expansion. Shopify has their app marketplace, but as an app developer myself I find that there are significant hurdles within the Shopify app ecosystem that makes it less attractive for expansion.

WooCommerce and Magento provide incredible plugins and customization. They are built to be automated and have thriving marketplaces for plugins. There are connectors for external shipping, ERP, tracking, and other services.

Will I need to host the store myself?

WooCommerce, Magento, PrestaShop, and other open source platforms require you to host them. This means that you need a company that can run your store for you. There are a lot of good options.

Some companies like GoDaddy and 1&1 provide ultra low-cost ($11/month) shared hosting and claim to be able to run WooCommerce and Magento. While they can technically run, they can’t handle many products at all. I’ve seen these kinds of stores max out at around 1000 products. They run slow and the back-end throws a lot of internal server errors when trying to update pricing, images, add new products, etc.

The best scenario is to find a hosting company that gives you platform-optimized options. For example, WordPress (WooCommerce) optimized hosting is becoming more popular. This means that the hosting company is providing a high capacity database and dedicated resources. It takes some digging because some hosting companies claim to have optimized hosting but it’s really just marketing.

There are a lot of factors to consider when setting up a new store.  Choosing the right platform is like buying a new jacket.  When it fits, it feels great.  When it doesn’t, it feels like a big bag of burlap.  Choose the store platform that fits you.

Three things you need to get right when selling aftermarket parts online

Selling online can be overwhelming.  There are so many choices and moving parts to consider.  But no matter what you do, here are some essential survival tips for selling aftermarket parts online.

MAP Pricing

Minimum Advertised Price (or MAP) will make or break your store. Some brands will assign a minimum price that you can sell at. It makes the pricing consistent no matter where the parts are sold.  It’s similar to what you see with a new iPhone model. You’ve probably noticed that there isn’t a lot of variation between stores when a new model is released. They all seem to have the same price. This is MAP pricing. It preserves the perceived value of the brand and ensures profit along the supply chain. If any store sells below this price they risk being blacklisted, unable to sell those products any more.

So let’s say you sell BELOW MAP.

This becomes tricky because most stores have automated pricing set up. The store owner might not even know that they are selling below MAP until they get a “Do Not Sell” letter in their inbox. Make sure that your store is enforcing the rules and not allowing products to drop below MAP pricing.

This applies to Web Stores and Marketplaces alike. It doesn’t matter which you are using, MAP pricing must be enforced.

Product Data Quality

We’re in the pioneer days of online shopping. Most people don’t realize that anyone can set up their own online store. But that’s what’s happening now.  Likewise, most manufacturers still don’t know how to create high quality product data for online selling.

– No product images
– Product images that are way too big and hurt SEO
– Brand logos or Prop65 images instead of images that show the product
– Terse product descriptions
– Product descriptions that talk about the company instead of the product
– Titles that don’t describe the product

These strategies are proven in dealing with data quality issues.

  1.  Set up a test store and a live store. The test store is hidden and seen by only the store owner. The live store is what everyone sees and is polished. Use the test store to preview new brands before putting them in the live store. If they look like garbage, don’t put them in the live store.
  2. Clean the data yourself. Some problems can be fixed easily by Excel or an automated process. For example, some brands will include a logo image for every product they sell. This looks very bad in a store because it shows a lot of products with a logo image instead of the products. It’s hard to tell what the customer is buying. An automated process can strip out these images, leaving the good ones in place.
  3. Report the issues back to the brand. This won’t provide an immediate fix but it’s important to tell them that there are problems with their data. If they want to sell their products, they’ll take your feedback seriously. Once it’s fixed you can import the products into your store.


The way we search for auto parts is fundamentally different than a regular store that sells electronics, pet food, clothing, etc. In a way it is completely backward. For example, let’s say you are shopping for shoes. You typically start with the type of shoe then narrow it down to size. From there you might pick a color, then press the order button.

Automotive is very different. Instead, we start with the ‘size’ and work backward. Specifically, we start with the year, make, and model of vehicle that I have. So if I am looking for parts for a 2015 Ford F-150 I shouldn’t see Kia, Dodge, or Lexus parts on my screen. After choosing the fitment, a customer will typically go to the category…engine parts, electronics, performance parts, etc. to narrow the list of parts down further.

The store needs to live and breathe fitment. Each product needs to know what vehicles it fits and the store needs to give the customer a way to filter by fitment. Furthermore, when building a specialty store you’ll need a way to curate the products by fitment as well. The brand data you receive will have parts for a wide variety of vehicles. If it is a truck store, there shouldn’t be motorbike and sedan parts loaded into the store because it just doesn’t make sense.

There you have it.  Good luck and happy selling!

Category Mapping

Welcome to Data Here-to-There Ask An Expert, a series where our integration specialists tackle real questions from store owners who want to launch, build, and scale their stores. We can get you all the way from first store… to full-fledged marketplace.

Who are we? We’re the ecommerce data integration experts. We have integrated dozens of your stores, connecting them to a long list of suppliers from Banggood, DNH, Petra, RSR Group, BigBuy, Premiere, Keystone, SEMA and DCI. For a complete list of the suppliers and data feeds we support, look here

Since we started we have learned a lot from our customers. Do you have a question? Feel free to ask. Chances are good that someone else has had… or will have… the same question. Some of our customers are just getting started, while others have been managing entire online marketplaces for years now. So when you ask us a question, you are actually drawing on the expertise of a whole diverse team of ecommerce integration experts.  Let’s get started!


Question: How do I get the products from my supplier data feed into the right categories in my store?

Answer: With the Product Mapper

So you’ve decided what you want to sell online.  You’ve designed your website and chosen your suppliers. But how do you actually get your items into your store, in the right categories? If items aren’t easy to find, then how long are your potential new customers going to stick around? Answer: Not very long! But the challenge is that the supply category structure from your supplier might not be quite the same as what you want in your online store.

For example: say your store has men’s fashions – belts, shoes, shirts etc. – and you have your own categories for everything. But your supplier is giving you different categories, and so if you try to import the data into your store without mapping those categories, your items end up in all kinds of weird places. So what do you do?

Category Mapping: How it Works

We can rename categories and change the structure to suit your needs.  By default we simply import the same structure as the supplier as this is usually sufficient for a new store.

The system we use, the Product Mapper, is basically a translator.  It takes the source categories from the supplier and creates target categories for the store.  For example, let’s say your supplier has the following categories:

Men’s clothing > Accessories > Belts

Men’s Shoes > Trainers

Men’s Shoes > Volleyball

Men’s Shoes > Soccer

But maybe we don’t want that structure in the store.  Perhaps you as the store owner want something more simple, like this:



Our Product Mapper would turn the above into this:

Source: Men’s clothing > Accessories > Belts, Target = Accessories

Source: Men’s Shoes > Trainers, Target = Shoes

Source: Men’s Shoes > Volleyball, Target = Shoes

Source: Men’s Shoes > Soccer, Target = Shoes

 The Product Mapper is something you would get as part of your Data Here-to-There subscription, when you purchase a Catalog Data Mover. This handy gadget (the Product Mapper) does a number of things to get your products into your store correctly. One of these things is to map the categories from your suppliers to your store categories.

We use a spreadsheet to configure the system.  This allows us to bulk-load large numbers of categories, rules, filters, and other configuration.  We will configure the spreadsheet with you during the interview process to suit your store.  You can also modify it at any time.

Here is an example of the category mapping section.  We take the ‘source’ categories from your supplier and map them into ‘target’ categories in your store.  This is quite flexible so we can match pretty much any structure.

source_level1 source_level2 source_level3 source_level4 target_level1 target_level2
Clothing Women’s Scarves * Fashion Women
Clothing Men’s Hats * Fashion Men
Clothing Children * * Fashion Kids
Home and Garden * * * Home
Electronics Printers Ink * Technology Accessories
Electronics Printers Laser * Technology Printers

Once your categories have been set up and mapped, your store is ready to be loaded up with all your products! After the initial load it may be necessary to do a little tweaking. We can rename, move, make categories more generic or specific – whatever you require. Once all is set up to run smoothly then that’s just what your store will do. Allowing you more time to cater to your customers and stay on top of the market trends… or just sit back and allow that passive income stream to come rolling in.

The ImageFinder

Bad supply data is everywhere. The worst is when you get products without images. How can your customers tell what they are buying if they can’t see it? E-Commerce is mysterious enough – virtual products appear online and we order them. We hope that reality matches the online experience. So why make them guess what a product looks like?

Most carts will offer you the option to hide products which do not have images. But this can sacrifice a large part of your inventory.

We have come up with a way to solve this issue with our Shopify “Image Finder” app.

The Image Finder searches everywhere to get the best images for your store. It finds them, imports them, and lets you choose the ones you like. All automatically!

This app is great when:

  • Your dropship supplier does not provide images, or has a lot of missing images
  • You want to improve the quality of your product displays
  • You want to get rid of those annoying “image not found” placeholders

This app will do the manual work for you, searching for the best match for your products. It imports a set of pictures that you can choose from. All you need to do is review the images and discard the ones you don’t like. And it runs all day, every day to fill in the gaps when you add new products.

Images are also given some SEO attention, giving your store an extra boost!

Click here to check out our Image Finder App for Shopify.

We also provide image services for WooCommerce, Magento, 3dcart, EPages, PrestaShop, Wal-Mart, BestBuy, Amazon, and many others.

TopDawg Dropship Images

TopDawg provides a data feed to keep your store catalog up-to-date.  It has CSV data with titles, categories, descriptions, prices, and other things that are typically needed. The inventory is updated on a regular basis.

The products are great but the data feed has a common problem – missing images. Lots of “Image Not Available” placeholders.

How can you sell products with placeholder images?  Or completely missing images?  It’s not easy.  Most people want to see what they are buying.

If you are using Shopify the Image Finder app can help you to find matching product images and import them into your store.  We can also provide the same thing for WooCommerce and other carts.

The problem with CSV import plugins

The quickest route to linking a web store to a supplier is with an e-commerce plugin. They are installed into your store and usually need CSV data files from your supplier. Some have the ability to grab data from FTP, run on a regular schedule, and do other basic things.

Unfortunately they won’t grow with your business.

Plugins are meant to cover the lowest-common-denominator. They can do the basics but then will hit a wall as soon as you need to create a custom pricing rule, or change the product meta-data, or simply change how the inventory is updated. Plugins are created for the purpose of selling in volume and therefore the developers who create them are not interested in your custom requirements. However, any substantial e-commerce store will need customization.

Some of the more common questions I see are:

– What about my ERP system? I want to relay WooCommerce orders back to my supplier and they need them uploaded via FTP or with a web service.

– My supplier doesn’t use CSV. It is XML format. How can I load this into my Shopify store?

– How can I get my supplier’s shipping data back into my PrestaShop store so that my customers can use the tracking number.

– My supplier’s data feed doesn’t provide variants but I need them to show different t-shirt sizes. How can I fix this?

– Can you import products into my store categories for me? They don’t match my supplier but I can tell you which ones to map.

These are all exceptions which we handle on a daily basis at Data Here-to-There. We can adapt our innovative platform to any supplier or online service.

It’s surprising how many data feeds out there have low-quality product information. When I first got started integrating supply feeds for my clients I was shocked at how bad the data was. These are, after all, companies which *want* to sell their products. Yet they have terse (or missing) product descriptions, missing images, and so on. Data Here-to-There can actually enhance data feeds – create variants, add tagging, automatically find product images using Google, or even web-scrape entire product catalogs if your supplier doesn’t have a data feed!

Plugins are a great tool to get started with and can be sufficient for a simple single store, single supplier setup. But to grow your e-commerce empire you’ll need customized automation that fits your business strategy.

How to make a great data feed

If you are a drop-shipper, you need a data feed.

A Data Feed provides a catalog of products that merchants can import into their store. Maybe it’s a collection of kids toys with the SKU, title, description, price, and other information that the store owner can use to entice potential buyers. Data feeds are a critical component of any e-commerce supply chain. So how do you make a data feed?

To get started you need a list of products. Perhaps this is simply an Excel spreadsheet, or maybe it is stored in your QuickBooks system or in an inventory management database. The first step is to decide which products can actually be drop-shipped. There might be products like gift cards or rare products that you don’t want to supply. So those need to be filered. Now we create key information for the vendors. We need the following information.

SKU – stock keeping unit. This is an unique identifier which will be used when placing orders.
Title – The name of the product.
Description – A friendly explanation of the product which commonly includes HTML formatting.
Cost – The cost to your vendor, they will mark it up based on this number.
MSRP – Manufacturer’s Suggested Retail Price. This is higher than cost and used to indicate sale prices.
MAP – Minimum Advertised Price. Sometimes you want to set a lower limit on the price. Vendors can’t sell below it.
Stock – How many units are available to be sold? Vendors use this to prevent oversells in their store.
Manufacturer – The brand name of the product.
UPC – Sometimes referred to as GTIN, UPC codes are required for most major marketplaces.
Weight/Height/Length/Width – Determines shipping costs
Images – Pictures showing the product from different angles. These are stored separately.

The above data is required by most web stores and marketplaces so it’s good to provide as much as possible. It’s also important not to have empty values in any of these fields because it can break their store. Missing prices, titles, or other data causes a lot of grief because the vendors won’t be able to list them.

The most common data feed is simply a set of CSV (Comma Separated Values) files that vendors can download. CSV files are very easy to create, in fact you can make them using Excel. If you have a large inventory it makes sense to split your data feed into multiple CSV files. Especially the stock numbers – these fluctuate the most! So if you have a stock CSV file that will allow your vendors to grab it quickly and update their stores frequently without having to download *everything*. It saves your bandwidth costs too.

The Cadillac of data feeds doesn’t stop here. We can create a CSV file for product variants as well. This allows us to group collections of similar products. For example, a watch which can have a blue, green, or red background. It’s the same watch just with different colors. So we call these ‘variants’. The vendor has to be told how to connect up these variants though so this data is critical. Otherwise they have to list each watch as a separate product and that looks bad.

It’s also a great idea to create attributes as well. Descriptive tags help vendors sell products because tags can be searched in their store. For example a book seller can create an author tag, number of pages tag, an ISBN tag and so on. This data can be imported and allows customers to search by author, number of pages, etc. What a great way to help vendors out!

It’s also handy to publish a list of any products that you removed. This provides a signal to the vendor that they have to remove those products from their store. Otherwise they will end up selling products that no longer exist.

Data Here-to-There can design your data feed for you. We can use your source data and transform it into standard web-store compliant CSV data files. These files are then downloaded by your vendors and imported into their store. We’ll automate it as well so that it stays fresh every day.

Create your own marketplace

Marketplaces are really hot right now. On the freelancer sites I see daily requests to help create new ones. Marketplaces for fitness equipment, used goods exchange, health foods, and more. It makes a lot of sense because a marketplace owner can create a robust products collection by aggregating offerings from other vendors. However, I see one common flaw in those systems.

Most often the marketplace owner requires the vendors to import data on their own. It usually means submitting CSV files into a magical portal or manually updating products one-by-one. Not a lot of fun, right? Sometimes these vendors are not very technical so asking them to do this is a big job. It also puts the accuracy of the marketplace inventory in their hands. What happens if they get busy and forget? The marketplace will oversell which will frustrate customers.

A better way is to use automation. If the vendor already has a web store, just connect it to the marketplace. New products can be added automatically, existing inventory and pricing can be refreshed, and products which are no longer being sold can be removed. The vendor simply manages their own store and instantly has the benefit of exposing his products into a new market. Their store has essentially become a drop-shipper to the new marketplace.

If a vendor does not have a store already, why not use their existing work process? Let them use their own Excel spreadsheet format or connect to their QuickBooks system. This allows them to focus on running their store while the marketplace increases its product set.

Data Here-to-There specializes in Marketplace automation. We can connect vendors to your WooCommerce, Shopify, Magento, or any other e-commerce platform. Products are imported while you sleep and kept fresh. We can also relay orders from your marketplace to your vendors, integrate shipping, or a variety of other online services.