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.
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:entrances): The number of entrances to the property measured as the first pageview in a session, typically used with
Entrance rate (
ga:entranceRate): The percentage of pageviews in which this page was the entrance.
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.
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:
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.
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.
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).
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.
The unique table ID in the form
XXXX is the Analytics view (profile) ID for which the query will retrieve the data.
You can retrieve the ID in two ways:
- Using dashdash
1. Retrieving the ID manually
To retrieve your ID manually, you need to:
- Head to Google Analytics.
- Select your desired analytics account, property, and view.
- You will find the view ID under the view name. For example, in the screenshot below the ID is the highlighted
2. Retrieving the ID using dashdash
- Use the
LIST_ACCOUNTS_ANALYTICS_GOOGLEfunction to list down your Google Analytics accounts, properties, and views.
- 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.
- Note that you need the view ID for the functions to work, not the account or property ID.
Date range (date_range)
The data range you want to look up. This can be:
- A specific date formatted as
- Before or after a specific date or year by using the less than (
<) and greater than (
>) signs (e.g. <
- A date range including a start and an end date. (e.g.
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 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 (
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)
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
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
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.
The dimensions parameter breaks down metrics by common criteria; for example, by
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.
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:
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:
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.
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:browser for view (profile) 12345, where the
ga:browser dimension is 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.
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.
There are six filter operators for dimensions and six filter operators for metrics.
|==||Equals||Return results where the time on the page is exactly ten seconds:
|!=||Does not equal||Return results where the time on the page is not ten seconds:
|>||Greater than||Return results where the time on the page is strictly greater than ten seconds:
|<||Less than||Return results where the time on the page is strictly less than ten seconds:
|>=||Greater than or equal to||Return results where the time on the page is ten seconds or more:
|<=||Less than or equal to||Return results where the time on the page is ten seconds or less:
|==||Exact match||Aggregate metrics where the city is Irvine:
|!=||Does not match||Aggregate metrics where the city is not Irvine:
|=@||Contains substring||Aggregate metrics where the city contains York:
|!@||Does not contain substring||Aggregate metrics where the city does not contain York:
|=~||Contains a match for the regular expression||Aggregate metrics where the city starts with New:
|!~||Does not match regular expression||Aggregate metrics where the city does not start with New:
^ character in the last two examples is used to anchor a pattern to the beginning of the string.
Filters can be combined using OR and AND boolean logic.
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.
- Country is either (United States OR Canada):
- Firefox users on (Windows OR Macintosh) operating systems:
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.
- Country is United States AND the browser is Firefox:
- Country is United States AND language does not start with ‘en’:
- Operating system is (Windows OR Macintosh) AND browser is (Firefox OR Chrome):
- Country is United States AND sessions are greater than 5:
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:
- Segments by ID: Query using the numeric ID of a built-in or custom segment.
- 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:
The Google Analytics built-in segments are:
|gaid::-9||Sessions with Conversions|
|gaid::-10||Sessions with Transactions|
|gaid::-11||Mobile and Tablet Traffic|
|gaid::-15||Tablet and Desktop Traffic|
|gaid::-18||Other Traffic (Neither iOS nor Android)|
|gaid::-100||Single Session Users|
|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:
- Selecting Users vs. Sessions
- Using Conditions vs. Sequences
- 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).
users::to select users.
sessions::to select sessions.
- If conditions for both
- user conditions are applied first to output the sessions for the matching users.
- session conditions are only applied to sessions from #1.
- Select users who used Chrome browser in at least one of their sessions.
- Select sessions where the Chrome browser was used.
- Select sessions from the city of London from users that had at least 1 session where the Chrome browser was used.
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 use the
condition:: prefix. For example:
- To select users from London that have visited using the Chrome browser.
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.
- You can also use multiple conditions for each step
- You can also combine conditions and sequences in a segment using an AND (i.e., ‘
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:transactions are HIT level metrics, since they occur in a hit, whereas
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: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
- Select users that have spent at least $10 on a website (i.e., lifetime value of a user is at least $10).
- Select users who have spent at least $10 in a session.
Google Analytics Tips
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:
You can automate the date parameter by using date functions like
TODAY returns the current date, while
YEAR return the day, month, and year of a specified date respectively. You can use arithmetic operations on these functions to retrieve previous or upcoming dates.
Once you’ve retrieved the date, you need to pass it to the Google Analytics function using the
TO_TEXT function to input its value in the necessary text format. You can also use the
CONCATENATE function to construct date ranges.
Here are some examples of different dynamic date parameters you can use.
|Date parameter||Data retrieved for|
|TO_TEXT(TODAY()-7)||One week ago (e.g.
|=CONCATENATE("[",TO_TEXT(TODAY()-7),";",TO_TEXT(TODAY()-1),"]")||The previous week (e.g.
|=CONCATENATE("[",TO_TEXT(TODAY()-31),";",TO_TEXT(TODAY()-1),"]")||The previous month (e.g.
|=CONCATENATE(">",LEFT(TO_TEXT(TODAY()),8),“01”)||This month (e.g.
|=TO_TEXT(CONCATENATE(">",YEAR(TODAY()),"-01-01"))||This year (e.g.
You can then use the
REFRESH function to automate the retrieval of the dynamic data ranges.
When you’re getting started with Google Analytics in dashdash, you can get started with pre-built templates for different use cases:
- Create Google Analytics dashboards
- Get Google Analytics Slack reports
- Get Google Analytics email reports
You can use the templates 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 logic of the templates, see how they’re built, and customize them 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.