Facebook Ads Creative Reporting (GSheet) – Kitchn

JSON Recipe

Import the automation recipe(s) into your own account. Don’t know how? Learn here.

Screenshot Library

{"name":"Screenshot Library","description":"fb_creativereport_screenshots_20230814","nodes":{"fb_fetch_ad_v2_1":{"type":"fb_fetch_ad_v2","logging_enabled":false,"error_rules":[],"x":489,"y":378,"group":"group_1"},"sort_1":{"type":"sort","logging_enabled":false,"error_rules":[],"x":581,"y":363,"group":"group_2","inputs":{"direction":{"export_enabled":true,"data":"DESC"},"pointer":{"export_enabled":true,"data":"/impressions"}}},"google_sheets_append_table_rows_v3_1":{"type":"google_sheets_append_table_rows_v3","title":"Append Google Spreadsheet Rows","description":"Append table rows to a Google Spreadsheet","logging_enabled":true,"error_rules":[],"x":1719,"y":582,"group":"group_1","inputs":{"range":{"title":"range","description":"Input the range to search for the table to append to"},"spreadsheet_id":{"title":"spreadsheet_id","description":"Select a Google Spreadsheet file to append data to"}},"outputs":{"appended":{"title":"appended","description":"Triggered when appended"}}},"stop_1":{"type":"stop","logging_enabled":true,"error_rules":[],"x":144,"y":540,"inputs":{"stop":{"export_enabled":true}}},"for_each_v2_1":{"type":"for_each_v2","title":"For Each: Ad Id","description":"Loops over elements in a list","logging_enabled":false,"error_rules":[],"x":203,"y":367,"group":"group_1","inputs":{"items":{"title":"ad_ids","description":"The items to iterate over"}},"outputs":{"item":{"title":"ad_id","description":"The current item of the iteration"}}},"fb_fetch_insights_v6_1":{"type":"fb_fetch_insights_v6","title":"Fetch Facebook Insights","description":"Fetches insights and returns table rows","logging_enabled":true,"error_rules":[],"x":188,"y":221,"group":"group_2","inputs":{"date_range":{"export_enabled":true,"data":"last_30d"},"ad_account_ids":{"title":"ad_account_ids","description":"Select one or more Facebook Accounts to retrieve Insights from"},"action_report_time":{"export_enabled":true,"data":"impression"},"breakdowns":{"export_enabled":true,"data":[]},"action_breakdowns":{"export_enabled":true,"data":[]},"fields":{"export_enabled":true,"data":["impressions","ad_id","account_id","ad_name","account_name","adset_name","campaign_name"]},"use_unified_attribution_setting":{"export_enabled":true,"data":false},"level":{"export_enabled":true,"data":"ad"},"action_attribution_windows":{"export_enabled":true,"data":["default"]},"time_increment":{"export_enabled":true,"data":"all_days"}}},"start_1":{"type":"start","title":"Start","description":"Starts an automation","logging_enabled":true,"error_rules":[],"x":133,"y":456},"google_sheets_fetch_table_rows_v3_1":{"type":"google_sheets_fetch_table_rows_v3","logging_enabled":false,"error_rules":[],"x":257,"y":323,"group":"group_3","inputs":{"date_time_render_option":{"export_enabled":true,"data":"FORMATTED_STRING"},"value_render_option":{"export_enabled":true,"data":"FORMATTED_VALUE"},"dimension":{"export_enabled":true,"data":"ROWS"}}},"fb_create_ad_screenshot_1":{"type":"fb_create_ad_screenshot","logging_enabled":false,"error_rules":[],"x":785,"y":356,"group":"group_1","inputs":{"ad_format":{"export_enabled":true,"data":"INSTAGRAM_STANDARD"}}},"extract_list_1":{"type":"extract_list","logging_enabled":false,"error_rules":[],"x":874,"y":394,"group":"group_2","inputs":{"pointer":{"export_enabled":true,"data":"/ad_id"}}},"parse_strings_to_ids_1":{"type":"parse_strings_to_ids","logging_enabled":false,"error_rules":[],"x":1426,"y":430,"group":"group_3","inputs":{"schema":{"export_enabled":true,"data":"fb_ad_id"}},"outputs":{"result":{"title":"ad_ids","description":"The result of the parsed strings"}}},"parse_strings_to_ids_2":{"type":"parse_strings_to_ids","logging_enabled":false,"error_rules":[],"x":1267,"y":411,"group":"group_2","inputs":{"schema":{"export_enabled":true,"data":"fb_ad_id"}},"outputs":{"result":{"title":"ad_ids","description":"The result of the parsed strings"}}},"filter_v2_1":{"type":"filter_v2","title":"Filter out header and empty row","description":"filters a items of any by atributes of it's schema","logging_enabled":false,"error_rules":[],"x":603,"y":409,"group":"group_3","inputs":{"filters":{"export_enabled":true,"data":[{"value":"Screenshots!A1:","pointer":"/range","operator":"not_contains"},{"value":[],"pointer":"/columns","operator":"!="}]}}},"extract_list_2":{"type":"extract_list","logging_enabled":false,"error_rules":[],"x":988,"y":376,"group":"group_3","inputs":{"pointer":{"export_enabled":true,"data":"/columns/0"}}},"calculate_set_operation_1":{"type":"calculate_set_operation","title":"Ad Ids without Screenshots","description":"Calculates operation between two sets","logging_enabled":false,"error_rules":[],"x":886,"y":600,"group":"group_4","inputs":{"operator":{"title":"operator","description":"Set operator","export_enabled":true,"data":"-"}},"outputs":{"data":{"title":"ad_ids","description":"The operated data"}}},"google_sheets_select_spreadsheet_id_v2_1":{"type":"google_sheets_select_spreadsheet_id_v2","title":"Select Google Spreadsheet","description":"Selects spreadsheet id from a list of options and can set a range","logging_enabled":false,"error_rules":[],"x":334,"y":126,"inputs":{"range":{"title":"range","description":"The range in A1 notation to fetch"},"spreadsheet_id":{"title":"spreadsheet_id","description":"The spreadsheet to fetch from"}}},"data_relay_1":{"type":"data_relay","title":"Relay range","description":"Relays data","logging_enabled":false,"error_rules":[],"x":871,"y":754,"group":"group_1","inputs":{"data":{"title":"range","description":"Data to relay"}},"outputs":{"data":{"title":"range","description":"Data to relay"}}},"delay_to_1":{"type":"delay_to","logging_enabled":false,"error_rules":[],"x":282,"y":618,"inputs":{"weekdays":{"export_enabled":true,"data":["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"]},"time":{"export_enabled":true,"data":"03:00:00+02:00"}}},"map_any_to_table_rows_1":{"type":"map_any_to_table_rows","logging_enabled":false,"error_rules":[],"x":1398,"y":434,"group":"group_1","inputs":{"add_header":{"export_enabled":true},"schema":{"export_enabled":true,"data":"any"}}},"patch_json_v3_1":{"type":"patch_json_v3","logging_enabled":false,"error_rules":[],"x":958,"y":447,"group":"group_5","inputs":{"value":{"title":"errors","description":"A value that can be used in patches"},"patches":{"export_enabled":true,"data":[{"op":"add","path":"/error","value":"{{inputValue('/0')}}"}]}}},"patch_json_v3_2":{"type":"patch_json_v3","title":"Patch screenshot url","description":"Apply JSON patches against row with url","logging_enabled":false,"error_rules":[],"x":659,"y":424,"group":"group_5","inputs":{"value":{"title":"url","description":"An url from schema type url"},"data":{"title":"ad","description":"The data the patches are applied to"},"patches":{"export_enabled":true,"data":[{"op":"add","path":"/link","value":"{{inputValue('/url')}}"},{"op":"add","path":"/timestamp","value":"{{timeNow(\"UTC\")}}"}]}}},"google_sheets_fetch_table_row_mapping_1":{"type":"google_sheets_fetch_table_row_mapping","logging_enabled":false,"error_rules":[],"x":1009,"y":361}},"edges":[{"id":"fb_fetch_insights_v6_1.fetched:sort_1.sort","points":[]},{"id":"fb_fetch_insights_v6_1.insights:sort_1.items","points":[]},{"id":"calculate_set_operation_1.data:for_each_v2_1.items","points":[]},{"id":"google_sheets_fetch_table_rows_v3_1.rows:filter_v2_1.items","points":[]},{"id":"google_sheets_fetch_table_rows_v3_1.fetched:filter_v2_1.filter","points":[]},{"id":"google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_append_table_rows_v3_1.spreadsheet_id","points":[]},{"id":"google_sheets_select_spreadsheet_id_v2_1.range:google_sheets_fetch_table_rows_v3_1.range","points":[]},{"id":"google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_fetch_table_rows_v3_1.spreadsheet_id","points":[]},{"id":"for_each_v2_1.item:fb_fetch_ad_v2_1.ad_id","points":[]},{"id":"for_each_v2_1.do:fb_fetch_ad_v2_1.fetch","points":[]},{"id":"for_each_v2_1.item:fb_create_ad_screenshot_1.ad_id","points":[]},{"id":"fb_fetch_ad_v2_1.fetched:fb_create_ad_screenshot_1.create","points":[]},{"id":"sort_1.items:extract_list_1.data_list","points":[]},{"id":"sort_1.sorted:extract_list_1.extract","points":[]},{"id":"extract_list_1.data_list:parse_strings_to_ids_2.values","points":[]},{"id":"extract_list_1.extracted:parse_strings_to_ids_2.parse","points":[]},{"id":"parse_strings_to_ids_2.result:calculate_set_operation_1.a","points":[]},{"id":"parse_strings_to_ids_2.parsed:google_sheets_fetch_table_rows_v3_1.fetch","points":[]},{"id":"filter_v2_1.items:extract_list_2.data_list","points":[]},{"id":"filter_v2_1.filtered:extract_list_2.extract","points":[]},{"id":"extract_list_2.data_list:parse_strings_to_ids_1.values","points":[]},{"id":"parse_strings_to_ids_1.result:calculate_set_operation_1.b","points":[]},{"id":"parse_strings_to_ids_1.parsed:calculate_set_operation_1.calculate","points":[]},{"id":"extract_list_2.extracted:parse_strings_to_ids_1.parse","points":[]},{"id":"google_sheets_append_table_rows_v3_1.appended:for_each_v2_1.next","points":[]},{"id":"google_sheets_select_spreadsheet_id_v2_1.range:data_relay_1.data","points":[]},{"id":"data_relay_1.data:google_sheets_append_table_rows_v3_1.range","points":[]},{"id":"start_1.start:fb_fetch_insights_v6_1.fetch","points":[]},{"id":"for_each_v2_1.done:delay_to_1.start","points":[]},{"id":"delay_to_1.delayed:fb_fetch_insights_v6_1.fetch","points":[]},{"id":"google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_fetch_table_row_mapping_1.spreadsheet_id","points":[]},{"id":"google_sheets_fetch_table_row_mapping_1.mapping:map_any_to_table_rows_1.mapping","points":[]},{"id":"patch_json_v3_2.patched:patch_json_v3_1.patch","points":[]},{"id":"patch_json_v3_1.data:map_any_to_table_rows_1.data","points":[]},{"id":"patch_json_v3_2.data:patch_json_v3_1.data","points":[]},{"id":"patch_json_v3_1.patched:map_any_to_table_rows_1.map","points":[]},{"id":"map_any_to_table_rows_1.rows:google_sheets_append_table_rows_v3_1.rows","points":[]},{"id":"fb_create_ad_screenshot_1.url:patch_json_v3_2.value","points":[]},{"id":"fb_create_ad_screenshot_1.created:patch_json_v3_2.patch","points":[]},{"id":"calculate_set_operation_1.calculated:google_sheets_fetch_table_row_mapping_1.fetch","points":[]},{"id":"google_sheets_select_spreadsheet_id_v2_1.range:google_sheets_fetch_table_row_mapping_1.range","points":[]},{"id":"google_sheets_fetch_table_row_mapping_1.fetched:for_each_v2_1.start","points":[]},{"id":"map_any_to_table_rows_1.mapped:google_sheets_append_table_rows_v3_1.append","points":[]},{"id":"fb_fetch_ad_v2_1.ad:patch_json_v3_2.data","points":[]},{"id":"fb_create_ad_screenshot_1.errors:patch_json_v3_1.value","points":[]}],"groups":{"group_1":{"title":"Create screenshot and add to sheet","description":"","x":1373,"y":440,"inputs":["google_sheets_append_table_rows_v3_1.spreadsheet_id","for_each_v2_1.items","for_each_v2_1.start","data_relay_1.data","fb_create_ad_screenshot_1.ad_format","map_any_to_table_rows_1.mapping"],"outputs":["for_each_v2_1.done"]},"group_4":{"title":"Get Ad Ids","description":"","x":650,"y":255,"inputs":["fb_fetch_insights_v6_1.fetch","google_sheets_fetch_table_rows_v3_1.spreadsheet_id","google_sheets_fetch_table_rows_v3_1.range","fb_fetch_insights_v6_1.date_range","fb_fetch_insights_v6_1.ad_account_ids"],"outputs":["calculate_set_operation_1.calculated","calculate_set_operation_1.data"]},"group_2":{"title":"Ad Ids from account","description":"Currently running ads with sorted by impressions","x":236,"y":639,"group":"group_4","inputs":["fb_fetch_insights_v6_1.ad_account_ids","fb_fetch_insights_v6_1.fetch","fb_fetch_insights_v6_1.date_range"],"outputs":["parse_strings_to_ids_2.result","parse_strings_to_ids_2.parsed"]},"group_3":{"title":"Ad Ids with screenshot","description":"","x":608,"y":427,"group":"group_4","inputs":["google_sheets_fetch_table_rows_v3_1.fetch","google_sheets_fetch_table_rows_v3_1.range","google_sheets_fetch_table_rows_v3_1.spreadsheet_id"],"outputs":["parse_strings_to_ids_1.result","parse_strings_to_ids_1.parsed"]},"group_5":{"title":"Build Row","description":"","x":1114,"y":350,"group":"group_1","inputs":["patch_json_v3_2.value","patch_json_v3_1.value","patch_json_v3_2.data","patch_json_v3_2.patch"],"outputs":["patch_json_v3_1.data","patch_json_v3_1.patched"]}},"widgets":{"widget_1":{"title":"Spreadsheet","description":"Choose your spreadsheet.","position":2,"input":"google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id"},"widget_2":{"title":"Ad Format","description":"Choose a format for the screenshots.","position":5,"icon":"la la-facebook","input":"fb_create_ad_screenshot_1.ad_format"},"widget_3":{"title":"Lookback","description":"Filter those insights with a specific date_preset value","position":4,"icon":"las la-calendar","input":"fb_fetch_insights_v6_1.date_range"},"widget_4":{"title":"Ad Account","description":"Select one or more Facebook Accounts.","position":0,"input":"fb_fetch_insights_v6_1.ad_account_ids"},"widget_5":{"title":"Tab","description":"The tab or range in A1 notation.","position":3,"input":"google_sheets_select_spreadsheet_id_v2_1.range"}},"variables":{},"config":{"capture_inputs_enabled":false,"caching_enabled":false},"tags":[]}

