Category Archives: Google Analytics

Misunderstood Metrics: Bounce Rate

What is bounce rate in Google Analytics? Is higher or lower better? What is typical? Does it affect my search engine rankings?

Bounce rate defined

Google says the bounce rate is “The percentage of single-page sessions (i.e., sessions in which the person left the property from the first page)“.

Notice that is says NOTHING about how long the person spent looking at the page; that is because in Google Analytics the times are calculated between page views, and with only one page…well, the time is zero! (see Time on Page/Session Duration) People can read your entire page, but if they don’t click to another page on your site, Google Analytics considers it to be a bounce.

What is a typical value?

Typical values depend a lot on the page itself: navigation-centric pages can hover around 0, while landing pages with no navigation links will have 100% bounces. A lot of pages, especially blog articles, tend to hover above 80% unless you make an effort to direct the reader to another page.

This chart shows the bounce rates of the top landing pages from a hundred random websites.

Very low bounce rate?

One common problem people experience is bounce rates hovering around 1%, and this is usually caused by website errors. Either the Google Analytics tracking code has been added twice on the page or there is an auto-redirect occurring . In the case of duplicate tracking code, you will see inflated (almost double) sessions and pageviews as well.

Does it affect my website traffic or rankings?

Some people feel a high bounce rate might affect the number of visitors your site gets, but the real answer is more complicated. If the page satisfies the reader’s interest, then chances are the traffic and rankings will come. If they keep looking for a better answer, then you will probably see things drop.

Looking at the same sample of pages as above, the next chart shows that bounce rate is not related to the raw number of sessions that a page may get. You will also find it common to get high ranking pages with high bounce rates, as well as low ranking pages with low bounce rates.  Given Google’s definition of a single page visit, bounce rate is not a good metric to focus on.  (see time on bounce pages)

As a parting note, do not get hung up on bounce rate — it is not a good Key Performance Indicator since it can be subject to so many influences that have nothing to do with ‘performance’ of your website or pages.

WTF is Happening to My Traffic?

googleanalytics-wtfJoe Kelly of Workshop Digital created this custom dashboard to easily analyze what is causing organic search traffic to decline, and I adapted it to work with the free Analytics Edge Basic Add-in. Just enter your Google Analytics view ID number and refresh.

The dashboard will help you see:

  • If you’re losing organic visibility in a specific city where you previously dominated
  • If there’s a high probability that top landing pages lost valuable organic positions
  • If the drop in organic traffic is impacting conversion metrics
  • If other traffic sources are down, which can help identify the impact of other marketing channels
  • If the drop in traffic is specific to a device type

Read the original Workshop Digital blog article with a Google Sheets version.

Download the workbook:
GoogleAnalytics-WTF-is-Happening-to-My-Traffic-Dashboard-2-0.xlsx

Tabular Summary Report

Core Add-in, Google Analytics Connector
googleanalytics-summary-report-1-0This quick overview report shows all the key performance metrics for the past year in a single tabular view. All the overview, acquisition, behavior and conversion metrics you expect in a quick summary report.

Everything is automated; just Refresh All with the Analytics Edge Core Add-in. Easy to modify and rebrand – make it your own!

Download the workbook: GoogleAnalytics-Summary-Report-1-0.xlsx

 

 

Analytics Reporting – Output

outputThe Output tab (available only in the Google Analytics Pro Connector) offers the ability to download direct to a CSV (comma-separated) file instead of returning the results to Excel. This may be useful for extra large downloads that might exceed the capacity of Excel.

Note: the Pivot capability is not supported for CSV downloads, and any selection will be ignored.

Caution: large downloads with many dimensions are subject to server overload errors (500 error code) which will stop the download.

Spam-Free Reporting With Dynamic Segments

Google Analytics has had a bit of a referral spam problem over the past year, and although you can install filters in your account to prevent most of it (see the Definitive Guide to Removing GA Spam), you can’t apply them retroactively. The good news is that you can use a little-known feature of Analytics Edge to strip it out of your Excel reports.

dynamic-spam-segment

Continue reading

Google Analytics Premium Support

The Google Analytics Pro connector from Analytics Edge supports the extra dimensions and metrics available to Google Analytics Premium customers.

This includes:

  • all 200 custom dimensions and metrics (including their user-defined names)
  • all 50 custom variables

The connector automatically detects the account type and adjusts the available metrics and dimensions list accordingly.

Year-Over-Year Comparison in One Query

Here’s a neat little technique you can use to get year-over-year comparisons in one query with the Google Analytics Pro connector from Analytics Edge. There are some limitations, but it can make it a lot easier to create quick reports for smaller sites.

Introducing the Month Index

year-over-year-1The trick uses a little-known dimension in the API: Month Index. Used with, or instead of, the Month of the Year dimensions, this index always shows the first month in the report as ‘0000’, then ‘0001’, ‘0002’, and so on. Its purpose was to make it easier to build trend charts that span multiple years but didn’t need a timeline on the x-axis, like in sparklines charts.

If you make your query span 13 months, ending at the end of last month, then last month would show as ‘0012’, and the same month in the previous year would be ‘0000’.

Filtering on Month Index

year-over-year-2If you filter the query for these two Month Index values (matching regex ‘0000|0012’), then all the other months would show 0.

If you REMOVE the Month Index and Month of the Year dimensions, you are left with just the Year, and the two monthly values!

One query comparing last month’s metrics to the same month last year.

Add a Dimension: Pivot and Sort

year-over-year-3Now we can take this one steps further, and add another dimension, like Default Channel Grouping, to our query and then Pivoting by the Year.

You can get everything ordered properly by sorting the query by Year ascending, then your metric descending. This makes sure that the previous year is shown at the left, and the channel with the larger values are shown first.

Still just one query.

Download an example of this query: year-over-year-in-one-query.xlsx

Volume Limitation

This technique relies on a query that spans 13 months, so if your website has about 40,000 sessions per month or more, the query would involve over 500,000 sessions and Google Analytics sampling would become a concern.  If this is the case, I suggest you do it the hard way, with separate queries for greater accuracy.

 

02 Add an Account

Once you have installed and activated the trial for the Google Analytics connector, you need to log in to your Google Analytics account. Analytics Edge will save your login credentials to make refreshes faster and easier, and you can log into as many different accounts as you want.

Open the Accounts Wizard

ribbon-cropFrom the Google Analytics (Free or Pro) button on the ribbon bar, select Accounts from the menu. The Accounts wizard will open.

The Free Google Analytics connector and the Google Analytics Pro connector share their saved accounts, and they are upwards compatible from Free to Pro.

Login and Create a Default Account

For security reasons, Analytics Edge stores your account information on your computer, not in the workbooks. Accounts are stored using Reference names, and the queries use a Reference name to identify which account to use. Before you can log into an account, you must enter a Reference name in the field provided, then click the Add Account button.

accounts-referencename

The Google Analytics Pro connector provides two option checkboxes which must be checked if you want to use elevated permissions for uploading data or managing the account.

Log into your Google Analytics account and Allow the application to have offline access.

accounts-login

Select your new Account in the list at the bottom, pick the Property and View you want to use as a default, and click the Make Default button, then you can Close the wizard.

accounts-selected

You are now ready to make your first query…

 

 

Unsampled Data From Google Analytics For Free

Wondering how to get unsampled data from Google Analytics into Microsoft Excel? Analytics Edge provides a free solution: the Free Google Analytics Connector included with all Analytics Edge Add-ins has the ability to minimize sampling problems with large sites and long time frame queries (up to a million rows!). Simply add a date dimension (Date, Week, Month), then check the box to Minimize Sampling on the Options tab, and Analytics Edge will make separate queries for each date in the selected date range and merge the results into a single table.

Continue reading

Building a Marketing Dashboard

Marketing-Dashboard[updated 2016-04-05] I enjoyed reading an article from Erin Simmons of Delphic Digital about building a digital marketing dashboard template, Simple Visualizations with Swagger, and thought it would be a nice example to show how you can automate a Google Analytics report in Excel using the Simply Free products from Analytics Edge. This article describes the steps I went through to build a working prototype; it is light on process and documentation because this is an educational article, not a statement of best practices.

