How to Use Google Analytics in dashdash Like a Pro

dashdash’s latest integration, Google Analytics, gives you the power to create custom dashboards, automate your metrics reporting, and query your data to make important business decisions.

With such a powerful integration like Google Analytics comes countless things that you can do with it. That’s why we’re going to quickly go through the available functions and parameters below as well as how you can leverage each one to the fullest.

Functions

LIST_ACCOUNTS_ANALYTICS_GOOGLE: Returns a list of all your Google Analytics accounts, properties, and views. This could be helpful to retrieve the ID of the view you want to pull data from instead of having to do it manually.

PAGE_ANALYTICS_GOOGLE: Returns the Google Analytics page metrics of the website or a specific webpage including:

  • Page value (ga:pageValue): The average value of this page or set of pages, which is equal to (ga:transactionRevenue + ga:goalValueAll) / ga:uniquePageviews.
  • Entrances (ga:entrances): The number of entrances to the property measured as the first pageview in a session, typically used with ga:landingPagePath.
  • Entrance rate (ga:entranceRate): The percentage of pageviews in which this page was the entrance.
  • Pageviews (ga:pageviews): The total number of pageviews for the property.
  • Pageviews per session (ga:pageviewsPerSession): The average number of pages viewed during a session, including repeated views of a single page.
  • Unique pageviews (ga:uniquePageviews): Unique Pageviews is the number of sessions during which the specified page was viewed at least once. A unique pageview is counted for each page URL + page title combination.
  • Time on page (ga:timeOnPage): Time (in seconds) users spent on a particular page, calculated by subtracting the initial view time for a particular page from the initial view time for a subsequent page. This metric does not apply to exit pages of the property.
  • Average time on page (ga:avgTimeOnPage): The average time users spent viewing this page or a set of pages.
  • Exits (ga:exits): The number of exits from the property.
  • Exit rate (ga:exitRate): The percentage of exits from the property that occurred out of the total pageviews.

SESSION_ANALYTICS_GOOGLE: Returns the Google Analytics session metrics of the website or a specific webpage including:

  • Users (ga:users): The total number of users for the requested time period.
  • New users (ga:newUsers): The number of sessions marked as a user’s first sessions.
  • % New Sessions (ga:percentNewSessions): The percentage of sessions by users who had never visited the property before.
  • Sessions (ga:sessions): The total number of sessions.
  • Session duration (ga:sessionDuration): The length (returned as a string) of a session measured in seconds and reported in second increments.
  • Average session duration (ga:avgSessionDuration): The average duration (in seconds) of users’ sessions.
  • Sessions per user (ga:sessionsPerUser): The total number of sessions divided by the total number of users.
  • Bounces (ga:bounces): The total number of single page (or single interaction hit) sessions for the property.
  • Bounce rate (ga:bounceRate): The percentage of single-page sessions (i.e., session in which the person left the property from the first page).
  • Hits (ga:hits): Total number of hits for the view (profile). This metric sums all hit types, including pageview, custom event, e-commerce, and other types. Because this metric is based on the view (profile), not on the property, it is not the same as the property’s hit volume.

CUSTOM_ANALYTICS_GOOGLE: Allows you to return a specified set of Google Analytics metrics of the website or a specific webpage. You can retrieve up to 10 metrics per request.

Make sure to check the Google Analytics Dimensions & Metrics Explorer for a list of all available dimensions and metrics. You can select a dimension or metric for additional details such as descriptions and attributes.

Functions Parameters

ID (id)

Required

The unique table ID in the form XXXX or ga:XXXX, where XXXX is the Analytics view (profile) ID for which the query will retrieve the data.

You can retrieve the ID in two ways:

  1. Manually
  2. Using dashdash

1. Retrieving the ID manually

To retrieve your ID manually, you need to:

  1. Head to Google Analytics.
  2. Select your desired analytics account, property, and view.
  3. You will find the view ID under the view name. For example, in the screenshot below the ID is the highlighted 12345678

