Recipe: Creating cohort reports with a lifetime month

Follow

A cohort is a group of people (users, customers, visitors) usually defined by year and month. Cohort analyses look at how long these people are retained and changes in their activity over time.

For example, if you are an analyst for a phone provider, you might want to predict a customer's phone usage rate and how long customers will use your provider. You can't use calendar months to track results because customer sign up dates differ and the nth month or lifetime month is needed to calculate results.

This tip will instruct you on how to create a cohort report for analyzing different results over time.

Skill level : Beginner

Time required : 15 minutes

Ingredients :
  • Four calculated attributes
  • One result path calculation
  • Two metrics, one color encoded

Creating your report

Before you can begin to create your report, you need to ensure that your dataset includes the two date field attributes below. You will need to use these attributes in your calculations
  • Sign up date: A single date of the earliest contact. This could be registration, sign up, contract subscription, etc. The sign up date field in the examples below is 'registrationdate'.
  • Transaction date: Multiple dates containing the relevant activity. Transaction dates could be for monthly payments, daily usage, etc. The transaction date field in the examples below is 'transactiondate'.

To create your report

  1. Connect to the data source containing your cohort data (see Adding connections ).
  2. In the Query Builder, click the Calculation engine icon.
  3. Select Standard calculated attribute .
  4. Create the following four calculated attributes by typing directly into the formula box.
    • Cohort:
      [YEAR(registrationdate)]+"/"+TO_MONTH_NUMERIC([MONTH(registrationdate)])
    • Month Diff:
      NUMBER(TO_MONTH_NUMERIC([MONTH(transactiondate)]))-NUMBER(TO_MONTH_NUMERIC([MONTH(registrationdate)]))
    • Year Diff:
      NUMBER([YEAR(transactiondate)])-NUMBER([YEAR(registrationdate)])
    • Nth month:
      M"+(NUMBER([Month Diff])+(12*NUMBER([Year Diff]))+1)

      This creates a label where the first month is M1, second M2, third M3, and so on. If you want to start at 0, remove the +1 from your formula.

  5. On Metrics, click the + button to add a new metric.
  6. Select the metric you want to use in your analysis. For example, a phone provider would select a usage metric to track how frequently customers use their phones.

  7. On Columns, click the + button to add a new attribute, then select the Nth month calculated attribute.

  8. On Rows, click the + button to add a new attribute, then select the Cohort attribute.

Your report should now resemble the image below. If you would like to perform other calculations, such as the usage percentage over time against the first month, you can follow the steps in the section below.

Note : If you are connected to a database and would like to perform different cohort calculations, your end result might not be correct. Please contact our support team, if you would like to set up your chart differently.

Customizing your cohort report

This section will instruct you on how to customize your report and create result manipulations to further analyze data.

To customize your report

  1. In the Visualization type menu, select the table chart.
  2. Click the + button to add a second metric to your query. You will need to add a second metric differently depending on whether your analysis measure was imported from your dataset or created using the calculation engine.
      • If your metric was imported from your dataset, add the same metric, but with the SUMX aggregator. You can select a different aggregator by clicking the arrow next to a metric's name. The SUMX aggregator will give you the same results as SUM.
    • If your metric was created in the calculation engine, you will need to duplicate the metric using the same formula. You can then add the duplicated metric to your query.
  3. Click on your second metric.
  4. Click the Color button at the bottom of the menu. Your metric will then become a color encoded metric.
  5. On Metrics, add a new color encoded metric by clicking the color encoded metric icon ( ).
  6. In the Result manipulation menu, select Result path calculation .
  7. To calculate the % usage or retention over time, select the following options for your color encoded metric. For more information on using result path calculations, see Creating result path calculations .
    • From the Pattern drop-down list, select % .
    • From the Path drop-down list, select On columns .
    • From the Reference drop-down list, select First element .

  8. Click Apply when finished.
  9. In the Chart configuration menu, select Colors > Color encoding to customize the color gradient for your metric.

You have now successfully created your cohorts report.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk