Ravindra BagaleCourses & study guides मराठी Track your progress

21. Real-World Project Briefs

21.8 Customer Repeat and Cohort Table

By Last updated:

The ask. Ruhi Bagale wants to know if customers come back. From the order list she needs each customer's first month, order count, a repeat flag, the repeat rate, and a small cohort table: of the customers who started in a month, how many ordered in each later month.

Sheet Orders, one row per order (A: Customer, B: Month as a real date, the 1st of the month). Fictional, Sep to Nov 2026:

Customer Months with an order
Shraddha Bagale Sep, Oct, Nov
Ruhi Bagale Sep, Nov
Ravindra Bagale Sep
Shahrukh Sep, Oct
Amir Oct, Nov
Salman Oct
Zoya Oct, Nov
Ravina Nov

(14 order rows in total.)

What to deliver (one under the other):

  1. Put the customers once in a Customers sheet with =SORT(UNIQUE(Orders!A2:A15)).
  2. First month: MINIFS on the month column for that customer.
  3. Order count: COUNTIF on the customer column.
  4. Repeat: "Yes" when the order count is more than 1.
  5. Repeat rate = repeat customers ÷ all customers.
  6. Cohort table: rows = first month, columns = order month, cell = number of those customers who ordered in that month (COUNTIFS on a helper column).

Key formulas (row 2; copy down or let the Table fill them):

Customers!A2: =SORT(UNIQUE(Orders!A2:A15))
B2: =MINIFS(Orders!$B$2:$B$15,Orders!$A$2:$A$15,A2)
C2: =COUNTIF(Orders!$A$2:$A$15,A2)
D2: =IF(C2>1,"Yes","No")
Repeat rate: =COUNTIF(D2:D9,"Yes")/COUNTA(A2:A9)
Orders!C2 (first month helper): =XLOOKUP(A2,Customers!$A$2:$A$9,Customers!$B$2:$B$9)
Cohort cell: =COUNTIFS(Orders!$C$2:$C$15,$A12,Orders!$B$2:$B$15,B$11)

Expected result (check your numbers):

Customer First month Orders Repeat
Amir Oct 2 Yes
Ravina Nov 1 No
Ravindra Bagale Sep 1 No
Ruhi Bagale Sep 2 Yes
Salman Oct 1 No
Shahrukh Sep 2 Yes
Shraddha Bagale Sep 3 Yes
Zoya Oct 2 Yes

Repeat customers 5 of 8 = 62.5%.

Cohort table (customers who ordered)

First month Customers Sep Oct Nov
Sep 4 4 2 2
Oct 3 0 3 2
Nov 1 0 0 1

Read the first row: 4 customers started in Sep; 2 of them ordered again in Oct and 2 in Nov. Cells before the first month are always 0; grey them out. In this file each customer has at most one order per month, so counting order rows gives the same answer as counting customers.

Checks before you send it:

  1. The month column holds real dates (1st of the month), formatted mmm.
  2. The cohort counts customers, not orders.
  3. If a customer can order twice in one month, count distinct customers: =IFERROR(ROWS(UNIQUE(FILTER(Orders!$A$2:$A$15,(Orders!$C$2:$C$15=$A12)*(Orders!$B$2:$B$15=B$11)))),0).
  4. The repeat rate uses customers as the denominator.

Ravindra Bagale's Tip

Count customers in a cohort, not orders. One loyal customer with ten orders must not look like ten people coming back.

Loading practice tasks...

  1. Total parcels (Easy): Add up Parcels in D2:D11.
  2. Average cost (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail (Easy): How many rows have Carrier "Royal Mail"?
  4. Largest parcels (Easy): The largest value in D2:D11.
  5. Parcels for Royal Mail (Medium): Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Bristol (Medium): Total of D2:D11 where B is "Royal Mail" and C is "Bristol".
  7. Best in Royal Mail (Medium): Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Aarav Shah (Medium): The Parcels for "Aarav Shah" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Royal Mail (Hard): Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →