Assignment Overview
In this assignment, you will complete your first business analytics project. You will calculate essential business metrics, analyze marketing campaign performance, and provide data-driven recommendations to improve business outcomes.
Metric Calculations
Calculate CAC, LTV, ROI, conversion rates, and other KPIs
Campaign Analysis
Compare marketing channel performance and identify winners
Recommendations
Provide actionable insights to improve business results
The Scenario
TechGrow SaaS Company
You have just been hired as a Junior Business Analyst at TechGrow, a fast-growing SaaS company that provides project management software. The marketing team has been running campaigns across multiple channels and needs your help:
"We've been spending a lot on marketing across Google Ads, Facebook, LinkedIn, and content marketing. We need to know which channels are actually profitable and where we should focus our budget. Can you analyze our data and tell us what's working and what's not?"
Your Task
Create a comprehensive Excel or Google Sheets analysis that calculates key business metrics, compares marketing channel performance, and provides clear recommendations. Your analysis should be professional enough to present to the marketing director.
The Dataset
You have been provided with the following business data from the last quarter:
Customer & Revenue Data
| Metric | Value |
|---|---|
| Total Customers (Start of Quarter) | 1,200 |
| New Customers Acquired | 450 |
| Customers Lost (Churned) | 85 |
| Average Subscription Price | $89/month |
| Average Customer Lifespan | 18 months |
| Total Revenue This Quarter | $360,000 |
Marketing Campaign Data
| Channel | Spend | New Customers | Revenue Generated |
|---|---|---|---|
| Google Ads | $45,000 | 180 | $145,000 |
| Facebook Ads | $28,000 | 95 | $76,000 |
| LinkedIn Ads | $35,000 | 75 | $88,000 |
| Content Marketing | $18,000 | 100 | $95,000 |
Sales Funnel Data
| Stage | Count |
|---|---|
| Website Visitors | 125,000 |
| Free Trial Sign-ups | 6,250 |
| Paid Customers | 450 |
Requirements
Your Excel/Google Sheets analysis must include all of the following sections with proper calculations and clear explanations:
Cover Sheet
Create a title sheet with your name, submission date, company name (TechGrow), and a brief executive summary (2-3 sentences) explaining what this analysis covers.
Customer Metrics Sheet
Calculate the following customer metrics:
- Churn Rate: (Customers Lost / Total Customers at Start) × 100
- Customer Retention Rate: 100 - Churn Rate
- Net Customer Growth: New Customers - Churned Customers
- Total Customers (End of Quarter): Starting + New - Churned
Include formulas and brief interpretation of each metric.
Revenue Metrics Sheet
Calculate the following revenue metrics:
- Customer Lifetime Value (LTV): Average Subscription Price × Average Lifespan
- Average Revenue Per Customer (ARPC): Total Revenue / Total Customers
- Projected Annual Revenue: (Monthly Subscription × Active Customers) × 12
Marketing Channel Analysis Sheet
For EACH marketing channel, calculate:
- Customer Acquisition Cost (CAC): Spend / New Customers
- ROI: ((Revenue - Spend) / Spend) × 100
- Profit: Revenue - Spend
- LTV:CAC Ratio: Customer Lifetime Value / CAC
Create a comparison table showing all channels side by side.
Funnel Conversion Analysis Sheet
Calculate conversion rates for each stage:
- Visitor to Trial Conversion: (Trial Sign-ups / Visitors) × 100
- Trial to Paid Conversion: (Paid Customers / Trial Sign-ups) × 100
- Overall Conversion Rate: (Paid Customers / Visitors) × 100
Insights & Recommendations Sheet
Provide clear, actionable recommendations based on your analysis:
- Which marketing channel should receive MORE budget? Why?
- Which channel should receive LESS or be eliminated? Why?
- What is the biggest opportunity for growth?
- What is the biggest risk to the business?
- List 3-5 specific action items for the marketing team
Submission
Create a public GitHub repository with the exact name shown below:
Required Repository Name
business-metrics-analysis
Required Files
business-metrics-analysis/
├── metrics_analysis.xlsx # Your Excel workbook (or .gsheet link)
├── screenshots/ # Folder with screenshots of each sheet
│ ├── customer_metrics.png
│ ├── revenue_metrics.png
│ ├── channel_analysis.png
│ ├── funnel_analysis.png
│ └── recommendations.png
└── README.md # REQUIRED - see contents belowREADME.md Must Include:
- Your full name and submission date
- Executive Summary: 2-3 sentences on key findings
- Top 3 Recommendations: Your most important action items
- Tools Used: Excel/Google Sheets, formulas applied
- Link to spreadsheet: Google Sheets link or instructions to view Excel file
Do Include
- All required calculation sheets
- Clear formulas (not just values)
- Screenshots of each sheet
- Professional formatting and labels
- README.md with all sections
Do Not Include
- Hard-coded numbers (use formulas)
- Messy or unlabeled sheets
- Missing calculations
- Vague recommendations
- Private repository
Enter your GitHub username - we'll verify your repository automatically
Grading Rubric
Your assignment will be graded on the following criteria:
| Criteria | Points | Description |
|---|---|---|
| File Organization | 10 | Proper folder structure, all required files present |
| Customer Metrics | 15 | Correct calculations for churn, retention, growth |
| Revenue Metrics | 15 | Accurate LTV, ARPC, revenue projections |
| Marketing Analysis | 25 | All channel metrics calculated (CAC, ROI, Profit, LTV:CAC) |
| Funnel Analysis | 10 | Conversion rates at each funnel stage |
| Insights & Recommendations | 20 | Clear, actionable, data-driven recommendations |
| Presentation Quality | 5 | Professional formatting, clear labels, documentation |
| Total | 100 |
Ready to Submit?
Make sure you have completed all requirements and reviewed the grading rubric above.
Submit Your AssignmentWhat You Will Practice
Business Metrics Mastery
Calculate and interpret essential KPIs including CAC, LTV, ROI, churn rate, and conversion rates
Data Analysis
Compare performance across multiple marketing channels and identify the most profitable opportunities
Strategic Thinking
Transform numbers into actionable business recommendations that drive real results
Spreadsheet Skills
Build professional, well-organized analyses using Excel or Google Sheets formulas
Pro Tips
Getting Started
- Read all requirements before starting
- Set up your sheet structure first
- Use formulas, not hard-coded values
- Test each calculation as you go
Spreadsheet Best Practices
- Label every column and row clearly
- Use bold headers and formatting
- Add comments to explain complex formulas
- Keep each analysis on its own sheet
Analysis Tips
- Compare channels side by side in a table
- Look for patterns (highest ROI, lowest CAC)
- Consider both percentage and dollar amounts
- Think about scalability and sustainability
Common Mistakes
- Using values instead of formulas
- Not showing your calculations
- Vague recommendations like "improve marketing"
- Forgetting to make repository public