System Admin

Get a Google prompt on your phone to sign in

Google Prompt is a new 2-step verification option that improves the usability of the second verification step by pushing a prompt to a connected smartphone that you just need to tap on to allow or deny the request.2-step verification, often also called 2-factor authentication, is a popular security option to improve the sign-in security of user accounts.As the name implies, instead of just having to enter username and password to sign-in, a second form of verification is required to complete the process.This second step requires access to a local device usually, a smartphone or authentication device, and stops many popular attack forms, phishing or password stealing dead in their tracks.Google was not the first company to offer 2-step verification, but the option to use it has been available for years.The two common methods of verification are to receive a text or voice message to a linked mobile phone, or to use the company's official authenticator application.Both require that you enter the code manually whenever sign-in requires you to do so.Google Prompt The new Google Prompt option improves the process as it does away with having to enter the code manually. It requires a smartphone for that though, and is only available on Android or iOS.Setting up 2-Step VerificationDo the following to set up Google Prompt as an option when it comes to the second verification step when you are signing in to a Google Account.Step 1: Visit the Sign-In & Security page on the Google My Account website.Step 2: Click on 2-Step Verification on the page.Step 3: You are asked to enter the password of the Google account in question if 2-Step Verification is already enabled. If that is the case, proceed to "configuring Google Prompt" below.If not, proceed with step 4 and you won't be asked to enter the account password at that point.Step 4:If you have not set up 2-Step Verification yet, do that. This requires that you add a phone number to your Google Account. Click on the Get Started link.Step 5: Enter your Google Account password for verification.Step 6: Select the country flag the phone is registered in, and enter the phone number afterwards.Step 7: Google sends a verification code or calls using the phone number you have entered. You need to enter that verification code as confirmation on the page to proceed. There is an option to resend it should if required.Step 8: Click on the turn on link to complete the process.Configuring Google Prompt Step 1: Select the Google Prompt option under "set up alternative second step" and click on "add phone" underneath it.Step 2: An overlay explains what Google Prompt is: Get a Google prompt to sign in. Instead of typing verification codes, get a prompt on your phone and just tap Yes to sign in.Click on Get Started to start the setup.Step 7: Google tries to find a compatible phone automatically. If that is the case, select the phone and proceed.If that fails however, select "set up your Android phone" or "set up your iPhone (5S or later).On Android, it requires that you add the Google account as an account first on the device, and then retry linking the device to Google Prompt.This completes the setup, and you will receive push prompts on the device afterwards when you sign in and need to complete a second verification step.Google suggests to use a phone with an active lock screen and that Security Keys cannot be used simultaneously with the new feature.Closing Words Google Prompt is one of several apps or services that improves the second step of verification during sign-in. Microsoft launched Microsoft Account recently, an application for Android that does the same thing.Ghacks needs you. You can find out how to support us here or support the site directly by becoming a Patreon. Thank you for being a Ghacks reader.The post Get a Google prompt on your phone to sign in appeared first on gHacks Technology News.
  • 3 min read
  • Jun 29, 2016
Mac & Apple

Alternatives to Apple Numbers

