SAS Reporting Helped Us Make a Killing

This Christmas season was intense. Had some jarring personnel issues alongside the highest sales I've seen by far. We sold $50K during the Black Friday - Cyber Monday weekend, which is high when our average sales are around 2k per day. Shipped over 1,000 orders on some days.

The programs I built summarized remaining quantity, sold quantity, average sold per day, and the amount of time until sellout. Informed by projections from the previous year, I was able to raise and lower prices based on projected sell-out. Ours is a business of volume -- our fixed price is relatively small compared to operating costs -- so my goal is to price to sell-out given a certain quantity and time frame.

I priced several lower-quantity items quite above the average price, and with few exceptions they all sold-out by Christmas. Rather than bulk pricing, using SAS helped me fine-tune prices in a timely manner. Usually, sales optimization suffers when order fulfillment overwhelms; this year I did both.

Exposing Under-Performing Products

With 1500+ products it is very easy to list an item and forget about it, especially if the item never sells. This program is designed to bring these products to my attention.

This program looks at items listed in the last 30 days and tells me the following: total purchases, days listed, and percent of total quantity sold. I sorted so the oldest items with no sales come up first.

What can I do with this information? The best thing I can do is adjust their titles using a rough approximation of eBay's Best Match algorithm. (A guide to this poor man's SEO on ebay can be found here).

When that doesn't work, I cut the price. Identifying and liquidating under-performing product is just as important as making money off the better items.

Program logic

In order to analyze clothing sales I have to compensate for varying item titles. A normal item title looks like this:

ADIDAS CLIMALITE COACHES POLO NEW WHITE GOLF XL

I used the substr() function to select only the first 40 characters of my title. While it isn't pretty, I get the information I need to identify the slackers (and the go-getters, which may or may not be underpriced). Nesting TRANWRD and SCAN functions identified and removed size, but also removed the size letter elsewhere (So a large (L) OAKLAND RAIDERS item came out as OAK AND RAIDERS, and padding with a blank only works when the size letter doesn't start a word).

Once the different sizes of an item have the same title, I use first/last processing to sum their sales and quantities.

Mean data

Unfriendly, really.

(Yes, the picture is tiny and the format for this blog is poor for code. Working on that...)

The data above shows us another data irregularity. Each order, which can have multiple items, has the same sales ID number. But the customer info only appears in the first line. In order to perform detailed analysis such as "Which state purchases the most 49ers items?" I need every item to be associated with all buyer info.

Here is what I came up with. I use an if statement to select only those values with the same order number, as not all missing data represents a combined order. This also saves processing time vs checking all orders.

data ebaysale.june09e;
set ebaysale.june09e;
by Sales_Record_Number;
if last.Sales_record_number=0 or first.sales_record_number=0 then do;
array Ovar{3} user_id buyer_state Buyer_Country;
array Ovarn{2} shipping_and_handling total_price;
array Rvar{3} $ 55;
array Rvarn{2};
retain Rvar1-Rvar3 Rvarn1-Rvarn2 paid_on_dateR;
do i = 1 to 3;
if missing(Ovar{i}) then Ovar{i}=Rvar{i};
else Rvar{i}=Ovar{i};
end;
do i = 1 to 2;
if missing(Ovarn{i}) then Ovarn{i}=Rvarn{i};
else Rvarn{i}=Ovarn{i};
end;
if paid_on_date=. then paid_on_date=paid_on_dateR;
else paid_on_dateR=paid_on_date;
end;
run;

Head scratcher: I could NOT get date to work in the character or numeric array. If I put it in the numerical array, I got an error, and if I used the character array it did nothing. Also, missing values for date are '.', but the properties defines it as a text value. I suspect dates behave differently than normal character / numerical variables, but I haven't learned yet in what way.

Pleased with my progress thus far. I'm nearly to the point of having a program that will make a new dataset download from ebay immediately analysis friendly.

eBay Date Values

eBay has always been a pain in and on the back end (the word bratty comes to mind) so why should the sales data be any different? To wit, a date value::

Mar-03-10

Excel just looks at me like I’m an idiot. And it sorts alphabetically(!).

I cracked this one on SAS, but not without false starts. CATX(SUBSTR... gave results I still do not know how to explain: I used 3 SUBSTR statements in the correct order, so the output should be 03MAR10, but it kept coming out as MAR0310. Does CATX arrange statements automatically?

Sunday-frustration-lengthed-story later, I ended up with this:

sale_date=input((substr(sale_date,5,2)||substr(sale_date,1,3)||substr(sale_date,8)),date7.);

I first included ||20|| before the 2-digit-year, to use date9., but it added a bunch of spaces after the 20? Trim didn’t help. Putting the 20 in quotes did help. Then I realized I could just use date7.

There was much cheering in my apartment.

About

Hi.

This blog is a report of my progress establishing an analytics practice at my work. I manage a team that runs the online sales department of a wholesale liquidator.

I will be focusing on eBay data.

Moreso, I am learning SAS. So if you’re a SASpert this will all be quite boring for you I’m sure.


Who is this blog for?


Anyone in the tip-of-the-tail with me who is at once into SAS and eBay. For whom would such seller-side analytics be useful, especially when we’re talking about establishing a practice by scratch? Well, I’m not sure if this is a scalable project, but given that its free education, it’s actually a steal of a deal.

(Yes, there’s a real possibility of being good enough to sell eBay analytic services to the big guys. But something about joining the scads of 3rd party eBay mollusks is unsettling, especially as Amazon continues to take over.)