{"id":5575,"date":"2019-05-12T17:11:43","date_gmt":"2019-05-12T17:11:43","guid":{"rendered":"https:\/\/max-drake.cc\/?p=5575"},"modified":"2019-05-13T12:01:13","modified_gmt":"2019-05-13T12:01:13","slug":"google-sheets-google-analytics-add-in-to-create-dashboards","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=5575","title":{"rendered":"Google Sheets Google Analytics add-in to create dashboards"},"content":{"rendered":"\n<iframe seamless=\"\" scrolling=\"no\" data-src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=363606754&amp;format=interactive\" width=\"600\" height=\"371\" frameborder=\"0\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=577301364&amp;format=image\" alt=\"\"\/><\/figure>\n\n\n\n<p>I was looking at the GlideApps showcase and the Bogdan Popa created a <strong><a rel=\"noreferrer noopener\" aria-label=\"News App (opens in a new tab)\" href=\"https:\/\/spectrum.chat\/glideapps\/app-showcase\/news-app~dcf857df-434d-424e-8761-aedebfe5ee54\" target=\"_blank\">News App<\/a><\/strong>.  In it he uses  <code>IMPORTXML<\/code> in Google Sheets to pull in web data that he filters to different tables. A nice app. I started thinking about what other import formulas does Google Sheets have, I know it has GOOGLEFINANCE for share data is one &amp; I&#8217;ve just found out about GOOGLETRANSLATE too. I still haven&#8217;t set up an app that utilises this as a formula directly, but have some ideas on a use. <\/p>\n\n\n\n<p>I came across this article about <strong><a rel=\"noreferrer noopener\" aria-label=\"Google Analytics Spreadsheet Add-on (opens in a new tab)\" href=\"https:\/\/developers.google.com\/analytics\/solutions\/google-analytics-spreadsheet-add-on\" target=\"_blank\">Google Analytics Spreadsheet Add-on<\/a><\/strong>. This is something of an issue with me, Google Analytics has heaps of stuff in it but I never seem to get it in a format that I want. <\/p>\n\n\n\n<p>The Sheet add in looks like a good way to set up a dashboard to display information. In the article there are 2 videos, a basic use of the tool and then a dashboard is built. Both worth watching. I will look to try and replicate the dashboard in the 2nd video and embed the data about my website on a page or post on the website.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Results- Some above<\/h3>\n\n\n\n<p>After following the tutorial video these are the charts I ended up creating. There are a couple at the front of the post as I couldn&#8217;t resist putting them in a prominent position. <\/p>\n\n\n\n<p>I did a number of sessions chart  and one for users as some users go through a lot of pages, others just view one and bounce. <\/p>\n\n\n\n<p>Sessions\/user this week are not looking so good so far compared to the previous week. <\/p>\n\n\n\n<iframe seamless=\"\" scrolling=\"no\" data-src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=577301364&amp;format=interactive\" width=\"600\" height=\"371\" frameborder=\"0\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n\n\n\n<iframe seamless=\"\" scrolling=\"no\" data-src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=873168338&amp;format=interactive\" width=\"600\" height=\"371\" frameborder=\"0\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n\n\n\n<p>It seems that the US is the main country for users, with Italy 2nd. I wonder if this is because of the OpenMaint (from Italy), The OpenMaint seems to be the most popular part of the site.<\/p>\n\n\n\n<iframe seamless=\"\" scrolling=\"no\" data-src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=1252639634&amp;format=interactive\" width=\"600\" height=\"371\" frameborder=\"0\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n\n\n\n<p>Chrome seems to be the most popular browser, IE 2nd, which is interesting, this infers that a lot of views are from companies that have IE as main browser (inferring older corporate IT systems), with the beloved Firefox 3rd. <br><\/p>\n\n\n\n<iframe seamless=\"\" scrolling=\"no\" data-src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=1062844306&amp;format=interactive\" width=\"600\" height=\"371\" frameborder=\"0\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n\n\n\n<p>I know my session duration&#8217;s have been dropping. I may have to look at the yearly ones and see if its a seasonal thing. <\/p>\n\n\n\n<iframe seamless=\"\" scrolling=\"no\" data-src=\"https:\/\/docs.google.com\/spreadsheets\/d\/e\/2PACX-1vTBh_uyhngTlDrwVB48zA0HeoGaf1oB4dcRCjnR-pW5eWIIUDRzh22qvpZGPiCKxBok_Eevpo0dAhjV\/pubchart?oid=1858124427&amp;format=interactive\" width=\"600\" height=\"371\" frameborder=\"0\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe>\n\n\n\n<h4 class=\"wp-block-heading\">Scheduling updates\/refresh of data<\/h4>\n\n\n\n<p>I have scheduled daily updates between 4-5am so that the charts are refreshed. <\/p>\n\n\n\n<p>\nOn GlideApps they do not like iFrames, so I wonder if I can update a \nchart and get an image from it to save to the same location (Apps \nScript) so that i can upload latest image which will reflect daily \nupdate. \n\n<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">End comment<\/h3>\n\n\n\n<p>I am pleased with the result. I think I&#8217;ll have to do a few more of these to drill down to find information that I may be able to understand. <\/p>\n\n\n\n<p>Now that I have a controllable process I&#8217;ll look at the metrics I can get and do a few reports. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was looking at the GlideApps showcase and the Bogdan Popa created a News App. In it he uses IMPORTXML in Google Sheets to pull in web data that he filters to different tables. A nice app. I started thinking about what other import formulas does Google Sheets have, I know it has GOOGLEFINANCE for [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5576,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,204,40,32,265,12,29,27],"tags":[156,255,440,439],"class_list":["post-5575","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api_json","category-automation","category-data-analysis","category-power-bi","category-google-docs","category-visualisation","category-web","category-wordpress","tag-google-analytics","tag-google-sheets","tag-visualise-web-performance","tag-web-performance-analytics"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/05\/2019-05-13-17_06_11-Add-New-Post-\u2039-Vast-\u2014-WordPress.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/05\/2019-05-13-17_06_11-Add-New-Post-\u2039-Vast-\u2014-WordPress.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5575"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/5575\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/5576"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}