eCommerce

{ "name":"Performance Exporter eCom", "description":"fb_creativereport_performance_ecom20220802", "nodes":{ "start_1":{ "type":"start", "logging_enabled":false, "x":148, "y":81 }, "stop_1":{ "type":"stop", "logging_enabled":false, "x":450, "y":50, "inputs":{ "stop":{ "export_enabled":true } } }, "fb_select_ad_account_ids_v2_1":{ "type":"fb_select_ad_account_ids_v2", "logging_enabled":false, "x":127, "y":211, "inputs":{ "ad_account_ids":{ "title":"ad_account_ids", "description":"Select one or more Facebook Accounts" } } }, "map_any_to_table_rows_1":{ "type":"map_any_to_table_rows", "logging_enabled":false, "x":773, "y":262, "inputs":{ "add_header":{ "export_enabled":true, "data":true }, "mapping":{ "export_enabled":true, "data":{ "A":"/date_start", "B":"/ad_id", "C":"/adset_id", "D":"/campaign_id", "E":"/ad_name", "F":"/adset_name", "G":"/campaign_name", "H":"/spend", "J":"/outbound_clicks_outbound_click", "I":"/impressions", "M":"/action_values_omni_purchase", "L":"/actions_omni_purchase", "K":"/actions_video_view" } }, "schema":{ "export_enabled":true, "data":"fb_insights" } } }, "delay_to_1":{ "type":"delay_to", "logging_enabled":false, "x":168, "y":645, "inputs":{ "weekdays":{ "export_enabled":true, "data":[ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ] }, "time":{ "export_enabled":true, "data":"05:00:00+02:00" } } }, "google_sheets_select_spreadsheet_id_v2_1":{ "type":"google_sheets_select_spreadsheet_id_v2", "logging_enabled":false, "x":912, "y":39, "inputs":{ "range":{ "title":"range", "description":"The range in A1 notation to fetch" }, "spreadsheet_id":{ "title":"spreadsheet_id", "description":"The spreadsheet to fetch from" } } }, "google_sheets_append_table_rows_v3_1":{ "type":"google_sheets_append_table_rows_v3", "logging_enabled":false, "x":1554, "y":441 }, "fb_fetch_insights_v6_1":{ "type":"fb_fetch_insights_v6", "logging_enabled":false, "x":448, "y":134, "inputs":{ "action_report_time":{ "export_enabled":true, "data":"conversion" }, "breakdowns":{ "export_enabled":true }, "action_breakdowns":{ "export_enabled":true }, "fields":{ "export_enabled":true, "data":[ "account_id", "account_name", "spend", "impressions", "actions", "action_values", "adset_id", "adset_name", "campaign_id", "campaign_name", "date_start", "reach", "ad_id", "ad_name", "video_play_actions", "outbound_clicks" ] }, "use_unified_attribution_setting":{ "export_enabled":true, "data":true }, "level":{ "export_enabled":true, "data":"ad" }, "action_attribution_windows":{ "export_enabled":true, "data":[ "default" ] }, "time_increment":{ "export_enabled":true, "data":1 }, "date_range":{ "export_enabled":true, "data":"last_30d" } } }, "google_sheets_clear_table_rows_v3_1":{ "type":"google_sheets_clear_table_rows_v3", "logging_enabled":false, "x":1214, "y":315 } }, "edges":[ "google_sheets_clear_table_rows_v3_1.cleared:google_sheets_append_table_rows_v3_1.append", "map_any_to_table_rows_1.rows:google_sheets_append_table_rows_v3_1.rows", "google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_clear_table_rows_v3_1.spreadsheet_id", "google_sheets_select_spreadsheet_id_v2_1.range:google_sheets_append_table_rows_v3_1.range", "google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_append_table_rows_v3_1.spreadsheet_id", "fb_select_ad_account_ids_v2_1.ad_account_ids:fb_fetch_insights_v6_1.ad_account_ids", "delay_to_1.delayed:fb_fetch_insights_v6_1.fetch", "fb_fetch_insights_v6_1.insights:map_any_to_table_rows_1.data", "fb_fetch_insights_v6_1.fetched:map_any_to_table_rows_1.map", "google_sheets_append_table_rows_v3_1.appended:delay_to_1.start", "map_any_to_table_rows_1.mapped:google_sheets_clear_table_rows_v3_1.clear", "google_sheets_select_spreadsheet_id_v2_1.range:google_sheets_clear_table_rows_v3_1.range", "start_1.start:fb_fetch_insights_v6_1.fetch" ], "groups":{ }, "widgets":{ "widget_1":{ "title":"Ad Accounts", "description":"Select one or more Facebook Accounts.", "position":0, "input":"fb_select_ad_account_ids_v2_1.ad_account_ids" }, "widget_2":{ "title":"Spreadsheet", "description":"Select a spreadsheet.", "position":2, "input":"google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id" }, "widget_3":{ "title":"Tab / Range", "description":"The spreadsheet's tab or range in A1 notation.", "position":3, "icon":"las la-table", "input":"google_sheets_select_spreadsheet_id_v2_1.range" }, "widget_4":{ "title":"Weekdays", "description":"On which das of the week should this automation run?", "position":5, "icon":"las la-calendar-week", "input":"delay_to_1.weekdays" }, "widget_5":{ "title":"Time", "description":"And what time of day?", "position":6, "icon":"las la-clock", "input":"delay_to_1.time" } }, "config":{ "capture_inputs_enabled":false, "caching_enabled":false }, "tags":[ ] }

