下面是我使用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();
?>
