Author Archives: articles

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. A number of theories have been thrown around, including ad click fraud, affiliation fraud, purpose-built crawlers that hide their identity, and more. Whatever the reason, people need to be able to see the non-bot traffic to their websites.

What can you do if you are affected?

Check your Referral Exclusion List

The Referral Exclusion List prevents traffic from the listed domains from starting a new referral session. If your site links to a partner site (like a payment gateway), this would join an initial session on your site with a returning visit from the partner site.

If you list other domains (like spam referrals), it will strip the referrals off the session, but the sessions remain — as direct visits. Use exclude filters in your view for spam referrals.


Check City, Service Provider and Network Domain

Sometimes there is a single crawler, bot or service causing the whole thing. Maybe it is an ‘uptime’ monitor that you initiated. If it all comes from one place, you can easily build a filter to eliminate it.


Check Browser and Operating System Versions

If the traffic comes from various locations, it may be generated from one of several bots that have been propagated around the internet. Look for a spike in direct traffic from old browser versions like Firefox 11.0, Internet Explorer 7.0, or Chrome 18.0.1015.168. Programmers sometimes use code they found on the ‘net and it uses embedded versions of these browsers. Real people tend to upgrade their devices; bots tend to stay the same. If you aren’t sure about a particular version, check the traffic using that version over the past 6 months and see if it is all direct, or if it includes any organic search visits.


Check Browser Size

When programmer’s make a bot, the code can spread across browser versions, and maybe even operating systems, but if they open a hidden browser window to visit your page, it is probably the same size, every time. In particular, look for sizes like 620×480 or 610×480 that have a lot of direct visits but no other sources.


Helpful Filters I Have Used

After performing a significant analysis on several client sites, I came up with this series of filters, which must be implemented IN ORDER. It works on the theory that most of the direct spam was from old browsers or specific browser sizes, and it landed only on the home page of the web site. Note that there were always a few people running really old computers that happen to match the profile, and they visit other pages of the site as well. I did not want to filter out this other traffic, especially if it included ecommerce transactions!

Filtering out visits from a  specific service provider is fairly simple, but how do you eliminate visits based on multiple criteria, like visits to the home page from a specific browser and version? Google Analytics Advanced Filters offers a solution.

A note of caution: this is not a comprehensive guide to advanced filters — that really is an advanced topic. I offer it as an example of what I have used for my own clients so that others can benefit. Also, it worked for me in the past…it may not work for you in the future.

Capture the Browser and Browser Version

The first filter gets the browser (Firefox, Internet Explorer, Chrome, etc) and the browser version (11.0, 43.0.2357.130, etc) into a single blob that I can work with — I used a field called “User Defined”. After the filter runs, the User Defined field will contain things like “Firefox 11.0”.

Tag Unwanted Traffic

Next comes a series of filters that change the User Defined value to “Direct Spam” if it happens to use a specific browser version AND is a hit to the home page (in this case, “/Home”; yours might be “/” in which case use the expression “^/$“).

The list of browser versions I have used includes (verify on your own site before you adopt these):

Firefox 11.0
Internet Explorer 7.0
Chrome 18.0.1025.168
Chrome 39.0.2171.95
Safari 5.1
Chrome 43.0.2357.130
Internet Explorer 9.0
Internet Explorer 8.0
Firefox 3.6.28
Firefox 12.0

Tag Other Unwanted Traffic

You may have other criteria, like browser size, that you want to use – include similar filters for them as well.

Exclude Tagged Traffic

Finally, look at the value in User Defined, and if it has been tagged as “Direct Spam”, then exclude it.


Put The Filters In Order!

This is critical! The first filter (capturing the browser and version) MUST be first, and the last filter (excluding the spam) MUST be last.  Google will execute the filters in order, and that is the only way they will accomplish the task.


Enjoy It While It Lasts

You should now see a drop in direct spam visits as the filters take effect (use a segment for historical reporting). Just remember that things change and you will need to revisit the effectiveness of the filters as time passes, especially if you see a jump in direct visits again.

This article describes a technique I used that was effective for me. Every website is different– you need to do your own analysis before implementing a solution.

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.

Keep this in mind when building your reports.

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.

To create your own refresh is most easily explained by looking at one already scheduled by the add-in. After you have scheduled a report to refresh in Analytics Edge, you can find an entry for it in the Windows Task Scheduler app. All Analytics Edge refreshes will be named ‘AnalyticsEdgeRefresh#‘.

When you select one (upper pane), it will show you the full path to the workbook file to be refreshed in the Description field (lower pane). Click the Properties link on the right sidebar to open the entry for editing.

If you want to see the status of the refresh in the Analytics Edge Schedule Manager, you need to name it “AnalyticsEdgeRefresh#’ where # is a unique number in the Task Scheduler listing. You also need to include the full path to the file in the description field.

On the Triggers tab, Edit the entry to see the settings; in this case weekly on Wednesday.

On the Actions tab, you can Edit the entry to see the settings. This is the command line structure used to refresh the workbook.

Specifically, the scheduler launches a utility called “AnalyticsEdgeRefresh.exe” from your installation folder. Arguments for the command include a full path to Excel.exe, a full path to the file to be refreshed, and the number of minutes to allow the Schedule Manager before a ‘timeout’ status would be returned. e.g.:

"C:\Program Files\Microsoft Office\root\Office16\Excel.exe" "C:\AnalyticsEdge\aeWorkbooks\GoogleSearch-HistoricalBackups.xlsx" 10

Note that the ‘timeout’ does not stop the processing of the file – it only tells the Schedule Manager to stop waiting for it to finish and move on to the next refresh if one is queued.

Creating your own scheduled refresh

To create your own scheduled refresh, simply copy the file paths and settings from a pre-scheduled one, modifying the file path, and giving it a unique name. Note that once you manually edit a scheduled task, you may not be able to use Analytics Edge to edit the schedule, especially if you select settings not available in the Schedule wizard



Please reduce the amount of data you’re asking for

When the Facebook Ads API returns an error 500, it is 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 fields you are asking for. With v2.6.0 of the Facebook Ads connector, Analytics Edge provides a mechanism to work around this limitation of the API to get the data you want.

Using a divide-and-conquer approach, you can split up the query into smaller chunks of data (make a query for each campaign, ad set or ad) and merge the results. The new connector release features a new capability — Ads Listing — that lets you get all of the Ad Accounts for a specific login, or all the Campaigns, Ad Sets and/or Ads for a specific Ad Account, Campaign or Ad Set. Using that listing as a guide, you can repeat your query for each campaign, ad set and/or ad, then merge the results together for your report.

The Analytics Edge Core Add-in can be used to automate this process, and the rest of this article describes the steps to take.

Continue reading

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. But even with the API, by including the ‘query’ dimension and the ‘page’ dimension together, Google can limit the number of combinations it makes available.

You can get a lot more combinations if you make separate requests with just the query dimension, filtered for each of your pages in turn, then combine the results. While that sounds like it could be a lot of work, the latest Analytics Edge connector makes it simple: just run a ‘MultiQuery’ request for your ‘top pages’, and use a large number that would include all of your pages.

Making the query

Fields: select ‘query’ and the metrics you want
Dates: pick the date range desired (Last 90 days will yield the most data)
MultQuery: Vertical layout, by Top Pages with a number larger that the number of pages you have

Caution: asking for too much data can take a long time to download and potentially overload Excel.

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! Here’s a quick example made in conjunction with the Analytics Edge Core Add-in.

Average Positions Trend for Top Page-Query Combinations

There are some search queries that bring lots of traffic to various pages on your site, and this quick report lets you see if any of them start to climb or drop in the rankings. Use it to gauge the success of your SEO efforts, or to easily see the impact of new search ranking algorithms.

The report starts with a quick “multi-query” using the Google Search connector. Select the dimensions ‘page‘ and ‘query‘, plus the metrics ‘position‘ and ‘impressions‘. Select the preset date range of ‘Last 90 days‘, and a MultiQuery ‘by 7-Day‘ with a ‘Horizontal – Group by Metric‘ layout.