2. Retrieving the ID using dashdash

  1. Use the LIST_ACCOUNTS_ANALYTICS_GOOGLE function to list down your Google Analytics accounts, properties, and views.

    A1: =LIST_ACCOUNTS_ANALYTICS_GOOGLE()

  2. Find the view ID you want to use and PARSE it into a cell. You can do so by clicking on it to copy it to the clipboard and paste it in a cell to automatically generate the necessary formula. You can then always reference that cell whenever the view ID is required.

    B1: =PARSE(A1,"[‘items’][0].[‘webProperties’][0].[‘profiles’][0].[‘id’]")

  3. Note that you need the view ID for the functions to work, not the account or property ID.

Date range (date_range)

Required

The data range you want to look up. This can be:

  • A specific date formatted as YYYY-MM-DD (e.g. 2020-02-01).
  • Before or after a specific date or year by using the less than (<) and greater than (>) signs (e.g. <2020 or >2019-06-15).
  • A date range including a start and an end date. (e.g. [2020-01-01;2020-02-29]).

The earliest valid start and end dates are 2005-01-01. There is no upper limit restriction for the end-date. Dates are always based on the timezone of the view (profile) specified in the request.

Metrics (metrics)

Required

Metrics are the aggregated statistics for user activity to your site, such as clicks or pageviews. This parameter is only required for the CUSTOM_ANALYTICS_GOOGLE functions as the other ones (PAGE_ANALYTICS_GOOGLE and SESSION_ANALYTICS_GOOGLE) have predefined sets of metrics that they return.

If a request has no dimensions parameter, the returned metrics provide aggregate values for the requested date range, such as overall pageviews or total bounces. However, when dimensions are requested, values are segmented by dimension value. For example, ga:pageviews requested with ga:country returns the total pageviews per country.

When requesting metrics, keep in mind:

  • You must supply at least one metric; a request can’t consist only of dimensions.
  • You can supply a maximum of 10 metrics per request. However, you can use multiple functions, each with a different set of metrics to get more.
  • Most combinations of metrics from multiple categories can be used together, provided no dimensions are specified.
  • A metric can be used in combination with other dimensions or metrics, but only where valid combinations apply for that metric. See the Dimensions and Metrics Explorer for details.

Page path (page_path)

Optional

Page path is used to look up the metrics of a specific webpage by entering its relative path. It is used to make the process of filtering by page path easier, instead of having to define it as one of the filters as pagePath==/example.
For example, if we assume we want to look up the metrics of the pricing page of our website (www.website.com/pricing) we would use the page path "/pricing".
Please note that:

  • If you leave the page path parameter empty, you will get the metrics for the entire website. If your view is limited to a specific part of the website then you’ll get all the metrics of the part that the view has access to.
  • If you pass a forward slash as the page path parameter "/", you will get the metrics of the main page of the website linked to your view (In most cases, the homepage).
  • When using a page path, you should use the relative page path (e.g. "/pricing"). This will return the metrics for that specific page only.

Dimensions (dimensions)

Optional

The dimensions parameter breaks down metrics by common criteria; for example, by ga:browser or ga:city. While you can look up the total number of pageviews to your site, it might be more interesting to look up the number of pageviews broken down by browser. In this case, you’ll see the number of pageviews from Firefox, Internet Explorer, Chrome, and so forth.

When using dimensions, be aware of the following constraints:

  • You can supply a maximum of 7 dimensions in a request.
  • Not all dimensions and metrics can be queried together. Only certain dimensions and metrics can be used together to create valid combinations. You can head to the Google Analytics Dimensions & Metrics Explorer and select a dimension or metric checkbox to see all the other values that can be combined in the same query.

Sort (sort)

Optional

A list of metrics and dimensions indicating the sorting order and sorting direction for the returned data. Note that:

  • Sorting order is specified by the left to right order of the metrics and dimensions listed.
  • Sorting direction defaults to ascending and can be changed to descending by using a minus sign (-) prefix before the metric or dimension.

