Saturday, 16 November 2013

What's the point of bringing data together?

So why is that we bring data together? What is the benefit in the end?
I'm asking these questions because with the advent of the digital age, and rapid development cycles, much of many companies data sources are left stranded, on their own, floating off into the ether and never to be seen again - unless you are directly concerned with that particular data source.

So what are these data sources and why should I care?
Well they could be your website and product page traffic data locked up in a third party analytics tool. It could be your app user data again locked up in a third party app solutions company. Maybe it's your media spend data? Is it your e-commerce data? Email marketing data? Facebook app data?
Whatever it is, centralising it into your data warehouse can add value and visibility across the business!


So what could I get out of centralising my data?
Firstly, it will provide a one stop shop for all your company data. This means whatever you are looking for you can find it in the Data Warehouse. Ask many an executive, business analyst, marketing manager, etc., of the times they have wanted visibility over specific activities and their cross-functional influence and the answer has been Too Hard? It's usually many, many times! It's also quite frustrating for those involved; especially when KPI's are riding hard on them.
Secondly, it gives a vast array of data for your analysts to mine and extrapolate insights from all at their fingertips. It is literally the equivalent of laying down water pipes to your house to save you from fetching your drinking water from the local well! It is of course absolutely doable to go and fetch water from the well but it is hardly the ideal situation and it will be much quicker in the long term to have everything at hand.
Thirdly, and most importantly, it's all about measurement! It's being able to know how many of your highest paying customers are using the app you created. Or whether the advertising you are conducting in certain media markets are producing a greater increase in sales versus those that are not receiving the activity.

So how does one actually bring it all together?
Well that's where you pick up the phone and bring in a Business Intelligence and Data Warehousing team in to create a data strategy and then begin the process of creating the connections between these far distant islands of data and your data warehouse.

Thursday, 7 November 2013

The treasure within

I recently watched Todd Sampson's redesign my brain series on ABC1 and I found it really cool how we all use so little of our brain and that with a little training we can increase our performance dramatically. It is fair to say that it was the opportunity and the gains to be made through some training that could result in a real impact that was most impressive to me. I guess the reason it struck a chord with me was my long held view of a similar ilk that we use only a little of the vast array of data within our companies' datamarts and warehouses and that with a little exploration and perhaps a bit of training a lot of value can be gained.

So where is it?
Largely this untapped data is contained in the transactional data that is collected every time a customer has an interaction with your company. This might be logging in online, updating information, it might be a late payment, a call into your service centre, it could be their payment timings; it could be a range of things. The key is that they are stored in transactional tables and by definition measure a behaviour of sorts.

So how can I use it?
Typically the way data modellers use the data in this sense is to link it back to the account table and rationalise the data into a flat file. This is done by aggregating the transactional data into a measure. For instance it could be the number of logins in the last 90 days, or calls into to the service centre in the last month or a yes/no flag as to whether they have made a late payment in a given time period.

From there it is about what you are looking to achieve. Is it to reduce churn? Is it about increasing sales? Is it about targeting people who are most likely to purchase? All of this data is typically held in the average data warehouse and it's about getting in touch with the right people who can give access to these tables.

I'm interested to hear about peoples experience with using data from strange sources to achieve a measurable outcome - please share if you can and any challenges faced!

Thursday, 24 October 2013

Product recommedation engine built in sql server

Hi there, yes the topic of today's discussion is a product recommendation engine built in sql server.

It seems a strange concept to most I'd imagine. Most people would only talk about such a thing in programs such as R, python or hadoop because they imagine that only these programs hold the processing power to do such things. However, the reality is that unless you hold the data of google and you are looking to build a search engine from scratch, you probably don't need hadoop. R and Python are brilliantly powerful tools and I don't discount them for a minute, however, if you come from the humble background of a sql developer/analyst, you can achieve some great results without needing to learn a whole new language even though they may be quite attainable.

Warning
So I'll warn that matrix factorization in sql requires a lot of repetitive coding. The reality is that if you also have a good grasp of excel, you can probably do all the leg work there. The key is to have your list of products that you wish to build your recommendations off as well as your customer table and your transaction table handy. With those at hand it is simply a coding issue and if you're handy with sql this shouldn't be an issue.

Use case
I've built a product recommendation engine based on 3.3 million customers and 20 products. The end result is a product recommendation on what a customer would like to purchase based on what the customer would like to purchase next. This is generated from the cohort effect (ie what other customers similar to them have purchased). It also removes the purchases they have already made so as to remain relevant.