You will end up with a lot of combinations, many of which get very few impressions, so we will want to filter them. Because we have 12 weekly columns of data, it would help to get a total for the whole 12 weeks first. This is easily accomplished with the Core Add-in’s Table>Total function, summing the last 12 columns. I chose to put the column before any of the others and to remove the weekly impressions columns themselves since they don’t add much to the report.

Once we have the Total Impressions column, it is a simple matter to use a Filter function with an appropriate number (will vary depending on your website traffic – I used 1000 in this example).

Then a Write to Worksheet function finishes off the macro and your report is done! Refresh the report weekly for the latest insights!

As a final step, I tend to use Excel’s custom formatting for visualization of trends. Since we are looking at search engine rankings, page 1 and 2 are where your focus needs to be, so I use a reverse 3-color rule, with red for higher numbers and green for smaller ones. I edit the rule and change the defaults so numbers 9 and lower are green (page 1 search results), numbers 19 and lower are white (page 2 search results), and numbers over 29 are red (page 3 results will shade to red). You can adjust the numbers to your preferences.

The final result now clearly shows page-query combinations that are improving or degrading in rankings, plus it makes new ‘hot’ pages very visible. If you sort the report by page, you can see how new search terms are picking up existing pages and supporting or replacing older terms.


Track a specific list of page-query combinations. Create a second worksheet of the 2 columns with the desired values, then instead of Filtering, use the Match function to keep matching values from your reference worksheet.

Highlight hot content and queries. Instead of filtering by the total impressions, use Total to get a sum of the last 4 weeks (do not remove the selected columns), then use Total again to get the  sum of the last 12 weeks. Now use Calculate to create a new column with the ratio (4 week total/12 week total) and filter on the result.  Filter on the Total Impressions to remove the really long tail searches… Hot combinations would have a decent number of impressions and a high ratio.



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.

Using the new ‘MultiQuery’ capability, you select the dimensions and metrics you want, then pick the time period of interest (select the whole 2-week or 2-month period).

The final step is to select the matching time period on the MultiQuery tab (by 7-Day or by Month), plus choose the Layout option Horizontal – Group by Metric.

The result will be pairs of columns for each metric in your query, one for each time period (colored here for clarity).


This time-based multiquery can be used with any combination of dimensions and metrics, even if you use filters (e.g. search metrics for a specific query or page over time).

You can have more than 2 time periods in the trend report, but the Google Search Console only has data for the past 90 days. You could do three 28-Day periods (totaling 84 days), or 12 weeks.

For weekly reporting, you can report rolling 7-Day periods (ending on the last day of the period selected), or calendar weeks (Sunday-to-Saturday or Monday-to-Sunday). Note that if you select calendar weeks, only whole weeks will be reported i.e. no partial weeks. That means you should get 12 periods, but if all the dates are not available on the day you run the report it may drop to 11 periods.

The Date tab selections and the MultiQuery date options interact, so selecting ‘duration 2 months’ and ending at ‘end of Last Month’ on the Date tab allows you to get calendar months when selecting ‘by Month’ on the MultiQuery tab. If you select ‘duration 2 months’ and ending at ‘end of Current’, it would return  a rolling 2 month period ending a couple of days ago (Google Search Console data is delayed by a couple of days).

Sorting works on each individual query, but when the queries are joined, the first time period determines the base report order. So if you report on pages sorted on impressions, the report will be order by the highest impressions for the first time period.This could result in new pages added after that date appearing at the bottom of the report, even though they have lots of impressions.

Optimize Search Engine Rankings

How do we monitor and optimize our website presence and increase our search click-through rates? One approach is to adapt your content to include the terminology that people actually use in their “long-tail” searches — the longer, smaller volume searches. This article explores a technique to discover these ‘common’ phrases that people are already using to find your content, so you can look for opportunities for improvement.


Continue reading

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

google-search-repeat-macroThere is a single macro in the workbook [DownloadQueriesForTopPages.xlsx].

The repeat macro reads the pages from the Top Pages worksheet, splits it by the ‘/’ character to extract the web server, then issues a Google Search Console query to get the queries for the specific page on that server. After the download, the results are cut off at the top 5, then a page column is inserted and the results are appended to the Queries worksheet.

Each step is described in more detail below.



Starting the macro with a Repeat Macro wizard, we pick the Top Pages worksheet to process, 1 row at a time. This means we will process the entire macro for every row in the Top Pages worksheet. The values from each column are available to Analytics Edge wizards as ‘range names’ — meaning you can enter the column name in square brackets (like ‘[page]‘) instead of entering a real value (you will see this later).

We also choose to clear the Queries worksheet before the first iteration. Note that the last step in the macro is to append to the Queries worksheet. This lets us start with an empty Queries worksheet to which we will append the results of each pass through the macro.



As you can see, the first row of the Top Pages worksheet has been loaded into memory, and the ‘page‘ column contains the full page URL.  To make the next steps easier, we’ll split this column by the ‘/‘ character to make it possible to get the web server portion of the page URL — ‘‘ in this case.

WT Search Analytics


With the page URL and server now available in separate columns (‘page‘ and ‘page-3‘), we can make the query to get the Google Search data. Using the default account (‘*‘) and the web server from the URL (as a range name, with the square brackets around the column name ‘[page-3]‘), we get the ‘query‘ dimension, sorted by clicks/impressions/ctr/position.


On the Filters tab, we elect to filter by the page URL using the range name (‘[page]‘).

Note: On the Dates tab, I have left it with the default Last 28 days — you can change that if you want.

Keep top 5


Since the original requirement was only the top 5 queries for each page, we need to get rid of the rest, so we add a keep Top 5 rows step to our macro (using the Table > Top/Bottom wizard).

Arrange Insert column


So we can tell which queries were for which pages, we use the ArrangeInsert wizard to put a new column into the results and set the initial text value to be the ‘[page]‘ range name we have used. This will create a new column and populate it with the page URL being processed in each loop through the macro.

Append to Worksheet Queries


Now that we have the results we wanted for that page, we append the results to the Queries worksheet. On each pass through the macro, each page’s queries will be appended to the results, giving us a single worksheet with the top 5 queries for each of our pages listed on the Top Pages sheet.


Note: when you finish the macro, it will only show the first page’s results in the Queries worksheet. Run the macro to process all of the rows.


! Keep it Free and Unrestricted !

The Analytics Edge Basic Add-in is free, as is the included Free Google Analytics Connector. There are no account restrictions, no query restrictions, and the product is updated, maintained and supported. To keep it that way, I need your help to SPREAD THE WORD!  Twitter, Facebook, Reddit, at conferences, in meetups, or just mention it over a beer with colleagues.

Please take a few seconds to add a comment and rating to the product listing in the
Google Analytics Partner Listings.

leave-a-commentFirst, make sure you are LOGGED IN (upper right corner).

Just below the listing is a Write a comment link,
and make sure your select a Rating!

A quick rating is fine, but if you can add a few words about what you like (and don’t like) about the product, it all helps.

Keep it Free! Keep it Unrestricted!



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!

The Google Search connector uses the Google Search Console API to get your Search Analytics data. The Analytics Edge Core Add-in lets you automate the process of downloading your top pages, then for each page, downloading the queries into a Microsoft Excel worksheet.

Spam in Google Analytics?

Yes, the popularity of Google Analytics has gotten to the point that it has become a popular target for spammer. They have discovered that they can spoof the system into thinking that real traffic went to your website from fake referrals, searches and events.

Their objective: make you go to their website to see what is going on. Don’t do it! 

What is a ghost referral?

A ghost referral is a fake visit created by spammers spoofing the Google Analytics tracking servers into thinking that your website had referral traffic from their site. In reality, no one ever visited your site! They don’t even know what it is – they randomly select tracking id numbers. A Valid Hostname filter is the best defense.