Included below:

  • determine what data you will need and how you’ll get it
  • optimize your queries to reduce duplication and speed up refreshes
  • layout your dashboard on a grid using Excel rows and columns
  • an included example shows the actual queries and techniques used

Continue reading

Advanced Techniques: the Monthly PDF Report

[updated 2016-01-11] This article describes some of the more advanced techniques used to create the free Excel workbook Google Analytics Monthly PDF Report. The workbook can automatically send PDF reports for multiple websites as email attachments, and it uses a number of the new features of both the Analytics Core Add-in v2.20 and the Analytics Edge Connector for Google Analytics v2.3.

The workbook has four Analytics Edge macros in it; one master repeating macro, and one macro for each of the three pages in the report.

The Repeat Macro

The magic in this report is the Repeat Macro. This macro reads a table from the Setup worksheet and repeats the rest of the macro for each row in the table. Each row describes a website to be processed — the Login and View Id number (for the Analytics Edge queries), plus a descriptive name for the site and an email address to send the report to.

monthlypdf-websites

As it processes each row, it writes the values from that row to a different location in the Setup worksheet where the other macros can find them. Then the repeat macro uses the Run Macro Function to call each of the 3 report macros in turn.

Those 3 macros were given names that start with an underscore (e.g. “_Refresh Audience”) so they will not be triggered by a Refresh All operation. The Run Macro Function allows you to force those macros to run as part of the repeat macro, and only after the values at the top of the Setup worksheet have been changed.

save-email-pdf-fileAfter the report macros have been run, the report is ready to be saved. A Save/Email PDF File function is used to save the workbook as a PDF file and send it via email to its intended recipients.

Two columns in the Repeat Macro table are used to customize the file name and email message. The [Name] range is used in the file name and message to give meaning to which website the report covers, and the [EmailTo] range is used to set the email address for the recipient(s).

Report Macros

The 3 macros for the pages in the report start by reading the range of cells on the Setup worksheet to get the Google Analytics login account and website view id needed for the queries.  The information is passed this way because macros cannot pass parameters between them – they all run independently.

The macros write results to both the report worksheet as well as a hidden data worksheet used to feed the charts on the report.

report-worksheetMost of the reports are a simple display of the numbers coming from Google Analytics. The key metric section includes a percentage comparison to the previous period, and this is done by downloading data for both periods and using the Compare function, with a growth calculation.

The time metrics (avg session duration, time on page) are output in Days using an option in the connector. This is done so the resulting number can be formatted in Excel using the mm:ss time format, displaying minutes:seconds.

One feature of the workbook is the ability to change the report worksheet names to suit the client; maybe a difference in language, or simply a local spelling variant (like Behaviour vs Behavior). This is accomplished by putting an Excel formula on the hidden data worksheet that references a cell on the report sheet and returns the name of that sheet:

=MID(CELL("filename", Audience!A1),FIND("]",CELL("filename", Audience!A1))+1,32)

The macro reads this cell and assigns it to an Analytics Edge range name so it can be used in the WriteToWorksheet functions. If you change the worksheet name, the macro automatically detects the new name.

Summary

These new features combine to make it possible to send a series of emailed reports from a single, automated macro. Using the Analytics Edge Scheduled Refresh, it can run on your computer unattended, reducing hours of report updates to literally nothing.

Recommended Modifications

Two changes recommended for production use:

1. Move the Setup worksheet to a separate workbook. The Refresh Macro can reference the other workbook, and should you decide to distribute the report workbook or forget to hide the Setup worksheet, you will not send out the names and email addresses of other clients.

2. Some email systems will automatically keep a copy of sent messages, but if yours doesn’t, in the Save/Email PDF File wizard, check the box to Bcc me. It is always a good idea to have a copy of what is sent to a client.

 

How the Blog Cohort Analysis Macro Works

If you’re trying to use the free report Cohort Analysis for Blog Articles, but don’t have the same blog path, here’s a behind the scenes look at how the macro was made so you can adapt it to your own article path.

First of all, it works by extracting the year and month from the page path, so if your URLs do not have year and month components, this report won’t work for you. Let’s step through the macro and see what makes it work. You can step through it yourself to see if your results are consistent or where you might need to make changes.

step-through-macro-300x164>> GA Reports

Makes the query to get the data we need. Edit this function to specify the account and Google Analytics view you want to use. It is set to use your default settings, which is convenient for me, but you really should set it to a specific view to avoid reporting the wrong web property by mistake some day in the future (if your default selection changes).

The Filters tab sets a filter to restrict the response to content where the Page dimension matches a regular expression of “/20../”, meaning any pages with /2000/ through /2099/ in it, but it might also match /20AA/ or similar if your site has such things.

Note that the “minimize sampling” is checked on the Options tab, which will cause 12 queries to be run, one for each month over the past year. If you have a low-volume blog site (under 500,000 sessions per year), you can turn off this option to speed up the refresh (making only 1 query for the whole period).

Join