Benefits
The benefit of the way this was developed was that it is easy to run and re-run on a as desired basis so that campaigns can be generated off the back of them. A lot of recommendations within CRM's are generated from data outside of an organisation such as census data, credit bureau data, as well as other intel collated via list brokers. This method however sources the richest form of data available to an organisation which is its own transaction data linked to its customers. Many will discount this as hard to decifer but the reality is that if you compare the r-squared values of transactional data to any other form of purchased data to the likelihood of sales, you'll find that transactional data wins out hands down.

Relevancy is key
The fact is a lot of communications coming out of organisations are promotion or product based. This means that either an offer or sale is being promoted or a new product is. So the centre of the communication is either the promotion or the product. Unfortunately, in these examples the individual consumer is not the first though, and in many examples, the individual consumer is not the thought at all.

Consumer centric marketing
Imagine a world where Google only served you the search results they wanted to show you based on what their advertisers wanted to pay for. Or imagine a world where Amazon would show you only what the highest bidder would pay to promote their latest book. This is what product centric marketing looks like and is a polar opposite to what either company does.
The reality is consumer centric marketing is key. This can be achieved in a variety of ways but I guess the most successful companies in this space have achieved this via product recommendations tailored to each and every individual customer. The reason is that this can be achieved in a fairly automated fashion.

How to make this work in your environment
The simple answer is run matrix factorisation over the products you wish to. Then summate this across the products your customers own. Next remove the products your customers own from the recommendations. Then viola, you have built your own product recommendation engine.

Get in touch for more information on how to make this work in your organisation.

Saturday, 18 May 2013

When was the last time your organisation completed a customer comms audit?

Auditing is probably one of the least favourite words in the English language and I'd bet that even auditors don't really like auditing that much. But jokes aside, there can be some interesting take home learnings from conducting a customer communications audit - may even result in some serious savings or opportunity development.

So the question is, when was the last time your organisation completed a customer comms audit? If the answer is recently then it'd be great to hear your feedback. However, the most likely answer is that it hasn't been done recently and more often than not, organisations have never completed a customer comms audit.

Why customer comms auditing is tough
The reason for this is the disparate data systems that most of their customer comms are carried out on. Yes, most organisations have a CRM system of some description where business rules are maintained and contact histories are stored but few have the feedback information from the various delivery systems streaming in. For instance, outbound telemarketing teams usually capture information such as call duration, even as simple as whether the particular customer was contacted in the first place but usually this information is out of the scope of the daily loads that are fed into the average CRM.

There is also the data that is collected by eDM campaign systems, usually maintained by a third party agency. This data includes opens and click-throughs as well as campaign performance and bounce-backs. Again, generally speaking only bounce-backs and opt-outs are sent back to the organisations for updating their customer and opt-in information. In terms of direct mail, the bulk of the feedback data is generally in the form of returned mail - generally held at the mail house side of things.

So most organisations are dealing with a variety of systems that are difficult to integrated for reason of cost or lack of benefit envisaged. On top of that there is a general reluctance usually due to the sheer volume of data that needs to be computed and analysed.

So why do it?
The reason is simple, and feeds directly into your organisations strategy, and that is firstly to see if your business rules are being adhered to and secondly to see exactly how many comms a customer is receiving from you in a given period of time. This will show up a variety of interesting information on which customers are receiving nothing, which are receiving multiple comms, and which are being bombarded. It'll also show the mix of comms that a customer receives.

Typically organisations audit their comms by doing a top-down methodology whereby they look at their comms plan from various angles such as on-boarding, up-sell, retention pieces, etc. Not many conduct the bottom-up approach whereby they look at each individual customer and what they actually receive. Sounds scary? Well it's not so scary for a data analyst, generally once the data has been all collated it is a fairly simple exercise and can provide interesting and insightful results.

