Stop Trying to Delight Your Customers and Simply Make it Easy for Them

This month's Harvard Business Review (HBR) article Stop Trying to Delight Your Customers argues that companies should focus on meeting their customers' needs rather than trying to provide over-the-top service to increase tenure and customer loyalty.  The latter doesn't translate into increased loyalty and efforts toward it are usually at the expense of the former.

Customers notoriously punish bad service, particularly when left on hold, transferred while on a service call, or when they receive egregious customer service.  We've all been there and we've all told two friends and two friends and so on.  Customers share negative experiences at twice the rate of positive ones, according to a customer service and loyalty study from the Customer Contact Council. What's more, reducing customers' efforts builds loyalty, the vague notion of delighting them does not.  Customers want their problem solved, not some whiz-bang, over the top widget.  And finally, improved customer service, particularly in the call-to-contact realm does more to reduce churn than the "delight them" efforts.  Maybe, the "delight them" focus should be on the customers' experience rather than delight in the company's product.

What are your thoughts on customer service and the customer experience?  Do you want to be delighted or are you often disappointed because your core services needs are unmet?

Calculating Tenure when your Cancel Date is in the future

A configuration on our EComm system requires that every subscriber have a NOT NULL Cancel Date.  This presented some difficulty for calculating tenure, particularly since the default for paying subscribers is 2999-01-01.  Wow!  That's some great tenure.  They've been paying subscribers longer than we've been in business.

For the past few weeks I've been grappling with how to adjust the CancelDt and calculate tenure.  Since I'm using Tableau on top of Teradata, I've dug into both and decided on the below.

These calculations are included in a monthly churn workbook I produce for our product team.  The workbook is updated at the beginning of each month and analyzes churn and media adoption for the previous month, it's preceding three month average, and the same periods in the previous year.  The dates are configured to update dynamically, so the current_date assignment is valid for the entire month the workbook is valid.  Once the workbook updates for the following month, you'll be ahead and the current_date assignment will remain in the month ahead of the study month.

Previous method for adjusting the CancelDt and calculating churn:

In Teradata create a view or use custom query for Tableau

select RptMonth
, Id
, SignupDt - (EXTRACT(DAY from SignupDt) -1) as SignupMo
, CancelDt - (EXTRACT(DAY from CancelDt) -1) as CancelMo
from foo

Then in Tableau, create a custom metrics to resolve the future date
"CancelMo Truncated"
IIF([CancelMo] > TODAY(), DATETRUNC('month',TODAY()),[CancelMo])

and  to calculate "Tenure"

DATEDIFF('month',[SignupMo],[CancelMo Truncated])


Updated Method
Push it all back to the database:


select RptMonth
, Id
, SignupDt 
, CASE WHEN CancelDt = '2999-01-01' THEN current_date ELSE CancelDt END CancelDtUpd
, (CancelDtUpd - SignupDt) month(4) as Tenure
, CASE WHEN CancelDt = '2999-01-01' THEN 1 ELSE 0 END PayingFlag
from foo

Date Formats in Tableau

ddd - Short Day of Week (ex., Mon)
mmm - 3 Letter Month (ex., Jan)
MMMM - Full Month (ex., January)
d - short digit of month (7, not 07)
DDDD - Weekday (ex., Monday)
DD - Day (01, not 1)
yyyy - 4 digit year
yy - 2 digit year
q - quarter (Q)
w - week (W)

Teradata - Secondary Index

Two challenges for this week:

1. How to create a volatile table with a secondary index
2. How to add an index to an existing table.

While there is no secondary index explicit in TD for VOLATILE tables, I found that I could simply add indices after the PRIMARY INDEX and voila:

CREATE VOLATILE TABLE FOO
(
Id INT
, Date DATE
, Product VARCHAR(20)
)
PRIMARY INDEX (Id)
INDEX (Product)
ON COMMIT PRESERVE ROWS; -- to ensure when you populate the table you can query the data.

Adding a Secondary Index (unique or non-unique) to an existing table:
create index index_name (Field) on database.table;

Extracting the Day of Week to roll up cohorts into Discrete Weeks

Dates seem to be a common theme for me.  I'm sure I'm not alone. Today I'm working on a project where I need to group all people into a calendar week for their event. Thus, I want to take their Event Date, extract AND subtract the DayOfWeek from the Event Date to group everyone by the week's first day.

Also, we calculate our weeks as Monday - Sunday, so you'll see a +2 on the day_of_week field.

SELECT 
StartDt
, EXTRACT(DAY from StartDt) as DayOfMonth
, day_of_week + 2 as DayOfWeek
, StartDt - (day_of_week + 2) as FirstDayOfWeek
from mydb.table
INNER JOIN sys_calendar.calendar
ON StartDt = calendar_date;

This give me:

SubStartDt DayOfMonth DayOfWeek FirstDayOfWeek
 09/25/2009         25         8 09/21/2009
 10/12/2009         12         4 10/12/2009
 09/28/2009         28         4 09/28/2009
 10/17/2009         17         9 10/12/2009
 09/29/2009         29         5 09/28/2009
 10/15/2009         15         7 10/12/2009
 10/07/2009          7         6 10/05/2009
 10/16/2009         16         8 10/12/2009
 10/11/2009         11         3 10/12/2009
 10/04/2009          4         3 10/05/2009

Now back to my weekly assessment.

Renaming a Column in Teradata

Sometimes it's the little stuff where syntax differences between DMBSs get me.  Today I had to look up how to rename a column.  Easy enough stuff:

ALTER TABLE mydb.foo RENAME OldColumnName TO NewColumnName;

Teradata Date Conversion - Getting the First day of the Month

I have a table containing start dates and I want to identify all the records for a given month by their month. This will allow me to identify sets of users by their signup month.

Thanks to a tip from Craig F. I can do this by subtracting from the date the day of the month minus 1. This will ensure that regardless of the date I choose, all my dates are returned as the first of the month.

I created a table (foo) with one column (startdt type DATE) and three records:

select * from foo

== Results ==
startdt
12/31/2009  12:00:00 AM
8/1/2009    12:00:00 AM
7/15/2009  12:00:00 AM

Now I want to get the number of days to subtract from the day in the date. To do this, I use Teradata’s EXTRACT function and get the day of the date:

EXTRACT(Day from StartDt) as DayOfDate 

and I get the number day value : 31, 1, 15

EXTRACT(Month from StartDt) returns the month values 12, 8, 7

EXTRACT(Year from StartDt) returns 2009

Now that I have the numeric day of the month I simply subtract one from it:

SELECT startdt
, startdt - INTERVAL '1' DAY as Start_Minus_1_Day
, EXTRACT(Day from StartDt) -1 as DaySubAmount
, startdt - (EXTRACT(DAY from StartDt) -1) as FirstofMonth
FROM database.foo

You'll notice another function you can do with dates is add or subtract an INTERVAL. There is a lot you can do with INTERVAL so I'll limit my comments here to how I used it.

Above I subtracted 1 day from the date using - INTERVAL '1' DAY. Day could be replaced with Month, Year, Hour, Minute, Second and transformations (like Day to Second). You can also Add an INTERVAL to a date using the same syntax.

My final result set was:

startddt Start_Minus_1_Day DaySubAmount FirstofMonth
8/1/2009 0:00 7/31/2009 0:00  0  8/1/2009 0:00
7/15/2009 0:00 7/14/2009 0:00  14  7/1/2009 0:00
12/31/2009 0:00 12/30/2009 0:00  30  12/1/2009 0:00