2010 - The Year We Made Contact...

...with customers on their mobile devices rather than ‘just’ our web site!

If I were told to describe the work we did in 2010 in just one adjective, I would say ‘tumultuous’! 2010 was the year everything changed as we focussed our efforts to bring the Tesco online experience to mobile smart phones.

You could argue that actually we started this in Autumn 2009 when Tesco Finder launched  but, let’s face it, customers were asking - in droves - for the full grocery home shopping experience on their phones.

So that’s exactly what we did. First Nokia, then iPhone, the Windows Phone 7, and now the delicious and sumptuous iPad experience.

In terms of R&D, our delivery into this adventure is of course the Application Programming Interface (API) that abstracts all the complexity of our grocery service into a simple set of ‘commands’ that are easily used by the phone apps. It’s a mix of pride and concern that the R&D API (rather than a production version) is supporting everything. Pride that it all works a treat and needs virtually no handholding, and concern that “things” moved so fast that there was no time for one of our agile IT teams to write a production version first.

Actually this is my fault. I have - within professional boundaries - “refused” to let production teams just take the code and tune up a production version. Instead I have insisted that they design everything new from scratch. The R&D API being used by one million Tesco Groceries and Tesco Finder customers has evolved from a proof of concept through to its present pseudo-production format. Along the way I have discovered coding practices and taken on board the great new functionality of Microsoft .Net Framework 4.0. The latest commands in the API uses the latest coding techniques. The earliest commands do not; reading the API source code is to learn of Nick Lansley’s journey from basic C# coding through to XPath, Regular Expressions and Generics!

Let's take a look at this mighty year:

In January was a about my visit to CES 2010 - with revelations are still echoing through my work backlog. TVs and set-top boxes with internet connection; tablet computers; devices for the kitchen, and so forth.

February saw the launch of the Tesco Clubcard application for iPhone (and now on other phones too). Tesco.com R&D played only a consultancy role in helping the developer, Ben Martin, locate the clubcard points data. I was so impressed with the results that I decided to persuade Ben to come and work in Tesco.com R&D! More results of his handiwork are already appearing in public with the new version of Tesco Finder and there’s a lot more to come.

In March our 10-day trip to visit to Microsoft in Redmond, Google in Mountain View and Mix 2010 in Las Vegas opened up new avenues and new ways of thinking which has helped inform R&D projects - and strategy - for the future. In the meantime the work to rebuild our grocery service into a much more scalable and modular system, Project Martini, was rolled out to more and more customers.

April saw us taking steps into providing support for customers via Twitter. The insight team started monitoring tweets to uncover customer sentiment about Tesco (documented earlier in this blog). We also switched off the old proof-of-concept API and moved to a rather more elegant - and RESTful - interface suitable for mobile phone app access.

In May, my name reaching number 35 in Wired UK’s “Top 100 Britain’s top digital power brokers” was incredible, and showed that finally Tesco.com was being noticed for its innovation in the world of e-commerce.

In June Tesco Finder for iPhone, now under the guardianship of Ben, received its functional update to include a barcode scanner. Ever since the update, barcode scanning is the most popular form of product searching on that app. Throughout the summer we kicked off various projects (some behind-the-scenes) to improve various aspects of our service was very enjoyable. It’s no good hiding the fact that we can be a little selfish with some of our projects: Everything we do is about taking an extra step in service for customers - because we are customers too and we build what we would want to use!

In July Tesco held its Annual General Meeting and it was great to see Tesco apps being mentioned by the main board in their presentation to shareholders. It meant that the highest people in the company could see the importance of a Tesco presence on mobile phones.

August saw the launch of our Nokia groceries app with attendant fanfare and media interest in why we chose Nokia first (documented ‘as it happened’ earlier in this blog) with much quoting of my ‘Busy Mums” theory. That no doubt helped my CEO, Laura Wade-Gery and myself together reach the RetailInsider Top 20 major players in Multi-channel and e-commerce!