Auditing can be interesting
Once you have completed the basic analysis, you can then go in and look at all the feedback data such as call durations, open and click-through rates, return-to-sender, opt-outs, as well as the most important which is customer transactions (it's always ideal to keep this in mind because it is how an organisation survives). Looking at all of these interactions and compiling all of this data in the one place, is where new models and customer segmentations can be built.

Strategy
From all this work - the models and customer segmentations - strategy can be informed. I've seen in many organisation that strategy has informed data rather than data informing strategy. Whenever I've delved deep enough into an organisations data systems, many of the answers for future testing in marketing campaigns and business strategy are already there in some shape or form. Yes, it is always good to conduct well thought of and rigorously planed tests with strong control groups but before you do that, you might have a good deal of the answer already hidden in your data-sets. This data can inform your tests to make it even more rigorous.

What are your thoughts on this - is it too complicated? has your organisation got too many hurdles to jump through to get this happening? Or have you already done it and you're reaping the benefits? Please share :)

Friday, 17 May 2013

Upcoming training and industry events calendar... LinkedIn can you help us!

LinkedIn has become such a useful tool in keeping up to date with colleagues and industry trends. It recommends who to connect with, groups that may be of interest, it even recommends jobs that we might be keen on. However, it seems to be a little short in the space of training and events. Not only LinkedIn but the entire web seems a bit scattered in this space.

Training and Events calendar
I've noticed a lot of training and industry events that I would have loved to have gone to provided I had the foreknowledge that they were on. Certainly some of the LinkedIn groups I'm a member of keep me up-to-date on some events. There's also my colleagues who let me know of things from time to time. I've also got my faves which I keep an eye on. However there's such a variety of courses out their, with different training centers, membership associations, softwares, and the list goes on to keep up-to-date with that it almost seems like a full-time job to keep track of all the different training calendars.

Where LinkedIn can help
I think with the sheer size of the professional social media platform as well as the industry incentive to advertise their training wares, it seems like a natural fit that LinkedIn would get involved in this space. Personally, as a data analyst, just the number of software seminars located here in Sydney that go by without realising that would benefit my career tremendously are quite numerous. Having a tailored training calendar to a profession and location and perhaps even based on previous courses listed on LinkedIn would be quite interesting and perhaps quite profitable for LinkedIn.

Peoples republic unite - lets get this built :)

What are your thoughts - would you find this useful to have?

Friday, 10 May 2013

Preference databases and recommendation engines

Webpages and email marketing are increasingly being used to deliver content to people that is tailored to their specific needs, wants or preferences. Most of us would have received emails from an airline company with travel destinations from origins where we are currently living. That is because typically they are basing that eDM on the last address you provided or the origin of the last booking you made with them. Perhaps you have purchased guitar strings from eBay and they have then sent you an email a short time afterwards with various guitar related products that you might also like to buy. This all forms part of a businesses marketing effort to speak to their customers in a more tailored and one-to-one way.

What is a preference database?
A preference database is usually developed out of a survey where a business will ask their customers to fill out a questionnaire to indicate which things they like or would like to hear more about. This is quite useful in tailoring future eDM's or content on the webpages they are signed in to. For instance, someone might put down that they have school-aged children and so they will then receive information driven by that such as the latest back to school offers. However, the specific products and/or content displayed will need to be decided upon by someone. In a preference database driven system, this typically involves things that are currently on sale or new content because we don't know specifically what they are interested in since it would probably make the survey we get them to fill out too large.

Recommendation engine
A recommendation engine is different to a preference database because it looks at customer behaviour over time and also looks at what other customers are doing to determine what is the best product to offer to that specific customer. For example, if one customer purchases a set of golf clubs, perhaps the next item of interest will be a bag for the clubs or a pair of golf gloves. But how do you decide which is the best one to present to your customers? What if the matter is further complicated by multiple brands of gloves and bags, so many that it is impossible to fit them in to the one eDM?
A possible solution to this is to find out what other customers have purchased after purchasing a set of golf clubs (perhaps that particular brand of golf clubs) and then use that information to present to other products that a customer might be interested in.
This is called building a recommendation engine. It is different to a preference database in that the content is chosen by the cohort (ie what other customers are doing) and is highly tailored the more a customer interacts with the business.

How can I make it more in depth?
Some businesses allow you to rate products. This has a two-fold benefit in that you can see how well people respond to or appreciate a product or content and you can also see whether that particular customer has already purchased or experienced that product or content. The reason being is that if they are rating it, they must have had some exposure to it already. Once you have this knowledge you can further optimise your tailoring and remove unnecessary and unwanted products from the communication. Other ways of further drilling down could be by demographics such as the age of the customer if you collect it and compare that to what other customers of a similar age are purchasing. Geography: what other customers in a similar area are doing? Also time of day; some companies will specifically offer content or products based on who is likely to be engaging with them at that particular time of day.

It's fairly exciting stuff and its all driven by data that your business is already collecting.

So, where is your business at with this... have you implemented a recommendation engine or preference database? What has been your experience so far. Has your engagement/sales improved as a result?

Wednesday, 8 May 2013

What product should I offer my customer to buy next?

So your customer has bought the shoes, the shirt, the trousers so perhaps the jacket or belt next? They've flown to Bali, Brisbane, the Bahamas so maybe the next destination will be Bahrain? They've gambled on a win, the scoreline, a player starting the match so perhaps they will fancy their chances on a half-time result? Maybe they've downloaded Rihanna and Justin Timberlake and they just need a bit of encouragement to download Will.I.Am!

