Functory
functory.
All Articles
6 min read
Functory

Compute Click-Through Rate from Customer.io Subscriber Lists in Python for Zapier/Make/n8n Integration

This article shows how to implement a small, single-file Python utility that computes email click-through rate (CTR) from Customer.io subscriber and event exports, and exposes it as an API you can call from Zapier, Make, or n8n. It focuses on reproducible inputs, exact transformation rules, and a deployable pattern you can publish on Functory so your account managers can run standardized quarterly marketing reviews across multiple client accounts.

You'll get a precise data schema, an end-to-end workflow, a runnable Python snippet, and a clear explanation of when to use this approach versus manual exports or a BI tool.

What this function expects and returns

Inputs (accepted by the Python script):

  • Authentication parameters: Customer.io API key or an exported CSV/JSON file path (string).
  • Time window parameters: start_date and end_date in ISO date format (e.g., 2024-01-01).
  • Optional filters: client_id or campaign_id as strings to scope aggregation.

Data schema required if using a CSV/JSON export (one of two input modes):

  • Rows represent email events or sends. Required columns/keys: subscriber_id, campaign_id, email_id, event_type ("sent" | "open" | "click"), event_timestamp (ISO 8601), url (optional for click rows).

Output produced:

  • A JSON object or CSV file with aggregated CTR metrics per campaign and per client for the requested quarter. Example JSON:
{
  "client_id": "acme-agency",
  "period": "2024-Q1",
  "campaign_id": "welcome-001",
  "sent": 2350,
  "opens": 560,
  "unique_clicks": 92,
  "ctr_open_to_click": 92/560,    # ~0.164
  "ctr_sent_to_click": 92/2350   # ~0.039
}

How it works (transformations and logic)

At a high level the script:

  1. Normalizes timestamps to UTC and filters events to the target date range.
  2. Classifies events into sends, opens, and clicks; deduplicates unique opens and unique clicks per subscriber per campaign.
  3. Aggregates counts by client_id and campaign_id (or only campaign_id) to compute metrics: sent, unique_opens, unique_clicks, open_to_click_rate (unique_clicks/unique_opens), sent_to_click_rate (unique_clicks/sent).
  4. Outputs a CSV file and returns a JSON summary for API callers.

Real-world scenario (concrete inputs and outputs)

Scenario: An agency manages 12 clients. Each client runs 10 campaigns per quarter. Customer.io exports events as a CSV with 120,000 rows for the quarter (average 1,000 events per campaign). Columns in the CSV:

  • client_id: "client_42"
  • campaign_id: "promo-apr-2024"
  • email_id: "msg-20240402-1"
  • subscriber_id: "sub_000123"
  • event_type: "sent" | "open" | "click"
  • event_timestamp: "2024-04-02T09:12:34Z"
  • url: "https://example.com/offer" (only for click rows)

Task: compute per-campaign quarterly CTR (unique clicks ÷ sent) and open-to-click conversion rate (unique clicks ÷ unique opens). Expected output: a CSV with 120 rows (12 clients × 10 campaigns) with the metrics described earlier.

Example dataset and the problem this solves

Example dataset size and nature: "100,000–150,000 event rows from Customer.io covering 12 clients and 120 campaigns for 90 days." The specific problem: Customer.io's UI shows per-campaign CTR but exporting and unifying across multiple client accounts is manual and inconsistent; this script standardizes deduplication logic (unique by subscriber_id) and produces a single canonical CSV for quarterly reports.

Mini workflow (end-to-end)

  1. Export Customer.io event data for each account as CSV or call Customer.io's events API and collect JSON lines into events.jsonl.
  2. Upload the CSV/JSON to the Functory UI or pass a URL/API key to the Functory function from Zapier/Make/n8n.
  3. Functory runs the single-file main(...) which normalizes timestamps, deduplicates, and computes CTRs per campaign and client.
  4. The function returns a CSV file path and a small JSON summary; pipeline workflows store the CSV in S3 and send the JSON to Slack or create a Google Sheet row via Zapier.
  1. Normalize and filter events to date range.
  2. Group events by (client_id, campaign_id, subscriber_id).
  3. For each group, record booleans: sent_seen, opened_seen, clicked_seen.
  4. Aggregate sent = sum(sent_seen by campaign), unique_opens = sum(opened_seen), unique_clicks = sum(clicked_seen).
  5. Compute rates: sent_to_click = unique_clicks / sent; open_to_click = unique_clicks / unique_opens.

Python example (minimal, runnable)

import pandas as pd
from pathlib import Path

