Circle Separator5 mins read

How Do You Export Pinterest Ads to BigQuery?

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.

Choose the Right Pinterest Ads to BigQuery Method

RequirementRecommended methodWhy
Recurring marketing reportsManaged connectorFaster setup with less maintenance
Multiple ad accounts or platformsManaged ETL platformCentralises scheduled ingestion
Custom fields, attribution logic or schemaPinterest API pipelineGives you control over the data model
One-off historical exportPinterest CSV plus BigQuery uploadSimple, but not automated
Near-real-time operational dataCustom API architectureGives 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.

What Pinterest Ads Data Can You Load Into BigQuery?

Pinterest's Ads Reporting API supports performance data for advertisers, campaigns, ad groups, ads, product groups, keywords and targeting breakdowns. Common fields include:

  • Ad account ID
  • Campaign ID and name
  • Ad group ID and name
  • Ad or promoted Pin ID
  • Reporting date or period
  • Impressions
  • Pin clicks
  • Outbound clicks
  • Saves
  • Video views
  • Spend
  • Conversions
  • Conversion value
  • Return on ad spend
  • Targeting type and value
  • Country, region or metro breakdown
  • Reporting timezone
  • Attribution settings

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.

Option One: Use a Managed Pinterest Ads Connector

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.

Setup Process

  1. Create or select a Google Cloud project.
  2. Enable BigQuery and create a destination dataset.
  3. Select a connector that supports Pinterest Ads and Google BigQuery.
  4. Authenticate the Pinterest business account.
  5. Select the ad accounts, reports, dimensions and metrics.
  6. Authenticate the BigQuery destination.
  7. Choose a refresh schedule.
  8. Run an initial historical load.
  9. Compare totals with Pinterest Ads Manager.
  10. Build reporting tables or dashboards from the loaded data.

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:

  • Available Pinterest Ads reports
  • Supported historical range
  • Ad account and business access
  • Conversion metrics
  • Attribution settings
  • Currency handling
  • Timezone handling
  • Incremental loading
  • Backfills
  • Schema change handling
  • BigQuery dataset and table naming
  • Pricing and data-volume limits

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.

Option Two: Build a Custom Pinterest API to BigQuery Pipeline

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:

  1. Retrieve the Pinterest ad accounts available to the authorised user.
  2. Request the required campaign, ad group or ad report.
  3. Use asynchronous reporting for large exports or broad historical ranges.
  4. Poll until Pinterest finishes generating the report.
  5. Download the CSV or JSON response.
  6. Store the original response in Cloud Storage.
  7. Load the response into a raw BigQuery table.
  8. Deduplicate and merge records into a reporting table.
  9. Reprocess recent reporting periods to capture late-arriving conversions.

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.

How Should You Design the BigQuery Tables?

Use separate raw, staging and reporting layers.

LayerExample tablePurpose
Rawraw_pinterest_ads_reportPreserve the API or connector output
Stagingstg_pinterest_ads_reportStandardise names, types and currencies
Entitydim_pinterest_campaignStore campaign and ad hierarchy
Reportingmart_pinterest_ads_performanceCreate 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_id
  • campaign_id
  • ad_group_id
  • ad_id
  • reporting_date
  • reporting_timezone
  • currency_code
  • attribution_configuration
  • source_loaded_at
  • source_file_id
  • extract_run_id

Keep Pinterest entity IDs even when names are available. Campaign and ad names can change, while IDs provide stable join keys.

Example BigQuery Query for Pinterest Ads Performance

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.

Manual CSV Export for a One-Time Transfer

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:

  • You need a one-time historical snapshot.
  • The report will not be refreshed.
  • You are testing which Pinterest fields you need.
  • You want to check a connector before automating the workflow.

Manual exports do not suit ongoing dashboards because every update requires another download and upload.

Important Pinterest Ads and BigQuery Data Risks

Spend Units

Pinterest can return spend in micro currency units. Convert the value once in staging and retain the original field for auditability.

Attribution Settings

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.

Timezones

Store the Pinterest reporting timezone with the data. Do not silently convert reporting periods to the BigQuery project timezone.

Duplicate Records

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.

Late Conversions

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.

API Rate Limits

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:

  1. Confirm the available Pinterest reports and metrics.
  2. Load campaign, ad group and ad-level data.
  3. Preserve the raw source tables.
  4. Convert micro currency into standard currency in staging.
  5. Store attribution and timezone metadata.
  6. Reprocess recent reporting periods.
  7. Compare spend and impressions with Pinterest Ads Manager.
  8. Build BigQuery marts for campaign, creative and conversion analysis.

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.

Next Read