*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

Account Reference Names

Analytics Edge takes account security very seriously. When you use the add-in or one of the connectors to access one of your accounts, the product will encrypt a security token and save it to your computer. You will be asked for an account reference name for this token. 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 File Download Link Text/URL To Google Analytics 4

It is easy to get more details about file downloads added to your Google Analytics 4 reports — just add the custom dimensions for the existing event parameters ‘link_text’ and ‘link_url’! The data doesn’t start collecting until you do this, so do it now! More

Add Session Count To Google Analytics 4 Reports

It is easy to get a Session Count fields added to your Google Analytics 4 reports — just add a custom dimension for the existing event parameter! The data doesn’t start collecting until you do this, so do it now! 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

Adding Page Count To Google Analytics 4

There are a lot of things from Google Analytics (Universal Analytics) that I miss in Google Analytics 4. Although page engagement metrics are much improved, I was frustrated by the lack of any ability to gauge the Page Depth of my visitors. Do they look at 1 page, 3 pages, or 20?  The averages just don’t tell the whole story. Here is how I added a Page Count dimension and a couple of early observations I made with the data.

 

More

Amazing SEO Reporting In Excel

With version 4.0 of the Analytics Edge Google Search connector, you can quickly create SEO analysis reports with tremendous insight potential; way beyond what you can do in Google Search Console itself! More

Analytics Without Programming

Avoid complex Excel formulas; Analytics Edge lets you build reports in Excel without complicated formulas or programming. Using a simple wizard interface, this small Excel add-in lets you combine, calculate, split, pivot, sort, filter and much more. Try it yourself and see how easy it is! 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

Caution: Last Day of Data May Not Be Accurate

According to a tweet by John Mueller of Google (2017-06-23), the most recent day of data from the Google Search Analytics API may contain partial results, so you should plan to refresh that data the next time you run your report. More

Challenges with Annual Reporting

It’s that time of year again, time to summarize the performance of your website, search engine optimization efforts, and campaign spending. As you start your marathon of data manipulation, there are a few things you should keep in mind to avoid disaster. 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

Custom MailChimp Multi-Campaign Reports

A flurry of interest in the Analytics Edge MailChimp connector over the past few weeks has made me realize that most of the email marketing automation examples I have posted have focused on the Constant Contact connector. More

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. More

Definitive Guide to Removing All Google Analytics Spam

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

Direct ‘Spam’ in Google Analytics

A number of my clients have seen unexplained direct traffic in their Google Analytics accounts. There is no apparent purpose or reason behind this traffic — it just exists; sometimes for a short period of time, and sometimes for months. 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

Facebook Ads Bidding and Budgets

The Facebook Ads bidding and budget information may seem to work in mysterious ways, especially when you are trying to generate a report from the API. Here is a picture of how it fits together. More

Get Impressions From Google Analytics

A lot of people want to include the Google Analytics clicks and impressions metrics in their reports, and are frustrated when they see most reporting tools do not offer those metrics. The reason is that they don’t actually come from Google Analytics — they come from Google Search Console, and that uses a separate API.

More

Google Analytics 4 Report Automation

Now that the Google Analytics Data API has been released, it is possible to automate your GA4 reporting. Analytics Edge makes the process easy with a powerful add-in for Microsoft Excel and the Google Analytics Pro connector.

More

Google Analytics Premium Support

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

Google Analytics vs GA 4: Different Cities

I dug into the detailed data behind Google Analytics 4 and compared it to the old Google Analytics we have come to know, and something popped out that was unexpected: sometimes the city being recorded for a specific user’s visit was different. More

Google Analytics vs GA 4: Pages Reporting Is Flawed

Comparing Google Analytics 4 page reports to the old Universal Analytics version exposes another ‘little’ difference between the measurement systems: your page titles can be shown in different languages, spread across twice as many rows (your data will vary).

More

Google Analytics vs GA 4: Sessions Spanning Midnight

Updated 2021-03-27 after a deep dive into additional metrics…

As reported in one of my Misunderstood Metrics articles, one of the quirks we discovered in Google Analytics is that sessions that span midnight are restarted on the first hit after midnight, even though the session id (and Count of Sessions dimension value) doesn’t change. But what quirks await us in Google Analytics 4?

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

How To: Email Campaign Domain Analysis

email-domain-analysisFind out how your email campaigns are performing with particular domains, so you can see which ISPs your subscribers use most often, and detect if you’re having delivery or compatibility problems. More

Incremental Report Updates

If your monthly report is regularly pulling down a full year’s worth of data, it is probably downloading 11 months of data that hasn’t changed. You can speed things up by doing an incremental update — just download the data you need. 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

Life Lesson: Simple Solutions Are Often The Best

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 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

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…. More

Misunderstood Metrics: Average Position

Google Search Console makes it easy to track the position of your website pages in Google’s search results, but the numbers are averages, and averages don’t tell the whole story. When it comes to search engine ranking, they can be really misleading. More

Misunderstood Metrics: Bounce Rate

What is the bounce rate in Google Analytics? Is it better to be higher or lower? What is typical? Does it affect my search engine rankings? Here is some insight into this frequently misunderstood metric. More

Misunderstood Metrics: Google Analytics 4 Sessions

As more and more people start the process of switching over to the new Google Analytics 4 for website tracking, they are discovering that the session counts are significantly different from the previous release of Google Analytics (a.k.a. Universal Analytics). My recommendation: stop focusing on sessions — focus on user engagement with your content instead.

More

Misunderstood Metrics: Google Analytics 4 Sessions for Pages

Since Google Analytics 4 is still under development, this is just an introductory article.

Google Analytics was plagued with custom reporting problems by mixing metrics and dimensions with different scopes  (e.g. sessions for pages). Well, even though Google Analytics 4 is very different, it shares the same problem: it has dimensions and metrics that are user-scoped, session-scoped, event(hit)-scoped, and item-scoped. More

Misunderstood Metrics: Google Analytics Users

Why do the Users numbers in my report not add up to the total in Google AnalyticsThis Misunderstood Metrics article explores the Users metric in Google Analytics, why the numbers are different, and why both of them are probably wrong. More

Misunderstood Metrics: New vs Returning Visitors

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

Misunderstood Metrics: Next Page Path

In Google Analytics, the Previous Page Path shows what page was visited just before the current Page, but Next Page Path seems to be broken or not working. The problem is that it doesn’t hold what the name implies. More