> ## Documentation Index > Fetch the complete documentation index at: https://docs.nekt.com/llms.txt > Use this file to discover all available pages before exploring further. # Facebook Ads as a data source > Bring data from Facebook Ads to Nekt. Facebook Ads (also known as Meta Ads) is Meta's advertising platform that allows businesses to create and manage digital advertising campaigns across Facebook, Instagram, and other Meta-owned platforms. It provides tools for targeting audiences, tracking performance, and optimizing ad spend for maximum return on investment. ## Configuring Facebook Ads as a Source In the [Sources](https://app.nekt.ai/sources) tab, click on the "Add source" button located on the top right of your screen. Then, select the Facebook Ads option from the list of connectors. Click **Next** and you'll be prompted to add your access. ### 1. Add account access You'll need to authorize Nekt to access your Facebook Ads data. Click on the `Facebook Authorization` button and log in with your Facebook account. Grant the necessary permissions for the ad accounts you want to extract data from. After authentication, select the specific Ad Account for this source and define a start date for data retrieval. Facebook's API allows fetching reports up to 36 months in the past. The following configurations are available: * **Start Date**: The earliest date from which records will be synced. This should be in DD-MM-YYYY format. * **Lookback Window**: (Default: 28 days) Facebook "freezes" insight data 28 days after it's generated. This means data from the past 28 days can change. Nekt uses a lookback window to re-fetch data within this period to ensure your data is up-to-date. * **Enable Advanced Reports**: (Default: `false`) When enabled, this option unlocks additional, more granular `AdInsights` reports, broken down by dimensions like age and gender, country, device, and region. Be aware that enabling this can significantly increase extraction times due to the volume of data being processed. * **Creative Fields Mode**: (Default: `basic`) This setting controls the level of detail extracted for ad creatives. * `basic`: Extracts essential fields for faster processing and lower API usage. * `advanced`: Includes all media and configuration details, which requires more processing time by Facebook's API. * **Ad Accounts Fields Mode**: (Default: `basic`) Controls which fields to extract from the Ad Accounts stream. * `basic`: Core account fields that work with standard permissions. Use this if you experience permission errors. * `extended`: Includes additional fields like owner info, tax details, agency declarations, business manager info, and funding source details. Requires elevated (admin/finance) permissions on all connected ad accounts. You can also customize the behavior of the `AdInsights` stream using the advanced settings: * **Attribution Windows**: Define the attribution windows for view and click conversions (e.g., `1d_view`, `7d_click`). This determines how conversions are credited to your ads based on when they were seen or clicked. * **Action Report Time**: Determines how the time of an action is recorded. It can be based on `impression` time, `conversion` time, or `mixed`. Once you're done, click **Next**. ### 2. Select streams Choose which data streams you want to sync. For faster extractions, select only the streams that are relevant to your analysis. You can select entire groups of streams or pick specific ones. > Tip: The stream can be found more easily by typing its name. Select the streams and click **Next**. ### 3. Configure data streams Customize how you want your data to appear in your catalog. Select a name for each table (which will contain the fetched data) and the type of sync. * **Table name**: we suggest a name, but feel free to customize it. You have the option to add a **prefix** and make this process faster! * **Sync Type**: you can choose between INCREMENTAL and FULL\_TABLE. * Incremental: every time the extraction happens, we'll get only the new data - which is good if, for example, you want to keep every record ever fetched. * Full table: every time the extraction happens, we'll get the current state of the data - which is good if, for example, you don't want to have deleted data in your catalog. Once you are done configuring, click **Next**. ### 4. Configure data source Describe your data source for easy identification within your organization, not exceeding 140 characters. To define your [Trigger](https://docs.nekt.com/runs/scheduling-and-triggers), consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times). Optionally, you can determine when to execute a [full sync](https://docs.nekt.com/get-started/core-concepts/types-of-sync#additional-full-sync). This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while. Once you are ready, click **Next** to finalize the setup. ### 5. Check your new source You can view your new source on the [Sources](https://app.nekt.ai/sources) page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog. For you to be able to see it on your [Catalog](https://app.nekt.ai/catalog), you need at least one successful source run. # Streams and Fields Below you'll find all available data streams from Facebook Ads and their corresponding fields: Stream containing information about your Facebook Ad Accounts, including account settings, spending limits, business information, and funding details. > **Field Modes**: You can configure the extraction level using the **Ad Accounts Fields Mode** setting: > > * `Basic` (default) - Core account fields that work with standard permissions > * `Extended` - Includes owner, tax, agency, business manager, and funding source details (requires admin/finance permissions) **Basic Fields (always included):** *Account Identifiers:* * `id` - Unique identifier for the ad account * `account_id` - The ad account ID (primary key) * `name` - Name of the ad account *Account Status & Settings:* * `account_status` - Status of the account (1=Active, 2=Disabled, etc.) * `age` - Age of the account in days * `created_time` - When the account was created * `currency` - Currency used for billing * `disable_reason` - Reason code if account is disabled * `is_personal` - Whether this is a personal account * `is_prepay_account` - Whether this is a prepay account *Spending & Budget:* * `amount_spent` - Total amount spent on the account (in cents) * `balance` - Current account balance (in cents) * `spend_cap` - Maximum spending limit for the account * `min_daily_budget` - Minimum daily budget allowed * `min_campaign_group_spend_cap` - Minimum campaign group spend cap *Business Information:* * `business_name` - Name of the business * `business_street`, `business_street2` - Business address * `business_city`, `business_state`, `business_zip` - Business location * `business_country_code` - Country code for the business *Timezone:* * `timezone_id` - Timezone identifier * `timezone_name` - Name of the timezone * `timezone_offset_hours_utc` - UTC offset in hours *Capabilities & Features:* * `capabilities` - Array of enabled capabilities for the account * `can_create_brand_lift_study` - Whether brand lift studies are available * `is_direct_deals_enabled` - Whether direct deals are enabled * `is_notifications_enabled` - Whether notifications are enabled * `offsite_pixels_tos_accepted` - Whether offsite pixels ToS was accepted **Extended Fields (only in extended mode):** *Owner & Tax Information:* * `owner` - Owner of the ad account * `tax_id` - Tax identification number * `tax_id_status` - Status of tax ID verification * `tax_id_type` - Type of tax ID *Agency Client Declaration:* * `agency_client_declaration_*` - Fields related to agency-client relationships, including client name, location, and mandate information *Business Manager:* * `business_manager_name` - Name of the Business Manager * `business_manager_id` - Business Manager ID * `business_manager_created_time` - When Business Manager was created * `business_manager_verification_status` - Verification status * `business_manager_vertical` - Business vertical category *Funding & Invoicing:* * `funding_source_details` - Object containing funding source information: * `id` - Funding source ID * `type` - Type of funding source * `display_string` - Display name * `coupons[]` - Array of coupon objects with amount, currency, and expiration * `extended_credit_invoice_group_*` - Fields related to invoice groups * `io_number` - Insertion order number * `media_agency` - Media agency information * `partner` - Partner information Stream for retrieving ad account activities, including billing events such as charges, refunds, and failed transactions. > **Note**: This stream retrieves activities from the **last 7 days** only. It provides a historical record of account-level events, with a focus on billing-related activities. **Key Fields:** * `event_time` - The time when the event occurred (ISO 8601 format) - used for incremental sync * `event_type` - The type of event (see billing events below) * `actor_id` - ID of the user/entity who triggered the activity * `actor_name` - Name of the user/entity who triggered the activity * `extra_data` - Additional data about the event in JSON format (e.g., transaction details) **Computed Billing Fields:** * `charge_amount` - Extracted charge amount from extra\_data (if available and event is billing-related) * `currency` - Currency code extracted from extra\_data (if available) * `is_billing_event` - Boolean flag indicating if this is a billing-related event **Billing Event Types:** * `ad_account_billing_charge` - Charges made to credit card * `ad_account_billing_charge_failed` - Failed billing attempts * `ad_account_billing_refund` - Refunds processed Other event types include account changes, user access modifications, and campaign/ad set/ad updates. Stream for managing ad campaigns and their settings. **Key Fields:** * `id` - Unique identifier for the campaign * `name` - Name of the campaign * `objective` - The objective of the campaign (e.g., brand awareness, conversions) * `status` - The status of the campaign (e.g., active, paused) * `daily_budget` - The daily budget for the campaign * `lifetime_budget` - The lifetime budget for the campaign * `start_time` - The start time of the campaign * `stop_time` - The stop time of the campaign * `created_time` - When the campaign was created * `updated_time` - When the campaign was last updated **Budget & Spending:** * `budget_remaining` - The remaining budget for the campaign * `spend_cap` - Maximum amount that can be spent on the campaign * `budget_rebalance_flag` - Whether budget rebalancing is enabled * `last_budget_toggling_time` - Last time the budget was modified * `bid_strategy` - The bidding strategy used for the campaign **Campaign Settings:** * `account_id` - The ID of the ad account * `buying_type` - The buying type of the campaign (e.g., auction, fixed price) * `configured_status` - The configured status of the campaign * `effective_status` - The effective status of the campaign * `pacing_type` - Array of pacing types for the campaign * `primary_attribution` - Primary attribution setting * `source_campaign_id` - ID of the source campaign if this is a copy * `boosted_object_id` - ID of the boosted object * `topline_id` - ID of the topline **Special Ad Settings:** * `special_ad_category` - Category for special ads (e.g., housing, employment) * `special_ad_categories` - Array of special ad categories * `special_ad_category_country` - Array of countries for special ad targeting **Advanced Features:** * `can_create_brand_lift_study` - Whether brand lift studies are available * `can_use_spend_cap` - Whether spend caps can be used * `has_secondary_skadnetwork_reporting` - Whether secondary SKAdNetwork reporting is enabled * `is_skadnetwork_attribution` - Whether SKAdNetwork attribution is enabled * `smart_promotion_type` - Type of smart promotion * `ad_strategy_group_id` - ID of the ad strategy group * `ad_strategy_id` - ID of the ad strategy **Labels:** * `adlabels` - Array of labels attached to the campaign, each containing: * `id` - Label ID * `name` - Label name * `created_time` - When the label was created * `updated_time` - When the label was last updated Stream for managing ad sets, which control targeting, budget, and scheduling for a group of ads. **Key Fields:** * `id` - Unique identifier for the ad set * `name` - Name of the ad set * `campaign_id` - The ID of the campaign this ad set belongs to * `status` - The status of the ad set * `daily_budget` - The daily budget for the ad set * `lifetime_budget` - The lifetime budget for the ad set * `start_time` - The start time of the ad set * `end_time` - The end time of the ad set * `created_time` - When the ad set was created * `updated_time` - When the ad set was last updated **Budget & Bidding:** * `account_id` - The ID of the ad account * `bid_strategy` - The bid strategy for the ad set * `bid_amount` - The bid amount * `bid_info` - Detailed bid information including: * `CLICKS` - Bid for clicks * `ACTIONS` - Bid for actions * `REACH` - Bid for reach * `IMPRESSIONS` - Bid for impressions * `SOCIAL` - Bid for social impressions * `budget_remaining` - Remaining budget * `daily_min_spend_target` - Minimum daily spend target * `lifetime_min_spend_target` - Minimum lifetime spend target * `lifetime_spend_cap` - Maximum lifetime spend **Optimization & Delivery:** * `billing_event` - The billing event type * `optimization_goal` - The optimization goal * `optimization_sub_event` - Sub-event for optimization * `pacing_type` - Array of pacing types * `destination_type` - Type of destination * `is_dynamic_creative` - Whether dynamic creative is enabled * `source_adset_id` - ID of source ad set if this is a copy **Targeting:** * `targeting` - Complex targeting object containing: * `age_max` - Maximum age * `age_min` - Minimum age * `genders` - Array of targeted genders * `geo_locations` - Geographic targeting settings * `interests` - Targeted interests * `behaviors` - Targeted behaviors * `custom_audiences` - Custom audience targeting * `excluded_custom_audiences` - Excluded custom audiences * `device_platforms` - Targeted devices * `publisher_platforms` - Targeted publishing platforms * `facebook_positions` - Ad positions on Facebook * `instagram_positions` - Ad positions on Instagram * `excluded_publisher_categories` - Excluded publisher categories * And many more targeting options... **Performance & Learning:** * `learning_stage_info` - Information about the learning stage: * `status` - Current learning status * `conversions` - Number of conversions * `last_sig_edit_ts` - Last significant edit timestamp * `attribution_windows` - Array of attribution windows **Labels & Metadata:** * `adlabels` - Array of labels attached to the ad set * `attribution_spec` - Attribution specifications * `review_feedback` - Review feedback * `rf_prediction_id` - RF prediction ID Stream for managing individual ads within an ad set. **Key Fields:** * `id` - Unique identifier for the ad * `name` - Name of the ad * `adset_id` - The ID of the ad set this ad belongs to * `campaign_id` - The ID of the campaign this ad belongs to * `status` - The status of the ad * `created_time` - When the ad was created * `updated_time` - When the ad was last updated **Creative & Content:** * `creative` - The creative object containing: * `id` - Creative ID * `creative_id` - Alternative creative ID reference * `tracking_specs` - Array of tracking specifications: * `application` - Application tracking * `post` - Post tracking * `conversion_id` - Conversion tracking * `action_type` - Types of actions to track * `fb_pixel` - Facebook pixel tracking * And many more tracking options... * `conversion_specs` - Array of conversion specifications * `conversion_domain` - Domain for conversions **Bidding & Budget:** * `bid_type` - Type of bidding * `bid_amount` - Bid amount * `bid_info` - Detailed bidding information **Status & Configuration:** * `account_id` - The ID of the ad account * `effective_status` - The effective status of the ad * `configured_status` - The configured status * `last_updated_by_app_id` - ID of the app that last updated the ad * `source_ad_id` - ID of the source ad if this is a copy **Additional Fields:** * `recommendations` - Array of recommendations containing: * `blame_field` - Field causing the issue * `code` - Recommendation code * `confidence` - Confidence level * `importance` - Importance level * `message` - Recommendation message * `title` - Recommendation title * `adlabels` - Array of labels attached to the ad Stream for managing ad creatives, which are the visual components of an ad. This stream extracts creative data from the ads stream context, providing incremental sync based on ad updates. > **Field Modes**: You can configure the extraction level using: > > * `Basic` (default) - Essential creative fields for faster processing > * `Advanced` - Includes all media and configuration details **Basic Fields (always included):** * `id` - Unique identifier for the creative * `account_id` - The ID of the ad account * `name` - Name of the creative * `title` - The title of the creative * `body` - The body text of the creative * `status` - Status of the creative * `actor_id` - The ID of the actor (e.g., page) associated with the creative * `authorization_category` - Category for authorization * `call_to_action_type` - Type of call to action button * `enable_direct_install` - Whether direct install is enabled * `link_url` - The URL the ad links to * `object_id` - ID of the object * `object_type` - Type of object * `object_url` - URL of the object * `use_page_actor_override` - Whether to override the page actor **Advanced Fields (included only in advanced mode):** *Media & Assets:* * `image_hash` - Hash of the image used * `image_url` - The URL of the image used in the creative * `video_id` - The ID of the video used in the creative * `thumbnail_id` - ID of the thumbnail * `thumbnail_url` - URL of the thumbnail * `playable_asset_id` - ID of the playable asset *Links & URLs:* * `link_destination_display_url` - Display URL for the link * `link_og_id` - Open Graph ID for the link * `object_store_url` - Store URL for the object * `template_url` - Template URL * `url_tags` - URL parameters for tracking *Instagram Integration:* * `instagram_user_id` - ID of the Instagram user * `instagram_permalink_url` - Permalink URL for Instagram * `instagram_story_id` - ID of the Instagram story * `effective_instagram_story_id` - Effective ID of the Instagram story * `effective_instagram_media_id` - Effective ID of the Instagram media * `source_instagram_media_id` - Source Instagram media ID *Advanced Configuration:* * `applink_treatment` - Treatment for app links * `branded_content_sponsor_page_id` - ID of the sponsor page for branded content * `bundle_folder_id` - ID of the bundle folder * `categorization_criteria` - Criteria for categorization * `category_media_source` - Source of category media * `degrees_of_freedom_spec` - Degrees of freedom specification * `destination_set_id` - ID of the destination set * `dynamic_ad_voice` - Voice setting for dynamic ads * `effective_authorization_category` - Effective authorization category * `effective_object_story_id` - Effective ID of the object story * `object_story_id` - ID of the object story * `place_page_set_id` - ID of the place page set **Ad Context Fields (always included):** * `ad_id` - ID of the parent ad * `ad_updated_time` - Last update time of the parent ad (used for incremental sync) Stream for ad labels used to organize and filter campaigns, ad sets, and ads. Supports incremental sync by `updated_time`. **Key Fields:** * `id` - Unique ID of the ad label (primary key) * `account` - Ad account this label belongs to (object with account\_id, id) * `created_time` - When the label was created * `updated_time` - When the label was last updated (replication key) * `name` - Name of the ad label Stream for custom conversions defined at the ad account level. Custom conversions let you track specific URL or event-based actions. Supports incremental sync by `creation_time`. **Key Fields:** * `id` - Unique ID of the custom conversion (primary key) * `account_id` - ID of the ad account that owns the custom conversion * `name` - Name of the custom conversion * `creation_time` - When the custom conversion was created (replication key) * `business` - Business (Business Manager) that owns this custom conversion (object with id, name, etc.) * `is_archived` - Whether the custom conversion is archived * `is_unavailable` - Whether the custom conversion is unavailable * `last_fired_time` - When the custom conversion last fired Stream for custom audiences (customer lists, website visitors, engagement audiences, lookalikes, etc.) associated with the ad account. **Key Fields:** * `id` - Unique ID of the custom audience (primary key) * `account_id` - ID of the ad account that owns the custom audience * `name` - Name of the custom audience * `approximate_count_lower_bound`, `approximate_count_upper_bound` - Approximate audience size range * `time_created`, `time_updated` - Unix timestamps for creation and last update * `customer_file_source` - Source of the customer file (e.g. USER\_PROVIDED\_ONLY, PARTNER\_PROVIDED\_ONLY) * `data_source` - Data source type and sub-type (object) * `delivery_status` - Delivery status code and description (object) * `description` - Description of the custom audience * `lookalike_spec` - Specification for lookalike audience (country, ratio, origin) if applicable * `is_value_based` - Whether this is a value-based custom audience * `operation_status` - Operation status code and description * `pixel_id` - Facebook Pixel ID associated with the audience * `retention_days` - Number of days to retain the audience * `subtype` - Subtype of the custom audience * `rule_aggregation` - How rules are aggregated (AND or OR) * `opt_out_link` - Opt-out link for the audience Stream for images uploaded to the ad account that can be used in ad creatives. Supports incremental sync by `id`. **Key Fields:** * `id` - The ID of the image (primary key, replication key) * `account_id` - The ad account that owns the image * `created_time`, `updated_time` - When the image was created and last updated * `creatives` - List of ad creative IDs that use this image (array) * `hash` - Hash which uniquely identifies the image * `height`, `width` - Dimensions of the image in pixels * `original_height`, `original_width` - Dimensions as originally uploaded * `name` - The filename of the image (max 100 characters) * `permalink_url` - Permanent URL of the image for use in story creatives * `status` - Status of the image (ACTIVE, INTERNAL, DELETED) * `url` - Temporary URL to retrieve the image * `url_128` - Temporary URL for version resized to fit within 128x128 pixels * `is_associated_creatives_in_adgroups` - Whether this image is associated with creatives in ad groups Stream for videos uploaded to the ad account that can be used in ad creatives. Supports incremental sync by `id`. **Key Fields:** * `id` - The ID of the video (primary key, replication key) * `account_id` - The ad account that owns the video * `created_time`, `updated_time` - When the video was created and last updated * `title` - Title of the video * `description` - Description of the video * `length` - Length of the video in seconds * `source` - Source URL of the video file * `permalink_url` - Permanent URL to the video * `status_value` - Processing status of the video * `status_processing_progress` - Video processing progress percentage * `views` - Number of views * `published` - Whether the video is published * `format` - Available formats (dimensions, thumbnail) for the video (array of objects) * `embeddable` - Whether the video can be embedded * `content_category` - Content category of the video * `is_crosspost_video`, `is_crossposting_eligible` - Crossposting flags * `is_instagram_eligible` - Whether the video is eligible for Instagram * `from_object` - Object (page, user) that owns or uploaded the video * `post_views` - Number of post views * `scheduled_publish_time` - Scheduled publish time for the video * `live_status` - Live status if the video is a live broadcast * `universal_video_id` - Universal video ID across Facebook properties Stream for retrieving performance data for ads, ad sets, and campaigns. > **How it works**: The Ad Insights stream uses Facebook's asynchronous API. This means that when an extraction runs, Nekt requests a report from Facebook, which gets queued and processed. Once the report is ready, Nekt downloads the data. This process is highly efficient for large data volumes but can introduce latency depending on Facebook's processing times. **Identifiers & Time:** * `ad_id` - The ID of the ad * `adset_id` - The ID of the ad set * `campaign_id` - The ID of the campaign * `account_id` - The ID of the ad account * `date_start` - The start date of the data * `date_stop` - The stop date of the data **Basic Metrics:** * `impressions` - Number of times the ad was shown * `reach` - Number of unique people who saw the ad * `frequency` - Average number of times each person saw the ad * `clicks` - Number of clicks on the ad * `unique_clicks` - Number of unique clicks * `ctr` - Click-through rate **Cost Metrics:** * `spend` - Amount spent on the ad * `cpc` - Cost per click * `cpm` - Cost per thousand impressions * `cpp` - Cost per 1,000 people reached * `cost_per_unique_click` - Cost per unique click * `cost_per_action_type` - Cost per action type **Conversion Metrics:** * `conversions` - Number of conversions * `cost_per_conversion` - Cost per conversion * `conversion_rate_ranking` - Ranking of conversion rate * `conversion_values` - Values of conversions * `cost_per_conversion` - Cost per conversion * `website_purchase_roas` - Return on ad spend for website purchases * `purchase_roas` - Overall return on ad spend **Engagement Metrics:** * `social_spend` - Spend on social impressions * `social_impressions` - Number of social impressions * `actions` - Detailed breakdown of different types of actions * `video_p25_watched_actions` - Video views at 25% * `video_p50_watched_actions` - Video views at 50% * `video_p75_watched_actions` - Video views at 75% * `video_p95_watched_actions` - Video views at 95% * `video_p100_watched_actions` - Complete video views * `video_avg_time_watched_actions` - Average video watch time * `video_play_actions` - Number of video plays **Quality & Relevance:** * `quality_ranking` - Quality ranking of the ad * `engagement_rate_ranking` - Ranking of engagement rate * `quality_score_organic` - Organic quality score * `quality_score_ectr` - Expected click-through rate score * `quality_score_ecvr` - Expected conversion rate score #### Advanced Insight Reports (Breakdowns) When you enable the "Advanced Reports" option in the source configuration, a set of additional streams become available. These streams provide the `AdInsights` data broken down by different dimensions, allowing for more granular analysis. * **`adsinsights_by_age_and_gender`**: Breaks down performance data by age group and gender. * **`adsinsights_by_country`**: Segments data by country. * **`adsinsights_by_device_platform`**: Provides insights into performance across different devices (e.g., mobile, desktop), publisher platforms (e.g., Facebook, Instagram), and placements (e.g., feed, stories). * **`adsinsights_by_region`**: Breaks down data by geographical region (e.g., state, province). * **`adsinsights_hourly_advertiser_timezone`**: Aggregates data on an hourly basis, based on the ad account's timezone. # Data Model The following diagram illustrates the relationships between the core data streams in Facebook Ads. The arrows indicate the join keys that link the different entities, providing a clear overview of the data structure. ```mermaid theme={null} graph TD; subgraph "Account Level" AdAccounts("Ad Accounts"); end subgraph "Core Entities" Campaigns("Campaigns"); AdSets("Ad Sets"); Ads("Ads"); Creatives("Creatives"); end subgraph "Account-Level Streams" AdLabels("Ad Labels"); CustomConversions("Custom Conversions"); CustomAudiences("Custom Audiences"); AdImages("Ad Images"); AdVideos("Ad Videos"); end subgraph "Activity & Performance" Activities("Activities"); AdInsights("Ad Insights"); end Campaigns -- "account_id" --> AdAccounts; AdSets -- "account_id" --> AdAccounts; AdSets -- "campaign_id" --> Campaigns; Ads -- "account_id" --> AdAccounts; Ads -- "adset_id" --> AdSets; Ads -- "campaign_id" --> Campaigns; Ads -- "creative.id" --> Creatives; AdLabels -- "account" --> AdAccounts; CustomConversions -- "account_id" --> AdAccounts; CustomAudiences -- "account_id" --> AdAccounts; AdImages -- "account_id" --> AdAccounts; AdVideos -- "account_id" --> AdAccounts; Activities -- "account_id" --> AdAccounts; AdInsights -- "account_id" --> AdAccounts; AdInsights -- "ad_id" --> Ads; AdInsights -- "adset_id" --> AdSets; AdInsights -- "campaign_id" --> Campaigns; ``` # Use Cases for Data Analysis This guide outlines valuable business intelligence use cases when consolidating Facebook Ads data, along with ready-to-use SQL queries that you can run on [Explorer](https://app.nekt.ai/explorer). ## Campaign Performance Analysis ### 1. Campaign Performance Metrics Track the overall performance of your campaigns, including detailed conversion metrics. **Business Value:** * Identify which campaigns deliver the best value for money * Understand the relationship between quality scores and performance * Optimize budget allocation based on performance metrics ```sql theme={null} WITH campaign_metrics AS ( SELECT c."name" AS "campaign_name", c."objective", c."status", c."buying_type", SUM(TRY_CAST (ai."impressions" AS BIGINT)) AS "total_impressions", SUM(TRY_CAST (ai."reach" AS BIGINT)) AS "total_reach", SUM(TRY_CAST (ai."clicks" AS BIGINT)) AS "total_clicks", SUM(TRY_CAST (ai."spend" AS DOUBLE)) AS "total_spend", AVG(TRY_CAST (ai."frequency" AS DOUBLE)) AS "avg_frequency", SUM(TRY_CAST (ai."clicks" AS DOUBLE)) * 100.0 / NULLIF(SUM(TRY_CAST (ai."impressions" AS DOUBLE)), 0) AS "ctr", SUM(TRY_CAST (ai."spend" AS DOUBLE)) / NULLIF(SUM(TRY_CAST (ai."clicks" AS DOUBLE)), 0) AS "cpc", SUM(TRY_CAST (ai."spend" AS DOUBLE)) * 1000.0 / NULLIF(SUM(TRY_CAST (ai."impressions" AS DOUBLE)), 0) AS "cpm" FROM "nekt_raw"."facebook_ads_campaigns" c LEFT JOIN "nekt_raw"."facebook_ads_adsinsights" ai ON c."id" = ai."campaign_id" WHERE c."effective_status" = 'ACTIVE' AND date_parse (ai."date_start", '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY c."name", c."objective", c."status", c."buying_type" ) SELECT "campaign_name", "objective", "total_spend", "total_impressions", "total_reach", "total_clicks", "avg_frequency", "ctr", "cpc", "cpm" FROM campaign_metrics ``` ```sql theme={null} WITH campaign_metrics AS ( SELECT c.name AS campaign_name, c.objective, c.status, c.buying_type, SUM(SAFE_CAST(ai.impressions AS INT64)) AS total_impressions, SUM(SAFE_CAST(ai.reach AS INT64)) AS total_reach, SUM(SAFE_CAST(ai.clicks AS INT64)) AS total_clicks, SUM(SAFE_CAST(ai.spend AS FLOAT64)) AS total_spend, AVG(SAFE_CAST(ai.frequency AS FLOAT64)) AS avg_frequency, SAFE_DIVIDE(SUM(SAFE_CAST(ai.clicks AS FLOAT64)) * 100.0, SUM(SAFE_CAST(ai.impressions AS FLOAT64))) AS ctr, SAFE_DIVIDE(SUM(SAFE_CAST(ai.spend AS FLOAT64)), SUM(SAFE_CAST(ai.clicks AS FLOAT64))) AS cpc, SAFE_DIVIDE(SUM(SAFE_CAST(ai.spend AS FLOAT64)) * 1000.0, SUM(SAFE_CAST(ai.impressions AS FLOAT64))) AS cpm FROM `nekt_raw.facebook_ads_campaigns` c LEFT JOIN `nekt_raw.facebook_ads_adsinsights` ai ON c.id = ai.campaign_id WHERE c.effective_status = 'ACTIVE' AND PARSE_DATE('%Y-%m-%d', ai.date_start) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY c.name, c.objective, c.status, c.buying_type ) SELECT campaign_name, objective, total_spend, total_impressions, total_reach, total_clicks, avg_frequency, ctr, cpc, cpm FROM campaign_metrics ``` | campaign\_name | objective | total\_spend | total\_impressions | total\_reach | total\_clicks | avg\_frequency | ctr | cpc | cpm | | -------------------- | ---------------- | ------------ | ------------------ | ------------ | ------------- | -------------- | ---- | ---- | ---- | | Summer Sale 2024 | CONVERSIONS | 8,542.30 | 1,245,890 | 892,340 | 42,156 | 1.40 | 3.38 | 0.20 | 6.85 | | Brand Awareness Q4 | REACH | 5,686.00 | 3,892,450 | 2,145,670 | 28,430 | 1.81 | 0.73 | 0.20 | 1.46 | | Product Launch | TRAFFIC | 4,998.50 | 892,340 | 567,230 | 31,245 | 1.57 | 3.50 | 0.16 | 5.60 | | Retargeting Campaign | CONVERSIONS | 3,784.00 | 2,145,670 | 456,890 | 18,920 | 4.70 | 0.88 | 0.20 | 1.76 | | Lead Gen Campaign | LEAD\_GENERATION | 2,890.00 | 567,230 | 312,450 | 12,340 | 1.82 | 2.18 | 0.23 | 5.09 | ### 2. Creative Performance and Content Analysis Analyze how different creative elements and content types perform across your campaigns, helping optimize your creative strategy. **Business Value:** * Identify which creative formats drive the best engagement * Understand the impact of different call-to-action types * Optimize creative elements based on performance data * Guide future creative development with data-driven insights ```sql theme={null} WITH creative_metrics AS ( SELECT cr.name AS creative_name, cr.title, cr.body, cr.call_to_action_type, cr.object_type, COALESCE(cr.video_id, 'image') AS content_type, SUM(TRY_CAST (ai.impressions AS BIGINT)) AS total_impressions, SUM(TRY_CAST (ai.reach AS BIGINT)) AS total_reach, SUM(TRY_CAST (ai.clicks AS BIGINT)) AS total_clicks, SUM(TRY_CAST (ai.spend AS DECIMAL(20, 2))) AS total_spend, AVG(TRY_CAST (ai.frequency AS DECIMAL(10, 2))) AS avg_frequency, CAST( SUM(TRY_CAST (ai.clicks AS DOUBLE)) * 100.0 / NULLIF(SUM(TRY_CAST (ai.impressions AS DOUBLE)), 0) AS DECIMAL(10, 2) ) AS ctr, SUM( CASE WHEN action.action_type = 'video_view' THEN TRY_CAST (action.value AS DECIMAL(20, 2)) ELSE 0 END ) AS total_video_views, AVG( CASE WHEN action.action_type = 'video_view' THEN TRY_CAST (action.value AS DECIMAL(20, 2)) ELSE NULL END ) AS avg_video_completion_rate FROM nekt_raw.facebook_ads_creatives cr LEFT JOIN nekt_raw.facebook_ads_ads a ON cr.id = a.creative.id LEFT JOIN nekt_raw.facebook_ads_adsinsights ai ON a.id = ai.ad_id CROSS JOIN UNNEST (ai.actions) AS t ("action") WHERE date_parse (ai.date_start, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY cr.name, cr.title, cr.body, cr.call_to_action_type, cr.object_type, cr.video_id ) SELECT creative_name, content_type, call_to_action_type, object_type, total_impressions, total_reach, total_clicks, total_spend, ctr, CAST( total_spend / NULLIF(total_clicks, 0) AS DECIMAL(10, 2) ) AS cost_per_click, CASE WHEN content_type != 'image' THEN total_video_views ELSE NULL END AS video_views, CASE WHEN content_type != 'image' THEN avg_video_completion_rate ELSE NULL END AS video_completion_rate FROM creative_metrics ORDER BY total_clicks DESC ``` ```sql theme={null} WITH creative_metrics AS ( SELECT cr.name AS creative_name, cr.title, cr.body, cr.call_to_action_type, cr.object_type, COALESCE(cr.video_id, 'image') AS content_type, SUM(SAFE_CAST (ai.impressions AS INT64)) AS total_impressions, SUM(SAFE_CAST (ai.reach AS INT64)) AS total_reach, SUM(SAFE_CAST (ai.clicks AS INT64)) AS total_clicks, SUM(SAFE_CAST (ai.spend AS BIGNUMERIC)) AS total_spend, -- Changed NUMERIC to BIGNUMERIC for consistency/precision AVG(SAFE_CAST (ai.frequency AS BIGNUMERIC)) AS avg_frequency, -- Changed NUMERIC to BIGNUMERIC ROUND( SAFE_DIVIDE ( SUM(SAFE_CAST (ai.clicks AS FLOAT64)) * 100.0, SUM(SAFE_CAST (ai.impressions AS FLOAT64)) ), 2 ) AS ctr, SUM( CASE WHEN action.action_type = 'video_view' THEN SAFE_CAST (action.value AS BIGNUMERIC) -- Changed NUMERIC to BIGNUMERIC ELSE 0 END ) AS total_video_views, AVG( CASE WHEN action.action_type = 'video_view' THEN SAFE_CAST (action.value AS BIGNUMERIC) -- Changed NUMERIC to BIGNUMERIC ELSE NULL END ) AS avg_video_completion_rate FROM `nekt_raw.facebook_ads_creatives` cr LEFT JOIN `nekt_raw.facebook_ads_ads` a ON cr.id = a.creative.id LEFT JOIN `nekt_raw.facebook_ads_adsinsights` ai ON a.id = ai.ad_id -- Using comma syntax for UNNEST is BigQuery standard LEFT JOIN UNNEST (ai.actions) AS action ON TRUE WHERE PARSE_DATE ('%Y-%m-%d', ai.date_start) >= DATE_SUB (CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY cr.name, cr.title, cr.body, cr.call_to_action_type, cr.object_type, cr.video_id ) SELECT creative_name, content_type, call_to_action_type, object_type, total_impressions, total_reach, total_clicks, FORMAT ("%.2f", total_spend) AS total_spend, FORMAT ("%.2f", ctr) AS ctr, FORMAT ("%2f", SAFE_DIVIDE (total_spend, total_clicks)) AS cost_per_click, -- CPC calculated in the final select. CASE WHEN content_type != 'image' THEN total_video_views ELSE NULL END AS video_views, CASE WHEN content_type != 'image' THEN FORMAT ("%.2f", avg_video_completion_rate) -- Format this rate as well ELSE NULL END AS video_completion_rate FROM creative_metrics ORDER BY total_clicks DESC ``` | creative\_name | content\_type | call\_to\_action\_type | object\_type | total\_impressions | total\_reach | total\_clicks | total\_spend | ctr | cost\_per\_click | video\_views | video\_completion\_rate | | ----------------- | ------------- | ---------------------: | ------------ | ------------------ | ------------ | ------------- | ------------ | ---- | ---------------- | ------------ | ----------------------- | | Summer Video Ad | video | SHOP\_NOW | VIDEO | 456,780 | 312,450 | 12,340 | 2,468.00 | 2.70 | 0.20 | 89,450 | 45.20 | | Product Carousel | image | LEARN\_MORE | SHARE | 234,560 | 156,780 | 8,920 | 1,784.00 | 3.80 | 0.20 | NULL | NULL | | Brand Story Video | video | WATCH\_MORE | VIDEO | 189,340 | 123,450 | 5,670 | 1,134.00 | 2.99 | 0.20 | 56,780 | 38.90 | | Sale Banner | image | SHOP\_NOW | SHARE | 345,670 | 234,560 | 15,890 | 3,178.00 | 4.60 | 0.20 | NULL | NULL | | Testimonial Video | video | SIGN\_UP | VIDEO | 123,450 | 89,670 | 4,230 | 846.00 | 3.43 | 0.20 | 34,560 | 52.30 | ### 3. Time-Based Performance Analysis Track campaign performance trends over time to identify patterns and optimize campaign timing. **Business Value:** * Understand daily and weekly performance patterns * Identify best-performing days and times * Optimize campaign scheduling and budget pacing * Track performance trends for better planning ```sql theme={null} WITH daily_metrics AS ( SELECT date_parse (ai.date_start, '%Y-%m-%d') AS date, date_format (date_parse (ai.date_start, '%Y-%m-%d'), '%W') AS day_of_week, SUM(TRY_CAST (ai.impressions AS BIGINT)) AS total_impressions, SUM(TRY_CAST (ai.reach AS BIGINT)) AS total_reach, SUM(TRY_CAST (ai.clicks AS BIGINT)) AS total_clicks, SUM(TRY_CAST (ai.spend AS DECIMAL(20, 2))) AS total_spend, SUM(TRY_CAST (ai.clicks AS DOUBLE)) * 100.0 / NULLIF(SUM(TRY_CAST (ai.impressions AS DOUBLE)), 0) AS ctr, SUM(TRY_CAST (ai.spend AS DOUBLE)) / NULLIF(SUM(TRY_CAST (ai.clicks AS DOUBLE)), 0) AS cpc, COUNT(DISTINCT ai.campaign_id) AS active_campaigns, SUM( CASE WHEN action.action_type = 'purchase' THEN TRY_CAST (action.value AS DECIMAL(20, 2)) ELSE 0 END ) AS total_conversions FROM nekt_raw.facebook_ads_adsinsights ai CROSS JOIN UNNEST (ai.actions) AS t (action) WHERE date_parse (ai.date_start, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY GROUP BY ai.date_start ), weekly_summary AS ( SELECT day_of_week, AVG(total_impressions) AS avg_daily_impressions, AVG(total_reach) AS avg_daily_reach, AVG(total_clicks) AS avg_daily_clicks, AVG(total_spend) AS avg_daily_spend, AVG(ctr) AS avg_ctr, AVG(cpc) AS avg_cpc, AVG(total_conversions) AS avg_daily_conversions, AVG(active_campaigns) AS avg_active_campaigns FROM daily_metrics GROUP BY day_of_week ), daily_vs_average AS ( SELECT dm.*, ws.avg_daily_spend AS typical_daily_spend, ws.avg_daily_clicks AS typical_daily_clicks, ws.avg_daily_conversions AS typical_daily_conversions, ROUND( (total_spend - ws.avg_daily_spend) * 100.0 / NULLIF(ws.avg_daily_spend, 0), 2 ) AS spend_vs_typical, ROUND( (total_clicks - ws.avg_daily_clicks) * 100.0 / NULLIF(ws.avg_daily_clicks, 0), 2 ) AS clicks_vs_typical, ROUND( (total_conversions - ws.avg_daily_conversions) * 100.0 / NULLIF(ws.avg_daily_conversions, 0), 2 ) AS conversions_vs_typical FROM daily_metrics dm JOIN weekly_summary ws ON dm.day_of_week = ws.day_of_week ) SELECT date, day_of_week, total_impressions, total_reach, total_clicks, CAST(total_spend AS DECIMAL(20, 2)) AS total_spend, CAST(ctr AS DECIMAL(10, 2)) AS ctr, CAST(cpc AS DECIMAL(10, 2)) AS cpc, total_conversions, active_campaigns, CAST(typical_daily_spend AS DECIMAL(20, 2)) AS typical_daily_spend, CAST(spend_vs_typical AS DECIMAL(10, 2)) AS spend_vs_typical_pct, CAST(clicks_vs_typical AS DECIMAL(10, 2)) AS clicks_vs_typical_pct, CAST(conversions_vs_typical AS DECIMAL(10, 2)) AS conversions_vs_typical_pct FROM daily_vs_average ORDER BY date DESC ``` ```sql theme={null} WITH daily_metrics AS ( SELECT PARSE_DATE ('%Y-%m-%d', ai.date_start) AS date, FORMAT_DATE ('%A', PARSE_DATE ('%Y-%m-%d', ai.date_start)) AS day_of_week, SUM(SAFE_CAST (ai.impressions AS INT64)) AS total_impressions, SUM(SAFE_CAST (ai.reach AS INT64)) AS total_reach, SUM(SAFE_CAST (ai.clicks AS INT64)) AS total_clicks, SUM(SAFE_CAST (ai.spend AS BIGNUMERIC)) AS total_spend, SUM(SAFE_CAST (ai.clicks AS FLOAT64)) * 100.0 / NULLIF(SUM(SAFE_CAST (ai.impressions AS FLOAT64)), 0) AS ctr, SUM(SAFE_CAST (ai.spend AS FLOAT64)) / NULLIF(SUM(SAFE_CAST (ai.clicks AS FLOAT64)), 0) AS cpc, COUNT(DISTINCT ai.campaign_id) AS active_campaigns, SUM( CASE WHEN action.action_type = 'purchase' THEN SAFE_CAST (action.value AS BIGNUMERIC) ELSE 0 END ) AS total_conversions FROM `nekt_raw.facebook_ads_adsinsights` AS ai, UNNEST (ai.actions) AS action WHERE PARSE_DATE ('%Y-%m-%d', ai.date_start) >= DATE_SUB (CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY 1, 2, ai.date_start ), weekly_summary AS ( SELECT day_of_week, AVG(total_impressions) AS avg_daily_impressions, AVG(total_reach) AS avg_daily_reach, AVG(total_clicks) AS avg_daily_clicks, AVG(total_spend) AS avg_daily_spend, AVG(ctr) AS avg_ctr, AVG(cpc) AS avg_cpc, AVG(total_conversions) AS avg_daily_conversions, AVG(active_campaigns) AS avg_active_campaigns FROM daily_metrics GROUP BY day_of_week ), daily_vs_average AS ( SELECT dm.*, ws.avg_daily_spend AS typical_daily_spend, ws.avg_daily_clicks AS typical_daily_clicks, ws.avg_daily_conversions AS typical_daily_conversions, ROUND( (total_spend - ws.avg_daily_spend) * 100.0 / NULLIF(ws.avg_daily_spend, 0), 2 ) AS spend_vs_typical, ROUND( (total_clicks - ws.avg_daily_clicks) * 100.0 / NULLIF(ws.avg_daily_clicks, 0), 2 ) AS clicks_vs_typical, ROUND( (total_conversions - ws.avg_daily_conversions) * 100.0 / NULLIF(ws.avg_daily_conversions, 0), 2 ) AS conversions_vs_typical FROM daily_metrics AS dm INNER JOIN weekly_summary AS ws ON dm.day_of_week = ws.day_of_week ) SELECT date, day_of_week, total_impressions, total_reach, total_clicks, -- FIX 1: Format currency/metrics as strings to guarantee display format FORMAT ("%.2f", total_spend) AS total_spend, FORMAT ("%.2f", ctr) AS ctr, FORMAT ("%.2f", cpc) AS cpc, CAST(total_conversions AS BIGINT) AS total_conversions, active_campaigns, FORMAT ("%.2f", typical_daily_spend) AS typical_daily_spend, FORMAT ("%.2f", spend_vs_typical) AS spend_vs_typical_pct, FORMAT ("%.2f", clicks_vs_typical) AS clicks_vs_typical_pct, FORMAT ("%.2f", conversions_vs_typical) AS conversions_vs_typical_pct FROM daily_vs_average ORDER BY date DESC ``` | date | day\_of\_week | total\_impressions | total\_reach | total\_clicks | total\_spend | ctr | cpc | total\_conversions | active\_campaigns | typical\_daily\_spend | spend\_vs\_typical\_pct | clicks\_vs\_typical\_pct | conversions\_vs\_typical\_pct | | ---------- | ------------- | ------------------ | ------------ | ------------- | ------------ | ---- | ---- | ------------------ | ----------------- | --------------------- | ----------------------- | ------------------------ | ----------------------------- | | 2024-11-27 | Wednesday | 156,234 | 112,450 | 5,892 | 1,178.40 | 3.77 | 0.20 | 78 | 8 | 1,130.50 | 4.2 | 8.5 | 12.8 | | 2024-11-26 | Tuesday | 148,920 | 108,340 | 5,234 | 1,046.80 | 3.51 | 0.20 | 65 | 8 | 1,072.30 | -2.4 | -3.2 | -8.3 | | 2024-11-25 | Monday | 142,560 | 102,890 | 4,987 | 997.40 | 3.50 | 0.20 | 58 | 8 | 1,018.60 | -2.1 | -5.8 | -18.5 | | 2024-11-24 | Sunday | 98,450 | 72,340 | 3,245 | 649.00 | 3.30 | 0.20 | 42 | 7 | 834.20 | -22.2 | -28.4 | -28.6 | | 2024-11-23 | Saturday | 112,340 | 84,560 | 3,890 | 778.00 | 3.46 | 0.20 | 52 | 7 | 834.20 | -6.7 | -14.2 | -11.2 | | 2024-11-22 | Friday | 178,560 | 128,450 | 6,234 | 1,246.80 | 3.49 | 0.20 | 95 | 8 | 1,148.90 | 8.5 | 14.8 | 32.4 | This analysis provides valuable insights into: * Daily performance trends * Day-of-week patterns * Performance against typical metrics * Campaign activity levels * Spend and conversion efficiency You can use these insights to: * Adjust campaign scheduling * Optimize budget allocation across days * Identify and investigate performance anomalies * Plan campaign launches around high-performing periods * Improve budget pacing strategies ## Implementation Notes ### Data Quality Considerations * Ensure you have selected the appropriate attribution windows for your business goals, as this will significantly impact conversion metrics. * When analyzing performance over time, be mindful of the 28-day lookback window. Data within this window may be subject to change. * For granular analysis, use the advanced insight reports (breakdowns). Combining these with the core tables will provide a deeper understanding of your ad performance. ### API Limits & Performance * Enabling advanced reports and selecting a large number of fields in the `AdInsights` stream can lead to longer extraction times and higher API usage. * To optimize performance, select only the streams and fields necessary for your analysis. Use the `basic` mode for creatives if you don't need detailed media asset information. ### Ad Accounts Permission Requirements * The **Ad Accounts** stream has two field modes to accommodate different permission levels: * **Basic mode** (default): Works with standard advertiser permissions. Use this if you're getting permission errors during extraction. * **Extended mode**: Requires admin or finance access to all connected ad accounts. This mode includes sensitive information like funding source details, tax information, and business manager data. * If extraction fails with permission errors on the AdAccounts stream, switch to "Basic" mode in the advanced settings. ## Skills for agents Facebook Ads connector documentation as plain markdown, for use in AI agent contexts.