LeadGen

{ "name":"Performance exporter LeadGen", "description":"fb_creativereport_performance_leadgen_20220802", "nodes":{ "start_1":{ "type":"start", "logging_enabled":false, "x":148, "y":81 }, "stop_1":{ "type":"stop", "logging_enabled":false, "x":450, "y":50, "inputs":{ "stop":{ "export_enabled":true } } }, "fb_select_ad_account_ids_v2_1":{ "type":"fb_select_ad_account_ids_v2", "logging_enabled":false, "x":127, "y":211, "inputs":{ "ad_account_ids":{ "title":"ad_account_ids", "description":"Select one or more Facebook Accounts" } } }, "map_any_to_table_rows_1":{ "type":"map_any_to_table_rows", "logging_enabled":false, "x":773, "y":262, "inputs":{ "add_header":{ "export_enabled":true, "data":true }, "mapping":{ "export_enabled":true, "data":{ "A":"/date_start", "B":"/ad_id", "C":"/adset_id", "D":"/campaign_id", "E":"/ad_name", "F":"/adset_name", "G":"/campaign_name", "H":"/spend", "J":"/outbound_clicks_outbound_click", "I":"/impressions", "L":"/actions_lead", "K":"/actions_video_view" } }, "schema":{ "export_enabled":true, "data":"fb_insights" } } }, "delay_to_1":{ "type":"delay_to", "logging_enabled":false, "x":168, "y":645, "inputs":{ "weekdays":{ "export_enabled":true, "data":[ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ] }, "time":{ "export_enabled":true, "data":"05:00:00+02:00" } } }, "google_sheets_select_spreadsheet_id_v2_1":{ "type":"google_sheets_select_spreadsheet_id_v2", "logging_enabled":false, "x":912, "y":39, "inputs":{ "range":{ "title":"range", "description":"The range in A1 notation to fetch" }, "spreadsheet_id":{ "title":"spreadsheet_id", "description":"The spreadsheet to fetch from" } } }, "google_sheets_append_table_rows_v3_1":{ "type":"google_sheets_append_table_rows_v3", "logging_enabled":false, "x":1554, "y":441 }, "fb_fetch_insights_v6_1":{ "type":"fb_fetch_insights_v6", "logging_enabled":false, "x":448, "y":134, "inputs":{ "action_report_time":{ "export_enabled":true, "data":"conversion" }, "breakdowns":{ "export_enabled":true }, "action_breakdowns":{ "export_enabled":true }, "fields":{ "export_enabled":true, "data":[ "account_id", "account_name", "spend", "impressions", "actions", "action_values", "adset_id", "adset_name", "campaign_id", "campaign_name", "date_start", "reach", "ad_id", "ad_name", "video_play_actions", "outbound_clicks" ] }, "use_unified_attribution_setting":{ "export_enabled":true, "data":true }, "level":{ "export_enabled":true, "data":"ad" }, "action_attribution_windows":{ "export_enabled":true, "data":[ "default" ] }, "time_increment":{ "export_enabled":true, "data":1 }, "date_range":{ "export_enabled":true, "data":"last_30d" } } }, "google_sheets_clear_table_rows_v3_1":{ "type":"google_sheets_clear_table_rows_v3", "logging_enabled":false, "x":1214, "y":315 } }, "edges":[ "google_sheets_clear_table_rows_v3_1.cleared:google_sheets_append_table_rows_v3_1.append", "map_any_to_table_rows_1.rows:google_sheets_append_table_rows_v3_1.rows", "google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_clear_table_rows_v3_1.spreadsheet_id", "google_sheets_select_spreadsheet_id_v2_1.range:google_sheets_append_table_rows_v3_1.range", "google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id:google_sheets_append_table_rows_v3_1.spreadsheet_id", "fb_select_ad_account_ids_v2_1.ad_account_ids:fb_fetch_insights_v6_1.ad_account_ids", "delay_to_1.delayed:fb_fetch_insights_v6_1.fetch", "fb_fetch_insights_v6_1.insights:map_any_to_table_rows_1.data", "fb_fetch_insights_v6_1.fetched:map_any_to_table_rows_1.map", "google_sheets_append_table_rows_v3_1.appended:delay_to_1.start", "map_any_to_table_rows_1.mapped:google_sheets_clear_table_rows_v3_1.clear", "google_sheets_select_spreadsheet_id_v2_1.range:google_sheets_clear_table_rows_v3_1.range", "start_1.start:fb_fetch_insights_v6_1.fetch" ], "groups":{ }, "widgets":{ "widget_1":{ "title":"Ad Accounts", "description":"Select one or more Facebook Accounts.", "position":0, "input":"fb_select_ad_account_ids_v2_1.ad_account_ids" }, "widget_2":{ "title":"Spreadsheet", "description":"Select a spreadsheet.", "position":2, "input":"google_sheets_select_spreadsheet_id_v2_1.spreadsheet_id" }, "widget_3":{ "title":"Tab / Range", "description":"The spreadsheet's tab or range in A1 notation.", "position":3, "icon":"las la-table", "input":"google_sheets_select_spreadsheet_id_v2_1.range" }, "widget_4":{ "title":"Weekdays", "description":"On which das of the week should this automation run?", "position":5, "icon":"las la-calendar-week", "input":"delay_to_1.weekdays" }, "widget_5":{ "title":"Time", "description":"And what time of day?", "position":6, "icon":"las la-clock", "input":"delay_to_1.time" } }, "config":{ "capture_inputs_enabled":false, "caching_enabled":false }, "tags":[ ] }

