Queries for Facebook Ads data, Latest Events Views – PostgreSQL

Bellow is a number of SQL views that will help you work better with Facebook Ads data and PostgreSQL. If you want to work only with the latest values then it is helpful to create a view where only the latest values are maintained. For reference here is an overview of the expected Facebook Ads data into your data warehouse.

SQL View (PostgreSQL): Latest Events for Facebook Ads ads table

CREATE materialized VIEW AdsView AS
SELECT a.account_id,
       a.ad_review_feedback_global_text_overlay,
       a.ad_review_feedback_placement_specific_facebook_text_overlay,
       a.adset_id,
       a.bid_amount,
       a.bid_info_actions,
       a.bid_info_reach,
       a.bid_type,
       a.blendo_id,
       a.blendo_imported_at,
       a.campaign_id,
       a.configured_status,
       a.created_time,
       a.creative_id,
       a.effective_status,
       a.id,
       a.last_updated_by_app_id,
       a.name,
       a.recommendations_blame_field,
       a.recommendations_code,
       a.recommendations_confidence,
       a.recommendations_importance,
       a.recommendations_message,
       a.recommendations_title,
       a.status,
       a.tracking_specs_action_type,
       a.tracking_specs_application,
       a.tracking_specs_creative,
       a.tracking_specs_event,
       a.tracking_specs_fb_pixel,
       a.tracking_specs_page,
       a.tracking_specs_post,
       a.id,
       a.blendo_id,
       tmp.MostRecent
