In my day job as an ecommerce director, we currently sell our products through 12 desktop and mobile websites (in addition to 7 Amazon channels and Walmart.com).
On our own websites, we have 93 different pages which display product grids, 81 product pages which include enhanced content (we call these PDP+ pages), 51 product pages which include accessory charts, and 6 pages which describe daily astronomical observing recommendations. In addition, we send approximately 150 email blasts per year, many of which include product grids. The HTML, CSS, and javascript for all of these web pages and email blasts are manually maintained.
We manage all of this with 3.5 people (2 full-time and 3 part-time employees). Many of our ongoing tasks involve HTML/CSS/javascript but only one of us (myself) has a fair amount of experience with HTML and CSS (and I would say my skills are only decent). The other employees in our department have gone through some basic HTML/CSS training, but do not have a great deal of experience utilizing these skills.
To allow any of us to manage our various web pages and email blasts, I have created several Excel spreadsheets which both bypass the need for HTML/CSS/javascript knowledge and streamline the overall processes we use to maintain our online channels. We call these spreadsheets “generators”.
All of the non-personal generators described below incorporate product-related data that we import from data files exported from our proprietary order management system and from our product review partner’s online portal on a daily basis.
Tab and Home Page Generators
Two of our desktop websites (telescope.com and binoculars.com) include a tabbed interface. We call the pages which are displayed when website visitors click on these tabs “Tab Pages”. Our other desktop websites – for our European customers – have more traditional navigation paradigms and use simple home pages. Each of these tab and home pages features promotional graphic assets and a grid of products which displays 20 products. The HTML and CSS related to these elements are manually maintained through the use of a “Tab Generator” and “Home Page Generators.”
To use these generators, our staff enters the image file names, alt tags, and target urls for various promotional graphic assets shown near the top of our tab and home pages. They then enter the 20 skus we want to be displayed in the product grids displayed on these pages, along with their categories (chosen from drop-down menus associated with lists of our online product categories). Once this data is entered, formulas within the spreadsheet retrieve several pieces of data from the aforementioned data files (which are imported into the generator every weekday at the press of one button): product ratings, category urls, product names, numerical online ids associated with products (largely different from their corresponding skus), image ids, image icons, price information, and brands.
In addition, conditional formatting color codes rows with skus which are no longer available for sale, are in danger of inventory depletion, or are drop-ship products – so that our staff can evaluate the wisdom of including certain products on prominent pages on our websites immediately after entering skus into the generator.
Our staff can override pricing and image icons (free shipping, sale, new, etc.) within the spreadsheet. They can also specify certain changes to the presentation of product rows, such as highlighting products as “Featured Deals” or “Doorbusters”:
or adding a customer testimonial:
or bringing attention to a product row by using a special background:
All of these choices can be controlled from within the spreadsheet.
Once our staff has finalized all the desired settings, additional formulas within the spreadsheet (some of which are more than 6,500 characters long) create the HTML and CSS required to display the product grids shown on our tab and home pages. Our staff can then simply copy the rows which produce the HTML/CSS (with the click of a button within the spreadsheet), and then paste that code into our online content management system. These generators are used to manage 12 different tab and home pages on our sites.
Site Section Page Generator
Each top-level item in the main navigation of our websites (for example, on telescope.com, these would be “Telescopes”, “Mounts & Tripods”, “Accessories”, “Astrophotography”, “Binoculars”, “Sale”, “Gift Center”, and “Shop by Brand”) leads to a page which we call a “Site Section Page.” These pages feature a list of brands we sell, various categories of products related to the top-level item, and a grid of products which displays up to 16 products. The HTML and CSS related to these elements are manually maintained through the use of a “Site Section Page Generator.”
When using this generator, our staff chooses brands and product categories to feature at the top of each site section page, which categories to feature, and up to 16 skus.
Formulas within the spreadsheet retrieve product-related information from imported data files in the same way as in the Tab and Home Page Generators. In addition, many of the features included in the Tab and Home Page Generators (the ability to override prices and image icons, color coding to signify sku status, etc.) are also available within the Site Section Page Generator.
Once our staff has finalized all the desired settings, additional formulas within the spreadsheet (some of which are more than 1,200 characters long) create the HTML and CSS required to form the brand listing, category graphics, and product grid sections of the page. Our staff can then simply copy the rows which produce the HTML/CSS (with the click of a button within the spreadsheet), and then paste that code into our online content management system. This generator is used to manage 81 different pages on our websites.
Accessory Chart Generator
Certain types of accessories on our websites come in multiple sizes and must be matched to properly fit the telescope or binocular they are used with. To make it easier for website visitors to choose the appropriate accessory, we provide accessory charts on the accessory family and product detail pages. The HTML and CSS which are used to construct these charts are manually maintained through the use of an “Accessory Chart Generator.”
To maintain our accessory charts, our staff enters product names and skus, the sku for the appropriate accessory, and details of the appropriate accessory (dimensions for example) into the generator. Once the data is entered, formulas within the spreadsheet create the contents of a javascript include file which pulls the accessory chart into the accessory family and product detail pages. Our staff then copies and pastes the content into a text editor, saves it as a javascript file, and then uploads it to our FTP site.
PDP+ Page Generator
The product detail pages for over half of our telescopes include enhanced content – additional details and images regarding included accessories and product features. We call pages which have this enhanced content “PDP+ Pages.” The HTML and CSS required to create these enhanced content sections are manually maintained through use use of a “PDP+ Page Generator.”
To create the HTML and CSS for these content sections, our staff enters a product, a set of included accessories and product features, along with an image file name, headline, and short paragraph about each included accessory and/or product feature. Once this data is entered, formulas within the spreadsheet create the HTML and CSS used to display the enhanced content section. Our staff can then copy and paste the code into our online content management system.
Email Blast Generator
Well over half of the email blasts we send include product grids. Before we started using our “Email Blast Generator,” creating the graphics and html file for an email blast could easily take a full day. Now, by using the “Email Blast Generator,” we can create the necessary files in about an hour.
To create the HTML for an email blast with a product grid, our staff enters several pieces of data into the generator: the subject line, text for the top line of the email, the file name of the HTML web version, Google Analytics tracking parameters (source and campaign), the directory where the images for the email are stored on the website, the url of the main landing page for the email, the image file name for the main banner, the main banner’s height in pixels, the email’s copy, and up to 12 skus. Once that data is entered, formulas within the spreadsheet (some of which are more than 2,500 characters long) create the HTML/content for the email blast, the web version of the email, various website promotional assets, and related social media posts. The spreadsheet also creates the text for internal emails we send during the email blast approval process.
Night Sky Tonight Generator
Six of our desktop and mobile websites include pages which provide daily recommendations for astronomical observing targets for a day’s evening or early morning skies. We call those pages “Night Sky Tonight.” The HTML and CSS for those pages is created manually through the use of a “Night Sky Tonight Generator.”
To create the HTML and CSS, once a week our staff enters a short sentence or two about what can be seen in the night or early morning sky for the next 9 days, a skill level associated with observing the astronomical object, two recommended skus for viewing the object, and a brief blurb about the next day’s recommended observing target. Once the data is entered, formulas within the spreadsheet create the HTML and CSS for the desktop and mobile pages, and our staff can copy and paste the code into our online content management system. The content that is generated is also used for daily social media posts.
In addition to the generators listed above, I have also created several tools which help our team to manage other aspects of our ecommerce business:
Product Evaluator
Our merchants use this spreadsheet to inform their decisions about which products to feature within product grids (both on web pages and in email blasts). They simply enter a list of skus (from a particular category, for example), and based on data from our order management system, the spreadsheet displays margin data by month and current inventory data for each sku. Merchants will then be aware of two data points which, while not being the only considerations, can help them make decisions about which products to include in the grids.
Inventory Check
Our staff uses this spreadsheet to quickly make sure there are no products featured in manually-maintained product grids which are no longer available for sale.
Seller Central Buy Box Calculator
Amazon provides data regarding overall buy box percent in its Detail Page Sales and Traffic report. We review this data on a weekly basis in our executive meetings. But we also wanted a slightly different view of the data – we wanted to see what percentage of skus fall into various ranges of buy box percents (90-100%, 75-89%, 50-74%, 25-49%, 0-24%). In order to bring this view to the surface, I created what I call the “Seller Central Buy Box Calculator.”
Using the spreadsheet, I can copy and paste the first 8 columns from Amazon’s Detail Page Sales and Traffic by Child Item business report into the spreadsheet. The spreadsheet then automatically calculates the percentage of skus which fall into the various ranges of buy box percents for the time period in question. The spreadsheet also creates links to the seller page of any product whose buy box percent for the period is less than a particular threshold (this allows us to quickly discover any unauthorized third-party sellers).
Daily Pace Calculator
This tool is designed to enable us to compare the pace of business within a day to the daily pace over the same hours of the day in a previous time period (the screenshot below does not reflect actual data – I made up the numbers).
For example, at 1:00 pm today, I may want to compare the pace of business in today’s first 13 hours to the pace of business in the first 13 hours of the day for the previous 30 days.
To do this, I would run a Google Analytics transaction report and medium report – both with a secondary dimension of hour of day – for today and for the previous 30 days (a total of four separate reports). I would then import the data from those Google Analytics reports into the spreadsheet (at the press of a button). The spreadsheet would then create content for an internal email to our executives and the staff of our internet marketing department which would include some explanatory content and a chart that shows the number of sessions and orders, the conversion rate, revenue and average order size for the same time period for both today and the previous 30 days.
We use these daily pace reports to warn us of potential issues on our websites or with our promotions. We generally run the reports twice a day, once in the morning and once in the afternoon.
The morning report is designed to alert us if there is something drastically wrong – there might be a checkout problem, one of our websites might be down, there might be a problem with the processing of an order feed, etc.
The afternoon report is designed to alert us if there is a problem with performance in a digital channel – traffic, conversion rate, and/or average order size may be down in a particular channel. By reviewing the afternoon report, we can begin to investigate potential issues and if the problem persists over a longer time period, we can dig deeper and attempt to implement corrective actions.
In addition to the tools I have created in my day job, I have developed a couple of personal tools in Excel as well:
Ebay Listing Generator
I use this spreadsheet to generate HTML content for the long description section of an ebay auction listing. I enter the product’s UPC code, name, manufacturer’s name, up to five bullet points, the manufacturer’s suggested retail price, the manufacturer’s part number, my part number, up to five additional features, up to five quotes from online reviews, up to five lines of included items, and a general description of the item’s condition.
From that information, formulas within the spreadsheet create HTML and CSS for the long description section. In addition, several links to online information are created which can help me write the description: the UPC code’s entry at upcindex.com, Google search results for the product name, Google search results for the product name plus the product brand, Ebay search results for the product name, Ebay search results for the product name but only sold listings, Amazon search results for the product name, and Google search results for reviews related to the product name.
Curated Blog Post Generator
The posts on this blog through which I curate articles from ecommerce and digital marketing experts are actually created through a spreadsheet. To create such a post, I enter the following information into the generator:
- the original article’s title
- the original article’s author
- the original article’s url
- the url of the author’s bio
- the url of the author’s LinkedIn profile
- the url of the author’s Twitter account
- the author’s first name
- the author’s email address
- a quote from the article
- a revised title I write (to avoid competing with the original article in search results)
- elements of my post’s introductory paragraph
- additional commentary from me
- information about the primary image I use for my post – the photographer’s user name, the url of his/her collection, the url of the image’s license
- a link title and link url for up to 7 additional links (often used for additional websites associated with the original article’s author)
- up to 10 tags
- whether or not urls entered into the spreadsheet include Google Analytics tracking
Once all of that information is entered, formulas within the spreadsheet create HTML and CSS that I can copy and paste and then enter into WordPress to create the curated blog posts. The spreadsheet also creates the text for an email I can send to the original article’s author to inform them of my curation of their article.
If you have any questions or comments about the tools I’ve described in this article, please don’t hesitate to either add a comment or contact me. I’d be more than happy to discuss the tools further.