Hot Topics iPhone SE first look Apple's 21 March iPhone SE launch iPad Air 3 preview Funny things to ask Siri iPhone 7 New 21in iMacAlternatives to Numbers Apple's Numbers is a capable spreadsheet, but it's not the only one. Take a look at the alternatives.byCliff Joseph| 24 Mar 16AdvertisementApple Numbers 3.6.1Microsoft Excel 2016Google DocsLibreOfficeOpenOffice CalcPanorama Sheets 6.0SmartsheetSoulver 2.4More storiesNext PrevApple Numbers 3.6.1Price: Free with new Macs (£14.99 upgrade for existing users)More info: The App StoreNumber-Crunching: Numbers is a good example of how the best Apple software takes difficult tasks and makes them look easy. As soon as you type ‘=’ into a cell, the Inspector palette on the right-hand side of the spreadsheet displays a list of available functions, and to help new users get started it even displays explanations and examples of how to use each function.Numbers doesn’t have the sheer range of functions that you’ll find in Excel, or support for advanced features such as pivot tables, but it provides a good basic set of functions that will be useful for home users, education and small businesses. And, of course, Numbers also allows you to import and export spreadsheets in Excel format if you need to.Graphs And Charts: Apple describes numbers as ‘the most beautiful spreadsheet ever’, and it does put a lot of emphasis on its graphics tools. A Numbers spreadsheet is really just a blank page where you can place data tables, text and graphics in any layout that you choose. The program includes tools for quickly creating 2D and 3D graphs and charts, and even interactive charts that can include simple animations to illustrate changes in data.Collaboration: You can share your spreadsheets online with other people by using the online version of Numbers at www.icloud.com, and the online program can warn you if there are conflicts between changes made by different people. However, you can’t track changes made to a spreadsheet, and Numbers lacks the more comprehensive collaboration tools found in Google Sheets and Microsoft’s Office Online.Pros: Free with new Macs, lots of help for new users, simple tools for creating charts and attractive layoutsCons: Fewer functions and analytical tools than Excel, limited collaboration toolsYou can read all our iWork reviews here: Apple Pages, Keynote & Numbers reviewsRead:Alternatives to NumbersAlternatives to PagesAlternatives to KeynoteNext »AdvertisementNext Prev Price: Free with new Macs (£14.99 upgrade for existing users)More info: The App StoreNumber-Crunching: Numbers is a good example of how the best Apple software takes difficult tasks and makes them look easy. As soon as you type ‘=’ into a cell, the Inspector palette on the right-hand side of the spreadsheet displays a list of available functions, and to help new users get started it even displays explanations and examples of how to use each function.Numbers doesn’t have the sheer range of functions that you’ll find in Excel, or support for advanced features such as pivot tables, but it provides a good basic set of functions that will be useful for home users, education and small businesses. And, of course, Numbers also allows you to import and export spreadsheets in Excel format if you need to.Graphs And Charts: Apple describes numbers as ‘the most beautiful spreadsheet ever’, and it does put a lot of emphasis on its graphics tools. A Numbers spreadsheet is really just a blank page where you can place data tables, text and graphics in any layout that you choose. The program includes tools for quickly creating 2D and 3D graphs and charts, and even interactive charts that can include simple animations to illustrate changes in data.Collaboration: You can share your spreadsheets online with other people by using the online version of Numbers at www.icloud.com, and the online program can warn you if there are conflicts between changes made by different people. However, you can’t track changes made to a spreadsheet, and Numbers lacks the more comprehensive collaboration tools found in Google Sheets and Microsoft’s Office Online.Pros: Free with new Macs, lots of help for new users, simple tools for creating charts and attractive layoutsCons: Fewer functions and analytical tools than Excel, limited collaboration toolsYou can read all our iWork reviews here: Apple Pages, Keynote & Numbers reviewsRead:Alternatives to NumbersAlternatives to PagesAlternatives to KeynoteMicrosoft Excel 2016Price: Subscription – from £5.99 per month; MS Office 2016 – from £119.99More info: office.microsoft.comNumber-Crunching: Excel is the big-daddy of spreadsheets, used by businesses all over the world and with hundreds of functions and features crammed into its Ribbon toolbar.The sheer range of features built into Excel can seem daunting, but it includes dozens of templates to help you get started, including spreadsheets for home and personal use, business budgets, time-tracking and invoicing. The Formula tab on the Ribbon includes a pull-down menu that quickly lists standard functions, along with a Formula Builder for creating your own functions and formulae. A second tab on the Ribbon provides more advanced tools such as pivot tables and linking to external data sources such as Filemaker or corporate databases, and even HTML web pages.Graphs And Charts: Even Numbers has to tip its hat to the sheer variety of graphs and charts included in Excel. The Charts tab on the Ribbon includes standard options such as bar, area, pie and scatter charts. However, each type of chart also has a separate pull-down menu that includes many additional variations, such as 3D charts, exploded pie charts, or stacked area charts. Excel also includes a special type of chart, called Sparklines, that can help to highlight trends within dense collections of data.Collaboration: There are plenty of ways to collaborate with Excel. The Review tab on the Ribbon allows you to track changes made to a spreadsheet, and to control the level of access that you provide to other users. Larger organisations can set up their own servers for in-house collaboration using Microsoft’s SharePoint software, but you can also upload documents to the OneDrive cloud storage service, or use Microsoft’s Office Online suite of web apps.Pros: Unrivalled range of spreadsheet features and functions, comprehensive collaboration tools, attractive graphing toolsCons: Expensive, complicated for beginnersRead: Microsoft Office for Mac 2014 release dateGoogle DocsPrice: FreeMore info: docs.google.comNumber-Crunching: The latest version of Google Docs includes a number of new features, including many additional functions and new ‘filter views’ that allow you to hide certain data in order to focus on just the key data within the spreadsheet. There’s a handy ‘quicksum’ feature that automatically works out the total of a set of selected cells, along with a ‘range-selection’ mode that helps you to quickly select a set of cells for your formula. However, Google Docs doesn’t provide as much help for newcomers as Pages, so you do need to be familiar with spreadsheet work and to know the basic functions that you’re likely to need.Graphs And Charts: Google Docs doesn’t have the eye-catching 3D graphs and charts of Numbers, but it does include a quick and easy Chart Editor. This can create a variety of common charts, including bar charts and pie and scatter charts. There are some useful extras here too, such as the ability to superimpose charts over a map, or to create flowcharts and org charts.Collaboration: Online collaboration is a strong point with all of Google’s online apps – although it helps if the people you’re sharing with also have Google accounts of their own. You can display your spreadsheet publicly on the web, just send email invitations to specific people, or set up a Google Group for regular collaborators. You can add comments to your spreadsheet, and there’s a Revision History option that lets you view previous versions of your spreadsheet.Pros: Free, good features for sharing and collaborationCons: Full collaboration features require a Google account, not ideal for beginnersLibreOfficePrice: Free – donations encouragedMore info: libreoffice.orgNumber-Crunching: It’s no coincidence that the LibreOffice spreadsheet is called Calc – just like that of OpenOffice – as both software suites share the same open-source roots. Not surprisingly, the two programs also share many of the same features and functions, as well as similar formatting palettes and toolbar layouts.Calc’s main toolbar includes options for quickly sorting data in cells, SUM calculations, and correcting decimal point placing. For more advanced work there’s a Function Wizard that quickly lists the entire range of available functions, and also helps you to create and structure more complex functions and formulae of your own.Graphs And Charts: The Chart Wizard in LibreOffice Calc is almost identical to the one found in OpenOffice. It includes a good selection of two-dimensional bar, column, pie and scatter charts, along with a ‘3D Look’ option that allows you to add simple 3D perspective effects. You can also liven up your charts by applying transparency or graduated tint effects, and if things get a bit untidy you can just hit the Reset button to revert to one of the standard chart types.Collaboration: Like OpenOffice, LibreOffice focuses more on offline collaboration. You can send copies of your spreadsheets to other people, and each user can record the changes they make within their own copy. There’s an option for comparing different versions of a document, and if you approve the changes that have been made you can import those changes and merge them into your own copy of the spreadsheet. However, you can’t collaborate online with other people all working on the same version of a document.Pros: Free, Function Wizard assists with complex functions, simple chart toolsCons: No online collaboration, few features to differentiate it from OpenOfficeApache OpenOffice CalcPrice: Free – donations encouragedMore info: www.openoffice.orgNumber-Crunching: Calc is the spreadsheet program within the free OpenOffice suite. It looks a bit like a mash-up of Numbers and Microsoft Excel, as there’s a large ‘side-deck’ palette on the right-hand side of the screen that is similar to the Inspector palette in Numbers, along with a densely populated toolbar like the Excel ribbon that runs across the top of each document. That cluttered interface might be a bit intimidating for new users but, like Numbers, it does help you out by providing information about all the available functions in the side-deck palette. One useful feature is the Detective option, which helps you to work with complex formulae by showing the relationships linking data cells and formulae.Graphs And Charts: The graphs and charts in Calc aren’t as pretty as those of Numbers, but the program’s Chart Wizard guides you easily through the process of selecting a chart type, selecting data and then modifying elements such as titles and labels for the X and Y axis. The Wizard includes the usual bar, pie, line and scatter charts, and there are options that let you add transparency and simple 3D perspective effects to your charts.Collaboration: Calc does include a review function that allows it to record changes made to a document by different people. However, each person needs to work on their own separate copy of the spreadsheet, using their own copy of OpenOffice. There’s no option for simultaneous online collaboration as there is with Numbers and some of its other rivals.Pros: Free, extensive set of functions, simple Chart WizardCons: Cluttered interface, limited collaboration optionsPanorama Sheets 6.0Price: £29.99More info: The App StoreNumber-Crunching: Developer ProVue is well-known for its powerful Panorama database, and Panorama Sheets is a simpler version designed for home users and small businesses. Like its big brother, Panorama Sheets presents database information in a spreadsheet format that makes it easy to quickly browse, search and sort through your data.You start work by defining the fields in your database, such as the names and addresses of your customers, or the members of a local sports club. Panorama Sheets then presents you with a blank spreadsheet into which you can insert the necessary data. You can create fields that perform calculations, and the program does include a wide range of mathematical, scientific and financial functions similar to those you might find in a conventional spreadsheet. And, like any database it also allows you search and sort information quickly, perhaps locating customers who share the same post-code, or club members who haven’t paid their subscription fees.This combination of database and spreadsheet features can be a little confusing if you’re not already familiar with database concepts, but it’s one of the better options for simpler database work since FileMaker scrapped Bento last year.Graphs And Charts: Panorama Sheets is very efficient at storing and sorting your data, but it’s pretty basic from a graphical point of view. There are no options for creating graphs and charts, and it lacks the attractive graphics and layout tools that Bento used to provide.Collaboration: Panorama Sheets is very much a single-user product, but it does provide the option of upgrading to other products in the Panorama range as your business grows, including Panorama Server for sharing data across an organization.Pros: Simple database program that presents data in spreadsheet format, wide range of functions and sorting toolsCons: Emphasis on database features, limited graphics and layout features, complex for beginners.SmartsheetPrice: Subscription £7 per user, per month:£84 per annumMore info: www.smartsheet.comNumber-Crunching: At first glance, Smartsheet might look like a fairly conventional spreadsheet, but it’s actually a collaborative project management tool. Its designers decided to use the columns-and-rows format of a spreadsheet simply because most people already understand how spreadsheets work. Smartsheet documents – known as ‘sheets’ – can be used to store information, such as a series of tasks, dates and names of the people involved, and to share that information online with your colleagues.It doesn’t provide the same range of mathematical functions that you’d find in dedicated spreadsheet, but Smartsheet does allow you to attach additional files to individual rows within a sheet, including wordprocessor documents, PDF files, and more detailed spreadsheets if required.Graphs And Charts: Smartsheet isn’t a conventional spreadsheet, so it doesn’t provide tools for converting data into graphs and charts like rivals such as Excel or Numbers. However, it can be used to create ‘Gantt charts’ that are used in project management, and you can attach proper spreadsheets and charts to rows along with other types of information.Collaboration: Smartsheet provides plenty of options for collaborating with colleagues. The creator of a sheet document can publish it openly on the web, or simply send invitations to specific colleagues. You can also decide whether to share the entire sheet, or just a set of files attached to one particular row. Pricing depends on the size of your organization, with subscription prices starting at £84 per year for a single-user license that allows you to collaborate with an unlimited number of colleagues. There’s also a free iOS app that you can use as well.Pros: Versatile project-management tool based around familiar spreadsheet concepts, designed for collaborationCons: Requires a subscription, lacks the number-crunching power of a true spreadsheetSoulver 2.4Price: £8.99More info: The App StoreNumber-Crunching: Soulver isn’t a true spreadsheet program like Numbers or Excel – in fact, it doesn’t even use cells like a spreadsheet at all. However, it’s a handy tool for performing simple calculations that don’t require the complexity of a spreadsheet.A Soulver document just looks like a blank page that is divided into two columns. The left-hand column allows you to type simple notes and calculations – such as ‘3 nights at £50 per night’, or ‘£50 in US dollars’ – and the answer will then appear in the right-hand column. It doesn’t have the range of functions that you’d find in a proper spreadsheet, but Soulver can perform a number of trigonometry calculations and allows you to create variables or use operators such as brackets within your calculations. Soulver can also track share prices – though it needs an Internet connection for updates – and can convert currencies, distances and weights, so it’s a good alternative to a basic calculator. The Mac version is a little pricey at £7.99, but there’s a free trial available and there’s also an iOS version that costs just £2.29.Graphs And Charts: Soulver has some simple formatting tools that allow you to highlight different elements within a calculation, but there are no tools for converting data into graphs or charts.Collaboration: You can save Soulver documents as PDF, CSV, HTML or text documents, which might be handy for sharing your notes with other people. However, Soulver is really just a personal calculator and doesn’t provide any options for collaborating on documents with others.Pros: Good for quick calculations, tracks shares, converts currencies, weights and distancesCons: Mac version is expensive, no charts or collaboration featuresCommentsPlease enable JavaScript to view commentsHow to watch Microsoft Build 2016 live stream - Build 2016 keynote live blog: Watch live as Satya N6...…1995-2015: How technology has changed the world in 20 yearsBest free fonts: 10 free typefaces every designer should haveLearn how to code in Java on a Mac: Set up a Java programming environment in Mac OS X and get…Follow Macworld on Twitter Follow Macworld on Facebook
  • 10 min read
  • Mar 30, 2016
