Category Archives: Analytics Edge Core Add-in

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

 

 

Reporting Multiple Google Analytics Segments

When you want to combine two or more queries, such as multiple Google Analytics segments, the Analytics Edge Core Add-in provides the tools to automate it. In a simple scenario, let’s make a quick table showing top-level metrics like Users and Sessions for each of several segments.

multiple-segments-1

Building the Queries

We start with the individual queries — the current release of the Google Analytics connector requires separate queries for each segment. Creating a query with the metrics you want is straightforward — pick the account and view, the segment, add the fields, and pick a date range (see Your First Query). Notice that there is no “Segment” column in the results — this will need to be added.

To add a Segment column with an appropriate label, you use the Arrange function to Insert a column. Insert the column Before column A (select the first row in the list of columns), with a New Column Name of “Segment” and an Initial Text Value appropriate to the segment you have used in your query (“All Users” in this case).

multiple-segments-insert-segment-label

Before we move on to the next query, set a bookmark to these results so we can reference them later in the macro. Use the Table Name function to Assign a name to the current table with something appropriate, like “All Users“.

multiple-segments-assign-table-name

Repeat for the other segments you want in your results, making the query, inserting a “Segment” column and naming the table.

Combining the Results

Analytics Edge offers a number of ways to combine multiple tables of data, but in this case, we want to simply Append each row to a common table.  Start by Switching to the first saved Table Name: “All Users“.

multiple-segments-select-table-name

Then we use the Append function to append each of the other tables to the one we are working with. The Append function adds the referenced data to the bottom of the current table, aligning the column names so the various metrics are automatically aligned properly from row-to-row.

multiple-segments-append-table

Note that if your data required appending new columns to your existing rows (instead of new rows to existing columns as in the example shown), you would use the Combine function instead of the Append function (see Using the Combine Function).

End your macro by writing the results to your desired worksheet location with the Write Worksheet function.

Summary

multiple-segments-macroThe Analytics Edge Core Add-in can be used to transform any combination of queries and data sets into named tables, which are then easily merged into a single table for your report.

Start with your queries, adding a label column if necessary, then assign table names to the results. Finish off by using the Append or Combine functions to bring all the tables together before writing to your worksheet.

 

 

06 Next Steps

The real power of the Analytics Edge Core Add-in are the other functions that let you sort, filter or pivot your data. You can convert text to dates, and dates to first-of-the-month. You can calculate ratios or averages, make totals, or keep only the top 10 entries. You can hold one set of data in memory (Table – Name), and compare or combine it with another data set or worksheet. You can do all those things you would have to do manually in Excel to get the numbers you need for your report.

Here’s an example of the kind of thing that can be accomplished in just a few minutes:

Other examples are provided in the form of free reports (listed at the right). You don’t need to figure everything out right away – drop me an email ([email protected]) and I’ll help get you started fast (and free).

05 Write the Results into Excel

The Analytics Edge Core Add-in is holding your query results in memory, where you can easily manipulate it with other Analytics Edge functions. When you are ready, you will want to write the results into an Excel worksheet.

The easiest way is to select a cell in the target worksheet, then pick the function File > Write to Current Cell.

write-to-current-cell2

When you write the results to a worksheet, you will notice that the temporary “Æ Step Results” worksheet is removed automatically. It will also be removed if you run or refresh any macro. It only appears when you are creating a macro or when you Step through one.

Your macro is now ready to be replayed at any time. This can be done by clicking the Run/Play button in the Task Pane, or click the Refresh All button on the Analytics Edge ribbon.

run-or-refresh

00 Terminology

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

Macros

In Excel, you can record a series of actions as a Excel Macro, then replay them to automate the tasks. Macros can be tricky, though, and to fully understand them, you will need to be familiar with Visual Basic programming.

macros-excel

With Analytics Edge, your actions are automatically recorded as an Analytics Edge Macro, allowing them to be replayed to automate the tasks. You can create and edit these macros using simple wizards, though; no programming is required.

macros-analyticsedge

Files

In Excel, macros can only be saved in macro-enabled workbook files (.xlsm), and may require special permissions to load and run.

In Analytics Edge, macros can be saved in standard Excel workbook files (.xlsx) and do not require special permissions to load and run.

Tables

In Excel, you can define a block of data as an Excel Table, which allows you to quickly sort and filter. You can also easily format the table for easier reading. The Table can be used as a data source for an Excel Pivot Table or Pivot Chart for analysis.

table-excel

In Analytics Edge, you download data into a virtual Table in memory, which allows you manipulate the data in several ways before writing it to a worksheet. You can use Analytics Edge to refresh the data in an Excel Table in the worksheet.

table-analyticsedge

Range Names

In Excel, you can assign Range Names to worksheet cells so your formulas can reference the cell by name instead of by location. This can make formulas easier to understand. In Excel Tables, entire columns can be referenced by enclosing the column title in square brackets.

rangenames-excel

In Analytics Edge, you can assign Range Names to columns in one Table. Then you can reference the values in those columns by name in other wizard functions later in the same macro simply by entering the name of the column enclosed in square brackets.

rangenames-analyticsedge

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!

mailchimp-activity014

 

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!

simple-function-wizards

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.

search-trends

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.

split-into-rows-1

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.

long-tail-keyword-analysis

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.

monthlypdf-websites

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.

Summary

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.

mailchimp-reports

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

