A live marketing data warehouse: mapping every API into one dashboard

Every marketing team eventually accumulates the same problem: traffic lives in Google Analytics, search performance in Search Console, pipeline in HubSpot, and spend across two or three ad platforms, each with its own login, its own date logic and its own definition of a conversion. Answering a simple question, like whether last month’s spend increase actually moved qualified pipeline, means opening five tabs, exporting five spreadsheets, and reconciling them by hand. That reconciliation is where hours disappear every week, and it is also where errors creep in.

A live marketing data warehouse solves this by pulling every source into one common schema on a schedule, so a single dashboard can answer cross-channel questions directly. This does not require an enterprise data platform; a small, well-structured Python pipeline and a lightweight analytical database are enough for most businesses. This guide covers the schema design, the extraction pattern, the storage layer, and how to keep the whole thing trustworthy.

Design the schema before you write a single extractor

The temptation is to start pulling data immediately and figure out the shape later. Resist it. Every source should land in a common fact table with the same core columns: date, channel, campaign, spend, sessions, conversions and revenue, with source-specific detail kept in separate tables that join back to it. This is what makes a single dashboard possible across sources that otherwise share almost no vocabulary.

FACT_SCHEMA = {
    "date": "DATE",
    "source": "VARCHAR",          # ga4 | search_console | hubspot | ads
    "channel": "VARCHAR",
    "campaign": "VARCHAR",
    "spend": "DOUBLE",
    "sessions": "BIGINT",
    "conversions": "BIGINT",
    "revenue": "DOUBLE",
}

Every extractor’s job is simply to map its source’s native fields onto this shape. GA4 has no native spend, ad platforms have no native sessions; those columns are left null rather than omitted, which keeps every row structurally identical and every downstream query simple.

Write one extractor per source, same contract

Each extractor is a small, independent function that authenticates, pulls, and returns rows already shaped to the fact schema. Keeping them independent means one source failing does not block the others, and adding a new source later is additive rather than disruptive.

def extract_ga4(client, property_id, days=7):
    rows = ga4_report_all(client, property_id, days)   # from the GA4 guide
    return [{
        "date": r["date"], "source": "ga4", "channel": r["channel"],
        "campaign": None, "spend": None, "sessions": r["sessions"],
        "conversions": r.get("conversions", 0), "revenue": None,
    } for r in rows]

def extract_hubspot_deals(hs, days=7):
    deals = hs.fetch_recent_deals(days)
    return [{
        "date": d["closedate"][:10], "source": "hubspot", "channel": d.get("source", "unknown"),
        "campaign": d.get("campaign"), "spend": None, "sessions": None,
        "conversions": 1, "revenue": float(d.get("amount", 0) or 0),
    } for d in deals if d.get("closedate")]

def extract_ad_spend(client, days=7):
    rows = client.fetch_daily_spend(days)
    return [{
        "date": r["date"], "source": "ads", "channel": r["platform"],
        "campaign": r["campaign_name"], "spend": r["spend"], "sessions": None,
        "conversions": r.get("conversions", 0), "revenue": None,
    } for r in rows]

Notice that spend, sessions and revenue are genuinely absent for sources that do not track them, rather than defaulted to zero. Zero implies a measured absence; null means the source simply does not know, and the distinction matters the first time someone builds a total that should exclude nulls but include measured zeros.

Land the data in an embeddable analytical database

For most businesses, the right storage layer is not a hosted cloud warehouse; it is an embeddable analytical database that lives in a single file and needs no server to run. DuckDB is well suited to this: it speaks SQL, handles the aggregation volumes marketing data produces without effort, and reads directly from the parquet or CSV files a Python pipeline naturally produces.

import duckdb

def load_fact_table(rows, db_path="warehouse.duckdb"):
    con = duckdb.connect(db_path)
    con.execute("""
        CREATE TABLE IF NOT EXISTS fact_marketing (
            date DATE, source VARCHAR, channel VARCHAR, campaign VARCHAR,
            spend DOUBLE, sessions BIGINT, conversions BIGINT, revenue DOUBLE,
            loaded_at TIMESTAMP DEFAULT current_timestamp
        )
    """)
    con.executemany(
        "INSERT INTO fact_marketing (date, source, channel, campaign, "
        "spend, sessions, conversions, revenue) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        [(r["date"], r["source"], r["channel"], r["campaign"],
          r["spend"], r["sessions"], r["conversions"], r["revenue"]) for r in rows]
    )
    con.close()

A single file that any team member can query with SQL, without provisioning infrastructure, is often the entire warehouse a small or mid-sized marketing team needs. It scales to millions of rows comfortably and can be upgraded to a hosted warehouse later without changing the schema or the extraction logic.

Make loads idempotent so a re-run never duplicates

A pipeline that runs on a schedule will eventually be run twice for the same day, whether by a retry after a failure or a manual re-trigger. The fact table needs to tolerate that without duplicating rows, which means deleting the target date range before inserting rather than blindly appending.

def load_fact_table_idempotent(rows, db_path, date_range):
    con = duckdb.connect(db_path)
    con.execute(
        "DELETE FROM fact_marketing WHERE date BETWEEN ? AND ? AND source = ?",
        [date_range[0], date_range[1], rows[0]["source"]] if rows else [None, None, None]
    )
    # then insert as above
    con.close()

Deleting and reinserting by date and source, rather than appending unconditionally, is the difference between a warehouse whose totals you can trust and one that silently doubles every metric after the second scheduled run.

Build the cross-source views the dashboard actually needs

