How to Create a Custom Google Analytics Dashboard in a Spreadsheet

In this tutorial, you will learn how to create a custom Google Analytics dashboards in a spreadsheet. Then, publish it as a web app that you can share with your team members.

You can use the Create Google Analytics dashboards template to get started right away or follow this step-by-step guide to build it from scratch.

What you’ll need

• A dashdash account. You’ll find more information at the end of the guide on how to get early access to the platform.
• A Google Analytics account.

1. Connecting Google Analytics

First, you will need to connect the Google Analytics integration and retrieve the Google Analytics view ID that you will use to fetch the data.

Connecting the Integration

To connect the Google Analytics integration, you need to:

1. Head to the Google Analytics integration page or open it inside the spreadsheet by clicking on the “Integrations” button in the upper right corner.
2. Click the “Connect” button. This will redirect you to the Google Analytics website.
3. Select the Google Analytics account you want to connect.
4. Click “Allow” to authorize access to the platform.

Getting the View ID

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”.

Retrieving the ID using dashdash

Alternatively, you can use the LIST_ACCOUNTS_ANALYTICS_GOOGLE function to list down your Google Analytics accounts, properties, and views.

D1: Get ID
E1: =LIST_ACCOUNTS_ANALYTICS_GOOGLE()

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 then paste it in a cell to generate the necessary formula automatically. You can always reference that cell whenever the view ID is required.

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

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

2. Retrieving the Metrics

Analytics Functions

dashdash offers three functions that allow you to retrieve data from Google Analytics.

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

• Page value (ga:pageValue)
• Entrances (ga:entrances)
• Entrance rate (ga:entranceRate)
• Pageviews (ga:pageviews)
• Pageviews per session (ga:pageviewsPerSession)
• Unique pageviews (ga:uniquePageviews)
• Time on page (ga:timeOnPage)
• Average time on page (ga:avgTimeOnPage)
• Exits (ga:exits)
• Exit rate (ga:exitRate)

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

• Users (ga:users)
• New users (ga:newUsers)
• % New Sessions (ga:percentNewSessions)
• Sessions (ga:sessions)
• Session duration (ga:sessionDuration)
• Average session duration (ga:avgSessionDuration)
• Sessions per user (ga:sessionsPerUser)
• Bounces (ga:bounces)
• Bounce rate (ga:bounceRate)
• Hits (ga:hits)

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

We’ll be using CUSTOM_ANALYTICS_GOOGLE to customize our dashboard with our desired set of metrics. However, you can use any of the other functions if they cover your needs.

Getting the Metrics

The CUSTOM_ANALYTICS_GOOGLE function has three required parameters and five optional ones:

Required:

• id: The view ID retrieved in the previous section.
• date_range: The date range we want to look up.
• metrics: The aggregated statistics for user activity to your website, such as clicks or pageviews.

Optional:

• page_path: Used to look up the metrics of a specific webpage by entering its relative path.
• dimensions: Breaks down the metrics by common criteria (e.g. country, browser).
• sort: A list of metrics and dimensions indicating the sorting order and sorting direction for the returned data.
• filters: Restricts the data retrieved by defined dimensions or metrics expressions.
• segment: Restricts the data by a specific segment of users and sessions.

For example, if we want to look up the pageviews and sessions of view “12345678” during the first week of April 2020. By browsing the returned {analytics} object, you will find the retrieved metrics and their values.

E2: =CUSTOM_ANALYTICS_GOOGLE(“12345678”,”[2020–04–01;2020–04–07]”,”ga:pageviews,ga:sessions”)

Check out the Google Analytics Metrics & Dimensions Explorer for a full list of available metrics and dimensions.

To make this more customizable, we can list down the parameters in the spreadsheet and add them to the function as cell references. That way, we can easily change their values and have the function automatically reflect the changes.

