How to Display Your Google Analytics Data in dashdash

[hadyelhady]

Once you’ve successfully executed a Google Analytics function in dashdash, you will probably want to display the data in your spreadsheet. Unlike many functions, the Google Analytics functions don’t offer recommended tables that allow you to easily display your data in the form of a table with just a few clicks.

The reason for this is that Google Analytics is a very advanced integration, and there are countless things that you can do with it. That’s why a prebuilt table can’t be defined because the table and the output will change according to the different metrics and dimensions that you specify.

Nonetheless, displaying the retrieved Google Analytics data in your spreadsheet is still easy if you know the right functions to use. Below we will discuss a few ways to display your Google Analytics metrics.

Displaying Metrics Without Dimensions

If a request has no dimensions, the returned metrics provide aggregate values for the requested date range, such as overall pageviews or total bounces.

For example, by executing a function without a dimensions parameter. You will get a JSON object that looks like this.

=PAGE_ANALYTICS_GOOGLE(“123456789”,">2020")

By expanding the totalsForAllResults section, you will find all the retrieved values next to the corresponding metrics.

To parse any of the values, you can easily do so just by clicking on it to copy it to the clipboard. Then, paste it in a cell to automatically generate the necessary formula.

=PARSE(A1,"[‘totalsForAllResults’].[‘ga: pageviews’]”)

To display all of the metrics at once in table form, you can use INSERT_DATA, OVERWRITE_DATA, or UPDATE_DATA. All three functions are used to display JSON data in the spreadsheet, however:

• INSERT_DATA: Keeps adding the data to the table in new rows with every function execution.
• OVERWRITE_DATA: Overwrites the table data with every new function execution.
• UPDATE_DATA: Takes an extra key parameter, which is usually a unique identifier (e.g., date). It checks the defined key to check if the inserted rows are unique (don’t match the key) or not. If a row is unique, it’s inserted normally in the table, acting like INSERT_DATA. If a row is not unique, it doesn’t insert it in a new row and only updates the other data points it contains if they had any changes.

In this case, we can use any of them to display all the metrics.

=INSERT_DATA(PARSE(A1,"[‘totalsForAllResults’]"),A3:J3)

You can also remove the J, indicating that you want to insert all of the metrics in the third row, no matter how many columns it takes.

=INSERT_DATA(PARSE(A1,"[‘totalsForAllResults’]"),A3:3)

If you define a specific number of columns that are less than the number of retrieved metrics, you will only display the first few metrics depending on the number of defined columns. For example, by defining A3:D3 (four columns), you would only display the first four metrics.

Since no dimensions are defined, you will only retrieve one row of results. You can see it by expanding the rows section in the retrieved JSON. These are the same aggregate values returned in the same order as the totalsForAllResults section.

Once again, you can display the values using INSERT_DATA, UPDATE_DATA, or OVERWRITE_DATA. However, this is only recommended if you already have your columns defined. Otherwise, it will just insert the values without headers since the values are in an array without any names.

=INSERT_DATA(PARSE(A1,"[‘rows’]"),A3:3)

Displaying Metrics With Dimensions

When dimensions are defined, the values of the Google Analytics metrics are segmented by the dimension values. For example, ga:pageviews requested with ga:country returns the total pageviews per country. That means that instead of aggregate values, a table is returned with multiple rows. For example, by requesting to get the pageviews and users of the website broken down by operating systems.

=CUSTOM_ANALYTICS_GOOGLE(“123456789”,">2020",“ga:pageviews,ga:users”,“ga:OperatingSystem”)

In this case, we have three rows returned as the values of the pageviews, and users are broken down by the retrieved operating systems; Mac, Windows, and iOS.

You can still find the aggregate values of the metrics in the totalsForAllResults section. This includes the values for all retrieved operating systems combined.

To display the aggregate values, we can do it the same way as before by parsing the values separately or by using INSERT_DATA, OVERWRITE_DATA, or UPDATE_DATA on the totalForAllResults section. However, to retrieve the full table, we would have to use INSERT_DATA, OVERWRITE_DATA, or UPDATE_DATA on the rows section.

=OVERWRITE_DATA(PARSE(A1,"[‘rows’]"),A3:3)

As previously mentioned, the values will be inserted without the headers. The order that the columns are inserted in are as follows:

1. The dimensions are inserted first. If multiple dimensions are defined, they are shown in the same order specified in the dimensions parameter. In this case, column A is ga:operatingSystem.
2. The metrics are then inserted. For the CUSTOM_ANALYTICS_GOOGLE function, they’re inserted in the specified order. For PAGE_ANALYTICS_GOOGLE and SESSION_ANALYTICS_GOOGLE, the order is prespecified and doesn’t change. You can find out the order of the metrics in the totalsForAllResults section. In this case, column B is ga:pageviews, and column C is ga:users.

You can either define the column headers manually by entering them in the spreadsheet (cells A3:C3), or you can have them displayed dynamically.

Displaying the Column Headers Dynamically

When manually defining the column headers, you might run into issues if you make any changes to the Google Analytics function. Imagine in this last example if we added a few more metrics and/or dimensions, or if we changed their order. Once the function is re-executed, the OVERWRITE_DATA function would insert the new values under the old pre-defined column headers, mixing all of the values and causing confusion. This is why it is better to define the Google Analytics metrics column headers dynamically.

You might have noticed a section in the retrieved JSON called columnHeaders. This section contains an array of the column headers of the retrieved data. Each array item contains:

• The name of the metric or dimension.
• The column type and whether it’s a metric or a dimension.
• The data type (e.g., string, integer, etc.).

To display the headers dynamically, all you have to do is parse the header names in the cells of the column headers. Again, you can do this by clicking on the name to copy it. Then, paste it in the desired cell.

A3: =PARSE(A1,"[‘columnHeaders’][0].[‘name’]”)
B3: =PARSE(A1,"[‘columnHeaders’][1].[‘name’]”)
C3: =PARSE(A1,"[‘columnHeaders’][2].[‘name’]”)

You can add more column headers simply by copying and pasting the formula and changing the array index. “0” for the first column, “1” for the second, “2” for the third, and so on.

That way, if we make any changes to the function or the defined metrics and dimensions, the entire table, including the values and the headers, would automatically reflect those changes. For example, if we change ga:operatingSystem to ga:browser, you can see that the values, as well as the headers, will change accordingly.

Make the Most out of Google Analytics!

Make sure to check out our forum post on How to Use Google Analytics in dashdash Like a Pro to learn more about the integration and how you can leverage its full power.