# ISO Week Reporting

When a connector (API) does not offer the ability to make weekly reports based on international or ISO weeks (Monday to Sunday), you can use the Analytics Edge Core Add-in‘s Repeat Macro functionality to fill in the gap. It is a 2-step process:

• create a list of date ranges for the queries
• repeat a query for each date range in the list

The Repeat Macro function lets you do this without building queries for each of the date ranges; you build one query and let it ‘repeat’.

## Creating a List of Dates

You have a choice here: use Excel formulas to calculate a series of start and end dates for each week desired in the report, or use some automated method to get the same information. In this article, I will use simple Excel formulas.

The key here is a starting date — I will use the most recent Sunday past. In Excel, we have the WEEKDAY() function that returns a 1 for Sunday, 2 for Monday…and 7 for Saturday. If today is Sunday, we want LAST Sunday, otherwise we want the Sunday of this week. The rest of the dates would simply be 6 days before that (start of the same week) and 7-day offsets for previous weeks. The Excel formulas would be:

 StartDate EndDate =B2-6 =TODAY()-IF(WEEKDAY(TODAY())=1,7,WEEKDAY(TODAY())-1) =A2-7 =B2-7 =A3-7 =B3-7 =A4-7 =B4-7 =A5-7 =B5-7 =A6-7 =B6-7 =A7-7 =B7-7 =A8-7 =B8-7 =A9-7 =B9-7

## Creating the Repeat Macro

Before we begin, create a worksheet to collect the results of our weekly queries. This will have to be a worksheet all on its own, with nothing else on it. Assign it the final name you want to give it — in this example, I will use ‘ISO Week Data’, and the worksheet containing the dates I have called ‘Dates’.

### New Repeat Macro

In the Analytics Edge Core Add-in, start a repeat macro with File > New Repeat Macro.

Worksheet: select the worksheet with the date formulas; in this case, Dates.

Read a table…: I have elected to select a range from the Dates worksheet starting at cell A1. This allows me to use the Dates worksheet for other data (for other queries).

Clear Worksheet: select the target worksheet, ISO Week Data, and check the box to Clear Worksheet before the first iteration (starts the macro with a clean worksheet).

### Converting the Dates to Text

You need to convert the dates to text strings for the connector.  Just use the Convert function to change both the StartDate and EndDate columns using the format yyyy-MM-dd.

Now make the query you want, using the [StartDate] and [EndDate] column references (using the column names with square brackets around them lets you use them like range names or variables). In this example, I show a Bing Ads query Date tab.

### Insert the Date Columns

Since the query itself does not contain the date range used, you will need to add that information. The Arrange function has an Insert capability that can be used for this — insert a new column named StartDate that contains the initial value of [StartDate] — this will be populated by the macro with the actual date being used.  Use Arrange > Insert again for the EndDate in the same way.

### Append the Results to a Worksheet

As the final step, use the Append to Worksheet function to add the results to the worksheet you created for them (the one you cleared when the Repeat Macro was set up). Since the Repeat Macro clears it when it starts, the worksheet will be left with all of the new query results appended to each other.

## The Results

Now Refresh the macro, and it will repeat the query for each row in your Dates worksheet, appending the results to the ISO Week Data worksheet.

# Running Queries from Buttons

With v2.0.0 of the Analytics Edge Standard Add-in, it is now possible to run a specific Analytics Edge query from a button on your worksheet (the Analytics Edge Standard Add-in must be installed). This opens the door to interactive refreshes  and improved user experiences.

Starting with a workbook containing an Analytics Edge query, first select the cell containing the query. A comment box should pop up containing the name of the Analytics Edge Macro (e.g. Sheet1!\$A\$1):

CAUTION: This will ONLY work on worksheets with names that do NOT include spaces.

When prompted for the Macro name, enter AnalyticsEdge.RunMacro. Click OK.

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 (remove any ! or \$ characters, so Report!\$C\$15 becomes ReportC15). You enter the button name in the Name box, located to the left of the formula bar:

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

# Formatting Notes

Once installed and registered, the Analytics Edge Standard Add-in can be used to quickly refresh multiple queries in your workbook. Simply select a destination cell and make a query, then repeat. To refresh all of the queries, click the Refresh All button on the Analytics Edge ribbon.

Some things worth pointing out:

Formatting will be preserved. If you format the cells in Excel, a refresh should not change the format (including conditional formatting) you have done. Note: if the data changes from text, date or numeric to a different type, the cell formatting may be confused by the new data and you may need to reset it to General.

Table formatting will be preserved. If you format a range as an Excel table, a refresh should preserve the table formatting EXCEPT: added columns, rows or calculated values will be lost. The table will be auto-resized to the data coming from the query.

