The cohort analysis blog post series

• Cohort analyses for digital businesses: an overview
• Performing cohort analysis on web analytics data using SnowPlow
• Performing the cohort analysis described by Eric Ries in the Lean Startup
• On the wide variety of cohort analyses
• Approaches to measuring user engagement as part of cohort analysis
• Approaches to measuring customer value as part of cohort analysis
• Faking cohort analysis with Google Analytics

In the previous posts in this series on cohort analyses, we looked at what cohort analysis is, explored the wide variety of cohort analyses that are possible and walked through the steps necessary to perform them using SnowPlow. In this post, we look at how to perform cohort analyses in Google Analytics. As will quickly become apparent, Google Analytics is not well suited to performing cohort analyses. Hence, although this blog post will be useful to people who have to use Google Analytics to perform their cohort analyses, it should be more helpful to analysts identifying the advantages are of using SnowPlow alongside (or instead of) Google Analytics, and how those advantages derive from the fundamentally different approach SnowPlow takes to web analytics.

The wrong tool for the job

When performing a cohort analysis, we need our web analytics to enable us to:

1. Report the metric we want to compare between the cohorts
2. Report the metric value for each of our cohorts
3. Compare the metric value for each of our cohorts alongside each other

1. Reporting the metric we want to compare between cohorts

Google Analytics provides analysts with a decent set of metrics to report on, including all the usual suspects:

• Visits
• Unique visitors
• Page views
• Pages per visit
• Bounce rate
• Conversion rates

