How to Automate Weekly Cohort Analysis in Soffi.io

A Detailed Guide Without Any Magic

---

1. Why Bother with Cohorts — and Why Doing It Manually Hurts

Analyst Katya spent all spring following the same ritual: every Friday, she opened Mixpanel, downloaded a CSV file, cleaned up junk columns, set up formulas in Excel, and colored the cells. It looked simple, but in practice, any typo would turn the table into a mess, and two hours before sending a report to investors, she’d have to start from scratch. The result — sleepless nights and nervous coffee.

If this sounds familiar, Soffi.io takes the manual routine off your hands. Set up the task once — and every Friday, a ready-made retention heatmap report lands in Slack, bug-free and with no late-night recalculations.

---

2. What You’ll Need

---

3. Step-by-Step

Step 0. Prepare the Source Table

In Google Sheets, create a sheet called raw_events with three columns:
user_id | signup_date | event_date
Export data from Mixpanel for the last 8 weeks and paste them here. That’s it — you won’t need to manually touch this table again.

Step 1. Create a New Task in Soffi.io

1. 1.

   Name: “Weekly Cohort Retention”.

2. 2.

   Data source: connect the relevant Google Sheets and select the raw_events sheet.

3. 3.

   Schedule: every Thursday at 23:00 (so the report will be ready for the team on Friday morning).

Step 2. Formulate the Request for Soffi-bot

			Split users into weekly cohorts by signup_date (last 8 weeks).
For each cohort, calculate the share who returned in weeks 1, 2, 4, and 8.
Build a table like this: rows are cohorts, columns are weeks.
Color code: >40% — green, <15% — red.
Add output: which cohort is best, which is worst, overall trend.
		

Pro tip: if your company has different “good/bad” thresholds, change the 40% and 15% values directly in the query.

Step 3. Set Up Output Format

• •

  Format: Google Doc + PNG screenshot of the heatmap (Soffi.io generates both).

• •

  Recipients: Slack channel #metrics and investor’s e-mail (if needed).

• •

  File name: Cohorts_{{date}}.pdf — so it’s always clear the report is current.

Step 4. Test

Run the task manually once. Check that the right dates are clustered and the cells are colored correctly. If everything is fine — put the task on a cron schedule.

---

4. What You Get as a Result

• •

  4–5 hours saved each week. Now Katya spends this time on hypotheses, not copy-pasting.

• •

  Error risk → 0. Formulas stay put, dates don’t get mixed up, colors aren’t forgotten.

• •

  Timely actions. As soon as retention falls below target 25–30%, the team sees it right away and launches A/B tests, no need to wait for month-end.

• •

  No red eyes before investors. The metrics e-mail goes out exactly at 07:00 Friday, while everyone’s still fresh.

---

5. Frequently Asked Questions

Q: Can I calculate cohorts by month instead of week?
A: Yes. Change the phrase “weekly cohorts” to “monthly” and adjust thresholds.

Q: How can I add more metrics (DAU/WAU, average check)?
A: Just add them in the request after the heatmap: “…and add a DAU/WAU chart for the same 8 weeks.”

Q: My data is in BigQuery, not Google Sheets.
A: Connect BigQuery as the source — Soffi.io supports native authentication, and the query structure is the same.

---

6. Final Checklist Before Launch

• •

  Google Sheets has up-to-date user_id | signup_date | event_date.

• •

  The task is activated in Soffi.io and linked to the right sheet.

• •

  The server’s time zone matches your team’s time zone.

• •

  Slack bot is invited to the #metrics channel.

• •

  The test report looks correct.

---

Final Note

Cohort analysis is the best indicator of your product’s “stickiness”, but when each calculation takes half a day, the metric quickly becomes just a formality. By automating the process with Soffi.io, you get the same analytics without human error and with “just in time” delivery. And Katya finally gets to close her laptop before midnight.