With every source in one fact table, the interesting SQL becomes straightforward. A daily cross-channel summary, spend against conversions by channel, or a rolling weekly trend are all a GROUP BY away, and because they read from the same table, they are automatically consistent with each other in a way five separate exports never are.

def channel_summary(db_path, days=28):
    con = duckdb.connect(db_path)
    return con.execute("""
        SELECT channel,
               SUM(spend) AS spend,
               SUM(sessions) AS sessions,
               SUM(conversions) AS conversions,
               SUM(revenue) AS revenue,
               CASE WHEN SUM(spend) > 0
                    THEN SUM(revenue) / SUM(spend) ELSE NULL END AS roas
        FROM fact_marketing
        WHERE date >= current_date - INTERVAL (?) DAY
        GROUP BY channel
        ORDER BY revenue DESC NULLS LAST
    """, [days]).df()

That single query, run against a warehouse that already contains GA4, HubSpot and ad-platform data, answers the question that used to take an afternoon of manual reconciliation: which channels are actually producing revenue, at what spend, this month.

Schedule it and connect a dashboard on top

Run the full extraction and load nightly, early enough that the morning’s dashboard reflects yesterday’s complete data. Any lightweight BI tool, or even a simple Python notebook rendered to a static page, can connect directly to the DuckDB file and read the summary views without needing to know anything about GA4, HubSpot or ad platforms individually. The dashboard’s only dependency becomes the warehouse schema, which is exactly the decoupling that makes the system maintainable: a source’s API can change entirely, and only its extractor needs to change, never the dashboard.

Keep it trustworthy

A warehouse nobody trusts is worse than five separate tabs, because at least the tabs are honest about being disconnected. Log row counts per source on every run and alert if a source returns zero rows unexpectedly, since that is almost always a broken credential rather than a genuinely quiet day. Keep a loaded_at timestamp on every row so you can always answer when a number was last refreshed, and reconcile totals against each native platform’s own dashboard periodically, because the moment your warehouse’s numbers drift from the source of truth without anyone noticing is the moment the whole system stops being useful.

Handle the sources that do not cooperate

Not every platform offers a clean, well-documented API, and part of designing this system realistically is accepting that some extractors will be messier than others. An ad platform might only offer bulk CSV export rather than a query API; a smaller tool might have no API at all and require a scheduled scrape of an exported report. The fact schema absorbs this without complaint, because the contract every extractor honours is the output shape, not the input mechanism. A CSV-parsing extractor and a REST-calling extractor look identical to the loader that follows them.

import csv

def extract_from_csv_export(path, source_name, channel_col, spend_col, date_col):
    rows = []
    with open(path, newline="", encoding="utf-8") as f:
        for r in csv.DictReader(f):
            rows.append({
                "date": r[date_col], "source": source_name, "channel": r[channel_col],
                "campaign": None, "spend": float(r[spend_col] or 0),
                "sessions": None, "conversions": None, "revenue": None,
            })
    return rows

This is also where a lightweight scheduling layer earns its place: a platform that only offers weekly CSV exports can be polled weekly while API-driven sources refresh nightly, and the warehouse simply reflects each source’s true freshness rather than forcing an artificial uniform cadence that some sources cannot actually support.

Add a slowly changing dimension for campaign metadata

Fact tables are deliberately narrow, but a dashboard user usually wants more context than the fact table alone provides, such as a campaign’s objective, its creative theme, or which team owns it. Rather than repeating that metadata on every fact row, keep it in a separate dimension table keyed by campaign, and join at query time. This keeps the fact table lean and fast to load, while giving the dashboard rich context to filter and group by.

con.execute("""
    CREATE TABLE IF NOT EXISTS dim_campaign (
        campaign VARCHAR PRIMARY KEY,
        objective VARCHAR,
        owner VARCHAR,
        theme VARCHAR
    )
""")

A dashboard query then joins fact_marketing to dim_campaign on the campaign name, and a marketer filtering by objective or owner never needs to know that distinction lives in a separate table at all.

The real cost this replaces

It is easy to underestimate how much a small team spends on manual reconciliation until it is measured directly. A weekly cross-channel report that takes even ninety minutes to assemble by hand, across four sources, costs a business roughly seventy-five hours a year in a task that produces zero new insight, only the arrangement of numbers that already existed somewhere. That time is not spent thinking about what the numbers mean; it is spent copying, pasting, renaming columns so two exports use the same channel labels, and manually checking that a total in one tab matches a total in another. A warehouse built along the lines described here typically takes a few days to stand up for the first two or three sources and a few hours for each source added afterward, which means the investment pays for itself within the first month for almost any team currently doing this reconciliation by hand. The larger, harder-to-measure cost is the analysis that never happens because the reconciliation overhead makes it not worth attempting: the quick question about a specific campaign’s cross-channel effect that would have taken five minutes with a warehouse but was never asked because everyone knew it would take an afternoon of exporting first. Removing that friction changes not just how fast existing reports get built, but how many good questions actually get asked at all.

Key takeaways

1. Design a common fact schema first; every extractor’s only job is mapping onto it.
2. Keep extractors independent per source, so one failure never blocks the others.
3. An embeddable analytical database like DuckDB is enough warehouse for most marketing teams.
4. Make loads idempotent: delete and reinsert by date and source, never blindly append.
5. Cross-source SQL views are what turn raw facts into the answers a dashboard needs.
6. Log row counts and reconcile against native dashboards periodically to keep the warehouse trustworthy.

Once every source lands in one schema, the five-tab reconciliation ritual disappears, and the question that used to take an afternoon becomes a query that runs in under a second.