FROM "ads" AS a,

  ( SELECT id,
           max(to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS')) AS MostRecent
   FROM "ads"
   GROUP BY id)tmp
WHERE (a.id = tmp.id)
  AND (tmp.MostRecent=to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS'))

SQL View (PostgreSQL): Latest Events for Facebook Ads adsets table

CREATE materialized VIEW AdsetView AS
SELECT a.blendo_id,
       a.account_id,
       a.id,
       a.attribution_window_days,
       a.billing_event,
       a.budget_remaining,
       a.campaign_id,
       a.configured_status,
       a.created_time,
       a.daily_budget,
       a.effective_status,
       a.end_time,
       a.frequency_cap_reset_period,
       a.is_autobid,
       a.is_average_price_pacing,
       a.lifetime_budget,
       a.lifetime_imps,
       a.name,
       a.optimization_goal,
       a.pacing_type,
       a.recurring_budget_semantics,
       a.rtb_flag,
       a.start_time,
       a.status,
       a.updated_time,
       a.use_new_app_click,
       tmp.MostRecent
FROM "adsets" AS a,

  ( SELECT id,
           max(to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS')) AS MostRecent
   FROM "adsets"
   GROUP BY id)tmp
WHERE (a.id = tmp.id)
  AND (tmp.MostRecent=to_timestamp(updated_time, 'YYYY-MM-DD"T"HH24:MI:SS'))

SQL View (PostgreSQL): Latest Events for Facebook Ads ad_insights table

CREATE materialized VIEW InsightsView AS
SELECT a.blendo_id,
       a.impressions,
       a.reach,
       a.adset_name,
       a.social_spend,
       a.unique_inline_link_click_ctr,
       a.total_action_value,
       a.unique_clicks,
       a.unique_social_clicks,
       a.app_store_clicks,
       a.unique_link_clicks_ctr,
       a.cost_per_estimated_ad_recallers,
       a.spend,
       a.cost_per_unique_inline_link_click,
       a.social_reach,
       a.relevance_score_negative_feedback,
       a.relevance_score_positive_feedback,
       a.cpp,
       a.newsfeed_impressions,
       a.unique_inline_link_clicks,
       a.cost_per_inline_link_click,
       a.campaign_name,
       a.inline_link_clicks,
       a.canvas_avg_view_time,
       a.unique_impressions,
       a.newsfeed_clicks,
       a.inline_link_click_ctr,
       a.cpc,
       a.date_start,
       a.cpm,
       a.adset_id,
       a.objective,
       a.newsfeed_avg_position,
       a.account_id,
       a.cost_per_unique_click,
       a.call_to_action_clicks,
       a.estimated_ad_recallers,
       a.ad_name,
       a.website_clicks,
       a.account_name,
       a.cost_per_inline_post_engagement,
       a.clicks,
       a.social_clicks,
       a.social_impressions,
       a.ad_id,
       a.frequency,
       a.estimated_ad_recall_rate,
       a.unique_social_impressions,
       a.ctr,
       a.inline_post_engagement,
       a.relevance_score_score,
       a.relevance_score_status,
       a.deeplink_clicks,
       a.campaign_id,
       a.unique_ctr,
       a.canvas_avg_view_percent,
       tmp.MostRecentStop,
       tmp.MostRecentImp
FROM "ad_insights" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "ad_insights"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop=tmp.MostRecentStop
  AND a.blendo_imported_at = tmp.MostRecentImp

SQL View (PostgreSQL): Latest Events for Facebook Ads ad_placement_device table

CREATE materialized VIEW InsightsPlacementDeviceView AS
SELECT a.account_id,
       a.account_name,
       a.action_values_action_type,
       a.action_values_value,
       a.actions_action_type,
       a.actions_value,
       a.ad_id,
       a.ad_name,
       a.adset_id,
       a.adset_name,
       a.app_store_clicks,
       a.blendo_id,
       a.blendo_imported_at,
       a.call_to_action_clicks,
       a.campaign_id,
       a.campaign_name,
       a.canvas_avg_view_percent,
       a.canvas_avg_view_time,
       a.clicks,
       a.cost_per_10_sec_video_view_action_type,
       a.cost_per_10_sec_video_view_value,
       a.cost_per_estimated_ad_recallers,
       a.cost_per_inline_link_click,
       a.cost_per_inline_post_engagement,
       a.cost_per_unique_action_type_action_type,
       a.cost_per_unique_action_type_value,
       a.cost_per_unique_click,
       a.cost_per_unique_inline_link_click,
       a.cpc,
       a.cpm,
       a.cpp,
       a.ctr,
       a.date_start,
       a.date_stop,
       a.deeplink_clicks,
       a.estimated_ad_recall_rate,
       a.estimated_ad_recall_rate_lower_bound,
       a.estimated_ad_recall_rate_upper_bound,
       a.estimated_ad_recallers,
       a.frequency,
       a.impression_device,
       a.impressions,
       a.inline_link_click_ctr,
       a.inline_link_clicks,
       a.inline_post_engagement,
       a.objective,
       a.placement,
       a.reach,
       a.social_clicks,
       a.social_impressions,
       a.social_reach,
       a.spend,
       a.total_action_value,
       a.unique_actions_action_type,
       a.unique_actions_value,
       a.unique_clicks,
       a.unique_ctr,
       a.unique_impressions,
       a.unique_inline_link_click_ctr,
       a.unique_inline_link_clicks,
       a.unique_link_clicks_ctr,
       a.unique_social_clicks,
       a.unique_social_impressions,
       a.video_10_sec_watched_actions_action_type,
       a.video_10_sec_watched_actions_value,
       a.video_15_sec_watched_actions_action_type,
       a.video_15_sec_watched_actions_value,
       a.video_30_sec_watched_actions_action_type,
       a.video_30_sec_watched_actions_value,
       a.video_avg_pct_watched_actions_action_type,
       a.video_avg_pct_watched_actions_value,
       a.video_avg_sec_watched_actions_action_type,
       a.video_avg_sec_watched_actions_value,
       a.video_complete_watched_actions_action_type,
       a.video_complete_watched_actions_value,
       a.video_p100_watched_actions_action_type,
       a.video_p100_watched_actions_value,
       a.video_p25_watched_actions_action_type,
       a.video_p25_watched_actions_value,
       a.video_p50_watched_actions_action_type,
       a.video_p50_watched_actions_value,
       a.video_p75_watched_actions_action_type,
       a.video_p75_watched_actions_value,
       a.video_p95_watched_actions_action_type,
       a.video_p95_watched_actions_value,
       a.website_clicks,
       tmp.MostRecentStop,
       tmp.MostRecentImp
FROM "ad_placement_device" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "ad_placement_device"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop=tmp.MostRecentStop
  AND a.blendo_imported_at = tmp.MostRecentImp

SQL View (PostgreSQL): Latest Events for Facebook Ads ad_age_gender table

CREATE materialized VIEW InsightsAgeGenderView AS
SELECT a.account_id,
       a.account_name,
       a.action_values_action_type,
       a.action_values_value,
       a.actions_action_type,
       a.actions_value,
       a.ad_id,
       a.ad_name,
       a.adset_id,
       a.adset_name,
       a.app_store_clicks,
       a.blendo_id,
       a.blendo_imported_at,
       a.call_to_action_clicks,
       a.campaign_id,
       a.campaign_name,
       a.canvas_avg_view_percent,
       a.canvas_avg_view_time,
       a.clicks,
       a.cost_per_10_sec_video_view_action_type,
       a.cost_per_10_sec_video_view_value,
       a.cost_per_estimated_ad_recallers,
       a.cost_per_inline_link_click,
       a.cost_per_inline_post_engagement,
       a.cost_per_unique_action_type_action_type,
       a.cost_per_unique_action_type_value,
       a.cost_per_unique_click,
       a.cost_per_unique_inline_link_click,
       a.cpc,
       a.cpm,
       a.cpp,
       a.ctr,
       a.date_start,
       a.date_stop,
       a.deeplink_clicks,
       a.estimated_ad_recall_rate,
       a.estimated_ad_recall_rate_lower_bound,
       a.estimated_ad_recall_rate_upper_bound,
       a.estimated_ad_recallers,
       a.frequency,
       a.impressions,
       a.inline_link_click_ctr,
       a.inline_link_clicks,
       a.inline_post_engagement,
       a.objective,
       a.gender,
       a.age,
       a.reach,
       a.social_clicks,
       a.social_impressions,
       a.social_reach,
       a.spend,
       a.total_action_value,
       a.unique_actions_action_type,
       a.unique_actions_value,
       a.unique_clicks,
       a.unique_ctr,
       a.unique_impressions,
       a.unique_inline_link_click_ctr,
       a.unique_inline_link_clicks,
       a.unique_link_clicks_ctr,
       a.unique_social_clicks,
       a.unique_social_impressions,
       a.video_10_sec_watched_actions_action_type,
       a.video_10_sec_watched_actions_value,
       a.video_15_sec_watched_actions_action_type,
       a.video_15_sec_watched_actions_value,
       a.video_30_sec_watched_actions_action_type,
       a.video_30_sec_watched_actions_value,
       a.video_avg_pct_watched_actions_action_type,
       a.video_avg_pct_watched_actions_value,
       a.video_avg_sec_watched_actions_action_type,
       a.video_avg_sec_watched_actions_value,
       a.video_complete_watched_actions_action_type,
       a.video_complete_watched_actions_value,
       a.video_p100_watched_actions_action_type,
       a.video_p100_watched_actions_value,
       a.video_p25_watched_actions_action_type,
       a.video_p25_watched_actions_value,
       a.video_p50_watched_actions_action_type,
       a.video_p50_watched_actions_value,
       a.video_p75_watched_actions_action_type,
       a.video_p75_watched_actions_value,
       a.video_p95_watched_actions_action_type,
       a.video_p95_watched_actions_value,
       a.website_clicks,
       tmp.MostRecentStop,
       tmp.MostRecentImp
FROM "ad_age_gender" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "ad_age_gender"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_at=tmp.MostRecentImp

 

SQL View (PostgreSQL): Latest Events for Facebook Ads ad_country table

CREATE materialized VIEW InsightsCountryView AS
SELECT a.account_id,
       a.account_name,
       a.action_values_action_type,
       a.action_values_value,
       a.actions_action_type,
       a.actions_value,
       a.ad_id,
       a.ad_name,
       a.adset_id,
       a.adset_name,
       a.app_store_clicks,
       a.blendo_id,
       a.blendo_imported_at,
       a.call_to_action_clicks,
       a.campaign_id,
       a.campaign_name,
       a.canvas_avg_view_percent,
       a.canvas_avg_view_time,
       a.clicks,
       a.cost_per_10_sec_video_view_action_type,
       a.cost_per_10_sec_video_view_value,
       a.cost_per_estimated_ad_recallers,
       a.cost_per_inline_link_click,
       a.cost_per_inline_post_engagement,
       a.cost_per_unique_action_type_action_type,
       a.cost_per_unique_action_type_value,
       a.cost_per_unique_click,
       a.cost_per_unique_inline_link_click,
       a.cpc,
       a.cpm,
       a.cpp,
       a.ctr,
       a.date_start,
       a.date_stop,
       a.deeplink_clicks,
       a.estimated_ad_recall_rate,
       a.estimated_ad_recall_rate_lower_bound,
       a.estimated_ad_recall_rate_upper_bound,
       a.estimated_ad_recallers,
       a.frequency,
       a.impressions,
       a.inline_link_click_ctr,
       a.inline_link_clicks,
       a.inline_post_engagement,
       a.objective,
       a.country,
       a.reach,
       a.social_clicks,
       a.social_impressions,
       a.social_reach,
       a.spend,
       a.total_action_value,
       a.unique_actions_action_type,
       a.unique_actions_value,
       a.unique_clicks,
       a.unique_ctr,
       a.unique_impressions,
       a.unique_inline_link_click_ctr,
       a.unique_inline_link_clicks,
       a.unique_link_clicks_ctr,
       a.unique_social_clicks,
       a.unique_social_impressions,
       a.video_10_sec_watched_actions_action_type,
       a.video_10_sec_watched_actions_value,
       a.video_15_sec_watched_actions_action_type,
       a.video_15_sec_watched_actions_value,
       a.video_30_sec_watched_actions_action_type,
       a.video_30_sec_watched_actions_value,
       a.video_avg_pct_watched_actions_action_type,
       a.video_avg_pct_watched_actions_value,
       a.video_avg_sec_watched_actions_action_type,
       a.video_avg_sec_watched_actions_value,
       a.video_complete_watched_actions_action_type,
       a.video_complete_watched_actions_value,
       a.video_p100_watched_actions_action_type,
       a.video_p100_watched_actions_value,
       a.video_p25_watched_actions_action_type,
       a.video_p25_watched_actions_value,
       a.video_p50_watched_actions_action_type,
       a.video_p50_watched_actions_value,
       a.video_p75_watched_actions_action_type,
       a.video_p75_watched_actions_value,
       a.video_p95_watched_actions_action_type,
       a.video_p95_watched_actions_value,
       a.website_clicks,
       tmp.MostRecentStop,
       tmp.MostRecentImp
FROM "ad_country" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "ad_country"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_at=tmp.MostRecentImp

SQL View (PostgreSQL): Latest Events for Facebook Ads account_insights table

CREATE materialized VIEW insightscountryview 
AS SELECT a.account_id, 
          a.account_name, 
          a.actions_action_type, 
          a.actions_value, 
          a.app_store_clicks, 
          a.blendo_id, 
          a.blendo_imported_at, 
          a.call_to_action_clicks, 
          a.canvas_avg_view_percent, 
          a.canvas_avg_view_time, 
          a.clicks, 
          a.cost_per_10_sec_video_view_action_type, 
          a.cost_per_10_sec_video_view_value, 
          a.cost_per_estimated_ad_recallers, 
          a.cost_per_inline_link_click, 
          a.cost_per_inline_post_engagement, 
          a.cost_per_unique_click, 
          a.cost_per_unique_inline_link_click, 
          a.cpc, 
          a.cpm, 
          a.cpp, 
          a.ctr, 
          a.date_start, 
          a.date_stop, 
          a.deeplink_clicks, 
          a.estimated_ad_recall_rate, 
          a.estimated_ad_recallers, 
          a.frequency, 
          a.impressions, 
          a.inline_link_click_ctr, 
          a.inline_link_clicks, 
          a.inline_post_engagement, 
          a.objective, 
          a.reach, 
          a.social_clicks, 
          a.social_impressions, 
          a.social_reach, 
          a.social_spend, 
          a.spend, 
          a.total_action_value, 
          a.unique_clicks, 
          a.unique_ctr, 
          a.unique_impressions, 
          a.unique_inline_link_click_ctr, 
          a.unique_inline_link_clicks, 
          a.unique_link_clicks_ctr, 
          a.unique_social_clicks, 
          a.unique_social_impressions, 
          a.video_10_sec_watched_actions_action_type, 
          a.video_10_sec_watched_actions_value, 
          a.video_15_sec_watched_actions_action_type, 
          a.video_15_sec_watched_actions_value, 
          a.video_30_sec_watched_actions_action_type, 
          a.video_30_sec_watched_actions_value, 
          a.video_avg_pct_watched_actions_action_type, 
          a.video_avg_pct_watched_actions_value, 
          a.video_avg_sec_watched_actions_action_type, 
          a.video_avg_sec_watched_actions_value, 
          a.video_complete_watched_actions_action_type, 
          a.video_complete_watched_actions_value, 
          a.video_p100_watched_actions_action_type, 
          a.video_p100_watched_actions_value, 
          a.video_p25_watched_actions_action_type, 
          a.video_p25_watched_actions_value, 
          a.video_p50_watched_actions_action_type, 
          a.video_p50_watched_actions_value, 
          a.video_p75_watched_actions_action_type, 
          a.video_p75_watched_actions_value, 
          a.video_p95_watched_actions_action_type, 
          a.video_p95_watched_actions_value, 
          a.website_clicks 
   FROM   "account_insights" AS a, 
          (SELECT Max(date_stop)          AS MostRecentStop, 
                  Max(blendo_imported_at) AS MostRecentImp, 
                  blendo_id 
           FROM   "account_insights" 
           GROUP  BY blendo_id)tmp 
   WHERE  ( a.blendo_id = tmp.blendo_id ) 
          AND a.date_stop = tmp.mostrecentstop 
          AND a.blendo_imported_at = tmp.mostrecentimp

 

SQL View (PostgreSQL): Latest Events for Facebook Ads account_insights_actions table

CREATE materialized VIEW AccInsightsActionsView AS
SELECT a.action_cost,
       a.action_value,
       a.blendo_imported_at,
       a.account_id,
       a.account_name,
       a.action_type,
       a.ad_id,
       a.adset_id,
       a.blendo_id,
       a.campaign_id,
       a.date_start,
       a.date_stop
FROM "account_insights_actions" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "account_insights_actions"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop=tmp.MostRecentStop
  AND a.blendo_imported_at=tmp.MostRecentImp

 

SQL View (PostgreSQL): Latest Events for Facebook Ads account_insights_unique_actions table

CREATE materialized VIEW InsightUniqueActionsView AS
SELECT a.action_cost,
       a.action_value,
       a.blendo_imported_at,
       a.account_id,
       a.account_name,
       a.action_type,
       a.blendo_id,
       a.date_start,
       a.date_stop
FROM "account_insights_unique_actions" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "account_insights_unique_actions"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_at = tmp.MostRecentImp

 

SQL View (PostgreSQL): Latest Events for Facebook Ads ad_conversion_specs table

CREATE materialized VIEW ConversionSpecsView AS
SELECT a.blendo_imported_at,
       a.action_type,
       a.application,
       a.blendo_id,
       a.conversion_id,
       a.event,
       a.object,
       a.page,
       a.parent_id,
       tmp.MostRecent,
       a.post,
       a.post_wall,
       a.response
FROM "ad_conversion_specs" AS a,

  ( SELECT max(parent_updated_at) AS MostRecent, blendo_id
   FROM "ad_conversion_specs"
   GROUP BY blendo_id)tmp
WHERE a.blendo_id=tmp.blendo_id
  AND a.parent_updated_at = tmp.MostRecent

 

SQL View (PostgreSQL): Latest Events for Facebook Ads insights_actions table

CREATE materialized VIEW InsightsActionsView AS
SELECT tmp.MostRecentImp,
       a.action_cost,
       a.action_type,
       a.action_value,
       a.ad_id,
       a.adset_id,
       a.blendo_id,
       a.campaign_id,
       a.date_start,
       tmp.MostRecentStop
FROM "insights_actions" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "insights_actions"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_at= tmp.MostRecentImp

 

SQL View (PostgreSQL): Latest Events for Facebook Ads insights_unique_actions table

CREATE materialized VIEW UniqueInsightsView AS
SELECT a.action_cost,
       a.action_value,
       tmp.MostRecentImp,
       a.account_id,
       a.account_name,
       a.action_type,
       a.ad_id,
       a.adset_id,
       a.blendo_id,
       a.campaign_id,
       a.date_start,
       tmp.MostRecentStop
FROM "insights_unique_actions" AS a,

  ( SELECT max(date_stop) AS MostRecentStop,
           max(blendo_imported_at) AS MostRecentImp,
           blendo_id
   FROM "insights_unique_actions"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_At = tmp.MostRecentImp

 

SQL View (PostgreSQL): Latest Events for Facebook Ads campaigns table

CREATE materialized VIEW CampaignsView AS
SELECT a.blendo_imported_at,
       a.account_id,
       a.budget_rebalance_flag,
       a.buying_type,
       a.can_use_spend_cap,
       a.configured_status,
       a.created_time,
       a.effective_status,
       a.id,
       a.name,
       a.objective,
       a.spend_cap,
       a.start_time,
       a.status,
       a.stop_time,
       tmp.MostRecent
FROM "campaigns" AS a,

  (SELECT max(updated_time) AS MostRecent, id
   FROM "campaigns"
   GROUP BY id)tmp
WHERE a.id=tmp.id
  AND a.updated_time=tmp.MostRecent