Skip to main content
The table includes the metric for each cohort over time as well as the size of each cohort.

​
Setup

To build this chart you will need to structure your query with the cohorts as the rows and the time passed as the pivot. A calculation is used to pull the cohort size out of the pivot. Additional calculations are necessary to figure out how to color each cell. We’ll also need the column totals enabled. The following table explains each field used in the example, including the calculation formulas.
ColNameDescription or formulaPurpose
AMonthly cohortquery fieldA row for each cohort, sorted with the oldest at the top
BUsers Countquery fieldCount of users in the cohort (inside pivot)
CCustomers=PIVOTOFFSET(B1, 0, 0)First column of users count is the size of the cohort
DSalesquery fieldTotal sales for the cohort (inside pivot)
ESales (row max)=MAX(PIVOTROW(D1))Maximum sales value for the row - set total calculation to maximum
FSales (row non-zero min)=IFERROR(MINIFS(PIVOTROW(D1), PIVOTROW(D1), “>0”), 0)Minimum non-zero value for the row. If row is all zeros, will set to 0. Set total calculation to minimum
Gpct=(D1 - F_TOTAL) / (E_TOTAL - F_TOTAL)how close to the max value is the current cell - will be a value between 0 and 1 where 0 is the overall min sales value, 1 is the overall max value
Hbucket=IF(G1 <= 0.167, “bucket1”, IF(G1 <= 0.333, “bucket2”, IF(G1 <= 0.5, “bucket3”, IF(G1 <= 0.667, “bucket4”, IF(G1 <= 0.833, “bucket5”, “bucket6”)))))string to identify bin for coloring
Don’t forget to enable the column totals and adjust the total calculation as follows:
  • column D - Sales - to be maximum
  • column E - Sales (row max) - to be maximum
  • column F - Sales (row non-zero min) - to be minimum

​
Example code

<style>
.cohort-table {
  --header-color: var(--color-foreground2);
  --total-color: var(--color-border1);
  --empty-color: var(--color-foreground2);
  --cell-radius: 8px;
  border-spacing: 2px;
  width: 100%;

  & th {
    text-align: right;
    padding: 4px 8px;
    font-weight: 500;
  }

  & th.left-align {
    text-align: left;
  }

  & td {
    text-align: right;
    padding: 4px 8px;
  }

  & tbody th {
    background: var(--header-color);
    border-radius: var(--cell-radius);
    font-weight: normal;
  }

  & tfoot th {
    background: var(--total-color);
    border-radius: var(--cell-radius);
    font-weight: 500;
  }

  & tfoot td {
    background: var(--total-color);
    border-radius: var(--cell-radius);
  }

  & tbody td {
    background: transparent;
    border-radius: var(--cell-radius);
  }

  & tbody td.bucket1 { background: #F0E654ee; color: hsl(219deg 3% 3% / 85%); }
  & tbody td.bucket2 { background: #94D161ee; color: hsl(219deg 3% 3% / 85%); }
  & tbody td.bucket3 { background: #5DB27Fee; color: hsl(219deg 3% 3% / 85%); }
  & tbody td.bucket4 { background: #468C8Dee; color: hsl(180deg 0% 100% / 85%); }
  & tbody td.bucket5 { background: #40628Aee; color: hsl(180deg 0% 100% / 85%); }
  & tbody td.bucket6 { background: #423076ee; color: hsl(180deg 0% 100% / 85%); }

  /* blank out bottom right */
  & tbody {
    & tr:nth-of-type(2) td:nth-child(n+14) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(3) td:nth-child(n+13) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(4) td:nth-child(n+12) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(5) td:nth-child(n+11) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(6) td:nth-child(n+10) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(7) td:nth-child(n+9) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(8) td:nth-child(n+8) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(9) td:nth-child(n+7) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(10) td:nth-child(n+6) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(11) td:nth-child(n+5) { color: var(--empty-color); background: var(--empty-color); }
    & tr:nth-of-type(12) td:nth-child(n+4) { color: var(--empty-color); background: var(--empty-color); }
  }
}
</style>

<table class="cohort-table">
  <thead>
    <tr>
      <th class="left-align">Monthly Cohort</th>
      <th>Customers</th>
      <th>1st Month</th>
      <th>2nd Month</th>
      <th>3rd Month</th>
      <th>4th Month</th>
      <th>5th Month</th>
      <th>6th Month</th>
      <th>7th Month</th>
      <th>8th Month</th>
      <th>9th Month</th>
      <th>10th Month</th>
      <th>11th Month</th>
      <th>12th Month</th>
    </tr>
  </thead>
  <tbody>
    {{#result}}
    <tr>
      <th class="left-align">{{ecomm__users.created_at[month].value}}</th>
      <th>{{calc_1.value_static}}</th>
      <td class="{{0.1st Month.calc_5.raw}}">{{0.1st Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{1.2nd Month.calc_5.raw}}">{{1.2nd Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{2.3rd Month.calc_5.raw}}">{{2.3rd Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{3.4th Month.calc_5.raw}}">{{3.4th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{4.5th Month.calc_5.raw}}">{{4.5th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{5.6th Month.calc_5.raw}}">{{5.6th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{6.7th Month.calc_5.raw}}">{{6.7th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{7.8th Month.calc_5.raw}}">{{7.8th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{8.9th Month.calc_5.raw}}">{{8.9th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{9.10th Month.calc_5.raw}}">{{9.10th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{10.11th Month.calc_5.raw}}">{{10.11th Month.ecomm__order_items.sale_price_sum.value}}</td>
      <td class="{{11.12th Month.calc_5.raw}}">{{11.12th Month.ecomm__order_items.sale_price_sum.value}}</td>
    </tr>
    {{/result}}
  </tbody>
  <tfoot>
    <tr>
      <th class="left-align">Max</th>
      <td>{{result._totals.0.calc_1.value_static}}</td>
      <td>{{result._totals.0.0.1st Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.1.2nd Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.2.3rd Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.3.4th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.4.5th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.5.6th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.6.7th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.7.8th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.8.9th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.9.10th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.10.11th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
      <td>{{result._totals.0.11.12th Month.ecomm__order_items.sale_price_sum.value_static}}</td>
    </tr>
  </tfoot>
</table>