Using the Repeat Macro Function

repeatmacroThe Repeat Macro function in the Analytics Edge Core Add-in lets you cycle through a list of items. This makes it possible to report on:

  • multiple accounts
  • multiple web sites
  • multiple filter expressions
  • multiple date ranges

This article shows you how to use it.

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 macro will be repeated for each row in the table. Have 30 clients; create 30 rows. Want 5 date ranges; create 5 rows. The macro will be repeated for each row in the source data.

To get the most out of a Repeat Macro, there are a few other things to be aware of.

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 as a source of data, and click Finish.

Note that the Repeat Macro function must be the first function in your macro.

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

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.

For a Repeat Macro, the list in the source worksheet could be created by another macro — just make sure the other macro runs first (they run in alphabetical order when you click Refresh All).

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 functions or otherwise combine the results of the previous loops.
  • make sure your first data set 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).

The Source Columns Can Be Used as [Range Names]

When it reads the source worksheet, the Repeat Macro function puts the values from the first row into Analytics Edge named ranges, similar to Excel named ranges. You use them by entering the column name surrounded by [square brackets] into a wizard instead of entering a value.

For example, in the image above, the first row contains a column named ‘Country’. In the Repeat Macro, we can include a query that references the value by entering the column name surrounded by square brackets ‘[Country]’. In the image, the value is used in a filter expression.

When the query runs, the actual value in the column, ‘United States’ in the example above, would be substituted for the range range, so the filter would get the country exactly matching ‘United States’.

Remember that each loop of the macro will be using different values (rows) from the source worksheet, so each loop could filter by different countries. Note that you can change the list of countries in the source worksheet at any time without changing the Repeat Macro — it will use whatever values are provided.

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 write each loop’s results to separate locations, or to write to a common location.

To write to separate locations, you could, for example, use Write to Worksheet to write to a worksheet with the name of the country (use ‘[Country]’ in place of the worksheet name).

I have also seen people enter row numbers in their source worksheet data, and used a range name in the Top-Left Cell value. e.g.  C[Row]  to write the results to column C in a row determined by the source data.

Combining Results

You could also combine all of the result sets together using the AppendToWorksheet function — pick a target worksheet and append the results of each loop to the bottom of the worksheet.

Note that the Repeat Macro function has an option to clear this target worksheet before the first loop. That makes sure each run of the macro starts clean.

Identifying Results of Each Loop

If the results of your queries do not have anything to identify what makes them different, appending them all to a common worksheet can be confusing. You can use the Arrange – Insert  function to add a column with an identifier to your results. For example, a new column could be added to the example above with the [Country] value as an Initial Text Value.

For each loop of the Repeat Macro, a different [Country] value would be inserted, so appended results could easily be identified.

Dealing With Bad Data

If your macro can experience an error during processing, such as a specific website not having any data from the filtered country, 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.

The source worksheet could be created by another macro, just name them to run in alphabetical order.

If you want to combine results in a common worksheet at the end, remember to clear that worksheet before the first loop (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.

Many Other Possibilities

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.