Internet Marketing

The Google Analytics Add-On for Sheets: An Intro to an Underutilized Tool

Posted by tian_wang [Estimated read time: 13 minutes]With today’s blog post I’m sharing everything one needs to know about an underappreciated tool: the Google Analytics add-on for Google Sheets. In this post I’ll be covering the following:1. What is the Google Analytics add-on?2. How to install and set up the Google Analytics add-on.3. How to create a custom report with the Google Analytics add-on.4. A step-by-step worked example of setting up an automated report.5. Further considerations and pitfalls to avoid.Thanks to Moz for having me, and for giving me the chance to write about this simple and powerful tool!1. What is the Google Analytics add-on and why should I care?I’m glad I asked. Simply put, the Google Analytics add-on is an extension for Google Sheets that allows you to create custom reports within Sheets. The add-on works by linking up to an existing Analytics account, using Google’s Analytics API and Regular Expressions to filter the data you want to pull, and finally gathering the data into an easy and intuitive format that’s ripe for reporting.The Google Analytics add-on’s real value-add to a reporting workflow is that it’s extremely flexible, reliable, and a real time-saver. Your reporting will still be constrained by the limitations of Sheets itself (as compared to, say, Excel), but the Sheets framework has served almost every reporting need I’ve come across to date and the same will probably be true for most of you!In a nutshell, the Add-On allows you to:Pull any data that you’d be able to access in the Analytics API (i.e analytics.google.com) directly into a spreadsheet Easily compare historical data across time periods Filter and segment your data Automate regular reporting Make tweaks to existing reports to get new data (no more re-inventing wheels!) If this all sounds like you could use it, read on!2. Getting started: How to install and set up the Google Analytics add-on2A. Installing the Google Analytics add-onGo into Google Sheets. On the header bar, under your Workbook’s title, click add-on. This opens a drop-down menu — click “Get add-ons.” In the following window, type “Google Analytics" into the search bar on the top right and hit enter.The first result is the add-on we want, so go ahead and install it.Refresh your page and confirm the add-on is installed by clicking “Add-ons” again. You should see an option for “Google Analytics.”That’s all there is to installation!2B. Setting up the Google Analytics add-onNow that we have the Google Analytics add-on installed, we need to set it up by linking it to an Analytics account before we can use it.Under the “Add-ons” tab in Sheets, hover “Google Analytics” to expose a side-bar as shown below.Click “Create New Report.” You’ll see a menu appear on the right side of your screen.In this menu, set the account information to the Analytics account you want to measure. Fill out the metrics and dimensions you want to analyze. You can further customize segmentation within the report itself later, so just choose a simple set for now. Click “Create Report.” The output will be a new sheet, with a report configuration that looks like this:Note: This is NOT your report. This is the setup configuration for you to let the add-on know exactly what information you’d like to see in the report. Once you’ve arrived at this step, your set-up phase is done!Next we’ll look at what these parameters mean, and how to customize them to tailor the data you receive.3. Creating a custom report with the Google Analytics add-on So now you have all these weird boxes and you’re probably wondering what you need to fill out and what you don’t.Before we get into that, let’s take a look at what happens if you don’t fill out anything additional, and just run the report from here.To run a configured report, click back into the “Add-Ons” menu and go to Google Analytics. From there, click “Run Reports.” Make sure you have your configuration sheet open when you do this!You’ll get a notification that the report was either successfully created, or that something went wrong (this might require some troubleshooting).Following the example above, your output will look something like this:This is your actual report. Hooray! So what are we actually seeing? Let’s go back to the “Report Configuration” sheet to find out.The report configuration:Type and View ID are defaults that don’t need to be changed. Report Name is what you want your report to be called, and will be the name generated for the report sheet created when you run your reports. So really, in the report configuration above, all the input we’re seeing is:Last N Days = 7 Metrics = ga:users In other words, this report shows the total number of sessions in the specified View ID over the last week. Interesting maybe, but not that helpful. Let’s see what happens if we make a few changes.I’ve changed Last N Days from 7 to 30, and added Date as a Dimension. Running the report again yields the following output:By increasing the range of data pulled from last 7 to 30 days, we get a data from a larger set of days. By adding date as a dimension, we can see how much traffic the site registered each day.This is only scratching the surface of what the Google Analytics add-on can do. Here’s a breakdown of the parameters, and how to use them:Parameter NameRequired?Description & NotesExample Value(s)Report NameNoThe name of your report. This will be the name of the report sheet that's generated when you run reports. If you’re running multiple reports, and want to exclude one without deleting its configuration setup, delete the report name and the column will be ignored next time you run your reports.“January Organic Traffic”TypeNoInputs are either “core” or “mcf,” representative of Google’s Core Reporting API and Multi-Channel Funnels API respectively. Core is the default and will serve most of your needs!“core”/“mcf”View (Profile) IDYesThe Analytics view that your report will pull data from. You can find your view ID in the Analytics interface, under the Admin tab.ga:12345678Start / End DateNoUsed alternatively with Last N Days (i.e you must use exactly one), allows you to specify a range of data to pull from.2/1/2016 – 2/31/2016Last N DaysNoUsed alternatively with Start / End Date (i.e you must use exactly one), pulls data from the last N days from the current date. Counts backwards from the current date.Any integerMetricsYesMetrics you want to pull. You can include multiple metrics per report. Documentation on Metrics and dimensions can be found in Google’s Metrics & Dimensions Explorer“ga:sessions”DimensionsNoDimensions you want your metrics to be segmented by. You can include multiple dimensions per report. Documentation on metrics and dimensions can be found here.“ga:date”SortNoSpecifies an order to return your data by, can be used to organize data before generating a report. Note: you can only sort by metrics/dimensions that are included in your report.“sort=ga:browser, ga:country”FiltersNoFilter the data included in your report based on any dimension (not just those included in the report).“ga:country==Japan; ga:sessions>5”SegmentNoUse segments from the main reporting interface.“users::condition:: ga:browser==Chrome”Sampling LevelNoDirects the level of sampling for the data you’re pulling. Analytics samples data by default, but the add-on can increase the precision of sampling usage.“HIGHER_PRECISION”Start IndexNoShows results starting from the current index (default = 1, not 0). For use with Max Results, when you want to retrieve paginated data (e.g if you’re pulling 2,000 results, and want to get results 1,001 – 2,000).IntegerMax ResultsNoDefault is 1,000, can be raised to 10,000.Integer up to 10,000Spreadsheet URLNoSends your data to another spreadsheet.URL for sheet where you want data to be sentBy using these parameters in concert, you can arrive at a customized report detailing exactly what you want. The best part is, once you’ve set up a report in your configuration sheet and confirmed the output is what you want, all you have to do to run it again is run your reports in the add-on! This makes regular reporting a breeze, while still bringing all the benefits of Sheets to bear.Some important things to note and consider, when you’re setting up your configuration sheet:You can include multiple report configurations in the the sheet (see below):In the image above, running the report configuration will produce four separate reports. You should NOT have one configuration sheet per report.Although you can have your reports generated in the same workbook as your configuration sheet, I recommend copying the data into another workbook or using the Spreadsheet URL parameter to do the same thing. Loading multiple reports in one workbook can create performance problems. You can schedule your reporting to run automatically by enabling scheduled reporting within the Google Analytics add-on. Note: this is only helpful if you are using “Last N Days” for your time parameter. If you’re using a date range, your report will just give you the same data for that range every month.The regularity options are hourly, daily, weekly, and monthly.4. Creating an automated report: A worked exampleSo now that we’ve installed, set up, and configured a report, next up is the big fish, the dream of anyone who’s had to do regular reporting: automation.As an SEO, I use the Google Analytics add-on for this exact purpose for many of my clients. I’ll start by assuming you’ve installed and set up the add-on, and are ready to create a custom report configuration.Step one: Outline a frameworkBefore we begin creating our report, it’s important we understand what we want to measure and how we want to measure it. For this example, let’s say we want to view organic traffic to a specific set of pages on our site from Chrome browsers and that we want to analyze the traffic month-over-month and year-over-year.Step two: Understand your framework within the add-onTo get everything we want, we’ll use three separate reports: organic traffic in the past month (January 2016), organic traffic in the month before that (December 2015), and organic traffic in the past month, last year (January 2015). It’s possible to include this all in one report, but I recommend creating one report per date period, as it makes organizing your data and troubleshooting your configuration significantly easier.Step three: Map your key elements to add-on parameters Report One parameter breakdown:Report Name – 1/1/2016Make it easily distinguishable from the other reports we’ll be running Type – coreThe GA API default View (Profile) IDThe account we want to pull data from Start Date – 1/1/2016The beginning date we want to pull data from End Date – 1/31/2016The cutoff date for the data we want to pull Metrics – ga:sessionsWe want to analyze sessions for this report Dimensions – ga:dateAllows us to see traffic the site received each day in the specified range Filters – ga:medium==organic;ga:landingpagepath=@resourcesWe’ve included two filters, one that specifies only organic traffic and another that specifies sessions that had a landing page with “resources” in the URL (resources is the subdirectory on Distilled’s website that houses our editorial content) Properly filling out filters and segments requires specific syntax, which you can find on Google’s Core Reporting API resources. Segments – sessions::condition::ga:browser==ChromeSpecifies that we only want session data from Chrome browsers Sampling Level – HIGHER_PRECISIONSpecifies that we want to minimize sampling for this data set Report One output: Past month’s sessionsNow that we’ve set up our report, it’s time to run it and check the results.So, in the month of January 2016, the resources section on Distilled’s website saw 10,365 sessions that satisfied the following conditions:organic source/medium landing page containing “resources” Chrome browser But how do we know this is accurate? It’s impossible to tell at face value, but you can reliably check accuracy of a report by looking at the analogous view in Google Analytics itself.Confirming Report One dataSince the Google Analytics add-on is an analogue to what you find on analytics.google.com, in your account, we can combine separate pieces in GA to achieve the same effect as our report:Date RangeOrganic Source/MediumLanding Page Path & BrowserThe resultHooray!Now that we’ve confirmed our framework works, and is showing us what we want, creating our other two reports can be done by simply copying the configuration and making minor adjustments to the parameters.Since we want a month-over-month comparison and a year-over-year comparison for the exact same data, all we have to do is change the date range for the two reports. One should detail the month before (December 2015) and the other should detail the same month in the previous year (January 2015). We can run these reports immediately.The results?Total Sessions In January 2015 (Reporting Month, Previous Year: 2,608Total Sessions In December 2015 (Previous Month): 7,765Total Sessions In January 2016 (Reporting Month): 10,365We’re up 33% month-over-month and 297% year-over-year. Not bad!Every month, we can update the dates in the configuration. For example, next month we’ll be examining February 2016, compared to January 2016 and February 2015. Constructing a dashboard can be done in Sheets, as well, by creating an additional sheet that references the outputs from your reports!5. Closing observations and pitfalls to avoidThe Google Analytics add-on probably isn’t the perfect reporting solution that all digital marketers yearn for. When I first discovered the Google Analytics add-on for Google Sheets, I was intimidated by its use of Regular Expressions and thought that you needed to be a syntax savant to make full use of the tool. Since then, I haven’t become any better at Regular Expressions, but I’ve come to realize that the Google Analytics add-on is versatile enough that it can add value to most reporting processes, without the need for deep technical fluency. I was able to cobble together each of the reports I needed by testing, breaking, and researching different combinations of segments, filters, and frameworks and I encourage you to do the same! You’ll most likely be able to arrive at the exact report you need, given enough time and patience.One last thing to note: the Google Analytics interface (i.e what you use when you access your analytics account online) has built-in safeguards to ensure that the data you see matches the reporting level you’ve chosen. For example, if I click into a session-level report (e.g landing pages), I’ll see mostly session-level metrics. Similarly, clicking into a page-level report will return page-level metrics. In the Google Analytics add-on, however, this safeguard doesn’t exist due to the add-on being designed for greater versatility. It’s therefore all the more important that you’re thorough in outlining, designing, and building your reporting framework within the add-on. After you’ve configured a custom report and successfully run it, be sure to check your results against the Google Analytics interface!Abraham Lincoln famously said, “Give me six hours to chop down a tree and I will spend the first four sharpening the axe.” Good advice in general that also holds true for using the Google Analytics add-on for Google Sheets.Supplementary resource appendix:Kristi Hines' Google Analytics for Beginners guide – See title. Google's Analytics Core Reporting API Documentation – Dig around here to find out how to build reports that give you exactly what you’re looking for! Avinash Kaushik's Analytics Resource – Discusses the Google Analytics reporting framework and how to think about metrics and sessions. RegExr – General Regular Expressions resource. Debuggex – Visual Regular Expressions debugging tool.Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don't have time to hunt down but want to read!
  • 9 min read
  • Mar 20, 2016