The Repeat Macro function in Analytics Edge lets you cycle through a list of items, repeating the rest of the Analytics Edge macro for each line in the source list. This article shows you how to use it.
The Repeat Macro function makes it possible to report on multiple accounts, multiple web sites, multiple filter expressions, or multiple date ranges.
The operation of a Repeat Macro is simple: you identify a source worksheet from which to get a table of values. The rest of the Analytics Edge macro will be repeated for each row in the table. If you have 30 clients, create 30 rows in your source worksheet, one for each client. Want 5 date ranges; create 5 rows with date ranges. The macro will be repeated for each row in the source data.
A typical Repeat Macro will involve only a few steps.
Step 1. Setup
Repeat macros work by processing rows from a list, so start with a worksheet that has columns for each of the items that may change from one cycle to the next. You should also add a new worksheet to collect the results. In this example, the worksheets will be named Setup and Results.
Step 2. Create a Repeating Macro
Create a new Analytics Edge macro:
- select the AE Macro mode on the ribbon
- pick –new macro– in the task pane dropdown
- enter a name for the macro and click OK
- select File/Workbook > New Repeat Macro from the ribbon
When the wizard opens, select the list created in step 1 (Setup worksheet, top left cell A1). Also select the option at the bottom to clear the Results worksheet before running.
When you click OK, the function runs and the first row is displayed in the AE Step Results temporary worksheet, showing you what data is available to the rest of the macro.
Note that the values can be referred to by referencing the column names enclosed in square brackets, like [Account] and [Website].
Step 2. Make a Query
Next, get the data you want using one of the connector functions. In this example, Google Search Console data is being pulled using the Google Search > Search Analytics wizard.
Note how ‘[Account]’ and ‘[Website]’ values are being entered — many function wizard selections allow you to enter values like this. The macro will use the specified column values when it runs.
Step 3. Label the Results
When the query runs, the results usually do not include any identifier — like what account and website the data relates to. In this example, there are just a few metric columns as you can see in the image below (shown in the green worksheet behind the wizard).
Use the Table > Arrange function to Insert a column (named ‘Website’) with the ‘[Website]’ value, positioned before the first column. Repeat as necessary to add other columns, like the account, if desired.
Step 4. Append to the Results Worksheet
Add an Append to Worksheet function to add the data to the Results worksheet — each cycle of the Repeat Macro will process a different website and append to the results sheet.
Step 5. Run the Completed Macro
When you finish the last step, only the first row will be processed — when editing your repeat macro, only the first row gets processed to keep things simple.
To process all the rows, refresh/run the macro again — all rows will be processed in order, appending to the Results worksheet.
Notes:
Repeat Macro function must be the first function in the macro
The Repeat Macro function will not work unless it is the first function added to your macro.
If you want to do some pre-processing, create another macro to do that — macros run in alphabetical order, so name them to run in order when Refresh All is clicked.
Macros Are Standalone
All Analytics Edge macros run independently. To pass information from one macro to another, the first macro needs to write it to a worksheet, then the next macro can read the worksheet to get the data.
In Development, Only the First Row is Processed
When creating the Repeat Macro, the first row of the source worksheet is loaded and processed for you, but only that one row. When you run the macro, it will loop through all the values, one after the other. This raises 2 important points:
- when you run the macro, each loop will run independently with different source data values. If you are writing the results to a specific location, each loop will overwrite the previous results, so make sure to use Append to Worksheet function or otherwise combine the results of the previous loops.
- make sure your first data row is a good one. During development, only the first row is processed, so make sure what you are processing will result in data (an account with lots of good sessions, for example).
Writing Separate Result Sets
At the end of the Repeat Macro, you will need to write out your results in some way. Since this is a macro, there are many possibilities, but the most common ones are to append to a common worksheet, or to write to separate worksheets.
To write to separate locations, you could, for example, use Write to Worksheet to write to a worksheet with the name of the website — use ‘[Website]’ in place of the worksheet name [this is not a great example since website names tend to be overly long and contain special charaters].
Another alternative is to list cell references in the source worksheet, and use a range name in the top left cell value. e.g. ‘[Cell]’ to write the results to a column/row determined by the source data. Analytics Edge lets you do almost anything.
Dealing With Bad Data
Your macro could experience an error during processing, such as a specific website not having any data, the Repeat Macro may stop processing with an error. You can prevent this ‘no data’ type of error from happening by including a Filter function immediately after your query. The Filter function has an option to Stop a macro if there is no data [it is not necessary to have any filter expressions].
This option will stop the current loop of a Repeat Macro but will not cause an error that prevents it from continuing with the next loop. In other words, it lets you ignore empty queries — if there is no data, there will be nothing to write out at the end.
Summary
A Repeat Macro allows you to process multiple rows of a source worksheet, such as accounts, websites, filter expressions or data ranges.
The Repeat Macro function must be the first function in a macro.
Macros run independently, in alphabetical order. Pass data through worksheets.
If you want to combine results in a common worksheet at the end, remember to clear that worksheet before running (select the option), and use the Append to Worksheet function.
If you need to identify which loop a particular result set came from, you can use the Arrange > Insert function to add a column with a value from the source worksheet (use range name of a column).
If a lack of data or bad data may cause the macro to stop, you can use the Filter > Stop macro if empty function to stop processing on an individual loop before it gets too far.
The Repeat Macro can be used in many possible situations. If you aren’t sure how it could help you with your reporting, just ask.