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 technical how-to article. More
If you’ve written a few lines of script and aren’t afraid of words like “variables”, this guide will get you going quickly with Analytics Edge macros. Even if you’ve just played with a few Excel formulas, you might surprise yourself with how much you can do with Analytics Edge macros and how FAST it gets done! More
What is this green worksheet with the message “ANALYTICS EDGE – active table in memory…“? When you are editing a query or stepping through an Analytics Edge macro, a green worksheet (ᴁ Step Results) will appear. This temporary worksheet shows you what it is currently in memory — the results of the last function step — before you formally write it out to a worksheet.
Let’s say you have an Analytics Edge workbook that uses a Repeat Macro function to loop through several web properties, each writing to a separate worksheet. Since Analytics Edge doesn’t help you with worksheet formatting much, let’s say you whipped up a little VBA code to do it. How would you integrate that VBA code into the Analytics Edge macro? More
The Analytics Edge Core Add-in allows you to place Quick Queries anywhere in your workbook, but all those queries can be edited in a single place: the ‘ᴁ Analytics Edge Queries’ worksheet. Caution is strongly recommended–make a backup of your workbook before you start! More
A common reporting challenge in Excel is merging sets of data, such as combining monthly numbers for a quarterly or annual total. Adding up simple metrics is obvious, but what do you do with things like the average position or conversion rate? [Hint: you should not average or total anything that is already an average or ratio] More
Analytics Edge makes it easy to combine data from different sources in the same report. This article describes one of our free workbooks that combines data from Google Analytics with Google Webmaster Tools and Moz. More
Constant Contact offers some simple reports on your autoresponder campaigns, but little detail is available. Even through the API, the data is difficult to extract, but it can be done. This article details how Analytics Edge makes it possible to monitor your email series to see who each message was sent to, which ones bounced, whether they were opened, and whether they clicked on a link.
With Analytics Edge, your reporting gets a whole lot easier with single-click refreshes, and you don’t lose any of the flexibility that makes Microsoft Excel so popular for Constant Contact analytics reporting. Here’s an example of one engagement that went from idea to “This is AMAZING!!” in 24 hours. More
This is a PROVEN WORKING SOLUTION to remove referral spam in your Google Analytics. Updated for 2021: Bothered by referral spam from the likes of trafficnow.club, bot-traffic.xyz or other domains? Implement a Custom Segment as described in #3 below or, if you catch the spam traffic on the day it happens, a Spam Crawler Filter (#4). More
This Google AdWords API error occurs when you try to query an old account that has not been active in over 15 months. The API will not permit queries to inactive accounts. This usually occurs in agencies with long account lists, some of which are old and no longer used. More
This Google Analytics API error indicates that you are referencing an invalid view ID. The view ID is the number that appears beside the view in the Accounts wizard (Analytics Edge connector for Google Analytics), or in the Google Analytics web interface when you click on the account selector at the top. More
This Google Analytics API error indicates that the metrics and dimensions being requested cannot be used together. This usually happens when you are creating a custom report, and you added a combination of fields that the API rejects. More
This Google Analytics API error indicates that the security token for the account you are using has failed. Normally the security tokens are auto-renewed, but if you changed the account password or otherwise changed your security settings, the renewal can also fail. More
This Google Analytics API error means that the selected advanced segment is not available to the account you are using. This usually happens when you share a report with a coworker. Advanced segments in Google Analytics are personal assets and are associated with your login account. More
This Google Analytics API error indicates that the account you are using cannot access the Google Analytics view (profile). This usually happens when the Google Analytics account owner removes your access (a typical agency problem). More
This Google Analytics API error indicates that there was a problem processing your query request. This can occur if you make too many bad queries in a short period of time, or if you use use up your quota for the day. In some cases, it may indicate that the server was too busy to process your request (usually for a very large query). More
This error usually occurs when the cells you referenced in a query have moved or the contents are changed. Analytics Edge connectors offer the ability to use cell references for the dates, but the references can get out of alignment if you add rows or columns in the worksheet, or if you renamed the worksheet. More
Analytics Edge is licensed by computer, so the add-in keeps track of the computer it is licensed for (even the free product is licensed). Sometimes a Windows Update or hardware change will mess up the license, and you will get an error saying “License issued to a different computer”. More
With the surge in working-from-home employees, many people are trying to figure out how to exclude this “internal” traffic from their Google Analytics reports. There is a simple option for sites with low to moderate traffic. More
The Google Analytics Pro connector from Analytics Edge supports the extra dimensions and metrics available to Google Analytics 360 customers. It also supports the new resource quotas, allowing GA 360 customers to get unsampled reports up to 100 million sessions! More
FAQ: do NOT encode the “ga:” in the field names of your expression!
Custom filter expressions can be used in the Google Analytics connectors, but there are a number of rules you must follow. This is documented in Google’s API Reference, but it is part of the old v3 API documentation, which could be removed at any time, so I have copied and embellished it here.
One of the features of the Google Analytics connector is the ability to create a custom dynamic segment, which is really useful when you are sharing workbooks with others. Custom segments in Google Analytics are stored as personal assets — they are associated with the login account, not the Google Analytics account. Because of that, using one of your custom segments in a query means other people cannot run your query; they will get an error when they try to refresh the query. More
Google Analytics provides a New vs Returning report to measure the pull of your site and the extent to which you are encouraging first time users to return. Making this report with Analytics Edge allows you to easily track these metrics the way you want to see. More
If you find your Google Analytics reports won’t download any data beyond 25 months, chances are this is due to your data retention settings. By default, they are set to purge after 26 months.
To change this, go into your Google Analytics Admin settings for your account: Property Settings > Tracking Info > Data Retention.
This video shows how easily multiple queries can be created and the results combined to produce a detailed report, in this case, grabbing the top sources for each of the top landing pages on your site. More
The filtering capability in Google Search Console is very restrictive (single phrase match), making it useless for separating branded from non-branded search terms in all but the simplest cases. Enter the Analytics Edge Core Add-in — a quick regular expression filter and your work is done. More
If you manage several large websites, the Analytics Edge Core Add-in allows you to build an efficient template for separating branded and non-branded search queries from the Google Search connector. More
Downloading your Google Search Console data into Excel is easy with Analytics Edge. The Google Search Connector uses the API to pull down all of the query and page metrics — without the 1000 or 5000 row limit imposed by the web interface. More
The Google Search API now supports a full 16 months of historical data, but the only date-related field is ‘date’, so this is how to get all that data out in a meaningful way — in weekly or monthly buckets. The Google Search Connector from Analytics Edge provides a MultiQuery tab that does just what you need, and your appears in Excel in seconds! More
Building queries based on calendar periods is pretty straight forward, but when challenged to build a report using fiscal periods, the Analytics Edge cell references can to be used to get any fiscal period you need. The magic is figuring out a series of Excel formulas (or just manually entering them) for the fiscal dates. More
The Analytics Edge Core Add-in allows you to fully automate the refresh and delivery of report by email on a schedule of your choosing. It involves several steps:
- setup your email server options
- create the report you want to send
- add a macro with a Save/Email PDF or Workbook command
- schedule the report to run
In this series, I am sharing some of the stories that have shaped who I am, how I run my business,
and the products I have designed. I hope you can benefit from them as well.
A very long time ago, while I was in a university mechanical engineering class, we were challenged with a construction project: build a structure out of balsa wood, string and glue that would support a weight hanging from a string. Structures would be scored by a formula: weight carried/weight of the structure.
The designs produced by the various teams were quite varied, but the team I was on decided to take a very simple and minimal approach. Like good students, we applied our theory and started with a simple design, then we built it and tested it.
If you have never worked with balsa wood, it is a surprisingly robust material, but it has its weaknesses. It twists, it cracks along the grain easily. and like all woods, failure happen with a catastrophic “CRACK” sound. So we built our structure and tested it — it broke. So we built another one — it broke. And another…and another…and…
This was an engineering class, so when it broke, we had to do a failure analysis, applying our theory to explain why our design was flawed. Then we had to propose a modification to the design that “fixed” the flaw, and build and test it. We beefed up the weak points in the design, added some lateral stability to prevent it from twisting, changing how we attached the string at critical points, but our team tried to stay true to the simple design.
When we started, we were full of bravado about the simple task and trusting in our simple tension and compression formulas. With each failure, we grew increasingly skeptical of our formulas, and we learned the difference between theoretical and practical. We gained an appreciation for the term “safety factor”. About how a simple oversight could result in massive failures like “galloping gertie“.
Long-tail keyword phrases are often made up of many different combinations of similar words, making it difficult to tell when there is a common topic of interest. This article walks through the creation of a keyword analysis report, based on weekly snippets of data from Google Search Console. More
With the MailChimp Connector, Analytics Edge lets you automate more than just a data download – it lets you create a finished report that you can refresh with just a click. In this article, we’ll show how one click can get you a subscriber activity report for the most recent MailChimp campaign. More
“I want to increase the number of returning visitors.” Now you need a report to track your progress. This article will help you navigate Google Analytics to find the right data, and to produce a meaningful report using Analytics Edge. More
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…. More
The Google Analytics report for Frequency displays a Count of Sessions dimension that is misleading in several ways. More
Why do New + Returning Visitors add up to more than the total Users? How can I have more New Users than Users who are ‘New Visitors’? This Misunderstood Metrics article explores the Behavior > New vs Returning report in Google Analytics, and why the numbers can be confusing. More
How can the Average Session Duration be less than the Average Time on Page? In Google Analytics, seeing the time spent on your website or on individual pages is not as obvious as it first appears. This Misunderstood Metrics article explores the concept of time in Google Analytics…or lost time as the case may be. More
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.
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. More
When the Facebook Ads API returns an error 500, it is usually telling you that the combination of the number of metrics and rows of data being asked for is too large for them to process all at once. You may hit the limit around 1000 rows, or it could be higher or lower depending on the number of columns you are asking for. More
When analyzing website traffic, there are times you might want a full listing of the website’s pages…even those that never get any traffic or impressions. Google Analytics and Google Search Console will only report on pages were there is data, so these unrecognized pages continue to be overlooked. This article shows how the Analytics Edge Core Add-in can read the website’s sitemap files to extract a full listing of pages. More
With the [undocumented] addition of the Client ID dimension to the Google Analytics API, you can now use the Analytics Edge Core Add-in and Google Analytics Pro connector to recreate the User Explorer report in Google Analytics. More