CODEWITHSUNDEEP
google-analyticsgoogle-visualizationgoogle-analytics-api
Pohsible
Pohsible

Reputation: 21

Format data in Google Analytics API response and visualize with Charts API

I am building a reports page for my clients to be able to view their data in a format they are already familiar with. Up to this point they receive a .pdf with select metrics and explanations. I want to automate this using Google Analytics Embed API. I am having trouble formatting data before drawing the charts. Specifically I want to display Avg Session Duration in a 00:00:00 time format rather than a decimal. Also, I would like to truncate Pages / Session down to two decimal places.

Here is the code for the chart I am drawing: 

 /**
         * Create a table chart showing top Keywords by sessions with average session duration and pages / session
        */

        var keywordChart = new gapi.analytics.googleCharts.DataChart({
          query: {
            'dimensions': 'ga:keyword',
            'metrics': 'ga:sessions,ga:avgSessionDuration,ga:pageviewsPerSession',
            'sort': '-ga:sessions',
            'start-date': '30daysAgo',
            'end-date': 'yesterday',
            'max-results': '10'
          },
          chart: {
            type: 'TABLE',
            container: 'keyword-chart-container',
            options: {
              width: '100%'
            }
          }
        });

        keywordChart.on('success', function(response) {
          console.log(response.data.cols[2]);
        });

I appreciate any help or insight, thank you for your time!

EDIT

I have changed my code to use the google analytics API along with the Charts API, however I am still having issues getting the Avg Session Duration to format using a date pattern. Here is my updated code:

var keywordChart = new gapi.analytics.report.Data({
  query: {
    'dimensions': 'ga:keyword',
    'metrics': 'ga:sessions,ga:avgSessionDuration,ga:pageviewsPerSession',
    'sort': '-ga:sessions',
    'start-date': '30daysAgo',
    'end-date': 'yesterday',
    'max-results': '10',
    'output': 'dataTable'
  }
});

keywordChart.on('success', function(response) {
  console.log(response.dataTable);
  var data = new google.visualization.DataTable(response.dataTable);
  var timeFormatter = new google.visualization.DateFormat({pattern: 'H:mm:ss'});
  var numFormatter = new google.visualization.NumberFormat({fractionDigits: 2});

  timeFormatter.format(data, 2);
  numFormatter.format(data, 3);

  var keywordsTable = new google.visualization.Table(document.getElementById('keyword-chart-container'));
  keywordsTable.draw(data);

});

I need to divide the data in data[2] by (24*60*60) before applying the pattern as well.

data[2]/(24*60*60) to then be put into the pattern 'H:mm:ss' before my table is drawn.

I am having trouble finding in the Charts documentation how to accomplish this.

Again any insight is much appreciated, thank you for your time!

Upvotes: 2

Views: 1965

Answers (1)

WhiteHat
WhiteHat

Reputation: 61230

you can use a DataView to replace data[2] with a calculated column

assuming data[2] is a time value and will result in a valid date
new Date(data[2])

see following snippet... 

keywordChart.on('success', function(response) {
  var data = new google.visualization.DataTable(response.dataTable);

  var timeFormatter = new google.visualization.DateFormat({pattern: 'H:mm:ss'});

  // create view
  var view = new google.visualization.DataView(data);

  // include all columns from data
  var columns = [];
  for (var i = 0; i < data.getNumberOfColumns(); i++) {
    columns.push(i);
  }

  // replace date column with calculation
  columns[2] = {
    calc: function (dt, r) {
      return {
        // value
        v: new Date(dt.getValue(r, 2)),
        // formatted value
        f: timeFormatter.formatValue(new Date(dt.getValue(r, 2)))};
    },
    type: 'date',
    label: data.getColumnLabel(2)
  };
  view.setColumns(columns);

  var keywordsTable = new google.visualization.Table(document.getElementById('keyword-chart-container'));
  // draw chart with view
  keywordsTable.draw(view);
});

obviously, you could do the same with the number column using numFormatter

Upvotes: 2

Related Questions