What is a ghost visit?

A ghost referral is a fake visit created by spammers spoofing the Google Analytics tracking servers into thinking that your website had real traffic, and it could appear as a referral, as an organic search with fake keywords, as direct visits with fake landing pages and even as fake events. In reality, no one ever visited your site! They don’t even know what it is – they randomly select tracking id numbers. A Valid Hostname filter is the best defense.

What is a valid hostname filter?

When traffic hits your web server, it records the server name in the Google Analytics ‘hostname‘ field. Since spoofed traffic doesn’t actually know what your website is, it contains fake hostname values. Your can easily create a filter to let in only traffic that contains your valid hostname value. See the Definitive Guide for detailed instructions.

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.


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.


First Looks: Google’s New Search Console API

The long awaited Google Search Console (Webmaster Tools)  API update is finally out, and here’s a quick review of some of the new capabilities and limitations. It’s not yet time to give up the old Webmaster Tools CSV download scripts just yet, though; the new Search Analytics Query method fills a big void but there is still a number of other Search Console datasets unavailable in the new API. Google has said this is not a finished product, though, so expect changes.

The Analytics Edge Webmaster Tools Connector v3.0 supports both the old Webmaster CSV downloads and the new Search Console API, and makes it easy to get your data into Excel.

Not All The Data, No Sorting Options

limited-rowsBefore I get started, though, those of you hoping to start massive data mining projects with your search metrics will be disappointed;  the new API it is limited to 5,000 rows (default 1,000) of data — less in some query combinations. Another annoyance is that the queries are always delivered in order of the most clicks, with a random secondary sort.

The Analytics Edge Connector always downloads the maximum row count, and allows you to sort the results by any metric combination.

Dimension Flexibility

Queries can be made with just the metrics to deliver those top-of-the-report numbers for Total Clicks, Total Impressions, CTR and Avg. Position. You can also include the Date to reproduce the requisite trend chart. The date range is still limited to approximately 90 days (3 months), so longer-term trends will require a download-and-store solution.

Analytics Edge provides a free workbook that automates the process on a weekly or monthly basis.

The meat of the data can be sliced and diced by any combination of Queries, Pages, Countries and Devices, giving the API a capability beyond what you can get in the web interface. This is a dataset begging for a slice-and-dice segment analysis! Unfortunately, as you add dimensions, you get less data, dropping to around 3,500 rows.


The date cannot be combined with the other dimensions.

Filter Freedom

Your filter options mimic the web interface, although most people probably haven’t figured out you can actually filter on Queries, Pages, a specific Country, Device or Search Type all at the same time. One noticeable difference over the old CSV downloads is that you can’t select all search types together, you have to pick one of web, image or video. This is consistent with the new web interface, so it shouldn’t be a surprise.


Analytics Edge automatically converts from the 3-character ISO 3116-1 alpha-3 codes (GBR) used by the API to full country names (United Kingdom).

Date Challenges

The final caveat has to do with the date range selected. We know there is 90-day limit and that the data may be delayed by a couple of days, but it might be 3 sometimes. You need to test to see which days have data before you can ask for the most recent 7 or 28 days. This means 2 queries are required in many cases: e.g. the first to determine which dates are available, and the second to query the most recent 7 day range.


Analytics Edge automatically does this for you, always giving you the most recent data and flexibly adjusting the date range to suit your needs and the availability of the data.

Summing It Up

The new Google Search Console API provides a good first step to filling the void but there is a long way to go before it satisfies most web analysts cravings for data. Maybe it is a good thing – we can’t get lost in endless analysis sessions and don’t need sophisticated tools. Keep it simple; keep it focused.


Which referral spam villains are annoying which country the most? The new Search Console API opens up new analysis possibilities. Analytics Edge makes it easy to get the data you need into the format you want it in. The Analytics Edge Webmaster Tools Connector is available for only $50/year (free for 30 days).

Sending Excel Reports To Clients

Many analysts, consultants and agencies use Excel to create reports for their clients because it allows them to deliver a unique solution, tailored to the client’s needs. By using Analytics Edge to generate and automate the report refresh, the monthly process becomes a lot easier and doesn’t require any changes to the client’s environment.

Continue reading

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. No configuration needed – it’s all automatic!



Continue reading

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!


Analytics Edge wizards let you build reports without programming

Simple Sequences

The wizards perform single functions that you combine to perform the analysis you need. They work the way you think: import the file, then filter, then pivot, then sort, then put the results into my report worksheet. There is no need to create intermediate calculation worksheets or to make formulas that reference formulas that reference formulas. You get to the results faster and with less effort. Download, install, save time.

90% of complex spreadsheets have errors (Coopers & Lybrand study)

Simple Functions

The wizards make it easy to do things that are hard to find in Excel, like fill in the missing dates in a pivot table, or calculate a rolling 7-day average. Powerful features you would use every day, but without the complexity. Stop wasting time searching for Excel solutions and stay focused on your task. Learn more about Analytics Edge function wizards.


Analytics Edge lets you stop searching for Excel solutions

run-macroSimple Refreshes

All the wizard steps are recorded automatically and you can easily replay the sequence at any time. Build your report, and run the saved macro to refresh it. No more manual importing, copying, calculating. No need to remember all the little steps you took, or worry about forgetting something. Try it with your own report!

Simple Modifications

When things change (and they always do), you can easily step through the macro and edit any step. Insert new functions, rearrange them, you can even copy steps from other reports. Analytics Edge is designed for a dynamic environment. No need to rebuild a workbook full of formulas. We even give away sample workbooks to show how easy it is!

Maintenance typically consumes 40-80% of software costs (Robert L. Glass)

Simple Decision

Analytics Edge will pay for itself in the time you save while refreshing your first report. Add in your ability to perform a faster, better analysis and you will wonder how you could live without it! Priced at only $75/year, your first 30 days are absolutely free! Download your copy now and start saving time!


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. If your messages to one domain are significantly different from others, it may indicate a problem with spam filtering or how your messages appear in the recipients mailbox. Check the Bounce Rate, Open Rate and Click Rate.

Download the workbook: EmailDomainAnalysis-1-41.xlsx

Continue reading

Constant Contact Click-Through Data Analysis

image0081In 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. Looking at the raw download, we notice several things that need to be dealt with: the dates are in a strange format, it contains multiple clicks to the same link from the same person, and the links themselves are identified only by a meaningless link id number.

Continue reading

Using Worksheet Values to Determine Reporting Period

date-rangesThis 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.

Analytics Edge provides a number of floating date range options that allow you to refresh reports without worrying about the date range. That lets you build reports for the past 7 days, or the last calendar month, but sometimes you want to see the data for a specific date range, and want to be able to enter it in a worksheet — ‘from’ and ‘to’ dates for the report. Continue reading

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. Among many other functions, the add-in includes the ability to split a column into multiple rows without programming. This makes it easy to extract detail rows from summarized data.


Continue reading

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

Long-Tail Keyword Analysis (Advanced Example)

Updated 2015-10-06 to reference Google Search instead of Webmaster Tools.

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. It goes beyond most keyword reports by splitting the search phrases into individual words, applying a Soundex function to group similar and misspelled words, and merging the results into a historical trend analysis. The result shows which individual words are rising or dropping in interest over time.


This is an advanced example of the capabilities of the Analytics Edge Core add-in along with the Connector for Google Search. The workbook uses several special techniques to deliver the analysis, and the magic happens with the click of a refresh button:

  1. using a Setup worksheet to configure the macro functions
  2. using Regular Expression search and replace to remove non-letters
  3. splits multiple-word phrases into multiple rows
  4. uses a custom SOUNDEX function written in VBA
  5. merges the results with the existing historical worksheet

Download the workbook from the free report page.

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.


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.


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.


Automated Email Campaign Reporting

