E-Commerce Data: Tips for Formatting with Google Sheets

Running an e-commerce store is a lot of work for a business owner. Everything falls on your shoulders, from driving orders through marketing to fulfilling those orders. One of the biggest hassles when running an e-commerce store is filling the store with inventory. Luckily, there are many processes in place to help make this as smooth as possible. For example, sometimes vendors can provide you a catalog of new products to list on your site in CSV format. Other times, if you have a brick and mortar store, you can use connectors to link your POS or ERP inventory system to your e-commerce store to import your products that way.

While all of these solutions were created with the intention of making less work for the store owner, the remaining challenge is that the exported data from a vendor or a POS comes out in a CSV that seldom aligns with the format for CSV import to your e-commerce store. This leaves store owners in a pickle, because without the know-how to rearrange the data, they’re essentially at square one.


Also on StartupNation.com: Boost Your E-Commerce Business Branding


Let’s look a one method I use when restructuring e-commerce data in Google Sheets:

This is such a useful feature in Google Sheets, and the formula can be applied to so many situations. Once you have an understanding of the formula and how it works for your e-commerce data, it’s easy to remember and apply as the need arises.

Here’s an example: We have a client who wanted to list a product’s minimum order quantity and the product number as part of the description that would display on the product detail page. We had both pieces of this information in a spreadsheet provided by the vendor, as well as the item description. The challenge is that when we import this description into their e-commerce platform (such as Shopify), we want to import the description with both pieces of information already appended so we don’t have to manually add it later in the Shopify backend.

Here’s how to use a simple formula to combine all of this data, while adding some basic HTML so it renders spaced correctly once it’s imported:

Ecommerce Data

In this example, we have the following e-commerce data:

Column B: The vendor supplied product description

Column C: The vendor supplied minimum quantity

Column D: The stock number

We want to combine these all into cell E2.

We start editing the value of the cell E2 by opening a formula with an equals, immediately followed by B2 to indicate that we want the contents of the cell B2 to be transposed into this cell.

To append additional information, we follow the cell number with an “&” symbol. Immediately following this, we include the exact information we want to appear after the value of cell C2. There’s two possible next steps: append the contents of another cell or append text (or html code). In our example, we’re doing the latter. If you are appending anything other than the contents of a cell, you must immediately open quotes after the “&” and include the specific content in there. In this example, I inserted a line break HTML tag, followed by another “&” to indicate that I’m appending more information. (I could have combined the “<br>” and “Minimum Quantity” both times they appear and it would work fine. I used this as an opportunity to demonstrate how to append text with other text).

When you want to append the value of another cell after this text, simply put the cell number that you want to transcribe the value of as we did with the start of the example. In this case, after displaying “Minimum Quantity,” I want to show the value of the minimum quantity for this product, which I have stored in cell C2, so I simply put C2 after the “&” in the cell. (It’s important to note that unlike text, when you are referring to the contents of a cell, you do not wrap the cell number in quotes. If you do, it will literally add that cell number as the value. In this example, if I wrapped “C2” in quotes, the value “C2” would appear instead of the number “7”).

This is a very simple and easy to remember formula equation that has infinite practical uses and can be used to manipulate thousands to tens of thousands of rows of product e-commerce data.

Total
1
Shares
Previous Article
Business risk management

A Startup's Guide to Business Risk Management

Next Article
Email marketing

7 Ways to Get More From Email Marketing

Related Posts
outsourcing
Read More

4 Reasons to Consider Outsourcing Your Sales Support

Sales representatives are responsible for boosting the company’s profits, and it can be a daunting task. Because of this, sales support takes some of this burden off their backs. Having sales support may not directly improve sales, but it has a positive impact on operations. Additionally, representatives can concentrate on improving their performance. The roles...
strategic annual plan
Read More

Why The Traditional Strategic Annual Plan is Failing You

For small to mid-sized enterprises, the annual strategic planning process can be fraught with frustration. On one hand, it is acknowledged that the most accepted best practice for a business to go from their vision to achieving their stated result, for example, grow revenue by 5x over 24 months with existing resources and infrastructure, is...
milestones
Read More

How to Motivate Your Team Using Milestone Charts

When planning and tackling a project, there are a variety of ways to help your team stay on track. You can keep a list, create a Kanban board, or use other organization resources. Another popular method is to focus on reaching your project milestones. Milestones are important steps that need to be completed to finish...
budget
Read More

4 Ways Business Owners Unknowingly Drain Their Monthly Budget

As an entrepreneur, you likely have a set monthly budget that consists of all of the overhead costs needed to keep your enterprise running smoothly. For example, your budget may include payroll for employees and freelancers, payments for Internet service and cloud-based project management software, rent for an office space, etc. It is paramount that...