September saw the launch of our totally gorgeous Tesco Groceries for iPhone app which became an even greater joy to use after adding the barcode scanning functionality a month later.

October was the month when we launched Tesco Groceries on Windows Phone 7. The app was demo’d as part of the UK Windows Phone 7 launch! October also saw the first QR-code let loose in Tesco marketing advertising, and Deborah Meaden from Dragon's Den visited us to help launch her investment: the MyDish recipe web site linked to your Tesco grocery account via the API. Tesco Online was voted the most accessible website for visually impaired computer users by members of RNIB and listeners of Insight Radio, the RNIB's online radio station for blind and partially sighted people in the UK.

November saw the launch of our mobile-enabled Tesco Direct web site. We’ll see over time if customers prefer apps or mobile web sites now we have both!  This month also saw the publication of a couple of interviews with me in Computing and New Media Age magazines. I valued the opportunity to talk about what we do in Tesco.com because I want to let people know that it’s a great place to work because we’re all encouraged to act like entrepreneurs. That's refreshing in a company as big as Tesco and one that no doubt helped Tesco.com CEO Laura Wade-Gery get to number 3 in Retail Week's inaugural Etail Powerlist 2010 - Retail Week’s ranking of 50 most powerful people in online retail. And yes, I was mentioned on the same list as a "rising star", both humbling and fantastic!

December saw the launch of our Tesco Recipes app for iPad, a more gorgeous design you ever did see! In R&D we kicked off the Tesco Freeview Experiment which gets underway in January.

Quite a year!

Within blog entries I have already thanked so many people for the work they did in delivering some amazing innovative projects in 2010. You should re-read some articles in this blog and get to know some of these names. It would be great to see them entering a ‘Top X E-commerce people’ chart.

Happy New Year to you and all my other blog readers! Keep feeding back what you like and hate about what you read so I can continue to improve what I write here.

And here's to a happy, successful, and hey, tumultuous 2011!

R&D Christmas Experiment: Tesco Instant - Write-up and Play

OK so it's live - and achieved using no more than some C#/Java and SQL database skills! It helps me answer the question:  Can I get to a particular product faster than via our standard web offering (of departments, aisles and shelves, or text searching)? Does it for you?

Go play at http://www.techfortesco.com/tescoinstant !!

The rest of this article is about the technical design and implementation, of interest to anyone wishing to look at similar solutions.

Step 1 - Design!

I created a database with the following tables and developed code that used the API to extract the products for a particular store (Greenford Dotcom Store which serves all homes in north London) and perform 'lexicon analysis' to make the data available efficiently. 

My theory was that, if I broke all products down into their constituent words (called the 'Lexicon'), and indexed those words efficiently using all possible key combinations up to 5 characters, then I could make product searching really fast!

Pencil, paper and SQL Enterprise Manager brought me to this design (note that there are extra clustered indices applied to the final design that are not shown in this diagram):

Step 2 - Get The Data

Using the Tesco grocery API I parsed through all the departments, aisles and shelves and copied some essential product data - just ProductID, BaseProductID (known as TPNB for future use when I hook up the application to the Tesco API for 'add to basket') and the product's description name into a table called ProductsTable - totalling 21,638 rows.

Step 3 - Extract and store individual words to create the Lexicon.
I took each product description and extracted individual words thanks to C#'s String.Split(' ') method using space ' ' as a delimiter. Each word was inserted into the LexiconTable (unless it was already there, in which case I just incremented the ProductCount value at the rate of one per product - a value which would be useful later).

Since I was creating a many-to-many relationship between ProductTable and LexiconTable (that is, products have more than word and each word can be used by more than one product), I also created a lookup table - LexiconProductTable. That way I could get to list the products quickly using any particular word much because it would be indexed (the alternative would have been to search for all products containing a particular word using the 'LIKE' keyword in SQL, which is highly inefficient and slow).