branded-report-exampleSeveral Constant Contact Solution Providers use the Analytics Edge Core Add-in for Microsoft Excel to automate their clients’ campaign reports. With the latest release of the add-in, they are taking the leap to fully automated custom reporting. You can, too! This article describes the updated (and completely free) Branded Agency Report workbook that can be used as a template for your own reporting solution.

analytics-edge-macroThe Analytics Edge Core Add-in runs on a Windows PC inside Microsoft Excel, and it comes with a number of optional connectors, including one for Constant Contact.

The add-in uses a simple wizard interface to automate the tedious download and transformation steps needed to update or refresh a report workbook with results from the latest email campaign.

New functionality in the Core Add-in and Constant Contact Connector allows you to process multiple campaigns, save the results to separate workbooks or PDF files, send them as email attachments, and even schedule the whole thing to run overnight!

Download the workbook here.

How It’s Done

The report lets you enter a number of Constant Contact accounts in a worksheet. When it runs, it will process all email campaigns sent in the past few days and send the results to a specified email address. You enter the number of days and email address to use for each account.

There are two Analytics Edge automation ‘macros’: one to get the account information needed, and the second to process each campaign for that account.

Account Information

setup-worksheet-referenceThe main macro loads the list of account names, email addresses and number of days from a setup worksheet. In a production deployment, you would move this setup worksheet to a separate workbook so there is no chance the information in it (a list of your clients) would end up as part of a report. The first wizard function in the macro is the only reference to this worksheet, so it can easily be changed.

That first function wizard causes the rest of the macro to repeat for each row in the list of accounts. In the very next step, it writes out the row to a hidden worksheet. This is how the main macro passes information to the other macro.

The rest of the macro gets the general account information, including the full name of the account for display purposes, a list of the email campaigns sent in the past number of days, and all the contact email addresses with the contact list they are associated with. All this information is written to hidden worksheets for use by the second macro.

The very last step runs the second macro. Because the Run Macro function is part of the first macro, and because the first macro repeats for each account, the second macro will read different account information from the hidden worksheets each time it runs.

Processing Email Campaign Results

hidden-worksheetOn one of the hidden worksheets, the first macro had populated the login account to use, the email address to send the report to, and the number of days in the reporting period. It also grabbed the full account (organization) name and the list of campaigns that were sent in the reporting period.

We start the second macro very much like the first; setting it up to repeat for each campaign in the list. The rest of the macro is adapted from the previous branded agency report, which was designed to report on a single campaign.

save-email-workbookThe last step saves a copy of the workbook and sends it as an email attachment to the address provided. When configuring the wizard for this step, we use Analytics Edge ‘range names’ (similar to Excel range names) so that the file name, message and email address will all use components of the account’s organization name, the name/subject line of the specific email campaign being reported, and the date it was sent.

The result is that each email campaign report is saved to a unique file and attached to an email — all automatically!

Customizing for Production Use

All Analytics Edge free reports are completely open and easily customized to your unique reporting needs. This particular workbook uses a placeholder logo image that is easily replaced.

As mentioned above, it is strongly recommended that you move the _setup worksheet to a separate workbook to avoid sending your customers’ email addresses as part of your report workbook.

If you are looking for different metrics in the reports, the wide array of Analytics Edge wizard functions make it easy to manipulate and transform data from Constant Contact as well as a number of other sources. Just ask for help.

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. While all of it can be just as easily applied to MailChimp, there are a few differences in the products and how the connectors work. The automation potential is the same.


A common challenge is producing multi-campaign summary reports. This type of report is actually really easy with the Analytics Edge Core Add-in; the process goes like this:

1. create an Analytics Edge macro that gets the campaigns of interest. It could be driven by a date range, specific keywords in the name, the segment it was sent to, or just dump the whole lot to a spreadsheet and let you manually edit it.

2. create an Analytics Edge repeating macro that steps through that list and grabs the metrics for each of the selected campaigns, merging them into a summary report as it does.

This process is demonstrated in the free Branded Agency Report. Note that these reports are very easy for me to produce with Analytics Edge, so if you have a custom reporting challenge, don’t be afraid to ask: I can usually deliver a proof-of-concept in no time.


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


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

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.


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.


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


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.


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.


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.


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


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.


Continue reading

Using the Moz API without the Pain

If you have looked at the Moz (formerly SEOMoz) API with the thought of automating your downloads, you were probably overwhelmed with the effort required. Even if you use the free SEOgadget for Excel, you still have to perform some math (adding “bit flags”) to get the columns you want, and then translate the gibberish column labels you get (like “uu” and “ujid”) to figure out which is which.

Math and translations are things that computers are supposed to do for you, and Analytics Edge brings simplicity to downloading Moz data. The updated Moz connector lets you pick the columns you want without making you talk “API”. Supporting both Free and Paid Mozscape accounts, the connector shows you the metrics available to you and adjusts the API rate limits accordingly.


Use the Moz connector with the free Analytics Edge Basic Add-in to automate your downloads, or fully automate your analysis with the cost-effective Analytics Edge Core Add-in. Multiple accounts, multiple queries, multiple connectors — Analytics Edge doesn’t limit you.

[iframe width=”784″ height=”441″ src=”” frameborder=”0″ allowfullscreen]

Download your fully functional 30 day trial today!

Note: remember, if you are going to show your downloaded numbers in public, you need to comply with Moz attribution and branding rules.

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.



Continue reading

Upgrading from Basic Add-in to Core Add-in

If you have used the free Analytics Edge Basic 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.

Upwards Compatibility

First of all, any workbook you built using the Basic Add-in should work with the Core Add-in, BUT there are some cases where the compatibility may need a bit of tweaking.

Macros vs Queries

With the Basic Add-in, you select a cell and create a query at that cell.  Behind the scenes, the add-in creates a macro that holds 2 functions: your query, and a write-to-worksheet function that writes the results of the query to that worksheet cell location. The Basic Add-in names the macro after the cell location and leaves a comment in the cell as a reminder.

The Core Add-in gives you a lot more control — the macros can include a lot more functions and multiple queries. They are not associated with a single cell in a worksheet, and can have any name. Multiple macros are refreshed in sequence (alphabetically), so it is possible to build multi-step solutions where the results of one macro are used by the next macro. There are no comments in the worksheets — everything is managed from the Task Pane.

Upgrading a Workbook to Use the Core Add-in

You shouldn’t have to do anything to upgrade a workbook created with the Basic Add-in to work with the Core Add-in. The queries will all appear in the Task Pane as macros named after the worksheet cells they were created in.

If you installed the Core Add-in on the same computer that the Basic Add-in was installed on, all of the connector accounts and configuration will work as-is. Your queries should run just like they did before. If you installed the Core Add-in on a different computer, see Sharing Workbooks.

One Potential Problem: the Basic Add-in does not actually run the write-to-worksheet function that is saved as part of the macro – it forces the output into the worksheet cell that matches the name of the macro. If you moved the query or renamed the worksheet after the query was originally created, some versions of the Basic Add-in didn’t keep that ‘extra’ write-to-worksheet function in sync. You should check to make sure that the worksheet and cell references line up with the name of the macro…and that is where you want the results to be written.



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.


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.


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?


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.


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.


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


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

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

Misunderstood Metrics: Facebook Insights

fb-misunderstood-metricsIt can be confusing to download your data from Facebook Insights and try to recreate some of the online reports, only to discover the metric names don’t match. The online Facebook reports use terminology that changes over time (remember People Talking About This?), and do not always have an equivalent downloadable metric. This makes it difficult to recreate those reports in Excel. This article, and the companion Excel workbook, shows which downloaded metrics are behind those online numbers.

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

Definitive Guide to Removing All Google Analytics Spam

This is a PROVEN WORKING SOLUTION with complete filter expressions.

2017-09-13: the need for this solution has dramatically dropped over the past year as Google’s defenses have gotten significantly better. Referral spam attacks are routinely stopped within a day, so creating a filter the next day is a waste of time.  If you want to get rid of the remaining spam from your reports, you will need to use a Segment (step 4) that includes all of the filter expressions listed below. — Mike Sullivan