How To

Step 1

After importing the automations into your account, create a copy of the spreadsheet template for eCommerce or LeadGen, depending on what automation you chose.

Step 2 - Screenshot Library

Choose an ad account, and the previously created spreadsheet and “screnshots” tab.

Choose a timeframe for which you would like to generate screenshots. For any ad that had at least 1 impression in the selected timeframe one screenshot will be generated. We recommend last_30d. You may run the automation once for a longer timeframe, and then set it to a shorter timeframe (like last_3d or yesterday) to use less resources.

Every time the automation runs it will check whether an ad (identified by its id) has already an existing screenshot in the spreadsheet. If so, it will be skipped.

Choose the ad format. We recommend: “INSTAGRAM_STANDARD”

Alternatively you may choose “MOBILE_FEED_STANDARD” for a Facebook screenshot, though some accounts may get faulty screenshots with this option.

Now you can run the automation. It will immediately start creating screenshots for any ads that had delivery in the specified date range, adding about 5 screenshots per minute to the sheet. Once all ads have been screenshotted, the automation will go idle and wait until the following morning to check for any new ads that may have been started today and weren’t previously screenshotted.

Step 3 - Performance Export

In order to get relevant data into our report, we will need to pull Facebook insights data on ad level into the report.

Choose an ad account and the previously created spreadsheet and the “import” tab. With the default settings, it will be updated every morning. Per default, the report will show data for the last 30 days broken down by day.

