In this article I am explaining a simple process to setup Google Bid Manager (DBM) API by PHP Artisan Command with laravel 5.4. You can get campaigns or your line items data either setting up a cron using created command or running command on terminal.
First, edit composer.json in the project's root folder to include the Google Client:
{ "require": {
"google/apiclient": "^2.0" }
}
Next run composer update at shell prompt to pull in the sdk to the vendor folder.
php composer.phar install --no-dev
Now we would like to use the Google DBM SDK within our app as a command. But before doing that, let us setup our client_id, client_secret and access_token which are parameters required while making requests to Google DBM API. You can obtained client_id and client_secret from your
Google App Console .
Once we have these credentials from Facebook, we would now edit .env file in the project's root folder. Add them to the end:
DBM_CLIENT_ID=8990-ru2amnc17f5jchhgsepp0uro96smu16b.apps.googleusercontent.com
DBM_CLIENT_SECRET=XXXXXXXXXXXXX
DBM_REFRESH_TOKEN=1/VeTb6k8T291WCKWsPfnBrjOBxtwwCxO11Q6FGH8DZJk
Replace the xxx.. with the values provided to you. Note that the variable names are just my own creation. You can name them whatever you'd like. We would now have to use these variables to setup a separate config file. We need to do this so that we can use Laravel's config() helper function to retrieve the values wherever we want within the app. So let's create google-dbm.php in the config folder and add the following:
return [
'client_id' => env('DBM_CLIENT_ID', null),
'client_secret' => env('DBM_CLIENT_SECRET', null),
];
Now we have to create a class GoogleDbmService.php with below command..
php artisan make:console GoogleDbmService
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
class GoogleDbmService extends Command
{
/**
* The name and signature of the console command.
*/
protected $signature = 'google-dbm:service {--date=today}';
/**
*The console command description.
* @var string
*/
protected $description = 'google ads api for campaign
service, adset etc'; /**
* Create a new command instance.
* @return void
*/
public function __construct(){
parent::__construct();
}
/**
* Execute the console command.
* @return mixed
*/
public function handle() {
//Your Code Goes Hare
}
}
You have to write your all code to get Google campaign & their reporting in the handle() function.
Now to run this command on our terminal we have to register it in Kernal.php with path "/app/Console/kernel.php".
So add "Command\GoogleDbmService::class" in the $commands array as below -
protected $commands = [
Commands\GoogleDbmService::class
];
Now we have all setup to get campaign or lineitems reporting from google dbm api.
Now just add some classes at the top of our GoogleDbmService class that we gonna use to get campaign data.
use Google_Client;
use Google_Service_DoubleClickBidManager;
use Google_Service_DoubleClickBidManager_DownloadLineItemsRequest;
use Google_Service_DoubleClickBidManager_DownloadRequest;
use Google_Service_DoubleClickBidManager_FilterPair;
use Google_Service_DoubleClickBidManager_Parameters;
use Google_Service_DoubleClickBidManager_Query;
use Google_Service_DoubleClickBidManager_QueryMetadata;
use Google_Service_DoubleClickBidManager_QuerySchedule;
use Google_Service_Exception;
Now write your code to get google dbm campaign data in its handle() function as below -
We have to get access token first to make an google bid manager api call.
To instantiate an Api object you will need a valid access token:
$client = new Google_Client();
$client->setAccessType('offline');
$client->setClientId(env('DBM_CLIENT_ID'));
$client->setClientSecret(env('DBM_CLIENT_SECRET'));
$client->addScope('https://www.googleapis.com/auth/doubleclickbidmanager');
$service = new Google_Service_DoubleClickBidManager($client);
$data = $client->refreshToken(env('DBM_REFRESH_TOKEN'));
$client->setAccessToken($data['access_token']);
Download Campaigns by AdvId -
you should pass adv_id to get all campaigns -
public function downloadCmp($service, $adv_ids){
// Setup any filtering on the API request
$dliRequest = new
Google_Service_DoubleClickBidManager_DownloadRequest();
$dliRequest->setFilterType("ADVERTISER_ID");
$dliRequest->setFileTypes(array("CAMPAIGN"));
$dliRequest->setFilterIds(array($adv_ids));
try {
$result = $service->sdf->download($dliRequest);
} catch (Google_Service_Exception $e) {
printf('<p>Exception: %s</p>', $e->getMessage());
print '<p>Consider filtering by ADVERTISER_ID</p>';
return;
} catch (Exception $e) {
printf('<p>Exception: %s</p>', $e->getMessage());
return;
}
$csv_content = $result['campaigns'];
$csvDelimiter = '';
$csvLines = str_getcsv($csv_content, "\n");
$linesToImport = [];
foreach($csvLines as $row)
{
$linesToImport[] = str_getcsv($row, $csvDelimiter);
}
return $linesToImport;
}
Create Query -
In DBM it is necessary to create query to get data of a lineitems. So here I here is the code for creating a query.
//Create Query
public function createQuery($service, $dateRange, $filters){
// Setup any filtering on the API request
$qryRequest_prm = new Google_Service_DoubleClickBidManager_Query();
$qryRequest = new Google_Service_DoubleClickBidManager_QueryMetadata();
$qryRequest->setDataRange($dateRange);
$qryRequest->setFormat('csv');
$qryRequest->setTitle('query:'.time());
$qryRequest->setRunning('true');
$a = [];
foreach($filters as $k=>$v){
$filterPairRequest = new Google_Service_DoubleClickBidManager_FilterPair();
$filterPairRequest->setType($k);
$filterPairRequest->setValue($filters[$k]);
$a[] = $filterPairRequest;
}
$groupBY = array(
//"FILTER_ADVERTISER",
//"FILTER_ORDER_ID",
"FILTER_MEDIA_PLAN",
"FILTER_LINE_ITEM",
//"FILTER_BUDGET_SEGMENT_DESCRIPTION",
"FILTER_DATE"
);
$prmRequest = new Google_Service_DoubleClickBidManager_Parameters();
//$prmRequest->setFilters(["FILTER_PARTNER"]);
$params = array(
"METRIC_CLICKS",
"METRIC_TOTAL_CONVERSIONS",
"METRIC_CONVERSIONS_PER_MILLE",
"METRIC_IMPRESSIONS",
"METRIC_CTR",
"METRIC_REVENUE_PARTNER",
//'METRIC_REVENUE_ADVERTISER',
);
$prmRequest->setMetrics($params);
$prmRequest->setType("TYPE_GENERAL");
$prmRequest->setFilters($a);
$prmRequest->setGroupBys($groupBY);
//$prmRequest->setGroupBys();
$schRequest = new Google_Service_DoubleClickBidManager_QuerySchedule();
$schRequest->setFrequency('ONE_TIME');
$qryRequest_prm->setKind('doubleclickbidmanager#query');
$qryRequest_prm->setMetadata($qryRequest);
$qryRequest_prm->setParams($prmRequest);
$qryRequest_prm->setQueryId(123456);
$qryRequest_prm->setSchedule($schRequest);
try{
$result = $service->queries->createquery($qryRequest_prm);
}catch (Exception $e){
print($e);
}
return $result->queryId;
}
Delete Query -
Always delete query id after you done the all operation related to that query id.
//Delete query after operations
public function deleteQuery($service, $queryId){
try{
$result = $service->queries->deletequery($queryId);
}catch (Exception $e){
print($e);
}
return $result;
}
Download All LineItems of an Advertiser -
if you want to download all line items of an advertiser you can do it with below function.
//Download Line Items
public function downloadLineItems($service, $adv_ids){
$dliRequest = new
Google_Service_DoubleClickBidManager_DownloadLineItemsRequest();
$dliRequest->setFilterType("ADVERTISER_ID");
$dliRequest->setFilterIds(array($adv_ids));
try {
$result = $service->lineitems->downloadlineitems($dliRequest);
//print_r($result);
} catch (Google_Service_Exception $e) {
printf('<p>Exception: %s</p>', $e->getMessage());
print '<p>Consider filtering by ADVERTISER_ID</p>';
return;
} catch (Exception $e) {
printf('<p>Exception: %s</p>', $e->getMessage());
return;
}
if (!isset($result['lineItems']) || count((array)$result['lineItems']) < 1) {
print '<p>No items found</p>';
return;
} else {
//print_r( $result->lineItems);
$csv_content = $result->lineItems;
$csvDelimiter = '';
$csvLines = str_getcsv($csv_content, "\n");
$linesToImport = [];
foreach($csvLines as $row)
{
$linesToImport[] = str_getcsv($row, $csvDelimiter);
}
print '<p>Download complete</p>';
}
return $linesToImport;
}
Get LineItems Insight -
You can change fields and parameters according to your need.
public function handle()
{
if ($this->option('date') == "today") {
$dateRange = 'CURRENT_DAY';
} elseif ($this->option('date') == "yesterday") {
$dateRange = 'PREVIOUS_DAY';
} elseif ($this->option('date') == "last_month") {
$dateRange = 'PREVIOUS_MONTH';
}elseif ($this->option('date') == "this_month") {
$dateRange = 'MONTH_TO_DATE';
}elseif ($this->option('date') == "last_year") {
$dateRange = 'PREVIOUS_YEAR';
}elseif ($this->option('date') == "this_year") {
$dateRange = 'YEAR_TO_DATE';
}elseif ($this->option('date') == "all_time") {
$dateRange = 'ALL_TIME';
} else {
$this->info('Please choose date param between today/yesterday/last_month/this_month/last_year/this_year');
return;
}
$prefix = "DBM_";
$partner_id = 20534561234;
$requestParam = [];
$requestParam['account_type'] = "DBM";
$account = Account::getAccountListByAccountType($requestParam);
if(count($account) <= 0)
{
$this->error('You have not any account please set up your account');
return;
}
$this->info("Total Ad Account:-" . count($account));
if($account)
{
foreach ($account as $key => $value)
{
if($value['access_token'] && $value['ext_account_id'])
{
$requestParam['user_id'] = $value['user_id'];
$requestParam['account_id'] = $value['account_id'];
$requestParam['account_name'] = $value['name'];
$requestParam['ext_account_id'] = $value['ext_account_id'];
$requestParam['adv_id'] = $value['adv_id'];
$requestParam['manager_id'] = $value['manager_id'];
$this->info("Account:-".$value['ext_account_id']);
$client = new Google_Client();
$client->setAccessType('offline');
$client->setClientId(env('DBM_CLIENT_ID'));
$client->setClientSecret(env('DBM_CLIENT_SECRET'));
$client->addScope('https://www.googleapis.com/auth/doubleclickbidmanager');
$service = new Google_Service_DoubleClickBidManager($client);
$data = $client->refreshToken($value['access_token']);
$client->setAccessToken($data['access_token']);
try{
$cmpByAdvId = $this->downloadCmp($service, $requestParam['ext_account_id']);
$this->info('Campaign count:-' . (count($cmpByAdvId)-1));
if(count($cmpByAdvId) > 0) {
foreach ($cmpByAdvId as $key_id => $cmp_value) {
if ($key_id == 0) {
continue;
}
else{
$filters = array(
'FILTER_PARTNER'=>$partner_id,
'FILTER_ADVERTISER'=>$requestParam['ext_account_id'],
'FILTER_MEDIA_PLAN'=>$cmp_value[0],
);
$queryId = $this->createQuery($service, $dateRange, $filters);
if ($queryId != '' || $queryId != 0) {
sleep(60); #coz csv takes time to write on google cloud
// Call the API, getting a list of queries.
$result = $service->queries->getquery($queryId);
$csv_content = file_get_contents($result->getMetadata()->getGoogleCloudStoragePathForLatestReport());
if (isset($csv_content)) {
$csvDelimiter = '';
$csvLines = str_getcsv($csv_content, "\n");
$linesToImport = [];
foreach ($csvLines as $row) {
$linesToImport[] = str_getcsv($row, $csvDelimiter);
}
$this->info('Campaign Id:-'.$cmp_value[0]);
$i = 0;
if (count($linesToImport) > 0) {
foreach ($linesToImport as $key => $value) {
if ($key == 0) {
continue;
}
else if($key > 0 && count($linesToImport[$key])<5){
continue;
}
else{
if(!empty($value[10])){
if(!empty($cmp_value[10])){
$sdate = strtotime($cmp_value[10]);
$cmp_sdate = date('Y-m-d h:i:s', $sdate);
}else{
$cmp_sdate='';
}
if(!empty($cmp_value[11])){
$edate = strtotime($cmp_value[11]);
$cmp_edate = date('Y-m-d h:i:s', $edate);
}else{
$cmp_edate='';
}
$requestParam['cmp_id'] = $cmp_value[0];
$requestParam['flight_id'] = $value[3]; //adsGroup id
$requestParam['conversions'] = $value[10];
$requestParam['impressions'] = $value[12];
$requestParam['clicks'] = $value[9];
$requestParam['ctr'] = $value[13];
$requestParam['date'] = $value[7];
$requestParam['wkno'] = (int)date("W", strtotime($value[7]));
$requestParam['rev_model'] = NULL;
$requestParam['cmp_name'] = $prefix . $cmp_value[2];
$requestParam['cmp_status'] = $value[2];
$requestParam['campaign_name'] = $cmp_value[2];
$requestParam['cmp_type'] = NULL;
$requestParam['flight_name'] = $prefix . $value[2];
$requestParam['start_date'] = $cmp_sdate;
$requestParam['end_date'] = $cmp_edate;
$requestParam['budget'] = $cmp_value[9];
$requestParam['payout'] = $value[14];
$requestParam['hour'] = 0;
$requestParam['ms_id'] = '178';//DBM media source id
$saveCmpRptData = DbmOverviewTableForTest::saveYourCmpRptData($requestParam);
if ($saveCmpRptData) {
if (isset($saveCmpRptData['status']) && $saveCmpRptData['status'] == 9) {
DbmOverviewTableForTest::updateYourCmpRptData($requestParam);
}
} else {
$this->info('database not connected and something went wrong');
return;
}
$i++;
}
}
}
$this->info('Count Report:-' . $i);
$this->info('*************************');
$this->deleteQuery($service, $queryId);
}
}
else{
$this->info('@Error:csv_content variable is not found');
$this->deleteQuery($service, $queryId);
}
}
}
}
}
}catch (\Execption $e){
print($e)
return;
}
}
}
}
}
Run your command on terminal -
php artisan google-ads:service
You can see your output on the terminal.
If you found any difficulty in setting up Google DBM API with laravel, you can intimate me by comment.
