How To Guides (filter at right)

*Building a Marketing Dashboard in Excel

Marketing-DashboardThis 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 →

1 Welcome to Analytics Edge

Analytics Edge makes it easy to get your data into Microsoft Excel. You can place multiple queries anywhere in your workbook as this video explains. More →

2 Help Resources Available

The Analytics Edge help site has plenty of resources available for learning and assistance with reporting. This video explains what is available and highlights the ‘account not found’ problem people experience when sharing workbooks. More →

4 Creating Custom Reports

Analytics Edge was designed to create the customized reports people need for their website or campaign tracking needs. This video explains the common errors you may run across and what you can do about them. More →

5 Licenses and Updates

This video explains how Analytics Edge products are licensed and updated. It demonstrates how to identify updates available and how to install them.  More →

A Programmer’s Guide to Analytics Edge No-Code Macros

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 →

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 (ᴁ 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.

More →

Add-in: Call VBA From A Repeating Macro

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 →

Add-in: Changing Dates in Multiple Queries

If you are build a report that includes multiple queries and you want to be able to change the dates of all the queries at once, there are a couple of ways you can get this done — with a little pre-planning, or a little hacking. More →

Add-in: Hacking the Query Worksheet

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 →

Calculating a Weighted Average (Average of Averages)

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 →

Constant Contact: Autoresponder Reporting

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.

More →

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 →

Constant Contact: 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 →

Constant Contact: Reporting Automation

branded-report-example (1)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 →

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.

creating-a-dashboard-widget More →

Error: 400 AuthorizationError CUSTOMER_NOT_ACTIVE

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 →

Error: 400 invalidParameter ids

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 →

Error: 401 authError Authorization

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 →

Error: 503 There was a temporary error

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 →

Error: Account not found. Check Account wizard

This error usually occurs when you share workbooks with coworkers, or after a computer move or rebuild. Analytics Edge stores a reference to the account in the workbook, but the actual security tokens are stored on your computer.  More →

Error: Invalid/missing dates

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 →

Error: License issued to a different computer?

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 →

Google Analytics: API Filter Expressions

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.

More →

Google Analytics: 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 →

Google Analytics: Custom Dynamic Segments

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: 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 Analytics: Reports Limited To 25 Months?

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.

 

Google Search: Annual Reporting

Long-term reporting with Google Search Console data is fraught with challenges. This article discusses some of the unique problems time can create, and how Analytics Edge products can save you time while solving them. More →

Google Search: Branded Versus Non-Branded

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 →

Google Search: 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 or 5000 row limit imposed by the web interface. More →

Google Search: Eliminating Noise

As discussed in a companion blog article, while trying to create an annual report for my own websites, I discovered that Google Search data is full of ‘noise’ — data that actually makes it harder to make useful observations about trends. This article includes the workbook I used.

More →

Google Search: Get 16 Months Of Search Data

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 →

How To Add Row Numbers

There are times you want to see row numbers in your results — a cumulative count column starting at 1 and increasing down the column. A combination of two Analytics Edge macro functions makes this possible. More →

How To Build a Report Using Fiscal Weeks

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 →

How to Schedule Email Delivery of Reports

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

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 →

MailChimp: Activity for the Latest 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 →

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 →

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 →

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 →

Reading Sitemaps to Get a Full Listing of Pages

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 →

Running Macros from VBA

With Analytics Edge Core Add-in, you can run an Analytics Edge macro (e.g. “MyMacro”) from Visual Basic for Applications (VBA), using code More →

Running Queries from Buttons

With the Analytics Edge Core Add-in, it is now possible to run a specific Analytics Edge query from a button on your worksheet (the Analytics Edge Core 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 →

Terminology

The Analytics Edge Core Add-in uses some common Microsoft Excel terminology, but the meanings are slightly different. More →

Tiny Wizards, Fonts And Big Screens

Analytics Edge products are being updated to support the newer high resolution monitors, but until all of the products get updated, some of the wizards may appear smaller than the rest of your applications. These hard-to-read wizards can be fixed if you change your Display Settings. 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 →

Using Append to Worksheet

The Analytics Edge Core Add-in allows 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. 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

append4-300x230The 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 Combine Function

combineThe 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

compareThe 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 Convert Function

The Convert Function of the Analytics Edge Core Add-in lets you convert text, numeric and date columns in a wide variety of ways. This article describes how it can be used in your Analytics Edge macros. More →

Using the Group Function

The Group function in the Analytics Edge add-in lets you easily group or categorize data. It evaluates the values (text, numbers or dates) in a selected column, and adds a new column with group or category labels. The simple wizard interface gets the job done in seconds. More →

Using the Run Macro Function

The Run Macro Function in the Analytics Edge Core Add-in lets you run other Analytics Edge macro or Excel VBA macros. This would typically be used to create a ‘master’ macro that runs through a specific sequence of other macros. With v7 of the Core Add-in, you can also trigger Excel VBA macros, which opens the door to running custom VBA code that does things Analytics Edge does not enable. More →

Using [Range Names]

Report automation goes to a whole new level when you discover [range names] in the Analytics Edge Core Add-in! This capability is embedded throughout the product, and it allows you to grab values from your data to be used in subsequent functions, like filtering one query based on the result of another query.  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 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 →