As you can see above, no metrics are selected in this view as we are using the default (eCommerce) settings to output: spend, impressions, outbound clicks, 3s video views, purchases and revenue.

Run the automation.

Step 4 - Working with the report

Besides the absolute values exported, a few key metrics will also be automatically calculated:

CTR: outbound clicks / impressions

CPC: spend / outbound clicks

CAC: spend / purchases

CVR: spend / outbound clicks

ROAS: revenue / spend

Thumbstop Ratio: 3s video views / impressions

All data will always be aggregated by ad name!

In the spreadsheet, there are two import tabs (import and screenshots) where data gets pushed into. You generally won’t ever have to work in these.

There are also 2 data manipulation sheets and one helper lookup sheet to make the multi-level dropdown work.

As seen above all tabs other than “Report” are hidden but can be made visible and customized (more below).

In the report tab, you will find the 4 editable fields marked with a light yellow.

While you can choose a campaign, an adset belonging to said campaign and a month, you can also leave any or all of them empty, which will aggregate the ad level data across the entire account.

So e.g. leaving the month empty will simply aggregate all data across the entire selected timeframe.

Additionally, you can choose how many ads should be visible.

💡

Any cells with formulas inside the spreadsheet are marked purple and are generally hidden. E.g. in the Report tab, all formulas are array formulas and work across the entire width of the spreadsheet but all live in column C.