Existing data blocks will be cleared. Analytics Edge looks for an empty row or column to determine the limits of the existing data ‘block’ or table in the worksheet. Everything inside that block will be cleared before the new data is written to the worksheet.

No merged cells. Analytics Edge does not support writing data to merged cells in Excel. Trying to do this will cause an error.

# 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 (support@analyticsedge.com) 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.

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.

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

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.

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

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.

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

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.

# 01 Formatting Notes

Once installed and registered, the Analytics Edge Basic Add-in can be used to quickly refresh multiple queries in your workbook. Simply select a destination cell and make a query, then repeat. To refresh all of the queries, click the Refresh All button on the Analytics Edge ribbon.

Some things worth pointing out:

Formatting will be preserved. If you format the cells in Excel, a refresh should not change the format (including conditional formatting) you have done. Note: if the data changes from text, date or numeric to a different type, the cell formatting may be confused by the new data and you may need to reset it to General.

Table formatting will be preserved. If you format a range as an Excel table, a refresh should preserve the table formatting EXCEPT: added columns, rows or calculated values will be lost. The table will be auto-resized to the data coming from the query.

Existing data blocks will be cleared. Analytics Edge looks for an empty row or column to determine the limits of the existing data ‘block’ or table in the worksheet. Everything inside that block will be cleared before the new data is written to the worksheet.

No merged cells. Analytics Edge does not support writing data to merged cells in Excel. Trying to do this will cause an error.

### Open the Accounts Wizard

From the Google Analytics (Free or Pro) button on the ribbon bar, select Accounts from the menu. The Accounts wizard will open.

The Free Google Analytics connector and the Google Analytics Pro connector share their saved accounts, and they are upwards compatible from Free to Pro.

### Login and Create a Default Account

For security reasons, Analytics Edge stores your account information on your computer, not in the workbooks. Accounts are stored using Reference names, and the queries use a Reference name to identify which account to use. Before you can log into an account, you must enter a Reference name in the field provided, then click the Add Account button.

The Google Analytics Pro connector provides two option checkboxes which must be checked if you want to use elevated permissions for uploading data or managing the account.

Select your new Account in the list at the bottom, pick the Property and View you want to use as a default, and click the Make Default button, then you can Close the wizard.

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

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

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

# 04 Your First Query (video)

Most people use Analytics Edge to download data from one of the connectors into Excel. With the Analytics Edge Core Add-in, this is a 2-step process:

2. write the results to Excel.

Here’s a quick video showing the process, getting data from Google Analytics: watch fullscreen

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

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

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

# 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

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

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

If you experience any problems with the product registration process, contact support@analyticsedge.com for assistance.

# Getting Started with the Constant Contact Connector

### Installation and Activation

To get started with Analytics Edge and the Constant Contact connector, you need to install and register the Analytics Edge Basic Add-in or the Analytics Edge Core Add-in. Then the Constant Contact connector needs to be installed — it will automatically activate a 30 day trial for you.

### Constant Contact Account Wizard

Open the Constant ContactAccounts wizard from the Analytics Edge  ribbon bar. All of the connector wizards are available from a drop-down menu that appears when you click the connector’s button.

The button may appear as a large square, or it may appear as a wide button as shown in the image.

On the Accounts wizard, enter a name for the account and click the Add Account button. Note that the account name does not have to match your login account — it is used to identify the account in Analytics Edge, so it should be meaningful to you.

A second dialog will confirm that you are allowing Analytics Edge to access your Constant Contact account.

The new account will be added to the list of Constant Contact Accounts and it will be tagged (*) as the default account to use.

Close the Accounts wizard.

### Get A List of Recent Campaigns

click image to enlarge

Open the Constant ContactCampaigns wizard. By default, the wizard is configured to get all campaigns for the account.

You might want to limit the results to SENT campaigns, and only the most recent 10.

Click Finish to run the query.

From the results, we can see that each campaign has a unique id number.  To get details on a campaign, we need to use that id number to identify the campaign of interest.

Analytics Edge Core Add-in Users: you will be looking at a temporary worksheet with green shading on the cells. This is what Analytics Edge is working with in memory. Only a sample of a large query will be shown. [optional] If you want the results in a worksheet, click the Analytics Edge ribbon File > Write to Current Cell menu option.

### Get Details For A Campaign

Now that you have the campaign id’s, you can get information about any of the campaigns: summary statistics, opens, clicks, bounces, etc. We can start with simply getting more information about the campaign itself, such as the subject line and summary statistics. To do this, we open the Constant ContactCampaigns wizard again, and select Get a campaign.

Either enter a campaign id from the list you had previously, or click the lookup  button to pop up a list that you can select a specific campaign. Click Finish to run the query. From the results we get  a lot of information about the campaign.

