In the world of Australian business, it is much cheaper to keep a customer than to acquire a new one. Cohort analysis is the 'secret sauce' of growth hacking because it allows you to see exactly when and why customers drop off, rather than looking at misleading vanity metrics like total user counts.
By grouping users based on when they first interacted with your brand, you can identify if your latest marketing campaign or product update actually improved long-term loyalty or just provided a temporary spike. Here is how to build a robust system to track this.
Prerequisites
Before you begin, ensure you have the following:- A Transactional Database or CRM: Access to data containing a unique User ID and their 'Join Date' (or first purchase date).
- Event Tracking: Data on subsequent actions (purchases, logins, or app opens).
- Analysis Tool: Microsoft Excel, Google Sheets, or a BI tool like Tableau or Power BI.
- Your ABN-linked Business Data: If you are a service-based business, ensure your client records are cleaned and deduplicated.
---
Step 1: Define Your Cohort Type
The most common cohort is 'Acquisition Cohort', which groups users by the month they started. However, you might also choose 'Behavioral Cohorts' (e.g., users who used a specific discount code vs. those who didn't). For this guide, we will focus on Monthly Acquisition Cohorts.Step 2: Extract Your Raw Data
You need a simple table export from your CRM or POS system. You require three columns:- User ID: A unique identifier for the customer.
- Start Date: The date of their first transaction or sign-up.
- Activity Date: The date of every subsequent transaction or interaction.
Screenshot Description: You should see a spreadsheet where User #101 appears multiple times—once for their sign-up in January and again for every purchase they made in February, March, and April.
Step 3: Normalise the Dates
To compare cohorts fairly, you need to turn specific dates into 'Cohort Months'. In Excel or Google Sheets, use theEOMONTH or TEXT function to convert a date like '15/03/2023' into 'March 2023'. This ensures everyone who joined in March is grouped together regardless of the specific day.
Step 4: Calculate 'Month Index'
This is the most critical step. You need to calculate the time elapsed between the 'Start Date' and the 'Activity Date'.- Month 0: The month they joined.
- Month 1: The following month.
(Year of Activity - Year of Start) * 12 + (Month of Activity - Month of Start)
Step 5: Create a Pivot Table
Highlight your data and insert a Pivot Table.- Move Cohort Month (Start Date) to the Rows.
- Move Month Index to the Columns.
- Move User ID to the Values area and set it to 'Count Unique' (or 'Distinct Count').
Step 6: Calculate Retention Percentages
Raw numbers are hard to read. Create a duplicate table below your pivot table that calculates the percentage. Formula:(Current Month Users / Month 0 Users) 100.
- Month 0 will always be 100%. If Month 1 shows 30%, it means you lost 70% of those customers within 30 days.
Step 7: Apply Conditional Formatting (The Heatmap)
To make the data actionable, highlight your percentage table and apply 'Conditional Formatting' using a 'Colour Scale'.- Tip: Set high values (100%) to dark green and low values (0%) to white or red.
Screenshot Description: You should now see a 'triangle' shaped table where the top-left is dark green, fading to lighter colours as you move to the right. A vertical 'column' of dark green in a later month indicates a successful re-engagement campaign.
Step 8: Identify the 'Retention Cliff'
Look at your heatmap. Where does the colour drop off most aggressively? For many Aussie Shopify stores, the cliff is between Month 0 and Month 1. If you see a drop from 100% to 15%, your onboarding or initial product experience needs urgent work.Step 9: Segment by Channel
Repeat the process but filter by 'Lead Source' (e.g., Facebook Ads vs. Organic Search). You might find that Facebook leads have high initial volume but 0% retention by Month 3, whereas Organic leads stay for years. This tells you where to spend your marketing budget.Step 10: Automate the Feed
Manual exports are tedious. Use a tool like Zapier or a direct Google Sheets integration with your CRM (like HubSpot or Salesforce) to ensure this data refreshes weekly. This allows you to react to retention drops in real-time.---
Pro Tips for Growth Hacking
- Ignore Month 0: While 100% looks good, focus your energy on the gap between Month 1 and Month 2. This is where 'habit formation' happens.
- The Smile Curve: In world-class businesses, the retention curve eventually flattens out or even ticks back up (Net Negative Churn). If your curve never flattens and eventually hits zero, you don't have a 'leaky bucket'—you have a bucket with no bottom.
- Aussie Seasonality: Take note of January and EOFY (June). Retention often dips in Jan as Australians head to the beach, so don't panic if your cohorts look weaker during the summer holidays.
Common Mistakes to Avoid
- Counting 'Total Users': Never use cumulative totals. It masks the fact that old users are leaving while new ones arrive.
- Inconsistent Definitions: Ensure 'Activity' is defined strictly. Is it a login or a purchase? Mixing these will ruin your data integrity.
- Ignoring Small Sample Sizes: If a cohort only has 5 people, a 20% retention rate is just one person. Don't make major pivot decisions based on tiny cohorts.
Troubleshooting
- The numbers are over 100%: This usually happens if you aren't using 'Unique Count'. Check that you aren't counting the same user twice in a single month.
- Data is missing for recent months: Remember that a cohort for 'Last Month' can only have a 'Month 0' and 'Month 1' value. It's okay for the right side of the triangle to be empty for newer cohorts.
Next Steps
Now that you can see when people leave, you need to find out why.- Conduct 'Exit Surveys' for the specific cohorts that dropped off.
- Implement a 'Win-back' email sequence triggered at the exact month your 'Retention Cliff' occurs.
- If you need help setting up advanced tracking via Google Analytics 4 or server-side tagging, contact the team at Local Marketing Group.
Building a retention system is the difference between a business that struggles to survive and one that scales predictably. Start with your Excel pivot today!