Repurchase Rate By Item: Which Items Cause Customers To Fail To Repurchase?

Want to identify the items that are associated with customers who do not repurchase at high rates in the future?

Pull a file that has one row for every item a customer purchased over the past two years, and store that file on your hard drive. Once you have this file, run the following SPSS code (SAS code would be amazingly easy to imitate, given this code --- and for that matter, your IT staffer could use SQL to replicate this code).

Seriously, it's this easy. Give it a try! If you want to add more sophistication, split your file into new customers and existing customers. And don't mock me for the simplistic code --- what do you want for four minutes of effort?!!

get file = 'h:\kevin\itemdetail.sav'.
select if (demand gt 0).
select if (quantity gt 0).
select if (order_date lt 20080800).
select if (order_date gt 20080700).
compute rebuy = 1.
aggregate outfile = 'h:\kevin\dummy.sav'
/presorted
/break = household_id
/rebuy = max(rebuy).
get file = 'h:\kevin\itemdetail.sav'.
select if (demand gt 0).
select if (quantity gt 0).
select if (order_date lt 20070800).
select if (order_date gt 20060800).
match files file = *
/table = 'h:\kevin\dummy.sav'
/by = household_id.
if missing(rebuy) rebuy = 0.
aggregate outfile = *
/break = item_number
/cases = n
/rebuy = mean(rebuy).
select if cases ge 200.
sort cases by rebuy(d).
formats cases(f8.0) rebuy(f6.4).
list.
execute.

Great Moments In Database Marketing #5: Free Friday

If you are a database marketer, one who specializes on answering questions using SPSS/SAS/SQL, you've met up face-to-face with the concept of the "time crunch".

You begin your work day with a clear vision of what you want to accomplish. By 8:43am, your day has gone sideways. Elsie in Customer Service wants you to query the customer records of an individual who is upset that she no longer receives e-mail campaigns. The angry VP down the hall wants you to count how many customers responded to his promotion using a specific discount code last Wednesday. The earnest search marketer wants you to calculate how many customers visiting your site with a specific keyword place subsequent orders.

Before you know it, you spend your entire day writing queries for folks who do not have the skills necessary to query a database. If you go down this path, you lose your identify. You are no longer a valuable employee --- instead, you become a hybridized version of software.

Worse, you spend almost no time working on strategic issues. Everything you do is calibrated toward answering random "point in time" questions.

How do you fix this problem?

You don't!

But you can do something to mitigate the problem. I called it "Free Friday".

The idea isn't a new one. In 1996 at Eddie Bauer, I was stuck in a rut similar to the one I mentioned earlier. So I declared Friday to be "Free Friday". I would not answer any business question on Friday ... ANY Friday! Friday was "my time", time to research strategic issues and obtuse problems others didn't find important.

There was a price to pay for this freedom. I had to work forty hours a week Monday - Thursday to meet the needs of my co-workers. But then Friday was all mine.

Almost every key strategy that we employed in catalog marketing in 1998 - 2000 came from the "Free Friday" days of 1996 - 1997. Multichannel Forensics are almost entirely the result of "Free Friday". Our new store scoring algorithm and cannibalization metrics were the result of "Free Friday". Understanding online cannibalization was the result of "Free Friday". Interestingly, none of these topics were requested by co-workers or Sr. Management. They came from having the time to think clearly, to research ideas, to freely explore concepts.

Consider the concept of "Free Friday" in your work environment.

Great Moments In Database Marketing #10: The 3/2/1 Rule

During the next two weeks, we'll explore some of the unique things teams I've worked with have learned during the past twenty years about customer behavior.

#10 is the "3/2/1" rule. I once worked with a large retailer that did a spectacular job of linking website visitation data with store visit survey information and purchase data across all channels. The retailer learned that multichannel customers visit the e-commerce website three times a month, shop the store two times a month, then purchase once a month (with 85% of the purchases occurring in-store, 15% online).

How does your view of customer behavior change when you know this fact? It should cause your head to pop with possibilities!!!

First of all, you realize that your Web Analytics information is largely incomplete. Who cares if the visit-specific conversion rate is 3.04838290%? Within this project, we realized that conversion, when measured on a monthly basis (counting e-commerce and store purchases) was utterly staggering. Staggering!! More than ten times the visit-specific conversion rate.

All of a sudden, that cross-channel inventory system sounds like a good idea!

The web analytics corner of the world doesn't have enough data to tell you about the true power of your e-commerce website. You need your Business Intelligence team (and they better know SAS or SPSS, not just basic tools like Business Objects or MicroStrategy) to lead you down this path. And most important ... you need your BI team to mentor your Web Analytics team, you need them to teach the Web Analytics folks how customer behavior works across and between channels.