Information Available

The campaign details includes information about the campaign email itself, like subject, who it is from, the greeting used, and various other settings. It also includes tracking summary counts of sends, opens, clicks, etc. Finally, all the links being tracked in the campaign are included along with the link id (url_uid) number and count of clicks.

Option: Use a Cell Reference

The wizard also allows you to get the campaign id from a worksheet cell, such as the most recent campaign sent from the previous query. To do this, click the cell reference  button and select the worksheet cell where the id number is stored.  if you select a range of cells, only the first will be used.

Analytics Edge Core Add-in Users: Use a Range Name Option

With the Core Add-in, you can use the results of one query to drive the next by using a [Range Name] reference. If you want to use the campaign id’s from the “id” column of the previous query, enter “[id]” (without the quotes). Analytics Edge will get details for all of the campaigns listed in the “id” column; if you picked 10 campaigns in the previous query, it would make 10 queries and deliver 10 rows.

Analytics Edge Core Add-in Users: Optional Next Steps

You can select a subset of columns that you want to keep using the TableArrange wizard. Use the By Name option to select the columns. You can also do other things like calculate Open Rate with the ColumnCalculate wizard.

click image to enlarge

You can download a detailed list of who opened a specific campaign using the Constant ContactCampaign Reports wizard.

Select opens and pick a campaign id, then click Finish. Using a limit of 0 will retrieve all of the opens for the campaign.

The results may include what appear to be duplicates, but they are actually individual contacts that have opened your email multiple times.

Analytics Edge Core Add-in Users: you can use the TableDuplicates wizard to get rid of the duplication, keeping the earliest open date/time.

You can download the bounces, clicks, forwards, sends and unsubscribes in a similar fashion.

To download all the contacts from Constant Contact, use the Contacts wizard, get contacts option. You should select the ACTIVE status so you don’t bother downloading the removed, unconfirmed or opt-out entries.

Information Available

The contact list includes all the available information about the contacts as well as the lists that they belong to. Contacts are uniquely identified by an id number, and the lists are identified by the list id number.

Using the Contact Reports wizard you can look into the behaviour of specific contacts (identified by a contact id number). Caution: if you click the lookup  button, you will have to wait while the entire contact list is downloaded.

You can also get a summary by campaign, or individual types of activities like opens, sends or clicks.

Campaigns are identified by campaign id number. You will need to cross reference with the results of a campaigns query (above) to get information about which campaign is which.

Analytics Edge Core Add-in Users: you can use the Multiple Combine wizard to get the campaign name or subject line added to your results. The campaign_id column will need to be renamed ‘id’ using the Arrange wizard before the tables can be combined; they need to share a ‘key’ column.

### Next Steps

You have all the basics to using the Constant Contact connector to download your campaign and contact data. You can also get information about the various Contact Lists you have in your account. That wizard works similarly to the Contacts wizard.

Although you can use the Analytics Edge Basic Add-in to download your data, you will find the calculations and lookups required to generate a finished report are much easier to perform with the Core Add-in functions.

### Open the Analytics Reporting Wizard

Form the Analytics Edge ribbon, pick the Google Analytics > Analytics Reporting menu to open the query wizard.  You must select a View, at least one metric Field, and a Date range for a valid query, and there are many other parameters available.

### Select a View

On the Views tab, select a Login, then click the View you want to use — it will highlight in yellow, and information about the view will be displayed to the right.

### Select Fields

On the Fields tab, you must can select up to 7 dimensions and 10 metrics. Select the field you want, then click the Add button (or double-click on the field name) to add the field to the selected lists to the right.

You can type in the filter box to find specific fields, and restrict the list to Dimension or Metrics by clicking the options at the top. Deprecated fields are ones that Google doesn’t want you to use any more, and they are hidden by default.

### Select a Date Range

On the Dates tab, select one of the Preset date ranges or use a combination of date options. Date ranges can be specified with a Start and End, Start and Duration, or Duration and End.

Start and End dates can be selected from the date selectors, picked from a list of preset items (like Today or Last Month), or a value can be entered. The button to the right of the value box can be used to create an Excel cell reference, so Analytics Edge will use the date in that cell when the query is refreshed.

Click Finish to run your query. Note that the Finish button will be disabled if you have not selected a View, selected at least one metric, and picked a date range.

# 01 Orientation Video

See how easy it is to get your Google Analytics data in Excel!  Watch Fullscreen

This video provides an overview of the Analytics Edge connector for Google Analytics, used from the free Analytics Edge Basic Add-in. Upgrade to the Analytics Edge Core Add-in for even more reporting automation possibilities.

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.

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.

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

Most 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