The RJMetrics Dashboard is a powerful analytics platform that allows users to dissect their data in countless ways from dozens of perspectives. This post focuses on one of our favorite ways to slice data: cohort analysis.

Cohort analysis has been used by statisticians for decades (most prominently in the fields of medicine and finance). However, recent advancements in data collection and processing power have made cohort analysis a viable technique for online businesses to study customer loyalty trends, predict future revenue, and monitor churn.

Major tech players including investor Josh Kopelman and entrepreneur Eric Ries have heralded cohort analysis as a preferred analysis technique. Cohort analysis is heavily used by venture investors and consulting shops to quantify the value of a company’s existing customer base.

In this article, we explain what a cohort analysis is, why you should use it, and how to conduct a cohort analysis on your company’s data using RJMetrics‘ newly revamped cohort analysis builder.

Performing Cohort Analysis Using RJMetrics

The video above shows how easy it is to conduct a cohort analysis in RJMetrics. Read on for more information about cohort analysis, and why using RJMetrics to conduct cohort analysis can help your company save money and drive growth.

What is Cohort Analysis?

Sometimes called static pool analysis, cohort analysis can be broadly defined as the view of how specific, unchanging groups of customers behave over the same periods in their respective customer life-cycles.

The most popular cohort analysis (and the one we will demonstrate here today) involves segmenting customer groups based on a “join date.” This might be the date a given customer signed up for your website or the date they made their first purchase. As a general rule, it’s the timestamp of their first relevant interaction with your business. The year, quarter, month, or week of that date then becomes the user’s “cohort,” meaning each cohort is the set of users who joined in that same time period.

For example, if you are an e-commerce business and you choose to group cohorts by month, the “January 2009” cohort contains every customer who made their first purchase in January 2009. By design, no single customer can exist in more than one cohort, and a customer’s cohort will never change.

In a cohort analysis, we compare how members of different cohorts behave over time. This is typically visualized using a multi-series line chart that displays each cohort as its own line and shows “time since join date” on the x-axis. Note that the time periods associated with each cohort’s data are different. For example, the “Month 3” data point for the January 2009 cohort is their sales data from March 2009, whereas the same data point for the July 2007 cohort is their sales data from September 2007. Naturally, we have more data points for older cohorts, so their lines will be longer than newer cohorts (or we can cut their lines off after a specific amount of time).

Below, we show a graph of the raw data from a cohort analysis of the fictitious e-commerce company Vandelay Industries:

What a mess! It’s hard to extract any value out of something this jumbled, so we’ll have to whittle down the cohort data set to get what we’re looking for. First, however, we need to decide what we’re looking for in the first place. One of the best uses of cohort analysis is to monitor whether customers you have added recently are as “valuable” as those customers you added in the past.

Customer “value” is directly tied to the amount of revenue a customer generates over their lifetime (or their engagement over time in ad-driven businesses), which is directly tied to repeat purchase behavior. If we can look at cohorts from the past and compare their repeat purchase behavior in some initial time period against newer cohorts, we’ll have a pretty clear picture of how the customer set is evolving in terms of expected lifetime value. This information plays a valuable role in many business decisions, including customer acquisition cost thresholds.

Let’s take a few steps to extract this information from the data above.

Cohort Size

We want to be sure that each cohort contains enough customers to constitute a representative sample. Depending on your repeat purchase rates, the magic number can fluctuate considerably, but as a general rule each cohort should contain at least several dozen customers. To be safe with our data set, we’ll use quarterly cohorts instead of monthly cohorts. (Increasing or decreasing the size of the cohort’s time period obviously increases or decreases the number of customers in each cohort.) Here’s the quarterly picture:

Now we’re getting somewhere. Let’s proceed by further reducing the number of cohorts.

Number of Cohorts

Since we’re interested in how cohorts behave over the lifetime of this business, it’s not really necessary that we show every single cohort. To avoid clutter, we’re best served by only showing one cohort per year (evenly spaced out). Let’s take a look at only the Q1 cohort from each year since 2005:

This is much better– now we’re looking at a manageable number of cohorts. But, there’s much more we can do.

Relative (Percentage) Comparisons

