Pinterest Ads to BigQuery is a data integration workflow that moves Pinterest advertising data into BigQuery for storage, SQL analysis and reporting. Use a managed ETL connector for recurring reports, or build a custom Pinterest API pipeline when you need control over fields, attribution and table design. Pinterest's advertising and organic analytics APIs provide more than 90 metrics.
Google's native BigQuery Data Transfer Service source list does not include Pinterest Ads, so a direct first-party transfer is not the usual route. You need a third-party connector, custom code or a manual CSV upload.
| Requirement | Recommended method | Why |
|---|---|---|
| Recurring marketing reports | Managed connector | Faster setup with less maintenance |
| Multiple ad accounts or platforms | Managed ETL platform | Centralises scheduled ingestion |
| Custom fields, attribution logic or schema | Pinterest API pipeline | Gives you control over the data model |
| One-off historical export | Pinterest CSV plus BigQuery upload | Simple, but not automated |
| Near-real-time operational data | Custom API architecture | Gives you more control over refresh and ingestion |
For most marketing and analytics teams, a managed connector is the practical starting point. Use a custom API pipeline when the connector does not support your metrics, security requirements or attribution rules.
Pinterest's Ads Reporting API supports performance data for advertisers, campaigns, ad groups, ads, product groups, keywords and targeting breakdowns. Common fields include:
Pinterest reports spend in micro currency units through fields such as SPEND_IN_MICRO_DOLLAR. One currency unit equals 1,000,000 micro currency units, so divide the value by 1,000,000 before displaying it as normal currency.
A managed connector authenticates with Pinterest, extracts the selected reports, transforms the response and loads the data into BigQuery on a schedule. Several third-party platforms advertise Pinterest Ads to BigQuery workflows with options for table selection, field selection and scheduled refreshes.
Confirm that the connector supports the reports you need before choosing one. Some tools may include campaign and ad performance but omit targeting, product group or conversion breakdowns.
Check the following:
A connector is convenient, but its schema becomes a dependency. Store the imported data in raw tables before applying transformations. This gives you an original copy to inspect if the connector changes a field or maps a value incorrectly.
A custom pipeline gives you control over authentication, report definitions, retries, historical backfills and table design.
Pinterest API authentication requires the appropriate advertising permissions. The ads:read scope allows an application to read advertising data, including ad accounts, campaigns, ad groups and ads. The user or business must also have access to the relevant Pinterest advertising account.
Pinterest Ads API
|
v
Cloud Run, Cloud Functions or another scheduled worker
|
v
Cloud Storage staging files
|
v
BigQuery raw tables
|
v
BigQuery staging models
|
v
Reporting and marketing marts
The extraction process should:
Pinterest recommends synchronous endpoints for small requests and asynchronous endpoints for larger or more complex reports. The API also applies rate limits by endpoint category, so include backoff, retries and request monitoring in the pipeline.
Use separate raw, staging and reporting layers.
| Layer | Example table | Purpose |
|---|---|---|
| Raw | raw_pinterest_ads_report | Preserve the API or connector output |
| Staging | stg_pinterest_ads_report | Standardise names, types and currencies |
| Entity | dim_pinterest_campaign | Store campaign and ad hierarchy |
| Reporting | mart_pinterest_ads_performance | Create analysis-ready metrics |
A useful reporting grain is:
One row per reporting period, ad account, campaign, ad group, ad and targeting breakdown.
Do not mix different grains in one table. For example, campaign-level and ad-level data should not share a table unless the table includes a clear reporting_level field.
Recommended technical fields include:
ad_account_idcampaign_idad_group_idad_idreporting_datereporting_timezonecurrency_codeattribution_configurationsource_loaded_atsource_file_idextract_run_idKeep Pinterest entity IDs even when names are available. Campaign and ad names can change, while IDs provide stable join keys.
The exact column names depend on the connector. Pinterest's API uses uppercase metric names, while connectors may convert them to lowercase.
SELECT
reporting_date,
ad_account_id,
campaign_id,
SUM(impressions) AS impressions,
SUM(outbound_clicks) AS outbound_clicks,
SUM(SPEND_IN_MICRO_DOLLAR) / 1000000 AS spend
FROM
`project_id.pinterest_ads.ad_report`
GROUP BY
reporting_date,
ad_account_id,
campaign_id
ORDER BY
reporting_date,
spend DESC;
Recalculate ratios from aggregated totals instead of adding or averaging row-level percentages.
SELECT
campaign_id,
SUM(impressions) AS impressions,
SUM(outbound_clicks) AS outbound_clicks,
SAFE_DIVIDE(SUM(outbound_clicks), SUM(impressions)) AS outbound_click_rate,
SUM(SPEND_IN_MICRO_DOLLAR) / 1000000 AS spend
FROM
`project_id.pinterest_ads.ad_report`
GROUP BY
campaign_id;
This calculates the campaign-level click rate from total clicks and total impressions, rather than averaging percentages from individual ads.
For a one-off export, download a Pinterest Ads report from Ads Manager and upload the file to BigQuery.
BigQuery supports loading local CSV files through the Google Cloud console. Select the file, choose CSV as the format, define the destination dataset and table, and provide or infer the schema.
This method works when:
Manual exports do not suit ongoing dashboards because every update requires another download and upload.
Pinterest can return spend in micro currency units. Convert the value once in staging and retain the original field for auditability.
Pinterest conversion totals depend on the report's attribution configuration. Keep attribution windows, conversion report time and report granularity consistent between loads. Otherwise, the same campaign can show different results across tables.
Store the Pinterest reporting timezone with the data. Do not silently convert reporting periods to the BigQuery project timezone.
Use a deterministic key such as:
ad_account_id +
reporting_date +
campaign_id +
ad_group_id +
ad_id +
breakdown
Merge new data into the reporting table instead of appending every extraction result.
Conversion metrics can change after the original ad delivery period. Re-extract a rolling set of recent reporting periods rather than loading only new records.
Pinterest applies different rate limits to advertising analytics and advertising read endpoints. Add retries and backoff to custom integrations, especially when loading multiple ad accounts.
Use a managed Pinterest Ads to BigQuery connector when standard reports meet your requirements. Before putting it into regular use:
Choose a custom Pinterest API pipeline when you need unsupported metrics, custom attribution logic, strict data residency controls or one ingestion framework for several advertising platforms. Use manual CSV uploads for one-time transfers and initial testing.