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.

Select Pinterest Ads from the list of available integrations.

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

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.

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.

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.

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.

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.

A data synchronization schedule is created automatically. More information on this topic can be found here.
Click Create to complete the configuration.

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 Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| account_id | NULLABLE | STRING |
| account_name | NULLABLE | STRING |
| spend_in_dollar | NULLABLE | FLOAT |
| cpm_in_dollar | NULLABLE | FLOAT |
| ecpc_in_dollar | NULLABLE | FLOAT |
| total_impression | NULLABLE | INTEGER |
| total_impression_user | NULLABLE | INTEGER |
| total_impression_frequency | NULLABLE | FLOAT |
| total_clickthrough | NULLABLE | INTEGER |
| ctr | NULLABLE | FLOAT |
| outbound_click | NULLABLE | INTEGER |
| outbound_click_rate | NULLABLE | FLOAT |
| cost_per_outbound_click_in_dollar | NULLABLE | FLOAT |
| outbound_click_1 | NULLABLE | INTEGER |
| outbound_click_2 | NULLABLE | INTEGER |
| total_engagement | NULLABLE | INTEGER |
| engagement_rate | NULLABLE | FLOAT |
| repin_1 | NULLABLE | INTEGER |
| repin_2 | NULLABLE | INTEGER |
| total_repin_rate | NULLABLE | FLOAT |
| total_page_visit | NULLABLE | INTEGER |
| total_signup | NULLABLE | INTEGER |
| total_checkout | NULLABLE | INTEGER |
| total_lead | NULLABLE | INTEGER |
| total_conversions | NULLABLE | INTEGER |
| total_conversions_value_in_dollar | NULLABLE | FLOAT |
| page_visit_roas | NULLABLE | FLOAT |
| checkout_roas | NULLABLE | FLOAT |
| custom_roas | NULLABLE | FLOAT |
| web_checkout_roas | NULLABLE | FLOAT |
| total_web_checkout | NULLABLE | INTEGER |
| total_web_checkout_value_in_dollar | NULLABLE | FLOAT |
| video_spend_in_dollar | NULLABLE | FLOAT |
| total_video_3sec_views | NULLABLE | INTEGER |
| total_video_mrc_views | NULLABLE | INTEGER |
| total_video_p0_combined | NULLABLE | INTEGER |
| total_video_p25_combined | NULLABLE | INTEGER |
| total_video_p50_combined | NULLABLE | INTEGER |
| total_video_p75_combined | NULLABLE | INTEGER |
| total_video_p100_complete | NULLABLE | INTEGER |
| total_video_avg_watchtime_in_second | NULLABLE | FLOAT |
| video_length | NULLABLE | INTEGER |
| cpv_in_dollar | NULLABLE | FLOAT |
| cpcv_in_dollar | NULLABLE | FLOAT |
| ecpv_in_dollar | NULLABLE | FLOAT |
| ecpcv_in_dollar | NULLABLE | FLOAT |
Data schema for pinterest_campaigns
| Field Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| campaign_id | NULLABLE | STRING |
| campaign_name | NULLABLE | STRING |
| campaign_objective_type | NULLABLE | STRING |
| campaign_entity_status | NULLABLE | STRING |
| campaign_budget_optimization | NULLABLE | STRING |
| spend_in_dollar | NULLABLE | FLOAT |
| cpm_in_dollar | NULLABLE | FLOAT |
| ecpc_in_dollar | NULLABLE | FLOAT |
| total_impression | NULLABLE | INTEGER |
| total_impression_user | NULLABLE | INTEGER |
| total_impression_frequency | NULLABLE | FLOAT |
| total_clickthrough | NULLABLE | INTEGER |
| ctr | NULLABLE | FLOAT |
| outbound_click | NULLABLE | INTEGER |
| outbound_click_rate | NULLABLE | FLOAT |
| cost_per_outbound_click_in_dollar | NULLABLE | FLOAT |
| outbound_click_1 | NULLABLE | INTEGER |
| outbound_click_2 | NULLABLE | INTEGER |
| total_engagement | NULLABLE | INTEGER |
| engagement_rate | NULLABLE | FLOAT |
| repin_1 | NULLABLE | INTEGER |
| repin_2 | NULLABLE | INTEGER |
| total_repin_rate | NULLABLE | FLOAT |
| total_page_visit | NULLABLE | INTEGER |
| total_signup | NULLABLE | INTEGER |
| total_checkout | NULLABLE | INTEGER |
| total_lead | NULLABLE | INTEGER |
| total_conversions | NULLABLE | INTEGER |
| total_conversions_value_in_dollar | NULLABLE | FLOAT |
| page_visit_roas | NULLABLE | FLOAT |
| checkout_roas | NULLABLE | FLOAT |
| custom_roas | NULLABLE | FLOAT |
| web_checkout_roas | NULLABLE | FLOAT |
| total_web_checkout | NULLABLE | INTEGER |
| total_web_checkout_value_in_dollar | NULLABLE | FLOAT |
| video_spend_in_dollar | NULLABLE | FLOAT |
| total_video_3sec_views | NULLABLE | INTEGER |
| total_video_mrc_views | NULLABLE | INTEGER |
| total_video_p0_combined | NULLABLE | INTEGER |
| total_video_p25_combined | NULLABLE | INTEGER |
| total_video_p50_combined | NULLABLE | INTEGER |
| total_video_p75_combined | NULLABLE | INTEGER |
| total_video_p100_complete | NULLABLE | INTEGER |
| total_video_avg_watchtime_in_second | NULLABLE | FLOAT |
| video_length | NULLABLE | INTEGER |
| cpv_in_dollar | NULLABLE | FLOAT |
| cpcv_in_dollar | NULLABLE | FLOAT |
| ecpv_in_dollar | NULLABLE | FLOAT |
| ecpcv_in_dollar | NULLABLE | FLOAT |
Data schema for pinterest_ad_groups
| Field Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| ad_group_id | NULLABLE | STRING |
| ad_group_name | NULLABLE | STRING |
| campaign_id | NULLABLE | STRING |
| campaign_name | NULLABLE | STRING |
| campaign_objective_type | NULLABLE | STRING |
| campaign_entity_status | NULLABLE | STRING |
| campaign_budget_optimization | NULLABLE | STRING |
| spend_in_dollar | NULLABLE | FLOAT |
| cpm_in_dollar | NULLABLE | FLOAT |
| ecpc_in_dollar | NULLABLE | FLOAT |
| total_impression | NULLABLE | INTEGER |
| total_impression_user | NULLABLE | INTEGER |
| total_impression_frequency | NULLABLE | FLOAT |
| total_clickthrough | NULLABLE | INTEGER |
| ctr | NULLABLE | FLOAT |
| outbound_click | NULLABLE | INTEGER |
| outbound_click_rate | NULLABLE | FLOAT |
| cost_per_outbound_click_in_dollar | NULLABLE | FLOAT |
| outbound_click_1 | NULLABLE | INTEGER |
| outbound_click_2 | NULLABLE | INTEGER |
| total_engagement | NULLABLE | INTEGER |
| engagement_rate | NULLABLE | FLOAT |
| repin_1 | NULLABLE | INTEGER |
| repin_2 | NULLABLE | INTEGER |
| total_repin_rate | NULLABLE | FLOAT |
| total_page_visit | NULLABLE | INTEGER |
| total_signup | NULLABLE | INTEGER |
| total_checkout | NULLABLE | INTEGER |
| total_lead | NULLABLE | INTEGER |
| total_conversions | NULLABLE | INTEGER |
| total_conversions_value_in_dollar | NULLABLE | FLOAT |
| page_visit_roas | NULLABLE | FLOAT |
| checkout_roas | NULLABLE | FLOAT |
| custom_roas | NULLABLE | FLOAT |
| web_checkout_roas | NULLABLE | FLOAT |
| total_web_checkout | NULLABLE | INTEGER |
| total_web_checkout_value_in_dollar | NULLABLE | FLOAT |
| video_spend_in_dollar | NULLABLE | FLOAT |
| total_video_3sec_views | NULLABLE | INTEGER |
| total_video_mrc_views | NULLABLE | INTEGER |
| total_video_p0_combined | NULLABLE | INTEGER |
| total_video_p25_combined | NULLABLE | INTEGER |
| total_video_p50_combined | NULLABLE | INTEGER |
| total_video_p75_combined | NULLABLE | INTEGER |
| total_video_p100_complete | NULLABLE | INTEGER |
| total_video_avg_watchtime_in_second | NULLABLE | FLOAT |
| video_length | NULLABLE | INTEGER |
| cpv_in_dollar | NULLABLE | FLOAT |
| cpcv_in_dollar | NULLABLE | FLOAT |
| ecpv_in_dollar | NULLABLE | FLOAT |
| ecpcv_in_dollar | NULLABLE | FLOAT |
Data schema for pinterest_ads
| Field Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| ad_id | NULLABLE | STRING |
| ad_name | NULLABLE | STRING |
| pin_id | NULLABLE | STRING |
| pin_promotion_id | NULLABLE | STRING |
| ad_group_id | NULLABLE | STRING |
| ad_group_name | NULLABLE | STRING |
| campaign_id | NULLABLE | STRING |
| campaign_name | NULLABLE | STRING |
| campaign_objective_type | NULLABLE | STRING |
| campaign_entity_status | NULLABLE | STRING |
| campaign_budget_optimization | NULLABLE | STRING |
| spend_in_dollar | NULLABLE | FLOAT |
| cpm_in_dollar | NULLABLE | FLOAT |
| ecpc_in_dollar | NULLABLE | FLOAT |
| total_impression | NULLABLE | INTEGER |
| total_impression_user | NULLABLE | INTEGER |
| total_impression_frequency | NULLABLE | FLOAT |
| total_clickthrough | NULLABLE | INTEGER |
| ctr | NULLABLE | FLOAT |
| outbound_click | NULLABLE | INTEGER |
| outbound_click_rate | NULLABLE | FLOAT |
| cost_per_outbound_click_in_dollar | NULLABLE | FLOAT |
| outbound_click_1 | NULLABLE | INTEGER |
| outbound_click_2 | NULLABLE | INTEGER |
| total_engagement | NULLABLE | INTEGER |
| engagement_rate | NULLABLE | FLOAT |
| repin_1 | NULLABLE | INTEGER |
| repin_2 | NULLABLE | INTEGER |
| total_repin_rate | NULLABLE | FLOAT |
| total_page_visit | NULLABLE | INTEGER |
| total_signup | NULLABLE | INTEGER |
| total_checkout | NULLABLE | INTEGER |
| total_lead | NULLABLE | INTEGER |
| total_conversions | NULLABLE | INTEGER |
| total_conversions_value_in_dollar | NULLABLE | FLOAT |
| page_visit_roas | NULLABLE | FLOAT |
| checkout_roas | NULLABLE | FLOAT |
| custom_roas | NULLABLE | FLOAT |
| web_checkout_roas | NULLABLE | FLOAT |
| total_web_checkout | NULLABLE | INTEGER |
| total_web_checkout_value_in_dollar | NULLABLE | FLOAT |
| video_spend_in_dollar | NULLABLE | FLOAT |
| total_video_3sec_views | NULLABLE | INTEGER |
| total_video_mrc_views | NULLABLE | INTEGER |
| total_video_p0_combined | NULLABLE | INTEGER |
| total_video_p25_combined | NULLABLE | INTEGER |
| total_video_p50_combined | NULLABLE | INTEGER |
| total_video_p75_combined | NULLABLE | INTEGER |
| total_video_p100_complete | NULLABLE | INTEGER |
| total_video_avg_watchtime_in_second | NULLABLE | FLOAT |
| video_length | NULLABLE | INTEGER |
| cpv_in_dollar | NULLABLE | FLOAT |
| cpcv_in_dollar | NULLABLE | FLOAT |
| ecpv_in_dollar | NULLABLE | FLOAT |
| ecpcv_in_dollar | NULLABLE | FLOAT |
