Direct-to-consumer ecommerce, paid social creative team
Meta Ads Creative Performance Pipeline: BigQuery and Looker
The extraction runs daily against the client's own ad accounts and appends to a BigQuery table that a Looker Studio report reads through a view; the repository carries the script variants, the view SQL and the dashboard link. The handover documents one outstanding hardening item, API credentials and the service-account key sit inline in the script and belong in environment variables or Secret Manager, and no before/after performance uplift was measured, so none is claimed.
- Our role
- Data engineer, extraction, warehouse model and dashboard specification
- Timeline
- Built as a daily pipeline, handed over with the warehouse view and dashboard spec
- Sector
- Direct-to-consumer ecommerce, paid social creative team
At a glance
- Extraction cadence
- Daily, yesterday's ad-level insights
- Grain
- One row per ad per day
- Naming-convention fields parsed
- 13 positions from Ad_Name
- Creative enrichment
- 30 concurrent Graph API fetches
- Winner rule
- Lifetime spend >= 1,000 and lifetime ROAS >= 2.0
- Warehouse model
- Append-only table plus a five-CTE view
- Dashboard
- Five Looker Studio pages over one view
What was breaking
The reporting the team had was account-level and spend-shaped. It could tell them what an account returned last week, which is the one question nobody in a creative function needs answered. They need to know what to make next. Answering that means grouping performance by the attributes of the creative itself: the angle it argues, the product it sells, who briefed it, whether it is a fresh concept or the fourth variation of one that already worked.
Those attributes did exist. The team encoded them in a disciplined ad naming convention, awareness stage, campaign, creative id, variation, strategist, creative type, brief type, angle, product, launch date, market, editor, call to action, in a fixed order separated by a delimiter. Meta returns that as a single string. Every reporting surface downstream treated it as a single string too, so the convention was effectively write-only: it cost the team discipline to maintain and returned nothing.
The second problem was that the Marketing API does not hand over what a creative dashboard needs in one call. Insights come back per ad per day with conversion counts buried inside nested action arrays under several possible action types, and the creative's permalink and thumbnail are not in the insights payload at all; they require a separate Graph API lookup per ad. Doing that lookup serially across every active ad in every account is slow enough to turn a daily job into a problem of its own.
Third, the API itself is unreliable in specific, repetitive ways: rate limits, page-level throttles, transient service errors, token problems, spend-cap errors. A script that treats any of those as a fatal error produces a day with no data, and a day with no data in an append-only table is invisible unless something checks for it. The gap only surfaces later, when someone notices a flat line in a chart and no longer trusts the rest of it.
The naming convention returned nothing
Thirteen fields of creative metadata were being maintained by hand on every ad and then discarded at the reporting layer, because no downstream tool split the string.
Creative decisions were made on recall
Which angles win and which products carry spend were answered from memory and anecdote, because no surface grouped performance by those attributes.
No shared definition of a winner
Without a stated threshold, 'this one worked' meant something slightly different to each person, and win rate could not be compared across angles, products or weeks.
Creative velocity was unmeasured
The ratio of new concepts to variations to revived creatives is the thing a testing programme is supposed to control, and it was not visible anywhere.
Manual export was the reporting bottleneck
Pulling each ad account by hand, per reporting cycle, is both the slowest part and the part most likely to be quietly skipped under pressure.
What we built
The pipeline is deliberately plain: a Python extraction that writes a staging Parquet file and loads it to one BigQuery table, and a single BigQuery view that does all of the modelling. Nothing clever sits between them. That split is the main design decision, and it is the one worth defending, extraction changes for API reasons, modelling changes for business reasons, and keeping them in separate artefacts means a redefinition of 'winner' never risks the data collection, and an API migration never silently alters a metric.
Extraction pulls ad-level insights for the previous day across every configured ad account, then parses the conversion fields out of Meta's nested action arrays: purchases and purchase value, initiated checkouts, add-to-cart, and the video metrics including three-second plays, completions and average play time. The video average arrives in milliseconds and is converted to seconds on the way in, which is the kind of detail that is invisible until a chart is wrong by a factor of a thousand.
Creative enrichment runs asynchronously because it has to. Each ad needs a separate Graph API call for its permalink and thumbnail, so those run concurrently, thirty at a time, while ad accounts themselves are processed two at a time. That keeps a per-ad lookup from dominating the runtime without pushing the account-level calls into rate limits.
Retries are specific rather than general. The script retries on the Facebook error codes that are genuinely transient or throttle-related (unknown and service errors, rate limit and call-volume errors, page-level throttles, access-token errors, custom spend-limit errors) with exponential backoff capped at sixty seconds and a limit of five attempts. A general catch-all retry would mask real errors; retrying a known list means an unrecognised failure still surfaces as a failure.
Gaps are treated as a first-class case. After the main fetch the script checks for dates with missing coverage and re-fetches them, because Meta's insights for a day are not always complete the first time they are asked for. The repository keeps the variants this produced in practice: the daily run, a corrected creative-URL build, and a separate path for the case where one ad account returns no data for a day, which is normal, and must be logged and skipped rather than treated as a failed run.
The warehouse table stores numeric fields as strings and casts them inside the view. This looks wrong and is intentional: it means an unexpected value from the API lands in the warehouse instead of rejecting the load, and the cast is a modelling decision that can be corrected by replacing a view rather than by backfilling a table. The changelog bears this out, four separate cast and field-mapping corrections, including moving link clicks from the general clicks field to inline link clicks, were all fixed in the modelling layer without touching the stored data.
The view is five CTEs and does the work the dashboard should not. It deduplicates creative URLs per ad name, casts every metric and splits the naming convention into its thirteen positions, aggregates lifetime spend, revenue, purchases, ROAS and CPA per ad name, finds each creative's first-ever launch date, and then joins all of it together with two derived labels: the winner flag, and whether a creative is new, a variation or a revived relaunch of an earlier creative id. Everything the dashboard shows is therefore a field, not a formula hidden in a chart.
The dashboard is five pages over that one view: creative overview, winner analysis, angle and product performance, weekly creative velocity, and a creative library that renders thumbnails inline with filters on angle, product, creative type and winner status. Ten calculated fields (win rate, CTR, thumb-stop and hook rate, completion rate, ROAS, CPA, and the three creative-label shares) are defined once at the data source level so no two charts can disagree about what CTR means.
Extract and model in separate artefacts
The script collects; the view defines. A changed metric definition cannot break collection, and an API migration cannot quietly change a metric. It also means the business logic is one readable SQL file rather than scattered across chart configuration.
Store permissively, cast in the model
Numeric fields land as strings and are cast in the view. An unexpected API value lands in the warehouse instead of failing the load, and a casting mistake is fixed by replacing a view rather than by backfilling history.
Read the convention the team already maintains
Thirteen creative dimensions were already being typed into every ad name. Parsing that string is cheaper than any tagging system and does not ask the people launching ads to change anything.
Make 'winner' a stated rule, not a judgement
Lifetime spend and lifetime ROAS thresholds in SQL mean win rate is comparable across angles, products, strategists and weeks. The threshold can be argued about; what matters is that it is applied identically to every ad.
Retry a known list of errors, not everything
Backoff is applied to the specific Facebook codes that are transient or throttle-related. Anything unrecognised still fails loudly, which is what keeps a silent empty day from looking like a successful run.
Treat a missing day as a case, not an incident
Insights can arrive incomplete, and an account with no active ads legitimately returns nothing. The pipeline re-fetches gap dates and logs-and-skips empty accounts, so neither becomes a hole in an append-only table.
Non-negotiables
- The ad naming convention could not change. It is enforced by the people launching ads, and the pipeline had to read what they already write rather than ask them to write differently.
- The warehouse had to tolerate schema drift. Meta adds and renames action types, so a load that fails on an unexpected field is a load that fails regularly.
- A failed day had to be recoverable without a full rebuild, because Meta's insights for a given day can arrive incomplete and need re-fetching.
- The dashboard had to sit on a single reporting object, so that a metric definition changes in one place rather than in every chart.
- Multiple ad accounts had to land in the same table with the account preserved as a column, since the team reports both per-account and blended.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
Daily extraction through to the creative dashboard
One script collects and stages; one BigQuery view does all of the modelling. The dashed path is the gap check that re-fetches dates with incomplete coverage before the load.
- Trigger
- Processing step
- Branch / filter
- Data store or CRM
- Delivered output
- Primary path
- Conditional or exception path
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Pin down what the dashboard has to answer
The questions were not about spend. They were win rate overall and per angle, per product and per strategist; how many creatives launched each week and what share of them were genuinely new; which angles and products hold the largest share of spend and of creative volume. Writing those down first is what determined the warehouse model, because every one of them is a grouping by a creative attribute rather than by a campaign.
Starting from the questions stops the model being a mirror of the API's own shape, which is organised around campaigns rather than creatives.
Extract ad-level insights for the previous day
The script authenticates with the Business SDK and pulls insights at ad level, per day, across every configured ad account, two accounts in parallel. Ad-level daily grain is the finest grain the dashboard needs and the coarsest that still permits lifetime aggregation per creative later. Accounts are processed in parallel but modestly, because the concurrency budget is better spent on the per-ad creative lookups.
Parse conversions out of the nested action arrays
Meta returns conversions as arrays of action objects rather than columns, and the action type that carries a value differs by account configuration, initiated checkout, for instance, arrives under either the omni action type or the pixel-specific one. The parser reads each metric from the action types that can carry it, and converts video average play time from milliseconds to seconds. That conversion is the sort of thing that produces a plausible-looking chart that is wrong by three orders of magnitude.
Reading several possible action types per metric keeps a change in account configuration from silently zeroing a conversion column.
Enrich each ad with its creative permalink and thumbnail
Neither the permalink nor the thumbnail is in the insights payload, so each ad needs its own Graph API lookup. These run asynchronously, thirty at a time, and the permalink is rebuilt from the effective object story id into a stable post URL. This is what makes the creative library page possible: a reviewer sees the actual creative next to its numbers rather than an ad name.
Concurrency is capped rather than unbounded, so the enrichment pass does not itself trigger the rate limits the retry logic exists to absorb.
Retry the known-transient API failures and fill coverage gaps
Backoff is applied to a specific list of Facebook error codes (service errors, rate limits and call-volume errors, page throttles, token errors and spend-cap errors) doubling up to a sixty-second ceiling for at most five attempts. After the main pass, the script looks for dates whose coverage is incomplete and re-fetches them. An ad account with no active ads that day returns nothing legitimately, so that case is logged and skipped rather than retried.
A named retry list means an unrecognised error still fails visibly, instead of being swallowed by a blanket retry and leaving an empty day behind.
Stage to Parquet, then append to the warehouse
Rows are written to a compressed Parquet staging file per account per day and loaded into one BigQuery table in append mode. Staging first means a partial API failure never leaves a half-written load job, and the file is a concrete artefact to inspect when a number looks wrong. Append-only keeps the load path simple; the cost is that a re-run for the same day duplicates rows, which the modelling layer and a documented delete-before-re-run step both handle.
Staging separates 'the API gave us bad data' from 'the load failed', which are different problems with different fixes.
Model everything in one view
A single BigQuery view does the modelling in five stages: deduplicate creative URLs per ad name, cast every stored string metric to a number and split the ad name into its thirteen positions, aggregate lifetime spend, revenue, purchases, ROAS and CPA per ad name, find each creative id's first-ever launch date, then join those together. Keeping this in one replaceable object is why four separate casting and field-mapping corrections were deployed without backfilling any stored data.
Deduplicating in the model absorbs the duplicate rows that an append-only load produces if a day is re-run.
Derive the winner flag and the creative label
Two derived columns carry most of the dashboard's meaning. The winner flag is a stated rule, lifetime spend at or above the spend floor and lifetime ROAS at or above the ROAS floor, so win rate is comparable across any grouping. The creative label distinguishes a new concept from a variation, detected by the dot suffix in the variation field, from a revived creative, detected by the same creative id relaunching after its own first launch date. Those three categories are what makes testing velocity measurable.
Because the labels are columns rather than chart-level formulas, no two pages of the dashboard can disagree about what counts as a winner.
Build the dashboard on calculated fields, not per-chart formulas
Ten calculated fields are defined once at the data source: win rate, CTR, thumb-stop and hook rate, completion rate, ROAS, CPA and the three creative-label shares. The five pages then reuse them. Filter controls for date, angle, product, creative type, market, winner status and creative label sit in every page header so any chart can be re-cut without editing it.
One definition per metric at the source removes the most common dashboard failure: two charts that claim to show the same number and do not.
Hand over the schedule, the backfill path and the hardening list
The handover documents the daily schedule as either a cron entry or an n8n schedule trigger calling the script, the edit required to backfill an arbitrary date range, the delete-before-re-run step that prevents duplicate rows, and a troubleshooting table mapping the failures that actually occur to their causes. It also names the outstanding hardening item plainly: the API credentials and service-account key are inline in the script and belong in environment variables or a secret manager before the script travels any further.
Documenting the known failure modes and the credential debt is what keeps the pipeline maintainable by someone who did not build it.
What changed
| Aspect | Before | After |
|---|---|---|
| Creative dimensions | Angle, product, strategist and creative type existed only inside the ad name as text. | Parsed into thirteen columns in the view, so every chart can group by them. |
| Defining a winner | A judgement call made per ad, per conversation. | One stated rule in SQL, lifetime spend and lifetime ROAS thresholds, applied identically to every ad. |
| Creative velocity | Nobody could say how many new creatives versus variations went out in a week. | New, variation and revived are labelled in the view and counted by launch week. |
| Reviewing the actual creative | Finding the ad in Ads Manager to see what the creative was. | Thumbnail and permalink carried on the row, rendered inline in the creative library page. |
| Reporting data gathering | Export, paste, reconcile, repeat for each ad account. | A scheduled script appends every account's rows to one table before the working day starts. |
What it runs on
Every tool listed with the job it actually does in this build.
Integration
Meta Marketing API
Source of ad-level daily insights: spend, clicks, impressions and the nested action arrays that carry conversions and video metrics.
Facebook Graph API
Second, per-ad lookup for the creative permalink and thumbnail, which the insights payload does not include.
Platform
Python (Business SDK, aiohttp)
The extraction itself, authentication, paging, action-array parsing, retry and backoff, and the concurrent creative enrichment.
Data
Parquet (zstd)
Staging format between the API and the warehouse, so a partial failure leaves an inspectable file rather than a half-written load job.
BigQuery table
Append-only store at one row per ad per day, with numeric fields held as strings so an unexpected API value does not reject the load.
BigQuery view
The whole modelling layer in one replaceable object: dedup, casting, naming-convention parse, lifetime aggregates, winner flag and creative label.
Delivery
Looker Studio
Five-page creative dashboard over the single view, with ten metric definitions held at the data source rather than in charts.
Orchestration
Cron or n8n schedule
Runs the extraction daily for the previous day; n8n adds success and failure notification around the same command.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
Splitting the ad naming convention into reporting dimensions
Replaces the manual act that made the convention useless: reading an ad name and mentally extracting the angle, product, strategist and launch date before any grouping was possible. Thirteen positions become thirteen columns.
sql
-- Illustrative. One split, done once, in the view.
WITH daily AS (
SELECT
Ad_Name,
SPLIT(Ad_Name, ' _ ')[SAFE_OFFSET(0)] AS awareness,
SPLIT(Ad_Name, ' _ ')[SAFE_OFFSET(2)] AS creative_id,
SPLIT(Ad_Name, ' _ ')[SAFE_OFFSET(3)] AS creative_variation,
SPLIT(Ad_Name, ' _ ')[SAFE_OFFSET(7)] AS angle,
SPLIT(Ad_Name, ' _ ')[SAFE_OFFSET(8)] AS product,
SAFE.PARSE_DATE('%Y-%m-%d',
SPLIT(Ad_Name, ' _ ')[SAFE_OFFSET(9)]) AS ad_launch_date,
-- stored permissively, cast in the model
SAFE_CAST(amount_spent AS FLOAT64) AS spend,
SAFE_CAST(Impressions AS FLOAT64) AS impressions
FROM `project.dataset.ads_daily`
)
SELECT * FROM daily;One stated definition of a winning creative
Replaces a judgement call. Previously each person decided what 'worked' meant, so win rate could not be compared between angles, products or weeks. Now it is a rule applied identically to every ad.
sql
-- Illustrative. Lifetime aggregates per creative, then the rule.
WITH lifetime AS (
SELECT
Ad_Name,
SUM(spend) AS lifetime_spend,
SUM(revenue) AS lifetime_revenue,
SUM(purchases) AS lifetime_purchases,
SAFE_DIVIDE(SUM(revenue), SUM(spend)) AS lifetime_roas,
SAFE_DIVIDE(SUM(spend), SUM(purchases)) AS lifetime_cpa
FROM daily
GROUP BY Ad_Name
)
SELECT
*,
lifetime_spend >= 1000 AND lifetime_roas >= 2.0 AS is_winner
FROM lifetime;Backoff on the API errors that are actually transient
Replaces a person re-running a failed export and hoping. Rate limits and throttles are retried with widening gaps; anything not on the list still fails loudly, so an empty day cannot pass for a successful one.
python
# Illustrative. Named codes only: service errors, rate limits,
# page throttles, token and spend-cap errors.
RETRYABLE = {1, 2, 4, 17, 32, 190, 613}
MAX_ATTEMPTS = 5
def fetch_with_backoff(call):
for attempt in range(MAX_ATTEMPTS):
try:
return call()
except FacebookRequestError as err:
if err.api_error_code() not in RETRYABLE:
raise # unrecognised: fail visibly
time.sleep(min(2 ** attempt, 60))
raise RuntimeError("retries exhausted")What it was worth
The thing this pipeline actually changed is what a creative review meeting can discuss. Before it, the available facts were spend and return per account, which lead to conversations about budget. After it, the facts are win rate per angle, spend share per product, and how many new concepts versus variations went out last week, which lead to conversations about what to brief next. That is a different meeting, and it is the whole point of parsing the naming convention.
The second effect is that the naming convention started paying for itself. Thirteen fields per ad is real discipline to maintain, and before the parse it bought the team nothing but tidy-looking ad names. It is now the dimensional model of the dashboard, which also means the convention's own quality becomes visible: a mis-typed angle shows up as its own category rather than disappearing into a string.
The separation between the collecting script and the modelling view is what made the build maintainable rather than a one-off. Four corrections to casting and field mapping (including link clicks, which had been reading the general clicks field instead of inline link clicks) were all deployed by replacing a view, with no backfill and no change to collection. A pipeline where fixing a metric means reprocessing history is a pipeline nobody fixes.
Two honest limits. No before-and-after performance uplift was measured, so none is claimed here: the deliverable is the measurement capability, not a demonstrated change in ROAS. And the winner thresholds are the thresholds this team agreed; they are a convention, not a law. What matters is that the convention is written down in one place and applied to every ad identically.
Creative dimensions parsed per ad
Awareness, campaign, creative id, variation, strategist, type, brief, angle, product, launch date, market, editor and CTA, read from the ad name.
Extraction cadence
Yesterday's ad-level insights across every configured ad account, with a gap check that re-fetches incomplete dates.
Warehouse grain
Fine enough for every dashboard question and coarse enough to aggregate to lifetime metrics per creative.
The whole modelling layer
Dedup, cast and name parse, lifetime aggregates, first launch date, then the join that adds the winner flag and creative label.
Creative enrichment lookups
Permalink and thumbnail are separate per-ad Graph API calls, so they run asynchronously with a capped concurrency.
The agreed winner rule
A stated threshold on lifetime figures, which is what makes win rate comparable across angles, products and weeks.
Dashboard surface over one view
Overview, winner analysis, angle and product, weekly velocity, and a creative library with inline thumbnails.
Handed over
- A daily Python extraction pulling ad-level insights for the previous day across every configured ad account.
- Conversion parsing out of Meta's nested action arrays (purchases and purchase value, initiated checkout, add-to-cart) tolerant of the alternate action types different account configurations use.
- Video metric handling including three-second plays, completions and average play time, converted from milliseconds to seconds.
- Asynchronous creative enrichment at thirty concurrent Graph API lookups, adding a stable post permalink and thumbnail to every row.
- Exponential backoff over a named list of seven Facebook error codes, capped at sixty seconds and five attempts.
- A coverage gap check that re-fetches incomplete dates, and a logged-and-skipped path for accounts with no active ads that day.
- Compressed Parquet staging and an append load into a single BigQuery table at one row per ad per day.
- A five-CTE BigQuery view carrying the naming-convention parse, lifetime aggregates, the winner flag and the new / variation / revived creative label.
- A five-page Looker Studio dashboard (overview, winner analysis, angle and product performance, weekly velocity, and a thumbnail creative library) with ten metric definitions held at the data source.
- Handover documentation: the daily schedule, the backfill edit, the delete-before-re-run step, a troubleshooting table of real failure modes, and the outstanding credential-hardening item.
Questions about this build
What teams usually ask when they recognise their own process here.
Because the information is already in the ad name and nowhere else. The team encodes thirteen attributes there as a matter of discipline (angle, product, strategist, creative type, launch date and the rest), and Meta's own campaign structure does not carry any of it. Asking them to re-enter the same metadata into a labelling system would add work and create a second source of truth that drifts. Parsing the string they already maintain costs one SQL split and changes nothing about how ads are launched.
So that an unexpected value from the API lands in the warehouse instead of rejecting the load, and so that a casting mistake is a view replacement rather than a backfill. That is not theoretical here: four separate cast and field-mapping errors were found and corrected after launch, including link clicks reading the wrong source field, and every one of them was fixed by replacing the view while the stored data stayed untouched. The cost is that every query goes through the view, which is the intended behaviour anyway.
Two things, at different levels. The modelling view deduplicates per ad name, so the dashboard is not double-counting creative URLs. And the operating documentation specifies deleting the day's rows before deliberately re-running it, which is the honest answer for an append-only load: the write path is kept simple and the duplicate case is handled explicitly rather than by a merge that has to be right every time.
A winner is an ad whose lifetime spend has reached the agreed spend floor and whose lifetime ROAS is at or above the agreed return floor, currently 1,000 and 2.0. Both are in one place in the view, so changing them is a single edit that applies everywhere at once. The spend floor matters as much as the return floor: without it, an ad that spent very little at a high return would be counted as a winner, which would make win rate meaningless.
A revived creative is the same creative id launching again after its own first-ever launch date, a relaunch of a concept rather than a new one or a variation of one. Tracking it separately is what makes testing velocity honest. A team that launches forty ads a week looks productive until you see that most of them are variations and relaunches; splitting new, variation and revived is the only way to know whether the programme is actually generating new concepts.
Credential handling first, and it is documented as outstanding: the API token, app secret and service-account key sit inline in the script and belong in environment variables or a secret manager. After that, two structural changes, a load that replaces a day's partition rather than appending to it, which removes the delete-before-re-run step, and an explicit freshness check that alerts when a day is missing rather than relying on someone noticing a flat line in a chart.
The shape does. The valuable parts are platform-independent: a daily extraction at the finest grain the reporting needs, permissive storage with modelling in a replaceable view, one stated definition of success, and creative attributes as columns rather than strings. What does not carry is the specifics, each platform buries conversions in its own structure and has its own set of transient error codes, so the parsing and retry list are written per platform.
Capabilities this used
Read next
All product & gtm analytics workHave a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.