Advanced (1): Customizing dropdowns

Note: In a previous version the default was set to include monthly data. This has been changed to daily data in the meantime, so the time breakdown will look differently.

The default dropdowns allow you to filter your ad performance by Campaign and Adset names. It is absolutely possible to change this to support something like Geo and Campaign instead.

But any other breakdowns are also possible - it just depends on the ability to read out the information from the existing campaign or adset names.

In order to do this, we will add a column to the “prepare” tab, change the QUERY() formula in the “aggregate” tab and lastly, re-name the field in our “Report” tab.

First, unhide the “aggregate” and “prepare” tabs.

The “prepare” tab combines the data export and some of the screenshot information and makes sure all metrics are correctly idenified as such (and not as text).

i) Adding a new column with extra information, e.g. Geo

Extracting text with the MID() formula

In this Demo spreadsheet I show how we can extract the GEO information with a simple MID() formula by reading out 2 characters, starting at the 6th from the adset name.

=MID(F2,6,2)

Note that we are using an Arrayformula that can take a range (in this case a whole column) instead of individual cells as reference. So the actual formula in cell O2 is:

=ArrayFormula(MID(F2:F,6,2))

This generates the output on the right.

So when should you use this? Generally, the MID() formula makes sense when we know at what character position the first character lives (in our case 6) and how long the text element is (in our case 2 characters long).

