Thursday, September 30, 2010

If you're going to invest in the future you have to ask yourself, what's in the future? Lithium and coffee are good bets.

In my quest to better understand who Kiva loans to I looked into who gets the most money.  Surprisingly I found it was Bolivia and Uganda.  My first thoughts were, Why?  I know almost nothing about Bolivia and even less about Uganda.  I know which continent each are on, which is probably a leap over most Americans.  Cynicism aside, why are they getting lots more money than other countries?  What's going on in these countries that make them so appealing?  Why am I asking this?  Take a look (I apologize for the poorly rendered graphic but I cannot yet post a Tableau 6 public viz since it's still in beta and hence a bit skewed):

With my limited information, I could surmise that Kiva gives loans to countries that have lithium (Bolivia), for the lithium ion batteries in your laptops and hybrid cars, and coffee (Uganda) because high-end, delicious coffee is market valuable. But there must be more, right?

For Bolivia there is.  They have a 0.00% delinquency and 0.00% default rate.  Stellar!  And their economy is growing respectably.  In many ways, they've benefitted from the post-Cold War period: even after the West pulled their support in the late 1980s, Bolivia had the second largest reserve of natural gas in South America and now with the growing importance of lithium they have quite a leg to lean on.

But what about Uganda?  Uganda is in the Top 5 for delinquency.  So why does Kiva loan to them?  It could be their vast coffee exports, 56% of GDP.  Is that enough?  Clearly this warrants more study.

And with all this, what's up with the Dominican Republic? They're in the Top 10  of direct loans from Kiva, but with a considerable default rate.  What are they doing that affords them additional funding even with a high default rate?

More importantly, in emerging economies how can we replicate what Bolivia and the others in the Top Tier countries are doing especially with their miniscule default rate?

Stay tuned.

Monday, September 13, 2010

Where exactly are your Kiva micro loans going?

I love  I love the idea of Kiva (micro lending) and the practice is so easy it makes me smile (and make regular loans).   In lieu of a Peet's cappuccino each day I can help a Peruvian woman buy a motorcycle to transport her family to the doctor, or to buy supplies to make clothes or to sell cosmetics, or to stock her grocery store.  I'm not sure why Peruvian women are such a lure for me, but they are.  And Nicaraguan women.  It's women.  I find myself wanting to help women in developing nations improve their lives.  Women in Central and South America.  Women in Africa.  Women the wold over.

These are the entrepreneurs that I sponsor: the Peruvian and the Nicaraguan women.  These are the women that I envision when making my loans.

I made my first Kiva loan in February 2009.  I think I heard about Kiva on NPR and was instantly hooked.  I loved the idea of direct assistance and assistance to women in particular.  Since then, I've made 10 loans, 3 to Nigerian women, 2 to Peruvian women, and 1 each to women in Nicaragua, Cambodia, Mexico, the Dominican Republic, and the Philippines.  For some reason, I thought they were mostly Peruvian women but alas the data tells a different story.

Tonight I was wondering where Kiva sends its money and whether some countries are better than others at repaying their loans.  And, with the miraculous wizardry of Tableau I was able to quickly see that there is a correlation between place and delinquency, place and loans disbursed.  Mind you, I'm talking correlation and not causality.  I don't have enough data to say why Kenya is the greatest defaulter of loans with the US trailing as a close second, or why Tanzania, Nicaragua and Ghana have delinquency rates exceeding 50%, which are considerably greater than any other country with active loans, but I can quickly see these details and that tells me there's something to investigate.

So what did I learn?  Kiva makes lots of loans to Cambodia and Peru.  And those borrowers, they're good for it.  In fact, they're better than those in the US.  Uganda gets less money and is a bigger risk.    The countries who were the biggest risks got the least money.  So, Kiva is spending my money well.  I think.  But what can we do to help those with high delinquency or default rates?  It must be more than your country of origin that shapes your credit worthiness.  Or so I would hope.  What do you think?

Check out my data set and visualizations at  

Tuesday, August 3, 2010

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?

Tuesday, July 27, 2010

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

Wednesday, May 12, 2010

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)