Sorting the results of a request enables you to ask different questions about your data. For example, to address the question “What are my top countries, and which browsers do they use most?” you can make the function request with the following parameter. It sorts first by ga:country and then by ga:browser, both in ascending order:

ga:country,ga:browser

To answer the related question “What are my top browsers, and which countries use them most?”, you can make a query with the following parameter. It sorts first by ga:browser and then by ga:country, both in ascending order:

ga:browser,ga:country

When using the sort parameter, keep in mind the following:

  • Sort only by dimensions or metrics values that you have used in the dimensions or metrics parameters. If your request sorts on a field that is not indicated in either the dimensions or metrics parameter, you will receive an error.
  • By default, strings are sorted in ascending alphabetical order.
  • Numbers are sorted in ascending numeric order by default.
  • Dates are sorted in ascending order by date by default.

Filters (filters)

Optional

The filters parameter restricts the data returned from your request. To use the filters parameter, supply a dimension or metric on which to filter, followed by the filter expression. For example, the following query requests ga:pageviews and ga:browser for view (profile) 12345, where the ga:browser dimension is Firefox:

=CUSTOM_ANALYTICS_GOOGLE(“12345”,">2020",“ga:pageviews”,,“ga:browser”,,“ga:browser==Firefox”)

Filtered queries restrict the rows that do (or do not) get included in the result. Each row in the result is tested against the filter: if the filter matches, the row is retained and if it doesn’t match, the row is dropped.

  • Dimension filtering: Filtering occurs before any dimensions are aggregated so that the returned metrics represent the total for only the relevant dimensions. In the example above, the number of pageviews would be only those pageviews where Firefox is the browser.
  • Metrics filtering: Filtering on metrics occurs after the metrics are aggregated.
  • Valid combinations: You can filter for a dimension or metric that is not part of your query, provided all dimensions/metrics in the request and the filter are valid combinations. For example, you might want to query for a dated list of pageviews, filtering on a particular browser.

Filter Syntax

A single filter uses the form:

ga:name operator expression

In this syntax:

  • name: the name of the dimension or metric to filter on. For example: ga:pageviews filters on the pageviews metric.
  • operator: defines the type of filter match to use. Operators are specific to either dimensions or metrics.
  • expression: states the values to be included in or excluded from the results. Expressions use regular expression syntax.

Filter Operators

There are six filter operators for dimensions and six filter operators for metrics.

Metric Filters

Operator Description Examples
== Equals Return results where the time on the page is exactly ten seconds: ga:timeOnPage==10
!= Does not equal Return results where the time on the page is not ten seconds: ga:timeOnPage!=10
> Greater than Return results where the time on the page is strictly greater than ten seconds: ga:timeOnPage<10
< Less than Return results where the time on the page is strictly less than ten seconds: ga:timeOnPage<10
>= Greater than or equal to Return results where the time on the page is ten seconds or more: ga:timeOnPage>=10
<= Less than or equal to Return results where the time on the page is ten seconds or less: ga:timeOnPage<=10

Dimension Filters

Operator Description Examples
== Exact match Aggregate metrics where the city is Irvine: ga:city==Irvine
!= Does not match Aggregate metrics where the city is not Irvine: ga:city!=Irvine
=@ Contains substring Aggregate metrics where the city contains York: ga:city=@York
!@ Does not contain substring Aggregate metrics where the city does not contain York: ga:city!@York
=~ Contains a match for the regular expression Aggregate metrics where the city starts with New: ga:city=~^New
!~ Does not match regular expression Aggregate metrics where the city does not start with New: ga:city!~^New

Note: the ^ character in the last two examples is used to anchor a pattern to the beginning of the string.

Combining Filters

Filters can be combined using OR and AND boolean logic.

OR