LexiconTable ended up with 11,237 unique words that described all the Tesco products, and LexiconProductTable had inserted 124,413 lookup rows.

If I sort by descending product count, I can uncover the top words used in most Tesco products! Intrigued? Here is the top 20:

10	Tesco	6288
4	And	2337
14	Pack	1320
136	Chicken	873
238	75Cl	732
311	500G	714
275	Sauce	665
150	Finest	661
49	400G	660
29	In	638
199	200G	627
60	Chocolate	623
47	250Ml	568
21	Value	532
24	1	517
465	2	517
266	250G	508
37	4	498
120	Organic	486
25	Litre	475

The left column is the LexiconID unique identifier, and the right column is the count of the number of products in which the word is used.


Step 4 - Build Indexed Searching based in anticipated input
Now to get this application fast-acting when anyone typed in a search character. I needed a table that had a unique match for up to the first 5 letters typed in. That's ANY 1, 2, 3, 4, or 5 characters from 'aaaaa' to 'zzzzz' as long as there at is at least one lexicon word that satisfies the particular combination. Index this match and the system should react like lightning!

To do this I wrote some code to look at each word in the LexiconTable that had an 'a' in it and, if so, inserted an entry into a new table, LexiconSearchCharTable. I then searched 'b', 'c'' and so on. Indeed I had a 5-nested search loop that efficiently checked for up to 5-letter combinations from 'aaaaa' to 'zzzzz' like this:

Does the current Lexicon word I am studying have an 'a'?
If yes, does it have an 'aa'?
    If yes, does it have an 'aaa'?

        If yes, does it have an 'aaaa'?

            If yes, does it have an 'aaaaa'?

In other words, I iterated through the alphabet from a-z, and if I got a 'hit' I then iterated again through the alphabet for a second character 'aa-az'. Any hit I got I iterated through the alphabet using a third character 'aaa'-'azz', then a fourth 'aaaa'-'azzz' and finally a fifth character 'aaaaa-azzzz'. This worked really well without going through every combination of 'aaaaa'-'zzzzz' since of course words that have no characters 'ab' could not possibly have characters 'aba' through 'abz' either!

LexiconSearchCharTable ended up with 211,561 rows. Here is what the first 20 rows look like:

1	E	8973
2	H	8973
3	HE	8973
4	T	8973
5	TH	8973
6	THE	8973
7	B	7136
8	BE	7136
9	BER	7136
10	BERR	7136
11	BERRY	7136
12	C	7136
13	E	7136
14	ER	7136
15	ERR	7136
16	ERRY	7136
17	R	7136
18	RR	7136
19	RRY	7136
20	RY	7136

The left column is the table's unique identifier for each row, the middle row contains the successfully tested characters, and the third column is the LexiconTable unique identifier for a word that matched. You can probably deduce that Lexicon ID 8973 is "THE" and 7136 has "BERRY" and a mysterious "C" - the word actually stored is "C/BERRY" (cranberry).

A user typing in 'B' will have row 7 returned. There will be other rows with just 'B' in them but they will be returned instantly since that column is indexed. Only rows with just 'B' in this column will be returned. Type in 'BE' and now row 8 is returned instead, really fast along with the other 'BE'-only rows,

Step 5 - Extract appropriate data on demand QUICKLY via a stored procedure
So now I have my data in a good place. I just needed a stored procedure to act swiftly on the tables to bring back the list of products quickly. Here we go:

CREATE PROCEDURE [dbo].[TescoGroceryInstantSearch]
(
@SearchCharacters varchar(5)
)
as
SELECT TOP 20
pt.ProductID,
pt.Description
FROM
ProductsTable pt
INNER JOIN  LexiconProductTable lpt ON pt.ProductID = lpt.ProductID
INNER JOIN LexiconTable lt ON lt.LexiconID = lpt.LexiconID
INNER JOIN LexiconSearchCharTable lsct on lsct.LexiconID = lt.LexiconID
WHERE
SearchCharacters = @SearchCharacters
ORDER BY lt.ProductCount DESC

