DTS facebook ads to bigquery - table AdInsightsActions UNUSEFUL

[giobreccia]

Hello,
I’ve started using the native Facebook Ads to BigQuery data transfer.

The transfer populates 3 tables:

AdAccounts
AdInsights
AdInsightsActions

The AdInsightsActions table, as it is currently structured, is completely useless.

Although it provides "ActionValue" data for various attribution windows (1dClick, 7dClick, 1dView, and so on), it lacks a field that specifies which conversion action the value refers to — whether it's the primary campaign goal or other secondary actions.

Facebook campaigns can receive multiple types of conversion actions.

Therefore, performing joins or other SQL operations with this table is essentially pointless.

Is there anything that can be done?

From the official documentation, I don’t see any additional information.

When querying the traditional Facebook Ads API, I receive not only the value of each action (e.g., action1.value), but also — and crucially — what the action is (action1.type).

Would it be possible for your engineers and developers to step in and address what I believe is a serious gap in the current implementation?

In shorts, the AdInsightsActions table from the native Facebook Ads to BigQuery transfer does not include any information about the type of action, making it extremely difficult — if not impossible — to use the data meaningfully in analysis or join it reliably with other tables.

This limitation significantly reduces the usefulness of the dataset for advertisers and analysts.

[nikacalupas]

Hi giobreccia,

Welcome to the Google Cloud Community!

Currently, based on the BigQuery Data Transfer Service for Facebook Ads, it appears that the AdInsightsActions table is explicitly designed to include only the ActionValue for predefined attribution windows, without the action_type breakdown you're looking for. The specifications outline the exact fields transferred, and action_type is not listed as a mapped field for AdInsightsActions.

Here are some potential workarounds that might help:

• Leverage the AdInsights table by parsing its JSON fields. You can utilize BigQuery's JSON functions (e.g., JSON_EXTRACT_SCALAR, JSON_EXTRACT_ARRAY) to extract the specific action_type and associated values. You'll likely need to experiment to determine the exact JSON structure and write appropriate queries for extraction.
• I suggest filing a feature request on the issue tracker so that our engineers can look into it as they can provide more insights into the issue. Please provide as much information as possible. Please note that I can't provide any details or timelines at this moment. However, you may keep an eye on the release notes for any latest updates or new features related to BigQuery.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[giobreccia]

Thank you!

You mentioned JSON fields in the AdInsights table, but I actually don’t see any.

Here is the actual schema of the table created by the DTS service

field name mode type description

Target	NULLABLE	STRING
DatePreset	NULLABLE	STRING
DateStart	NULLABLE	DATE
DateEnd	NULLABLE	DATE
TimeIncrement	NULLABLE	STRING
Level	NULLABLE	STRING
AccountCurrency	NULLABLE	STRING
ActionAttributionWindows	NULLABLE	STRING
AdAccountId	NULLABLE	STRING
AdAccountName	NULLABLE	STRING
CampaignId	NULLABLE	STRING
CampaignName	NULLABLE	STRING
AdSetId	NULLABLE	STRING
AdSetName	NULLABLE	STRING
AdId	NULLABLE	STRING
AdName	NULLABLE	STRING
BuyingType	NULLABLE	STRING
Clicks	NULLABLE	BIGNUMERIC
ConversionRateRanking	NULLABLE	STRING
CostPerEstimatedAdRecallers	NULLABLE	BIGNUMERIC
CostPerInlineLinkClick	NULLABLE	BIGNUMERIC
CostPerInlinePostEngagement	NULLABLE	BIGNUMERIC
CostPerUniqueClick	NULLABLE	BIGNUMERIC
CostPerUniqueInlineLinkClick	NULLABLE	BIGNUMERIC
CPC	NULLABLE	BIGNUMERIC
CPM	NULLABLE	BIGNUMERIC
CPP	NULLABLE	BIGNUMERIC
CTR	NULLABLE	FLOAT
EstimatedAdRecallRate	NULLABLE	FLOAT
EstimatedAdRecallers	NULLABLE	FLOAT
Frequency	NULLABLE	FLOAT
Impressions	NULLABLE	BIGNUMERIC
InlineLinkClicks	NULLABLE	BIGNUMERIC
InlineLinkClicksCounter	NULLABLE	FLOAT
InlinePostEngagement	NULLABLE	BIGNUMERIC
InstantExperienceClicksToOpen	NULLABLE	BIGNUMERIC
InstantExperienceClicksToStart	NULLABLE	BIGNUMERIC
InstantExperienceOutboundClicks	NULLABLE	BIGNUMERIC
Objective	NULLABLE	STRING
QualityRanking	NULLABLE	STRING
Reach	NULLABLE	BIGNUMERIC
Spend	NULLABLE	BIGNUMERIC
UniqueClicks	NULLABLE	BIGNUMERIC
UniqueCTR	NULLABLE	FLOAT
UniqueInlineLinkClicks	NULLABLE	BIGNUMERIC
UniqueInlineLinkClickCounter	NULLABLE	FLOAT
UniqueLinkClicksCounter	NULLABLE	FLOAT
Checkins	NULLABLE	INTEGER
EventResponses	NULLABLE	INTEGER
LinkClicks	NULLABLE	INTEGER
OfferSaves	NULLABLE	INTEGER
OutboundClicks	NULLABLE	INTEGER
PageEngagements	NULLABLE	INTEGER
PageLikes	NULLABLE	INTEGER
PageMentions	NULLABLE	INTEGER
PagePhotoViews	NULLABLE	INTEGER
PostComments	NULLABLE	INTEGER
PostEngagements	NULLABLE	INTEGER
PostShares	NULLABLE	INTEGER
PostReactions	NULLABLE	INTEGER
PageTabViews	NULLABLE	INTEGER
Video3SecondViews	NULLABLE	INTEGER
Region	NULLABLE	STRING
AdEffectiveStatus	NULLABLE	STRING
UseAsync	NULLABLE	BOOLEAN
DefaultSummary	NULLABLE	BOOLEAN

By the way I will open a request on the issue tracker.

Thanks a lot!