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. To get your numbers in weekly or monthly buckets, the Google Search Connector from Analytics Edge provides a MultiQuery tab. More
How To Guides (filter at right)
Active table in memory?
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 will appear (AE Step Results). 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. More
Activity for the Latest MailChimp Campaign
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
Advanced Techniques: the Monthly PDF Report
This article describes some of the more advanced techniques used to create the free Excel workbook Google Analytics Monthly PDF Report. More
Append to Worksheet: Problems and Solutions
The Analytics Edge Standard and Core Add-ins allow you to append the results of a query to the existing data in a worksheet. While the function is simple, there are some scenarios where you can experience problems. This article describes the problems you could experience and the solutions.
Dimensions That Look Like Numbers
The Append function looks for columns that are not numbers, and it uses all of those columns to try to align the rows of the new query with the existing data in your worksheet. If a column, like Week of Year or Month of Year (e.g. 201810) is not formatted as Text, then the Append function will ignore the value when it aligns the rows — the result: mashed up numbers.
Solution: make sure the existing column of data is formatted as ‘Text’ in Excel (not ‘General’).
Dates That Aren’t Dates
Sometimes you get data that looks like a date, but it is actually text. In Excel, a date is actually stored as a ‘serial number’ and formatted to appear as a date. In some Analytics Edge connectors, dates can be downloaded as text strings, and it can be difficult to tell them apart. IF you are appending data with one data type into a worksheet with the other, strange things can happen (extra columns, improper row alignment).
Solution: In Excel, select the worksheet cell and check the format – if it shows ‘Date’, then you are dealing with a date. If the query column is Text, the add-in will try to convert it into a date, and things should work as expected.
Solution: If the worksheet data is formatted as Text, and it is not in the 2018-10-31 format, Analytics Edge will likely be unable to align the rows properly — you may get duplicate rows with different date formats (actually text strings). Change the existing data to a Date in Excel (how it is displayed as not important), or change the text to 2018-10-31 representation to be consistent with the connectors.
Basic Reporting: Google Analytics
This article gets you started with some simple Google Analytics reports with Analytics Edge. More
Branded Versus Non-Branded Search Terms
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 Standard and Core Add-ins — a quick regular expression filter and your work is done. More
Branded vs Non-Branded: Template for Large Sites
If you manage several large websites, the Analytics Edge Core Add-in allows you to build an efficient template for getting branded and non-branded search queries from the Google Search connector. More
Build an Email Cohort Analysis
This article explains how to build a cohort analysis report in Excel using the Analytics Edge Core Add-in with the Constant Contact connector. Cohort Analysis lets you see how one group of subscribers behaves differently from another. More
Building a Cohort Analysis
This article explains how to build a cohort analysis report in Excel using the Analytics Edge free Google Analytics connector. Cohort Analysis lets you see how one group of people behaved differently from another. More
Building a Marketing Dashboard in Excel
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. More
Building a Multi-Site Report
With the Analytics Edge Core Add-in for Excel, you can easily build a multi-website report, quickly and without programming. This article and the attached sample workbook show how easy it can be. More
Constant Contact Click-Through Data Analysis
In a follow-up to Downloading Constant Contact Click-Through Data, this article shows how Analytics Edge can easily turn the raw downloaded data into meaningful analytics without a lot of work or confusing formulas. More
Constant Contact Report Automation
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 reporting. Here’s an example of one engagement that went from idea to “This is AMAZING!!” in 24 hours. More
Creating an Excel Dashboard Widget
Creating a dashboard in Excel can be quite simple –it’s really just a matter of downloading the right data and some easy formatting techniques. Layout is your challenge, but even that can be overcome. More
Different Computer Error?
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
Download All Your Page And Query Data
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 row limit imposed by the web interface. More
Downloading Constant Contact Click-Through Data
Downloading Constant Contact click-through data for multiple clients has never been easier than with Analytics Edge and the Constant Contact connector. This article describes the process in detail, so you can get the job done fast! More
Formatting Notes
Once installed and registered, the Analytics Edge Basic Add-in can be used to quickly refresh multiple queries in your workbook. Simply select a destination cell and make a query, then repeat. More
Formatting Notes
Once installed and registered, the Analytics Edge Standard Add-in can be used to quickly refresh multiple queries in your workbook. More
Get Queries for All Top Pages
Here’s a short video showing how easy it is to download the Google Search queries for each of your top pages using the Analytics Edge Core Add-in and Google Search connector: less than 5 minutes of work, and no programming! More
Get Top Queries for Selected Pages
Similar to the Queries for Top Pages report, this article details the steps to create a custom report grabbing the top 5 queries for a selected set of pages [Requires the Analytics Edge Core Add-in and the Google Search Connector]. More
Get Top Sources for Top Landing Pages
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
Getting Started with the Constant Contact Connector
To get started with Analytics Edge and the Constant Contact connector, you need to install and register an Analytics Edge add-in. Then the Constant Contact connector needs to be installed — it will automatically activate a 30 day trial for you. More
Google Analytics New vs Returning Report
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
Google Search Quick SEO Analysis
See how quickly the Analytics Edge Core Add-in can transform a pile of Google Search data into a spreadsheet of actionable information (video 7:17). More
Google Search Trends Reporting
With the release of v4 of the Analytics Edge Google Search connector, you can now easily get week-to-week or month-to-month reporting in a single command. More
Hacking the Query Worksheet
The current release of the Analytics Edge Basic Add-in and Standard Add-in allows you to directly edit a worksheet containing all of the query details. This is an advanced topic with limited support. More
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. More
ISO Week Reporting
When a connector (API) does not offer the ability to make weekly reports based on international or ISO weeks (Monday to Sunday), you can use the Analytics Edge Core Add-in‘s Repeat Macro functionality to fill in the gap. More
Long-Tail Keyword Analysis (Advanced Example)
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
Making a CTR versus Position Chart By Device
The Analytics Edge Core Add-in is perfect for doing a quick analysis of search engine ranking data. In this article, I show how to get a CTR versus Position chart by Devices — showing the difference for click-thorough-rate of desktop versus mobile. More
Manually Scheduling a Refresh
If you want to refresh a workbook more than once a day, or if you simply want to control the refreshes yourself, this article shows how the Analytics Edge Core Add-in and Standard Add-in interacts with the Windows Task Scheduler app. More
Meaningful Metrics: New vs Returning Visitors in Google Analytics
“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
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. More
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. More
Optional Connectors
Analytics Edge has a variety of connectors available. They can be installed, updated and uninstalled using the License wizard. When you add a paid connector, it automatically starts a 30-day free trial period. More
Problems With A Macro? How To Get Help
If you have problems developing a macro in Analytics Edge, I am here to help [support@analyticsedge.com]. Two things will make the process faster and more effective. More
Reporting Multiple Google Analytics Segments
When you want to combine two or more queries, such as multiple Google Analytics segments, the Analytics Edge Core Add-in provides the tools to automate it. More
Running Macros from Buttons
With v2.6.0 of the Analytics Edge Core Add-in, it is now possible to run a specific Analytics Edge macro from a button on your worksheet (the Analytics Edge Core Add-in must be installed). More
Running Macros from VBA
With Analytics Edge Core Add-in v2.7.2 and higher, you can run an Analytics Edge macro (e.g. “MyMacro”) from Visual Basic for Applications (VBA), using code More
Running Queries from Buttons
With v2.0.0 of the Analytics Edge Standard Add-in, it is now possible to run a specific Analytics Edge query from a button on your worksheet (the Analytics Edge Standard Add-in must be installed). More
Selecting a Date Range
In most Analytics Edge wizards, you can select a data range for your query. There are a large number of combinations possible, but here are the most common selections. More
Simple Google Search Console Query
See how quickly (20 seconds) you can get your Google Search Console data into Microsoft Excel using Analytics Edge products. (video 1:01) More
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. More
Splitting a Column Into Multiple Rows
It may sound like a simple request, but splitting a column into rows is something you can’t do in Microsoft Excel without resorting to Visual Basic programming or using an add-in, like the Analytics Edge Core Add-in. More
Terminology
The Analytics Edge Core Add-in uses some common Microsoft Excel terminology, but the meanings are slightly different. More
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. More
Unsampled Data From Google Analytics For Free
Google Analytics sampling can really mess up a custom Excel report! 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. More
Upgrading from Basic Add-in to Core Add-in
If you have used the Analytics Edge Basic Add-in or Standard Add-in, and are moving up to the paid Analytics Edge Core Add-in, you will notice that things do not work the same way. More
Using Excel Tables With Analytics Edge
It may not be obvious, but the Analytics Edge add-ins support the use of Excel Tables (and linked PivotTables). Not sure what an Excel Table is? Chandoo.org has a good introductory article to this powerful Excel feature. More
Using Functions in Macros
The real power of the Analytics Edge Core Add-in are the other functions that let you sort, filter or pivot your data. You can convert text to dates, and dates to first-of-the-month. More
Using the Append Function
The Analytics Edge add-in lets you easily append data from one table to another, aligning the common columns but appending the new rows to the bottom. More
Using the Arrange Function
The Analytics Edge add-in for Microsoft Excel lets you easily rearrange or rename columns in your report. More
Using the Combine Function
The Analytics Edge add-in lets you easily combine data from two tables, aligning the common rows in key columns, and aggregating the cell values in other columns. More
Using the Compare Function
The Analytics Edge add-in lets you easily compare data from one table to another, calculating growth or change in numbers as well as time differences in dates. More
Using the Duplicates Function
The Analytics Edge add-in for Microsoft Excel lets you easily combine rows with identical values in some of the columns. More
Using the Filter Function
The Analytics Edge add-in for Excel lets you easily filter data in a table. More
Using the Match Function
The Analytics Edge add-in lets you keep or remove rows from one table if matching values exist in another. More
Using the Pivot Function
The Analytics Edge add-in for Excel lets you easily pivot data without the complexity of an Excel pivot table. More
Using the Read Worksheet Function
With the Analytics Edge add-in for Microsoft Excel, you can easily include data from existing worksheets in your automation macros. More
Using the Repeat Macro Function
The Repeat Macro function in the Analytics Edge Core Add-in lets you cycle through a list of items. This makes it possible to report on:
- multiple accounts
- multiple web sites
- multiple filter expressions
- multiple date ranges
This article shows you how to use it.
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. More
Using the Update Function
The Analytics Edge add-in lets you easily update one table with values from matching rows of another table. More
Using Worksheet Values to Determine Reporting Period
This article explores how to use date values in your Excel workbook to drive Analytics Edge connector queries, opening the door to a more interactive reporting experience within Excel. More
Write the Results into Excel
The Analytics Edge Core Add-in is holding your query results in memory, where you can easily manipulate it with other Analytics Edge functions. When you are ready, you will want to write the results into an Excel worksheet. More
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. More
Year-Over-Year Reporting
If you are trying to build a Google Analytics report comparing one year to the previous one, you can use the Pivot operation to make charting easier. The trick is to choose your dimensions wisely. More