Every week, the same ritual. Open the CRM export. Open the ads platform export. Open the website analytics export. Copy everything into a master spreadsheet. Clean the dates. Fix the formatting. Recalculate the formulas. Build the summary table. Export to PDF. Send.

Three hours. Every week. Without fail.

I automated this with Python. Now it takes about four minutes to run the script, verify the output, and send the report. Here’s how.

The core problem

The issue with manual reporting is not complexity — it’s repetition. The same columns need the same transformations every single time. That’s exactly what Python is good at.

The stack I used:

• pandas — for loading, cleaning, and transforming the data
• openpyxl — for writing formatted Excel output
• Google Sheets API — for pulling source data directly (no manual export)

The script structure

The script does four things:

1. Pull data from each source (CRM, ads, analytics)
2. Clean and normalize the data (consistent date formats, column names, types)
3. Merge everything into one unified view
4. Output a formatted report

import pandas as pd

def load_crm_data(filepath):
    df = pd.read_csv(filepath)
    df['date'] = pd.to_datetime(df['date'])
    df = df.rename(columns={'deal_value': 'revenue', 'close_date': 'date'})
    return df[['date', 'revenue', 'source', 'stage']]

def load_ads_data(filepath):
    df = pd.read_csv(filepath)
    df['date'] = pd.to_datetime(df['date'])
    return df[['date', 'spend', 'clicks', 'impressions', 'campaign']]

def build_weekly_summary(crm, ads):
    crm_weekly = crm.resample('W', on='date')['revenue'].sum().reset_index()
    ads_weekly = ads.resample('W', on='date').agg({
        'spend': 'sum',
        'clicks': 'sum',
        'impressions': 'sum'
    }).reset_index()
    
    merged = pd.merge(crm_weekly, ads_weekly, on='date', how='outer')
    merged['roas'] = merged['revenue'] / merged['spend']
    return merged

What actually took time

Writing the script was not the hard part. The hard part was:

1. Inconsistent source formats. Every platform exports dates differently. Some use 2026-01-15, others use Jan 15, 2026, others use Unix timestamps. pandas handles all of these with pd.to_datetime(), but you have to identify them first.

2. Missing data. Some weeks have no ad spend. Some campaigns have no CRM data. pd.merge() with how='outer' handles this, but you need to decide how to treat NaN values.

3. Column name drift. Platforms occasionally rename columns in their exports. I added a validation step at the start that checks for required columns and raises a clear error if they’re missing.

The output

The script writes a formatted Excel file with:

• Weekly summary table (revenue, spend, ROAS, clicks)
• Campaign breakdown
• Source attribution summary
• A simple chart showing spend vs revenue over time

The whole thing runs in under 60 seconds. The output is ready to share without any manual formatting.

What to do next

If you have a regular reporting task that follows the same pattern every week, this approach works. Start with one data source. Get the cleaning and output working. Then add sources one at a time.

The code for this script is in the marketing-report-automation repo.