The OR operator is defined using a comma (,). It takes precedence over the AND operator and may NOT be used to combine dimensions and metrics in the same expression.

Examples:

  • Country is either (United States OR Canada):
    ga:country==United States,ga:country==Canada
  • Firefox users on (Windows OR Macintosh) operating systems:
    ga:browser==Firefox;ga:operatingSystem==Windows,ga:operatingSystem==Macintosh

AND

The AND operator is defined using a semi-colon (;). It is preceded by the OR operator and CAN be used to combine dimensions and metrics in the same expression.

Examples:

  • Country is United States AND the browser is Firefox:
    ga:country==United%20States;ga:browser==Firefox
  • Country is United States AND language does not start with ‘en’:
    ga:country==United%20States;ga:language!~^en.
  • Operating system is (Windows OR Macintosh) AND browser is (Firefox OR Chrome):
    ga:operatingSystem==Windows,ga:operatingSystem==Macintosh;ga:browser==Firefox,ga:browser==Chrome
  • Country is United States AND sessions are greater than 5:
    ga:country==United%20States;ga:sessions>5

Segment (segment)

Optional

Segments allow you to filter your results by selecting a specific segment of users and sessions. For example, users that have completed at least 2 transactions with a lifetime revenue of over $1,000, or users who visited first on a mobile device followed by a desktop browser.

When using the segment parameter, you can request a segment in two ways:

  1. Segments by ID: Query using the numeric ID of a built-in or custom segment.
  2. Dynamic Segments: Specify the segment dynamically at the time of the request.

1. Segments by ID

You can request a segment by using the ID of a built-in or custom segment. For example:

gaid::-10

The Google Analytics built-in segments are:

Segment ID Segment
gaid::-1 All Users
gaid::-2 New Users
gaid::-3 Returning Users
gaid::-4 Paid Traffic
gaid::-5 Organic Traffic
gaid::-6 Search Traffic
gaid::-7 Direct Traffic
gaid::-8 Referral Traffic
gaid::-9 Sessions with Conversions
gaid::-10 Sessions with Transactions
gaid::-11 Mobile and Tablet Traffic
gaid::-12 Non-bounce Sessions
gaid::-13 Tablet Traffic
gaid::-14 Mobile Traffic
gaid::-15 Tablet and Desktop Traffic
gaid::-16 Android Traffic
gaid::-17 iOS Traffic
gaid::-18 Other Traffic (Neither iOS nor Android)
gaid::-19 Bounced Sessions
gaid::-100 Single Session Users
gaid::-101 Multi-session Users
gaid::-102 Converters
gaid::-103 Non-Converters
gaid::-104 Made a Purchase
gaid::-105 Performed Site Search

To select a custom segment, you can easily find its ID using the Google Analytics Query Explorer by clicking on the segment text field and scrolling down until you find the desired segment and its ID.

2. Dynamic segments

You can also dynamically create and use a segment when making a request. Typically when creating a dynamic segment you should consider the following:

  1. Selecting Users vs. Sessions
  2. Using Conditions vs. Sequences
  3. Using Metric Scopes

Not all dimensions and metrics can be used in segments. To review which dimensions and metrics are allowed in segments visit the Dimensions and Metrics Explorer.

1. Selecting Users vs. Sessions

Specify whether you’re trying to select users or sessions (or both).

  • Use users:: to select users.
  • Use sessions:: to select sessions.
  • If conditions for both users:: and sessions:: are specified:
    1. user conditions are applied first to output the sessions for the matching users.
    2. session conditions are only applied to sessions from #1.

For example:

  • Select users who used Chrome browser in at least one of their sessions.

    users::condition::ga:browser==Chrome

  • Select sessions where the Chrome browser was used.

    sessions::condition::ga:browser==Chrome

  • Select sessions from the city of London from users that had at least 1 session where the Chrome browser was used.

    users::condition::ga:browser==Chrome;sessions::condition::ga:city==London

