Functory
functory.
All Articles
6 min read
Functory

Facebook Ads CSV to Cost-Per-Lead Report in Python with Functory for B2B SaaS

This article shows how to turn raw Facebook Ads CSV exports that include creatives into a repeatable Cost-Per-Lead (CPL) reporting function written in Python and packaged as a Functory function. Instead of manually cleaning CSVs and copying rows into spreadsheets, you will learn a concrete script that reads CSVs (campaign and conversion rows), normalizes creative identifiers, aggregates spend and leads by campaign or creative, and outputs a clean CPL CSV or JSON for dashboards and reviews.

The target reader is a growth marketer or engineering generalist at a small B2B SaaS team who receives CSVs like campaigns_2025-10.csv and wants a reproducible tool that reduces manual work and feeds downstream analytics.

What this function expects and produces

Inputs (examples):

  • A Facebook Ads export CSV named campaigns_2025-10.csv with columns: campaign_name, ad_id, creative_url, date, spend_usd, impressions, clicks.
  • An optional conversions CSV named conversions_2025-10.csv with columns: ad_id, event_name, event_time, leads (integer count), or a mapping JSON that maps ad_id to lead count.

Processing steps:

  1. Normalize creative identifiers (strip URL query strings, map creative_url to a short asset key like hero_img_v2).
  2. Join campaign rows to conversions on ad_id (left join) and aggregate spend and leads by campaign or creative.
  3. Compute derived metrics: cpl = spend_usd / leads (handle zero leads), ctr = clicks / impressions.
  4. Output CSV and JSON with one row per grouping including sample creative preview URL and derived metrics.

Outputs (examples):

  • CSV: cpl_report_2025-10.csv with columns group_by, spend_usd, leads, cpl_usd, ctr, sample_creative_url.
  • JSON: a small summary object that your BI dashboard can ingest.

Real-world scenario

Acme Analytics is a 12-person B2B SaaS company running lead-gen campaigns. Each Monday the growth marketer downloads two files from Facebook Ads:

  • campaigns_2025-10.csv (12,000 rows): columns: campaign_name, ad_id, creative_url, date, spend_usd, impressions, clicks.
  • conversions_2025-10.csv (3,200 rows): columns: ad_id, event_name, leads. Leads are deduplicated server-side and aggregated per ad_id.

The problem: manually joining these in Google Sheets takes ~3 hours per week and often misses creatives with malformed URLs. The function cleans creative_url by removing tracking parameters, groups data by campaign_name or by normalized creative key, and produces a ranked CPL list so the marketer can stop low-performing creatives.

Example dataset

Fabricated but realistic dataset details:

  • Size: 12,000 ad-level rows, 3,200 conversion rows.
  • Columns: campaigns.csv columns as above; conversions.csv with ad_id keys and integer leads.
  • Problem solved: join and aggregate spend to compute CPL per creative and per campaign; normalize creative_url values like https://cdn.example.com/img/hero.jpg?fbclid=ABC → https://cdn.example.com/img/hero.jpg.

Step-by-step developer workflow

  1. Place campaigns_YYYY-MM.csv and optional conversions_YYYY-MM.csv in a working directory.
  2. Run the Python script locally to verify output CSV contains expected columns and correct cpl calculations.
  3. Pin dependencies and wrap core logic into a file to publish on Functory (optional: expose grouping and date range as parameters).
  4. Run function via Functory UI or call the API from your BI pipeline to produce automated weekly CPL reports into S3 or Slack.

Algorithm (high-level)

  1. Load campaigns CSV and conversions CSV into pandas DataFrames.
  2. Normalize creative_url by stripping query strings and mapping duplicates to canonical keys.
  3. Left-join conversions on ad_id, fill missing leads with 0.
  4. Group by chosen key (campaign_name or creative_key), sum spend and leads, compute cpl = spend / max(1, leads), and compute additional metrics like CTR.
  5. Write to CSV and return summary JSON.

Concrete Python example

The snippet below is a compact but complete routine you can run locally. It focuses on the data transformation (not the Functory wrapper).

import pandas as pd
from urllib.parse import urlsplit

def normalize_creative(url):
    try:
        parts = urlsplit(url)
        return f"{parts.scheme}://{parts.netloc}{parts.path}"
    except Exception:
        return url