A2: Date range
A3: Metrics
A4: Page path
A5: Dimensions
A6: Sort
A7: Filters
A8: Segment
E2: =CUSTOM_ANALYTICS_GOOGLE(B1,B2,B3,B4,B5,B6,B7,B8)

To recreate the previous request, we would just have to update the respective cells.

B2: [2020–04–01;2020–04–07]
B3: ga:pageviews,ga:sessions

If a request has no dimensions, the returned metrics provide aggregate values for the requested date range, such as overall pageviews or total bounces. We can easily display these values by parsing them, the same way we did for the ID, by clicking on it and pasting it in a cell.

E3: =PARSE(E2,”[‘totalsForAllResults’].[‘ga:pageviews’]”)

When dimensions are defined, the values of the 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 sessions of the website broken down by device categories.

B5: ga:deviceCategory

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

However, in this case, we also have three rows returned as the values of the pageviews and sessions are broken down by the retrieved device categories; desktop, mobile, and tablet.

We can parse these values to create the desired table. However, it’s not always efficient to parse them one at a time, especially if your request returns hundreds or thousands of rows. We also want the table to update automatically in case any changes occur.

In the next section, we’ll discuss the functions dashdash offers to automate the process of displaying the retrieved tables.

3. Creating the Dashboard

Displaying the Metrics

dashdash offers three functions that allow you to display JSON data in table form easily; INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA.

• INSERT_DATA: Displays the data in a table and keeps adding any new data to the same 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.

When opening the {analytics} object returned in E2, you will find two tabs; recommended and all data. The “all data” tab is the one we’ve been exploring so far which contains the API response. While the “recommended” tab offers recommended tables that you can create for specific custom dashdash functions. In this case, a Google Analytics report.

By clicking on it, you are presented with two options. “Insert” which displays the data using an INSERT_DATA function and “Overwrite” which uses an OVERWRITE_DATA function. We’ll be using OVERWRITE_DATA to overwrite the table and create a new one whenever we make any changes to the parameters.

Click on Overwrite, select where you want to display the table (e.g. A10), and press Enter. The function will be inserted in A9 and the table displayed in A10:C10. You can also cut the function in A9 and paste it in E3 to move it out of the way.

D3: Fill table
E3: =OVERWRITE_DATA(PARSE(E2,”[‘rows’]”),A10:10)

Now, if you make any changes to the parameters, the table will be updated automatically.

4. Publishing the Dashboard

Once you’ve created our desired dashboard, you can publish it as a web app. That way, you can share it with other people.

First, we need to define the cells where users can input data. To do that, we need to select the input cells B2:B8, click on the action elements section and select “Input field”. You can choose how much access you want to give to the users by limiting or eliminating certain parameters.

To publish the app, click on the “Publishing Settings” icon in the upper left corner next to “Preview.” Here, if the spreadsheet has multiple views, you can select which ones you want to publish and which ones to hide. For example, we can move the view ID to a separate view and reference it from that one, if we don’t want to display it in the shared app. Then, hit the “Publish” button.

The app is now published, and the link is copied to the clipboard. We can also copy it by clicking on the “Copy link to clipboard” icon that has appeared next to the publishing one. By pasting the link in the address bar, we can view the published app version of the dashboard.

The published app behaves in the exact same way as the spreadsheet. That way, you can share it with your team members without having to give them access to your dashdash or Google Analytics accounts.

Make the Most out of dashdash!

To take your dashboard to the next level, you can leverage dashdash’s automation functions like the REFRESH function to automate your report and have it retrieve new data periodically. You can also combine it with other integrations like Slack and Gmail to receive daily or weekly reports of your website performance by Slack or email.

You can get started with pre-built templates for these use cases:

• Get Google Analytics Slack reports
• Get Google Analytics email reports

Also, 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.

Get Early Access!

To get a dashdash account and start building your custom Google Analytics dashboard, you can reach out to me via:

• Email: hady@dashdash.com
• Twitter: @HadyElHady93
• Calendly: Book a call so I can help you set up your dashboard and get started.