2. Using Conditions vs. Sequences

Once you’ve determined whether you want to segment users or sessions, you specify one or more conditions and/or sequences.

Conditions

Conditions use the condition:: prefix. For example:

  • To select users from London that have visited using the Chrome browser.

    users::condition::ga:city==London;ga:browser==Chrome

Sequences

Sequence conditions consist of one or more steps, where each step is defined by one or more dimension/metric conditions.

Specify sequence-based conditions using the sequence:: prefix and the followed by (;–>>) or immediately followed by (;–>) operators. For example:

  • Select users that first used a desktop followed by a mobile device. Since we are segmenting users, this searches all the sessions of a user and checks if a user used desktop in one session, followed by a mobile device in one of the subsequent sessions.

    users::sequence::ga:deviceCategory==desktop;->>ga:deviceCategory==mobile

  • You can also use multiple conditions for each step

    users::sequence::ga:deviceCategory==desktop;ga:operatingSystem==Windows->>ga:deviceCategory==mobile;ga:operatingSystem==Android

  • You can also combine conditions and sequences in a segment using an AND (i.e., ‘;’) operator.

    users::condition::ga:totalEvents>10;sequence::ga:deviceCategory==desktop->>ga:deviceCategory==mobile


3. Using Metric Scopes

A scope for a metric defines the level at which that metric is defined — HIT, SESSION, or USER. For example, ga:pageviews and ga:transactions are HIT level metrics, since they occur in a hit, whereas ga:sessionDuration and ga:bounces are SESSION level metrics since there is a single value for these per session. Conceptually, USER is the highest level scope and HIT is the lowest level scope.

Metric values can also be reported at scopes greater than its primary scope. E.g., ga:pageviews and ga:transactions can be reported at SESSION and USER level by just adding them up for each hit that occurs in those sessions or for those users.

You can specify the scope for each metric condition, which will determine the level at which that condition is applied. Metric scopes are specified using perUser::, perSession::, or perHit:: prefix.

For example:

  • Select users that have spent at least $10 on a website (i.e., lifetime value of a user is at least $10).

    users::condition::perUser::ga:transactionRevenue>=10

  • Select users who have spent at least $10 in a session.

    users::condition::perSession::ga:transactionRevenue>=10

Google Analytics Tips

Skipping Parameters

You could skip any of the optional parameters by adding commas and leaving it empty. For example, if we want to get the page analytics of the entire website (without defining a page path) within a specific date range with ga:pagePath as a dimension. We can skip the page_path parameter by leaving it empty as follows:

=PAGE_ANALYTICS_GOOGLE(“123456789”,">2020",,“ga:pagePath”)

Templates

When you’re getting started with Google Analytics in dashdash, it is advised to start by using the Create Google Analytics dashboards. Each view in the template can be used to test out a different function:

  • Setup: LIST_ACCOUNTS_ANALYTICS_GOOGLE
  • Page Analytics: PAGE_ANALYTICS_GOOGLE
  • Session Analytics: SESSION_ANALYTICS GOOGLE
  • Custom Analytics: CUSTOM_ANALYTICS_GOOGLE

You can use the template to easily test out the different parameters and have the prebuilt template automatically retrieve and display the results before taking a deep dive into the functions separately and building your own dashboards from scratch. You can also go through the template logic, see how it’s built, and customize it based on need.

Google Analytics Query Explorer

You can leverage the Google Analytics Query Explorer as a reference when creating your function request.

The Query Explorer is great because it gives you easy access to the needed parameters including:

  • Your view ID.
  • A list of all available metrics, dimensions, sort filters, and segments by clicking on each respective field.
  • It also allows you to discover the possible combinations between the different metrics and dimensions.
  • You can finally run your queries to test them out and see if the results returned and the ones you’re looking for.

Get Started Now!

Sign up to dashdash to get started building your Google Analytics dashboards.
and automating the reporting process with Slack, emails, or SMS.

4 Likes