I am using the Facebook Marketing API for my company's reporting. I need to start getting the destination URL for ads to link the spend data with the correct ad group (as stored in our personal db). However when I make this extra call it is to a report that does not support async, and we reach our rate limit quick. Any ideas on how to simplify the AdInsights and the Creative information into one call? Or lessen the load of the Creative information?
# This program downloads all relevant Facebook traffic info as a csv file
# This program requires info from the Facebook Ads API: https://github.com/facebook/facebook-python-ads-sdk
# Import all the facebook mumbo jumbo
from facebookads.api import FacebookAdsApi
from facebookads.adobjects.adsinsights import AdsInsights
from facebookads.adobjects.adaccount import AdAccount
from facebookads.adobjects.business import Business
from facebookads.adobjects.adreportrun import AdReportRun
from facebookads.adobjects.adcreative import AdCreative
# Import th csv writer and the date/time function
import datetime
from datetime import date
import time
from datetime import timedelta
import csv
import string
# Set the info to get connected to the API. Do NOT share this info
my_app_id = '***'
my_app_secret = '***'
my_access_token = '***'
# Start the connection to the facebook API
FacebookAdsApi.init(my_app_id, my_app_secret, my_access_token)
# Create a business object for the *** business account
#business = Business('***')
# Get yesterday's date for the filename, and the csv data
yesterdayday = date.today() - timedelta(days=1)
yesterdayhyphen = (yesterdayday).strftime('%Y-%m-%d')
# Define the destination filename
filename = yesterdayhyphen + '_fb.csv'
filelocation = "/cron/downloads/"+ filename
# Get all ad accounts on the business account
#accounts = business.get_owned_ad_accounts(fields=[AdAccount.Field.id])
accounts = ['act_***', 'act_***', 'act_***', 'act_***', 'act_***', 'act_***', 'act_***', 'act_***']
# Open or create new file
try:
csvfile = open(filelocation , 'w+', 0777)
except:
print ("Cannot open file.")
# To keep track of rows added to file
rows = 0
try:
# Create file writer
filewriter = csv.writer(csvfile, delimiter=',')
except Exception as err:
print(err)
# Iterate through the adaccounts
for account in accounts:
# Create an addaccount object from the adaccount id to make it possible to get insights
#tempaccount = AdAccount(account[AdAccount.Field.id])
tempaccount = AdAccount(account)
# Grab insight info for all ads in the adaccount
ads = tempaccount.get_insights(params = {'level' : 'ad',
'date_preset' : 'yesterday' },
fields = [AdsInsights.Field.account_id,
AdsInsights.Field.account_name,
AdsInsights.Field.ad_id,
AdsInsights.Field.ad_name,
AdsInsights.Field.adset_id,
AdsInsights.Field.adset_name,
AdsInsights.Field.campaign_id,
AdsInsights.Field.campaign_name,
AdsInsights.Field.cost_per_outbound_click,
AdsInsights.Field.outbound_clicks,
AdsInsights.Field.spend],
async = True)
ads.remote_read()
while ((ads[AdReportRun.Field.async_percent_completion] < 100) and (ads[AdReportRun.Field.async_status] != "Job Completed")):
time.sleep(1)
ads.remote_read()
time.sleep(1)
ads = ads.get_result()
print(ads)
adcreatives = tempaccount.get_ad_creatives(params = {'level' : 'ad',
'date_preset' : 'yesterday'},
fields = [AdCreative.Field.object_story_spec])
# Iterate through all accounts in the business account
for ad in ads:
# Set default values in case the insight info is empty
date = yesterdayhyphen
accountid = ad[AdsInsights.Field.account_id]
accountname = ""
adid = ""
adname = ""
adsetid = ""
adsetname = ""
campaignid = ""
campaignname = ""
costperoutboundclick = ""
outboundclicks = ""
spend = ""
destinationurl = ""
# Set values from insight data
if ('account_id' in ad) :
accountid = ad[AdsInsights.Field.account_id]
if ('account_name' in ad) :
accountname = ad[AdsInsights.Field.account_name]
if ('ad_id' in ad) :
adid = ad[AdsInsights.Field.ad_id]
if ('ad_name' in ad) :
adname = ad[AdsInsights.Field.ad_name]
if ('adset_id' in ad) :
adsetid = ad[AdsInsights.Field.adset_id]
if ('adset_name' in ad) :
adsetname = ad[AdsInsights.Field.adset_name]
if ('campaign_id' in ad) :
campaignid = ad[AdsInsights.Field.campaign_id]
if ('campaign_name' in ad) :
campaignname = ad[AdsInsights.Field.campaign_name]
if ('cost_per_outbound_click' in ad) : # This is stored strangely, takes a few steps to break through the layers
costperoutboundclicklist = ad[AdsInsights.Field.cost_per_outbound_click]
costperoutboundclickdict = costperoutboundclicklist[0]
costperoutboundclick = costperoutboundclickdict.get('value')
if ('outbound_clicks' in ad) : # This is stored strangely, takes a few steps to break through the layers
outboundclickslist = ad[AdsInsights.Field.outbound_clicks]
outboundclicksdict = outboundclickslist[0]
outboundclicks = outboundclicksdict.get('value')
if ('spend' in ad) :
spend = ad[AdsInsights.Field.spend]
for adcreative in adcreatives:
if ( ad[AdsInsights.Field.ad_id] == adcreative[ 'id' ] ):
destinationurl = adcreative[AdCreative.Field.object_story_spec.link_url]
# Write all ad info to the file, and increment the number of rows that will display
filewriter.writerow([date, accountid, accountname, adid, adname, adsetid, adsetname, campaignid, campaignname, costperoutboundclick, outboundclicks, spend, destinationurl])
rows += 1
csvfile.close()
# Print report
print (str(rows) + " rows added to the file " + filelocation)
Here is how I am doing it with php:
<?php
$dbhost = "***";
$dbname = "***";
$dbtable = "***";
$dbusername ="***";
$dbpassword = "***";
$accounts = [ *** ];
$i = "-1";
$yesterday = date( 'Y-m-d', strtotime( $i.' day' ) ) . " 01:01:01";
echo $yesterday;
$yesterday_no_time = date( 'Y-m-d', strtotime( $i.' day' ) );
$conn = mysqli_connect( $dbhost, $dbusername, $dbpassword, $dbname );
if ($conn->connect_error) {
die( "Connection failed: " . $conn->connect_error );
}
$traffic_source_id = 2;
$delete = "DELETE FROM spend WHERE date( created_at ) = '" . date( 'Y-m-d', strtotime( $i.' day' ) ) . "' AND traffic_source_id = " . $traffic_source_id;
if ($conn->query($delete) === TRUE) {
if( $conn->affected_rows > 0 ) {
echo "Yesterday's data already exists. " . $conn->affected_rows . " record(s) deleted successfully. \n";
}
} else {
echo "Error deleting record(s): " . $conn->error;
}
$select = $conn->prepare( "SELECT website_id, website_type_id, user_id, campaign_id FROM groups WHERE id = ?" );
$select->bind_param( "i", $group_id );
$insert = $conn->prepare( "INSERT INTO spend ( campaign_name, group_name, ad_name, " .
"source_campaign_id, source_group_id, source_ad_id, impressions, clicks, leads, spend, website_id, website_type_id,".
"traffic_source_id, user_id, campaign_id, group_id, created_at, updated_at ) VALUES " .
"( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
$insert->bind_param( "sssiiiiiidiiiiiiss", $campaign_name, $group_name, $ad_name, $source_campaign_id,
$source_group_id, $source_ad_id, $impressions, $clicks, $leads, $spend, $website_id, $website_type_id, $traffic_source_id,
$user_id, $campaign_id, $group_id, $yesterday, $yesterday );
$ch = curl_init();
foreach ( $accounts as $account ) {
$url = "https://graph.facebook.com/v3.1/$account/insights?" .
"level=ad&time_range[since]=".$yesterday_no_time."&time_range[until]=".$yesterday_no_time."&fields=ad_id,ad_name,adset_id,adset_name,campaign_id," .
"campaign_name,impressions,outbound_clicks,spend,unique_actions&limit=5000&status=ACTIVE&" .
"access_token=EAACsZAOuIZAVMBAEJ37BHI8XezLfeX3fXQfatCSODBOAquV8j3SHD7qNRXz29fNnvF2bCu5m0agpbshRswGEIz6bIUZBsAfhgl3BVNPvCPwvyeQvuluy3XJ8M4KLxSTkzrmfRwjA95Ri29ynuKi5fmFyngq4JiAZBbfir0AjXAZDZD";
curl_setopt( $ch, CURLOPT_URL, $url );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
$insights = json_decode( curl_exec( $ch ),True );
$url = "https://graph.facebook.com/v3.1/$account/ads?fields=ad_id,adcreatives{object_story_spec}&status=ACTIVE&limit=2000&time_range[since]=".$yesterday_no_time."&time_range[until]=".$yesterday_no_time."&" .
"access_token=EAACsZAOuIZAVMBAEJ37BHI8XezLfeX3fXQfatCSODBOAquV8j3SHD7qNRXz29fNnvF2bCu5m0agpbshRswGEIz6bIUZBsAfhgl3BVNPvCPwvyeQvuluy3XJ8M4KLxSTkzrmfRwjA95Ri29ynuKi5fmFyngq4JiAZBbfir0AjXAZDZD";
curl_setopt( $ch, CURLOPT_URL, $url );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
$creatives = json_decode( curl_exec( $ch ), True );
$creative_links = [];
$creative_links[ 0 ] = [];
$key = 0;
foreach( $creatives[ "data" ] as $creativesAd ) {
if( isset( $creativesAd[ 'adcreatives' ][ 'data' ][ 0 ][ 'object_story_spec' ] ) ) {
$creative_links[ $key ][ 'id' ] = $creativesAd[ 'id' ];
$objectStorySpec = $creativesAd[ 'adcreatives' ][ 'data' ][ 0 ][ 'object_story_spec' ];
$final_url = "";
if ( isset( $objectStorySpec[ 'link_data' ] ) ) {
$final_url = $objectStorySpec[ 'link_data' ][ 'link' ];
} else if ( isset( $objectStorySpec[ 'video_data' ] ) ) {
$final_url = $objectStorySpec[ 'video_data' ][ 'call_to_action' ][ 'value' ][ 'link' ];
}
$creative_links[ $key ][ 'link' ] = $final_url;
$key++;
}
}
while( isset( $creatives[ 'paging']['next' ] ) ) {
$next_link = $creatives[ 'paging']['next' ];
curl_setopt( $ch, CURLOPT_URL, $next_link );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
$creatives = json_decode( curl_exec( $ch ), True );
foreach( $creatives[ "data" ] as $creativesAd ) {
if( isset( $creativesAd[ 'adcreatives' ][ 'data' ][ 0 ][ 'object_story_spec' ] ) ) {
$creative_links[ $key ][ 'id' ] = $creativesAd[ 'id' ];
$objectStorySpec = $creativesAd[ 'adcreatives' ][ 'data' ][ 0 ][ 'object_story_spec' ];
$final_url = "";
if ( isset( $objectStorySpec[ 'link_data' ] ) ) {
$final_url = $objectStorySpec[ 'link_data' ][ 'link' ];
} else if ( isset( $objectStorySpec[ 'video_data' ] ) ) {
$final_url = $objectStorySpec[ 'video_data' ][ 'call_to_action' ][ 'value' ][ 'link' ];
}
$creative_links[ $key ][ 'link' ] = $final_url;
$key++;
}
}
}
foreach ( $insights as $insightsData ) {
foreach ( $insightsData as $ad ) {
if ( isset( $ad[ 'campaign_name' ] ) ) {
$campaign_name = $ad[ 'campaign_name' ];
$group_name = $ad[ 'adset_name' ];
$ad_name = $ad[ 'ad_name' ];
$source_campaign_id = $ad[ 'campaign_id' ];
$source_group_id = $ad[ 'adset_id' ];
$source_ad_id = $ad[ 'ad_id' ];
$impressions = $ad[ 'impressions' ];
$clicks = 0;
if ( isset( $ad[ 'outbound_clicks' ] ) ) {
$clicks = $ad[ 'outbound_clicks' ][ 0 ][ 'value' ];
}
$leads = 0;
if ( isset( $ad[ 'unique_actions' ] ) ) {
foreach( $ad[ 'unique_actions' ] as $unique_action ) {
if( isset( $unique_action[ 'action_type' ] ) && $unique_action[ 'action_type' ] == "offsite_conversion.fb_pixel_lead" ) {
$leads = $unique_action[ 'value' ];
}
}
}
$spend = $ad[ 'spend' ];
foreach ( $creative_links as $creative_link ) {
if ( isset( $creativesAd[ 'id' ] ) ) {
if( $source_ad_id == $creative_link[ 'id' ] ) {
$link = $creative_link[ 'link' ];
}
}
}
$group_id = 0;
$parts = parse_url($link);
if ( isset( $parts['query'] ) ) {
parse_str($parts['query'], $query);
if ( isset( $query[ 'tc_gid' ] ) ) {
$group_id = (int) $query[ 'tc_gid' ];
} else if ( isset( $query[ 'tcagid' ] ) ) {
$group_id = (int) $query[ 'tcagid' ];
} else if ( isset( $query[ 'tcdgid' ] ) ) {
$group_id = (int) $query[ 'tcdgid' ];
}
}
$select->execute();
$result = $select->get_result();
if ( $result->num_rows > 0 ) {
$row = $result->fetch_assoc();
$website_id = $row[ 'website_id' ];
$website_type_id = $row[ 'website_type_id' ];
$user_id = $row[ 'user_id' ];
$campaign_id = $row[ 'campaign_id' ];
} else {
$website_id = 0;
$website_type_id = 0;
$user_id = 0;
$campaign_id = 0;
}
$insert->execute();
}
}
}
sleep( 20 );
}
//}
curl_close( $ch );
$select->close();
$insert->close();
$conn->close();
?>