Retrieving Clicks, Opens, Bounces, and Opt Outs from Saved Report


(Ben Lobach) #1

Trying to get the clicks, opens, bounces, and opts outs for an analytics dashboard.

I have created a saved report using the Email Broadcasts.
We are essentially trying to get the numbers that are on the Infusionsoft dashboard 30 day email stats. So far we have pulled the saved search, extracted the past thirty days, and added the total of emails sent. I just realized that there are no click, bounce, open, or opt-out columns in the broadcast report. They can be viewed by clicking view report but this information isn’t passed in the saved report array (it only passes a string that says “view report”).

Is there a report that I am missing that I can pull to tally the total of clicks, opt-outs, bounces, and opens of all email batches sent in the past month?


(Ben Lobach) #2

It would be nice if there was a way to retrieve the All Email Stats panel. Is that possible rather than doing a saved report?


(John Borelli) #3

Not long ago they added access to the EmailAddStatus table. It may not have everything you want but it gives last clicked, opened and sent dates. But cumulatively, no. The saved search (or keeping backend records) are the only way to gather that data.


(Ben Lobach) #4

I spent some time chatting with support and I found that I can click each email stat on the dashboard and it takes me to a report that specific stat. I am going to try to save each of those and then add their totals to get the numbers I want. Not sure if this will work as it seems most of the saved reports require a specified date or are entirely open so this will take some testing to see if its the simplest way to go.


(John Borelli) #5

The saved search report for Email Batch Results will only limit by date if you set it to. The results you get will be realtime results. I think that is the shortest distance between two points for you


(Ben Lobach) #6

Thanks! It does seem that will be the quickest way to go. Doing some testing and I will report back here with the results for others who may need something like this.


(Ben Lobach) #7

@John_Borelli, suggestion for which call to use? I am currently doing a loop with getSavedSearchResultsAllFields but it maxes out at 1000 results. As my php code doesn’t know how large the array is I can’t use foreach so is there a better infusionsoft call to put all of that data into an array rather than calling it by page or field?


(John Borelli) #8

There are no calls, regardless of the object that is getting used, that will allow pulling more than 1000 records at a time.


(Ben Lobach) #9

So if I want to get the total of all emails sent in a month and I am using getSavedSearchResultsAllFields on a saved report of Email Batch Results, I will have to run 1000 then offset and run another 1000 and do that several times? I could do a for loop and wait until it returns null after each 1000 so it will get all the fields. Is that too many requests? It will be in a cron job eventually and will only run once a day


(John Borelli) #10

So the benefit of pulling 1000 records at a time is that it only counts as one api call. If you had say, 100,000 emails sent over the past month and pulled that list then you would only require 100 api calls to do it. Really not bad especially if this is just a once a day process. But yes, that is how you would have to accomplish this.


(Ben Lobach) #11

So this is what I’m doing but it only returns the first 1000 results. I need a fresh set of eyes if someone can see what I’m doing wrong.

                    $n = 0;
		$a = 999;
		
		$searchResult = 0;
		do {
			for ($i = $n; $i < $a; $i++) {
				$searchResult = $infusionsoft->search()->getSavedSearchResultsAllFields(490, 4, $i);
				if (!empty($searchResult)) {
					array_push($allStatistics, $searchResult);
					$n++;
				}
				else {
					$a = $a + 1000;
					break;
				}
			}
		} while(!empty($searchResult));
		
		foreach ($allStatistics[0] as $innerArray) {
			if (!empty($innerArray['Opened'])) {$monthlyStatistics['opened']++;}
			if (!empty($innerArray['Clicked'])) {$monthlyStatistics['clicked']++;}
			if (!empty($innerArray['Bounced'])) {$monthlyStatistics['bounced']++;}
			if (!empty($innerArray['Opted'])) {$monthlyStatistics['optout']++;}
			$monthlyStatistics['total']++;
		}

I’m trying to run 999 results then upping the pageId 1000 and doing another 999. It currently returns 1000 then is done. I know there should be about 40,500 results though. Then I’m adding up the fields and putting that into a statistics array.


(Ben Lobach) #12

Final product. Lots of calls if you are trying to get this information. The $infusionsoft->search()->getSavedSearchResultsAllFields($savedSearchId, $userId, $i) returns 1000 results so you have to filter those into appropriate data and I’ve also unset the array index after it is used to not make it too heavy on the system.

                    $n = 0;
		$savedBatchSearch = 0;

			for ($i = 0; $i < 1000; $i++) {
				$savedBatchSearch = $infusionsoft->search()->getSavedSearchResultsAllFields($savedSearchId, $userId, $i);

				if (!empty($savedBatchSearch)) {
					array_push($allStatistics, $savedBatchSearch);

					foreach ($allStatistics[$n] as $innerArray) {
						if (!empty($innerArray['Opened'])) {$monthlyStatistics['opened']++;}
						if (!empty($innerArray['Clicked'])) {$monthlyStatistics['clicked']++;}
						if (!empty($innerArray['Bounced'])) {$monthlyStatistics['bounced']++;}
						if (!empty($innerArray['Opted'])) {$monthlyStatistics['optout']++;}
						$monthlyStatistics['total']++;
					}
					unset($allStatistics[$n]);
					$n++;
				}
				else { break; }
			}

(Jon Smith) #13

I have given this as product feedback to our development team. I want to make sure that I’m clear on what is being wanted. It seems like the statistics that are desired are the amounts not necessarily the records for these reports. Basically the exact same information as what is on the dashboard widget. Is this correct?


(Ben Lobach) #14

Yes, looking to get the analytics information from the dashboard. Having some table, or saved search, etc would be great for a start but direct access to that would be awesome!!
For now, I have to create a table of all the batch sends with their clicks, opens, etc and I will doing that by pulling all of the getSavedSearchResultsAllFields from Email Batch Results and piecing together the batch numbers. It’s only 30 days of data so we are going to setup a file or table to save the information to and retrieve the newest information each day.