How to Group stats on the basisi of week days

[Sachin Kumar]

Hello Team,


I have to show conversion rate, current bid etc per weekday(Monday to Sunday) basis i.e one row per each day. Currently we are using 'for' loop(form 1 to 7 ) in order to get the total data per weekday basis from criteriaPerformanceReport. As I am using loops it's taking lot of time for the page to load, so I just want to implement it in otherway. Is there any way to get the data using GroupBy clause on weekday field or anything similar to this so that I can bypass the loop and page loading time will be decreased.

Here is my code:

Loop:
          for($i=1;$i<=7;$i++){
                $result= $adwordsObj->getstatsDaywise($adwordsUser, $filePath,$currMonStartDate, $currMonEndDate,$clientId,$i,'',$cId);
                               
                $data[] = $result;
            }

=======================================================================================================
Called Function:

public function getstatsDaywise(AdWordsUser $user, $filePath = null,$startDate = null, $endDate=null,$clientId = null,$day=null,$formData=null,$cid=null) {
       
            $WeekDays = array(1=>'MONDAY',2=>'TUESDAY',3=>'WEDNESDAY',4=>'THURSDAY',5=>'FRIDAY',6=>'SATURDAY',7=>'SUNDAY');
           
            if(isset($day) && !empty($day)){
                //$day_number = $day;
                //echo 'I am in if';
            }else{
                $date = date('Y-m-d');
                $day = date('N', strtotime($date));
            }
            //echo $day;die();
            $performanceReportType = !is_null($reportType) ? $reportType : 'CRITERIA_PERFORMANCE_REPORT';
            //echo base64_decode('OTI5NTM1MzYyMA==');
            $user->SetClientCustomerId($clientId);
           
            //echo $performanceReportType;
            //echo $clientId.'---'.$performanceReportType;die();          
       
            // Load the service, so that the required classes are available.
            $user->LoadService('ReportDefinitionService', ADWORDS_VERSION);
            // Optional: Set clientCustomerId to get reports of your child accounts
           
            // Create selector.
            $selector = new Selector();
          
            $selector->fields = array('CampaignName','CampaignId', 'AdGroupId','AdGroupName','Id', 'Criteria','CriteriaType','AverageCpm','Ctr','AverageCpc','ClickConversionRate','ConvertedClicks','ConversionValue','Impressions','Clicks','Cost');
            // Optional: use predicate to filter out paused criteria.
            $selector->predicates[] = new Predicate('CampaignStatus', 'NOT_IN', array('PAUSED'));
           
            if($reportType != 'FINAL_URL_REPORT'){
                $selector->predicates[] = new Predicate('Status', 'NOT_IN', array('PAUSED'));
            }
           
            $selector->predicates[] = new Predicate('DayOfWeek', 'EQUALS',constant($WeekDays[$day]));
            //$selector->predicates[] = new Predicate('DayOfWeek', 'IN',array('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY'));
           
            $campaignId = array();
            if(isset($formData['campaign']) && $formData['campaign'] !='all'){
                $campaignId[] = $formData['campaign'];
                //$selector->predicates[] = new Predicate('CampaignId', 'IN', array($formData['campaign']));
            }
            if(isset($cid) && $cid != ''){
                $campaignId[] = $cid;
            }
            if(isset($campaignId) && !empty($campaignId)){
                $selector->predicates[] = new Predicate('CampaignId', 'IN', $campaignId);
            }
                           
            //$this->pr($selector->predicates);
            //die();
                   
            $selector->dateRange = new DateRange($startDate, $endDate);
           
            // Year+Month+Day
            // Create report definition.
            $reportDefinition = new ReportDefinition();
            $reportDefinition->selector = $selector;
            $reportDefinition->reportName = 'DayReports#' . uniqid();
            //$reportDefinition->dateRangeType = 'LAST_7_DAYS';
           
            $reportDefinition->dateRangeType = 'CUSTOM_DATE';
           
            $reportDefinition->reportType = $performanceReportType;
            $reportDefinition->downloadFormat = 'CSV';
            // Set additional options.
            $options = array('version' => ADWORDS_VERSION);
            //print_r($reportDefinition);
            //print_r($options);
            //die();
            ReportUtils::DownloadReport($reportDefinition, $filePath, $user, $options);
            //printf("Report with name '%s' was downloaded to '%s'.\n", $reportDefinition->reportName, $filePath);
                       
            if(file_exists($filePath)) {
                $result = $this->__parseAdwordsStats($filePath);
                return end($result['body']);
               
            } else {
                return false;
            }       
    }


Suggest a way to achieve the same.

Thanks & Regards
Sachin Kumar

[Vishal Vinayak (Adwords API Team)]

Hi Sachin,

Criteria Performance Report is aggregated at the ad group criteria level, one row per ad group and criteria combination i.e. it is not aggregated at the Date/Day of the week level. DayOfWeek field in the report acts as a segment field, i.e. adding DayOfWeek to the report segments the report at the Day level i.e. Monday, Tuesday etc. 

Any aggregations that are needed by you would have to be done manually. However, I can suggest using AWQL to extract only the required data from the report (instead of the entire report) and aggregating it manually. Please note that AWQL does not support JOIN or GROUP BY clauses.

Regards,

Vishal, AdWords API Team