Return Google Analytics Reporting API result as XML/JSON

Question

Not a PHP developer, but need to get a report from the Analytics Reporting API V4 using PHP. I'd appreciate it if anyone can help me to convert the result in xml or json format. I am having trouble to insert the result, retrieved using the function printResults($reports) from the sample code, into a database. Thank you!

Weihui Guo · Answer 1 · 2018-03-11T02:01:08.470

Above is the result of print_r($response); Based on the object, I use the following function to print the response in XML format:

function printResultsAsXml($response) {
    $array = $response['reports'];
    $arrayHeaders = ($array[0]['columnHeader']['dimensions']);

    $arrayRows = $array[0]['data']['rows'];
    $mdarray = array();

    for ( $rowIndex = 0; $rowIndex < count($arrayRows); $rowIndex++) { 
        $mdarray[] = array_combine($arrayHeaders, $arrayRows[$rowIndex]['dimensions']);
    }

    // print_r(xmlutils::array_to_xml($mdarray));
    $output = xmlutils::array_to_xml($mdarray);

    header('content-type: text/xml');

    $doc = new DOMDocument();
    $doc->loadXML($output, LIBXML_NOXMLDECL);
    echo $doc->saveXML();
}

And below is the result in XML. I am new to PHP, so if you happen to know a better answer, please let me know.

<data>
    <data-item>
        <gadimension9>1000342194.1520628512</gadimension9>
        <gacampaign>(not set)</gacampaign>
        <gasource>google</gasource>
        <gamedium>organic</gamedium>
        <gakeyword>(not provided)</gakeyword>
        <gaadContent>(not set)</gaadContent>
    </data-item>
    <data-item>
        <gadimension9>1000401494.1520637016</gadimension9>
        <gacampaign>(not set)</gacampaign>
        <gasource>google</gasource>
        <gamedium>organic</gamedium>
        <gakeyword>(not provided)</gakeyword>
        <gaadContent>(not set)</gaadContent>
    </data-item>
</data>

xmlutils.php is written by Jeremy Pyne for converting array to xml.

If the code throwing an error like this:

DOMDocument::loadXML(): xmlParseEntityRef: no name in Entity.

It's most likely due to the '&' character and should be replaced. I added a line to xmlutils.php, based on the answer to this question.

$value=preg_replace('/&(?!#?[a-z0-9]+;)/', '&amp;', $value);

Weihui Guo · Accepted Answer · 2018-09-21T18:53:30.913

The answer for xml is getting real long so I'll put the json part separately. Json handles special character such as '&' better. If you using SQL server 2016 or later, then using OPENJSON is much easier. However, we have a remote server with SQL server 2014 so I have to use xml.

function printResultsAsJson($reports) {
    $darray = array();
    $marray = array();
    $mkey = array();

    for ($reportIndex = 0; $reportIndex < count($reports); $reportIndex++) {
        $report = $reports[$reportIndex];
        $header = $report->getColumnHeader();
        $dimensionHeaders = $header->getDimensions();
        $metricHeaders = $header->getMetricHeader()->getMetricHeaderEntries();
        $rows = $report->getData()->getRows();

        for ($j = 0; $j < count($metricHeaders); $j++) {
            $entry = $metricHeaders[$j];
            $mkey[] = $entry->getName();
        }

        for ($rowIndex = 0; $rowIndex < count($rows); $rowIndex++) {
            $row = $rows[$rowIndex];
            $dimensions = $row->getDimensions();
            $metrics = $row->getMetrics();

            $darray[] = array_combine($dimensionHeaders, $dimensions);

            for ($j = 0; $j < count($metrics); $j++) {
                $values = $metrics[$j]->getValues();
                $marray[] = array_combine($mkey, $values);
            }
        }

    }

    $i = 0;
    $mdarray = array();
    foreach($darray as $value) {
        $mdarray[] = array_merge($value, $marray[$i]);
        $i++;
    }

    print_r(json_encode($mdarray));
}

Update: previous answer gets the dimensions only, this one is able to get metrics and merge 2 multidimensional arrays into one - PHP: Merge 2 Multidimensional Arrays.

score 0 · Answer 3 · answered Mar 22 '19 at 01:41

Not sure why but I was getting some funny errors with @kakugiki's function up top -- arrays not being equal size, something else too.

If anybody else is having funny results with that one, I rewrote to this, which seems to be pretty solid for my purposes.

 public function result($reports)
{
    $rows=[];

    foreach($reports as $report) {
        $header = $report->getColumnHeader();
        $dimensionHeaders = $header->getDimensions();
        $metricHeaderEntries = $header->getMetricHeader()->getMetricHeaderEntries();
        $rows = array_merge($rows, $report->getData()->getRows());
    }

    foreach($metricHeaderEntries as $metricHeaderEntry) {
        $metricHeaders[] = $metricHeaderEntry->getName();
    }

    $headers = array_merge($dimensionHeaders, $metricHeaders);

    foreach($rows as $row) {
        $metrics = [];
        $dimensions = $row->getDimensions();
        $metricEntries = $row->getMetrics();
        foreach($metricEntries as $metricEntry) {
            $metrics = array_merge($metrics, $metricEntry->getValues());
        }

        $results[] = array_combine($headers, array_merge($dimensions, $metrics));
    }

    return $results;

}

Return Google Analytics Reporting API result as XML/JSON

3 Answers3