Many companies have implemented next best offers or are looking at getting into it as part of speaking to their customers on a one to one basis. Really getting to know their customer and to be able to understand their needs and what they would like to do next. Some companies call this their recommendation engine.

How do I build a simple recommendation engine?
Firstly go into your product table and do a distinct on the product names and pick one out at random. Then go into your transaction table find all of the transactions where that product was purchased and then link that to the customer id to give a full list of customers who have purchased that particular product over a given amount of time.
The next step is to go back into your transaction table and look for what else a customer has purchased in a given amount of time and prior to purchasing the particular product that was purchased (this is not always necessary).
The next step is to do a distinct on the customer id and product names of the prior purchases. Then order this data by customer id and product name so that it is all uniformly ascending. The next step is to concatenate the rows of product names by customer id so that for each customer id the products will be listed in the column next to it such as product A, product B, product D.
So if our initial product was Product C, we can now understand which combination of product purchases happened prior to Product C purchase.

Why is that useful to know?
It means that if we produce the same steps for each and every product and then count the number of customers who have purchased the same products groups prior to purchasing another product, we can then understand what is the most probable next product they will buy based on what everyone else is doing.

Turning it into a marketing campaign
Sending out an eDM based on this data can be tricky because it involves creating multiple pods of creative that are selected to be in each customer's email based on what the data from the simple recommendation engine recommends. However, there are many agencies that specialise email segmentation and can assist with developing the creative.
The way to select which product to offer a customer next is based on what their current product mix is (ie what they have purchased recently) and the product that was purchased the most times by customers who had the same previous product mix.

It may sound fairly convoluted for the average punter but coding this up in say SQL for this simple recommendation engine is fairly straightforward. There are other ways of doing it as well for the more stats savvy of you out there and happy to discuss that in a future post.

So is this something your company does already? Are you interested in implementing a simple recommendation engine?

The analytics of customer retention campaigns in a subscription business

Subscription businesses provide access to a product or service for a fee typically collected on a monthly basis. In many business models, in order to attract new customers, they charge little or no establishment fees in order to get the product or service, sometimes at a heavy cost to the business. This leaves the company in a position where they need to retain customers for a certain period of time before they begin to make money from them. Sometimes this period can be as long as a few years.

Why do customers churn, disconnect, cancel their subscription?
Their are a variety of reasons such as inability to pay, change of financial circumstances, value for money, better offer elsewhere, change of address, no longer need it, don't fully understand the product or service, don't know how to use it properly, and the list goes on. The idea in a subscription business is that their will always be a certain level of churn which needs to be mitigated by acquiring new customers in order for the business to grow their customer base. However, customer base growth doesn't always equal revenue growth and although this is delving perhaps into commercial or financial analyst territory, the need to keep customer churn low is an increasingly important part of maintaining the profitability of a subscription businesses.

So how does one go about keeping churn low?
Firstly, good customer education on how the product or service benefits them and how they can use it effectively. Most marketing teams have a raft of communications they send out to new customers in the first days and weeks after they sign up. These are blanket comms, may be divided up into the various products or services subscribed to, but usually do not involve control groups for measuring effectiveness unless of course it is a new activity. The reason being every customer benefits and should receive the comms.
Secondly, targeting high risk groups with proactive campaigns, groups that through analysis are known to have higher churn rates than the rest of the customer base. Customers paying via direct debit and credit card are less likely to miss a payment and hence churn due to non-payment, so encouraging customers using other payment methods to pay their bills via direct debit or credit card is a churn reducing activity. Customers calling into contact centers for various reasons may have higher churn than others and so proactively calling them to resolve some or all of their issues in the one hit can help to reduce churn.
Thirdly, loyalty rewards help to reduce churn by rewarding customers who have held a subscription for a number of years or are high value customers. These campaigns can be costly and the net benefit has to be watched closely in terms of the cost of the campaign versus the churn reduction actually materialised - in cases, loyalty rewards programs can have a negative net benefit (ie they increase rather than reduce churn). In saying that though, there have been some instances where it has been highly effective so it is always good to keep an open mind but a watchful eye.

