A common question is how to identify repeated attempts of calls that abandoned. Identifying the abandons is easy. Identifying the repeated attempts is not straightforward. I developed a Designer Report to query the call_rec database for all CPN's that are the same as a CPN for an abandoned call for the current date. It shows call records for calls with the same CPN that occurred today before or after the abandoned call. It was tricky because the subquery's 'in' clause would also extract 'Calling_Pty' data for calls abandoned on previous dates (and maybe not today) if the the record was still in the call_rec table. And the system would not tolerate an input Date within the 'in' clause. I'm sharing what I have. Details are in the attached Excel (Abn_0_Days_Ago.xlsx) . IAUG won't allow uploading '.rep' files (the Designer Report). I renamed it as '.doc' and it uploaded. Change it back to .rep. and follow instructions in the Excel to import as a Designer Report. Or use the 'where' clause shown in the Excel to create your own version. The results are interesting. For example, you can see calls prior to opening time and maybe adjust your shift times. Please take a look and provide feedback and critique. For example, has anyone come up with logic to use repeated attempt analysis to identify and block spam? Or do you have a more graceful query? Do we have a place where we share the details of useful queries?.
------------------------------
John O'Brien
P.E.
Self Employed Consultant/Engineer
Staten Island NY
------------------------------