Integration of Pinterest Ads with BigQuery

Connect Pinterest Ads to Google BigQuery with WitCloud and collect daily account, campaign, ad group and ad performance data.

Updated

Introduction

This module connects to the Pinterest Ads API and collects daily performance reports (spend, impressions, clicks, engagement, conversions and video views) at four levels: account, campaign, ad group and ad. The data is stored in Google Cloud BigQuery, in your project's dataset.

Configuration guide

Follow the steps below to connect Pinterest Ads with BigQuery. Once the setup is complete, your ad data is transferred to your BigQuery project automatically.

You need a Pinterest account with access to the ad accounts you want to connect. Only the ad accounts that your Pinterest login can see appear in the list.

Module setup

Go to Integrations in the menu and click Add integration.

Integrations list with the Add integration button

Select Pinterest Ads from the list of available integrations.

Integration catalog filtered to Pinterest Ads

Enter the Collect name, e.g. Pinterest Ads PL. A good name helps you recognize the data source later.

Collect name field filled in with the example name Pinterest Ads PL

Authorization

If you have already authorized Pinterest in this project, pick that authorization from the Select Pinterest authorization drop-down and skip the authorization step.

Click Authorize.

Authorize button next to the Select Pinterest authorization field

A Pinterest window opens. Log in and allow WitCloud to access your account. WitCloud asks for read-only access: it can read your ad data and the list of your ad accounts, and it cannot change anything in Pinterest. When the window closes, the new authorization is selected for you. If you pick an existing authorization, the button changes to Re-authorize.

Select accounts

Open the Select accounts field and choose one or more ad accounts. The field stays disabled until an authorization is selected.

Select accounts drop-down open with example Pinterest ad accounts

You can select several accounts at once. Each selected account becomes its own data collect, named "your collect name - the account name", with the same settings.

Select accounts field with two example Pinterest ad accounts selected

Advanced settings

Click Show advanced settings to open the Tables to collect field. All four tables are selected by default (account, campaigns, ad_groups, ads). Remove the ones you do not need to shorten data retrieval. At least one table must stay selected.

Advanced settings open with the Tables to collect field

Click Next.

Historical data and scheduling

In the Historical Data field, enter the number of days to fetch, counted back from today. The maximum is 365 days.

Historical Data field filled in with 365 days

A data synchronization schedule is created automatically. More information on this topic can be found here.

Click Create to complete the configuration.

Data collection settings with the Create button

Done! Your Pinterest Ads data is now synchronized with BigQuery, one data collect per selected account.

Data schema

Each run collects the reports for one day. The tables below hold the data of one ad account.

