11 Jan

Golden State Flooring

gsfsiteNote: The site I built and describe here is currently dead. The company has gone into bankruptcy and I have no association with the current group calling themselves Golden State Flooring. Their site is not the site that I worked on for years. The best I can offer as far as viewing it is to go to the Internet Archive and view it there. Unfortunately, only about a third of the pages work.

If I can get a copy of the original site, I’ll post it here.

Golden State Flooring is a website that served flooring dealers and contractors throughout California. I was responsible for, well, everything on it, except, for it’s current design. I did the original designs and, I’ll admit, design is not my strong suit.

Specifically, the site provided customer credit information, pricing, promotions and other items for customers. The back-end allowed for an account to be limited in it’s access to certain product lines, or, for instance, a credit person to see a customer’s aging and purchase information but not allow a salesperson to see it. The site and the database running it also tied into the Higgins Hardwoods site. In other words, a user could log into either site and see, mostly, but not always, the same information. Both companies used the same ERP, and shared some customers, so it made sense to tie the two together in that way. Today, those sites would be built around Joomla or Drupal, and my life would have been a bit easier in that regard. But I started working on these in 2003, built the back end somewhere around that time, and, well, those two products weren’t as well done as they are today.

One question I always get asked was, did you do E-Commerce? The answer is a resounding No. And there are a couple of solid reasons for that. First, we didn’t sell directly to the public. We sold to flooring dealers and contractors or cabinet shops (Higgins). So giving the appearance that we sold online, especially in the flooring game, was problematic.

The second reason we didn’t sell online is that our data was a mess. The ERP did a very poor job of product attributes. Basically, they didn’t have any, so we didn’t have any. Flooring, which has at least a dozen potential attributes (species, width, length, warranty, color, stain, etc) had all of that information stored in a SKU description field where Red Oak might be “RO”, “Red Ok”, “Red Oak”, “Rd Oak” and so forth. As such, every SKU would have had to have been recreated with a cross-reference outside the ERP and the will to do such a thing was lacking.

We did come close, building an Overstock section, which took months, and as near as I can tell is broken, that allowed a visitor to request more information on a specific SKU.

Finally, the sites are LAMP based and supported around 2,500 registered users. That number, while small by many standards, represented significant market penetration in the flooring dealer and contractor business. I’m amazed at how well we did, most of which is owed to a very determined vice-president who pushed the sales team to sign people up.

11 Jan

My Reporting Structure At A Prior Company

For a recent job interview I put together a short 2-page summary of the things I’d done at Higgins. Mainly this is a data flow diagram showing where the data started and ended. I was involved in all of it (ok, not every single piece, I had an IT manager who liked command line programming) but for the most part I did all of the design, programming, testing and implementation for our reporting.

Reporting (PDF)

What follows is an explanation that I sent to a hiring manager.

The main business ERP was developed by a company called Activant (now Epicor) Falcon. It was a lumber based ERP (used by Higgins and Golden State) that was developed in and ran on Progress (the database and language are tied together in many ways). I don’t have the total database size but it would have been several hundred gigabytes. This is where the majority of our data was stored. The Pella side of the business was much smaller and did most of their analysis in an Access database. This was merged with our data.

Data was pulled from the Falcon ERP in multiple ways and used in multiple systems as follows:

  • Ad-hoc Reporting using Cyberquery – This was standard reporting (Excel, PDF, command line, automated emails, etc.).
  • Cyberquery hold files – These were flat-file based data extracts that were very fast. We used these extensively.
  • Cyberquery data extracts – These were extracted CSV files that were eventually imported into our intranet (Horde application framework – LAMP based) for reporting, CRM (purchasing used SugarCRM), Sharepoint (KPI’s displayed there) and web-based reporting for our on-line customers.
  • We did occasionally use an ODBC connection to access this data directly from PHP but it was awkward so in general we stuck to exporting it into MySQL.

We moved the data around, for the most part, using UNIX/Linux commands (rsync, cat, mysqlimport). Cyberquery also allowed you to create macros which ran AIX commands.

This data wound up in two main interfaces (company websites and our intranet which ran on the Horde application framework). The following are all LAMP based except for some minor reporting done using SQL Server and ASP.

  • Our company websites (www.goldenstateflooring.com and www.higginshardwoods.com) where customers could see their statements, invoice history, payment history and view product information on our blowout sku’s. Everything on those sites was designed and built by me except the appearance.
  • One-Stop Reporting (see the attachment for more information). The concept behind One-Stop was to take multiple reports from our ERP (which often had very poor indexing) and combine them into a single interface (LAMP based) improving efficiency and speed (in some cases reducing a report from hours to seconds). It was focused on four specific areas: purchasing, sales, credit and customer data. For instance, One-Stop Sales allowed you to select from 15+ parameters (inside rep, outside rep, warehouse, sku) and run 6 different reports based around some of the same factors. So it was possible to see sales by rep by warehouse, sales by rep for a given sku, sales by inside rep for a sku, etc. Data was updated once/day.
  • There was also customized sales reporting for the outside sales team, including automated emails, that allowed a salesperson to view all of his sales, customer and credit information. Management could access it to see a salesperson’s performance.
  • We also did some work with exporting data to Sharepoint and SugarCRM. The latter was used by purchasing and we exported PO data to it and the former was for some simple KPI’s for individual warehouses.

This is a bit long but it does clarify the process we were using.