Create access ranking with Google Analytics.

create_access_ranking_with_ga.js

const google = require('googleapis');
const analytics = google.analyticsreporting('v4');
const mysql = require('mysql');
require('date-utils');

exports.handler = (event, context, callback) => {

    // MySQL Setting
    var rdsConnection = mysql.createConnection({
        host: '<DB Host>',
        user: '<admin>',
        password: '<ps>',
        database: '<db>',
    });
    
    // GA Setting
    var credential = require('./<SERVICE_ACCOUNT_KEY_JSON>');
    var viewId = '<GoogleAnalyticsViewID>';
    
    // 今日から一週間前までのデータでランキングを作る
    var date = new Date();
    var endDate = date.toFormat('YYYY-MM-DD');
    date.setDate(date.getDate() - 7);
    var startDate = date.toFormat('YYYY-MM-DD');
    const rankingLimit = 10;
  
    var jwtClient = new google.auth.JWT(credential.client_email, null, credential.private_key, ["https://www.googleapis.com/auth/analytics.readonly"], null);
    
    jwtClient.authorize((error, tokens) => {
        if (error){
            console.log(error);
            return;
        }
        analytics.reports.batchGet({
            resource: {
                "reportRequests": [
                    {
                        "dimensions": [
                            {
                                "name": "ga:pagePath"
                            }
                        ],
                        "dimensionFilterClauses": [
                            {
                                "filters": {
                                    "dimensionName": "ga:pagePath",
                                    "operator": "PARTIAL",
                                    "expressions": [
                                        "articles"
                                    ],
                                }
                            }
                        ],
                        "metrics": [
                            {
                                "expression": "ga:pageviews"
                            }
                        ],
                        "dateRanges": [
                            {
                                "startDate": startDate,
                                "endDate": endDate
                            }
                        ],
                        "viewId": viewId,
                        "orderBys": [
                            {
                                "fieldName": "ga:pageviews",
                                "sortOrder": "DESCENDING"
                            }
                        ],
                        "pageSize": rankingLimit
                    }
                ]
            },
            auth: jwtClient
        }, (error, response) => {
            if (error){
                console.log(error);
            }
            const items = response.reports[0].data.rows.map((row, index) => {
                return [
                    index+1,
                    Number(row.dimensions[0].split('/')[2]),
                ];
            });
            console.log(items);
            rdsConnection.query('INSERT INTO weekly_ranking (rank, article_id) VALUES ? ON DUPLICATE KEY UPDATE article_id = VALUES(article_id)', [items], function(error, results, fields) {
                if (error) {
                  console.error(error);
                }
                else {
                  console.log(results);
                }
                rdsConnection.end();
            });
        })
    });
    callback(null, 'Done!');
};