def process_campaign_csv(campaigns_csv, conversions_csv=None, group_by='campaign_name', output_csv='cpl_report.csv'):
    campaigns = pd.read_csv(campaigns_csv)
    if conversions_csv:
        conv = pd.read_csv(conversions_csv)
        conv = conv.groupby('ad_id', as_index=False)['leads'].sum()
    else:
        conv = pd.DataFrame(columns=['ad_id', 'leads'])

    campaigns['creative_key'] = campaigns['creative_url'].fillna('').apply(normalize_creative)
    merged = campaigns.merge(conv, on='ad_id', how='left')
    merged['leads'] = merged['leads'].fillna(0).astype(int)
    merged['ctr'] = merged['clicks'] / merged['impressions'].replace({0: 1})

    group_col = group_by if group_by in merged.columns else 'creative_key'
    agg = merged.groupby(group_col).agg(
        spend_usd=('spend_usd', 'sum'),
        leads=('leads', 'sum'),
        impressions=('impressions', 'sum'),
        clicks=('clicks', 'sum'),
        sample_creative_url=('creative_url', 'first')
    ).reset_index()

    agg['cpl_usd'] = (agg['spend_usd'] / agg['leads']).replace([float('inf')], pd.NA)
    agg['ctr'] = (agg['clicks'] / agg['impressions']).fillna(0)

    agg = agg.sort_values('cpl_usd')
    agg.to_csv(output_csv, index=False)
    return agg.head(20).to_dict(orient='records')

# Example local run
if __name__ == '__main__':
    summary = process_campaign_csv('campaigns_2025-10.csv', 'conversions_2025-10.csv', group_by='creative_key')
    print(summary)

Comparison to common approaches

Many teams currently use spreadsheets, manual lookups in the Facebook UI, or one-off Jupyter notebooks. Spreadsheets are fragile (copy/paste errors) and notebooks are not easily automated or accessible to non-technical marketers. Embedding this logic into a small, testable Python function and deploying it as a callable API (via Functory) makes the process repeatable, auditable, and automatable. Unlike heavyweight ETL platforms, this approach is low-cost and fast to iterate.

Business impact

Quantified benefit: converting a 3-hour manual weekly task into an automated Functory function and scheduled call can save ~12 hours/month. For a small team where every hour of a marketer is worth $50, that's roughly a $600/month productivity improvement plus faster decision cycles that can improve ad ROI by 5-15% when low-performing creatives are turned off earlier.

Industry trend: According to a 2024 MarTech survey, 64% of growth teams reported they expect to automate routine ad reporting within 12 months (source: 2024 MarTech Automation Report).

How Functory Makes It Easy

On Functory you would wrap the core processing logic into a single main(...) entrypoint that accepts file paths or uploaded files and simple parameters (strings like group_by, dates, and numeric thresholds). Functory exposes each parameter as an input field in its UI and as JSON fields in the HTTP API. If your main(...) returns a path-like string (for example 'cpl_report_2025-10.csv'), Functory will expose that CSV as a downloadable result.

Practical steps to publish:

  • Choose an exact Python version (for example 3.11.11) in Functory so runtime is deterministic.
  • Create a requirements.txt with pinned versions, e.g. pandas==2.2.2, python-dateutil==2.8.2.
  • Structure code so Functory can call main(campaigns_file: str, conversions_file: str | None, group_by: str) directly. Do not rely on CLI parsing.
  • Upload the files via the Functory UI or pass remote URLs/JSON payloads to the API and trigger execution. Functory handles provisioning, autoscaling, logs via print(), and pay-per-use billing.

You can chain this Functory function with another function that uploads the CSV to S3, or with a Slack-poster function that publishes the top-5 creatives each week, creating a no-ops automated pipeline for weekly acquisition reviews.

Conclusion: Converting one-off Facebook Ads CSV scripts into a small, well-tested Python function and publishing it on Functory turns repetitive reporting into a reliable, automatable asset. Next steps: customize the grouping and thresholds for your account (e.g., flag creatives with CPL > $150), add automatic export to S3 or a dashboard, and put the function on a weekly schedule. Try packaging the example above, pin dependencies, and publish a Functory function to see immediate time savings and faster acquisition decisions.

Thanks for reading.