It is possible to use dynamic references by using the FIND() formula to specify the starting position. See the actual formula from cell P2 below. If you want to learn more, take a look at this Youtube tutorial.

=ArrayFormula(MID(F2:F,FIND(" - ",F2:F)+3,2))

Extracting text with the REGEXEXTRACT() formula

A more advanced and powerful way to do the same is the REGEXEXTRACT() formula. It allows you to dynamically extract exactly the element you are looking for and in itself allows you to do so in various ways. For more information, check out cells Q2 and R2 or this Youtube tutorial.

ii) Making the column available as a dropdown

Now that we have added an extra column, we need to make sure that we can use it in out report. In order to to that we need to edit the QUERY() formula in cell A1 of the “aggregate” tab.

=QUERY(prepare!A:X,"select A, E, G, F, sum(H), sum(I), sum(J), sum(K), sum(L), sum(M) group by A, E, G, F")

The QUERY() formula works a bit like SQL and is Googlesheet’s way of dynamically accessing a table’s (i.e. tab’s) data and manipulating it on the fly before returning a new table.

As you can see we are selecting the columns we want to use from the “prepare” tab. We only need to focus on the third and fourth column, which represent the top and middle dropdown in our “Report” tab.

As you can see above selecting column “G” returns the campaign name and “F” returns the adset name.

So if we want to have “Geo” and “Campaign” instead of “Campaign” and “Adset” as our dropdowns, we simply need to change the formula to access our Geo information from the newly created column O.

=QUERY(prepare!A:X,"select A, E, O, G, sum(H), sum(I), sum(J), sum(K), sum(L), sum(M) group by A, E, O, G")

Note that we also need to “group by” the same columns towards the end of the formula.

iii) Change the field names

And this is it. Change the field names to “Geo” and “Campaign” and validate that everything works as expected.

Anvanced (2): Customizing timeframes and time breakdowns

Note: In a previous version the default was set to include monthly data. This has been changed to daily data in the meantime. The following tutorial will be useful if you don’t want to have a daily breakdown.

Anvanced (3): Replacing the main conversion metric

Note: In a previous version the default was set to include monthly data. This has been changed to daily data in the meantime, so the time breakdown will look differently.