Downloading Constant Contact click-through data for multiple clients has never been easier than with Analytics Edge and the Constant Contact connector. This article describes the process in detail, so you can get the job done fast!
Create a new Analytics Edge macro
Start a new macro by clicking the Open Task Pane button,
and selecting –new macro- from the Analytics Edge Macros drop-down in the task pane.
Enter a macro name and click OK.
Select an account as the default
Open the Constant Contact – Accounts wizard,
and select the account you want to use.
Click the Save Default In Macro button.
Time-saver hint: Use this step at the beginning of each macro, and then make all the queries in the macro use the default account. The entire macro/workbook report can now be used as a template for other accounts; simply change the account selection in this first step, and the rest of the queries will track automatically.
Start a campaign query
Open the Constant Contact – Campaigns wizard,
make sure the *Use Default account is selected,
and click the Campaigns button.
Select query parameters
Select the get campaigns query.
Check the limit parameter and set the limit to 1,
and check the status parameter and select SENT in the drop-down.
This will return the most recently sent campaign and the corresponding campaign id. We need the campaign id to get the click-through data.
Verify the results
Analytics Edge will display the results of the query in a temporary worksheet. Note that the campaign id appears in the first column with the name of “id”.
Assign range names to the columns
We can grab that value right out of the query by using the Table Names wizard,
and Assign range names to each column of the current table.
This will make the “id” column available to other Analytics Edge wizards.
Start a campaign reports query
Now we make another campaign query, this time make sure the *Use Default account is selected,
and click the Campaign Reporting button.
Select the query parameters
Select the clicks query,
and enter “[id]” for the campaignId value. This means we want to use the value store in the range named “id” in the previous step.
Check the limit parameter and enter a number larger than the number of clicks you expect for the account/campaign selected. Note that the Constant Contact API limits each query to 500 rows, but Analytics Edge will automatically issue additional queries up to your entered value. Downloading 50,000 clicks will cause 100 queries to be run (500 x 100 = 50,000), and you are limited in the number of queries you can run per day (10,000 per API key).
Verify the results
Verify that the click data has been downloaded correctly; Analytics Edge displays some of the results of the query in a temporary worksheet for you to see.
Notice that the click_date column contains strangely formatted dates (2014-01-24T19:04:00.000Z). This will be imported as a text column because Excel will not recognize it as a date.
Fix the date column
Use the Column – Convert wizard to fix the date. Check the By Name option
and select the click_date column in the list.
Even if the columns come back from the query in a different order, we always want to convert the click_date column, so we select it by name instead of by position (the default).
On the Text -> Date tab,
enter the codes yyyy-MM-ddTHH:mm:ss.fffZ that match the date format being used by Constant Contact’s API. You can click the See Codes button for an explanation of these and other codes.
Select Convert GMT to local time to convert the date/time into your local timezone. The Constant Contact server uses GMT (Greenwich Mean Time) or UTC (Universal Coordinated TIme) times because it doesn’t know what timezone you are in.
Save the results to a worksheet
As the final step, open the File – Write Worksheet wizard,
enter a worksheet name
and check the box to Format as an Excel table.
This will write the entire list of click events to a worksheet, and gives it the nice alternating row colours that make long lists easier to read.