Revenue Analysis Report Generator

-- Monthly Recurring Revenue (MRR) Trend
SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(CASE WHEN subscription_type = 'recurring' THEN amount ELSE 0 END) as mrr,
    SUM(amount) as total_revenue,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(amount) / COUNT(DISTINCT customer_id) as revenue_per_customer
FROM revenue_transactions 
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Revenue Growth Analysis
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as revenue
    FROM revenue_transactions
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    ((revenue - LAG(revenue) OVER (ORDER BY month)) / 
     LAG(revenue) OVER (ORDER BY month)) * 100 as growth_rate_pct
FROM monthly_revenue
ORDER BY month;

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, timedelta

def analyze_revenue_trends(df):
    """
    Comprehensive revenue trend analysis
    """
    # Calculate key metrics
    df['month'] = pd.to_datetime(df['date']).dt.to_period('M')

    monthly_summary = df.groupby('month').agg({
        'revenue': ['sum', 'mean', 'count'],
        'customer_id': 'nunique'
    }).round(2)

    # Growth calculations
    monthly_revenue = monthly_summary[('revenue', 'sum')]
    growth_rates = monthly_revenue.pct_change() * 100

    # Revenue concentration analysis
    customer_revenue = df.groupby('customer_id')['revenue'].sum()
    top_20_pct = customer_revenue.quantile(0.8)
    concentration_ratio = customer_revenue[customer_revenue >= top_20_pct].sum() / customer_revenue.sum()

    return {
        'monthly_summary': monthly_summary,
        'growth_rates': growth_rates,
        'concentration_ratio': concentration_ratio,
        'avg_monthly_growth': growth_rates.mean()
    }

def create_revenue_dashboard(df):
    """
    Generate comprehensive revenue visualizations
    """
    fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, figsize=(15, 10))

    # Monthly revenue trend
    monthly_rev = df.groupby('month')['revenue'].sum()
    ax1.plot(monthly_rev.index.astype(str), monthly_rev.values, marker='o')
    ax1.set_title('Monthly Revenue Trend')
    ax1.tick_params(axis='x', rotation=45)

    # Revenue by product/segment
    segment_rev = df.groupby('segment')['revenue'].sum()
    ax2.pie(segment_rev.values, labels=segment_rev.index, autopct='%1.1f%%')
    ax2.set_title('Revenue Distribution by Segment')

    # Customer value distribution
    customer_values = df.groupby('customer_id')['revenue'].sum()
    ax3.hist(customer_values, bins=30, alpha=0.7)
    ax3.set_title('Customer Revenue Distribution')
    ax3.set_xlabel('Customer Lifetime Value')

    # Growth rate trend
    growth_rates = monthly_rev.pct_change() * 100
    ax4.bar(range(len(growth_rates)), growth_rates.values)
    ax4.set_title('Month-over-Month Growth Rate')
    ax4.axhline(y=0, color='r', linestyle='--')

    plt.tight_layout()
    return fig

| Segment | Current Revenue | Growth Rate | % of Total | Key Drivers |
|---------|----------------|-------------|------------|-----------|
| Enterprise | $2.5M | +15.3% | 45% | New product adoption |
| SMB | $1.8M | +8.7% | 32% | Geographic expansion |
| Consumer | $1.3M | -2.1% | 23% | Market saturation |

def cohort_revenue_analysis(df):
    df['order_month'] = df['order_date'].dt.to_period('M')
    df['customer_first_order'] = df.groupby('customer_id')['order_date'].transform('min').dt.to_period('M')

    # Create cohort table
    cohort_data = df.groupby(['customer_first_order', 'order_month'])['revenue'].sum().reset_index()
    cohort_table = cohort_data.pivot(index='customer_first_order', 
                                   columns='order_month', 
                                   values='revenue').fillna(0)

    return cohort_table