Nice - an easy to read and a quick way to grab the data! As you type each characters into the search box on the web page, the list of products results quickly from this stored procedure. This is confirmed by the SQL Execution Plan revealed by my SQL Server - all the heavy percentage CPU costs are borne by clustered index seeking.

I may as well make the maximum use of the data so I have a second stored procedure that will run in parallel with the first one in my web application, revealing word suggestions to help the user get to the product even quicker. Here it is:


CREATE PROCEDURE [dbo].[TescoGroceryInstantSearchSuggestions]
(
@SearchCharacters varchar(5)
)
as
SELECT TOP 15
lt.LexiconID,
lt.LexiconWord
FROM
LexiconTable lt 
INNER JOIN LexiconSearchCharTable lsct on lsct.LexiconID = lt.LexiconID
WHERE
lsct.SearchCharacters = @SearchCharacters
ORDER BY lt.ProductCount DESC

Again its clustered index seeking all the way. Nice! No need for third party software - just some intelligent thinking around data storage, formatting and extraction. I always like to try stuff out myself before I recommend buying anything, as I prefer to use the tools I already have.

Step 6 - Build a simple web application to make it all happen!

Now back to the whole reason for doing this. Can you get to a chosen product quicker?
Go play at http://www.techfortesco.com/tescoinstant and let me know.

Tesco Recipes App for iPad launches

Yes we've just launched our first (and frankly gorgeous) iPad app - Tesco Recipes.

The app was designed of course by our mobile app creators extraordinaire - Ribot, who used the 'salivation commences' slogan in their blog post as that sums up for me regarding what this app is all about.

Andy Beale, IT Manager into whom our fledgling production mobile development team reports, commented:

Special thanks to Sreelatha in India who has worked tirelessly to test the application and who is a cornerstone of Tesco's engineering team. Also thanks to Owen Day who actually led the project for IT at Tesco and helped the team to release this for our customers before Christmas. 

The application's mix of gorgeous presentation, access to over 1000 recipes and the ability to add ingredients straight to your Tesco.com grocery basket is just sensational (click images below for bigger picture):

In their blog post, Ribot wrote:

Christmas has come a day or two early this year for those that have been yearning for a source of inspiration whilst grocery shopping on their iPads… Well, we’re happy to announce that Tesco Recipes has been launched!

Bringing together the mouth-watering and inspiring photos and recipes from Tesco Real Food and the simplicity and utility of the Tesco grocery shopping experience, you can now purchase all the ingredients for your favourite recipes with ease.

This project wouldn’t have been possible without all the hard work by Rob, Jerome and Ian – the designers at ribot, Neil and Lei – the very talented ‘developers’ and of course Becky and co. from the Tesco team!

Happy shopping and Happy Christmas to all the foodies out there!

I couldn't put is better myself. Well done everyone involved!
Click here to go to iTunes to download the app for your iPad.

The R&D Christmas Project: Tesco.com Grocery Instant

That’s it! The final group of customers are having their groceries delivered before Christmas as this entry publishes - and once again they have entrusted us with delivering their Christmas Day dinner.

Yes, Tesco.com grocery customers have once again trusted us provide the ingredients for their most important meal of the year. It’s a huge honour and a huge responsibility. The weather hasn’t exactly helped but staff across the country are doing their best for customers this evening.

So finally I can draw breath and begin the process of planning the delivery of some exciting projects for the coming three months.

However, during the three working days of next week I’m not going to work. I’m going to play! I love working between Christmas and New Year because the office is mostly empty and the phone rarely rings. It’s the opportunity to start and finish a small proof of concept quickly, so let me explain it:

I love Google Instant, where search results start appearing with each key press (and it's not just the fact that Tesco has the kudos of being the first thing Google thinks of when I type 't'!). So next week I’m going to publish a proof of concept called, unless I think of a better name, Tesco.com Grocery Instant.

The objective is simple: on a web page running on our R&D server you start typing in a grocery product name. At the first key press, grocery product search results instantly appear that become more relevant with each subsequent key press. Could a user of this service find the product they are seeking more quickly than standard text search or following the department / aisle / shelf taxonomy?

To make it work I intend to follow these steps:

1. Take a copy of our product range and import it into a SQL database server table.
2. Create a second 'index' table which will have, in its first rows, a single letter of the alphabet and all the product ids from the first table that have that letter in their descriptions. I'll write a process that will carefully examine each product description and choose whether to add it to this 'index'. In total for this part I will look for characters a-z and numbers 0-9.
3. Continue the indexing, this time adding “aa” and finding all the products that match, then “ab”, “ac”, etc through to “zz” - that’s 26 x 26 letter combinations (no need for digits) = 676.
4. Continue the indexing, this time adding triple letters from “aaa” to “zzz” and finding all the products that match, That’s 26 x 26 x 26  letter combinations = 17,576.
5. Continue the indexing, this time adding four-letter combinations from “aaaa” to “zzzz” and finding all the products that match, That’s 26 x 26 x 26 x2 6 letter combinations = 456,976.

By doing this, you should always get an instant response from the database because there is always a spot-on data match for each key press as the SQL server engine searches for the correct products.

Hopefully by the time you have reached four characters then the instant product listing should have narrowed down to a reasonably small number of products. If not I’ll simply go to five-letter combinations which will add another 11,881,376 such combinations to the database.

As ever I’ll put this proof of concept onto our public internet facing labs server so you can have a go. Stay tuned to see how you can access the page.

In the meantime, may I wish you and your loved ones Yuletide Greetings and leave you with this seasonal message: On December 25th a saviour was born. He revealed eternal truth, bringing joy to millions. He astonished the world with his command of nature. He changed history forever. Yes Happy Birthday Sir Isaac Newton, born 25 December 1642.

"I get the message!" says customer sent 25,000 texts

Do you get the impression from this blog that Tesco.com R&D always gets it right?

I thought so. Well we do try and look for all the possible technical pitfalls - "Hope for the best, Plan for the worst" is a slogan I cling dearly to when unleashing an application or service on customers when it is still in R&D mode. We have some good project risk assessment meetings where each of us is asked to think of all the possible bad things an R&D application or service could do to a customer!

All our R&D projects that are tried out by customers have various counter-measures in place to protect them from "things going wrong". These include (but are not limited to) bandwidth restrictions, data limits, encryption and security measures, no privileged access to Tesco's production network or data services, and a code review to gain assurance that one of our R&D applications won't cause damage to the environment in which it runs.

The danger is that there can be Unknown Unknowns that we could not predict, and as a result haven't put in any risk-mitigating counter-measures to combat the problem, because we did not foresee the problem.

One project passing through R&D about three years ago was "Where's My Shopping?" (WMS), an SMS text-based service that sent text messages when any Tesco.com grocery delivery van left its store. Each message informed its customer of the time when their delivery was actually arriving. It's now a production service running in 60 Tesco branches and will roll out to further branches in 2011.

When WMS was in my hands, we were sending messages to customers attached to one particular Tesco branch in north London to see if they liked the messaging service and appreciated the information. To make this happen, we used to send text messages to a server in our telephone switch-room which forwarded them over a dedicated Link-60 network connection to our texting partner. Its reliability was so good that the project risk assessment did not consider every possibility of what 'going wrong' looked like. Normally a server in distress is doing less work (or no work) rather than the direction our SMS server actually took one day three years ago.

On this day, the Link-60 connection went slow. Not so slow that we couldn't send messages but slow enough that the communication between us and our SMS partner went out of synchronisation. The system normally worked by us sending a text message over the Link-60 and getting an acknowledgement (ACK) from our SMS parter that it had been received within a certain time frame. If the ACK was not received in that time then the message would be re-sent.

Only this time, the Link-60's slow response meant that the message was indeed received but the ACK was not received within the time frame, so we sent it again..... and again... to one customer 25,000 times. 

Oh yes.

(You're probably thinking, "Why not put a re-send limit on non-ACKed messages?". Alas the software that performed this work belonging to that of a third party and was a 'black box' into which we pushed text messages alongside all other text messages sent out by Tesco. I had neither detailed knowledge of its operation nor any ability to adjust it. Our R&D system only sent the message 'once'!).

The customer was very pleasant in the circumstances. Their mobile phone was a Blackberry and could store 100 message easily. It was just that, once he deleted any messages and freed up phone memory, down would come more.

After a short time this particular customer quickly got the message that their delivery was indeed going to arrive between 6:30pm and 6:45pm and just as quickly decided that he didn't need to be told again and again. He gave our customer service centre a call.

The message passed to me was, "a customer has called in to say they are getting endless text messages about their delivery!". I'll never forget receiving that message. I slammed the phone down, jumped up from my desk, tore across Tesco's Shire Park campus from one building to another looking like someone possessed, entered the switch-room and unceremoniously yanked the network and power cables from the back of the Link-60 SMS server.

The instant and sterling effort by our SMS partner and the customer's mobile phone company managed to remove the queue of messages (with the customer's readily given permission). My coveted R&D budget was reduced by a suitable amount in order to compensate the customer for the misadventure they had suffered at my hands.

These days - as a direct result of our experience - we use a nice and reliable secure web service interface to our partner's SMS service over the internet. It works a treat and the considerable counter-measures now in place at both ends prevent a customer from ever receiving more than three SMS messages from the WMS service in any one day.

Unknown Unknowns stalk R&D projects in every company, every day. Only skill, knowledge and the wisdom gained by experience allows us to grow a list of "Unknowns that become Known" whenever we have a project risk assessment meeting.

It's a list that we treasure. 

Christmas is coming / Tesco Finder update

I knew there was a reason for the workload - I have been radio silent on this blog for the last two weeks because you you could say that work reached a crescendo as we put in the final preparations for Christmas trading.

It was not just that customers seemed to be waiting for us to release the coveted 'festive grocery delivery slots' adjacent to Christmas Eve. Nor was it just the fact that people were waiting, it seemed, to the last minute to get the Clubcard deals at the original 4:1 ratio before the date it switched to 3:1. Oh and the 'Double Up' Clubcard deals that proved so fantastically popular. But it all happened at the same time and our servers have been feeling the heat like never before.

In addition, our API received updates to improve performance and add new functionally, and Tesco Finder for iPhone (version 2.2) has just been uploaded to the Apple iTunes Store for approval.

I sent out Technika Freeview set-top boxes to triallists around the country engaged in the forthcoming Tesco Freeview Experiment, and they are getting their boxes set up ready so we can start soon.

Then I went on holiday to Ireland for a few days to offer my in-laws Yuletide greetings and drink Guinness (it just tastes best when sipped in a classic Irish pub with great company sat round a roaring fire).

But now we're settling down into a mix of research and support mode rather than try and add anything new as we guide our way through the next fortnight to the celebrations of the Yuletide season.

-

I'll talk more about the great new features of Tesco Finder in the next few days but in summary the new version includes:

• A complete re-design and re-program from the ground-up by Ben Martin (who also authored the Clubcard app for iPhone).
• A branch list that that stays up to date and now includes all Tesco stores in the Republic of Ireland.
• Filtering of nearby stores to only show those with certain facilities (such as petrol station, ATM, cultural food, those open 24 hours, and many more filter options).
• The ability to refresh your product list(s) to get the latest prices / availability / special offers and in-store product locations - especially useful when you change to a different Tesco branch. 
• A great new classy Tesco Finder icon showing a map of the British Isles - nice!