In the end result, we have a column of links that let us go to the actual article. Google’s Page dimension does not include the hostname component (http://www.analyticsedge.com), so we have to join the Hostname column with the Page column to build one. Note that it is still missing the “http://” part, but we will add that later.

www.analyticsedge.com/2014/08/cohort-analysis-blog-articles/

Replace (.*/)(20../..)(/.*)

Now that the Page column is no longer needed, we’ll use it to determine the year and month that the article was published. This takes a series of steps, the first of which is to extract the 2014/08 portion from the URL, and a regular expression replacement does the trick.

2014/08

Split (x2)

Next we split the Month of Year column (201409) into year (2014) and month (09) portions using the fixed number of characters option, then split the Page column (2014/08) using the “/” character. The result is 4 new columns with year and month components we need for the cohort analysis — the month measured and the month published.

split-columns

Convert by name

Note that the 4 columns are text, and to do any calculations with them, we need to convert them into numbers.

Calculate (x6)

Now the challenge: having the year and month the article was published and the year and month when pageviews were counted, calculate the difference in months. Several ways you could tackle this; I divided the months by 12 to create fractions of a year, then added that to the years, then got the difference, and finally multiplied by 12 to get it in months. [In hindsight, if I had multiplied the years by 12, I could have kept it all in months and saved a step and avoided the next step].

age-calculation

Convert by name

Since I divided by 12 and took a difference, when I multiplied by 12 again the numbers sometimes didn’t come to nice clean values (like 0.99999999). To fix this, I used the Rounding function of the Convert function to round off to the nearest month.

Sort

I am going to want to pivot the table by the Age, but before I do that, I sort on the Age column in ascending order. This causes the pivot operation to put the columns in ascending numerical order — they get created in the order they are ‘discovered’ as the pivot takes place.

Pivot

Here I create new columns for each unique age value and collapse the table to show the number of pageviews for each page versus the age of the article.

Total columns

The next step is to decide how to order the results, and you have a choice here. I created a ‘Peak’ column with the maximum value for each row. When I sort this column in descending order, it will put the article with the single highest peak interest (pageview count) at the top. I wanted to see the most popular article for any single month at the top.

peak-pageviews

An alternative is to use a Total (sum) to show the article with the most pageviews for the entire year at the top. I found that favored the old articles and dropped the extremely popular new articles way down the list. Choose what suits your website goals and reporting desires.

Sort

Sort the pivoted table using column D values. Note that I used the By Position option instead of By Name to make it easier for you to change the ‘Peak’ column into a ‘Total’ column and not have to worry about the column column name being different.

Keep top 50

Don’t really want a huge list of hundreds of pages, do you? Pick a number…any number.

Arrange by position

This function lets me rename the ‘Page’ column to what it now represents — the date Published.

Replace *

Finally, I need to add that “http://” to all the values in the Link column.

WriteToWorksheet Cohort Analysis

Time to share your results with the world and write them to a worksheet. In this case, I selected the options to NOT clear the cell formatting — this allows me to use conditional formatting in the results without having the macro wipe it out when refreshing the data.

I also selected to add hyperlinks to the web addresses, which allows you to click a link in the report to see the actual page. Note: you may need to select the cells in this column and change the Excel number format to ‘General’ from ‘Text’ if it is displaying a formula (=HYPERLINK…).

cohort-analysis

That’s it! You can change any of the steps to suit your website URL structure, change the math, or change the sort order to your preference. Heck, you can change anything you want!

Multi-Source Waterfall Report

The wizard-based automation provided by the Analytics Edge Core Add-in for Excel makes it easy to combine data from multiple sources. This article explores how you can build a Waterfall Report that includes data from Google Webmaster Tools (now Google Search), Bing Webmaster Tools, Google Analytics and an external database. The same techniques can be used to combine data from any number of sources.

waterfall-report

Continue reading

Data Sampling Problems In Annual Reports

Google Analytics uses statistical sampling to improve responsiveness for large queries. There is nothing wrong with statistical sampling — it will usually produce numbers close to the actual, but not always. It starts to display problems with long term trend charts typically shown in annual reports. The Analytics Edge connectors for Google Analytics include a feature that can dramatically reduce sampling errors in your Excel reports.

google-analytics-annual-report-sampling

 

Continue reading

Collection of Top Reports

Basic Add-in, Google Analytics Connector
googleanalyticsoccamsrazorreportsThis free Analytics Edge workbook includes a set of reports inspired by an entry in the Google Analytics Solutions Gallery by Avinash Kaushik*. It can be refreshed with either the Analytics Edge Code add-in or the Basic add-in in combination with the Connector for Google Analytics.

The reports included:

  • All Traffic Sources Report
  • Content Efficiency – Business Analysis
  • Content Efficiency – Technical Analysis
  • Paid Search Report
  • Internal Site Search Analysis
  • Landing Pages Analysis
  • Mobile Performance Analysis

Inspired by: Occam’s Razor Awesomeness

Download the workbook: GoogleAnalyticsOccamsRazorReports-2-0.xlsx

Visits by Medium Trend

Core Add-in, Google Analytics Connector
google-analyt-cs-visits-by-mediumLet Analytics Edge do your work for you with this free report. Automatically download your Google Analytics data and transform that raw data into meaningful information. No formulas required; the workbook uses the wizard-driven Analytics Edge filter, replace, pivot and sort functions to do the work for you.

Download the workbook: GoogleAnalyticsVisitsByMediumReport.xlsx

Inspiration reference: annielytics.com

Google Analytics Summary

Basic Add-in, Google Analytics Connector
google-analytics-summaryThis free Google Analytics Summary Excel Report is refreshed in seconds using the Analytics Edge Connector for Google Analytics. It includes 4 trend charts for the past 30 days, providing great visual presentation of:

  • visits by continent
  • visits by medium
  • visits by social network
  • visits by device

Click the Refresh All button on the Analytics Edge ribbon and all the queries are updated. No fussing with dates. The report uses standard Excel pivot tables and charts, so after you refresh the queries, you will need to refresh the pivot tables as well (Data ribbon – Refresh All button). You can easily modify it to suit your website tracking challenges.

Download the workbook: Google-Analytics-Summary-1-3.xlsx

SEO Combination Report

Core Add-in, Google Analytics Connector, Google Search Connector, Moz Connector
ga-gwt-moz-reportThis free workbook uses the Analytics Edge Connectors for Google Analytics, Google Search, and Moz to combine key SEO metrics for your top 100 web pages. The included Analytics Edge Core Add-in macros make refreshes a snap.

The report lists your top 100 landing pages from Google Analytics, sorted by the number of Entrances. The macro then combines Google Search data and Moz data producing an all-in-one report in seconds!

The report includes:

  • Entrances and Unique Pageviews from Google Analytics
  • Avg. position, Impressions, and Clicks from Google Search Console
  • External Equity Links, Root Domains Linking, and Page Authority from Moz

Instructions: see blog article

Download the workbook: GoogleAnalytics-Search-Moz-report-3-0.xlsx  [updated 2014-09-21]

Google Analytics Audience Overview

Basic Add-in, Google Analytics Connector
google-analytics-audience-overviewThis free Excel workbook provides a quick summary of your website’s audience over the past 30 days, just like in Google’s web interface!

See the trends and totals for all the key metrics of Sessions, Users, Pageviews, Pages/Session, Avg. Sessions Duration, Bounce Rate and % New Sessions. Compare new and returning visitors day by day as well as a summary pie chart.

The bottom section is interactive! Click one of 9 dimensions and see the top 10 items: Language, Country, City, Browser, Operating System and Service Provider. You can even check out the Mobile (and tablet) Operating Systems, Service Providers and Screen Resolutions. Each listing includes a percentage of the total sessions (% of total mobile sessions for the mobile metrics).

Download the workbook: GoogleAnalytics-AudienceOverview-1-0.xlsx

Google Analytics Acquisition – All Traffic

Basic Add-in, Google Analytics Connector
google-analytics-acquisition-all-trafficBased on the standard Acquisition-All Traffic report, this free workbook gives you new perspective into where your website traffic is coming from. Refresh in seconds with Analytics Edge.

With interactive Excel features and conditional formatting, you can easily switch between 9 different dimensions and visualize the top key traffic source segments.

Interactive dimensions include: Source/Medium, Medium, Source, Referral Path, Campaign, Social Network, Landing Page, Visitor Type, and the Count of Sessions (visit frequency). Metrics are presented for easy reading, with rates in percent, and times in hours:minutes:seconds format. The main chart is based on an Excel pivot table, allowing you to filter which items are included.

Download the workbook: GoogleAnalytics-AquisitionAllTraffic-2-0.xlsx (Revenue metrics) or GoogleAnalytics-AquisitionAllTrafficGoals-2-0.xlsx (Goal metrics)

Desktop Screen Resolution/Browser Size

Core Add-in, Google Analytics Connector
DesktopScreenResolutionThis free report presents the Google Analytics screen resolution (or the new browser size) dimension the way it should be — showing the percentage of website users served by various combinations of screen widths and heights.

The report auto-adjusts to your site data, showing the most common size increments that your website visitors use.

The two-axis table and conditional formatting make it really easy to zero in on the target screen sizes that a specific percentage of users have. For example, in the sample image, 81% of visitors have screens 1280 wide x 768 high.

Download the workbooks:
GoogleAnalytics-DesktopScreenResolution-1-1.xlsx (desktop devices only)
GoogleAnalytics-BrowserSize-1-0.xlsx (all devices)

Frequency and Recency Report

Basic Add-in, Google Analytics Connector
FrequencyAndRecencyThis free Excel report includes all the formulas and conditional formatting needed to reproduce the Google Analytics Frequency and Recency reports. Use it as part of your dashboard project!

A quick refresh and your website data is presented in an easy-to-understand tabular format, complete with background bar chart-like conditional formatting providing a quick visual reinforcement of the larger numbers.

Download the workbook: GoogleAnalytics-FrequencyAndRecency-2.xlsx

Cohort Analysis for Blog Articles

Core Add-in, Google Analytics Connector
cohort-analysis-for-blog-articlesDo your blog articles peak in popularity as soon as they are published or do they gain attention over time? This free report shows the number of pageviews per month for your most popular blog articles versus the number of months since they were published.

Sometimes called “cohort analysis”, this report lets you compare how various articles perform from the day of the their introduction. Sorted by their peak performance over the past 12 months, the report compares the monthly pageview counts for each article. The included Analytics Edge macro leverages the Google Analytics connector’s ability to minimize data sampling.

The macro is designed to recognize blog articles with year and month numbers in their paths (…/2014/08/…), and is open for easy customization to suit your blog site specifics.

Download the workbook: GoogleAnalytics-BlogCohortAnalysis-2-0.xlsx

This report was inspired by an article from Jon Meck of LunaMetrics.

Interactive Behaviour Flow

Basic Add-in, Google Analytics Connector
behaviour-flowThis free interactive workbook lets you see the relationships between Sources and Landing Pages from your Google Analytics data. Simply refresh the queries and hover the mouse over any of the Top 10 Sources listed at the left or over any of the Top 10 Landing Pages listed at the right.

Inspired by an E90F50fx blog article.

Download the workbook: GoogleAnalytics-Source-to-LandingPage-Cosmograph-1-1.xlsm

Interactive Analytical Charts

Basic Add-in, Google Analytics Connector
interactive-analytical-chartsThis free workbook show how easy it is to mimic the alternating presentations of data as seen in the Google Analytics web interface. Simply click the top radio buttons to switch between table, pie chart (percentage), bar chart (performance) and relative to average (comparison) views.

Inspired by an article from Chandoo.org

Download the workbook: GoogleAnalytics-Analytic-Charts-1-0.xlsx

Time of Day, Day of Week, by Device Type

Basic Add-in, Google Analytics Connector
googleanalytics-timeofday-dayofweekThis free Excel report shows the most popular times of day, by day of week, for each device category tracked by Google Analytics: Desktop, Mobile or Tablet. It could also be easily adapted to display time-of-day vs day-of-week profiles for other dimensions such as Continent, Medium, of Social Network.

Inspired by a presentation from Gerry White of SiteVisibility

Download the workbook: GoogleAnalytics-TimeOfDay-DayOfWeek-1-0.xlsx

Can be refreshed with the Analytics Edge Basic Add-in and connectors.

Email-enabled Monthly PDF Report

Core Add-in, Google Analytics Connector
email-enabled-monthly-pdf-report[updated 2016-07-27] This free Analytics Edge report demonstrates the capabilities of the Analytics Edge Core Add-in v2.20 to fully automate monthly reporting. It supports multiple Google Analytics accounts, exports to Adobe PDF format and sends the file via email to named recipients.

Using a simple setup worksheet, you enter the Google Analytics login to use, the view ID, a descriptive name, and the recipient’s email address. If you enter more rows of values, Analytics Edge will process each row in turn, sending PDF versions of the report to the email addresses entered.

Download the workbook: GoogleAnalytics-MonthlyPDFReport-3-0.xlsx

Read the companion blog article : Advanced Techniques: the Monthly PDF Report 

Cohort Analysis by Source

Core Add-in, Google Analytics Connector
googleanalytics-gohort-analysis-by-sourceThis free report demonstrates how easy it is to use the Analytics Edge Core add-in and Google Analytics Connector to perform a dynamic cohort analysis in Excel.

Its shows the number of sessions (visits) by source for the date range, plus how many of those sessions came from visitors whose first session on your site was in each of the past 6 months. Quickly see which sources people are using to return  to your site again and again.

Easily modify the report to show traffic by Landing Pages, Countries, or Languages. Change the metric to see conversions, revenue or other measure.

The date range for the report can be entered directly on the report worksheet, with the individual cohort labels and date ranges defined on a supporting worksheet.

Download the workbook: GoogleAnalytics-Cohort-Analysis-by-Source-1-0.xlsx

This workbook requires the Analytics Edge Core Add-in v 1.4 or higher, and the Free or Pro version of the Google Analytics Connector v1.7.3 or higher.

Sampling-Free Annual Report

Core Add-in, Google Analytics Connector
google-analytics-annual-reportThis free report demonstrates how easy it is to use the Analytics Edge Core add-in and Google Analytics Connector to create an annual report that avoids Google Analytics data sampling errors.

The Analytics Edge automated refresh makes 24 monthly queries to virtually eliminate sampling errors and deliver accurate trend information, by channel, for the past year.

The report also displays top countries and top pages, as well as the top traffic source and landing page combinations.

Download the workbook: GoogleAnalytics-Annual-Report-1-1.xlsx
or for small sites that don’t need to worry about sampling, here is a faster version: GoogleAnalytics-Annual-Report-SmallSite-1-0.xlsx

This workbook requires the Analytics Edge Core Add-in and either the Free or Pro version of the Google Analytics Connector.

7 Day Cohort Analysis

Basic Add-in, Google Analytics Connector

7-day-cohort-analysisThis free Excel report from Analytics Edge works with the free Basic Add-in and Google Analytics connector to mimic the 7-day Cohort Analysis report provided in Google Analytics.

Using embedded queries with dynamic segments, the report shows how many of your new website visitors returned to your site over each of the past 7 days.

Use the report as-is to gauge short term campaign effectiveness, or use the technique in your own custom analysis.

Download the workbook: GoogleAnalytics-7DayCohortAnalysis-1-0.xlsx

A companion Help article shows how to make a 12-Month Cohort Analysis and includes a sample report.

The Hidden Value of Nofollow Blog Comments

When I was working on a new Excel report for my Google Webmaster Tools connector, I started seeing traffic from a specific query phrase that didn’t make sense, so I dug a little deeper, and what I found made me question the very foundation of conventional SEO wisdom and the meaning behind Google Webmaster Tools data.

Webmaster Tools says that it shows “queries that have returned URLs from your site” and the “average top position of your site” in the search results. I think maybe it did a long time ago and some of the algorithm changes they have made have slipped into the numbers.

Oddly enough, the search query term I questioned was “google webmaster tools”, and I had just released the Webmaster Tools connector and thought I should start tracking to see how effective my various awareness-raising initiatives were. I was very surprised: of the 8.2 million results and thousands of excellent, well-established articles, my little site was already showing on page 4. What? How could THAT happen? I was doubly surprised because I had done almost nothing to promote it.

webmaster-tools-tracker-report

How the heck did I get page 4? It turns out…I didn’t… but a LunaMetrics blog post did. The LunaMetrics article had a lot of social media coverage and quickly rose above the 8.2 million other pages of less-interesting material to garner a nice ranking.

lunametrics

At the bottom of that excellent article is an innocuous comment I posted, with my name linked to my home page. It was nice that it got me a bit of traffic but the GWT clicks metrics seem to line up with what I saw on my web site, and the “impressions” and “avg. position” I am seeing in GWT must be for the LunaMetrics page. My site did not rank for that term – and I got traffic through a nofollow link!

What Might Be Happening

I understand that Google algorithms are quite complex, and they will drop your ranking if people don’t engage with your page, so they must be tracking what people do after they come to your site. They would know that people clicked through to my site after reading the article, but what is surprising is that they attributed some of the LunaMetrics impressions and position to my site as a search result. Yes, people found me through search, but it wasn’t direct.

I am not complaining! This is absolutely wonderful! It means that GWT will provide me with search terms people have used to find me, whether it was direct, or indirectly through another website. #KeywordHeaven!

Misunderstood Metrics: Sessions for Pages

Build a custom report to see how many Sessions included your top pages, and the result is confusing! Chances are you will see the number of Sessions drops sharply once you get below your primary landing pages. What gives? How can the number of Sessions be lower than the number of Users? Doesn’t every user have at least 1 session?

sessions-for-pages

Blame it on Google. They made it really easy for you to build the report and didn’t warn you. The problem is that the Sessions metrics should not be used with Hit-level dimensions like Page, but Google doesn’t tell you that. They also don’t tell you what a Hit-level dimension is, and there is no simple way to figure it out. The standard reports are fine, but that Second Dimension button can be dangerous!

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Continue reading

Misunderstood Metrics: Events

Google Analytics allows you to track user interaction “events” independent of a page view, such as file downloads or video plays. The website developer needs to embed code to track these events, and Google gives them a lot of freedom over how the various parameters can be used. The dimensions and metrics used to track these events have a few odd behaviours that can be confusing. In this article, we’ll look at the common problems people have with the Total Events and Events/Session With Event metrics.

event-reporting-2

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Continue reading

Misunderstood Metrics: Unique Events

Google Analytics has recently changed, introducing a new Unique Events metric calculation. This article has not yet been updated to reflect that change — Mike Sullivan.

As a follow-up to my previous post on Google Analytics events, this article will look at the unique problem of “Unique Events“…the metric that defies understanding by many. Defined in Google Analytics as “The number of times during a date range that a session contained the specific dimension or combination of dimensions“, part of the challenge created by this metric is the way it is presented in the standard reports.

unique-events

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Continue reading

Misunderstood Metrics: Custom Dimensions

If you’ve made the leap into custom dimensions with Google Analytics, you’ve probably also tried to make a custom report to segment your traffic with the new dimension. You add the new dimension and suddenly the numbers don’t look right! What gives???

adding-a-custom-dimension-to-a-report

Like several other special dimensions in Google Analytics, the Custom Dimensions do not have a an empty ‘(not set)’ value. If you include the dimension in a report, there is no catch-all row to total up all the sessions that didn’t have a value to display. Instead, the report simply drops all that extra data.  Make sure you check the fine print (light grey text under the column aggregate numbers) where you’ll see a comparison to the website totals.

If you are building a custom report or dashboard, it will be important to keep this context when you start segmenting by your new dimension. e.g. Of the 24% of sessions that had a custom dimension value…

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone
Download Google Analytics data into Excel with the free Analytics Edge Basic Add-in.

Discover The Entire Google Analytics Misunderstood Metrics Series:
Next Page Path,  Sessions for Pages,  Events,  Unique Events,  Custom Dimensions,  Count of Sessions, Time on Page/Session Duration, Bounce Rate

Misunderstood Metrics: Frequency/Count of Sessions

The Google Analytics report for Frequency displays a Count of Sessions dimension that is misleading in several ways. It may not be what you think it is, it should not be used in isolation, and the report format can make you see things that aren’t there.

Count of Sessions

count-of-sessionsLets start with the number itself: the Count of Sessions number (actually it is a dimension, and as such it is a text string or label) comes from a cookie in the user’s browser — it keeps track of how many times the user has been to your site. On their first visit, it is a 1, on their second visit it is a 2, and so on.

In the example shown, this means that 6,508 users had their first session on the site during the period. If you checked the New vs Returning visitors report, you would see the numbers match the New Visitors number. All good.

But this begs a clarification: it does NOT mean that 6,508 users had only 1 session on the site; it means they had their FIRST session. They may have visited 5 times during the period, and they would be represented in the chart for Count of Sessions 1, 2, 3, 4 and 5 because their cookie would increment for each visit. There could be a huge amount of duplication in the report….or none.

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Continue reading

Measuring Time On (Bounce) Page

For people measuring their website traffic with Google Analytics, there is nothing more frustrating than building a really popular web page that gets tons of visits, but GA shows Session Duration close to ZERO! The problem is that Google Analytics doesn’t measure the time on the last page of a visit, so even if they read your article for 5 minutes, if they don’t click on another page, their session is recorded as 0 seconds. Sigh….

Continue reading

Using the Save Filter Function

The Google Analytics Pro connector contains a Save Filter function that lets you copy view filters from one account to a number of others with ease.

Driven almost entirely by spreadsheet values, the function takes filter definitions from one worksheet locations and saves them to Google Analytics accounts/views defined in another worksheet location. Even the login account used for the command can be obtained from the workbook. Let’s look at a typical exercise: copying a pair of spam filters to multiple web properties.

Setup: Getting Permission

Before you can use the Save Filter function, you need a login account that has the necessary permissions. When you login through the Accounts wizard, you have to ask for the extended permissions necessary to manage the account — just check the box before you click Add Account. 

Note that if you forget to check the box, the credentials given to Analytics Edge will not allow it to manage your filters for you — you have to ask for the permission during the login process. If you have a lot of accounts, you can leave a reminder in the Reference name you enter.

ga-accounts-wizard

Filter Definitions

We need the filter definitions for the filters we want to save, and we can get these from existing filters in one of our accounts. Using the Management Lists – Filters function, we can dump a full list of filters in our account to a worksheet.

ga-management-lists-filters

From the list, we can delete the rows we don’t want and keep just the filters we want — in this case, Spam Crawlers and Spam Crawlers 2. You can change any of the parameters manually, possibly updating this as a ‘Master List’, but do not change the column titles.

ga-filters-list

If you want to create a new filter, you can simply create one in one of your accounts and save it to a worksheet like we did here, then copy the row to the list (aligning the columns, in case they are different).

ga-filters-list2

Target Accounts/Views

The other information we need is the list of Accounts and Views that we want to copy the filters into. Note that with Google Analytics, View filters are stored at the Account level and linked to a View. Even if you create the filter in the View, it actually adds it to the Account and links it to that View for you. That makes it possible to use the same filter on other Views in the same Account.

The easiest way to get this list is to dump an Account Summary using the Management Lists function. As above, delete the rows you don’t want to save the filter into and keep the ones you want.

ga-account-summary-results

Just remember, do not change the column titles.

ga-account-summary-results2

Saving the Filters

This final step is the easiest. Open the Save Filter wizard, select or enter the location of the filter definitions, select the login account to use, and select or enter the location of the target accounts and views. The buttons to the right of the entry boxes allow you to pick the location of the data right in Excel — select the top-left cell of the table of data.

ga-save-filter-example

Note the three options at the bottom:

Overwrite existing filter in Account – this options looks for a filter with the same name in the target Account, and if found, overwrite the filter definition with a new one. If the filter has not yet been linked to the selected View, a new link is created. This options is useful when you want the same filter everywhere.

Overwrite existing filter in View – this option looks for a filter with the same name in the target Account AND a link to the selected View. If you are saving to multiple Views in the same Account, this option will create a unique copy of the filter (with the same name) in the target Account with a link to a specific View. This option is useful when you might want different filters in some Views.

Do not overwrite existing filter – this option creates a new filter in the Account and links it to the View for each entry. This option is useful when adding a filter and do not want to mistakenly overwrite a previous filter that just happens to have the same name.

Understanding the Results

The response from the function is a bit cryptic, but here’s what it means:

0,Spam Crawlers,EXCLUDE,xxxxxxx,UA-xxxxxxx-1,76111500,17566011,Existing filter OK,New view link
 0,Spam Crawlers,EXCLUDE,xxxxxxx,UA-xxxxxxx-1,76063423,17566011,Existing filter OK,Existing view link OK
 0,Spam Crawlers,EXCLUDE,xxxxxxx,UA-xxxxxxx-1,96933237,,FAILED copy to account !Error: 403 User does not have sufficient permissions for this account.,

For each filter to be copied, there is a row for each target Account-View showing the status of the save operation. The contents of each row is a number, the filter name, the filter type. the account, property and view ids, and a couple of status messages as below:

New filter id ###### – a new filter was created in the target account.

Updated Filter – the existing filter in the target account was updated.

Existing Filter OK – the filter already existed and did not need to be updated.

New view link – a new link was created between the filter and the target view.

Existing view link OK – the target view was already linked to the filter.

FAILED copy to account !Error:… – and error occurred trying to copy the filter into the target account. Usually this is a permission error caused by using a login that does not have the necessary access to the target account.

 

 

Tracking Multiple Websites

If you have multiple websites that you manage, and struggle to keep an eye on all of them at once, here’s how to create a quick report to compare their day-to-day traffic at a glance. It is made possible with the Google Analytics Pro connector from Analytics Edge — you can use it with either the free Basic add-in or the Core add-in for Excel.

ga-pro-accountsStart with a couple of predefined worksheets; one call Queries (you can use any name) and the other called Results (you can use any name).

If you haven’t yet done so, create an account entry using the Accounts wizard. Follow the prompts to login and grant access to the Analytics Edge application. Note that you may get an email from Google mentioning that you just logged-in from Internet Explorer — that’s OK; Analytics Edge uses an embedded IE browser.

Create a New Bulk Query

ga-bulk-queriesOpen the Bulk Queries wizard. Select the Queries worksheet to hold your query configuration data. Note that this worksheet should not be used for any other purpose, and it can be hidden is desired.

Click the +New Query button to create a new bulk query. The wizard will load account summary information for your default login.

Defining the Query

The wizard will present you with a number of tabs to configure your query. Start by assigning a unique name for the query. Use something meaningful but not too long. Then select a Location for the query results — click the button to select a worksheet cell, or just enter the location using the format of WorksheetName!A1.

ga-new-bulk-query

Using the Account / Property / View selectors, click the Add > button to add each of the web site views you want to the list on the right side.

Selecting Fields

For this particular report, we want to display the total number of sessions per week for multiple websites, so select the Week of Year dimension and the Sessions metric. If you want daily, then select the Date dimension instead; monthly, select Month of Year instead.

In the Pivot by selector, select your period dimension — Week of Year in this case.

ga-select-fields

Picking the Date Range

To select the date range for the query, click the Dates tab. Analytics Edge lets you select dates in a wide number of combinations and methods. In this case, we will select a duration of 13 weeks ending Last Week. That way, every time we refresh the query it will go back 13 weeks from today’s date; we don’t have to adjust the start and ending dates.

ga-select-date-range

Note that if you prefer weeks starting Monday, use the dimension ISO Week of ISO Year and ending date of Last ISOweek. For months, select a duration of a number of months, ending Last Month. For daily reports, you can query any number of days, ending Yesterday or Today (or any other option).

Avoiding Sampling

If your date range is long and will encompass a lot more than 500,000 sessions in your web properties, you can avoid the dreaded Google Analytics data sampling errors by selecting the option to “minimize sampling” on the Options tab. Caution: This will issue one API query for each week (day/month) for each website in your query. If you are trying to get 52 weeks of data for 20 websites, that will be 52 x 20 = 1,040 queries which could take a little while. Analytics Edge will display a query progress dialog during this time.

ga-minimize-sampling

Displaying the Results

The results of the queries will be written to the worksheet location selected for the query; in this case, the Results worksheet. You can use simple Excel conditional formatting to provide some quick visualization of trends in the data, or create an Excel multi-line chart. The choice is up to you.

Next week, just refresh the query for a quick overview of your websites health. Analytics Edge makes is simple.

ga-website-trends

Save Filter

ga-save-filter[Pro connector] Use this Analytics Edge wizard to save one or more view filters to one or more Google Analytics views.

The filter(s) must be defined in a worksheet using columns that match what is generated by the Management Lists – Filter function.

The Login to be used must either be selected or obtained from a worksheet link. NOTE: only logins that have been created with Manage access checked in the Account wizard will work!

The target Google Analytics accounts and views must be listed in a worksheet using columns that match what is generated by the Management Lists – Account Summary function.

Overwrite existing filter in Account – this options looks for a filter with the same name in the target Account, and if found, overwrite the filter definition with a new one. If the filter has not yet been linked to the selected View, a new link is created. This options is useful when you want the same filter everywhere.

Overwrite existing filter in View – this option looks for a filter with the same name in the target Account AND a link to the selected View. If you are saving to multiple Views in the same Account, this option will create a unique copy of the filter (with the same name) in the target Account with a link to a specific View. This option is useful when you might want different filters in some Views.

Do not overwrite existing filter – this option creates a new filter in the Account and links it to the View for each entry. This option is useful when adding a filter and do not want to mistakenly overwrite a previous filter that just happens to have the same name.

For more information, see Using the Save Filter Function

 

Bulk Queries – Queries

ga-bulk-queries

Click image to enlarge

Use this Analytics Edge wizard to select the Query Configuration Worksheet for Bulk (multiple) Google Analytics queries. Each column on the query configuration worksheet identifies a different query to be run and the worksheet / cell location where the results should be written.

+ New Query – create a new query using the wizard. Queries can also be created manually in the worksheet.

Name (click to edit) – lists the existing queries. Click on the link (name) to open the query for editing using the wizard interface. Queries can also be edited manually in the worksheet.

Location – identifies where the query results will be written, in the form of ‘worksheet’!cell.

ga-bulk-query-configurationQuery Configuration Worksheet

The Query Configuration Worksheet contains a separate column for each query. You can create and edit the queries using the wizard, or you can manually edit the worksheet as you require. Cell values can be manually entered or you can use an Excel formula (such as =TODAY()-1 for an end date).

Note that if you use formulas, the wizard will not modify them if you edit the query using the wizard interface.

To understand what values can be used for each parameter, use the wizard to construct your query. Some of the more advanced parameters may require understanding of the Google Analytics API.

Reference: https://developers.google.com/analytics/devguides/reporting/core/v3/reference

Bulk Queries – Options

ga-bulk-optionsThe Options tab provides access to several options that affect how the Google Analytics data is presented in Excel.

Rates/Percents: By default, Analytics Edge will convert rate and percentage metrics into fractions of 1 (50% = 0.5) so that the cells can be formatted using Excel’s % formatting. If you would rather see 50 in the cell, select Number.

Dates: Analytics Edge automatically converts dates into Excel dates. If you would rather get the non-standard response (20140725) format, select String/Number.

Numeric Dimensions: By default, all dimensions are delivered to Excel in text-formatted cells. Some dimensions, like Count of Sessions are numeric in nature. If you would like to see these “numeric” dimensions presented as numbers, select Number.

Time Metrics: by default, time metrics like Time on Page and Sessions Duration are expressed in seconds. Select Days to make it possible to format the cell in Excel using a Time format to get hh:mm:ss formatted times.

Sampled Data Options 

If your query is subject to data sampling, you can choose to be warned of this happening by checking the box ‘warn if results contain sampled data‘.

You can minimize sampling errors by reducing the time-span of the query, or include a time dimension and check the box to ‘minimize sampling‘. Doing this will cause Analytics Edge to make queries for the smallest time dimension in your query. If you include Date ( or Day Index, or Day of Month) then it will query for each day. Similarly for any Week, ISO Week, Month or Year dimension. If you include more than one time dimension, the smallest one will determine the query resolution. Note that there is no restriction or special handling required with other dimensions or metrics in your query. Be aware that there is a daily limit of 10,000 requests per View.

Bulk Queries – Segments

ga-bulk-segmentsThis Analytics Edge wizard is used to select a predefined Google Analytics segment from your account, or to define a dynamic one to be used solely for this query.

All system-provided segments are listed first, followed by your custom segments, alphabetically sorted.

When you select DYNAMIC from the drop-down, you can enter a segment expression in the text box below. Any expression can be entered — click the reference link in the wizard to open the Google Analytics API reference guide for segments.

Bulk Queries – Sort

ga-bulk-sortUse this Analytics Edge wizard to select the sort order to use for the query. Additionally, you can restrict the query to download a limited number of rows i.e. the top 10.

To sort on multiple columns, select each column in turn and click either Ascending or Descending. Columns will be sorted in the order entered.

Although Google’s API returns only 10,000 rows per query, Analytics Edge will transparently make multiple queries to get all your results. You can limit the query by entering a number other than zero.

Bulk Queries – Dates

ga-bulk-dates[Pro connector] Use this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 Days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Finally, you can select to use the date range chosen, or use the same duration period previous to the chosen date range, or use the same period from the previous year.

Bulk Queries – Filters

ga-bulk-filtersUse this Analytics Edge wizard to define filters to use with the query.

Select the Dimension or Metric, then pick a comparison operator and enter a value. Click the Add button. The filter will appear in the list at the bottom.

Use AND or OR logic to join multiple conditions in a single filter.

Note that all metrics and dimensions used must be valid when used in combination with the metrics and dimensions of the query itself.

Bulk Queries – Fields

ga-bulk-fieldsUse this Analytics Edge wizard to select the dimensions and metrics to be retrieved in the query.

Select a field in the listing on the left and click the [ >> ] button, or double-click on the field name to add it to the selected field list on the right. Dimensions and metrics are managed separately. You can add up to 7 dimensions and 10 metrics in a query.

Not all combinations are valid. To see Google’s authoritative listing, click the link provided on the wizard. Invalid selections will appear gray in the list.

Items marked with an asterisk (*) are deprecated by Google: this means that while they still work for now, they will stop working at a future date. In most cases, there is a second metric or dimension with the same or similar name. e.g. “Users” refers to the API field ga:users, but “Users*” refers to the old ga:visitors API field, which has been replaced by ga:users.

Pivot

After selecting your dimensions for the query, you can select one of those dimensions to segment the results into columns. For example, you can select the Date dimension to obtain one column per Date in the results.

Note that the columns will be created in the order in which they appear in the query, so changing your sort order may affect the order of the columns. Also, there will be one column for each unique value, so absolute column position may change from query to query.

For more control over pivoted resulted, use the Pivot function in the Analytics Edge Core Add-in.

Bulk Queries – Views

ga-bulk-viewsUse this Analytics Edge wizard to select the Login account to use for the query, plus the Account, Property, and View.

Click Add> to add the selected View to the query. Multiple Views can be added, but they must all be available from the same Login.

 

New sign-in from Internet Explorer on Windows

If you just logged into Google Analytics from the Analytics Edge connector, you may get a warning email from Google, confirming the action you just took. Analytics Edge uses an embedded Internet Explorer browser for the authentication process. Unfortunately, so does a lot of virus and trojan software. Google is playing it safe. Thank you.

new sign in

Building a Cohort Analysis

This article explains how to build a cohort analysis report in Excel using the Analytics Edge free Basic Add-in and Google Analytics connector. Cohort Analysis lets you see how one group of people behaved differently from another. A “cohort” is simply a group of people that share a common characteristic, such as the date they first visited your website, or whether they clicked on a specific campaign, or those people that came from a particular referral source. Continue reading

Multi-View Queries [Core Add-in]

The Pro version of the Analytics Edge connector for Google Analytics can be used to query multiple profiles/views at once. To do this, you need the Analytics Edge Core Add-in.

Start by loading the list of view ids into memory. To get the view ids available to you, you can use a Google Analytics Pro – Management Lists query to download all views available to your account.

account-summary-query

 

You can use the Analytics Edge Table – Filter function to keep the ones of interest

OR

use the File – Write Worksheet function to keep the whole list and manually remove the ones you don’t want, then reload the list using the File – Read Worksheet function. Note that the ‘webProperties/profiles/id’ column lists the View IDs we need.

account-summary

 

Now you can use that list in the Google Analytics Pro connector by entering the Analytics Edge range name ‘[webProperties/profiles/id]‘, which is the column name surrounded by square brackets.

multi-view-query

 

Continue to build your query. When it is run, 2 new columns will be added to the results: the View ID and the View Name.

multi-view-query-results

 

Remember to save the result to your workbook using the File – Write Worksheet function.

03 Your First Query

Open the Analytics Reporting Wizard

Form the Analytics Edge ribbon, pick the Google Analytics > Analytics Reporting menu to open the query wizard.  You must select a View, at least one metric Field, and a Date range for a valid query, and there are many other parameters available.

views-selectionSelect a View

On the Views tab, select a Login, then click the View you want to use — it will highlight in yellow, and information about the view will be displayed to the right.

Select Fields

On the Fields tab, you must can select up to 7 dimensions and 10 metrics. Select the field you want, then click the Add button (or double-click on the field name) to add the field to the selected lists to the right.

fields-selection2

You can type in the filter box to find specific fields, and restrict the list to Dimension or Metrics by clicking the options at the top. Deprecated fields are ones that Google doesn’t want you to use any more, and they are hidden by default.

Select a Date Range

On the Dates tab, select one of the Preset date ranges or use a combination of date options. Date ranges can be specified with a Start and End, Start and Duration, or Duration and End.

Start and End dates can be selected from the date selectors, picked from a list of preset items (like Today or Last Month), or a value can be entered. The button to the right of the value box can be used to create an Excel cell reference, so Analytics Edge will use the date in that cell when the query is refreshed.

dates-selection

Click Finish to run your query. Note that the Finish button will be disabled if you have not selected a View, selected at least one metric, and picked a date range.

01 Orientation Video

See how easy it is to get your Google Analytics data in Excel!  Watch Fullscreen

This video provides an overview of the Analytics Edge connector for Google Analytics, used from the free Analytics Edge Basic Add-in. Upgrade to the Analytics Edge Core Add-in for even more reporting automation possibilities.

Enter Data Source Schema

ga-enter-schemaEntering the Google Analytics data source schema into Analytics Edge makes it easier to upload data by automatically populating the target column names to match the schema.

Copy the schema from the Google Analytics Admin interface for the specific data source, and paste it into the box provided.

Upload Data into Google Analytics

ga-upload-dataCAUTION: read the information in Google Analytics Help regarding data upload before using the Analytics Edge upload feature.

To upload data into Google Analytics, select the Property and Custom Data Source (see Google Analytics help for details about custom data sources and uploading data).

You can enter the schema for the data source by clicking the button provided. You can also import any custom dimension and metric definitions to make it easier to map the columns.

Select the source of the data to be uploaded, then map the various columns from the source to the columns in the schema. NOTE: you need to match the schema of your data source; by default, Analytics Edge will list all possible fields. if you use fields that are not part of the selected data source schema, the extra columns will not be uploaded.

Once you have mapped all of the fields, click Finish. A dialog will appear giving you a 5-second countdown to cancel the upload.  To delete a upload after it has been completed, you will need to do so from the Google Analytics Admin interface.

Management Lists

ga-management-listsThe Management Lists wizard allows you to download information about your account(s), web properties and views. Select the Login to use for the query, then pick the Account, Web Property and View (Profile) to use for the query. Then select the report to run from the list at the left.

If you select ALL for the account, property or view, you will get listings for all your accounts, properties and/or views that match.

Analytics Reporting – Dynamic Segments

dynamic-segmentsAnalytics Edge makes it possible to create dynamic segments using a a wizard interface similar to that in the web version of Google Analytics. Select the category to the left, then select the options you want in your segment to the right.

You can add multiple conditions in a single segment, and all conditions must be met for a session to be included in the condition.

It is also possible to enter a Custom expression to pass direct to the API &segment= parameter (must be properly formatted). This can entered directly into the wizard, or an Excel cell reference can be used to a worksheet location.

Analytics Reporting – Options

The Options tab provides access to several options that affect how the Google Analytics data is presented in Excel.

Rates/Percents: By default, Analytics Edge will convert rate and percentage metrics into fractions of 1 (50% = 0.5) so that the cells can be formatted using Excel’s % formatting. If you would rather see 50 in the cell, select Number.

Dates: Analytics Edge automatically converts dates into Excel dates. If you would rather get the non-standard response (20140725) format, select String/Number.

Numeric Dimensions: By default, all dimensions are delivered to Excel in text-formatted cells. Some dimensions, like Count of Sessions are numeric in nature. If you would like to see these “numeric” dimensions presented as numbers, select Number.

Time Metrics: by default, time metrics like Time on Page and Sessions Duration are expressed in seconds. Select Days to make it possible to format the cell in Excel using a Time format to get hh:mm:ss formatted times.

Include Empty Rows: by default the GA API returns rows in some queries with zero results. You can suppress these rows with this option.

Sampled Data Options 

You can minimize sampling errors by reducing the time-span of the query, or include a time dimension and check the box to ‘minimize sampling‘. Doing this will cause Analytics Edge to make queries for the smallest time dimension in your query. If you include Date ( or Day Index, or Day of Month) then it will query for each day. Similarly for any Week, ISO Week, Month or Year dimension. If you include more than one time dimension, the smallest one will determine the query resolution. Note that there is no restriction or special handling required with other dimensions or metrics in your query. Be aware that there is a daily limit of 10,000 requests per View.

minimize-sampling

If your query is subject to data sampling, you can choose to be warned of this happening by checking the box ‘Warn if results contain sampled data‘.

Check Include columns with sample size to add columns showing the sample size and sample space (total number of sessions represented), as well as a True/False column for contains sampled data.

 

Convert Excellent Analytics

[Only available in the Free Google Analytics connector]

Use this wizard to convert existing queries built with Excellent Analytics into Analytics Edge queries. Select the cell below the Excellent Analytics query cell and open the wizard.

convert-excellent-analytics-1

The Excellent Analytics query will be detected and displayed. Select the Google Analytics Login that has access to the web property in the query, and click Convert.

convert-excellent-analytics-2

With the Analytics Edge Basic Addin

analytics-edge-conversion-of-excellent-analyticsProgress of the conversion will be displayed. If successful, Analytics will run the query and update the results in the worksheet when you click the Close button.

To edit your query, select the cell with the comment and click the Edit Query button on the Analytics Edge ribbon bar.

With the Analytics Edge Core Addin

convert-excellent-analytics-4Progress of the conversion will be displayed. If successful, Analytics will run the query and update the results in a temporary worksheet when you click the Close button.  From the Analytics Edge ribbon bar, click File – Write to Current Cell and the results will be put into your report.

 

Google Analytics Accounts

Use this wizard to log in to your Google Analytics account and authorize Analytics Edge to access your data. Enter a name for the account you want to login with, then click the Add Account button.

new-account

Multiple accounts can be stored, and you can change which account gets used by default. If your reports make multiple queries and always refer to the ‘Default account’, you can switch the default account to report on on different account.

[Pro version: if you want to be able to Upload Data into the account or Save Filters, check the box below the reference name before you Add the account to obtain elevated authorizations necessary.]

Select an account from the list, select a web property and view, and click Make Default to set that view to use as default for that login.

Use the ‘Save Default in Macro’ button to force a specific workbook macro to always use a specific account as the default.

MANUAL LOGIN

If you experience a problem with the login, click the Manual link in the dialog. A new browser window will open. Login to your Google Analytics account and Accept the access request. You will be taken to a web page on the Analytics Edge web site, and there should be a code in the URL of the browser. Copy the code part of the URL, close the browser, and paste the code into the dialog box provided.

ga-manual-auth

 

Tips for Google Analytics Reporting

Analytics Edge makes it easy to get data from Google Analytics into Excel, and here are a few tips to make it easier to build impressive reports.

rates-and-averagesRates and Averages

Any of the percent, rate or average metrics downloaded from Google Analytics will be automatically delivered as portions of 1 (so 50% will appear as 0.5). This allows you to use Excel’s Percentage cell formatting.

Also, do not get carried away with too many numbers after the decimal; one is usually enough (63.4%), two if the numbers are under 10% (6.34%). Web analytics is not an exact science.

displaying-timeDisplaying Time in hours:minutes:seconds

Time metrics like Session Duration in Google Analytics is reported in seconds. To display it in the more familiar time format, you need to first convert the seconds to a fraction of a day; =cell/60/60/24 (60 seconds/minute, 60 minutes/hour, 24 hours/day). Then you can use the Excel custom date format option and enter hh:mm:ss to get the display desired.

good-and-badRemember Good and Bad

If you are using conditional formatting to highlight metric trends, remember that some metrics like Bounce Rate may be considered worse, not better, if they are larger.

If you do use conditional formatting, consider making them red, or use a custom scale that shifts from green to yellow to red at specific thresholds consistent with your website goals.

 

Analytics Reporting – Segments

segmentsThis Analytics Edge wizard is used to select a predefined Google Analytics segment from your account, or to define a dynamic one to be used solely for this query.

All system-provided segments are listed first, followed by your custom segments, alphabetically sorted.

When you select DYNAMIC from the drop-down, a second wizard will open to guide you through the next steps. To edit a dynamic segment, click the button provided.

 

MCF Reporting – Dates

ga-mcf-datesUse this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Analytics Reporting – Sort

sortUse this Analytics Edge wizard to select the sort order to use for the query. Additionally, you can restrict the query to download a limited number of rows i.e. the top 10.

To sort on multiple columns, select each column in turn and click either Ascending or Descending. Columns will be sorted in the order entered.

Although Google’s API returns only 10,000 rows per query, Analytics Edge will transparently make multiple queries to get all your results. You can limit the query by entering a number other than zero.

Analytics Reporting – Dates

datesUse this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Finally, you can select to use the date range chosen, or use the same duration period previous to the chosen date range, or use the same period from the previous year.

Analytics Reporting – Filters

filtersUse this Analytics Edge wizard to define filters to use with the query.

Select a Dimension or Metric, then pick a Comparison operator and enter a Value. Click the Add button. The filter will appear in the list at the bottom.

Use AND or OR logic to join multiple conditions in a single filter.

Note that all the metrics and dimensions used must be valid when used in combination with the metrics and dimensions of the query itself.

Use a Cell Reference

For advanced users, you can use a filter expression stored in a worksheet, BUT the expression must be encoded exactly as the API expects it to be.  See the API documentation for details. For example, to filter for City equals Ottawa, the expression would be ‘ga:city%3D%3DOttawa’.

To use a cell reference, click the button to the right of the entry box, then click the cell in the worksheet where the filter expression is stored.

 

Analytics Reporting – Fields

fieldsUse this Analytics Edge wizard to select the dimensions and metrics to be retrieved in the query.

Add/Remove Fields: Select a field in the listing on the left and click the Add button, or double-click on the field name to add it to the selected field list on the right. Dimensions and metrics are managed separately. You can add up to 7 dimensions and 10 metrics in a query.

Invalid Combinations: Not all combinations are valid, and Analytics Edge will warn you of invalid combinations by graying out some entries as you add fields.

Deprecated: Items marked with an asterisk (*) are deprecated by Google: this means that while they still work for now, they will stop working at a future date. By default, deprecated fields are hidden but they can be included by selecting the check box.

Descriptions: Selecting any field will show the associate API field name and description at the bottom.

Filter: to filter the list of dimensions and metrics, simply type a few characters into the filter box provided. The filter can match the display name or the actual API field name.

Pivot (Pro Connector Only): After selecting your dimensions for the query, you can select one of those dimensions to segment the results into columns. For example, you can select the Date dimension to obtain one column per Date in the results.

Note that the columns will be created in the order in which they appear in the query, so changing your sort order may affect the order of the columns. Also, there will be one column for each unique value, so absolute column position may change from query to query.

For more control over pivoted resulted, use the Pivot function in the Analytics Edge Core Add-in.

 

Analytics Reporting – Views

viewsUse this Analytics Edge wizard to select the Login account and the reporting View to use for the query.

You can enter a view (profile) id directly, or click the cell reference button and select a cell in the workbook to get the value from. View ID’s are the numbers displayed in the View (profile) selector. e.g. 76063423.

[Pro version: you can also enter an Analytics Edge [range] reference (Core Add-in required) to a column of view id values, and the connector will query multiple views and deliver the results in a single response, prefixed by a View ID and View Name column.]

* Google Analytics Help

google-analytics-pro-menuThe Analytics Edge connector for Google Analytics comes in 2 variants: Free and Pro. They share the same account and configuration files, so it is possible to switch between them or to start with the Free version and upgrade to the Pro without having to rebuild your reports.

Before you can make a query, you must log in through the Accounts wizard.

The Free version has some amazing capabilities, but it cannot do some things. The Pro version can also:

  • perform multi-view queries
  • access the Management Lists
  • access Multi-Channel Funnel data
  • Upload data
  • Save Filters
  • has full support of custom dimensions and metrics
  • can access the extra custom dimensions and metric available to GA Premium users

 

Configuration – Accounts

Before you can use the connector, you MUST log in to a valid a Google Analytics identity. Both connectors support multiple logins, and you can mix queries from different logins in the same workbook. One of the logins is designated as a Default, and is identified with an asterisk (*).

Select Accounts from the connector’s menu to open the Accounts wizard.

Enter a Reference Name for the account and click the Add Account button. You will step through a Google Analytics login sequence to authorize Analytics Edge to access your account. *

new-account

Select your login in the list, then pick an Account/Property/View combination and click the Make Default button.

* A note about security: your account credentials are stored, strongly encrypted, in a file on your computer and are sent direct to Google’s servers using an encrypted https link. Your credentials are not stored in your workbooks, making them safe to share. Analytics Edge (the company) has no access to your account information.

Configuration – Purchased License

google-analytics-pro-licenseThe connector will automatically register. The Pro version will operate free for 30 days, after which time a license must be purchased.

To enter a license code, select License from the connector’s menu and enter your code.