Data schema for pinterest_account
Field NameModeType
dateNULLABLEDATE
account_idNULLABLESTRING
account_nameNULLABLESTRING
spend_in_dollarNULLABLEFLOAT
cpm_in_dollarNULLABLEFLOAT
ecpc_in_dollarNULLABLEFLOAT
total_impressionNULLABLEINTEGER
total_impression_userNULLABLEINTEGER
total_impression_frequencyNULLABLEFLOAT
total_clickthroughNULLABLEINTEGER
ctrNULLABLEFLOAT
outbound_clickNULLABLEINTEGER
outbound_click_rateNULLABLEFLOAT
cost_per_outbound_click_in_dollarNULLABLEFLOAT
outbound_click_1NULLABLEINTEGER
outbound_click_2NULLABLEINTEGER
total_engagementNULLABLEINTEGER
engagement_rateNULLABLEFLOAT
repin_1NULLABLEINTEGER
repin_2NULLABLEINTEGER
total_repin_rateNULLABLEFLOAT
total_page_visitNULLABLEINTEGER
total_signupNULLABLEINTEGER
total_checkoutNULLABLEINTEGER
total_leadNULLABLEINTEGER
total_conversionsNULLABLEINTEGER
total_conversions_value_in_dollarNULLABLEFLOAT
page_visit_roasNULLABLEFLOAT
checkout_roasNULLABLEFLOAT
custom_roasNULLABLEFLOAT
web_checkout_roasNULLABLEFLOAT
total_web_checkoutNULLABLEINTEGER
total_web_checkout_value_in_dollarNULLABLEFLOAT
video_spend_in_dollarNULLABLEFLOAT
total_video_3sec_viewsNULLABLEINTEGER
total_video_mrc_viewsNULLABLEINTEGER
total_video_p0_combinedNULLABLEINTEGER
total_video_p25_combinedNULLABLEINTEGER
total_video_p50_combinedNULLABLEINTEGER
total_video_p75_combinedNULLABLEINTEGER
total_video_p100_completeNULLABLEINTEGER
total_video_avg_watchtime_in_secondNULLABLEFLOAT
video_lengthNULLABLEINTEGER
cpv_in_dollarNULLABLEFLOAT
cpcv_in_dollarNULLABLEFLOAT
ecpv_in_dollarNULLABLEFLOAT
ecpcv_in_dollarNULLABLEFLOAT
Data schema for pinterest_campaigns
Field NameModeType
dateNULLABLEDATE
campaign_idNULLABLESTRING
campaign_nameNULLABLESTRING
campaign_objective_typeNULLABLESTRING
campaign_entity_statusNULLABLESTRING
campaign_budget_optimizationNULLABLESTRING
spend_in_dollarNULLABLEFLOAT
cpm_in_dollarNULLABLEFLOAT
ecpc_in_dollarNULLABLEFLOAT
total_impressionNULLABLEINTEGER
total_impression_userNULLABLEINTEGER
total_impression_frequencyNULLABLEFLOAT
total_clickthroughNULLABLEINTEGER
ctrNULLABLEFLOAT
outbound_clickNULLABLEINTEGER
outbound_click_rateNULLABLEFLOAT
cost_per_outbound_click_in_dollarNULLABLEFLOAT
outbound_click_1NULLABLEINTEGER
outbound_click_2NULLABLEINTEGER
total_engagementNULLABLEINTEGER
engagement_rateNULLABLEFLOAT
repin_1NULLABLEINTEGER
repin_2NULLABLEINTEGER
total_repin_rateNULLABLEFLOAT
total_page_visitNULLABLEINTEGER
total_signupNULLABLEINTEGER
total_checkoutNULLABLEINTEGER
total_leadNULLABLEINTEGER
total_conversionsNULLABLEINTEGER
total_conversions_value_in_dollarNULLABLEFLOAT
page_visit_roasNULLABLEFLOAT
checkout_roasNULLABLEFLOAT
custom_roasNULLABLEFLOAT
web_checkout_roasNULLABLEFLOAT
total_web_checkoutNULLABLEINTEGER
total_web_checkout_value_in_dollarNULLABLEFLOAT
video_spend_in_dollarNULLABLEFLOAT
total_video_3sec_viewsNULLABLEINTEGER
total_video_mrc_viewsNULLABLEINTEGER
total_video_p0_combinedNULLABLEINTEGER
total_video_p25_combinedNULLABLEINTEGER
total_video_p50_combinedNULLABLEINTEGER
total_video_p75_combinedNULLABLEINTEGER
total_video_p100_completeNULLABLEINTEGER
total_video_avg_watchtime_in_secondNULLABLEFLOAT
video_lengthNULLABLEINTEGER
cpv_in_dollarNULLABLEFLOAT
cpcv_in_dollarNULLABLEFLOAT
ecpv_in_dollarNULLABLEFLOAT
ecpcv_in_dollarNULLABLEFLOAT
Data schema for pinterest_ad_groups
Field NameModeType
dateNULLABLEDATE
ad_group_idNULLABLESTRING
ad_group_nameNULLABLESTRING
campaign_idNULLABLESTRING
campaign_nameNULLABLESTRING
campaign_objective_typeNULLABLESTRING
campaign_entity_statusNULLABLESTRING
campaign_budget_optimizationNULLABLESTRING
spend_in_dollarNULLABLEFLOAT
cpm_in_dollarNULLABLEFLOAT
ecpc_in_dollarNULLABLEFLOAT
total_impressionNULLABLEINTEGER
total_impression_userNULLABLEINTEGER
total_impression_frequencyNULLABLEFLOAT
total_clickthroughNULLABLEINTEGER
ctrNULLABLEFLOAT
outbound_clickNULLABLEINTEGER
outbound_click_rateNULLABLEFLOAT
cost_per_outbound_click_in_dollarNULLABLEFLOAT
outbound_click_1NULLABLEINTEGER
outbound_click_2NULLABLEINTEGER
total_engagementNULLABLEINTEGER
engagement_rateNULLABLEFLOAT
repin_1NULLABLEINTEGER
repin_2NULLABLEINTEGER
total_repin_rateNULLABLEFLOAT
total_page_visitNULLABLEINTEGER
total_signupNULLABLEINTEGER
total_checkoutNULLABLEINTEGER
total_leadNULLABLEINTEGER
total_conversionsNULLABLEINTEGER
total_conversions_value_in_dollarNULLABLEFLOAT
page_visit_roasNULLABLEFLOAT
checkout_roasNULLABLEFLOAT
custom_roasNULLABLEFLOAT
web_checkout_roasNULLABLEFLOAT
total_web_checkoutNULLABLEINTEGER
total_web_checkout_value_in_dollarNULLABLEFLOAT
video_spend_in_dollarNULLABLEFLOAT
total_video_3sec_viewsNULLABLEINTEGER
total_video_mrc_viewsNULLABLEINTEGER
total_video_p0_combinedNULLABLEINTEGER
total_video_p25_combinedNULLABLEINTEGER
total_video_p50_combinedNULLABLEINTEGER
total_video_p75_combinedNULLABLEINTEGER
total_video_p100_completeNULLABLEINTEGER
total_video_avg_watchtime_in_secondNULLABLEFLOAT
video_lengthNULLABLEINTEGER
cpv_in_dollarNULLABLEFLOAT
cpcv_in_dollarNULLABLEFLOAT
ecpv_in_dollarNULLABLEFLOAT
ecpcv_in_dollarNULLABLEFLOAT
Data schema for pinterest_ads
Field NameModeType
dateNULLABLEDATE
ad_idNULLABLESTRING
ad_nameNULLABLESTRING
pin_idNULLABLESTRING
pin_promotion_idNULLABLESTRING
ad_group_idNULLABLESTRING
ad_group_nameNULLABLESTRING
campaign_idNULLABLESTRING
campaign_nameNULLABLESTRING
campaign_objective_typeNULLABLESTRING
campaign_entity_statusNULLABLESTRING
campaign_budget_optimizationNULLABLESTRING
spend_in_dollarNULLABLEFLOAT
cpm_in_dollarNULLABLEFLOAT
ecpc_in_dollarNULLABLEFLOAT
total_impressionNULLABLEINTEGER
total_impression_userNULLABLEINTEGER
total_impression_frequencyNULLABLEFLOAT
total_clickthroughNULLABLEINTEGER
ctrNULLABLEFLOAT
outbound_clickNULLABLEINTEGER
outbound_click_rateNULLABLEFLOAT
cost_per_outbound_click_in_dollarNULLABLEFLOAT
outbound_click_1NULLABLEINTEGER
outbound_click_2NULLABLEINTEGER
total_engagementNULLABLEINTEGER
engagement_rateNULLABLEFLOAT
repin_1NULLABLEINTEGER
repin_2NULLABLEINTEGER
total_repin_rateNULLABLEFLOAT
total_page_visitNULLABLEINTEGER
total_signupNULLABLEINTEGER
total_checkoutNULLABLEINTEGER
total_leadNULLABLEINTEGER
total_conversionsNULLABLEINTEGER
total_conversions_value_in_dollarNULLABLEFLOAT
page_visit_roasNULLABLEFLOAT
checkout_roasNULLABLEFLOAT
custom_roasNULLABLEFLOAT
web_checkout_roasNULLABLEFLOAT
total_web_checkoutNULLABLEINTEGER
total_web_checkout_value_in_dollarNULLABLEFLOAT
video_spend_in_dollarNULLABLEFLOAT
total_video_3sec_viewsNULLABLEINTEGER
total_video_mrc_viewsNULLABLEINTEGER
total_video_p0_combinedNULLABLEINTEGER
total_video_p25_combinedNULLABLEINTEGER
total_video_p50_combinedNULLABLEINTEGER
total_video_p75_combinedNULLABLEINTEGER
total_video_p100_completeNULLABLEINTEGER
total_video_avg_watchtime_in_secondNULLABLEFLOAT
video_lengthNULLABLEINTEGER
cpv_in_dollarNULLABLEFLOAT
cpcv_in_dollarNULLABLEFLOAT
ecpv_in_dollarNULLABLEFLOAT
ecpcv_in_dollarNULLABLEFLOAT