Author: Axonn Echysttas

Thank you, Stack Overflow (& the other sister websites) for helping me many times in the past. But my experience in the past years has become worse and worse. Now-a-days, I have my stomach almost in knots when I post a question there. The attitude of some people there is quite… negative (to put it very politely).

Thank you, Stack Overflow, for accruing a vast body of problem-solving knowledge. Tools such as chatGPT stand upon the shoulders of giants.

Now that that’s out of the way…

I had a simple technical question that pretty much anybody that knows a bit about the Google Analytics APIs could answer.

What I got on Stack Overflow after about 6 hours of waiting was a -1 vote with no single explanation. There was a comment, which was hidden for no reason at all (other than the question having a -1 perhaps? who knows what the UX people of Stack Overflow are thinking?). The comment was ok, but not that useful (it was mostly in the area of “why do it like that?”, which is something that I have come to fear when asking anything on SO).

And then I remembered that I have an alternative.

It took chatGPT oh, about 5 seconds to answer my question (and I got spared of the attitude in the process). And then I had the following lovely conversation with it, even checking its knowledge of Haxe:

 

Axonn Echysttas

Does Google Analytics have a query language?
I’m working on a back-end script that takes some data from my Google Analytics account and sends it to another application. The script is controlled by a web client. I don’t want to add too many UI elements in the web client (I don’t want to build another Google Analytics Query Explorer like the one at https://ga-dev-tools.google/query-explorer/ ). I just want to offer 1 textbox and the user should write their query there.

On the back-end, I can query Google Analytics using their own libraries, which take in various parameters such as start date, end date, metrics, etc.. But each of these require some separate coding & validation client-side. Would be nice if there would be some sort of syntax for querying analytics data.

Chat GPT

Yes, Google Analytics has a query language called the Core Reporting API Query Language. It is a syntax that allows you to query Google Analytics data programmatically using HTTP requests. This language is based on the Google Analytics Data Model, and it allows you to specify dimensions, metrics, filters, and other parameters in a single HTTP request.

The Core Reporting API Query Language is similar to SQL and allows you to write complex queries to retrieve data from your Google Analytics account. You can use it to retrieve data for a single view or for multiple views, and you can specify the data range, sampling level, and sorting options.

To use the Core Reporting API Query Language, you will need to make HTTP requests to the Google Analytics API endpoint and include the query parameters in the request URL. You can then parse the response data, which is returned in JSON format, and use it in your script.

Google provides a Query Explorer tool for testing and learning the query language, which you can access at https://ga-dev-tools.google/query-explorer/. This tool provides a user-friendly interface for building and testing queries, and it can also generate the corresponding HTTP requests for you.

You can find more information about the Core Reporting API Query Language in the Google Analytics Developer documentation at https://developers.google.com/analytics/devguides/reporting/core/v3/reference.

Axonn Echysttas

Can you please give me an example of a query written using Core Reporting API Query Language?

Chat GPT

Sure, here’s an example of a query that retrieves the number of sessions, pageviews, and bounce rate for the top 10 landing pages for a specific date range:

lua
Copy code
https://www.googleapis.com/analytics/v3/data/ga?
ids=ga:123456789&
start-date=2022-01-01&
end-date=2022-01-31&
metrics=ga:sessions,ga:pageviews,ga:bounceRate&
dimensions=ga:landingPagePath&
sort=-ga:pageviews&
max-results=10
In this example, the query is sent to the Google Analytics API endpoint, which is https://www.googleapis.com/analytics/v3/data/ga. The ids parameter specifies the view ID for the Google Analytics account that you want to query.

The start-date and end-date parameters specify the date range for the query, which in this example is from January 1, 2022 to January 31, 2022.

The metrics parameter specifies the metrics that you want to retrieve, which in this example are sessions (ga:sessions), pageviews (ga:pageviews), and bounce rate (ga:bounceRate).

The dimensions parameter specifies the dimensions that you want to group the data by, which in this example is the landing page path (ga:landingPagePath).

The sort parameter specifies the sort order for the query results, which in this example is by descending pageviews (-ga:pageviews).

The max-results parameter specifies the maximum number of results that you want to retrieve, which in this example is 10.

When you send this query to the Google Analytics API endpoint, it will return a JSON response that includes the requested metrics and dimensions for the specified date range. You can then parse the JSON response and use the data in your script.

Axonn Echysttas

Can you please offer the above example in JavaScript?

Chat GPT

Sure, here’s an example of how you can send the Core Reporting API Query Language query in JavaScript using the Fetch API:

javascript
Copy code
const VIEW_ID = ‘ga:123456789’; // Replace with your Google Analytics view ID
const API_KEY = ‘YOUR_API_KEY’; // Replace with your Google API key
const API_URL = ‘https://www.googleapis.com/analytics/v3/data/ga’;

const start_date = ‘2022-01-01’;
const end_date = ‘2022-01-31’;
const metrics = ‘ga:sessions,ga:pageviews,ga:bounceRate’;
const dimensions = ‘ga:landingPagePath’;
const sort = ‘-ga:pageviews’;
const max_results = ’10’;

const query_params = new URLSearchParams({
‘ids’: VIEW_ID,
‘start-date’: start_date,
‘end-date’: end_date,
‘metrics’: metrics,
‘dimensions’: dimensions,
‘sort’: sort,
‘max-results’: max_results,
‘key’: API_KEY
});

const query_url = `${API_URL}?${query_params.toString()}`;

fetch(query_url)
.then(response => response.json())
.then(data => {
// Process the query data
console.log(data);
})
.catch(error => {
// Handle the query error
console.error(error);
});
In this example, we define constants for the Google Analytics view ID (VIEW_ID), the Google API key (API_KEY), and the Google Analytics API endpoint URL (API_URL).

We then define variables for the query parameters, including the start date, end date, metrics, dimensions, sort, and max results.

We create a new URLSearchParams object to encode the query parameters as a URL query string, and then append the Google API key to the query string.

We then construct the query URL by concatenating the Google Analytics API endpoint URL and the encoded query parameters.

We send the query to the Google Analytics API endpoint using the Fetch API, and then process the query data in the then callback function. If an error occurs, we handle it in the catch callback function.

Axonn Echysttas

Can you translate the above example from JavaScript to Haxe? Haxe also has a class that is used to fetch web requests.

Chat GPT

Sure! Here is an example of how to send the same Core Reporting API Query Language query using Haxe and its haxe.Http class:

haxe
Copy code
import haxe.Http;
import sys.net.URL;

class Main {
static function main() {
var VIEW_ID = ‘ga:123456789’; // Replace with your Google Analytics view ID
var API_KEY = ‘YOUR_API_KEY’; // Replace with your Google API key
var API_URL = ‘https://www.googleapis.com/analytics/v3/data/ga’;

var start_date = ‘2022-01-01’;
var end_date = ‘2022-01-31’;
var metrics = ‘ga:sessions,ga:pageviews,ga:bounceRate’;
var dimensions = ‘ga:landingPagePath’;
var sort = ‘-ga:pageviews’;
var max_results = ’10’;

var query_params = new URLSearchParams({
‘ids’: VIEW_ID,
‘start-date’: start_date,
‘end-date’: end_date,
‘metrics’: metrics,
‘dimensions’: dimensions,
‘sort’: sort,
‘max-results’: max_results,
‘key’: API_KEY
});

var query_url = ‘${API_URL}?${query_params.toString()}’;

var http = new Http(query_url);
http.onData = function(data:String) {
// Process the query data
trace(data);
};
http.onError = function(error:String) {
// Handle the query error
trace(error);
};
http.request();
}
}
In this example, we first import the haxe.Http class for sending web requests and the sys.net.URL class for building the query URL.

We define constants for the Google Analytics view ID (VIEW_ID), the Google API key (API_KEY), and the Google Analytics API endpoint URL (API_URL).

We then define variables for the query parameters, including the start date, end date, metrics, dimensions, sort, and max results.

We create a new URLSearchParams object to encode the query parameters as a URL query string, and then append the Google API key to the query string.

We then construct the query URL by concatenating the Google Analytics API endpoint URL and the encoded query parameters.

We create a new Http object and set its onData and onError properties to handle the query response data and errors, respectively.

Finally, we call the request method of the Http object to send the query to the Google Analytics API endpoint.

---

In a nutshell, somebody at Stack Overflow could have told me that the Query Language does exist, except that it is NOT that similar to SQL (you got that a bit wrong, chatGPT). But queries CAN be handled as strings that can be parsed into JSON objects for example.

It would have taken just a few seconds for somebody to empathize, give me a short, quick tip and move on. Now, 10 hours later, the question is still unanswered on Stack Overflow and I wonder how come an early-generation AI can make me feel so good compared to the human element?

This is not to say that humans aren’t amazing. It’s just that they’re probably too busy with their own over-crowded lives. Stack Overflow certainly has excellent people, I just don’t have the luck of meeting them. I’m very grateful that for such situations I have chatGPT to rely on.

It only strengthens my belief that knowing how to use such tools is essential for… pretty much anybody. More on that, in the future.

Oh, and I can’t write about the positives of chatGPT without mentioning this one, horrible negative:

https://time.com/6247678/openai-chatgpt-kenya-workers