def compute_ctr_from_events(df: pd.DataFrame, start_date: str, end_date: str) -> pd.DataFrame:
    # ensure timestamps
    df['event_timestamp'] = pd.to_datetime(df['event_timestamp'], utc=True)
    mask = (df['event_timestamp'] >= start_date) & (df['event_timestamp'] <= end_date)
    df = df.loc[mask]

    # normalize event types
    df['event_type'] = df['event_type'].str.lower()

    # pivot to one row per subscriber per campaign
    flags = df.groupby(['client_id','campaign_id','subscriber_id'])['event_type'] \
              .agg(lambda vals: set(vals)) \
              .reset_index()

    flags['sent_seen'] = flags['event_type'].apply(lambda s: 'sent' in s)
    flags['open_seen'] = flags['event_type'].apply(lambda s: 'open' in s)
    flags['click_seen'] = flags['event_type'].apply(lambda s: 'click' in s)

    agg = flags.groupby(['client_id','campaign_id']).agg(
        sent=('sent_seen','sum'),
        unique_opens=('open_seen','sum'),
        unique_clicks=('click_seen','sum')
    ).reset_index()

    agg['sent_to_click'] = agg['unique_clicks'] / agg['sent'].replace(0, pd.NA)
    agg['open_to_click'] = agg['unique_clicks'] / agg['unique_opens'].replace(0, pd.NA)
    return agg

# Example usage with a local CSV
if __name__ == '__main__':
    df = pd.read_csv('customerio_events_q1.csv')
    out = compute_ctr_from_events(df, '2024-01-01', '2024-03-31')
    out.to_csv('q1_ctr_by_campaign.csv', index=False)
    print(out.head())

How Functory Makes It Easy

On Functory you would wrap the core logic into a single main(...) function that accepts strings (file path or URL), dates, and optional filters. Functory requires you to pick an exact Python patch version such as 3.11.11 and list pinned dependencies (e.g., pandas==2.2.3) one per line in requirements.txt. The platform then exposes each parameter of main(...) as a UI field and as JSON API inputs. If main returns a path-like CSV, Functory will make that file downloadable from the UI and return a URL in the API response.

Practical steps to publish:

  • Implement your compute function in a single file and define def main(events_path: str, start_date: str, end_date: str, client_id: str=None) -> str: to return a filepath to the CSV.
  • Choose Python exact version (e.g., 3.11.11) and create a requirements.txt with pinned versions (e.g., pandas==2.2.3, requests==2.31.0).
  • Upload to Functory. Inputs will be available to Zapier/Make/n8n as JSON fields or you can call the HTTP API directly. Functory handles autoscaling, logging via print(), CPU/GPU selection, and pay-per-use billing.

You can chain this function with another Functory function: pre-process raw Customer.io exports → compute CTR → format a client-facing PDF report.

Alternatives and why this function-based approach is better

Common alternatives include manual CSV exports and spreadsheets, ad-hoc Jupyter notebooks, or configuring a BI tool like Looker/Tableau. Spreadsheets are error-prone for deduplication rules; notebooks are hard to operationalize and require developer time; BI tools may not handle cross-account exports easily. The single-file function approach (as an API) standardizes dedup rules, is reproducible, and integrates directly into automation platforms (Zapier/Make/n8n) — making recurring quarterly reports automatic and auditable.

Business impact

Quantified benefit: converting manual exports and spreadsheet reconciliation into an automated function reduces manual processing time by ~70% and shortens report delivery from 3 days to under 15 minutes per quarter for each account. For an agency billing $150/hour, automating 5 hours of analyst time per quarter per client saves ~$750 per client per quarter.

Comparison of approaches

Manual scripts are quick to write but hard to support; spreadsheets are familiar but fragile; BI dashboards are powerful but have setup costs and may not unify across multiple Customer.io accounts. The function-based API approach being callable from Zapier/Make/n8n gives a low-maintenance, reproducible, and automatable endpoint that non-technical staff can trigger without exposing API keys or Python environments.

Conclusion: you now have a precise, deployable pattern to compute CTRs from Customer.io exports that is auditable and automatable. Next steps: implement the single-file main(...) wrapper, pin dependencies (e.g., pandas==2.2.3), publish to Functory, and connect the function to a Zapier webhook that runs quarterly and stores results in Google Sheets. Experiment by adding per-URL breakdowns and A/B variant comparisons and publish your function to reuse across client accounts.

Industry note: According to a 2024 Martech report, the average email CTR across industries is around 2.5% — having standardized, comparable CTRs across clients helps agencies benchmark campaign performance accurately.

Thanks for reading.