From 2005 to 2009, Vandelay Industries grew considerably. If you look at the “Quarter 1? sales of Q1 2005 as compared to Q1 2009, you’ll notice that the latter generated over 10x as much revenue. Comparing repeat purchase behavior between these two groups on an absolute dollars basis could be very misleading, since the 2009 cohort is clearly much larger. What makes more sense is viewing the data on a percentage basis. Specifically, the quarterly revenue generated by each cohort as a percentage of its initial purchase amount:

This provides a much clearer, apples-to-apples comparison. You may notice in the chart above that the “Quarter 1? data point for each cohort is 100%. This happens by design, since each cohort obviously spends 100% of their first quarter’s sales in their first quarter.

Hide First Data Point

To free up some flexibility on the Y-axis, we can hide the first data point and only look at the repeat behavior in subsequent quarters. Since that first data point is 100% for every cohort, we’re not losing any information:

We’ve come a long way from our first chart and now we’re in a position to draw some conclusions from the data!

Conclusions

The chart above (and its underlying data) can lead us to some very telling conclusions about our customer set. Here are a few offhand observations:

  • Generally, new customer cohorts follow a predictably-shaped pattern of repeat purchasing activity, and the chart above provides us with a high degree of confidence about the expected revenue from a given cohort over the first 12 quarters of its lifetime. We can use this data to inform inputs to financial models that predict financial performance.
  • The spikes at quarters 4, 8, and 12 suggest that customers demonstrate strong repeat purchase behavior in Q4 (the holiday season), even if they did not make their first purchases during a holiday season. (Note that Quarters 4, 8, and 12 are always Q4s, since each of these cohorts is a Q1.)
  • Repeat purchase probability actually appears to increase over time. This is highly unusual for any business, as in most cases customer loyalty gradually declines over time. This an extremely interesting trend that places a premium value on the existing customer base.
  • There is no visible decline in repeat purchase likelihood in newer cohorts as opposed to older ones. Newer cohorts are clearly not any “weaker” than their predecessors when it comes to repeat purchase behavior. This answers our original question: Vandelay’s aggressive growth has not come with the cost of a “weaker” new customer base.

Each of these can be more directly quantified and informed using the underlying data from the above chart. It would also be worth looking at different cohorts to rule out any exposure to seasonality that came from our cohort selection.

Performing Cohort Analysis by Hand

Performing a cohort analysis by hand requires, at the very least, three things:

Strong SQL Knowledge

To perform a cohort analysis (and keep the data fresh), someone in your organization will need to routinely run complex queries on your database to extract data grouped by customer cohorts. However, customer cohorts are not stored by default in any database, and customer join dates (or first purchase dates) are rarely stored in the same table as sales or interaction data. This means the queries will involve complex joins and/or the creation of temporary tables.

Once a join date can be associated with each sale, the cohort itself will need to be extracted and grouped. Most database platforms will provide functions like YEAR() and MONTH() to extract the year or month of a given join date, but calculating a quarter or week is a significantly more complex proposition. In almost all cases, these data points will be calculated on-the-fly as part of your query, which means your database will not be able to use indexes to speed up the grouping process. Even with a small company’s data set, this could mean queries that bring your system to a crawl for minutes at a time. Obviously, cohort analysis should never be run on your production database.

Strong Excel Knowledge

If your SQL process went well, the raw cohort data will come out of your database as a tall, skinny table with three columns: cohort, purchase period, and value. This data will need to be migrated into Excel and further manipulated before any value can be extracted. Pivot tables are the weapon of choice for this data, and they allow you to easily view a table of how much revenue was generated by each cohort in each period.

However, recall that we don’t care about how much was bought by each cohort in each calendar period– we care about how much was bought by each cohort in each period relative to their first purchase period (i.e. Month 1, Month 2 rather than January, February). Doing this either requires manipulation of the data before it goes into the pivot table (to calculate a “relative period” field) or actually shifting the data appropriately by hand after it comes out of the pivot table.

When you’re done with all that, don’t forget to augment the data used for charting so it shows a ”percentage of first purchase” rather than absolute values.

Time (aka Money)

Conducting a cohort analysis requires that rare breed of super-employee who is both competent in computer science and financial analysis. It is an extremely time consuming process on both fronts, and unfortunately doesn’t get much faster when you do it on a regular basis. Generating and updating a cohort analysis each month can cost the equivalent of thousands of dollars of your team’s time. Bringing in an outside consulting firm to conduct similar analyses could easily cost tens or hundreds of thousands of dollars.

