<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Hady ElHady on Medium]]></title>
        <description><![CDATA[Stories by Hady ElHady on Medium]]></description>
        <link>https://medium.com/@hadyelhady?source=rss-adb9109052c9------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*xYC8I5Y_55nzl-PJ.</url>
            <title>Stories by Hady ElHady on Medium</title>
            <link>https://medium.com/@hadyelhady?source=rss-adb9109052c9------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 10:29:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@hadyelhady/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[5 Google Analytics Custom Alerts for SEO You Need to Track]]></title>
            <link>https://medium.com/rows/5-google-analytics-custom-alerts-for-seo-you-need-to-track-23a6ec1d3d36?source=rss-adb9109052c9------2</link>
            <guid isPermaLink="false">https://medium.com/p/23a6ec1d3d36</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[growth]]></category>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[seo]]></category>
            <category><![CDATA[marketing]]></category>
            <dc:creator><![CDATA[Hady ElHady]]></dc:creator>
            <pubDate>Thu, 25 Jun 2020 10:56:57 GMT</pubDate>
            <atom:updated>2020-06-25T10:59:58.553Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KQR-95WDJsryugTMArqUGw.png" /></figure><p>As an SEO manager or a marketer, you’re probably too busy to go through the different data and metrics of your website and analyze it every single day to monitor your performance and notice any changes. Even the process of retrieving the data from Google Analytics and comparing it with historical data can be tedious.</p><p>To solve that, Google Analytics events tracking allows you to set up custom events that would trigger actions based on specific behaviors on your website — for example, tracking changes to your pageviews, session durations, traffic referral, etc.</p><p>With so many options that Google Analytics has to offer, it can be confusing as to which alerts you should set up. Below we will discuss the five essential Google Analytics custom alerts for SEO that are a must-have.</p><p>You’ll also find more information at the end of the post on how you can set them up instantly with <a href="https://app.dashdash.com/dashboard/templates/get_seo_website_performance_alerts">Slack alerts using dashdash</a> and how to get early access to the platform.</p><h3>Custom Alert #1: Drop in Organic Traffic</h3><p>A drop in organic traffic can be normal and can be affected by different factors like seasonality. However, since organic visitors come to your website from a search engine, it can be an indicator of SEO issues.</p><p>That’s why it’s crucial to get alerted when drastic changes in organic traffic occur. The change percentage that you should set depends entirely on your average website organic traffic. The higher your organic traffic, the lower you should set the percentage of change as its sensitivity decreases.</p><p>You should check the average daily organic traffic you get and the average change of traffic week-over-week. Then, you should set the alert trigger percentage to be slightly higher than your average. You can set the percentage to a positive value to track spikes or a negative one to track drops in your traffic. You can also choose to monitor the traffic of a specific page as opposed to the entire website.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tnWF0t4ei8OhTt-D9jqtGA.png" /></figure><h3>Custom Alert #2: Drop in Page Visits</h3><p>Having an alert for when page visits drop is essential when tracking the performance of different pages individually — for example, your landing pages. It allows you to quickly identify any issues with your top-performing pages and fix them immediately. You can also track spikes in page visits, which could indicate an important mention on a website or social media.</p><p>A drop in pageviews starting somewhere from 20% to 50% week-over-week would be something to worry about.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZEWH3f3q2JKlTPrk1Q1yFA.png" /></figure><h3>Custom Alert #3: Drop in Goal Conversions</h3><p>A drop in your goal conversions or completions can indicate an issue with your conversion funnel. It could show that one of the funnel steps like a content piece or a landing page is performing poorly and needs your attention.</p><p>On average, a 15% drop in goal conversions from one week to the next would be alarming. That’s why not having such an alert can be very costly for your business as you would be missing out on a lot of opportunities that you could have had had you caught the issue earlier.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wi-Atcm5HjP9PvzX4p4uKQ.png" /></figure><h3>Custom Alert #4: Spike in 404 Visits</h3><p>Not only can a spike in the number of users coming across 404 pages on your website indicate a problem with your user experience, but it can also affect your SEO performance. The number of 404 page visits that would be considered alarming depends on your website traffic. Having a few 404 page views when you get thousands of visitors should be fine, but if the number suspiciously increases drastically, this can be an indication of an SEO issue on your website.</p><p>Setting up an alert that is triggered by a week-over-week 10% increase in 404 visits is, in most cases, a good indicator. The issue is usually caused by broken internal links, which causes search crawlers to get stuck trying to reach non-existent pages on your website, which can harm your SEO and rankings. Once the alert is received, you can easily use <a href="https://search.google.com/search-console/">Google Search Console</a> to identify the issue’s source and fix it.</p><p>For the alert to work, your 404 pages need to have a custom page title that would be used to track them. The most common formats look something like “Website | Not Found” or “Page Not Found | Website”. However, you can always customize them as you wish.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N-dFLHmkgt3LjxCbVmOAuQ.png" /></figure><h3>Custom Alert #5: Spike in Bounce Rate</h3><p>The last alert is for spikes in your bounce rate. The bounce rate is the percentage of users who exit the website after viewing only one page. You have to monitor the bounce rate, especially for your top-performing landing pages. That way, you can immediately take any needed actions like optimizing the content of the page or adjusting your call to actions.</p><p>Bounce rates can differ vastly depending on your business or the content of your website. For example, blogs naturally have lower bounce rate since it’s more likely for users to view more than one post or article as opposed to more commercial or business websites.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oX8H5FG1XzzGvfzWcFNlMw.png" /></figure><h3>Get Started With These Alerts Now!</h3><p>Instead of having to spend a lot of time and effort setting up these custom Google Analytics alerts, you can get started with all five of them right away using the <a href="https://dashdash.com/templates/seo-website-performance-alerts-slack">“Get SEO website performance alerts”</a> dashdash template. It will send you timely alerts straight to your Slack workspace, which is a feature you can’t set up through Google Analytics. Alternatively, you can adjust it to have them sent to your email instead or even by SMS.</p><p>Did I mention that it’s <strong>FREE</strong> and only takes a few minutes to set up?</p><h3>Get Early Access!</h3><p>To get a dashdash account and set up your custom Google Analytics alerts, you can reach out to me via:</p><ul><li><strong>Email:</strong> <a href="/service/mailto:hady@dashdash.com">hady@dashdash.com</a></li><li><strong>Twitter:</strong> <a href="https://twitter.com/HadyElHady93">@HadyElHady93</a></li><li><strong>Calendly:</strong> <a href="https://calendly.com/dashdash-onboardings/early-access">Book a call</a> so I can help you set up your alerts and get started.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=23a6ec1d3d36" width="1" height="1" alt=""><hr><p><a href="https://medium.com/rows/5-google-analytics-custom-alerts-for-seo-you-need-to-track-23a6ec1d3d36">5 Google Analytics Custom Alerts for SEO You Need to Track</a> was originally published in <a href="https://medium.com/rows">Rows</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Search GitHub Jobs using a Spreadsheet]]></title>
            <link>https://medium.com/rows/search-github-jobs-using-a-spreadsheet-f7f34cd33967?source=rss-adb9109052c9------2</link>
            <guid isPermaLink="false">https://medium.com/p/f7f34cd33967</guid>
            <category><![CDATA[spreadsheets]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[no-code]]></category>
            <category><![CDATA[jobs]]></category>
            <category><![CDATA[github]]></category>
            <dc:creator><![CDATA[Hady ElHady]]></dc:creator>
            <pubDate>Fri, 22 May 2020 12:19:42 GMT</pubDate>
            <atom:updated>2020-05-22T12:19:42.857Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4F-zfwWrvsbAfiRxs9qqYw.png" /></figure><p>The GitHub Jobs API allows you to search and view jobs. You can look up jobs by roles, functions, and locations, and retrieve a list of the job listings including the title, company name, location, description, how to apply, and more.</p><p>In this tutorial, you will learn how to create an app using the GitHub API to retrieve the listings in a spreadsheet. Then, publish it as a web app that you can share with other people.</p><p>You can use the <a href="https://dashdash.com/templates/find_jobs_on_github">Find jobs on GitHub</a> template to get started right away or follow this step-by-step guide to build it from scratch.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FXq2qtP9JsYU%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DXq2qtP9JsYU&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FXq2qtP9JsYU%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/816ffb6e9b0aaaf09cf8236bb6d64f40/href">https://medium.com/media/816ffb6e9b0aaaf09cf8236bb6d64f40/href</a></iframe><h3>What you’ll need</h3><ul><li>A <a href="https://app.dashdash.com/auth/get-early-access">dashdash account</a>. You’ll find more information at the end of the guide on how to get early access to the platform.</li></ul><h3>Connecting the API</h3><p>We’ll start by setting up the API request. We’ll be using the following parameters to perform our search:</p><ul><li><strong>description:</strong> A search term, such as “java” or “backend engineer”.</li><li><strong>location:</strong> A city name, zip code, or other location search term.</li><li><strong>full_time:</strong> If you want to limit results to full-time positions, set this parameter to “true”. Otherwise, you can leave it blank.</li><li><strong>page:</strong> Used for pagination as the API returns 50 listings at a time. By default, it’s set to “0” which retrieves the first page of results. You can set it to “1” for the second page, “2” for the third, and so on.</li></ul><p>All the parameters are optional and you can use any combination of them. We’ll list them down in column A.</p><blockquote><strong>A1:</strong> url<br><strong>A2:</strong> description<br><strong>A3:</strong> location<br><strong>A4:</strong> full-time<br><strong>A5:</strong> page</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*koo8RMctUq2VdqYzsGofig.png" /></figure><p>For “url”, we’ll copy and paste the API URL.</p><blockquote><strong>B1:</strong> <a href="https://jobs.github.com/positions.json">https://jobs.github.com/positions.json</a></blockquote><p>The API request is structured in the following way.</p><blockquote>[API URL]?[first parameter]=[value]&amp;[second parameter]=[value]&amp; …etc.</blockquote><p>That way, it corresponds to a request that looks like this.</p><blockquote><a href="https://jobs.github.com/positions.json?description=python&amp;location=new+york">https://jobs.github.com/positions.json?description=python&amp;location=new+york</a></blockquote><p>We’ll be using the GET function, which is the HTTP web request to retrieve data from the API, and structure the API call by adding the parameters and referencing their corresponding cells in which we’ll be adding the values.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X01Ou2kwvt_a5qVG65Gcvg.png" /></figure><blockquote><strong>D1:</strong> Get jobs<br><strong>E1:</strong> =GET(B1&amp;”?description=”&amp;B2&amp;”&amp;location=”&amp;B3&amp;”&amp;full_time=”&amp;B4&amp;”&amp;page=”&amp;B5)</blockquote><p>By executing E1, you will get a #SIZE! error because the API is trying to retrieve all the roles on GitHub Jobs as no search parameters are defined. We can execute our first request by defining the values to the parameters in column B. For example, if we enter “Full Stack” in B2 and “California” in B3, we’ll get a list of relevant roles from GitHub.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hPaI0DOd_jgjzjptWbFjLw.png" /></figure><blockquote><strong>B2:</strong> Full Stack<br><strong>B3:</strong> California</blockquote><p>By clicking on the {data} object, we can view the list of retrieved roles. In the next section, we’ll discuss how we can have them automatically displayed in the spreadsheet with every search.</p><h3>Displaying the Jobs</h3><p>dashdash offers three functions that allow you to display JSON data in table form easily; INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA.</p><ul><li><strong>INSERT_DATA:</strong> Displays the data in a table and keeps adding any new data to the same table in new rows with every function execution.</li><li><strong>OVERWRITE_DATA:</strong> Overwrites the table data with every new function execution.</li><li><strong>UPDATE_DATA:</strong> Takes an extra key parameter, which is usually a unique identifier (e.g., url). 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.</li></ul><p>We can use INSERT_DATA to display the retrieved job listings. All we have to do is define the data object (E1) and where we want to display the table (A8:8).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dYhRRAYtnfwelebRBBR4ZA.png" /></figure><blockquote><strong>D2:</strong> Fill table<br><strong>E2:</strong> =INSERT_DATA(E1,A8:8)</blockquote><p>When using INSERT_DATA, new job listings will be added to the table with every new search. If we would like to completely overwrite the table with each search, we can use OVERWRITE_DATA.</p><blockquote><strong>E2:</strong> =OVERWRITE_DATA(E1,A8:8)</blockquote><h3>Controlling the Function Execution</h3><p>Whenever any changes are made to any of the values of the parameters, the GET function automatically executes, and new results will be retrieved. However, if we want to control the function’s execution, we can use a button to do so.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KtT81M9bg0yALPi-hNuN9A.png" /></figure><p>To add a button, we need to select the cell where we want to add it (e.g. B6). Then, click on the action elements section, select “button”, and give it a label. We’ll give it the label “SEARCH”.</p><p>To link the button to the function, we need to use an IF function. The IF condition defines that if the button is pressed, the function is executed. Otherwise, we’ll show the text “Press the button”.</p><blockquote><strong>E1:</strong> =IF(B6=”SEARCH”,GET(B1&amp;”?description=”&amp;B2&amp;”&amp;location=”&amp;B3&amp;”&amp;full_time=”&amp;B4&amp;”&amp;page=”&amp;B5),”Press the button”)</blockquote><p>You might notice that E2 will return an error. That’s because until you press the button, E1 won’t be a data object anymore. We can use the IFERROR function to hide the error.</p><blockquote><strong>E2:</strong> =IFERROR(OVERWRITE_DATA(E1,A8:8),””)</blockquote><h3>Publishing as an App</h3><p>Once the spreadsheet is ready, we can go ahead and publish it as a web app.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rJ_pumqio-iJ1HbtoAjq5g.png" /></figure><p>All we have to do is define the cells where users can input the parameters values. To do that, we need to select the input cells B2:B5, click on the action elements section and select “Input field”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rbyNl4-SITXy8-I4OAD-rQ.png" /></figure><p>To publish the app, click on the “Publishing Settings” icon in the upper left corner next to “Preview.” Then, hit the “Publish” button.</p><p>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. We can view the published app version of the dashboard by pasting the link in the address bar.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0-77GXTQpGEK3It4OyDmrw.png" /></figure><p>The published app behaves in the same way as the spreadsheet. That way, you can share it with your other people.</p><h3>Make the Most out of dashdash!</h3><p>To take your app to the next level, you can leverage dashdash’s automation functions like the REFRESH function to automate the retrieval of new job listings. You can also combine it with other integrations like <a href="https://dashdash.com/integrations/slack">Slack</a> and <a href="https://dashdash.com/integrations/gmail">Gmail</a> to receive alerts of new jobs by Slack or email.</p><h3>Get Early Access!</h3><p>To get a dashdash account and start building your apps, you can reach out to me via:</p><ul><li><strong>Email:</strong> <a href="/service/mailto:hady@dashdash.com">hady@dashdash.com</a></li><li><strong>Twitter:</strong> <a href="https://twitter.com/HadyElHady93">@HadyElHady93</a></li><li><strong>Calendly:</strong> <a href="https://calendly.com/dashdash-onboardings/early-access">Book a call</a> so I can help you set up your dashboard and get started.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f7f34cd33967" width="1" height="1" alt=""><hr><p><a href="https://medium.com/rows/search-github-jobs-using-a-spreadsheet-f7f34cd33967">Search GitHub Jobs using a Spreadsheet</a> was originally published in <a href="https://medium.com/rows">Rows</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Custom Google Analytics Dashboard in a Spreadsheet]]></title>
            <link>https://medium.com/rows/how-to-create-a-custom-google-analytics-dashboard-in-a-spreadsheet-e1d309a35a3e?source=rss-adb9109052c9------2</link>
            <guid isPermaLink="false">https://medium.com/p/e1d309a35a3e</guid>
            <category><![CDATA[spreadsheets]]></category>
            <category><![CDATA[marketing]]></category>
            <category><![CDATA[no-code]]></category>
            <category><![CDATA[google-analytics]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Hady ElHady]]></dc:creator>
            <pubDate>Fri, 15 May 2020 11:36:31 GMT</pubDate>
            <atom:updated>2020-05-17T12:21:25.372Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rRc_RY43eZwSgowW0_D8VQ.png" /></figure><p>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.</p><p>You can use the <a href="https://dashdash.com/templates/create_google_analytics_dashboards">Create Google Analytics dashboards</a> template to get started right away or follow this step-by-step guide to build it from scratch.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FrIZEQTjAYsQ%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DrIZEQTjAYsQ&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FrIZEQTjAYsQ%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/6e3239c48d464e8ffa4576b77e7b1589/href">https://medium.com/media/6e3239c48d464e8ffa4576b77e7b1589/href</a></iframe><h3>What you’ll need</h3><ul><li>A <a href="https://app.dashdash.com/auth/get-early-access">dashdash account</a>. You’ll find more information at the end of the guide on how to get early access to the platform.</li><li>A <a href="http://analytics.google.com/">Google Analytics</a> account.</li></ul><h3>1. Connecting Google Analytics</h3><p>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.</p><h3>Connecting the Integration</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_BGcLxAdlvJRCP8fQkHIlQ.png" /></figure><p>To connect the Google Analytics integration, you need to:</p><ol><li>Head to the <a href="https://app.dashdash.com/dashboard/integrations/google_analytics">Google Analytics integration page</a> or open it inside the spreadsheet by clicking on the “Integrations” button in the upper right corner.</li><li>Click the “Connect” button. This will redirect you to the Google Analytics website.</li><li>Select the Google Analytics account you want to connect.</li><li>Click “Allow” to authorize access to the platform.</li></ol><h3>Getting the View ID</h3><h4>Retrieving the ID manually</h4><figure><img alt="Retrieving the Google Analytics view ID manually" src="https://cdn-images-1.medium.com/max/1024/1*bcxSC72NHb7GJ6GdgO_5vQ.png" /></figure><p>To retrieve your ID manually, you need to:</p><ol><li>Head to <a href="http://analytics.google.com/">Google Analytics</a>.</li><li>Select your desired analytics account, property, and view.</li><li>You will find the view ID under the view name. For example, in the screenshot below the ID is the highlighted “12345678”.</li></ol><h4>Retrieving the ID using dashdash</h4><figure><img alt="Retrieving the Google Analytics view ID using dashdash" src="https://cdn-images-1.medium.com/max/1024/1*5-9wF4vcyC_O3XlqtUB0aw.png" /></figure><p>Alternatively, you can use the LIST_ACCOUNTS_ANALYTICS_GOOGLE function to list down your Google Analytics accounts, properties, and views.</p><pre><strong>D1: </strong>Get ID<strong><br>E1:</strong> =LIST_ACCOUNTS_ANALYTICS_GOOGLE()</pre><p>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.</p><pre><strong>A1:</strong> ID<strong><br>B1:</strong> =PARSE(A1,”[‘items’][0].[‘webProperties’][0].[‘profiles’][0].[‘id’]”)</pre><p>Note that you need the view ID for the functions to work, not the account or property ID.</p><h3>2. Retrieving the Metrics</h3><h3>Analytics Functions</h3><p>dashdash offers three functions that allow you to retrieve data from Google Analytics.</p><ol><li><strong>PAGE_ANALYTICS_GOOGLE:</strong> Returns the Google Analytics page metrics of the website or a specific webpage including:</li></ol><ul><li>Page value (ga:pageValue)</li><li>Entrances (ga:entrances)</li><li>Entrance rate (ga:entranceRate)</li><li>Pageviews (ga:pageviews)</li><li>Pageviews per session (ga:pageviewsPerSession)</li><li>Unique pageviews (ga:uniquePageviews)</li><li>Time on page (ga:timeOnPage)</li><li>Average time on page (ga:avgTimeOnPage)</li><li>Exits (ga:exits)</li><li>Exit rate (ga:exitRate)</li></ul><p><strong>2. SESSION_ANALYTICS_GOOGLE:</strong> Returns the Google Analytics session metrics of the website or a specific webpage including:</p><ul><li>Users (ga:users)</li><li>New users (ga:newUsers)</li><li>% New Sessions (ga:percentNewSessions)</li><li>Sessions (ga:sessions)</li><li>Session duration (ga:sessionDuration)</li><li>Average session duration (ga:avgSessionDuration)</li><li>Sessions per user (ga:sessionsPerUser)</li><li>Bounces (ga:bounces)</li><li>Bounce rate (ga:bounceRate)</li><li>Hits (ga:hits)</li></ul><p><strong>3. CUSTOM_ANALYTICS_GOOGLE:</strong> 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.</p><p>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.</p><h3>Getting the Metrics</h3><p>The CUSTOM_ANALYTICS_GOOGLE function has three required parameters and five optional ones:</p><p><strong>Required:</strong></p><ul><li><strong>id:</strong> The view ID retrieved in the previous section.</li><li><strong>date_range:</strong> The date range we want to look up.</li><li><strong>metrics:</strong> The aggregated statistics for user activity to your website, such as clicks or pageviews.</li></ul><p><strong>Optional:</strong></p><ul><li><strong>page_path:</strong> Used to look up the metrics of a specific webpage by entering its relative path.</li><li><strong>dimensions:</strong> Breaks down the metrics by common criteria (e.g. country, browser).</li><li><strong>sort:</strong> A list of metrics and dimensions indicating the sorting order and sorting direction for the returned data.</li><li><strong>filters:</strong> Restricts the data retrieved by defined dimensions or metrics expressions.</li><li><strong>segment:</strong> Restricts the data by a specific segment of users and sessions.</li></ul><p>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.</p><pre><strong>E2:</strong> =CUSTOM_ANALYTICS_GOOGLE(“12345678”,”[2020–04–01;2020–04–07]”,”ga:pageviews,ga:sessions”)</pre><figure><img alt="The Google Analytics Metrics &amp; Dimensions Explorer" src="https://cdn-images-1.medium.com/max/1024/1*lYI2RdCaqsPGrigz4lUw0g.png" /></figure><p>Check out the <a href="https://ga-dev-tools.appspot.com/dimensions-metrics-explorer/">Google Analytics Metrics &amp; Dimensions Explorer</a> for a full list of available metrics and dimensions.</p><p>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.</p><figure><img alt="Google Analytics metrics retrieved" src="https://cdn-images-1.medium.com/max/1024/1*tP9JsTY4jBsN_bstNj4i9w.png" /></figure><pre><strong>A2:</strong> Date range<br><strong>A3:</strong> Metrics<br><strong>A4:</strong> Page path<br><strong>A5:</strong> Dimensions<br><strong>A6:</strong> Sort<br><strong>A7:</strong> Filters<br><strong>A8:</strong> Segment<br><strong>E2:</strong> =CUSTOM_ANALYTICS_GOOGLE(B1,B2,B3,B4,B5,B6,B7,B8)</pre><p>To recreate the previous request, we would just have to update the respective cells.</p><pre><strong>B2:</strong> [2020–04–01;2020–04–07]<br><strong>B3:</strong> ga:pageviews,ga:sessions</pre><figure><img alt="Retrieved Google Analytics metrics" src="https://cdn-images-1.medium.com/max/1024/1*S6VVjtEgUttE_MD_2fkCRg.png" /></figure><p>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.</p><figure><img alt="Google Analytics pageviews" src="https://cdn-images-1.medium.com/max/1024/1*6hHtSiIXp8Z6FrvNt7SUJQ.png" /></figure><pre><strong>E3:</strong> =PARSE(E2,”[‘totalsForAllResults’].[‘ga:pageviews’]”)</pre><p>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.</p><pre><strong>B5:</strong> ga:deviceCategory</pre><figure><img alt="The values of the Google Analytics metrics are segmented by the dimension values" src="https://cdn-images-1.medium.com/max/521/1*tcLCwqXDY-rzjJ8-j4a7oQ.png" /></figure><p>You can still find the aggregate values of the metrics in the totalsForAllResults section. This includes the values for all retrieved device categories combined.</p><p>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.</p><p>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.</p><p>In the next section, we’ll discuss the functions dashdash offers to automate the process of displaying the retrieved tables.</p><h3>3. Creating the Dashboard</h3><h3>Displaying the Metrics</h3><p>dashdash offers three functions that allow you to display JSON data in table form easily; INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA.</p><ul><li>I<strong>NSERT_DATA:</strong> Displays the data in a table and keeps adding any new data to the same table in new rows with every function execution.</li><li><strong>OVERWRITE_DATA:</strong> Overwrites the table data with every new function execution.</li><li><strong>UPDATE_DATA:</strong> 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.</li></ul><figure><img alt="Google Analytics dashdash Recommended Tables" src="https://cdn-images-1.medium.com/max/1024/1*ZHt57ACMv0SQZBredHJe6A.png" /></figure><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K4hTHW8cOK635gOPc_tYwg.png" /></figure><p>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.</p><figure><img alt="Displaying the Google Analytics report in a dashdash Spreadsheet" src="https://cdn-images-1.medium.com/max/1024/1*MrD4VWGzZMsEg44hzYpjpg.png" /></figure><p>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.</p><pre><strong>D3:</strong> Fill table<strong><br>E3:</strong> =OVERWRITE_DATA(PARSE(E2,”[‘rows’]”),A10:10)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iuTrvcZtiW0pdErXX4E5EA.png" /></figure><p>Now, if you make any changes to the parameters, the table will be updated automatically.</p><h3>4. Publishing the Dashboard</h3><p>Once you’ve created our desired dashboard, you can publish it as a web app. That way, you can share it with other people.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dD3w7k6R_bxAXRHcAC9urQ.png" /></figure><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-p95ZefOnCLlKAEwn61V1w.png" /></figure><p>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.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zo_ADf-or2fHPHU2hFjlgg.png" /></figure><p>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.</p><p>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.</p><h3>Make the Most out of dashdash!</h3><p>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 <a href="https://dashdash.com/integrations/slack">Slack</a> and <a href="https://dashdash.com/integrations/gmail">Gmail</a> to receive daily or weekly reports of your website performance by Slack or email.</p><p>You can get started with pre-built templates for these use cases:</p><ul><li><a href="https://dashdash.com/templates/get_google_analytics_slack_reports">Get Google Analytics Slack reports</a></li><li><a href="https://dashdash.com/templates/get_google_analytics_email_reports">Get Google Analytics email reports</a></li></ul><p>Also, make sure to check out our forum post on <a href="https://forum.dashdash.com/t/how-to-use-google-analytics-in-dashdash-like-a-pro/513">how to use Google Analytics in dashdash like a pro</a> to learn more about the integration and how you can leverage its full power.</p><h3>Get Early Access!</h3><p>To get a dashdash account and start building your custom Google Analytics dashboard, you can reach out to me via:</p><ul><li><strong>Email:</strong> <a href="/service/mailto:hady@dashdash.com">hady@dashdash.com</a></li><li><strong>Twitter:</strong> <a href="https://twitter.com/HadyElHady93">@HadyElHady93</a></li><li><strong>Calendly:</strong> <a href="https://calendly.com/dashdash-onboardings/early-access">Book a call</a> so I can help you set up your dashboard and get started.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e1d309a35a3e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/rows/how-to-create-a-custom-google-analytics-dashboard-in-a-spreadsheet-e1d309a35a3e">How to Create a Custom Google Analytics Dashboard in a Spreadsheet</a> was originally published in <a href="https://medium.com/rows">Rows</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>