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 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 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.
Make your Query
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.