Performing Cohort Analysis Using RJMetrics

As you saw in the video at the beginning of this post, anyone in your organization can do a cohort analysis in minutes using RJMetrics. This can save thousands in labor and provide new insights to help drive business growth and value.

The options for building a cohort analysis in RJMetrics include:

  • Grouping cohorts by weeks, months, quarters, or years
  • Showing any desired cohorts (either by explicit selection, specific ranges, or ranges that automatically shift over time)
  • Showing only the desired number of data points
  • Consolidating as many data periods as desired into each data point on the x-axis (e.g. show months 1-3, 4-6, etc.)
  • Showing by percentage of first data point
  • Hiding the first data point
  • Further restricting cohorts by geography, behaviors, or other attributes
  • Not straining your servers in any way
  • Computing a result in seconds
  • Exporting all underlying data Excel or CSV with one click

Want to learn more? Try out the free demo.

  • LRH

    This is a great explanation of the value and complexity of cohort analysis. CYou clearly demonstrate the value of the RJM process and technology

  • josh

    I’m not entirely sure what the Y axis here represents. What is the quarter 2 percentage in terms of? It seems like perhaps it would be in terms of cohort revenue from the previous period, but obviously that can’t be the case since it would generally be greater than 100% for all future quartes. Can you give me some insight?

  • Jake Stein

    Josh, the Y axis in the charts above represents revenue in a particular quarter as a percentage of revenue in the first quarter for the same cohort. For almost every cohort analysis we’ve done or seen, the subsequent periods show a decline from the first period (less than 100% when taken as a percent of the first period).
    To give you a numerical example, let’s say that Vandelay Industries gained 100 new customers in Q1 2009. Those customers spent an average of $5 per person in Q1, yielding $500 of revenue for the 1st quarter of the Q1 2009 cohort. In Q2 of 2009, only a subset of the 100 customers from the Q1 2009 cohort purchase again, maybe 50 people. Unless the 50 customers who purchase again spend at least twice as much per person ($10), the value for quarter two for the Q1 2009 cohort will be less than 100%.

  • PennVention « The Metric System

    [...] with smart, talented young entrepreneurs like them. We will be helping them out with metrics like cohort analysis, customer retention, and conversion. Congrats to CampusYap and the of the other contestants. [...]

  • Sasha

    awesome song, awesome write up. shame people hear whats on the radio and disregard all rap as mindless thumping. which I admit, most rap on the radio is. RIP BIG.

  • Andrew Sherrard

    so true its about time we looked at hip hop from this perspective, the world has much to learn from these modern day prophetic poets

  • soar

    Ah, one of my favorite biggie songs. Great article.

  • Soxialize

    Great article! Never underestimate those with street smart schooling! :)

  • Isaac Z. Schlueter

    “Number five: never sell no crack where you rest at”
    I think the message in #5 is not that you shouldn’t sell to your family, but rather that you shouldn’t sell to *anyone* out of your home, or else you’ll have crackheads coming to your house, and everyone will know where you live. Pretty soon the cops will be watching you, and it’s a bad scene.

    Without a healthy work-life balance, your business will invade your personal life, and you’ll never have a moment’s peace. I think the takeaway for startups is that, while it’s tempting to work out of your home, make sure that you still draw boundaries. Keep work time and personal time separate, so that you don’t get overwhelmed and burnt out. A lot of founders find it easier to work out of cafes or cube-sharing offices for this reason.

  • Robert J. Moore

    Awesome! That’s a way better angle on that one, thanks for chiming in!

  • daveconrey

    Lesson #11 should be “Go back to school and study grammar.”
    I get the point, but about half of these statements are double-negatives, implying the exact opposite of what is intended.

  • Mark suman

    I should have shared this song in my “Creating New Ventures” class I took last year.
    I love Rap/Hip-Hop when the songs actually mean something. Today’s rap music (on the radio) is missing there. There are many great rappers still out there, but they don’t get any air-time.

    Bring back Biggie and 2pac.

  • marissa

    so so great. classic. thoroughly enjoyed this

  • Ciaran

    I love this, and have been saying this to colleagues and friends for years! I also interpret number 5 as expressing the importance of maintaining a healthy home/ work balance. There is a lot of potential for entrepreneurs to burn themselves out and setting this boundary helps to mitigate that risk.

  • Ryan Graves

    1) Great rules to keep in mind through the startup process
    2) Brilliant use of Biggie lyrics…
    Nice post, I’ll be checkin back often after this gem.

    Cheers,
    Ryan

  • Jason Wizard

    This post was pretty ill!
    Respect to ya dude!
    I loved it!

  • Brent

    Stick to the Yanni CD’s and cassettes daveconrey.
    This is a brilliant post. I will be keeping up with this blog from here on out

  • Guerilla Billionaire

    Nice list and that reminds of this classic: http://www.youtube.com/watch?v=Tqmdph5DPHA

  • JohnBlack

    Great post but you interpreted commandment five wrong as pointed out by Isaac Z. Schlueter. Also i disagree with you assesment on numbero uno.
    In their paper titled “bringing the future forward: The effect of disclosure on the Returns-Earnings Relation”, Lundholm and Myers (2002) hypothesize that the disclosure activity of a firm can “bring the future forward” by revealing information in the current period that changes expectations about future earnings.

    In short, the more information an organization discloses on its financial state the more their current returns will reflect future earnings.

    Lundholm and Myers also find that changes in a firm’s disclosure activity are positively related to changes in the amount of future earnings news reflected in current returns. Meaning that, high disclosure activity coincides with a high amount of future earnings news reflected in the current returns. According to this argument, disclosures affect the ability of investors to predict future earnings.

    So as you might want to hide your financial details from your competitors, companies who have listed stock actually benefit from full disclosure.

  • Jason

    #9 Lyric is: “if you ain’t gettin’ BAGGED stay [away] from police”
    This is a clever and smart write up.

  • adam

    “Number eight: never keep no weight on you” should be interpreted more closely. I suggest something like: here biggie is commenting on the importance of legal security. Making sure that you are covered in case of legal problems is of utmost importance. And if you are doing anything cuestoinably leagal, make sure the paper trail leads to your underlings.

  • diamondTearz

    Very Clever write up! Now I gotta go pull up that Ready 2 die album and study it closer!

  • david

    nice article. While some of the points are being argued (I am not opposed to them), I believe that this illustrates, that at its core, business is business.
    They all operate on the same principle regardless of scale, industry, or legality.

    Hmmm…could be a great illustrative example to students. An artist students likely listen to, and an adult validating this artists’ lyrics as valid lessons & knowledge. Sounds like a good ‘bridging the gap’ tactic.

  • mattsmedia

    This is for really a straight up down to the core awesome post. Keepin it real hommie.

  • Mike Stenger

    love this post! These are all some really great tips from a really great hip hop artist.

  • ziombi

    point of correction: ‘never sell crack where you rest at.i don’t care if they want an ounce tell ‘em bounce..’ this one is more in support of crack commandment 7.ie, you’re not supposed to bring your customers home…

  • chris

    East Coast! Awesome article.

  • alexandrae

    Great, i always looked for the meaning of those rules……thx for u all
    Biggie was a prophet….the only regrets i’ve, it’s only tha ,when he alive nobody consider……that

  • importilla

    Great ..i have always sort for the other side of the meaning of most rap lyrics. thanks

  • kevin

    Number 5 “never sell no crack where you rest at”, could also be interpreted as a away to stay under the radar of the police if people keep showing up to your house to get a fix there is a much more likely chance of your house getting raided then getting sent to jail.

  • David Comdico

    Further proof why lack of ethics in business is destroying the US. Here is a man that was creative and used his talents not only to destroy his community via the crack epidemic but taught others how to destroy their own – and then died senselessly.
    This is not in the least bit amusing.

  • jason

    This was done wrong. Most of what you wrote isn’t what was being said. You should probably consult someone about what it means, because you clearly didn’t get it.

  • flashynista

    great break down. true arist always include truth in their lyrics that can have a broad application. good stuff

  • briana s

    Great read and nice break down of the rules! Some good insight to keep in mind. Definitely listening to this song later today.

  • http://schriftrolle.de Sven Doering

    Greetings from Germany,

    just one thing, that struck me, while starting this post. Your first link [cohort analysis] end in a 404… Just FYI ;-)

    Everything else, I have to read first, but it looks promising…