phpcsvgoogle-ads-apiadwords-budgetservice

Convert micros to currency in AWQL csv report with Adwords API


I have this query in AWQL and I get the response in CSV format using the ReportUtils::DownloadReportWithAwql

select Date, Clicks, Cost from ACCOUNT_PERFORMANCE_REPORT during LAST_30_DAYS

I need to convert the Cost data in the CSV from micros to the currency in the account (Cost / 1000000).

Also I need to be able to convert any the Cost data in the response using any AWQL query, for example the solution has to work for this query also:

SELECT CampaignName, KeywordText, Cost, CostPerConversion, QualityScore FROM KEYWORDS_PERFORMANCE_REPORT DURING LAST_7_DAYS

As of v201406, the returnMoneyInMicros header is no longer valid and values are always returned as micros. https://developers.google.com/adwords/api/docs/guides/reporting-concepts#money

This is my first question in stackoverflow.


Solution

  • Finally I did it and works great for me.

        //data is a string with data in micros in csv format
        $data = $this->DownloadCriteriaReportWithAwql($awql);
    
        //start micros conversion
        $count = 0;
        $costpos = array();
        $newarray = array();
        foreach(preg_split("/((\r?\n)|(\r\n?))/", $data) as $line){
    
            $linearray = str_getcsv($line);
    
            if($count == 0) {
                //adwords report title
                $newarray[] = $linearray;
                $count++;
                continue;
            }
    
            if($count == 1) {
                //columns report header
                $postvalue = 0;
    
                foreach($linearray as $value){
                    if (strpos($value,'Cost') !== false) {
                        $costpos[] = $postvalue;
                    }
                    $postvalue++;
                }
    
                $newarray[] = $linearray;
                $count++;
                continue;
            }
    
            if(!empty($costpos)) {
    
                foreach($costpos as $costpostval){
    
                    if(isset($linearray[$costpostval])) {
                        $linearray[$costpostval] = $linearray[$costpostval] / 1000000;
                    }
    
                }
            }
    
            $newarray[] = $linearray;
            $count++;
        }
    
    
        $tmpfname = tempnam(sys_get_temp_dir(), "FOO");
        $outstream = fopen($tmpfname, "r+");
        foreach($newarray as $newline){
            fputcsv($outstream, $newline);
        }
        fclose($outstream);
        //end micros conversion - $tmpfname temp file with cost in currency csv formated