How to Prevent and Remove Spam:

  1. BEFORE YOU START: Make an Unfiltered View!
  2. Implement a Valid Hostname Filter to eliminate ghost visits (like
  3. Implement Spam Crawler Filters to eliminate the targeted spam visits (like ḷ.com and
  4. Create a Custom Segment with these filters to use for reporting

All the information you need (and more!) is provided below in this step-by-step guide. Filter expressions are updated within a day or two as needed.

Continue reading

Why You Should Not Use the Referral Exclusion List for Spam

While all of us fight to get Google Analytics referral spam under control, one piece of well-intentioned, but bad advice comes up over and over again: use the Referral Exclusion List. Some people (myself included) have stated that is a bad idea, but no one has taken the time to really explain why. Here is the Why

[if you want to know what you should do, read the Definitive Guide to Removing Referral Spam]

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.


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.


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.


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


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.


Just remember, do not change the column titles.


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.


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.


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.


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.


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.


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.


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

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 similar to the following:

    Dim a As String
    a = Application.Run("AnalyticsEdge.RunMacro", "MyMacro")

Be careful of code conflicts between your VBA functionality and the Analytics Edge macro.

For example, if you wanted to trigger a refresh of the macro “Traffic Sources!$A$10″* when the value in cell A1 changed, you could write:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a As String
  If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
    a = Application.Run("AnalyticsEdge.RunMacro", "Traffic Sources!$A$10")
  End If
End Sub
  • this would be a default named macro created when the cell A10 was selected on the Traffic Sources worksheet.

Build a 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. A “cohort” is simply a group of people that share a common characteristic, such as the date they subscribed to your list, whether they clicked on a specific campaign, or those people that selected a particular interest in your signup form.

Cohort Segmentation


click image to enlarge

The first step is to assign each of your subscribers to a cohort — to create a look-up list of their email address and the cohort they belong to. One of the easiest cohorts to obtain is based on the date they were added to your list. If you download your Contacts list, the created_date is one of the columns. Use the Arrange wizard to keep the email address and date column — those are all we need.

To create a monthly cohort, use the Analytics Edge Convert wizard to change the created_date into a text string representing the year and month (format codes “yyyy-MM”). Save the results to a worksheet for later reference.


Collect Your Tracking Data

get-campaign-listThe next step is to get a list of all the emails that opened each of your campaigns over a period of time. Analytics Edge can automate this process. It starts with the Constant Contact connector downloading all the sent campaigns for the time period of interest.

Then, using a Repeat Macro, I get all of the Opens for each campaign and append them to a worksheet. Note that detailed open data for each campaign may include multiple opens by individual subscribers, so I use the Duplicates wizard to get rid of those extra ones. I am only interested if they opened the campaign, not how many times they opened it.


Combine the Worksheets

combine-the-worksheetsThe Opens data gives us campaign ids and emails, and I can use the Combine wizard to look up the cohort for each email from the Contacts worksheet.

Note that I used the Arrange wizard to rename the email address columns to match so that the Combine wizard could link the two on a common column name.

I now have a list of cohorts that opened each of my campaigns. Using the Duplicates wizard, I can collapse it all and get a Count column. While that is a nice summary, no one recognizes a campaign by its id number, so I use the Combine wizard again to get the campaign name and send (modified) date from the Campaigns worksheet.


Prepare for Presentation

The last step is to perform a Pivot operation, producing a table showing the number of opens for each cohort by campaign. It is best to Sort the data before the pivot operation so the rows and columns will appear in the desired order.

In my report, I wanted to show the date of the newsletters instead of their name or subject line. Here it what it looks like.


What Can You Do With A Cohort Analysis?

The final Cohort Analysis report is useful in a few ways: first, it lets you quickly see whether you have specific groups of subscribers (cohorts) that are more engaged with your emails. They are the ones that hold the number of readers over time. How did you attract those subscribers, and can you repeat the process?

Second, you could learn from the variation between campaigns. A great campaign will have better opens across all the cohorts, but you already knew it was a great campaign from the summary reports.

Finally, if you see one cohort more responsive to a particular campaign, there is an opportunity to learn what makes that cohort different from the rest, and can you leverage that knowledge in some way.

This is segmentation — the Holy Grail of email marketing. Cohort analysis makes it possible.


Everyone has slightly different reporting needs, and Analytics Edge is designed to adapt easily. If you need some guidance for your own analysis, just ask. Expert advice is free.





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

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). This opens the door to interactive refreshes without having to navigate the Task Pane controls.

add-a-buttonassign-macroStarting with a workbook containing an Analytics Edge macro, add a Form Control Button to your worksheet.

When prompted for the Macro name, enter AnalyticsEdge.RunMacro

Click OK.

Note: the workbook can be saved with XLSX format. An Analytics Edge macro is not an Excel macro and does not require a macro-enabled workbook.

With the button still selected (right click on the button if you need to select it), change the button name to match the name of the macro to be run when you click the button. You enter the button name in the Name box, located to the left of the formula bar:


That’s it! Now you can just click the button to run the Analytics Edge macro!


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.



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


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.



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.



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.



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

Active table in memory?

ANALYTICS EDGE – active table in memory. See Analytics Edge Help for more information.

read-worksheet-resultsThe Analytics Edge Core Add-in lets you download or import data from various sources, modify it using a variety of analytics function wizards, and then write the results to a Microsoft Excel worksheet.

When you are editing or stepping through an Analytics Edge macro, a worksheet will appear (AE Step Results) with the “active table in memory” message above. 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.

Why does it do this?

The add-in uses a series of wizard-based functions to read and process your data. For example, you could download data in the first step, then filter it, then pivot it to get a trend, then sort it, and finally write the results to a specific location on your report worksheet.

When you run the macro, all you would see is the data refreshing in your report. But when you are developing or editing the macro, you need to see what is going on, so Analytics Edge creates a new, temporary worksheet and displays the data to you.

It also highlights the data in green to make it distinctive, and will remove the worksheet when you write your result out to another worksheet (or Run the macro, or click the square Stop button). You don’t have to worry about it — the add-in will take care of everything for you.

Note that it may not contain all of the rows and/or columns of the actual data. This is done to prevent the workbook from getting too large, and because Analytics Edge can actually process more data that Excel can handle. To see all of the results, use the Write Worksheet wizard to write it all into to your workbook.

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. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Combine function

As with the other multiple-table functions in Analytics Edge (Append, CompareMatch and Update), the Combine function uses a reference table or worksheet. That reference can be an interim analysis step given a name using the Table – Table Name wizard, or it can be a worksheet that has data with a header row starting in cell A1.

Combine in Action

The rest of the options are best explained by example. Assume we start with the following two worksheets, one named ‘Addresses’:

addresses (3)


…and a second containing names:

names (3)


We use the ReadWorksheet wizard to load the names worksheet, then open the Combine wizard and select the Addresses worksheet from the drop-down list.



Select Key Columns

When we open the Combine wizard, we are presented with a list of column names to select from. The list only contains the columns that exist in both tables (‘Email’ in this example). When the tables are combined, there will be a row for each unique combination of values in the key columns i.e. a row for each email address.

You select a single column in the list by clicking on it, and can select others by holding the Ctrl-key down while clicking, just like you would in Excel.

Options – Comparison

When performing the comparison, you can tell Analytics Edge to ignore upper/lower case differences, or to ignore spaces. This is useful if the data sometimes has leading or trailing spaces.

Options – Extra Rows and Columns

By default, Combine only uses the rows and columns that it starts with, but you can choose to add rows and/or columns that only exist in the referenced table or worksheet. For example, you can report click totals for the names and email addresses in the names table only, or choose to add other email addresses and/or street addresses to the report that only exist in the Addresses worksheet.

No optioncombine3 Add rowscombine4 Add columnscombine5 Add bothcombine6

Options – Handling Duplicates

If there are columns in both tables that are not selected as key columns, any duplicate rows will be combined together using the options selected. You can choose to combine text, number and date columns differently using the drop-down selections.

In the example, we don’t have any text columns to combine, but if we did and the first table contained ‘A’ and the referenced one contained ‘B’ for the same row, the result for the All (list) option would be “A,B”.

There are other options for combining the columns, and each data type allows you to keep the first non-blank or non-zero values.



Where Would You Use the Combine Function

The function brings together two sets of data, like the Append function, but it also merges duplicate values at the same time. This makes it particularly useful when merging information that overlaps, such as finding a user’s first engagement date from multiple mailing list campaigns, or calculating the total sales by product from multiple purchasing files.

Note that the source files may contain different columns, in different orders — Combine works with columns by name. If the files use different names for similar columns, use the Arrange wizard to rename them (you could also reorder, but that is not necessary).

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. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Compare Function

As with the other multiple-table functions in Analytics Edge (Append, CombineMatch and Update), the Compare function uses a reference table or worksheet. That reference can be an interim analysis step given a name using the Table – Table Name wizard, or it can be a worksheet that has data with a header row starting in cell A1.

Compare in Action

The rest of the options are best explained by example. Assume we start with the following two worksheets, one named ‘Addresses’:

addresses (2)


…and a second containing names:

names (2)


We use the ReadWorksheet wizard to load the names worksheet, then open the Compare wizard and select the Addresses worksheet from the drop-down list.

combine1 (2)Select Key Columns

When we open the Compare wizard, we are presented with a list of column names to select from. The list only contains the columns that exist in both tables. When the tables are compared, values in non-key columns will be converted into calculated results (such as the Difference between numbers in the two tables).

You select a single column by clicking on it, and can select others by holding the Ctrl-key down while clicking, just like you would in Excel.

compare3Options –  Key Column Matching

When performing the key column comparison, you can tell Analytics Edge to ignore upper/lower case differences, or to ignore spaces. This is useful if the data sometimes has leading or trailing spaces.

Options – Handling Matches

If there are matching rows in both tables, then the other number or date columns will be converted to the percentage, growth or difference calculations you selected. Note: if either table contains empty cells or there are no matching rows or columns, the result of the comparison will be an empty cell. You can use the Convert wizard to fill empty cells before or after the comparison.



Where Would You Use the Compare Function

The function compares two sets of similar data, calculating relative metrics. This makes it particularly useful to calculate changing measures, like product sales, link clicks, or even rank positions.

Note that the source files may contain different columns, in different orders — Compare works with columns by name. If the tables have different names for similar columns, use the  Arrange wizard to rename them (you could also reorder, but that is not necessary).

Using the Match Function

matchThe Analytics Edge add-in lets you keep or remove rows from one table if matching values exist in another. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Match Function

As with the other multiple-table functions in Analytics Edge (Append, CombineCompare and Update), the Match function uses a reference table or worksheet. That reference can be an interim analysis step given a name using the Table – Table Name wizard, or it can be a worksheet that has data with a header row starting in cell A1.

Match in Action

The rest of the options are best explained by example. Assume we start with the following two worksheets, one named ‘Addresses’:

addresses (1)

…and a second containing Names:

names (1)

We use the ReadWorksheet wizard to load the Names worksheet, then open the Match wizard and select the Addresses worksheet from the drop-down list.

combine1 (1)Select Key Columns

When we open the Match wizard, we are presented with a list of column names to select from. The list only contains the columns that exist in both tables. When the tables are matched, only values in the key columns are compared.

You select a single column by clicking on it, and can select others by holding the Ctrl-key down while clicking, just like you would in Excel.

match3Options – Matching

When performing the comparison of the key columns, you can tell Analytics Edge to ignore upper/lower case differences, or to ignore spaces. This is useful if the data sometimes has leading or trailing spaces.

Options – Keep or Remove

You can choose whether to keep or remove rows that have matching values in the key columns, such as keeping the names for people we have addresses for, or keeping the names for people we do not have addresses for.

Keepmatch-keep Removematch-remove

Where Would You Use The Match Function

The function uses one list to filter another, which makes it particularly useful for things like filtering a large data set for specific items, like seeing transactions for the top selling product, or detail click data for the most popular campaign. It could also be used to remove the email addresses of people that have clicked on a previous email campaign, to create a list for followup.



Note that the source files may contain different columns, in different orders — Match aligns key columns by name. If the files use different names for similar columns, use the Analytics Edge Arrange wizard to rename them (you could also reorder, but that is not necessary).

Using the Update Function

updateThe Analytics Edge add-in lets you easily update one table with values from matching rows of another table. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Update function

As with the other multiple-table functions in Analytics Edge (Append, CombineCompare and Match), the Update function uses a reference table or worksheet. That reference can be an interim analysis step given a name using the Table –Table Name wizard, or it can be a worksheet that has data with a header row starting in cell A1.

Update in Action

The rest of the options are best explained by example. Assume we start with the following two worksheets, one named ‘Addresses’:



…and a second containing names:



We use the ReadWorksheet wizard to load the names worksheet, then open the Update wizard and select the Addresses worksheet from the drop-down list.

combine1Select Key Columns

We are then presented with a list of column names to select from. The list only contains the columns that exist in both tables. When the tables are compared, values are updated from rows with matching values in the key columns.

You select a single column by clicking on it, and can select others by holding the Ctrl-key down while clicking, just like you would in Excel.

update2Options – Matching

When performing the lookup to the reference table, you can tell Analytics Edge to ignore upper/lower case differences, or to ignore spaces.

Options – Empty Cells

When performing the update, you can choose to update only empty cells, preventing existing data from being overwritten i.e. fill in only missing information.

You can also choose to ignore update fields if they are empty. This prevents existing information from being wiped out if new values are not provided.

Options – Add Rows or Columns

You also have the options to add new rows or columns from the reference tables or worksheet.


Where Would You Use the Update Function

The function updates one set of data from another, making it useful to maintaining master email or contact lists. Data collected from sales conferences or collateral downloads can be used to update existing records, filling in any gaps and allowing you to collect a little more about each contact with each interaction.

Note that the source tables may contain different columns, in different orders — Update works with columns by name. If the sources use different names for similar columns, use the Arrange wizard to rename them.

Using the Arrange Function

arrange-by-nameThe Analytics Edge add-in for Microsoft Excel lets you easily rearrange or rename columns in your report. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Why you need the Arrange function

When building a report from imported data, one of the biggest problems people encounter is that the columns change over time. Sometimes new columns appear, sometimes the column names change, and sometimes they disappear! If you are using Excel formulas, this would be a nightmare, since cell references would be pointing to the wrong data columns, and you may not notice the difference in the report until too late!

Consistent column order

Analytics Edge solves the problem with a simple wizard; import your new data into a worksheet, use the File – Read Worksheet wizard to read it, and then open the Table – Arrange wizard. Select the By Name tab and remove or reorder the columns as you require. Now if the columns change over time, the rest of your analysis is protected; if the columns you want exist, they will be arranged in the correct order. If they don’t exist or have new column names, a blank column will be inserted in their place.

arrange-renameCorrecting name changes

If column names change unexpectedly, you can easily insert a step into your macro to fix things. Select a line in the macro just after the Read Text File or Read Worksheet command, then open the Arrange wizard. On the By Position tab, identify the misnamed column and change it. Done.

Ways to use the Arrange function

Quality control – If you need a quick sanity check on manually imported data or data coming from other worksheets, insert an Arrange function step to make sure that the columns you are getting are the ones you expected. You can write the results right back out to a reference worksheet, allowing you to make simple Excel cell references to the data with confidence.

Removing clutter – downloaded data often includes a lot of extra columns that are not needed for your report, and the Arrange function lets you easily discard that extra bulk for smaller, faster workbooks.

More meaningful reports – column names are often technical, brief and not always meaningful to report recipients. They can quickly be changed into more friendly terms to reduce confusion and improve readability.

Easier charting – Excel charts are very flexible, but if your data series columns are all in the right order, you can save a tremendous amount of time. Arrange lets you tweak the order of the columns so you can use the default data selection and spend your time more effectively.

Keep it simple

Stop wasting time with Excel report maintenance. Analytics Edge makes it easy to rearrange columns the way you need them, letting you focus on your task, not the tool.

Using the Duplicates Function

duplicatesThe Analytics Edge add-in for Microsoft Excel lets you easily combine rows with identical values in some of the columns. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Duplicates wizard

Once you have loaded your data, open the Duplicates function wizard and select the columns you want to remain unique, then select the options for how to deal with rows that have duplicates and for rows that don’t (singles). You can also choose to ignore uppercase-lowercase differences when comparing text, as well as whether to ignore spaces in the text. A final option allows you to get a Count column added to the table indicating the number of rows that were combined in the operation.

identical-duplicatesCombining identical rows

In the simplest case, you can combine rows where all the columns have the same values — i.e. the rows are complete duplicates. You may need to do this when combining lists that may have some of the same entries.

In this case, you would select all the columns (drag the cursor down the list or click on the first entry and Shift-click on the last).  For options, Keep Singles rows and for Duplicates, pick Keep a Single Row with Aggregate and selectFirst for all of the data types (all the rows are identical anyways).

This functionality is the same as the Remove Duplicates function in Excel, but it can be added as part of an automation macro in Analytics Edge, and you have the easy option of adding a Count column so you can see which rows were duplicated.

combining-duplicatesCombining rows with some duplicate values

Sometimes you want to see something like a subtotal for a few columns, but don’t want to lose the other columns of information. For example, if we have a sheet with work permits by address, we want to see all the permits for each address.

Select the columns that you want to remain unique, such as the street address columns, and then Keep Single Row with Aggregate for Duplicates. For Text columns, select All (list) which will keep all of the values from the separate rows, but they will all be listed in a single cell with commas separating them. In the example image, the original table contained 4 permits for 127 W HURON ST, so all 4 are listed in cell A2.

Note that Excel cannot show comma-separated numbers or dates in a single cell, so there is no equivalent aggregate option for those data types. However, you can get a similar list by first converting those columns to Text using the Convert wizard.


With numbers and dates, you can keep an aggregate value, which might be the First or Last, a Sum or the Latest date. What you select depends on your data and the value most useful in the results. Analytics Edge lets you choose for each data type.

Keeping only duplicates

If your worksheet isn’t supposed to have duplicates, and you want to deal with any manually, you can use the Duplicates wizard to Keep allDuplicates andRemove Singles. Also check the Ignore case and Ignore spaces options. Sort the result and write them to a temporary worksheet. Now you have a list to work through, with all of the original values in each row.

This situation often crops up in Customer Relationship Management (CRM) systems, where new accounts or contacts are created with one name or address component spelled differently, of they have extra spaces that prevented an exact match when entered.

Other combinations possible

As with all Analytics Edge function wizards, you pick the options you need to suit your duplicate data challenge. This gives you the flexibility you love in Excel without the complicated formulas, so you can focus on your task, not the tool.

Simple Excel automation from Analytics Edge.

Using the Filter Function

casestudy2-07The Analytics Edge add-in for Excel lets you easily filter data in a table. The simple wizard interface lets you construct a series of filter rules without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Filter Function

Once you have loaded your data with the add-in, the Filter function wizard lets you build one or more filter rules simply by selecting the column, the criteria and entering a value. Options allow you to either keep or remove rows that match the filters, as well as to remove empty rows or columns.

Selecting Filter Criteria

Creating a new filter rule starts with selecting a column, either by name or by position (column letter). Selecting by position is useful when the column names might change from month to month.

The criteria options will change depending on whether the column you select is text, numeric or dates. For text columns, you can test for whether the column begins with, ends with, contains or equals a value you enter, or whether it is empty. You can also make the test case sensitive, meaning to treat differences in upper and lower case as different values (e.g. “Sent” is not the same as “sent”).

Numeric columns can be tested for greater than, less than or equal to a number you enter. Date columns can be tested for before, after or equal to a date entered.

Once you have the rule set, click the [Add AND Filter] button to add the filter rule to the list. More rules can be added in the same way, using the [Add AND Filter] button if you want both rules to match, or the [Add OR Filter] button for either.



Note that AND has precedence, meaning that the AND logic will always be checked first, then the OR comparison. e.g. criteria A OR criteria B AND criteria Cwould be evaluated as either (criteria A) OR (criteria B AND criteria C).

Using the Pivot Function

pivot1The Analytics Edge add-in for Excel lets you easily pivot data without the complexity of an Excel pivot table. The simple wizard interface lets you choose the rows, columns, value and aggregation to use, and has extra features for generating trend reports. Use it in combination with other Analytics Edge functions to automate your Excel reports updates.

Why would you use a pivot function

Downloaded or imported data is usually provided in a column format with one row per event, item or transaction. While it is easy to Subtotal the information to see things like the total number of link clicks per campaign, if you wanted to see a summary of all the actions by campaign (segmentation) or link clicks by hour for each campaign (trending), you would need to pivot one of the dimensions (values in the action or date column).

While Excel provides a powerful pivot function, many find it overly complicated and difficult to work with. Because Analytics Edge lets you combine several functions in an analysis macro, the pivot function itself is kept simple and easy to use.



Using the Pivot function

Once you have loaded your data with the add-in, the Pivot function wizard lets you select the columns you want to keep, the one column you want to pivot, and the column of values to use. You can also select the aggregation to use when combining rows in the summary.

One option allows you to ignore upper and lower case difference when combining rows of similar values. Another option will ignore blank or empty values in the pivoted column, so you don’t end up with a column with no name.

Depending on the type of column you select to use for the Cell Values, the aggregations will align. Unlike Excel, Analytics Edge lets you use text and date columns for the Cell Values, which opens the door to new types of reports. For example, you can use a column of names with the [All (list)] aggregation to have each cell contain a comma-separated list of names for each segment.



Improved trend reporting capabilities

pivot2If you are pivoting  a date column, the Analytics Edge pivot wizard will take you to a second panel where you can select the date range you want to display.

You start by choosing how you want the column headings to appear. Analytics Edge will write the headings to Excel as text cells, so you can represent the dates any way you want.

Date ranges and resolution

With Analytics Edge, you can set the start and end date for your report, and even fill in any dates that are missing in your data. You have tremendous flexibility to start from the earliest date in the data up to the latest date, or you can select from other options to report on a fixed or floating time period with either rolling periods or calendar periods.

Only part of an analysis

While the pivot function is a powerful analytic tool itself, it is only part of a real-world analysis. Rather than try to pack more functionality into the function like Excel does, Analytics Edge makes it easy to string a series of functions together to deliver the analysis you need, fast and simple.

Using the Read Worksheet Function

read-worksheet-wizardWith the Analytics Edge add-in for Microsoft Excel, you can easily include data from existing worksheets in your automation macros. This lets you process virtually any data you have downloaded from any source. The Read Worksheet function wizard makes it simple, and has several options to handle a variety of common scenarios. Follow it with other Analytics Edge functions to quickly transform the detailed data into segmented and summarized reports without programming or formulas.

Why you need the Read Worksheet function

You get data from various systems in the format they give it to you; often with little choice as to the column selection, header rows, or cell formatting. Once you load it into Microsoft Excel, you are faced with the challenge of stripping out the unwanted columns, rearranging the remainder, changing column names, reformatting numbers or converting dates, and maybe filtering or pivoting the results for the summary analysis you were looking for. Analytics Edge can automate all these steps, and it starts by reading the data itself.

While you could read from a downloaded text file using the Read Text Filefunction, I prefer to start with something I can see, and loading the data into a worksheet gives me confidence that my analysis started with good data. If something strange appears in the analysis, I can easily refer to the detailed data.

Workbooks, Worksheets and Selections

Analytics Edge can refer to data in other worksheets as well as worksheets in other workbooks. To refer to another workbook, that workbook needs to be open when you build the macro, but not when you run it; Analytics Edge will open it automatically when the macro is run. Simply select the workbook and the worksheet from the drop-down lists.

  • read-worksheet-entireRead the entire worksheet as a single table

If your data is nice and cleanly formatted in columns with a header row in row 1 and nothing else in the worksheet, then select this option. Analytics Edge will automatically detect the columns, column names and load all the data, regardless of how many rows or columns are contained in the worksheet.

  • read-worksheet-tableRead a table in the worksheet

If your data is contained in an isolated table on the worksheet (a group of rows and column surrounded by empty cells — it does not have to be formatted as an Excel “table”), but does not start in cell A1 or there is other data on the worksheet, you can use this option. Enter the top-left cell of the data or click the reference button and click on the cell in the worksheet to auto-enter it.

  • read-worksheet-configRead a specific range only

If you want to grab specific cells from the worksheet that may be surrounded by other data, use this option. Enter the cell range in the format of “A2:C3” or click the reference button and select the range desired.

Header Row Is Optional

Sometimes data is delivered without a header row; there are no column names provided, or you want to grab data from the middle of a worksheet. No problem; just check the box Data does not include a header and Analytics Edge will automatically assign columns names of Col A, Col B, etc. to your loaded table. Of course, you can easily change them using the Arrange wizard.

read-worksheet-transposeTurning the World On Its Side

What if the data is not provided as columns in a table? What if it appears as rows in a report with headers at the left? No problem; check the box Data is transposed and Analytics Edge will automatically swap the rows for columns as it is loaded. This is really useful for reading existing trend reports or profit/loss statements.

Loading Into Named Tables or Assigning Range Names

A couple of advanced features of Analytics Edge are the ability to hold a set of data in memory so it can be referenced later in the macro (see Table Name). The entire table can be held by assigning it a table name, and checking the boxLoad direct into a named table will do just that, using the worksheet name.

You could alternatively check Assign range names to each column to make the various columns available to the macro as range names. This would allow you to use the specific values in cells as variables in other macro functions. This feature makes it easy to create a configuration worksheet with things like date ranges or value settings that you want used in the macro, creating easily customized reports without modifying the macros.

Ways to use the Read Worksheet function

Reading imported data – In the simplest form, download your data to a worksheet, then automate your analysis and report updates with Analytics Edge. All the detailed source data is included in the workbook, so anomalies can be investigated or alternative analysis can be performed with ready access to the original source.

Reading existing reports – sometimes the data you need comes from another report, so Analytics Edge makes is easy to extract the information you need, regardless of how it was formatted. Being able to transpose data as it is read means you can still use the data, even if it was pivoted or provided with a row-orientation.

Reading configuration settings – custom reporting is one step easier with Analytics Edge. Read values like a desired date range or filter values right from a worksheet. The macro can use these values to tailor your analysis.



Simple automation with Analytics Edge

Reading data from Worksheets is just the start of automating your report updates. Stop manually reformatting your downloaded or imported data and let Analytics Edge automate the process. Keep your focus on your task, not the tool.

Using the Repeat Macro Function

repeatmacroThe Analytics Edge Core Add-in v2.20 included an enhancement to the Repeat Macro function, making it easier to reports that combine a series of queries with one refresh.

Operation is simple: when setting up a Repeat Macro, you identify a source worksheet from which to get a table of values. The rest of the macro will be repeated for each row in the table. Have 30 clients? Create 30 rows.  The macro will be repeated for each row in turn.

A number of other Analytics Edge features need to be leveraged to make this truly useful, and there are a few things you need to consider when building a solution.

Macros must be stand-alone

All Analytics Edge macros must be able to operate by themselves. To pass information into a macro, you can read the data from a worksheet, and that is how the Repeat Macro works. When it reads the worksheet, it puts the values from the first row into Analytics Edge named ranges, similar to Excel named ranges. You use them in a similar fashion — when you need to get one of the values, you enter the column name surrounded by [square brackets].

Lets say you wanted to process 30 websites, and for each website, you needed the siteUrl. In your reference worksheet, you would create a column named “siteUrl”, and in the actual query for website data, you would enter “[siteUrl]” in place of the website value. As the Repeat Macro steps through the rows in your worksheet, the [siteUrl] will be replaced by the value from each row in turn. The first time through the macro, it would get the first row’s value; the second time through, the second row’s value, and so on.

Writing Separate Result Sets

Of course, at the end of the macro, you will need to write out your results in some way. You can write each result set to a separate worksheet, PDF file ,or copy of the workbook. You would use the [siteUrl] value again here to make sure the worksheet or file name is different for each website.

Combining Results

You could also merge all of the query result sets together using the AppendToWorksheet function — pick a target worksheet and append the results of each loop to the bottom of the worksheet. The target worksheet can be cleared by the Repeat Macro function before the first lop if desired by selecting the option.

If you need to be able to tell each loop apart, the Arrange – Insert a column function could be used to insert a new column with an initial value of “[siteUrl]”, for example, which would populate that column with the website that the query was for.

Macros are executed in order

The Repeat Macro is just like any other Analytics Edge macro, and it is executed in alphabetical order when Refresh All is clicked, or when a Scheduled Refresh is triggered. This means you can create a macro that runs before the Repeat Macro to set the stage. Maybe it downloads all the campaigns that were run in the past month and saves them to a worksheet; then the Repeat Macro could read that worksheet and generate a report for each of those campaigns.

Making a Repeat Macro

To create a Repeat Macro, create a new macro and then select FileNew Repeat Macro from the Analytics Edge ribbon, and the wizard will open. Select the worksheet and/or range of cells to use and click Finish. A new function step will appear at the top of the Task Pane. Note that if you already have function steps in your macro, the Repeat Macro function will be always be placed at the top.

The worksheet selection options are the same as the Read Worksheet function. You can choose the whole worksheet, a table identified by the top-left cell (recommended if the table is clearly separated from other data and may change in size), or a specific range of cells.

You can choose to process 1 row at a time, or if your other macro functions can handle it, more. If you choose more than 1 row per cycle, functions that can use a list will use all of the values, but the rest will only use the values of the first row in the set.

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. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.

Using the Append function

As with the other multiple-table functions in Analytics Edge (CombineCompare, Match and Update), the Append function uses a reference table or worksheet. That reference can be an interim analysis step given a name using the Table – Table Name wizard, or it can be a worksheet that has data with a header row starting in cell A1.

Append In Action

To see what the function does, we’ll take two sample worksheet and append them together. The first contains email addresses with first and last names, the second, email addresses with street addresses.



append-300x229We load the first worksheet using the ReadWorksheet wizard,  then open the Append wizard and select the second worksheet from the drop-down.

Then we use the WriteToWorksheet wizard write the results to a new worksheet and see that, even though the Email columns have been aligned and the two worksheets contain some of the same email address values, the original rows from both worksheets are kept intact.

appendresultsUseful in So Many Ways

You would use Append anytime you want to bring together two or more sets of data, aligning the columns but keeping the rows separate. Typical scenarios include merging multiple monthly downloads into a single file, or combining snippets of data capture sessions.

Note that the source files may contain different columns, in different orders — Append will automatically align them by name. If the files use different names for similar columns, use the Arrange wizard to rename them.

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.


Revoking access

To gain access to your Constant Contact data, Analytics Edge obtains an “Oauth token” when you log in to your account through the connector (use the Add Account function on the Accounts Wizard).  Once that token is issued, you no longer need to log in with the account and password — the token is accepted all by itself.

Note that the account is still verified before use, so even if an oauth token is issued for an account/login, if that account is deactivated, the oauth tokens will fail.

If it is necessary to revoke a token, the only way to do this is to send a request to Constant Contact Web Services group ([email protected] telling them to revoke all Oauth tokens issued to your account from the application “Analytics Edge Account Login”.