That’s not a bad set of metrics, but it’s not nearly as wide a set as that provided by SnowPlow. As we’ve shown in previous posts, SnowPlow offers analysts a lot of flexibility to define metrics to measure important things like user engagement and customer lifetime value. Both of these are very hard to approximate using the more limited set of metrics Google Analytics provides. (For example, the best we can do on “engagement” is to look at the number of pages per visit, or the fraction of users that reach a particular point in a pre-defined funnel. We certainly cannot develop our own models of engagement that assign different values for different user actions, and sum them to get a score for each of our users, as we can with SnowPlow.

2. Enable us to define our cohorts, and report the metric above for each cohort

There is a wide range of different cohorts we might want to define in different circumstances:

• Which marketing channel a customer was acquired on (useful, for example, if we want to compare the return on marketing investment between different channels, with a view to optimising ad spend going forwards)
• Which month a person signed up to a service (useful if we want to measure improvements in how effectively we are converting customers over time, as in the lean startup example.)
• Customer profile data e.g. gender, age (useful if we believe that customer behavior varies as a function of demography, and we want to examine how successfully we are serving different customer segments)

Google Analytics provides analysts with the ability to report on a range of subsets of the total userbase including:

• Where the user is based (geography)
• Whether this is a new visitor, or someone who has visited the site before
• What type of browser the user is running
• What traffic source drove the user to the site on this occasion

Google recently upgraded Analytics to provide users with the ability to define “advanced segments” of visits that meet multiple criteria, giving analysts improved flexibility to define their own cohorts:

That functionality is great, but it has a one critical limitation from an analysis perspective: we can only segment users based on variables related to this particular visit (i.e. this session). We cannot segment users based on their past behaviour – i.e. what happened to them on previous visits. To take two examples:

1. We cannot segment users based on when they started using our service, because that requires looking up the date of their first visit, which relates to a previous user session.
2. We cannot segment users based on whether they have seen a particular ad on their customer journey, because Google will only enable us to query which ads they have seen on this particular visit. So if a customer was originally acquired through a particular PPC campaign, but visited the site most recently by coming to it directly, we cannot use that when defining our cohort.

The above problems both stem from the same design feature in Google Analytics: namely that “visits” are the primary units of analysis, with Google only providing us with limited tools to analyse an individual’s behaviour across multiple customer visits. That is a big drawback when you’re doing cohort analysis, because however a cohort is defined, the customer is always the primary unit of analysis. You may define your customer segment based on the customer’s behaviour on a particular visit, but limiting yourself to the most recent visit make the vast majority of cohort analyses very tricky with Google Analytics.

There is a way to work around the above limitation, however. If we have a particular variable that we want to use in a cohort analysis, we need to assign it to the user in our own web platform (e.g. CMS or ecommerce package), and then pass that information to Google Analytics every time the user visits our site, by setting a custom variable in the JavaScript. This, like it sounds, is not trivial. It is the basis for the solutions to performing cohort analysis using Google Analytics proposed by Dan Hill and Matt Clarke.

3. Comparing the metric value for each of our cohorts alongside each other

In an ideal world, we would want Google Analytics to present us with a plot of our metric against different cohorts:

Unfortunately, Google wont give us our data in quite that format. Performing the cohort analysis then, is a multi-step process:

1. Go to the advanced segment interface, and either select a segment that corresponds to your first cohort (if the segment already exists), or create a new custom segment if not.
2. Once you have selected the appropriate segment, Google will only report results for this particular cohort. Now navigate to the metric you want to measure for this cohort, using the options in the left hand menu. Chances are that you will want to look at Overview for the standard metrics (visits, page views etc.), Behaviour -> engagement to measure visit duration or Conversions to measure propensity of each cohort to convert.
3. Now that you have your value for the particular cohort, record that. (Probably most easily done by downloading the relevant report in CSV format.)
4. Repeat the above steps for each different cohort.
5. Collate the results in Microsoft Excel (or equivalent).

Google Analytics: not the best tool for cohort analysis

As we’ve seen above, it is possible to perform cohort analyses with Google Analytics. However, it is not easy. In particular, two limitations stand out:

1. It is not possible to define cohorts in Google Analytics based on any user data, only data that is associated with the user’s most recent visit
2. Results from different cohorts have to be manually collated: Google will not present them alongside each other (although this pain point can be made automated using Google’s API, once the analyst has defined the segments in the web UI)

In contrast, SnowPlow gives analysts the ability to create segments based on any user data, and returns the complete result set (for all relevant cohorts) in a single table.

Interested in finding out more about how SnowPlow can empower analysis at your company? Then find out more from the SnowPlow website, SnowPlow wiki, or get in touch.

The introducing SnowPlow blog post series

• Introducing SnowPlow: the world’s most powerful web analytics platform
• SnowPlow update: first part of source code published
• Warehousing your online ad data with SnowPlow
• Reintroducing SnowPlow: what is wrong with web analytics in 2012 and how we fix it. (Presentation version)
• What is wrong with web analytics in 2012, and how SnowPlow starts to fix it. (Blog post version)

We developed SnowPlow out of frustration with the limitations of web analytics solutions today. We believe that there are many things that are wrong with today’s web analytics packages. In the last few weeks, demoing SnowPlow to different companies and discussing where web analytics falls short for them, we’ve seen that we’re not alone in believing that the web analytics world is in need of a shake-up. However, that view is by no means universal, so we thought we’d take the debate online, and explain our position here.

Web analytics solutions no longer support all the applications for web analytics data

What’s wrong with web analytics in 2012?

Too narrowly focused

Web analytics tools are narrowly focused on:

1. Marketing-related metrics (sources of traffic, visit numbers over time)
2. Ecommerce and online retail sites (clickthroughs, conversions, tracking a flow through a pre-defined funnel)
3. Conventional online publishing sites (page views by web page)

Web analytics tools have not been developed with the wide range of digital businesses that are active today online, including an enormous number of B2B SaaS services, online games companies (including social and massively multiplayer games), financial services companies, media broadcasters and social networks, to name just a few categories. These companies are incredibly poorly served by today’s web analytics solutions.

It is not just whole industries which are poorly served by today’s web analytics options: the current focus of web analytics packages on marketing activities means that even in the target industries (i.e. online retailers and conventional online publishers), it is hard for anyone outside of Marketing to use the tools – for example:

1. Product managers find it hard to effectively use web analytics tools to understand how users engage with their website, how new versions of the website improve engagement and how that improved engagement drives improved commercial outcomes. Worse, it is all but impossible to use current tools to spot where the product is currently not working (e.g. where users are getting “stuck”, and so where product development efforts should focus)
2. Developers find current web analytics packages far too crude to perform the type of event-level inspection and monitoring to understand how their applications are standing up to real-world use
3. Customer relationship managers typically do not use customer web analytics data to improve their knowledge of individual customers and develop customer segmentations

Inflexible

One of the most things that surprises us most is that web analytics packages are as a whole, highly inflexible. It is not easy to use them as a basis to build custom reports – reports that are not simply “recuts” on a subset of page views, visits and unique visitors. For example, try building a report to compare the lifetime value of users acquired from one marketing channel with another, or looking at how user engagement varies by when the user signed up to a service.

This is surprising because on the whole, companies are getting much better at building tools that developers and data scientists can take and extend to suit their own needs. Compare the growth in the number of libraries available for analysts using R or Mahout, as examples of analytics products that have been developed by a wide and disprate community of analysts. What kind of comparable developments have we seen in web analytics? Google’s Analytics API has been available for ages, but all it has been used for is to export cuts of data and come up with sexy visualisations of dubious analytic value. That is not the fault of the community around the product: Google’s Analytics API has simply not been built to enable the kind of analytic innovation we’re looking for, because data is only exposed in an aggregated form, limiting the scope of data scientists and other programmers to reimagine it used in very different types of analysis.

Too high-level

As mentioned above, web analytics packages typically only provide aggregated data. They do not make data available about individual users, making it hard to use web analytics data to diagnose what is going wrong or right with a particular customer journey, let alone to use that data to improve and personalise that journey in the future.

Too low-level

It should not be possible for web analytics tools to be both too high-level and too low-level, but today’s vendors have managed just that. Opening up a web analytics package, analysts are presented with a sea of metrics: hundreds of carefully collated numbers, very few of which actually matter for business success or decision making. By drowning us in “vanity metrics”, the current crop of web analytics products make it hard for us to see the wood for the trees.

Siloed

It is several decades since Ralph Kimball argued that companies should be drawing data from multiple sources together in a single repository so that analysts could use that data to derive insights. Those compelling arguments for warehousing data have been accepted by the whole analytics community. Sadly web analytics tools have not been built to facilitate this. Web analytics data still typically sits outside of company data warehouses and BI systems in its own impregnable silo. Because of this, web analytics data is rarely incorporated as part of a single customer view – which is shocking given how much modern companies engage with their customers via the web.

What are the consequences for businesses?

The consequences for business are simple to articulate: business questions that should be possible to answer using web analytics data are not. We divide those questions into two categories, customer analytics and product analytics:

Customer analytics

How effectively a company understands their customers, and uses that understanding to drive customer loyalty and value, is a key determinant of success in a wide range of industries. For just one example, look at how Tesco’s use of Clubcard data via Dunnhumby helped propel it from the second largest supermarket in the UK to a global behemoth not far behind Walmart. Web analytics should provide a wealth of detailed customer data based on the way those customers engage with our online products and services. Sadly, web analytics’ relentless focus on visits and page views has totally obscured the customer. The following customer-level questions are all very hard to answer using web analytics data, yet these are basic questions that every consumer-facing business should be asking and answering:

1. Who are our most valuable customers?
2. How can I spot those valuable customers in advance? (What are the key predictors of value?)
3. What are the “sliding doors” moments that move a customer from a less valuable to a more valuable segment, or vice-versa?
4. How should we break down our customer-base by behaviour? How do segments vary, by value?
5. How well do we serve each customer segment? Are some (potentially valuable) segments less well served than others? What parts of the product should we focus on developing to improve the service level for those segments?
6. What are the best opportunities for growing the value of my customer base?

Any company with an online presence should also be interested in using web analytics data to measure help make robust product development decisions. With the current crop of tools, however, this is very difficult: current tools enable A/B and multivariate testing but little else. In particular, it is hard to use today’s web analytics tools to answer:

1. How successful has each product iteration been at driving user engagement?
2. Are there parts of our customers’ journeys that are better served by the product than others? What parts of the journeys need improving?
3. Where should we focus our product development efforts?

How does SnowPlow enable businesses to answer the above questions?

For us, the key to empowering data scientists and analysts to answer the above questions is to start by giving them access to the underlying raw (aka “atomic”) data, in a straightforward format that makes it easy to query.

The strength of this approach is that it gives analysts maximum flexibility to take the data, query it directly, and use the most appropriate analytics tools to perform the analyses that suit the companies, data sets and business questions they work with. For example measuring customer lifetime value for a bank is going to look very different to a telco, which will also look different to a retailer.

Rather than spend time developing our own reporting functionality to meet all the potential analyses that you might want to perform with web analytics data, we have developed a platform that gives analysts the atomic, customer-level data and lets them use whichever tools they believe will most effectively work on the data to meet their needs.

The focus of our approach, then, has been on making it easy for companies to collect all f the possible data they can from their web analytics system, and store it in the cloud in an infrastructure that scales effortlessly with enormous data volumes.

Analysts can then use the wide variety of general analytics tools that are available. Unlike web analytics packages, these have been developing very rapidly. They include:

1. Statistical and modelling tools e.g. R
2. Slice-and-dice OLAP technology e.g. Microstrategy, Tableau
3. Behavioural database technologies e.g. Skylab
4. Machine-learning and data mining tools e.g. Weka, Mahout

Making the underlying data available is not enough

Making the underlying data available to analysts is a big step in the right direction, but it is not enough to fix web analytics. Fortunately, SnowPlow has a number of other key features:

Powerful, scalable, flexible analytics with Apache Hive

Hive was developed at Facebook to enable analysts there to perform very involved analyses of how Facebook’s users engage with the product. Built on top of Hadoop, it is incredibly scalable. It is also very flexible, with a framework that lets analysts develop their own functions to use as part of their queries. Best of all, it is easy to use for any analyst with passing knowledge of SQL.

The ability to upload data from other sources into SnowPlow

It is straightforward to add additional data sources into SnowPlow and perform analytics against the combined data set. CRM data, data from social networks or even analyst-generated data (e.g. the values of different SnowPlow “events” based on models developed from SnowPlow data using other analytics systems like R) can be uploaded into SnowPlow and joined with SnowPlow’s own data. This makes SnowPlow an ideal place to assemble a “single customer view” which incorporates the customer’s all-important web behaviours, helping you to answer business questions that require analysis across a range of customer-data sources.

More to come

There’s a lot more work to do to take web analytics to where it should be in 2012. The areas we are actively working on to develop SnowPlow functionality include:

1. Developing connectors to pipe SnowPlow data into analytics databases, to enable faster train-of-thought analysis and lower-cost repeated querying
2. Designing connectors to pipe SnowPlow data into behavioural databases, and develop a compelling analytic toolset around behaviour and eventstream analysis
3. Building additional clients to generate SnowPlow event data from mobile apps, Flash games, desktop apps and so on
4. Developing tools to enable less technically-savvy analysts to get more value out of the SnowPlow data
5. Designing a range of tools to enable companies to use web analytics data from SnowPlow in real-time operational systems e.g. product recommendation, content personalisation

Interested in learning more about SnowPlow?

Visit the SnowPlow Github repository, check out the code and wiki. Or if you prefer you can send us an email.

Interested in joining us on this journey?

We see a revolution coming in web analytics. We are only just beginning to scratch the surface of what is possible with customer event-data. SnowPlow is an open source project, and we encourage anyone with an interest in using web analytics data in novel and interesting ways to work with us to develop the SnowPlow platform and the analysis methodology and toolset around it. Start off by checking out the SnowPlow repo on Github and the new SnowPlow Analytics website

Note – the arguments presented here are much the same as those we gave in our presentation Re-introducing SnowPlow: a New approach to Web Analytics. Here, we describe them in long-form.

The cohort analysis blog post series

• Cohort analyses for digital businesses: an overview
• Performing cohort analysis on web analytics data using SnowPlow
• Performing the cohort analysis described by Eric Ries in the Lean Startup
• On the wide variety of cohort analyses
• Approaches to measuring user engagement as part of cohort analysis
• Approaches to measuring customer value as part of cohort analysis
• Faking cohort analysis with Google Analytics

As part of our cohort analysis series, we have emphasized that there are a wide variety of different cohort analyses that are possaible, depending on the business question to be answered. To recap, just quickly, we can vary the cohort analysis by what metric we use to compare between cohorts, and by how we define our cohorts. We have written a post about comparing user engagement between different cohorts, and how this is valuable to especially to social networks, community sites and publishers. In this post, we look at comparing customer value, including customer lifetime value (CLV) between cohorts. We explain why this is important to all companies whose business models depend, at the end of the day, on monetizing users – including retailers, media companies and financial services companies. Lastly, we look at how to measure these values in SnowPlow, so that an appropriate cohort analysis can then be performed, as described in our previous blog post.

Weighing your customers' value

Defining customer value and customer lifetime value

The idea behind customer value and customer lifetime value in particular is very simple, in spite of all of the nuances associated with the mathematics of calculating them. The idea is that each customer provides our business with a certain amount of value (which we can measure as profit or revenue) over a period of time. That value might come in a number of ways: to take the example of a retailer, a customer might make a number of purchases from us over an extended time period, and we need to include the value of all those purchases when assessing that customer’s value. But the value that customer provides might not lie only in the purchases she makes. She may recommend products to her friends, thus providing additional value in terms of marketing / customer acquisition. She may write product reviews on the site: thereby making the site more engaging for other customers and prospective customers. These additional activities are valuable, but hard to quantify precisely.

Customer lifetime value or CLV is an extension of the concept of customer value. When we talk about a customer’s value, we talk in terms of their value over a period of time. If we want to be more rigorous, we might want to quantify the total value of the customer over their complete “lifetime” – i.e. the time period over which that person is a customer of ours. For some types of business (e.g. a cable subscription businesses), defining the lifetime is very easy: it’s just the length of time that person was a subscriber. For others (such as retailers), it is harder to quantify, because it is not clear when a person stops being a customer: there is always the possibility they will revisit the site and make another purchase.

Why is customer value and customer lifetime value so important?

Historically, different types of businesses have optimised themselves against different metics, depending on their particular commercial dynamics. For example: hotels and airlines have optimised on occupancy; newspapers, car manufacturers and record labels on units sold; financial services firms on the number and profitability of products sold.

There are two major weaknesses with metrics such as these:

1. They obscure the customer-company relationship at the heart of every company’s business, and instead focus attention on supply-side issues like product production, capacity and distribution, which are generally much easier to control than demand-side issues like the way a customer feels about your product, service and brand.
2. By not looking at the customer journey as a whole, these metrics lead companies into making decisions that often have unintended negative consequences at a different part of the customer journey.

To start with the first weakness: record labels have historically made all of their money selling CDs, and consequently have spent a lot of analytical effort understanding which are the best selling artists by geography, and then making sure that they distribute the right volume of CDs to those geographies to maximize profits. (Not too many to depress CD prices, not too few to artificially depress sales.) Today, the music industry has changed so that music is distributed digitally (so no fretting about matching CD supply with demand), and labels monetize their artists through record sales, streaming deals, live concerts and merchandising. Record companies therefore need to focus on understanding the complete value of each artist but also the complete value per fan. It is only when a record label understands which are its most valuable fans (those that go to the concerts, buy the merchandise, influence the prospects of upcoming artists etc.) that they will know where to target their marketing to maximize their profits going forwards. That means understanding the customer value of each of their users, and how that varies by age, affluence, taste in music and taste in music services (Spotify, Songkick and the like).

To illustrate the second weakness, consider the widespread example of the retailer advertising his products using AdWords, and measuring the return on that marketing spend based on the fraction of those users who clicked on the ads who went on to make a purchase, and the value of the purchases they made. This is often given as a textbook example for data-driven marketing. Unfortunately, it is massively flawed:

• Many customers visit a site more than once before making a purchase. By looking at users’ behaviour only on their initial visit after clicking on an ad, the retailer will likely overlook a large number of return-visit sales which stemmed from that initial AdWords click. This means that the retailer is liable to underestimate the return on their search engine marketing spend.
• Many customers who purchase after some ad exposure will go on to make additional purchases in the future. Some of these sales would never have been made were it not for them having seen the ad originally, and then making a successful initial purchase. However, none of the value of the repeat purchases is taken into account by the person calculating the return on this marketing spend, and as a result, the retailer is again liable to underestimate his return on marketing spend.

It is only when a company looks at the complete value of each customer acquired from a given marketing activity that they can calculate the true return on that marketing spend. If they do not do this, they risk cutting marketing spend from potentially their best long-term marketing channels, and spending too much on channels that only drive short term sales increases.

For another example of how a company can make a terrible strategic decision because it does not monitor the lifetime value of its customers, consider the (probably apocryphal) story of the bank that axed its children’s accounts after realizing that these were unprofitable. That bank’s profits will have risen in the short-term, and then nose dived 15 years after the decision was made, as the cohort of users who would have formed the bedrock of their future profitable customer base took up children’s accounts with competing banks.

Measuring customer lifetime value (general approach)

Measuring customer lifetime value is difficult for a number of reasons:

1. As described above, there are particular customer actions (such as recommending a brand to a friend) that certainly create value, but are hard to quantify
2. It is not always easy to define the “lifetime” of a customer, in other words to determine over what period we should measure that customer’s value
3. Many of our most interesting customers will be in an ongoing relationship with our business: without a crystal ball we cannot be very certain what value we will be able to realize from those customers in the future

A full discussion of how to address the above challenges is beyond the scope of this blog post, although we do plan to explore some approaches in future posts. At this stage we make the following general observations, before discussing how to perform some initial customer lifetime value (CLV) calculations using SnowPlow:

• While it is difficult to quantify those more “intangible” user actions (such as reviewing a product or “liking” a fan page), it is possible to come up with models to do so. Developing these valuation models means analysing what effect these actions have over a long time period, and comparing those effects with “control groups” where no equivalent action was performed. This is quite an involved analysis, and it requires good granularity of different customer journeys to perform – fortunately, SnowPlow provides the data granularity required. Once the analysis has been performed, a provisional value for that user action can be assigned.
• Whilst defining the lifetime of a customer can be very difficult, we can instead make do by looking at the customer value over a defined period of time (e.g. a quarter), and see how that varies over time periods: are we increasing the value of our individual customers over time, or decreasing it? And how does that vary by customer segment? If we adopt this approach, we need to perform a separate churn analysis, to capture the undesirable but important commercial impact of users who leave us.
• An alternative if we want to stick with customer lifetime value is to build a model that predicts the lifetime of each current customer, based on their behaviour to date. Again, because SnowPlow provides a very granular data set for each customer, it can support this type of analysis well. The output is a function that predicts the lifetime value of a customer based on their to-date behaviour, where the salient aspects of that behaviour have been carefully selected, through a combination of customer knowledge and data analysis.
• Whichever of the above routes is taken, remember that a given user’s CLV may evolve over time, based both on their own changing behaviour but also on our evolving model and understanding of valuable customer behaviour. This is fine and to be expected – the important thing is that at any given moment in time we can attribute a meaningful value for each customer.

Measuring customer lifetime value (using SnowPlow)

SnowPlow makes it easy to take calculate customer value based on web analytics data, unlike traditional web analytics programs. There are a number of key areas where it excels:

1. It makes it possible to measure the total revenue of each customer directly, over their entire history

Remember the example given earlier of the retailer that measured the return on AdWords spend based on the fraction of users who clicked on the ad and went on to purchase on the same visit? Well, SnowPlow makes it trivial to look at the amount of revenue booked each customer over all their visits, using the query below:

/* HiveQL */
SELECT
	`user_id` AS customer,
	YEAR(`dt`) AS time_period_year,
	MONTH(`dt`) AS time_period_month,
	SUM(`ev_value`) AS `customer_value_per_time_period`
FROM `events`
WHERE `ev_action` LIKE 'order-confirmation'
GROUP BY `user_id`, YEAR(`time_period`), MONTH(`dt`);

The query above sums all the order values for each `user_id` by time period (in this case, month). It is trivial modify the query to add in different types of events, by simply adding them to the `WHERE` clause. It is also trivial to take a different time period, e.g. looking by week, by aggregating the event dates using the Hive WEEKOFYEAR() function rather than the MONTH() function.

If we wanted to perform the above analysis just for a cohort or users who had come to the site from AdWords, we would perform a separate query to identify just those users who’d clicked on an AdWords ad (shown below), and then JOIN the results (a list of user_ids) with the output of the query above. This query would get you that list of user_ids:

/* HiveQL */
SELECT `user_id`
FROM `events`
WHERE `mkt_source` = 'google' AND `mkt_medium` = 'cpc' ;

2. It is possible to include the value of “intangible” customer actions (e.g. “liking a product”) in the SnowPlow customer value calculation

If your company has developed a model / view of the value of “intangible” or otherwise hard-to-value actions (e.g. “liking” a product), those values can be incorporated by SnowPlow when you perform the analysis. That’s because, unlike other web analytics packages including Google Analytics, SnowPlow lets you upload your own first- and third-party data into SnowPlow to use in the calculation.

In this case, you would create a table in SnowPlow with a list of the different actions, categorised by events, where each event has an associated value. The table would look like this:

/* HiveQL */
CREATE TABLE `intangible_event_values` (
	ev_category STRING,
	ev_action STRING,
	value INT
) ;

You would then upload your event values to the `intangible_event_values` table and JOIN it with the `events` table when calculating the value per customer per time period:

/* HiveQL */
SELECT
	`user_id` AS customer,
	YEAR(`dt`) AS time_period_year,
	MONTH(`dt`) AS time_period_month,
	SUM(`e`.`ev_value`) + SUM(`v`.`value`) AS `customer_value_per_time_period`
FROM `events` e JOIN `intangible_event_values` v ON (e.ev_category = v.ev_category AND e.ev_value = v.ev_value)
WHERE (`ev_action` LIKE 'order-confirmation' OR v.ev_value IS NOT NULL)
GROUP BY `user_id`, YEAR(`dt`), MONTH(`dt`) ;

This allows us to track customer value over time, and monitor how effectively we are growing customer value over time – for the user base as a whole, by customer segment, or for each customer (by looking at particular `user_id`s).

3. It is possible to include model-based estimates of future customer value in customer lifetime value calculations

Assuming that we have developed a model that predicts future customer value based on their behaviour to-date, we can incorporate that into SnowPlow to calculate the full-lifetime value for the current customer base.

Our function to predict customer lifetime value would take certain events in a customer’s past and uses those to predict future value. Because all of the events in a customer’s online history are stored in SnowPlow, it is possible to run the function in SnowPlow and calculate a full-lifetime value for each of our `user_id`s. From this point we could sum all of those individual CLVs to calculate the value of our current user base, and compare that with the value for our userbase last month or year, for example.

How we do this depends on the nature of our predictive function. If it can be written directly in Hive using HiveQL, we simply need to include it in our query. If it is not possible to compose in HiveQL, we can create a user-defined function in Java to do the calculation and return the result to Hive. Either way, our analysis proceeds as follows:

First we define our “live” userbase:

/* HiveQL with pseudo-code */
CREATE TABLE `this_months_customers` (
	user_id STRING
) ;

INSERT OVERWRITE TABLE `this_months_customers`
SELECT `user_id`
FROM `events`
WHERE dt>'2012-05-31' /* In this case we define our live customers as those who have engaged with us in June */
GROUP BY `user_id` ;

Next we perform a comparable query to identify customers who were “live” last month:

/* HiveQL with pseudo-code */
CREATE TABLE `last_months_customers` (
	user_id STRING
) ;

INSERT OVERWRITE TABLE `last_months_customers`
SELECT `user_id`
FROM `events`
WHERE `dt`>'2012-04-30' AND `dt`<'2012-06-01'
GROUP BY `user_id` ;

We then calculate the value of this month’s customers, using our customer lifetime value predictor function:

/* HiveQL with pseudo-code */
CREATE TABLE `lifetime_value_by_customer_this_month` (
	user_id STRING,
	predicted_lifetime_value FLOAT
) ;

INSERT OVERWRITE `lifetime_value_by_customer_this_month`
SELECT
	`user_id` AS customer,
	customer_lifetime_value_predictor_function(input parameters) AS `customer_lifetime_value`
FROM `events`
GROUP BY `user_id`  ;

Note that we look at the complete history of each of this month’s customers to predict their future value.

Next we perform the comparable calculation for last month’s customers:

/* HiveQL with pseudo-code */
CREATE TABLE `lifetime_value_by_customer_last_month` (
	user_id STRING,
	predicted_lifetime_value FLOAT
) ;

INSERT OVERWRITE `lifetime_value_by_customer_last_month`
SELECT
	`user_id` AS customer,
	customer_lifetime_value_predictor_function(input parameters) AS `customer_lifetime_value`
FROM `events`
WHERE dt<'2012-06-01'
GROUP BY `user_id`  ;

We can now compare the two tables `lifetime_value_by_customer_last_month` with `lifetime_value_by_customer_this_month` to see how the average lifetime has varied, how the distribution of lifetime value has varied and how the number of customers by lifetime value has varied.

We will explore different approaches to developing the customer_lifetime_value_preditor_function in future blog posts.

What to learn more about SnowPlow? Then visit the SnowPlow website, and in particular, the guide to calculating customer lifetime value using SnowPlow.

The introducing SnowPlow blog post series

• Introducing SnowPlow: the world’s most powerful web analytics platform
• SnowPlow update: first part of source code published
• Warehousing your online ad data with SnowPlow
• Reintroducing SnowPlow: what is wrong with web analytics in 2012 and how we fix it. (Presentation version)
• What is wrong with web analytics in 2012, and how SnowPlow starts to fix it. (Blog post version)

In the last few weeks we have talked to many different people and organisations about SnowPlow. One of the things that has only become obvious since having those conversations is that the approach we’ve taken in developing SnowPlow is surprising to many of the people we’ve spoken to. That’s at least partly because the journey we’ve been on, in thinking about how web analytics can be done better, is not one that everyone else has necessarily been on.

The slide deck below is our attempt to describe that journey. It sums up, as briefly as we can manage, what is wrong with web analytics today, and how we believe SnowPlow addresses those fundamental problems in a fresh and new way. It goes on to outline some of the areas we hope to develop SnowPlow in.

I plan to write a blog post discussing some of these issues in more detail in the next few days. But in the meantime, check out the deck: we’d love your feedback.

Update: since writing this post we’ve launched a dedicated SnowPlow website. Check it out for more information on Snowplow.

The cohort analysis blog post series

• Cohort analyses for digital businesses: an overview
• Performing cohort analysis on web analytics data using SnowPlow
• Performing the cohort analysis described by Eric Ries in the Lean Startup
• On the wide variety of cohort analyses
• Approaches to measuring user engagement as part of cohort analysis
• Approaches to measuring customer value as part of cohort analysis
• Faking cohort analysis with Google Analytics

User engagement is one of the most interesting, most important, and yet challenging areas of data analysis. In this post, we will look at different metrics which can be employed to understand user behaviour as part of our overall series on cohort analyses. In addition, we will show how each suggested user engagement metric can be calculated in a straightforward way using SnowPlow.

Not an engaged user

Historically, it was online publishers who were most concerned with the level of their users’ engagement. But the advent of social networks like Facebook and Twitter, community sites (like Mixcloud and Mumsnet) and socially-aware applications (like Spotify and Steam) means that there is now a much larger number of businesses whose success depends very directly on how good they are at getting users to engage frequently (many times per day or month) and deeply (long sessions, many page views). That may be because engagement directly drives revenue (e.g. for an ad-funded business), or because building up a critical mass of users is key to building a viable online marketplace or social network.

There are countless potential measures of metrics to capture user engagement – to give just some examples:

• Number of visits / logins per month
• Number of page views per month
• Amount of time spent on the site / in the product
• Number of articles opened / read / downloaded / shared / saved / favourited
• Number of sessions per month where the user spends a minimum amount of time on the site / connects with another users / completes a level on a game

Which ones are most relevant to you will depend on your business, and the ways in which your users engage with your product. Let’s take some (famous) examples:

Example 1: User engagement at Facebook

In the case of Facebook, one well established user behaviour is to log into Facebook (e.g. at work, fairly early on in the day) and leave the page open during the day, rechecking it at intervals when there is a lull to see if anything interesting has appeared in the user’s stream. (Similar behaviour is evident when people access Facebok via mobile.) If this represents a significant fraction of user behaviour, then it becomes meaningless to measure the depth of engagement by the length of time a session lasts (because a session may last 8 hours). Similarly, measuring the breadth of engagement by the number of sessions would be misleading, because a user who logs in and out 5 times in a day (because they are on a public computer) is no more engaged than one who logs in once on their own machine, and keeps the page open all day.

It is likely for this reason (although we haven’t confirmed with analysts at Facebook), that Facebook tracks the number of days per week (or days per month) that a user logs in as a measure of the breadth of their engagement. Most likely Facebook would use a measure like this to get a handle on a user’s overall engagement with Facebook, and at the same time also look at how deeply or actively a user engages with Facebook by looking at how many items in their stream they actively engage with (e.g. by commenting on them, liking or actively contributing content to the stream by posting status updates and photos). Our guess would be that, for Facebook, user engagement is tracked across these two dimensions: one for breadth (number of days per month) and one for depth (level of activity when engaged, from active to passive).

Example 2: User engagement at eBay

For eBay on the other hand, measuring engagement is more difficult. For starters, there are different types of users on eBay, and we might want to apply different methods for measuring engagement based on the user type. Buyers on eBay may make up a distinct group as opposed to sellers. In the case of buyers, we might superficially be interested in the number of products they search for on eBay – so perhaps number of searches per week is a relevant metric? Or we might be interested in the breadth of products they look for on eBay – so a user who searches for electronic goods, clothing and stationery is more “engaged” than one who only looks at second-hand books. We might also want to analyse the “depth” of engagement by looking at how actively a user bids on products on eBay: what is the number of products bid on per week? How actively does the user bid on each item?

Conversely, if we look at sellers on eBay, things become more straightforward. For sellers we are interested in the number of items they sell (per week or per month). We might also want to quantify what more advanced sellers are making available on eBay relative to what they are retailing on their own website or Amazon Marketplace, or look at how frequently they are upgrading their listings with additional photos and similar. In all of this, we may want to differentiate “professional” sellers (including those who sell products on multiple channels) from part-time or hobbyists, who use eBay to sell their old stuff.

Measuring user engagement using SnowPlow

SnowPlow offers a lot of flexibility to use multiple different measures of engagement. To start with, let’s take the simple case of measuring each user’s engagement by the number of ‘actions’ they have performed (where an action might be anything from loading a web page, to playing a piece of media, to adding an item to a shopping basket). Because a line of data is recorded in SnowPlow’s event table every time a user peforms an action (and each line has a unique `txn_id`, all we need to do is sum the number of actions take per user per month:

/* HiveQL */
INSERT OVERWRITE TABLE `engagement_by_user`
SELECT
	`user_id`,
	YEAR(`dt`),
	MONTH(`dt`),
	COUNT(`txn_id`) AS `engagement`
FROM `events`
GROUP BY `user_id`, YEAR(`time_period`), MONTH(`dt`);

We can make our measure more sophisticated by weighting different actions based on the level of engagement they demonstrate. For example, the folks at Facebook might decide that posting a status update or uploading a photo constitute more active levels of engagement than simply liking a post in your stream, or clicking on a friend’s photo album. In this case, in SnowPlow, we would create a new table populated with the different user action types (e.g. from the `event_action` field) and assigning each a score:

/* HiveQL */
CREATE TABLE `engagement_by_event_action` (
	`event_action` string,
	`engagement_level` int
);

We would then populate the table with a list of different types of action and a representative value for the engagement level, like so:

Note: the above table would have to include a value for every type of action recorded in the SnowPlow events table. Our query for calculating the weighted engagement level by user per period would then join the `events` table with the `engagement_by_event_action` table:

/* HiveQL */
INSERT OVERWRITE TABLE `engagement_by_user`
SELECT
	`user_id`,
	YEAR(`dt`),
	MONTH(`dt`)
	sum(`engagement_level`) AS `engagement`
FROM
	`events`
JOIN
	`engagement_by_event_action`
ON
	`events`.`event_action` LIKE `engagement_by_event_action`.`event_action`
GROUP BY
	`user_id`, YEAR(`dt`), MONTH(`dt`);

For the eBay example, our first suggested measure of breadth of engagement for buyers was to examine the number of product searches performed. Assuming that every product search by a user is logged in Snowplow as an event with `event_action` = ‘search’ and `event_property` = “search string”, we could as a first measure simply count the number of searches per time period per buyer:

INSERT OVERWRITE TABLE `engagement_by_buyer`
SELECT
	`user_id`,
	YEAR(`dt`),
	MONTH(`dt`),
	COUNT(`txn_id`) AS `engagement`
FROM
	`events`
WHERE
	`event_action` like "search"
GROUP BY
	`user_id`, YEAR(`dt`), MONTH(`dt`) ;

If we want to be more sophisticated and look at the breadth of items searched for (e.g. the number of different product categories sought by the user), we would need:

1. A way to categorise the search query into a likely product category
2. To count the number of categories searched

Assuming our search facility had some kind of categorisation engine (i.e. could approximate the specific category of product the user is currently searching for), we could create a table in Hive that maps search terms to categories:

/* HiveQL */
CREATE TABLE `category_by_search_term` (
	`search_term` string,
	`product_category` string
);

We could then examine the breadth of products searched for by buyers using the following query:

/* HiveQL */
INSERT OVERWRITE TABLE buyer
SELECT
	`user_id`,
	YEAR(`dt`),
	MONTH(`dt`),
	COUNT(DISTINCT `product_category`) AS `engagement`
FROM
	`events`
JOIN
	`category_by_search_term`
ON
	`events`.`event_property` LIKE `category_by_search_term`.`search_terms`
GROUP BY
	`user_id`, YEAR(`dt`), MONTH(`dt`);

Meanwhile, to examine the engagement levels of sellers on eBay, we could look at the number of products listed by each seller per month. Assuming that each time a seller lists an item, SnowPlow records an event with `event_type` = “list” and `event_property` = item sku, it would be relatively simple to count the number of items listed per month:

/* HiveQL */
INSERT OVERWRITE TABLE `engagement_by_seller`
SELECT
	`user_id`,
	YEAR(`dt`),
	MONTH(`dt`),
	COUNT(`txn_id`) AS `engagement`
FROM
	`events`
WHERE
	`event_action` LIKE "list"
GROUP BY
	`user_id`, YEAR(`dt`), MONTH(`dt`) ;

If we wanted to compare the number of different SKUs listed as well, that is also straightforward:

/* HiveQL */
INSERT OVERWRITE TABLE `engagement_by_seller`
SELECT
	`user_id`,
	YEAR(`dt`),
	MONTH(`dt`),
	COUNT(DISTINCT `event_property`) AS `engagement`
FROM
	`events`
WHERE
	`event_action` LIKE "list"
GROUP BY
	`user_id`, YEAR(`dt`), MONTH(`dt`) ;

Comparing this number against a hypothetical number for the total amount each seller could list would require some data gathering / intelligence outside of SnowPlow to look at e.g. the total number of items the seller makes available on competitor platforms such as Amazon. This data would be put into a Hive table as below:

/* HiveQL */
CREATE TABLE `max_skus_by_user_id` (
	`user_id` string,
	`yr` int,
	`month` int,
	`max_skus` int
);

And finally, performing the comparison:

/* HiveQL */
INSERT OVERWRITE TABLE `engagement_by_seller`
SELECT
	`events`.`user_id`,
	YEAR(`dt`),
	MONTH(`dt`),
	COUNT(DISTINCT `event_property`) / `max_skus` AS `engagement` /* Note `engagement` needs to be type float or double */
FROM
	`events`
JOIN
	`max_skus_by_user_id`
ON
	`events`.`user_id` LIKE `max_skus_by_visitor`.`user_id`
WHERE
	`events`.`event_type` LIKE "list"
GROUP BY
	`user_id`, YEAR(`dt`), MONTH(`dt`) ;

Deciding which metric to use for user engagement

As the above examples hopefully illustrate, there are many possible metrics which you can employ to describe and measure user engagement. Which metrics are most applicable will vary by business: for each business, there will be multiple possible measures that might potentially work. Whilst a full discussion of all the different possibilities is beyond the scope of this post, a quick tip is that if you do have a number of different possible metrics, and if they rate different users similarly, relative to one another, for equivalent levels of engagement, then chances are that all of them are reasonably robust, and you are reasonably safe picking any one of them (because all of them give similar results).

Interested in using SnowPlow to measure engagement on your website or application?

Visit the SnowPlow website to learn more, especially the page on using SnowPlow to measure user engagement.

The cohort analysis blog post series

• Cohort analyses for digital businesses: an overview
• Performing cohort analysis on web analytics data using SnowPlow
• Performing the cohort analysis described by Eric Ries in the Lean Startup
• On the wide variety of cohort analyses
• Approaches to measuring user engagement as part of cohort analysis
• Approaches to measuring customer value as part of cohort analysis
• Faking cohort analysis with Google Analytics

In the last two blog posts in this series we looked at two different examples of cohort analysis and how to perform them using SnowPlow. The first example was taken from a Twitter case study, while the second example was taken from Eric Ries’s book The Lean Startup.

A wide variety of cheeses

In this post, we cast the net wider, to bring out the breadth of cohort analyses that you can potentially perform. As should become clear as you read this post, we don’t see “cohort analysis” as a single report that looks the same for every company: rather it is a whole category of analyses which can be brought to bare to answer a number of different business questions. As a result, the specific cohort analyses you conduct will depend on the exact nature of your business and the specific questions you need to answer. But for any one business, there will be a number of cohort analyses that will be relevant to answer a range of different business questions. So, if for now you’re only using one type of cohort analysis, we suggest you look creatively at how to employ this powerful technique to answer other business questions you face.

Below we outline the range of cohort analyses which are possible, outlining different metrics which can be employed to compare the behaviour of customers in different cohorts, before going on to outline some different ways of defining cohorts. In subsequent posts, we will dive into some of these variations in more detail, and explain how to perform those analyses using SnowPlow.

Different types of metric to compare between cohorts

There are a number of different types of metric which we might want to compare between cohorts:

1. Measures of user engagement. In the Twitter example, we looked at one possible measure, but as we said then, there are many other possibilities; we will outline some of them in the next blog post. Which measures are most appropriate will depend on the type of business and the way customers of that business engage with them.
2. Stages in a customer journey or funnel. In the Lean Startup example we looked at one example; we will look at variations on this in a future post. SnowPlow offers analysts the exciting possibility of defining new funnels retrospectively – in other words, an analyst can create bespoke funnels after-the-fact and then track retrospectively how many users in each cohort have progressed through the various stages of this funnel.
3. Customer Lifetime Value (CLV). This is perhaps the most important and valuable metric to compare between cohorts. Unfortunately (although not coincidentally), it is one of the more nuanced and least understood. Again, this is something we will explore more fully in a forthcoming blog post.

Different ways to define cohorts

There are a number of different approaches to defining our cohorts to compare. Which approach we use will depend critically on the business question we are looking to answer. To give some examples:

1. Cohorts defined by when the user joins/registers. Both of our in-depth examples in previous blog posts defined each cohort by when a user first registered for a service (either Twitter or IMVU, depending on the case study). In both cases, each cohort included a group of users who had all registered with the service in the same month. However, we might want to vary the time period over which we group users: for example, if we release new product versions on a weekly basis, it makes sense to compare new users this week with last week and see if there are significant differences. Likewise, we might want to key our cohorts off a different “trigger point” in the customer journey – for example, an online retailer might bucket users by when they made their first purchase, or when they signed up to an email newsletter. Again, SnowPlow makes dividing users into different cohorts using these different definitions very easy to do retrospectively – we’ll explore this in a separate blog post.
2. Cohorts defined by a type of user behaviour. One of the things we work with a number of businesses to do is to segment their users (e.g. using a cluster analysis) into groups based on different types of user behaviour. For an online retailer, for example, we might differentiate regular shoppers from infrequent shoppers, people who shop from work with people who shop from home; and people who shop for themselves with people who buy items as presents for others. In any of these examples, SnowPlow is flexible enough to enable us to bucket users into different cohorts by whatever behaviour we believe is predictive of underlying differences in user attitude and behaviour.
3. Cohorts defined by a user characteristic. As well as segmenting our customer base by behavioural characteristics, we can also segment them into different cohorts based on other user data points that we capture. For example, a social network might want to compare engagement rates between men and women, between different age groups, or between different countries. As long as that data is being captured, it can be used with SnowPlow to easily assign users to cohorts to compare behaviours between cohorts
4. Cohorts defined by the stage in a user’s journey. This is particularly useful for services which users engage with over a long period, and behave in different ways at different stages. For example, a blogging platform (like WordPress.com or Tumblr) might observe that users go through multiple stages when they setup a blog: coming up with a topic, customising the appearance, starting to post, developing a readership, posting more regularly, promoting on Twitter/Facebook, running out of steam, posting less frequently, quitting the blog. Different users might spend different periods at each stage (and some might not progress beyond even the first stage). Comparing engagement levels or customer lifetime value between users at different stages is a potentially useful exercise for companies like WordPress and Tumblr.
5. Cohorts defined by what channel a customer was acquired on. To calculate an accurate return on marketing spend, we need to analyse the average customer lifetime value of customers acquired on different marketing channels, by campaign. SnowPlow makes it easy to do this, including distinguishing users who arrive at a site for the first time from a particular channel with those that responded to a campaign on the same channel having already visited the site earlier. We will explain how to do this in a future blog post.

Have we missed anything? We are always on the lookout for new and imaginative applications of cohort analyses. If there’s a variation we haven’t covered, and you’d like us to, then get in touch! Similarly, if you’d like to discuss how to use SnowPlow and/or cohort analyses to answer your business questions, then get in touch as well.

Update: ready for more? The next blog post in this series is now available: Different metrics to understanding user engagement when performing cohort analyses.

The cohort analysis blog post series

• Cohort analyses for digital businesses: an overview
• Performing cohort analysis on web analytics data using SnowPlow
• Performing the cohort analysis described by Eric Ries in the Lean Startup
• On the wide variety of cohort analyses
• Approaches to measuring user engagement as part of cohort analysis
• Approaches to measuring customer value as part of cohort analysis
• Faking cohort analysis with Google Analytics

This blog post is the third in our series on cohort analyses using SnowPlow. In the first post, we provided an overview of cohort analyses: why they are so powerful and what are the different analytic steps necessary to perform a cohort analysis. In the second post, we looked at why SnowPlow is such a good platform for performing cohort analyses using web analytics data, and worked through the specific example of the Twitter cohort analysis that gets so much attention in startup circles.

In this post, we will follow up with a look at another famous example of a cohort analysis: this time from Eric Ries’s excellent book The Lean Startup. We will show how a company running SnowPlow can easily perform the type of analysis Eric performed when he was CTO at IMVU, to assess the progress they were making towards achieving a product-market fit.

A version of the data Eric Ries presents in his book is shown below:

Interpreting Eric Ries’s cohort analysis

Before I describe how to perform Eric Ries’s cohort analysis, let’s remind ourselves what this analysis demonstrates.

The analysis shows the percentage of new users added each month, split by what stage they reached in their customer journey. Remember that Eric’s startup had an instant messaging product where customers talked to one another using 3D avatars; ultimately, the success of the company depending on persuading users to pay for the product, but that meant each user working through a number of “stage gates”, including:

1. Downloading the product
2. Registering
3. Creating a 3D avatar (potentially more than one)
4. Connecting with another user (potentially multiple users)
5. Signing up to the service and paying the subscription fee

Understanding how many potential customers progressed through each stage-gate was critical to IMVU. The graph above looks at the number of people who registered in each of the first 7 months after the first version of their product was launched, and looks at what percentage of users made it through to each stage gate. The aim was to get as many customers as possible to the final stage gate: “Paid”. As you’ll see from the graph however, the percentage of users who make it to this stage each month was very small (less than 1.4% in each month.) Worse, this percentage did not increase month-by-month, as the team worked to improve the product. It was this lack of improvement, in spite of enormous product development effort, that persuaded the IMVU team to pivot their product, with dramatic effect.

There are some interesting things to note when comparing this analysis to the one we performed in the last post. As in the previous analysis, each cohort is defined by the month that the user started using the service (although in this case, that “starting point” is determined by the point at which the user “registers”, which is after they have already downloaded the product). For each cohort, we only look at one month of data. This is very different from the previous example, where we looked at the average engagement level of each cohort over successive months. This may be because the IMVU guys found that if a user hadn’t signed up to their service after 1 month, there was a negligable chance that they would go on to do so. (Meaning that the timeframe for converting a prospect to a paid user was reasonably short, and hence there was no point including subsequent months in the analysis for each cohort. This makes the IMVU product very different to Twitter, where a user may only graduate to a highly engaged user after two or even three months.) When you consider performing a comparable analysis for your company, decide carefully what time frame to look at, so you don’t miss relevant data points for each cohort by picking an inappropriate time period.

Performing the cohort analysis using SnowPlow

We will perform the analysis by following the steps outlined in the first post i.e.:

1. Start by defining the business question
2. Work out what is the most appropriate metric to measure, given the business question
3. Define the cohorts
4. Perform the analysis

1. Define the business question

This is simply: are we getting better over time at getting new people introduced to the product to become paid users?

2. What is the most appropriate metric to measure, given the business question?

In this case it is the percentage of users who register who go on to reach the different stage gates identified in the list above.

It’s important to note that SnowPlow’s data table in Hive contains five fields that describe a particular “event” in a user journey:

In our case, we’ll assume that the different actions that indicate what funnel stage a user is in can be read from the Action field (called `event_action` to be precise). In the IMVU case, that might mean that we need to check for the following values in the `event_action` column for a particular user:

1. `Subscribe` to indicate he/she has reached the final stage in the funnel
2. `Made-friend` to indicate he/she has connected with another user
3. `Created-avatar` to indicate he/she has created an avatar
4. `Login` to indicate he/she has logged in
5. `Register` to indicate he/she has logged in

To work out what stage each visitor has reached in his/her customer journey, we process the `snowplow_events_table` for each `user_id` and combine all the different `event_actions` which a user has taken into a single set (stored as a HiveQL array):

/* HiveQL */
CREATE TABLE `actions_by_user` (
	`user_id` string,
	`acts` array<string>
);

INSERT OVERWRITE TABLE `actions_by_user`
SELECT
	`user_id`,
	collect_set(`event_action`) AS `acts`
FROM `snowplow_events_table`
GROUP BY
	`user_id`;

For each user, we then look through the array of different actions that he / she has performed to work out at what stage each visitor is in the funnel:

/* HiveQL */
CREATE TABLE `stage_in_funnel_by_user` (
	`user_id` string,
	`stage` string
);

INSERT OVERWRITE TABLE `stage_in_funnel_by_user`
SELECT
	`user_id`,
	CASE
		WHEN array_contains(`acts`,"Subscribe") THEN "Subscribe"
		WHEN array_contains(`acts`,"Made-friend") THEN "Made-friend"
		WHEN array_contains(`acts`,"Created-avatar") THEN "Created-avatar"
		WHEN array_contains(`acts`,"Login") THEN "Login"
		WHEN array_contains(`acts`,"Register") THEN "Registered"
		ELSE "Not-registered" END AS stage
FROM `actions_by_user`;

Note that we start from the final stage in the funnel. For each visitor, we check if they have reached the final stage, and if not, work backwards stage-by-stage to work out if they’ve reached the previous stage.

3. Define your cohorts

This is straightforward: we want to divide up our users into cohorts based on the date in which they registered for the product. This is done using the following query:

/* HiveQL */
CREATE TABLE `users_by_cohort` (
	user_id string,
	cohort string
);

INSERT OVERWRITE TABLE `users_by_cohort`
SELECT
	`user_id`,
	concat(year(min(`dt`)),"-",month(min(`dt`))) as cohort
FROM `snowplow_events_table`
WHERE `event_action` LIKE "Register"
GROUP BY `user_id`;

4. Perform the analysis

We now need to join our `users_by_cohort` table which defines which cohort each `user_id` belongs with our `stage_in_funnel_by_user` which tells us which stage in the customer journey each user belongs to:

/* HiveQL */
CREATE TABLE `cohort_analysis` (
	`cohort` string,
	`stage` string,
	`number_of_users` int
);

INSERT OVERWRITE TABLE `cohort_analysis`
SELECT
	`cohort`,
	`stage`,
	count(`stage_by_user`.`user_id`) as `number_of_users`
FROM
	`users_by_cohort`
JOIN
	`stage_by_user`
ON
	`users_by_cohort`.`user_id` = `stage_by_user`.`user_id`
GROUP BY
	`cohort`, `stage`

And that’s all there is to this retrospective cohort analysis! Our resulting table looks as follows:

It is now straightforward to convert the absolute numbers of users in each cohort it a percentage value and plot a graph similar to the one displayed at the top of this blog post.

Interested in learning more? Then visit the SnowPlow website, including the section on performing cohort analyses with SnowPlow.

Update: you can now also discuss this blog post on Hacker News.

SnowPlow Security holds the line

At Keplar we are now hard at work building sophisticated web analytics, such as cohort analyses, using our open-source SnowPlow technology stack (now available on GitHub). So far, all of these analyses are being built on top of the “eventstream” data collected from a client’s website using the SnowPlow JavaScript tag installed across all pages.

Alongside these “eventstream”-based analyses, we are designing another set of analyses – equally powerful – based on the transactional data which lives inside your ecommerce platform; we have chosen to focus on PrestaShop first, because we have already developed and open-sourced a tool for fetching transactional data out PrestaShop…

The first of our new analyses will be around customer lifetime value (CLV) analysis. CLV analysis is critically important for retailers (as it is for just about every other type of business). Customer lifetime value analysis makes it possible for retailers to quantify the value of an average customer over his/her expected lifetime, and hence work out how much it is worth spending to acquire new customers. But that is just the beginning: once a company has a handle on their customer’s lifetime value, they can perform a cluster analysis to spot different segments of users with different average lifetime values – thus identifying different groups of customers, with different characteristics, some of whom are worth spending much more on acquiring and cultivating than others.

SnowPlow lets retailers go even further with customer lifetime value analysis, however. By combining a customer lifetime value analysis with the SnowPlow eventstream analytics, it is possible to identify those events and customer behaviours that:

1. Are predictive of a customer belonging to a higher value segment, or:
2. Are predictive of them migrating from a lower value segment to a higher value segment

This is invaluable to retailers who want to maximize satisfaction and profit per customer, guiding product development, service development and marketing promotions so that the retailer delivers services that their customers love.

Although we will start with CLV, we have a roadmap of other analyses to build leveraging operational e-commerce data; stay tuned for more about these other analyses in the near future.

So, we are looking for online retailers (up to five to start with) who are currently using PrestaShop and would like early access in return for giving us feedback and helping us to improve the analyses. Joining our Early Access Programme for SnowPlow will mean competition-beating access to some killer analytics tools, plus a chance to shape their evolution to help super-charge your business. And, assuming that this evolves into a hosted customer lifetime analysis product, we will give our Early Access Programme participants free access to this product for six months, followed by a first-year discount should you wish to continue using the product.

This is what we are looking for in a SnowPlow EAP participant:

• You use of a recent version of PrestaShop, with Web Services enabled (or ready to be enabled)
• You have had substantial order volumes over a two-to-three year period (if not more!)
• All of your orders are stored in PrestaShop (or easily accessible if partially archived)
• A good proportion of your customers are repeat customers

If you think that you fit the bill, we would love to hear from you and help us build the future of e-commerce analytics together! Please email us at eap@keplarllp.com with a brief introduction to you and your online shop.

At Keplar we remain committed to using open-source projects where possible to keep costs down for our clients and to avoid “reinventing the wheel”. Where high-quality open-source projects exist which meet our client’s needs, we use them by default; there are too many of these to name them all, but recent projects would have been impossible without Hive (Hadoop ecosystem), Spray (Scala/Akka), DictShield (Python), WAI (Haskell) and of course Redis.

Where open-source projects do not exist that meet our requirements, we are increasingly looking to develop those tools in-house and open source them where possible (i.e. where they are not part of a client deliverable). Our biggest initiative so far in this area is the SnowPlow web analytics platform, which since its soft-launch in February is already being used externally by one ad network to track ad impression data and internally by our team to perform some sophisticated analytics, such as website cohort analyses.

Other projects we have open-sourced since our CodeIgniter PayPal module include a Scala client for the Amazon Product Advertising API, a command-line tool for exporting Google Analytics data to CSV flatfiles, and a Scala client for the PrestaShop e-commerce API – the latter another release under our “Orderly” initiative for better e-commerce workflow automation and data analysis.

Onto our new Symfony2 library for PayPal IPN…

As with the original CodeIgniter version, this library is designed to make it easier for developers using Symfony 2 to receive, validate and store instant payment notifications (IPNs) sent by PayPal when an order has been paid for by a customer. To be clear: there is already a “maximalist” bundle for handling PayPal payments in Symfony2 – the excellent JMSPaymentPaypalBundle. By contrast, symfony2-paypal-ipn is a “minimalist” bundle which focuses on the post-payment workflow, validating an IPN notification from PayPal and then logging the order and order line items into your database (using the Doctrine 2 ORM). Sending an order confirmation email is also super-simple with our library.

For instructions on installing the bundle, please see the README file in the project’s repository. Once the bundle is installed, using it in a Symfony controller to validate incoming orders and send order confirmation emails is quite straightforward – just write a controller like this:

use Symfony\Component\HttpFoundation\Response;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Orderly\PayPalIpnBundle\Ipn;

class TwigNotificationEmailController extends Controller
{

    public $paypal_ipn;
    /**
     * @Route("/ipn-twig-email-notification")
     * @Template()
     */
    public function indexAction()
    {
        //getting ipn service registered in container
        $this->paypal_ipn = $this->get('orderly_pay_pal_ipn');

        //validate ipn (generating response on PayPal IPN request)
        if ($this->paypal_ipn->validateIPN())
        {
            // Succeeded, now let's extract the order
            $this->paypal_ipn->extractOrder();

            // And we save the order now (persist and extract are separate because you might only want to persist the order in certain circumstances).
            $this->paypal_ipn->saveOrder();

            // Now let's check what the payment status is and act accordingly
            if ($this->paypal_ipn->getOrderStatus() == Ipn::PAID)
            {
                //preparing message
                $message = \Swift_Message::newInstance()
                    ->setSubject('Order confirmation')
                    ->setFrom('support@CHANGEME.com', 'TEST')
                    ->setTo($this->paypal_ipn->getOrder()->getPayerEmail(), $this->paypal_ipn->getOrder()->getFirstName() .' '. $this->paypal_ipn->getOrder()->getLastName())
                    ->setBody($this->renderView('OrderlyPayPalIpnBundle:Default:confirmation_email.html.twig',
                            // Prepare the variables to populate the email template:
                            array('order' => $this->paypal_ipn->getOrder(),
                                  'items' => $this->paypal_ipn->getOrderItems())
                            ), 'text/html')
                ;
                //send message
                $this->get('mailer')->send($message);
            }
        }
        else // Just redirect to the root URL
        {
            return $this->redirect('/');
        }

        $response = new Response();
        $response->setStatusCode(200);

        return $response;
    }
}

If you prefer not to send an order confirmation email, then checkout the NoNotificationController.

Let us know how you get on with this library in the blog comments – and if there’s a feature missing that you would like, feel free to raise a new issue over on GitHub. We hope you find it useful, and stay tuned for more “Orderly” releases very soon!

The cohort analysis blog post series

• Cohort analyses for digital businesses: an overview
• Performing cohort analysis on web analytics data using SnowPlow
• Performing the cohort analysis described by Eric Ries in the Lean Startup
• On the wide variety of cohort analyses
• Approaches to measuring user engagement as part of cohort analysis
• Approaches to measuring customer value as part of cohort analysis
• Faking cohort analysis with Google Analytics

In the previous blog post in this series, cohort analysis for digital businesses: an overview we described what cohort analysis is and why it is so powerful. In this post, we will look at how to perform cohort analysis on web analytics data in SnowPlow. We will start with an overview of the general methodology and approach for cohort analysis using SnowPlow, and then launch into a specific example analysis: the Twitter engagement example that made cohort analysis so famous in startup circles.

Methodology for performing cohort analyses in SnowPlow

SnowPlow has been designed to:

1. Make it easy to perform specific cohort analyses
2. Give users maximum flexibility to perform a wide range of cohort analyses, by making it easy to define cohorts in multiple ways and leverage multiple different metrics to measure and compare between the different cohorts

To understand what makes SnowPlow so suitable for cohort analyses, we need to consider the way data is structured in SnowPlow. This is represented in the diagram below:

SnowPlow records all data in a single events table in Hive. Whenever one of your customers does anything on your website, be it click on a link, fill in a web form, play a video, add a product to basket, perform a search or roll-over an ad (to give just some examples), a line of data is generated in the events table.

The events table contains a number of different fields. Some of these describe the particular event: distinguishing the type of event (e.g. a pageview or an add-to-basket), noting the time and date of the event, and recording the value of the event if it is an e.g. transaction; other fields relate to the customer performing the action: distinguishing customers by the type of device they are using, where they are located, what language their browser is set to for example. (For a complete description of the table and fields, see the analyst documentation on the SnowPlow website.

Recall from our last blog post that performing a cohort analysis is a four step process:

1. Define your business question
2. Work out what is the most appropriate metric (or set of metrics) to measure, given your business question
3. Define your cohorts, given the business question
4. Perform the analysis

From a data crunching perspective, the key challenge when performing a cohort analysis is to access the data necessary to calculate the metric identified in step 2, sliced by the cohorts defined in step 3. This is not always straightforward with conventional web analytics tools – we will explore the limitations to doing this in Google Analytics in a later post.

We can now see what makes SnowPlow such a good platform to perform cohort analyses. Defining our cohorts and performing the data separation is simple: we define our cohorts by any of the data fields we associate with each visitor or, equally, we can define our cohorts based on user behaviour (or even by some combination of the two e.g. define a cohort of “male customers who shop regularly”). In either case, a straightforward HiveQL SELECT statement of the following form will return a table mapping customers (i.e. user_ids) to cohorts (however they are defined, in terms of either customer data, or event data, or a combination of the two):

/* HiveQL with pseudo-code */
CREATE TABLE `user_cohort_map` AS
SELECT
	`user_id`,
	[[ Generate cohort_ids using visitor_attributes and visitor_events ]] AS `cohort_id`
FROM
	`events`
GROUP BY
	`user_id`

Because we have all our raw data about customers and events in the events table, we have maximum flexibility to define our cohorts however we want, based on any combination of them. (In simple cases the cohort function can be written directly in HiveQL, in more difficult cases by writing a user-defined function.)

As a next step, we need to calculate the value of the desired metric we are comparing the cohorts against for each visitor. Again, this is straightforward to generate from the same events table:

/* HiveQL with pseudo-code */
CREATE TABLE `metric_by_user` AS
SELECT
	`user_id`,
	[[ Generate metric_per_customer using visitor_attributes and visitor_events ]] AS `metric_per_customer`
FROM
	`events`
GROUP BY
	`user_id`

Once again, we have maximum flexibility to calculate any metric we want, based on having all the available data in the events table.

In the final step, we aggregate the metrics generated per user to a cohort level, so that we can compare the results for each cohort against one another:

/* HiveQL with pseudo-code */
SELECT
	`cohort`,
	[[ Aggregate the metric over all the customers in each cohort ]] AS `metric_per_cohort`
FROM
	`user_cohort_map`
INNER JOIN
	`metric_by_user`
ON `user_cohort_map`.`user_id` = `metric_by_user`.`user_id`
GROUP BY
	`cohort`

Let’s apply this approach to a specific example:

Example 1: Looking for improvements in user engagement over time (the Twitter case study)

As a first example we’ll take the famous Twitter cohort analyiss exploring whether user engagement levels were rising over time. A version of the data is shown below. Remember: the folks at Twitter wanted to examine whether they were getting better at getting users to engage over time. So they looked at the users they’d acquired in January and compared how many of them remained engaged after using the service for 1 month, 2 months, 3 months, 4 months etc., with the users they added in February. To see the results more clearly, click on the graph below for an enlarged version.

As you should be able to see from the graph, the fraction of users who remained engaged after 1, 2, 3, 4 months etc. rises over time, so that the results for the February cohort look better than those for January. Similarly, the March cohort results are better then Feb, the April cohort better than March and so on.

We’ll follow the four step approach to cohort analysis outlined in the previous blog post, namely:

1. Define your business question
2. Work out the most appropriate metric to measure
3. Define your cohort
4. Perform the analysis

1. The business question: Are we getting better at getting user’s to engage over time? We are clear on the question to be answered.

2. The metric we want to examine: For each cohort, we want to examine “engagement”. For this specific example, we’ll take the number of actions per user per month. In the Twitter example, they looked at the percentage of users who remained active after X months. This is a good measure of how good Twitter are at facilitating a “minimum” level of engagement. Our measure, by contrast, will better reflect the difference between users who only engage occasionally with those that use the service heavily, but do a less good job of indicating what percentage of users do not engage at all. (Which is more appropriate depends on the type of service we are looking at, but in reality, most companies should be looking at both types of measure.)

Remember: for each cohort, we will want to plot the engagement level for the first month of use versus engagement level for the second month of use, versus engagement level for the third month of use etc.

Our HiveQL query to measure engagement then looks like this:

/* HiveQL */
CREATE TABLE `engagement_by_user` (
user_id string,
mn string,
engagement_per_month int
);

INSERT OVERWRITE TABLE `engagement_by_user`
SELECT
`user_id`,
substr(`dt`,1,7) AS mn,
count(tm)
FROM spconsolidated
GROUP BY `user_id`, substr(`dt`,1,7);

Unlike SQL, HiveQL does not have date processing functionality, which means that we have to use HiveQL’s string processing functions to group the results by year and month. (Note: I have since realised this is wrong – Hive DOES have date functionality, which can be used to rewrite the above query more elegantly… Nonetheless, the above query will still work )

The resulting table looks like this:

3. Cohort definition: We want to know if we are getting better over time at getting users to engage. So we will define each cohort by the month in which the user first starts using the service. The query for categorising each customer by cohort in HiveQL is then very simple:

/* HiveQL */
CREATE TABLE `visitors_by_cohort` (
cohort string,
user_id string
);

INSERT OVERWRITE TABLE `visitors_by_cohort`
SELECT
substr(MIN(`dt`),1,7) as `cohort`,
`user_id`
FROM spconsolidated
GROUP BY `user_id`;

The resulting table looks like this:

Perform the analysis. We simply combine the two queries above to aggregate our results by cohort, comparing the average engagement level between cohorts. This is illustrated schematically below:

The required query is straightforward to write in HiveQL:

/* HiveQL */
CREATE EXTERNAL TABLE `cohort_analysis` (
`cohort` string,
`month` int,
`average_engagement` double
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 's3n://{{your-snowplow-bucket-name}}/analysistables/cohortanalysis' ;

INSERT OVERWRITE TABLE `cohort_analysis`
SELECT
`cohort`,
(substr(`mn`,1,4))*12 + (substr(`mn`,6,2)) - (substr(`cohort`,1,4))*12 + (substr(`cohort`,6,2))+1 AS `month`,
AVG(`engagement_per_month`)
FROM `visitors_by_cohort`
JOIN `engagement_by_user`
ON `engagement_by_user`.`user_id` = `visitors_by_cohort`.user_id
GROUP BY `cohort`, (substr(`mn`,1,4))*12 + (substr(`mn`,6,2)) - (substr(`cohort`,1,4))*12 + (substr(`cohort`,6,2))+1;

The small complexity introduced in Hive is calculating the difference in month between the cohort date (i.e. the date when the user started using the service) and the month the calculation is being performed for. In Hive, we subtract one date from another, multiplying the number of years by 12 before adding on the number of months. We then add on a single month, so that the initial value (i.e. for the first month of use) is 1 instead of 0.

Note also that we make this final table an external table, and save the results back to S3. The results data set is small and easy to plot even in Excel.

And here is what our final cohort analysis looks like:

Et voila! Cohort analysis using web analytics data in 3 easy steps

Update!

Since writing this post, the SnowPlow website has gone live, including a detailed page describing how to perform cohort analyses with SnowPlow.

If you would like help performing cohort analyses like these, or would like to hear more about the benefits of SnowPlow for your web business, please visit the SnowPlow website, or contact us.