Pre-campaign analysis
In any customer database there are a variety of demographics, transaction information, interaction information and other measures captured for use in the different functions of the business. As a data analyst grows in their understanding of the data warehouse as well as how the products and/or services offered to customers work, so does their understanding and hunches grow as to what might put a customer into a higher churn risk category. Is it how old they are, is it that they have two subscriptions instead of one, is it because they have the cheapest subscription or the most expensive, is it because they have opted out of virtually every mode of customer interaction we have with them, they don't have an email, is it because they live in a particular suburb? The questions are about as many as the variables in your data warehouse. Most will either be insignificant, already thought of and mitigated, or too minor a difference to warrant any action. This is where the analyst earns their pay-check, in finding groups of customers which have much higher churn rates than the customer base so that a proactive activity has the possibility of reducing that. The reason it needs to be much higher is that all of these activities cost money to implement and maintain and the cost of the campaign needs to be smaller than the cost of losing those customers otherwise there would be no point in implementing it in the first place.

List creation
Once the pre-campaign analysis has been completed and a proven case has been presented and approved, one goes about producing the list which is similar to how the analysis is done except in this case to make it a rolling campaign rather than a one off, the timing selection will be altered in order to have a healthy number of people to contact each week/month/quarter. The usual marketing exclusions will be removed and the a sizeable control group will be held based on a randomised selection. The best way to determine how much of a control group to hold, one should determine what is the minimum effect this activity should have (ie 1% drop in churn) and multiply that out to the number selected in the campaign and then divide it by the smallest control group you wish to have - if your comfortable that the remaining number of customers is big enough to bring about a measurable difference then proceed with that. If not, increase the control group until you are satisfied, otherwise, a 50% randomised control group is the safest bet for new campaigns.

Post-campaign analysis
The effect of a churn reducing campaign may take many months to become measurable. Once sufficient time has passed (ie 3 to 6 moths), one goes about measuring the churn in the contacted versus control group. There may be other interesting factors in the works such as timing, demographics, or product and/or service mix which may have increased or decreased churn reduction benefit so delving deeper into those measures can be of value when presenting the post-campaign analysis.

It's definitely an interesting area and one that can potentially save a company millions.

So, how have you found this article? Is it similar to what you have been doing in your company?

Monday, 6 May 2013

What is a Data Analyst? What do Data Analysts do?

I've had a bit of a trawl through the internet to see if I could find a concise description of what a data analyst is and what a data analyst does and it seems the internet is a bit lacking in this space so I thought my first dabble in the blogging world could be about this field which I've spent 7 years of my career in.

So what is a data analyst?
Well they are usually someone who comes from a stats or maths background and at a very minimum have strong skills in Excel. Typically they will have either good SQL or SAS experience or both, mainly in querying, extracting and manipulating data. They will have come across a variety of business intelligence and reporting tools in their career such as Cognos or Business Objects. In addition, if they have dabbled in modeling, they will have surely come across SPSS or SPSS Modeler.

What are the favourite file types of a data analyst?
Well that's a pipe delimited text file because it has the least error rate when converting text to columns using a delimeter. However, a data analyst will be comfortable in a variety of file types; text, csv, dat, xls and so on, and importing them into a database be that Excel, Access, SQL, SAS, SPSS or some other tool.

So what does a data analyst do?
They act as an intermediary between the business and the reporting/business intelligence/data warehousing team as someone with a high degree of flexibility to create ad hoc reporting, analysis and marketing list/campaign extraction. The role is important as it enables stakeholders to get fast and accurate data out of a business's data warehouse via someone who isn't bogged down in maintaining it.
They usually deal with senior stakeholders; gathering requirements and then fishing around the databases to answer these questions. They also deal with third party data, such as purchased contact lists, to do data matching (ie current customers) and to wash out records such as those on the Do Not Call Registers. Another role they perform within marketing is to provide campaign response analysis and dashboards for the various activities undertaken which aren't so easily provided by a business' data warehouse team.
In short, they become company data experts and the go-to-person within the company for any requirements out of the norm.

What is the sign of a good data analyst?
Well in short, really good analytical skills and the ability to access the necessary data (ie they know the various languages). However, the real differentiator between a good data analyst and a really good data analyst is their stakeholder management skills. It's a process of gathering requirements, providing feedback on the viability of those requirements, educating stakeholders on the data warehouse, coming up with timelines, delivering to those, adding value to the process, the list goes on. It's these skills that really make a difference within a business because it's where the business' data really comes to life through stakeholder engagement.

That's great but how does a business benefit from this type of role?
Well businesses generally collect a lot more data than they ever use in a meaningful way. Opening up a businesses data warehouse to stakeholders via a data analyst means that you have a coming together of data and ideas. Cognos has been attributed with opening up the data warehouse to everyone in the company and it does a really good job of that. Where a data analyst steps in is bringing the understanding of a data warehouse to everyone in the company - it creates that two-way conversation and makes it possible for people to really have confidence when trying to support their ideas in an evidenced based manner.
So in short, data analysts link ideas with data and that my friend is where you find opportunity.