Join

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 (http://www.analyticsedge.com), 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.

www.analyticsedge.com/2014/08/cohort-analysis-blog-articles/

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.

2014/08

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.

split-columns

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

age-calculation

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.

Sort

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.

Pivot

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.

peak-pageviews

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

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

cohort-analysis

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.

waterfall-report

Continue reading

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.

google-analytics-annual-report-sampling

 

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.

basic-to-core

 

Append To Worksheet

appendtoworksheetThis Analytics Edge wizard is used to append the current table of data to the end of an existing worksheet.

If the worksheet is empty, the function will create a new header row.

If there is already data on the worksheet, data will be appended to the bottom of matching columns (by the caption/label in the header row). If a matching column does not exist, a new one will be created.

 

Comment

comment-wizardCreate a comment in the current macro.

The short comment will appear in the Task Pane with a leading # symbol. Comments are for documentation purposes only and serve no operational function.

The long comment is only visible when you edit the function step.

03 Configure the Connectors

Each Analytics Edge connector has a unique configuration requirement, but most of them use an Accounts wizard. Check the connector’s help page for specifics to the connector your are using.

As an example, for the Free Google Analytics connector, you need to configure the accounts you want to use in any queries.

1. Open the connector’s Accounts wizard

To open the Accounts wizard, click the connector button on the ribbon bar and select Accounts from the drop-down menu.

setup-google-analytics-account

 

2. Add an account with a Reference name

Accounts in Analytics Edge are stored using Reference names, not email addresses or real account names. The login credentials are stored on your computer, and the reference name is stored in the workbook with the query. This means you can freely distribute your workbook and your account credentials are never exposed.  Sharing workbooks? 

Enter a reference name and click Add Account, then log in to the account to be used.

accounts

 

 

3. Set a Default (optional)

Some connectors let you select a default account, and may even let you select default properties to use with that account. Click the Help button on the wizard for the options available.

Select an account in the list, the pick an Account, Property and View(Profile) from the drop-downs at the bottom. Click the Make Default button.

Then Close the wizard.

 

 

02 Optional Connectors

Analytics Edge has a variety of connectors available. They can be installed, updated and uninstalled using the Manage Connectors wizard. When you add a paid connector, it automatically starts a 30-day free trial period.

1. Open the Manage Connectors wizard

In Excel, on the Analytics Edge ribbon bar, click on the Manage Connectors button to open the wizard.

manage-connectors-button

2. Install the connector you want

In the wizard, click on the entry for the connector you want to load. A second tab will appear with details about the connector release and buttons to Install/Uninstall. Click the Install button.

install-connector

3. Restart Excel

You will be told to Restart Excel for the changes to take effect.  There should be a new button on the Analytics Edge ribbon bar for the connector(s) you have added.

4. Configure the Connector

Each connector has its own configuration requirements and help documentation. See the connector’s Help menu for more information.

connector-help

 

 

01 Registration

Welcome to the Analytics Edge Core Add-in! Your first step is to Register the add-in you have installed.  Open Microsoft Excel and go to the Analytics Edge ribbon bar.

1. Click the Register button

register-button

The registration wizard will open and ask you to Accept the Terms of Use.

 

2. Submit your email address

enter-email-address

An email message with a registration key should appear in your Inbox within a minute. If it doesn’t, check your spam folder. If it still doesn’t appear, you can try again with a different email address; some email systems have more aggressive spam filters that prevent delivery.

 

3. Submit the Registration Key

enter-registration-key

The add-in will contact the licensing server and a message should appear saying: 

!License activated.

 

If you experience any problems with the product registration process, contact [email protected] for assistance.

 

Task Pane

taskpaneThe “heart” of the Analytics Edge Core Add-in is the Task Pane, which opens automatically to let you create, run and edit Analytics Edge macros.

Analytics Edge macros are not the same as Excel macros; they are stored in a hidden worksheet and can only be run if the Analytics Edge Core Add-in is installed with the necessary connectors.

When you create an Analytics Edge macro, you must assign it a name. If you do not assign a name (leave the field empty and click OK), then Analytics Edge will assign it the name of the currently selected worksheet cell. This is consistent with how the Analytics Edge Basic Add-in works.

Macro Execution

When a macro runs, progress will be displayed in the task pane, coloring completed functions green and the current function yellow. If you Step through a macro, intermediate step results will be displayed in the temporary AE Step Results worksheet (green background). Clicking Stop or executing a SaveToWorksheet function will remove that temporary worksheet.

Macro Buttons

macro-buttons

Macros are selected from the drop-down, and can be run by clicking the Run button. You can also step through a macro one function at a time, or stop a macro in mid-execution. Note that the current step may complete before the execution is stopped.

Changes to macros can be undone (or re-done) for as long as you keep the workbook open. Once you close the workbook, all changes to the macro are saved and previous versions are lost.

The macro can also be renamed or deleted by clicking the appropriate button.

Function Buttons

function-buttons

Individual function steps in your macro can be selected by clicking on them. You can select multiple steps by holding Ctrl of Shift down while clicking, on by click-dragging down a range of functions.

Once selected, functions can be rearranged, edited, deleted, copied or pasted by clicking the appropriate button.

Copy / Paste

You can select any number of function steps and click the Copy button, then select the destination macro and click Paste. You can copy within the same macro, within macros in the same workbook, and even between macros in different workbooks. Note: the destination macro must exist before you start the copy/paste operation. If the Paste button does not appear enabled when you switch to the destination macro, try re-selecting that macro in the drop-down selector.

 

 

Lookup

lookupThis Analytics Edge wizard lets you quickly lookup values from another Table or Worksheet that share an index value with the current table.

Select the column in the current table in memory to use an an index value (like a campaign_id), then select the reference Table or Worksheet as well as the index column that contains matching values (like id).

Then select the columns you want to add to the current table from the reference table. To select more than one column, hold the Ctrl-key down while you click.

If more than one row in the reference source has a particular matching index value, you can select how you want to handle those duplicates.

Example

You may have a table of email campaign clicks with a “campaign_id” column:

clicks

And you want more information about that campaign. You also have a worksheet containing Campaign data, but the campaign id column is labelled simply “id“:

campaigns

You can select the campaign_id and id columns as the Index, then select the name and modified_date columns from the list (as per the image above).

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.

Schedule Manager

schedule-manager

click image to enlarge

This Analytics Edge utility is part of the Core Add-in, and is used to manage all of the Analytics Edge scheduled refreshes. If can be launched from the Schedule Refresh wizard, and will open automatically following any scheduled refresh.

Contents can be sorted and filtered in multiple combinations, and selected workbooks can be bulk refreshed (Run Now), or have their schedules changed (Reschedule), Disabled, Enabled or Deleted. Select the workbooks by checking the boxes in the left column, then pick the action to be performed at the top and click the Go button. Individual schedules can be modified by clicking the edit link.

Individual workbooks can be opened by clicking on the File name. Columns can be sorted by clicking on the column titles. You can filter for characters in the file name by typing in the Filter box, and further restrict the view by selecting the Folder, Type or Status values. The view can be refreshed by clicking the refresh button.

Details of the most recent refreshes can be seen by clicking the Log button.

Files can be scheduled in bulk by clicking the +Add Scheduled Refresh and selecting multiple files. The refreshes will be scheduled 1 minute apart starting at the time selected.

Status Messages

Each scheduled refresh is assigned a status based on the results of the last attempted automatic refresh. Note that if you manually open and refresh a workbook, it will not update the status, but if you select the workbook and pick Run NowGo, it will update the status.

ok – the refresh completed without error.

New – the refresh has not yet been run to completion. Note: if the computer is restarted after the scheduled First Start date, the refresh will run even if it is not scheduled to run that day. It is assumed the schedule was missed.

#Running – the scheduled refresh is currently running. Note that the process may not be visible in Windows. If a scheduled refresh remains in this state for more than an hour, it may be necessary to reboot your computer to clear the background process. If you do this, note that Excel may disable the Analytics Edge add-in, and you may have to re-enable it manually.

?See Log (macro error) – the refresh did not report an ok status upon completion. Usually this is caused by a macro error. See the Log in case there is indication of the failure.

?See Log (refresh crash) – the refresh did not complete withing the timeout period and may have failed unexpectedly. Check the workbook and manually refresh to see what the problem could be.

?See Log (no response) – the refresh ended without reporting success or failure. Check the file and refresh manually to see whether there is a problem.

!Disabled – the scheduled refresh has been disabled and will not run until re-enabled. Use this state to pause refreshes without deleting them.

!Not logged in – Windows attempted to run the scheduled refresh but the user was not logged in at the time. Run the Refresh at your earliest convenience.

!Problem with Scheduled Item – the scheduled task is not recognized as an Analytics Edge Scheduled Refresh. Recreate the scheduled item or delete the task.

!Not an AnalyticsEdgeRefresh – the scheduled refresh is not a properly configured Analytics Edge refresh. Delete and re-create the refresh schedule.

!Excel cannot be found – the instance of Excel on your system has moved since the refresh was scheduled. You will need to reschedule the refresh.

!Not an Excel workbook – the scheduled refresh does not point to a supported Excel file type (xls, xlsx, xlsm, xlsb).

!File does not exist – the schedule points to a workbook file that has been moved or deleted. Note that it may not be possible to refresh workbooks on network drives.

!Queue File Locked – if the refresh was unable to get access to the refresh queue file after several attempts, the file may be locked by a failure of you may have too many refreshes scheduled at the same time. It may be necessary to reboot your computer to clear the file lock.

!Aborted – the refresh was somehow aborted and will need to be rerun.

!Error <message> – the refresh reported an error. Open the workbook and refresh manually to confirm the error was not intermittent (server/service unavailable).

 

 

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:

add-a-button

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

 

Write to Current Cell

The Core Add-in includes this shortcut menu item that mimics the functionality of the Basic Add-in. At any time during macro development, the current active table in memory can be quickly written to a specific cell on a specific worksheet.

Select the worksheet and cell, then choose the menu item to Write to Current Cell. A new Write to Worksheet command will be added to the macro to write the active table in memory to the specified location. You can edit the command to change the options if desired.

* Core Add-in Orientation

Analytics Edge is simply different — it is designed to make it easier (and a lot faster) to refresh an Excel report, so it is worth taking a few minutes to see how it works before you dive in. 

RIBBON BAR

The Analytics Edge ribbon bar is the command center for the Core Add-in.  From here you can refresh your report, select functions to add to a macro, and manage the optional connectors. You can also check or update your licenses, and schedule workbook refreshes.

orientation-1

 

TASK PANE

The Task Pane is used to manage the macros in your workbook. Individual functions in your macros can be reordered, edited, and deleted. ref: Task Pane

The Analytics Edge Core Add-in works like a macro recorder, with every Function or Connector operation recorded automatically in the Task PaneAnalytics Edge macros are not the same as Excel macros; there is no programming involved. Every step is created and edited with a Function wizard. ref: see the video 

 

ᴁ Step Results WORKSHEET

The Analytics Edge Core Add-in processes your macro in memory until you are ready to write the results to a worksheet. As you build or edit a macro, the interim results up to that point are displayed in a temporary worksheet, called “ᴁ Step Results“, colored with a green background to be easily recognized. When working with large amounts of data, the temporary worksheet only shows a sample of the actual data. ref: Active table in memory

ae-step-results

The last step in your macro is usually a Write to Worksheet command. This lets you modify the results of the query using other Analytics Edge functions before you write it to your report. ref: WriteToWorksheet

 

CONFIGURE ACCOUNTS

orient-accountsMost Analytics Edge Connectors require some configuration. The Accounts wizard lets you create and save your login credentials, as well as set any other connector options.

Your account credentials and configuration options are stored in your computer, not in the workbook or macro. Analytics Edge servers are NOT involved in your queries and do not have access to your credentials.

There are some special considerations if you want other people to refresh your workbooks. ref: Sharing workbooks

 

ADDING OPTIONAL CONNECTORS

Analytics Edge has a variety of connectors available. You install, update and uninstall the connectors using the Manage Connectors wizard. Some connectors are free, and all of the rest can be used free for 30 days. They are all fully functional; there is no limited ‘trial mode’.

manage-connectors-button

To install a connector, simply click on the entry in the wizard, then click the Install button. You will be told to Restart Excel for the change to take effect.

install-connector

 

MORE TO DISCOVER

That is just a quick overview of the Analytics Edge Core Add-in. Online help is available for each function as well as how-to articles and tutorials. There are even free reports to get your started quickly. At any time, feel free to contact [email protected] for assistance — confidential email support is available with all paid products, even during the trial period.

Manage Connectors

Get the list of available connectors by clicking the Manage Connectors button on the Analytics Edge ribbon bar in Excel.

manage-connectors-button

The Manage Connectors wizard will open, showing a list of the currently installed and available connectors. If an upgrade is available, you will see both the version of the installed connector and the newest version available. To see details of a specific connector release, click the row in the listing.

manage-connectors

 

To Install or Upgrade a Connector

Details about the current release will appear in the install wizard. The wizard will always show the most current release available. To install a new connector or upgrade to a new release, click the Install button. You will be notified that you need to restart Microsoft Excel for the changes to take effect. If you want to install other connectors, click the < Back button and repeat these steps. Restart Excel when you are done.

manage-connectors-install

 

To License a Paid Connector

When you purchase license(s) for Analytics Edge products, you will be sent a license code for each product (the add-ins and connectors have separate licenses). To activate a connector license, you must open the Manage Connectors wizard and click into the connector, then enter the New License Key and click the Activate button. You should get a “License activated!” response.  Restart Excel to start using the new license.

manage-connectors-activate-license

 

Uninstall a Connector Release

To remove a connector, open the Manage Connectors wizard and click on the connector entry. Click the Uninstall button. Restart Excel for the change to take effect. Note that if you have upgraded a connector, when you restart Excel you will be asked if you want to delete all the older versions as well.

 

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.

combine8

 

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.

combine7

 

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.

compareresults

 

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.

match

 

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

addresses

 

…and a second containing names:

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.

updateresults

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.

keep-duplicates

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.

 

filter

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.

pivot-example-1024x688

 

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.

pivot-names-1024x524

 

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.

read-worksheet-easily

 

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.

append2

append1

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.

Number Format

Analytics Edge supports Microsoft .NET Framework format codes which are slightly different from Excel format codes. The common variants are listed below, but see the references at the end for a full listing.

Format specifier Name Description Examples
“0” Zero placeholder Replaces the zero with the corresponding digit if one is present; otherwise, zero appears in the result string. 1234.5678 (“00000”) -> 01235
0.45678 (“0.00”, en-US) -> 0.46
0.45678 (“0.00”, fr-FR) -> 0,46
“#” Digit placeholder Replaces the “#” symbol with the corresponding digit if one is present; otherwise, no digit appears in the result string. 1234.5678 (“#####”) -> 1235
0.45678 (“#.##”, en-US) -> .46
0.45678 (“#.##”, fr-FR) -> ,46
“.” Decimal point Determines the location of the decimal separator in the result string. 0.45678 (“0.00”, en-US) -> 0.46
0.45678 (“0.00”, fr-FR) -> 0,46
“,” Group separator and number scaling Serves as both a group separator and a number scaling specifier. As a group separator, it inserts a localized group separator character between each group. As a number scaling specifier, it divides a number by 1000 for each comma specified. Group separator specifier:
2147483647 (“##,#”, en-US) -> 2,147,483,647
2147483647 (“##,#”, es-ES) -> 2.147.483.647
Scaling specifier:
2147483647 (“#,#,,”, en-US) -> 2,147
2147483647 (“#,#,,”, es-ES) -> 2.147
“%” Percentage placeholder Multiplies a number by 100 and inserts a localized percentage symbol in the result string. 0.3697 (“%#0.00”, en-US) -> %36.97
0.3697 (“%#0.00”, el-GR) -> %36,97
0.3697 (“##.0 %”, en-US) -> 37.0 %
0.3697 (“##.0 %”, el-GR) -> 37,0 %
“‰” Per mille placeholder Multiplies a number by 1000 and inserts a localized per mille symbol in the result string. 0.03697 (“#0.00‰”, en-US) -> 36.97‰
0.03697 (“#0.00‰”, ru-RU) -> 36,97‰
“E0”
“E+0”
“E-0”
“e0”
“e+0”
“e-0”
Exponential notation If followed by at least one 0 (zero), formats the result using exponential notation. The case of “E” or “e” indicates the case of the exponent symbol in the result string. The number of zeros following the “E” or “e” character determines the minimum number of digits in the exponent. A plus sign (+) indicates that a sign character always precedes the exponent. A minus sign (-) indicates that a sign character precedes only negative exponents. 987654 (“#0.0e0”) -> 98.8e4
1503.92311 (“0.0##e+00”) -> 1.504e+03
1.8901385E-16 (“0.0e+00”) -> 1.9e-16
\ Escape character Causes the next character to be interpreted as a literal rather than as a custom format specifier. 987654 (“\###00\#”) -> #987654#
‘string’
“string”
Literal string delimiter Indicates that the enclosed characters should be copied to the result string unchanged. 68 (“# ‘ degrees'”) -> 68 degrees
68 (“#’ degrees'”) -> 68 degrees
; Section separator Defines sections with separate format strings for positive, negative, and zero numbers. 12.345 (“#0.0#;(#0.0#);-\0-“) -> 12.35
0 (“#0.0#;(#0.0#);-\0-“) -> -0-
-12.345 (“#0.0#;(#0.0#);-\0-“) -> (12.35)
12.345 (“#0.0#;(#0.0#)”) -> 12.35
0 (“#0.0#;(#0.0#)”) -> 0.0
-12.345 (“#0.0#;(#0.0#)”) -> (12.35)
Other All other characters The character is copied to the result string unchanged. 68 (“# °”) -> 68 °

Continue reading

Date Format

Analytics Edge supports Microsoft .NET Framework date format codes which are slightly different from Excel date format codes. The common variants are listed below, but see the references at the end for a full listing.

Format specifier Description Examples
“d” The day of the month, from 1 through 31. 6/1/2009 1:45:30 PM -> 1
6/15/2009 1:45:30 PM -> 15
“dd” The day of the month, from 01 through 31. 6/1/2009 1:45:30 PM -> 01
6/15/2009 1:45:30 PM -> 15
“ddd” The abbreviated name of the day of the week. 6/15/2009 1:45:30 PM -> Mon (en-US)
6/15/2009 1:45:30 PM -> Пн (ru-RU)
6/15/2009 1:45:30 PM -> lun. (fr-FR)
“dddd” The full name of the day of the week. 6/15/2009 1:45:30 PM -> Monday (en-US)
6/15/2009 1:45:30 PM -> понедельник (ru-RU)
6/15/2009 1:45:30 PM -> lundi (fr-FR)
“h” The hour, using a 12-hour clock from 1 to 12. 6/15/2009 1:45:30 AM -> 1
6/15/2009 1:45:30 PM -> 1
“hh” The hour, using a 12-hour clock from 01 to 12. 6/15/2009 1:45:30 AM -> 01
6/15/2009 1:45:30 PM -> 01
“H” The hour, using a 24-hour clock from 0 to 23. 6/15/2009 1:45:30 AM -> 1
6/15/2009 1:45:30 PM -> 13
“HH” The hour, using a 24-hour clock from 00 to 23. 6/15/2009 1:45:30 AM -> 01
6/15/2009 1:45:30 PM -> 13
“K” Time zone information. 6/15/2009 1:45:30 PM, Kind Unspecified ->
6/15/2009 1:45:30 PM, Kind Utc -> Z
6/15/2009 1:45:30 PM, Kind Local -> -07:00 (depends on local computer settings)
“m” The minute, from 0 through 59. 6/15/2009 1:09:30 AM -> 9
6/15/2009 1:09:30 PM -> 9
“mm” The minute, from 00 through 59. 6/15/2009 1:09:30 AM -> 09
6/15/2009 1:09:30 PM -> 09
“M” The month, from 1 through 12. 6/15/2009 1:45:30 PM -> 6
“MM” The month, from 01 through 12. 6/15/2009 1:45:30 PM -> 06
“MMM” The abbreviated name of the month. 6/15/2009 1:45:30 PM -> Jun (en-US)
6/15/2009 1:45:30 PM -> juin (fr-FR)
6/15/2009 1:45:30 PM -> Jun (zu-ZA)
“MMMM” The full name of the month. 6/15/2009 1:45:30 PM -> June (en-US)
6/15/2009 1:45:30 PM -> juni (da-DK)
6/15/2009 1:45:30 PM -> uJuni (zu-ZA)
“s” The second, from 0 through 59. 6/15/2009 1:45:09 PM -> 9
“ss” The second, from 00 through 59. 6/15/2009 1:45:09 PM -> 09
“t” The first character of the AM/PM designator. 6/15/2009 1:45:30 PM -> P (en-US)
6/15/2009 1:45:30 PM -> 午 (ja-JP)
6/15/2009 1:45:30 PM -> (fr-FR)
“tt” The AM/PM designator. 6/15/2009 1:45:30 PM -> PM (en-US)
6/15/2009 1:45:30 PM -> 午後 (ja-JP)
6/15/2009 1:45:30 PM -> (fr-FR)
“y” The year, from 0 to 99. 1/1/0001 12:00:00 AM -> 1
1/1/0900 12:00:00 AM -> 0
1/1/1900 12:00:00 AM -> 0
6/15/2009 1:45:30 PM -> 9
“yy” The year, from 00 to 99. 1/1/0001 12:00:00 AM -> 01
1/1/0900 12:00:00 AM -> 00
1/1/1900 12:00:00 AM -> 00
6/15/2009 1:45:30 PM -> 09
“yyyy” The year as a four-digit number. 1/1/0001 12:00:00 AM -> 0001
1/1/0900 12:00:00 AM -> 0900
1/1/1900 12:00:00 AM -> 1900
6/15/2009 1:45:30 PM -> 2009
“z” Hours offset from UTC, with no leading zeros. 6/15/2009 1:45:30 PM -07:00 -> -7
“zz” Hours offset from UTC, with a leading zero for a single-digit value. 6/15/2009 1:45:30 PM -07:00 -> -07
“zzz” Hours and minutes offset from UTC. 6/15/2009 1:45:30 PM -07:00 -> -07:00
“:” The time separator. 6/15/2009 1:45:30 PM -> : (en-US)
6/15/2009 1:45:30 PM -> . (it-IT)
6/15/2009 1:45:30 PM -> : (ja-JP)
“/” The date separator. 6/15/2009 1:45:30 PM -> / (en-US)
6/15/2009 1:45:30 PM -> – (ar-DZ)
6/15/2009 1:45:30 PM -> . (tr-TR)
“string”
‘string’
Literal string delimiter. 6/15/2009 1:45:30 PM (“arr:” h:m t) -> arr: 1:45 P
6/15/2009 1:45:30 PM (‘arr:’ h:m t) -> arr: 1:45 P

Continue reading

WriteToWorksheet

writetoworksheetThis Analytics Edge wizard is used to write the results of an analysis to a worksheet. It can either overwrite an entire sheet, or be written to a specific location.

It is normally the last step in an analysis sequence, but the macro can continue processing data and write to other worksheets or files.

Worksheet – enter a name for the worksheet to write to (located in the current workbook). If the worksheet does not exist, it will be created.

Replace entire worksheet – removes all data and formatting before writing the contents of the table to the worksheet. Prevents old data from remaining in a worksheet if the new results do not use as many rows or columns.

Insert into worksheet – specific the top-left cell for the results to be written to. This option will only clear a range big enough to write the data from the table.

Do not include header – check this to prevent the column names from being included in the first row of the results written.

Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data.

Transpose – check this to write each column as a row in the worksheet.

Add hyperlinks to web addresses – if the results include web addresses, they will be converted to hyperlinks that can be clicked on.

Format as Excel table – format the output as an Excel table using one of the predefined Excel formats. Select from the formats listed or pick the empty entry for no color formatting.

Updated in version 1.3.1

 

WriteToTextFile

write a table to a text fileThis Analytics Edge wizard lets you write the current table to a delimited text file, specifying the format to be used for dates.

It is usually used to produce a file for uploading to another system, but can also be used to export data that will not fit into an Excel worksheet.

File – enter or browse to the specific file to be saved to.

Delimiter – select or enter a delimiter to place between values as the file is written. Note that if ‘Comma’ is selected, Analytics Edge will use smart delimiting, wrapping text strings that include commas in double quotes and escaping double quotes by pairing them, similar to Excel saving as CSV (MS-DOS).

Do not write the header row – check this to prevent the column names from being included in the first row of the file.

Transpose – check this to write each column as a row in the file.

Desired date format – enter date format codes to determine how dates should be written to the file.

 

Update

update a table from another tableThis Analytics Edge wizard lets you update the current table from entries with matching rows in another table or worksheet.

It is typically used to update a master list with transactional items, and includes options to ignore empty update fields and to only update a field if it is empty.

Update from Table – select a worksheet or previously named table from the drop down list.  The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Replace only empty cells – do not update existing values unless they are empty.

Ignore update if empty – if the reference table contains empty cells, do not modify the matching row in the current table.

Add new columns – add any columns from the reference table that do not exist in the current table.

Add new rows – add rows from the reference table with values in the key columns that only exist there.

Blog article: Using the Update Function

Unpivot

unpivotThis Analytics Edge wizard is used to perform a reverse pivot operation, converting multiple columns of data into two; one with the original column name and the other with the value.

It is typically used to convert a trend report to transactional format for uploading into a database.

Pick Columns – select all of the columns to be unpivoted. For every non-blank cell, a row will be created with the column name and cell value.

Column Title for Column Names – enter a name for the column that will hold the names of the columns in the original table.

Column Title for Cell Values – enter a name for the column that will hold the values from the cells in the original table.

Floating Right Column – select the special column ZZZ in the list if the data may change over time and you want to include any extra columns to the right of those already selected, should they appear.

Total

totalThis Analytics Edge wizard is used to create an aggregate row or column, like Total or Average. You can choose to aggregate only the first or last group of rows or columns, and position the results before or after the data, or to remove the summarized data completely.

This simple wizard lets you do common reporting tasks like creating a total row at the end of your table, or to display the top 10 rows and an average of the rest.

Summarize – select whether you want to aggregate rows or columns in the current table.

Select Rows / Columns – allows you to choose all rows, just the top N rows, or everything expect the top N rows. This last option is useful for top 10 and average of the rest reports.

Aggregate – select whether to use the Sum, Average or other summary function.

Position – if all rows or columns are not removed, choose whether to position the summary row or column before or after the numeric data. Note that if you have multiple text or date columns in the table, a summary column positioned ‘Before’ will appear where the first numeric column was.

Name – assign a name for the new row or column.

Do not remove selected rows / columns – check this box to add the summary row / column to the table, otherwise the selected rows / columns will be replaced with the summary.

New in version 1.3.1

 

 

Top / Bottom

topThis Analytics Edge wizard lets you keep or remove a specific number of the top or bottom rows.

Select whether you want to keep or remove the rows, whether you want to pick the top or bottom, and enter a count for the number of rows.

 

New in version 1.3.1

 

Table Name

table nameThis Analytics Edge wizard lets you save the current table for reference later in the macro, such as with appending or comparing tables. You can also switch back to that table to perform multiple calculations from the same data. You can even use the values in the table as predefined ranges in other functions.

Assign a name to the current table – This allows you to lock the current table in memory and assign it a name for reference later in the macro. It is typically used with one of the comparison or combination functions (Append, Combine, Compare, Match, Update).

Switch to a previously named table – This allows you to switch the focus to a previously named table (as above). It is typically used to go back to an intermediate point in a macro and perform a different analysis on the same source data, such as calculating percentage growth and the calculating difference.

Assign range names to each column of the current table – Analytics Edge can sometimes use defined ranges instead of fixed values. This is typically used to read configuration options or selections from a worksheet, and then to customize the analytics based on the values, such as filtering by a date range or value from a specific cell. Each column in the current table is saved as a range, using the name of the column.

Subtotal

subtotalThis Analytics Edge wizard allows you to subtotal a table based on unique values in selected columns.

It is typically used to produce summary reports from detailed data.

Columns to Subtotal – select columns to be subtotalled by position or By Name. All unique combinations of values from the selected columns will be grouped.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Aggregation – select the aggregation to use when there are duplicate values.

Split

splitThis Analytics Edge wizard lets you split a text column into several columns. You can split on a delimiter or a fixed-width sequence.

It can be used to split name fields apart (use the smart name split function), or to split product codes or URLs.

Pick Column – select a column by position letter (optionally By Name) to be split.

Split on delimiter – select a delimiter that separates the values in the column. Optionally enter your own sequence of characters (may be more than one).

Split fixed width – if the column is to be split at specific character positions, use this option.

Smart name split – select this option to split a column into various name parts.

Split into Columns – splits the selected column into multiple columns, named with suffix -1, -2, etc.

Remove original column – to remove the column that has been split, check this option.

Split into Rows – splits multiple values in the selected column into multiple rows.

Ignore duplicates – if the column contains multiple but identical values, ignore the duplicates and create only one row for each unique value.

split-by-rows

split-into-rows-results

Sort

sort3This Analytics Edge wizard lets you create one or more sorting rules which will be applied in sequence to order your data.

If two rows are equal in value for the first rule, the next rule is used to determine the order. You can specify as many rules as required.

Column – select a column by column letter position (optionally by Name).

Sort On – select a sorting option to use, optionally check Case sensitive to order upper and lower case letters differently.

Order – select whether to sort ascending or descending order.

Add Rule – adds the selections above to the sort rules list. When multiple rules exist, they will be executed in order; if there is a tie in the first rule, the second rule is used to determine order.

Delete – remove the selected rule from the list.

Up / Down – move the selected rule up or down in the list. The rules will be run in order, so the first rule has highest priority.

Continue reading

Save/Email Workbook

save-email-workbookThis wizard lets you save a copy of the Excel workbook and optionally email it to someone.

You can save the copy to the same folder as the original file, to the default report folder, or to a custom folder location.The default report folder is shared across all macros, and would server as a single location to find updated reports, especially useful for those generated by a scheduled refresh.

The file name can be the same as the original with the current date appended to it (the copy MUST have a different file name from the original), or you can assign a custom file name to be used. With the custom option, you can also have Analytics Edge dynamically add the current date to the file name, automatically preserving previous versions.

Emailing reports requires you to configure your email server in the FileOptions wizard.

To send the report via email, check the Send Email box and enter the email addresses of the recipients (separated by commas). Enter a subject and optional message text. You can also receive a copy of your own by checking the Bcc me box. Note that creating or stepping through the macro will NOT send emails, allowing you to test the macro without annoying recipients.

Save/Email PDF

save-email-pdfThis wizard lets you save an Adobe PDF copy of the Excel workbook and optionally email it to someone.

You can save the copy to the same folder as the original file, to the default report folder, or to a custom folder location.The default report folder is shared across all macros, and would server as a single location to find updated reports, especially useful for those generated by a scheduled refresh.

The file name can be the same as the original with the current date appended to it, or you can assign a custom file name to be used. With the custom option, you can also have Analytics Edge dynamically add the current date to the file name, automatically preserving previous versions.

Emailing reports requires you to configure your email server in the FileOptions wizard.

To send the report via email, check the Send Email box and enter the email addresses of the recipients (separated by commas). Enter a subject and optional message text. You can also receive a copy of your own by checking the Bcc me box. Note that creating or stepping through the macro will NOT send emails, allowing you to test the macro without annoying recipients.

Run Macro Function

run-macro-functionAnalytics Edge macros that start with an underscore (e.g. “_setup”) will NOT run when you click the Refresh All button, nor during a Scheduled Refresh. This wizard lets you run those special underscore macros from inside another macro.

This can be used to create a ‘master’ macro that runs other macros in a specific order. It is especially powerful if the ‘master’ macro is a Repeat Macro.

Replace

replaceThis Analytics Edge wizard lets you replace text in selected columns. It supports the usual * and ? wildcard matching.

It is usually used to clean up data for reporting purposes, and you can use this wizard repeatedly to perform a series of replacements.

Pick Columns – select the columns to be modified either by column letter position or By Name.

Replace matching characters – this is the simplest form of the function. Characters you enter in the Find Text field will be replaced with characters in the Replace With field. It will replace characters anywhere in the cell value, even if they occur more than once.

Replace entire cell – this option allows you to use a wildcard match (* for any number of characters, ? for a single character), and only cells that match the whole cell value will be changed. If you use a * in the Replace With field, the original cell value will be inserted (useful for adding text before or after the original cell value).

Use regular expressions – you can also use a regular expression match and replacement. This is an advanced option, and knowledge of regular expressions in needed.
ref: http://msdn.microsoft.com/en-us/library/az24scfc(v=vs.100).aspx

Ignore case – check this option to match both upper and lower case letters.

 

Repeat Macro

repeatmacroThis wizard converts the current macro into a Repeating Macro. Repeating macros allow you to load a tables of values from a worksheet to be used as Analytics Edge named ranges while running the rest of the macro.

By default, the macro will be repeated for each row in the table loaded. For example, if the selected worksheet contains a column labelled ‘id’, other functions in the macro can refer to the range name [id] in place of a website view id number or email campaign id number. Each pass through the macro would process a different view or campaign.

Workbook / Worksheet: The referenced worksheet can be from a separate workbook, so that the list of account information is not included in the refreshed workbook. This is especially useful when refreshing a template report for a list of clients, so the client list is not part of the workbook you send to the client.

Rows to use: The function also allows you to process more than 1 row per cycle. The rest of the macro will have to know how to process more than one value in the ranges.

Setup – Clear Worksheet: Before the first row is processed, a selected worksheet can be cleared of all data. This can be used with Append To Worksheet to process all rows and combine the results to a target worksheet, clearing the data before the macro is run.

Keep header row: option to keep the header row of the target worksheet when clearing the data. This is useful to maintain the order of the columns when using the AppendToWorksheet function.

For additional information, see Using the Repeat Macro Function

Read Worksheet

readworksheetThis Analytics Edge wizard allows you to read an Excel worksheet into a table. The worksheet can be in any open workbook, and it is possible to read part of a worksheet if desired.

You can use this function to read data downloaded or imported form other data sources, or to consolidate data from other workbooks.

Workbook/Worksheet – select the workbook/worksheet combination you want to read from. Workbooks must be open to record the macro, but do not need to be open to run the macro.

Read the entire worksheet – select this option if the data is located starting in cell A1, and the whole worksheet is data.

Read a table in the worksheet – select this option, and pick a top left cell, to read a table located somewhere in a worksheet that may contain other data. The table must contain continuous data in the first row and column (no blanks).

Read a specific region only – if the data might contain blank cells, select this option and identify the entire range of cells to be read.

Data does not include a header – check this if the top row of data is not the column headers (column names).

Data is transposed – if the data is oriented by rows instead of columns, check this option to transpose the data as it is read in.

Load direct to a named table – this option lets you load a reference table from a worksheet in one step, as opposed to loading it and using Table Name to establish the reference.

Assign range names to each column – assigns Analytics Edge range names to each column that is read in. These range names can be used in the place of fixed values in other Analytics Edge wizards by enclosing them in square brackets [Column Name].

Blog article : Using the Read Worksheet Function

Updated in version 1.3.1

Read Text File

readtextfileThis Analytics Edge wizard allows you to import a text file into a table. I accepts both delimited files as well as fixed-width fields.

While the date format of the source file can be specified, you can follow up with a Convert function to handle any text, number or date conversions desired.

File/URL – enter or browse to the specific file to be loaded, or enter a direct URL to a web-based file.

Split on delimiter – select or enter a delimiter to use when reading the file.

Split into fixed width fields – if the file uses fixed width fields, select this option and continue to the next wizard panel (below).

File type – read a special file type. Currently only JSON files can be read.

File does not contain a header row – select this if the data does not contain column names.

Transpose – read the data in, swapping rows of data into columns. The first column in the row will be treated as the transposed column name.

Date format in file – enter codes to represent the exact date format used in the source file. Analytics Edge will recognize columns as dates if all of the values match that format. Pay specific attention to matching single or two-digit days, months or time components.

Continue reading

Rank

rank order data in ExcelThis Analytics Edge wizard is used to convert the selected column into a number representing either the rank order or it’s percentile, decile or quartile position.

It is typically used to segment data or to establish a normalized value for further analysis, such as using Filter and Match to obtain details for the top 10%.

Change Column Into – select a ranking option to use.

Rank – convert the value to a rank number starting at 1 and counting up. Duplicate rows will have the same value, and the next number will be skipped.

Percentile – split the column into 100 ordered groups and convert the value to a number between 1 and 100.

Decile – split the column into 10 ordered groups and convert the value to a number between 1 and 10.

Quartile – split the column into 4 ordered groups and convert the value to a number between 1 and 4.

Order – select whether larger values will rank higher (e.g. counting up), or whether larger values will rank lower (e.g. top 10).

Pick Columns – select the columns by position letter (optionally By Name) to change into a numeric rank column.

Pivot

pivot-bynameThis Analytics Edge wizard is used to pivot a column of data similar to Excel’s pivot table. If you are pivoting by date, you can select the date range and automatically fill in any missing values.

This function is typically used to produce time trend reports, converting tabular data into a more usable form.

Arrange Columns to Keep – select and rearrange the columns you want to keep in the resulting table.

By Name – select the columns by the name of the column instead of selecting them by column position (A, B, C, etc).

Move Up or Down – move the selected column up or down in the list.

Remove – remove the selected columns from the list.

Ignore Case – when combining duplicate rows, ignore differences in upper or lower case.

Reset – reset the list of columns to the original.

Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column.

Ignore blanks – if the pivoted column contains blank cells, ignore them and do not create a column with a blank name.

Cell Values – select a column to be used for the values in the cells in the pivoted table.

Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.

Continue reading

Options

This wizard lets you configure Excel Pivot Tables refresh options and Email server settings.

Pivot Tables

You can have Analytics Edge automatically refresh Excel Pivot Tables in your workbooks after a macro runs. Normally they would be need to be refreshed manually after your data changes.

options-autorefresh-pivot-tables

If you want to send emails from your macros, you will need to set your email server settings. Analytics Edge supports most SMTP servers, and defaults to the values used by Google’s Gmail servers.

Email

Start by entering the Email Address your messages will be from, and the Name to be displayed. Then enter the SMTP server settings for your server. Most servers require authentication, so you will need to enter your email account and password.

Gmail users: must enable ‘Access for less secure apps‘ in your account, and enter your user name (email address ) and password in the Options > Email form.

options-email

Privacy Note: your email account and password are stored, strongly encrypted, on your computer. They are not included in any Excel workbooks, nor are they shared or passed to any other application.

 

Match

matchThis Analytics Edge wizard is used to keep or remove rows based on whether matching row values exist in another table or worksheet.

It is usually used by taking the result of one analysis (top product sales, most popular links) and filtering a detail report to see what contributed to the ranking.

Match with Table – select a worksheet or previously named table from the drop down list.  The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Keep matching rows – keep only rows that have matching rows in the reference table.

Remove matching rows – remove rows that have matching rows in the reference table.

Blog article: Using the Match Function

Join

join

Typical uses include combining name or address components into a single column for uploading into a database.

Pick Column – select a column by position letter (optionally By Name) to be included in the Join operation.

>> – add the selected column(s) to the list of Columns to be joined.

<< – remove the selected column(s) from the list of Columns to be joined.

New Column Name – enter a name for the new column that will be created containing the joined contents.

Using Delimiter – select a delimiter to use between values of the columns being joined. Optionally enter your own sequence of characters (may be more than one).

Ignore missing values – do not include a delimiter when one of the columns has no value.

Remove original columns – after creating a new column with the joined values, remove the original columns from the table.

 

Formula

add a calculated column to ExcelThis Analytics Edge wizard, used at the end of a macro, allows you to populate a column with an Excel formula, giving you full access to Excel’s advanced functions. It must be the last transformation in the sequence.

The formulas are created with column references only, and Analytics Edge will automatically populate the row numbers as it writes to the worksheet (WriteToWorksheet).

Name – enter a name for the new column that will be created.

Formula – enter an Excel formula to be used in each row of the column. Reference other columns by picking them from the list at right. Do not enter cell references unless they are absolute cell references (e.g. $A$1).

Pick Column – select a column to add to the formula (optionally By Name). This will insert a column reference into the formula.

Analytics Edge will expand the formula, filling in cell references with the appropriate column and row coordinates, when the data is written out to a worksheet. Note that formula columns will be dropped if you apply other functions before writing to a worksheet, so add this as your next-to-last step in the macro.

Filter

filterThis Analytics Edge wizard lets you construct one or more filters to be applied to the current table. Filters can be combined using AND-OR logic, and you can choose to remove or keep the matching rows.

Typically used to remove unwanted data from a report, it can also selectively keep rows of interest.

Column – select a column by column letter position (optionally by Name).

Criteria – select a filter criteria or test to use.

Value – enter a value to use for the filter test. For text columns, optionally check Case sensitive to match upper and lower case letter exactly.

Add AND Filter – add the filter rules (Column, Criteria and Value) to the list using AND logic, meaning both this rule in addition to a previous rule entered are required to cause a match.

Add OR Filter – add the filter rules (Column, Criteria and Value) to the list using OR logic, meaning either this rule or a previous rule entered will cause a match.

Delete – delete a selected filter rule from the list shown.

Move Up or Down – move a selected filter rule up or down in the list shown.

Keep Matches – keep rows that match all of the filter rules.

Remove Matches – remove rows that match all of the filter rules.

Remove Empty Rows – if a row contains no information in any of the columns, remove it.

Remove Empty Columns – if a column contains no information in any of the rows, remove it.

Stop macro if empty – if the table is empty, then stop the macro. This does not cause an error condition, so other macros will continue to run if Refresh All or Scheduled refresh was used.

Blog article: Using the Filter Function

Duplicate

duplicatesThis Analytics Edge widget recognizes duplicate rows in your data and lets you choose whether to keep them, remove them or combine them.

Typically used to remove duplications in lists, it can also summarize your data for unique values in specific columns.

Select Columns – select columns by position (shown with column letter for reference), or By Name, Duplicate rows are rows with the same values as another row in all of the selected columns.

Duplicates – select to keep or remove all rows that have duplicates, or to keep a single row for each, aggregating values in the other, unselected columns. For text columns, the ‘All (list)’ option will contain a comma-separated list of all values.

Singles – select to keep or remove rows that do not have duplicate rows in the table.

Ignore case – when performing the duplicate check, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the duplicate check, match rows even if they differ because of different spacing (leading, trailing or within the text).

Add Count column – add a column to the table, named ‘Count’, with a count of the number of rows represented.

Blog article: Using the Duplicates Function

Convert

convertThis Analytics Edge wizard is used to convert columns from one data type (text, number, or date) into another. It can also convert to a variant of the same type, like converting dates to first of the month.

Typically used to force imported data into the correct type (such as dates with odd formats), it can also be used to convert dates and numbers into specific formats for reporting purposes.

Select Column – select a column in the list, shown with the column letter for position reference or optionally By Name, then click the Next button to choose options. Depending on the type of column, you will be given additional options to convert the selected column from text, number or date formats into different text, number or date formats. You will be redirected back here, where you can either convert another column or click Finish.

Note that you can convert within a type, such as truncating text, rounding numbers, and converting to first of the month.

Continue reading

Compare

This Analytics Edge wizard is used to compare the current table with another, computing the difference between matching rows.

compare-tablesCompare Tables

This option tab replaces the numbers and dates in the table with the calculated comparisons.

Compare to Table – select a worksheet or previously named table from the drop down list.  The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Handling Matches – where both tables have matching values in the key columns, compare the values of the remaining number and date columns using the selected options.

Blog article: Using the Compare Function

compare-columnsCompare Columns

This option is used to create a new column containing the comparison between a specific column in the current table with the reference table.

Combine

combineThis Analytics Edge wizard is used to combine two sets of data, merging the values from a worksheet or previously named table.

It is generally used with two similar data sets, totalling the numbers, retaining the most recent date, and even preserving a list of reference values.

Combine with Table – select a worksheet or a previously named table from the drop down list. The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Match by Row Number – select this option to combine the tables by row number. This is useful if the tables have no common columns.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Add new rows – automatically add rows that are only in the referenced table.

Add new columns – automatically add columns that are only in the referenced table.

Handling Duplicates – where both tables have matching values in the key columns, combine the values of the remaining columns using the selected options. The ‘All (list)’ option will produce a comma-separated list of values in each cell.

Blog article: Using the Combine Function

Calculate

calculate1This Analytics Edge wizard lets you perform typical math calculations without formulas, including a number of series calculations across rows or down columns.

Common uses include calculating period-to-period growth rates, levelling out spikes with rolling averages, and scaling numbers to report in thousands or millions.

Add a New Column – You can choose to add a new column to your table, and the new column will contain the results of the calculation.

Replace Existing Values – Alternatively, you can choose to replace the values in the table with the results of the calculation, such as a rolling average or percent of column total.

calculate2New Column Name – enter a name for the column to be added.

1-Column Math Functions – these functions allow you to use the value from the selected column and a number, such as add 1 or multiply by 100, and put the result in the new column. Note that the order is important for functions like subtract and divide, so functions with both arrangements are available.

1-Column Relative Functions – these functions allow you to use the value from the selected column and and express it relative to an aggregate of that column, such as percent of total (Divide by Sum). Note that the order is important for functions like subtract and divide, so functions with both arrangements are available.

2-Column Math Functions – these functions allow you to use the values from two selected columns in the calculation, and put the result in the new column. Note that some

Multi-Column Math Functions – these functions allow you to use values from a range of columns in the calculation, such as the sum of column C through F.

calculate3Pick Columns – The columns in the table are listed with their column letter (A, B, C, etc) for reference. Select one or more columns (they will be highlighted in yellow), then select one of the match operations to apply.

To select multiple individual columns, you can hold the Ctrl-key down and click. To select a range, pick one then hold the Shift-key down and select the other end of the range, or hold the mouse key down and drag down the list.

By Name – check this box to select the columns using the name of the column instead of its position. This is useful if the column order might change over time. Note that if the column names might change (dates, for example), do not use this option.

Math – select an operation and enter a value. Adds, subtracts, multiplies or divides the selected columns by the value entered. For example, divide by 1000 could be used to shows numbers in thousands.

Cumulative – select a function and a direction. Performs a cumulative operation down each column selected, or across the rows of the selected columns. For example, a cumulative sum down each column is also referred to as a running total where each cell would contain the sum of all of the cells above it.

Repeating – select a function and a direction. Performs the operation down each column selected, or across the rows of the selected columns. For example, repeating difference down the column would replace each cell value with the difference from the cell above it.

Rolling (moving) – enter a value and select a function and direction. Performs a rolling function down each column selected, or across the rows of the selected columns.

Relative – select the type of comparison, the axis to compare to, and the aggregate to compare with. Each of the selected columns is changed to that new comparison value.

Floating Right Column – automatically selects columns to the right of any selected columns. This is typically used with reports that have an unknown or inconsistent number of columns, such as days of the month.

Updated in version 1.3.1

 

Arrange

This Analytics Edge wizard is used to rearrange and/or rename columns in a table.

Typically used to change the column order or names for a report, it can also be used to make column names consistent with other tables so that comparison functions like Compare, Match, or Combine can recognize matching columns.

Blog article: Using the Arrange Function

arrange-by-positionBy Position

Use this option to rearrange the columns based on the original column positions (Excel columns A, B, C, etc).

Column Names and Order – The columns in the table are listed with their original column letter (A, B, C, etc) for reference. Select a column to modify (it will be highlighted in yellow), then select one of the actions.

Move selected up – move the selected column up in the list (will move it to the left in the table) The original column letter stays in the listing for reference.

Remove selected column – remove the selected column from the listing. Use Reset to get all of the original columns back if necessary.

Move selected down – move the selected column down in the list (will move it to the right in the table) The original column letter stays in the listing for reference.

Rename – select a column in the list, then enter a new column name in the text box beside the button and click the button to assign a new name to the selected column. The original column letter remains in the list for reference.

Reverse – completely reverse the order of all of the columns in the list from top to bottom (left to right in the table).

Reset – reset the list of columns back to the original. This removes all reorder and name changes previously made.

arrange-by-nameBy Name

Use this option to rearrange the columns based on the names of the columns. This is useful when you get imported data that may have additional, missing, or out-of-order columns.

Column Names and Order – The columns in the table are listed by name. Select a column to modify (it will be highlighted in yellow), then select one of the actions.

The options on this tab are similar to By Position above.

Stop macro if column missing – is used to stop process of the macro if a named column is missing in the table. This is useful to verify downloaded or imported data before processing.

Append – select a column in the list, then enter a new column name in the text box beside the button and click the button to create a new column following the selected column. This can be used to force certain columns in a report even if the source data may not contain those columns.

arrange-insertInsert Column

Use this option to insert a new (empty) column with a specific name at a specific location. The new column is inserted at the selected location, so the column selected is shifted to the right.

Before/After – insert the new column before or after the selected column.

By Name – use the name of the selected column instead of the column letter (position).

New Column Name – the title for the new empty column.

Initial Text Value – used to prepopulate the entire column with a specific text value.

arrange-deleteDelete Column

Use this option to delete one or more columns in the current table.

By Name – allows you select the column by name instead of position.

arrange-sort-by-nameSort by Name

Use this option tab to sort the columns by the names of the columns themselves. Select whether to sort alphabetically, numerically, or by date/time order.

Date/time sorting will recognize several date formats depending on the locale settings of your computer.

arrange-multi-row-recordsMultiRow Records

Downloads and file imports will typically create one row per record, but if a record contains multiple items, like a phone number, they may be loaded into separate columns. This function will align columns with the same name (but having -1, -2, -3 endings), but will result in multiple rows for a single record.

Append

appendThis Analytics Edge wizard makes it easy to merge two tables together, keeping all of the rows and columns of the original tables.

It would typically be used by loading data from a file (ReadTextFile) or worksheet (ReadWorksheet), and appending more data directly from a worksheet or from a previously named table in memory (TableName).

Append From Table – select a worksheet name or a previously named table from the drop down list. If you select a worksheet, the worksheet must contain data in columns with a header row starting in cell A1.

Blog article: Using the Append Function

* Analytics Edge Core Add-in

The Analytics Edge Core Add-in does not work like the Analytics Edge Basic Add-in.
See the Orientation page for the important differences.

FIRST: REGISTER THE ADD-IN

Your first step is to Register the add-in you have installed. Just start Excel, select the Analytics Edge ribbon, and click the Register button. The Register button will change to Check License once the add-in is activated.  Don’t see the ribbon?

register

Click the Activate 30-Day License to start your free trial period. No payment is necessary and the product is fully functional. OPTIONAL: Enter your email address to stay informed of updates to the product and make it easier to reset or transfer your product licenses after computer updates.

 LOOKING FOR MORE DATA?

See the Orientation page for simple instructions.

ae-step-results

Schedule Refresh

schedule-refresh-2This Analytics Edge wizard helps you to schedule unattended refreshes for the workbook you have open.

Conditions required for the refresh to occur:

  • your computer must be turned on
  • you must be logged in (screen lock/screen saver is ok)
  • the Analytics Edge Core Add-in must be enabled and licensed in Excel
  • the workbook must be available but not opened

The Analytics Edge Core Add-in includes a Scheduled Refresh that can be used to trigger unattended refreshes of specific workbooks. The Scheduled Refresh runs on your computer (not a server), so your computer must be turned on for it to work.

Only one schedule can be created per workbook. If you open the Schedule Refresh wizard from a workbook that already has a schedule, the wizard will edit the existing schedule.

Abort Refresh after # minutes

The scheduler will wait a period of time to allow a scheduled refresh to complete and prevent other refreshes from running. You can specify how long the scheduler should wait before abandoning the process and starting another refresh.

One-Time Refresh

Select the One Time radio button, select the First Run At date and time for the refresh, and click the Set button.

Daily Refresh

To refresh at a specific time every few days, select the Daily radio button, and enter the number of days between refreshes (every day = 1; every second day = 2). Select the First Run At date and time for the refresh, and click the Set button.

Weekly Refresh

To refresh on specific days of the week, select the Weekly radio button, select the days you want the refresh to occur on and the number of weeks between refreshes (e.g. every second Tuesday). To select multiple days, hold the Ctrl-key down while clicking on the days. Select the First Run At date and time for the refresh, and click the Set button.

Monthly Refresh

To refresh on a monthly basis, select the Monthly radio button, and select the months desired. If you want specific days of specific weeks of the month, select the ‘on’ radio button, select the weeks and days desired.

If you want to refresh on a specific day of the month (2nd of the month, every 3 months), then select the ‘days’ radio button and select the days desired. Hold the Crtl-key down to select multiple days or months.

Select the First Run At date and time for the refresh, and click the Set button.

Other Scheduled Refreshes

Any number of workbooks can be scheduled, and they can be managed individually from the Schedule Refresh wizard when the workbook is open. You can also launch the Schedule Manager to see all scheduled refreshes.

You may discover that the scheduled refreshes are trigger via the Windows Task Scheduler. If you manually edit the entries in the Task Scheduler directly, they may not run correctly.