The true power of your e-commerce website is measured in a monthly or yearly conversion rate, combining conversions from all channels. You'll never view your website (or your analytics team) the same way, once you identify your version of the 3/2/1 rule!

Around The Horn

Random thoughts:

• The Web Analytics folks are missing a major opportunity to understand customer behavior across visits, over time. I completed a Multichannel Forensics project for a website with numerous merchandise divisions. The visual representation of customer behavior was breathtaking ... this website is literally a visual representation of our Solar System. One merchandise line was like the sun --- it represented the gravity that kept the whole customer system in place. At least two other merchandise lines were like planets, with their own moons rotating around them. A few of the merchandise lines were not part of the gravitational pull of the whole system(website). When communicated properly, an Executive team can finally understand how customers interact with a website, over time. Web analysts and clickstream vendors have a huge opportunity to grow, to change, to understand customer behavior over time. Conversely, you SAS and SPSS folks who have always analyzed catalog and retail customer behavior --- this is your brief moment in time to make a difference. Do something with your data now, before the web analytics folks figure this out.
  

• Corporate America has an opportunity to allow employees to do more work from home. We really struggled with this during my time at Nordstrom. My employees could honestly do 60% or more of their work from home. Broadband internet access changes everything. Of course, you worry about people not doing their job while working at home. A manager could measure employees on productivity, and if the employee were productive, who cares where the employee produces work? I woke up at 6:00am today, was working at 6:45am, and wrapped things up by 2:45pm. This allowed me to re-wire a pond pump and lighting system, visit a hardware store, pick up prescriptions, clean outdoor furniture, pull weeds, all before 5:00pm. I did all of this with the cell phone right next to me, just in case somebody needed something. I didn't spend two hours in a car, traveling to work at 14mph, listening to the "morning zoo" on the radio. How much more productive would our employees be if we gave them a little bit of work/life flexibility? How much happier would employees be? I'll tell you, I was one happy worker today, with 72 degree temperatures and blue skies amplifying my experience.

• Multichannel Forensics in action in the music industry: Consumers have changed their behavior in a post-iPod world --- this music industry insider believes marketers and musicians/artists must change as well. Catalog industry executives need to monitor what is happening in other industries that are being flattened by the internet. Music is one of those industries.

Fresh Data And Storytelling

Back in 1990, you'd feed a 5.25 inch floppy disk into your IBM-AT desktop computer. You'd fire up SPSS. At the bottom of the screen ticked the number of records that were being processed .... 20 records ... 40 records ... 60 records ... 80 records.

Today, I loaded a large dataset, wrote six hundred lines of code, and began processing the information. At the bottom of the screen, I could visualize the records as they were being processed ... 200,000 records ... 400,000 records ... 600,000 records ... 800,000 records. Thank you, Acer, AMD and SPSS for providing a fun computing environment.

It's a blast to see new data, information you haven't been exposed to before. The data at Nordstrom seldom changed during my six years there. Sure, occasionally the annual retention rate would vary (maybe 67.2% one year, then 69.3% the next year --- if you are at Macy's or Neiman Marcus or Saks, sorry, those aren't the actual numbers). New customers might vary by fifty thousand verses forecast. There were seldom huge surprises. Such is the case when a business consistently meets or exceeds expectations.

When you get to see new data from a new company, there is a sense of exhilaration. It is like opening up a box of puzzle pieces. You find the corners and the border pieces first. Each piece fits into another piece. Eventually, the pieces provide a path for you to get to the end of the assembly process.

Each line of code produces reporting --- the reporting tells a story. Existing customers are retained, lapsed customers repurchase, new customers feed the future growth of a business. Within minutes, the lifeblood of a company is evident on your computer monitor.

Looking across years, you can visualize the decisions that executives made, decisions that caused increases or decreases in customer counts. These increases or decreases drove subsequent decisions, which drove increases or decreases in customer counts. Within an hour, Multichannel Forensics illustrated what happened, and forecast what is likely to happen in the future. Suddenly, there is a story to tell.

There is a huge difference between Data Mining and Storytelling. Data Mining seeks to explain the data. Storytelling is an art form that translates information in a way Executives can digest, understand, and act upon.

Data Mining has a place without Storytelling. Data Mining coupled with Storytelling yields potential. Data Mining and Storytelling that speaks directly to a current, future or perceived Executive need (as defined by the Executive) causes change.