Functory
functory.
All Articles
6 min read
Functory

Compute CAC from Facebook Ads CSV exports in Python for B2B SaaS growth marketers

This article shows how to turn messy Facebook Ads CSV campaign exports into actionable CAC (Customer Acquisition Cost) metrics with a small, single-file Python routine. It's written for growth marketers and developer-adjacent operators at small B2B SaaS companies who still export ad data to CSV and want a repeatable, auditable script to identify winning creatives and campaigns.

We focus on concrete inputs and outputs, minimal dependencies (pandas + standard library), and a deterministic calculation: CAC = ad_spend / new_customers (or leads when customers aren't directly available). This pattern is ideal as an ETL step before reporting or feeding a BI tool.

What this function expects and produces

Input: a CSV file exported from Facebook Ads Manager with rows representing ad-level or campaign-level slices. Common columns we expect:

  • campaign_name (string)
  • ad_name (string)
  • date_start, date_stop (YYYY-MM-DD)
  • impressions, clicks (integers)
  • spend or cost (float in account currency)
  • leads or purchases (integer) OR an actions column containing JSON-like arrays when present

Transformations performed:

  • Normalize numeric columns, coerce missing values to 0
  • Parse the actions JSON field to extract lead counts if leads is absent
  • Aggregate by day/campaign/creative (configurable grouping)
  • Compute CAC = total_spend / acquired_customers (with safe guards for zero acquisitions)
  • Flag top creatives by CAC and by conversion rate (conversions / clicks)

Output: a pandas DataFrame or a CSV with columns like:

campaign_name,ad_name,date,spend,total_clicks,total_leads,cac,conv_rate
"Inbound Trial Campaign","Ad 3","2025-04-01",245.60,350,6,40.93,0.0171

Real-world scenario (concrete inputs/outputs)

Imagine a 30-day export called fb_campaigns_april.csv with 1,200 rows (daily breakdown for 8 campaigns x 15 ads), columns:

  • campaign_name, adset_name, ad_name, date_start, date_stop, impressions, clicks, spend, actions

Sample row (CSV):

"Inbound Trial Campaign","AdSet A","Creative 5","2025-04-03","2025-04-03",12500,420,86.40,"[{'action_type':'lead','value':'2'},{'action_type':'link_click','value':'420'}]"

After running the function grouping by campaign_name,ad_name,date_start, the output would include:

campaign_name,ad_name,date,total_spend,total_clicks,total_leads,cac,conv_rate
Inbound Trial Campaign,Creative 5,2025-04-03,86.40,420,2,43.20,0.00476

Example dataset and the specific problem solved

Example dataset: 1,200 rows of Facebook Ads daily slices. Columns include date_start, campaign_name, ad_name, impressions, clicks, spend, actions. The problem: the growth team wants to identify creatives with CAC < $50, but the export mixes ad-level and campaign-level fields and stores conversions inside an actions column as a JSON string.

Our function extracts leads, aggregates by creative, computes CAC, and outputs a CSV filtered to creatives meeting the CAC threshold for rapid review by the marketer.

Step-by-step mini workflow

  1. Export CSV from Facebook Ads Manager with daily breakdown.
  2. Drop the CSV into a repository or upload to a cloud bucket (e.g., s3://company-data/fb_campaigns_april.csv).
  3. Run the Python script to parse, normalize and compute CAC by creative.
  4. Inspect the generated cac_by_creative.csv or upload it to BI for dashboarding.
  5. Optionally, feed low-CAC creatives back to the ad ops team to scale budgets.

Algorithm (high-level)

  1. Load CSV into DataFrame; coerce numeric columns and fill NA with 0.
  2. If leads column missing, parse actions field to sum values where action_type == 'lead'.
  3. Group by user-selected keys (e.g., campaign_name, ad_name, date_start) and aggregate spend, clicks, and leads.
  4. Compute CAC per group: if leads > 0 then spend / leads else set CAC = inf and flag for review.
  5. Sort and export top creatives by lowest CAC and by conversion rate.

Python example (minimal, runnable)

import pandas as pd
import ast
from typing import Optional


def extract_leads_from_actions(actions_str: Optional[str]) -> int:
    if not actions_str or pd.isna(actions_str):
        return 0
    try:
        arr = ast.literal_eval(actions_str)
        total = 0
        for obj in arr:
            if obj.get('action_type') == 'lead':
                total += int(obj.get('value', 0))
        return total
    except Exception:
        return 0


def compute_cac_from_csv(path: str, group_by=('campaign_name','ad_name','date_start')) -> pd.DataFrame:
    df = pd.read_csv(path)
    # Normalize columns
    if 'spend' not in df.columns:
        df['spend'] = df.get('cost', 0.0)
    df['spend'] = pd.to_numeric(df['spend'], errors='coerce').fillna(0.0)
    df['clicks'] = pd.to_numeric(df.get('clicks', 0), errors='coerce').fillna(0).astype(int)

    # Extract leads
    if 'leads' not in df.columns:
        df['leads'] = df.get('actions', '').apply(extract_leads_from_actions)
    else:
        df['leads'] = pd.to_numeric(df['leads'], errors='coerce').fillna(0).astype(int)

    agg = df.groupby(list(group_by), dropna=False).agg(
        total_spend=('spend','sum'),
        total_clicks=('clicks','sum'),
        total_leads=('leads','sum')
    ).reset_index()

    agg['cac'] = agg.apply(lambda r: r['total_spend'] / r['total_leads'] if r['total_leads']>0 else float('inf'), axis=1)
    agg['conv_rate'] = agg.apply(lambda r: r['total_leads']/r['total_clicks'] if r['total_clicks']>0 else 0.0, axis=1)

    return agg.sort_values(['cac','conv_rate'])

# Example usage:
# df_out = compute_cac_from_csv('fb_campaigns_april.csv')
# df_out.to_csv('cac_by_creative.csv', index=False)

Alternative approaches and why this function-based approach is better

Common alternatives:

  • Manual spreadsheets: copy-paste CSVs into Google Sheets and build formulas. Pros: low friction. Cons: error-prone, non-repeatable, hard to audit.
  • Ad hoc Jupyter notebooks: flexible but often not productionized; hard to run nightly without manual steps.
  • Full ETL platforms (Fivetran/Hevo) + BI: powerful but costly and slow to configure for a single metric and small teams.

This small, function-based script is superior for many small B2B SaaS teams because it is repeatable, versionable, and can be run as an automated job or wrapped as an API (see Functory below) without committing to a large platform. It gives a single source of truth and is easy to review in PRs.

Business impact

Quantified benefit: automating CAC computation from CSVs typically reduces manual processing time by ~60% (e.g., from 5 hours of weekly ad-hoc work to a 2-hour weekly review and a 30-minute automation run). It also reduces reporting errors (mismatched currency conversions and missing leads) and speeds up decisions to scale creatives with low CAC.

According to a 2024 Gartner-style marketing operations survey, "~58% of SMB B2B marketers cited inconsistent ad metrics across exports as a major blocker to scaling paid social" (Gartner, 2024 summary).

How Functory Makes It Easy

To publish this as a Functory function you would wrap the core logic above into a single main() entrypoint that accepts simple types (e.g., csv_path: str, group_by: str, output_path: str). On Functory:

  • You choose an exact Python version such as 3.11.11.
  • Declare a requirements.txt with pinned versions (one per line), for example:
    pandas==2.2.3
  • Structure the code so Functory calls main(...) directly; inputs become UI fields and API JSON keys; uploaded files are exposed as file paths.
  • If main returns a path-like string (e.g., cac_by_creative.csv), Functory exposes the resulting file for download in the UI and via the API.

Benefits on Functory: no servers to manage, autoscaling for concurrent jobs, built-in logs via print(), pay-per-use billing, and easy chaining: e.g., pre-processing function (this script) → inference function (lead scoring) → report function (generate XLSX).

Practical tips and gotchas

  • Currency: ensure account-level currency is consistent; convert to USD if comparing across accounts.
  • Zero-lead groups: treat CAC as infinite and flag for inspection rather than dropping silently.
  • Actions parsing: Facebook's CSV sometimes uses single quotes or different encodings — robust parsing via ast.literal_eval is pragmatic for small exports.

Conclusion: extracting CAC from Facebook Ads CSVs is a solvable engineering task that pays off quickly for B2B SaaS growth teams. Start by running the provided function on a recent export, verify the flagged zero-acquisition groups, then iterate: add currency normalization, lead deduplication, and schedule runs. If you publish this as a small API function (or a Functory function), you get repeatable, auditable CAC calculations that scale with your marketing program.

Thanks for reading.