SeETL-048-Extract Data From Get Response

0
391

Introduction

 

Hello and welcome to our latest blog post!

 

Thank you for dropping by!

 

We really appreciate you!

 

This is one for the "techies" in my audience.

 

If you have to get data from REST APIs from web sites that deliver services via web sites then this post is for you.

 

If you are a manager of such people? You might like to pass this post along.

 

Ok?

 

So…..As you know I really like Get Response.

 

Before I used Get Response I wrote an emailer in SeETL and I was sending out emails with SeETL.

 

I had to remove people from my list as they asked, I had to remove the bounces, I had to maintain my list by hand.

 

Also, I could not see who opened my emails or clicked my links.

 

The SeETL emailer is now available for free if you want to use it.

 

Basically it allows you to send html emails and it allows you up to 10 variables to be substituted in to the email.

 

Not bad for free I would argue!

 

But Get Response, of course, does a WHOLE LOT MORE!!

 

There are tens of thousand of companies that use it.

 

Hundreds of thousands of small businesses and individuals use it too.

 

I really like it and you can get your own 30 day trial on the link below.

 

http://www.peternolan.com/likes/GetResponseVideo

 

One of my "issues" was that I have 3,700 contacts in my GetResponse account where I have their linkedin URL in my personal CRM database.

 

So I wanted to put the 3,700 linkedin URLs into my GetResponse account so I could copy the link and paste it into a web browser to see if my contact had moved before I sent them an email.

 

But we all know how much we "put off" working something like that out.

 

There is also the issue that, as good as the reporting is in Get Response, it is operational style reporting and it does not let you do segmentation of customers. It does not allow you to integrate your Get Response data with your other customer data.

 

So I had these two reasons to want to learn how to both fetch data from Get Response and send updates in to Get Response.

 

Now, having been successful, I will likely integrate Get Response data into the BI4ALL Data Models at some point in time.

 

You are welcome to read through this blog and learn how to get Get Response data into your data warehouse as well!

 

And, of course, if you want me to help you with that I would be pleased to do so.

 

So, let us begin!

 

Firstly, I had thought I might use C++ to create this REST API reader.

 

But on searching I found this page for an SQL Server GIT project that exposes REST APIs from inside Stored Procedures. Since I wanted to do this on SQL Server I thought I would give this project a try.

 

https://github.com/geral2/SQL-APIConsumer

 

 

The project contains all the source code that you can download.

 

Please note. At the time of writing you need to download Newtonsoft Version 11.0.1 and put that in your dot net framework directory because this product would need to be recompiled if you use the later versions.

 

So where you see the instructions:

 

STEP 4

Create Assembly Newtonsoft.Json. If It doesn't exists you need to download and copy it in this path. Keep in mind that the compiled CLR of this project uses the version 11.0 of Newtonsoft. If you want to update it you would need to recompiled the code.

 

CREATE ASSEMBLY [Newtonsoft.Json]

AUTHORIZATION dbo

FROM  N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Newtonsoft.Json.dll'

WITH PERMISSION_SET = UNSAFE

 

You need to click on this button and download NewtonSoft V11.0.1 and install that in to your dot net framework directory. That will save you an hour or two!

 

https://github.com/JamesNK/Newtonsoft.Json/releases/tag/11.0.1

 

Once it was all installed and working I ran some simple tests like this one.

 

DECLARE @httpMethod nvarchar(max) = 'GET'

DECLARE @URL nvarchar(max) = 'https://www.routingnumbers.info/api/name.json?rn=122242597'

DECLARE @Headers nvarchar(max) = '[{"Name": "Content-Type", "Value" :"text/javascript; charset=utf-8" }]';

DECLARE @JsonBody nvarchar(max) =  ''

 

Declare @ts as table

(

       Json_Result nvarchar(max),

       ContentType varchar(100),

       ServerName varchar(100),

       Statuscode varchar(100),

       Descripcion varchar(100),

       Json_Headers nvarchar(max)

)

 

DECLARE @i AS INT

 

INSERT INTO @ts EXECUTE @i =  [dbo].[APICaller_Web_Extended]  @httpMethod ,@URL ,@Headers ,@JsonBody

 

SELECT * FROM @ts

 

SELECT

               [name]      

              ,[rn]        

              ,[message]   

              ,[code]      

 FROM (

                     SELECT Context = Json_Result

                       from @ts

              )tb

       OUTER APPLY OPENJSON  (context) 

  WITH

    (        [name]        VARCHAR(20)   '$.name'

       ,      [rn]          VARCHAR(20)   '$.rn'

       ,      [message]     VARCHAR(20)   '$.message'

       ,      [code]        INT           '$.code'

    );

 

SELECT  *  FROM OPENJSON((select Json_Headers from @ts)) 

                     WITH (  

                                   Header       NVARCHAR(MAX) '$."Name"'     

                                  ,Value        NVARCHAR(MAX) '$."Value"'     

                                  ) a

 

I then went on to go through the Get Response APIs to learn about them and learn how to use them.

 

Three important links you should click are these three.

 

They take you to the APIs manual itself, and also to the customer segmentation APIs manual.

 

These second two are well worth reading through.

 

https://apireference.getresponse.com/?_ga=2.165011446.899509516.1616285960-1979031222.1554191216

 

https://apidocs.getresponse.com/v3/case-study/search-contacts-guide

 

https://apidocs.getresponse.com/v3/case-study/segments-manual

 

So let's start with contacts.

 

The final code to download contacts will be below. I will highlight the snippets that you should read here and explain what they are doing. Obviously all confidental material is removed.

 

Set the post method and the base URL.

 

DECLARE @httpMethod nvarchar(max) = 'GET' ;

DECLARE @URLBASE nvarchar(max) = 'https://api.getresponse.com/v3/contacts' ;

DECLARE @URL nvarchar(max) ;

DECLARE @JsonBody nvarchar(max) =  '' ;

 

Set the headers desired. In this case we have only set the API Key name and value and the useQueryString header to true.

 

DECLARE @Headers nvarchar(max) = '[{ "Name": "X-Auth-Token", "Value": "api-key ### Your API Key###" },{ "Name": "useQueryString","Value" :"true"}]';

 

Obviously for larger data sets they will be returned in chunks. In GetResponse a chunk can be 1 to 1000 rows with a default of 100.

 

So in testing I created variables that allowed me to test setting the wsRowsPerPage integer and see the variations.

 

I currently have 6,600+ contacts in my GetResponse account and so I was playing around with wsRowsPerPage to see how that varied and how that worked.

 

Please not the code has not been designed properly to test for errors and all those things. This is just prototype code.

 

In the real code all the variables will be stored in the database and proper testing and data types will be used.

 

DECLARE @wsTotalCount integer ;

DECLARE @wsTotalPages integer ;

DECLARE @wsCurrentPage integer ;

Declare @wsLoopCounter integer ;

Declare @wsRowsPerPage integer  ;

set @wsCurrentPage = 1 ;

set @wsRowsPerPage = 200 ;

 

Now add the query string for the current page to fetch and the rows per page to fetch.

 

set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

Create a table for the results of the API call to be returned to. Also declare a variable for the return code from the call.

 

Declare @ts as table

(

       Json_Result nvarchar(max),

       ContentType varchar(100),

       ServerName varchar(100),

       Statuscode varchar(100),

       Description varchar(100),

       Json_Headers nvarchar(max)

)

DECLARE @i AS INT ;

 

If the temporary table to hold the test call results exists then drop it.

 

if object_id('tempdb.dbo.##z01_test1', 'u') is not null  drop table ##z01_test1; 

 

Now perform the actual call to the REST API via the product installed.

 

INSERT INTO @ts EXECUTE @i =  [dbo].[APICaller_Web_Extended]  @httpMethod  ,@URL  ,@Headers  ,@JsonBody ;

 

Get the number of total pages from the response from the API. Note that we presume it worked.

 

select @wsTotalPages = a.Value

from

(

 SELECT

                 Header          

                 ,Value   

 

 FROM (

                SELECT Json_Headers

                from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Headers) 

  WITH

    (

         Header      nvarchar(max) '$.Name'     

       ,Value        nvarchar(max) '$.Value'          

    )

 

) a

where a.Header = 'TotalPages' ;

 

Now get the first page of data from the returned table and insert it into the sql server temporary table. This will create the temporary table.

 

I have used nvarchar(max) for testing.

 

SELECT

                      contactId          

                     ,name 

                     ,origin                   

                     ,timeZone           

                     ,activities         

                     ,changedOn          

                     ,campaignId         

                     ,campaignhref       

                     ,campaignname       

                     ,email              

                     ,dayOfCycle         

                     ,scoring            

                     ,engagementScore    

                     ,href               

                     ,note               

                     ,ipAddress          

                     ,latitude           

                     ,longitude          

                     ,continentCode            

                     ,countryCode        

                     ,region                   

                     ,postalCode         

                     ,dmaCode            

                     ,city               

                     ,customFieldId

                     ,customFieldName

                     ,customFieldValue

 

into ##z01_test1

 

 FROM (

                     SELECT Json_Result

                       from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Result) 

  WITH

    (

                      contactId                  nvarchar(max) '$.contactId'     

                     ,name                       nvarchar(max) '$.name'     

                     ,origin                     nvarchar(max) '$.origin'  

                     ,timeZone                   nvarchar(max) '$.timeZone'                            

                     ,activities                 nvarchar(max) '$.activities'                          

                     ,changedOn                  nvarchar(max) '$.changedOn'                           

                     ,campaignId                 nvarchar(max) '$.campaign.campaignId'     

                     ,campaignhref               nvarchar(max) '$.campaign.href'     

                     ,campaignname               nvarchar(max) '$.campaign.name'     

                     ,email                      nvarchar(max) '$.email'     

                     ,dayOfCycle                 nvarchar(max) '$.dayOfCycle' 

                     ,scoring                    nvarchar(max) '$.scoring' 

                     ,engagementScore            nvarchar(max) '$.engagementScore' 

                     ,href                       nvarchar(max) '$.href' 

                     ,note                       nvarchar(max) '$.note' 

                     ,ipAddress                  nvarchar(max) '$.ipAddress' 

                     ,latitude                   nvarchar(max) '$.geolocation.latitude'   

                     ,longitude                  nvarchar(max) '$.geolocation.longitude'   

                     ,continentCode              nvarchar(max) '$.geolocation.continentCode'   

                     ,countryCode                nvarchar(max) '$.geolocation.countryCode'   

                     ,region                     nvarchar(max) '$.geolocation.region'   

                     ,postalCode                 nvarchar(max) '$.geolocation.postalCode'   

                     ,dmaCode                    nvarchar(max) '$.geolocation.dmaCode'   

                     ,city                       nvarchar(max) '$.geolocation.city'  

                     ,campaignJSON               nvarchar(max) '$.campaign' as JSON    

                     ,geolocationJSON            nvarchar(max) '$.geolocation' as JSON    

                     ,customFieldValuesJSON     nvarchar(max) '$.customFieldValues' as JSON 

                     ,customFieldId             nvarchar(max) '$.customFieldValues[0].customFieldId'

                     ,customFieldName            nvarchar(max) '$.customFieldValues[0].name'

                     ,customFieldValue           nvarchar(max) '$.customFieldValues[0].values[0]'

    );

 

While there are more pages increment the wsCurrentPage and update the URL to be used with the wsCurrentPage.

 

WHILE (@wsCurrentPage < @wsTotalPages)

BEGIN

       set @wsCurrentPage = @wsCurrentPage + 1 ;

       set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

Delete the data already sitting in the @ts table. If you don't do this you will get duplicates.

 

       DELETE FROM @ts ;

       INSERT INTO @ts 

       EXECUTE @i =  [dbo].[APICaller_Web_Extended] @httpMethod ,@URL ,@Headers ,@JsonBody ;

 

Now that the temporary table ##z01_test1 will exist the sql statement is insert into.

 

Then the while loop is ended.

 

       insert into ##z01_test1

              SELECT

                      contactId          

                     ,name 

                     ,origin                   

                     ,timeZone           

                     ,activities         

                     ,changedOn          

                     ,campaignId         

                     ,campaignhref       

                     ,campaignname       

                     ,email              

                     ,dayOfCycle         

                     ,scoring            

                     ,engagementScore    

                     ,href               

                     ,note               

                     ,ipAddress          

                     ,latitude           

                     ,longitude          

                     ,continentCode            

                     ,countryCode        

                     ,region                   

                     ,postalCode         

                     ,dmaCode            

                     ,city               

                     ,customFieldId

                     ,customFieldName

                     ,customFieldValue

       FROM (

                     SELECT Json_Result

                       from @ts

              )tb

              OUTER APPLY OPENJSON  (Json_Result) 

       WITH

       (

                      contactId                  nvarchar(max) '$.contactId'     

                     ,name                       nvarchar(max) '$.name'     

                     ,origin                    nvarchar(max) '$.origin'  

                     ,timeZone                   nvarchar(max) '$.timeZone'                            

                     ,activities                 nvarchar(max) '$.activities'                          

                     ,changedOn                  nvarchar(max) '$.changedOn'                           

                     ,campaignId                 nvarchar(max) '$.campaign.campaignId'     

                     ,campaignhref               nvarchar(max) '$.campaign.href'     

                     ,campaignname               nvarchar(max) '$.campaign.name'     

                     ,email                      nvarchar(max) '$.email'     

                     ,dayOfCycle                 nvarchar(max) '$.dayOfCycle' 

                     ,scoring                    nvarchar(max) '$.scoring' 

                     ,engagementScore            nvarchar(max) '$.engagementScore' 

                     ,href                       nvarchar(max) '$.href' 

                     ,note                       nvarchar(max) '$.note' 

                     ,ipAddress                  nvarchar(max) '$.ipAddress' 

                     ,latitude                   nvarchar(max) '$.geolocation.latitude'   

                     ,longitude                  nvarchar(max) '$.geolocation.longitude'   

                     ,continentCode             nvarchar(max) '$.geolocation.continentCode'   

                     ,countryCode                nvarchar(max) '$.geolocation.countryCode'   

                     ,region                    nvarchar(max) '$.geolocation.region'   

                     ,postalCode                 nvarchar(max) '$.geolocation.postalCode'   

                     ,dmaCode                    nvarchar(max) '$.geolocation.dmaCode'   

                     ,city                       nvarchar(max) '$.geolocation.city'  

                     ,campaignJSON               nvarchar(max) '$.campaign' as JSON    

                     ,geolocationJSON            nvarchar(max) '$.geolocation' as JSON    

                     ,customFieldValuesJSON     nvarchar(max) '$.customFieldValues' as JSON 

                     ,customFieldId             nvarchar(max) '$.customFieldValues[0].customFieldId'

                     ,customFieldName            nvarchar(max) '$.customFieldValues[0].name'

                     ,customFieldValue           nvarchar(max) '$.customFieldValues[0].values[0]'

       );

END

 

Now lets look at extracting contact activities. In Get Response you have a default for any transactions. It defaults to 2 weeks of transactions if you do not tell it otherwise. So say you want to get all the transactions for a contact when you are first downloading your data.

 

You can do that like this.

 

The URL Base contains the contact ID that has been retrieved from contacts.

 

DECLARE @URLBASE nvarchar(max) = 'https://api.getresponse.com/v3/contacts/mkjNxv/activities' ;

 

It took me a couple of hours to get the query string set correctly because I had not done this before. If you had not done this before this is exactly how you set the query string.

 

This is saying get all the contacts activities since 2010-01-01.

 

A timestamp can also be used so you could fetch contact activities from the prior extraction processing.

 

set @URL = concat (@URLBASE , '?&query[createdOn][from]=2010-01-01&page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

The full SP for downloading contact activities is also below.

 

Summary

 

So in this blog post I have explained to you how you can get a GitHub open source product to install on your SQL Server machine and to use that product to download your Get Response account. Information.

 

 

 

 

Footer

 

 

Procedure to update Contact URL

 

This procedure is so simple I did not think it needed explaining in the above blog post.

 

You will have to find out your custom field ID for the field.

 

In my case it is "0HSn" but it could be different in your case.

 

create procedure [dbo].[GR01_update_url_for_contact]

 @URLContact  nvarchar(max)

,@URLToSet    nvarchar(max)

 

AS

 

DECLARE @httpMethod nvarchar(max) = 'POST' ;

DECLARE @Headers nvarchar(max)    = '[{ "Name": "X-Auth-Token", "Value": "api-key ###Your API KEY###" }]';

DECLARE @JsonBody nvarchar(max)   ;

 

set @JsonBody = '{"customFieldValues": [{"customFieldId": "0HSn","name": "url","value":["http:\/\/' + @URLToSet + '"]}]}'

 

Declare @ts as table

(

       Json_Result nvarchar(max),

       ContentType varchar(100),

       ServerName varchar(100),

       Statuscode varchar(100),

       Descripcion varchar(100),

       Json_Headers nvarchar(max)

)

 

DECLARE @i AS INT

 

INSERT INTO @ts

 

EXECUTE @i =  [dbo].[APICaller_Web_Extended]

                        @httpMethod

                       ,@URLContact

                       ,@Headers

                       ,@JsonBody

 

return (@i)

 

GO

 

 

 

Procedure to Download Contact Data

 

 

create procedure [dbo].[GR01_dl_contacts] as

 

DECLARE @httpMethod nvarchar(max) = 'GET' ;

DECLARE @URLBASE nvarchar(max) = 'https://api.getresponse.com/v3/contacts' ;

DECLARE @URL nvarchar(max) ;

DECLARE @JsonBody nvarchar(max) =  '' ;

 

 

DECLARE @Headers nvarchar(max) = '[{ "Name": "X-Auth-Token", "Value": "api-key ###Your API Key###" },{ "Name": "useQueryString","Value" :"true"}]';

 

DECLARE @wsTotalCount integer ;

DECLARE @wsTotalPages integer ;

DECLARE @wsCurrentPage integer ;

Declare @wsLoopCounter integer ;

Declare @wsRowsPerPage integer  ;

set @wsCurrentPage = 1 ;

set @wsRowsPerPage = 200 ;

 

set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

Declare @ts as table

(

       Json_Result nvarchar(max),

       ContentType varchar(100),

       ServerName varchar(100),

       Statuscode varchar(100),

       Description varchar(100),

       Json_Headers nvarchar(max)

)

 

DECLARE @i AS INT ;

 

if object_id('tempdb.dbo.##z01_test1', 'u')                   is not null  drop table ##z01_test1;

 

INSERT INTO @ts EXECUTE @i =  [dbo].[APICaller_Web_Extended]  @httpMethod  ,@URL  ,@Headers  ,@JsonBody ;

 

select @wsTotalPages = a.Value

from

(

 SELECT

              Header       

              ,Value

 

 FROM (

              SELECT Json_Headers

              from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Headers) 

  WITH

    (

               Header              nvarchar(max) '$.Name'     

       ,Value        nvarchar(max) '$.Value'          

    )

 

) a

where a.Header = 'TotalPages' ;

 

 SELECT

                            contactId          

                           ,name 

                           ,origin                   

                           ,timeZone           

                           ,activities         

                           ,changedOn          

                           ,campaignId         

                           ,campaignhref       

                           ,campaignname       

                           ,email              

                           ,dayOfCycle         

                           ,scoring            

                           ,engagementScore    

                           ,href               

                           ,note               

                           ,ipAddress          

                           ,latitude           

                           ,longitude          

                           ,continentCode            

                           ,countryCode        

                           ,region                   

                           ,postalCode         

                           ,dmaCode            

                           ,city               

 --                        ,campaignJSON       

 --                        ,geolocationJSON

 --                        ,customFieldValuesJSON

                           ,customFieldId

                           ,customFieldName

                           ,customFieldValue

 

into ##z01_test1

 

 FROM (

                     SELECT Json_Result

                       from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Result) 

  WITH

    (

                            contactId                  nvarchar(max) '$.contactId'     

                           ,name                       nvarchar(max) '$.name'     

                           ,origin                    nvarchar(max) '$.origin'  

                           ,timeZone                   nvarchar(max) '$.timeZone'                            

                           ,activities                 nvarchar(max) '$.activities'                          

                           ,changedOn                  nvarchar(max) '$.changedOn'                           

                           ,campaignId                 nvarchar(max) '$.campaign.campaignId'     

                           ,campaignhref               nvarchar(max) '$.campaign.href'     

                           ,campaignname               nvarchar(max) '$.campaign.name'     

                           ,email                      nvarchar(max) '$.email'     

                           ,dayOfCycle                 nvarchar(max) '$.dayOfCycle' 

                           ,scoring                    nvarchar(max) '$.scoring' 

                           ,engagementScore            nvarchar(max) '$.engagementScore' 

                           ,href                       nvarchar(max) '$.href' 

                           ,note                       nvarchar(max) '$.note' 

                           ,ipAddress                  nvarchar(max) '$.ipAddress' 

                           ,latitude                   nvarchar(max) '$.geolocation.latitude'   

                           ,longitude                  nvarchar(max) '$.geolocation.longitude'   

                           ,continentCode             nvarchar(max) '$.geolocation.continentCode'   

                           ,countryCode                nvarchar(max) '$.geolocation.countryCode'   

                           ,region                    nvarchar(max) '$.geolocation.region'   

                           ,postalCode                 nvarchar(max) '$.geolocation.postalCode'   

                           ,dmaCode                    nvarchar(max) '$.geolocation.dmaCode'   

                           ,city                       nvarchar(max) '$.geolocation.city'  

                           ,campaignJSON               nvarchar(max) '$.campaign' as JSON    

                           ,geolocationJSON            nvarchar(max) '$.geolocation' as JSON    

                           ,customFieldValuesJSON     nvarchar(max) '$.customFieldValues' as JSON 

                           ,customFieldId             nvarchar(max) '$.customFieldValues[0].customFieldId'

                           ,customFieldName            nvarchar(max) '$.customFieldValues[0].name'

                           ,customFieldValue           nvarchar(max) '$.customFieldValues[0].values[0]'

      

    );

 

WHILE (@wsCurrentPage < @wsTotalPages)

BEGIN

       set @wsCurrentPage = @wsCurrentPage + 1 ;

set @URL = concat (@URLBASE , '?page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

       DELETE FROM @ts ;

 

       INSERT INTO @ts 

 

       EXECUTE @i =  [dbo].[APICaller_Web_Extended]

                            @httpMethod

                            ,@URL

                            ,@Headers

                            ,@JsonBody ;

 

       SELECT ContentType,ServerName,Statuscode,Description FROM @ts

 

       insert into ##z01_test1

              SELECT

                            contactId          

                           ,name 

                           ,origin                   

                           ,timeZone           

                           ,activities         

                           ,changedOn          

                           ,campaignId         

                           ,campaignhref       

                           ,campaignname       

                           ,email              

                           ,dayOfCycle         

                           ,scoring            

                           ,engagementScore    

                           ,href               

                           ,note               

                           ,ipAddress          

                           ,latitude           

                           ,longitude          

                           ,continentCode            

                           ,countryCode        

                           ,region                   

                           ,postalCode         

                           ,dmaCode            

                           ,city               

 --                        ,campaignJSON       

 --                        ,geolocationJSON

 --                        ,customFieldValuesJSON

                           ,customFieldId

                           ,customFieldName

                           ,customFieldValue

       FROM (

                     SELECT Json_Result

                       from @ts

              )tb

              OUTER APPLY OPENJSON  (Json_Result) 

       WITH

       (

                            contactId                  nvarchar(max) '$.contactId'     

                           ,name                       nvarchar(max) '$.name'     

                           ,origin                     nvarchar(max) '$.origin'  

                           ,timeZone                   nvarchar(max) '$.timeZone'                            

                           ,activities                 nvarchar(max) '$.activities'                          

                           ,changedOn                  nvarchar(max) '$.changedOn'                           

                           ,campaignId                 nvarchar(max) '$.campaign.campaignId'     

                           ,campaignhref               nvarchar(max) '$.campaign.href'     

                           ,campaignname               nvarchar(max) '$.campaign.name'     

                           ,email                      nvarchar(max) '$.email'     

                           ,dayOfCycle                 nvarchar(max) '$.dayOfCycle' 

                           ,scoring                    nvarchar(max) '$.scoring' 

                           ,engagementScore            nvarchar(max) '$.engagementScore' 

                           ,href                       nvarchar(max) '$.href' 

                           ,note                       nvarchar(max) '$.note' 

                           ,ipAddress                  nvarchar(max) '$.ipAddress' 

                           ,latitude                   nvarchar(max) '$.geolocation.latitude'   

                           ,longitude                  nvarchar(max) '$.geolocation.longitude'   

                           ,continentCode             nvarchar(max) '$.geolocation.continentCode'   

                           ,countryCode                nvarchar(max) '$.geolocation.countryCode'   

                           ,region                    nvarchar(max) '$.geolocation.region'   

                           ,postalCode                 nvarchar(max) '$.geolocation.postalCode'   

                           ,dmaCode                    nvarchar(max) '$.geolocation.dmaCode'   

                           ,city                       nvarchar(max) '$.geolocation.city'  

                           ,campaignJSON               nvarchar(max) '$.campaign' as JSON    

                           ,geolocationJSON            nvarchar(max) '$.geolocation' as JSON    

                           ,customFieldValuesJSON     nvarchar(max) '$.customFieldValues' as JSON 

                           ,customFieldId             nvarchar(max) '$.customFieldValues[0].customFieldId'

                           ,customFieldName            nvarchar(max) '$.customFieldValues[0].name'

                           ,customFieldValue           nvarchar(max) '$.customFieldValues[0].values[0]'

      

       );

END

GO

 

 

Procedure to Download Contact Activities

 

 

create procedure [dbo].[GR01_dl_contact_activities] as

 

DECLARE @httpMethod nvarchar(max) = 'GET' ;

DECLARE @URLBASE nvarchar(max)           = 'https://api.getresponse.com/v3/contacts/mkjNxv/activities' ;

DECLARE @URL nvarchar(max) ;

DECLARE @JsonBody nvarchar(max)          =  '' ;

DECLARE @Headers nvarchar(max)           = '[{ "Name": "X-Auth-Token", "Value": "api-key ###Your API Key###" },{ "Name": "useQueryString","Value" :"true"}]';

DECLARE @wsTotalCount integer ;

DECLARE @wsTotalPages integer ;

DECLARE @wsCurrentPage integer ;

Declare @wsLoopCounter integer ;

Declare @wsRowsPerPage integer  ;

set @wsCurrentPage = 1 ;

set @wsRowsPerPage = 200 ;

set @URL = concat (@URLBASE , '?&query[createdOn][from]=2010-01-01&page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

Declare @ts as table

(

       Json_Result nvarchar(max),

       ContentType varchar(100),

       ServerName varchar(100),

       Statuscode varchar(100),

       Description varchar(100),

       Json_Headers nvarchar(max)

)

 

DECLARE @i AS INT ;

 

if object_id('tempdb.dbo.##z01_test1', 'u')                   is not null  drop table ##z01_test1;

 

 

INSERT INTO @ts EXECUTE @i =  [dbo].[APICaller_Web_Extended]  @httpMethod  ,@URL  ,@Headers  ,@JsonBody ;

 

select @wsTotalPages = a.Value

from

(

 SELECT

              Header       

              ,Value

 

 FROM (

              SELECT Json_Headers

              from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Headers) 

  WITH

    (

               Header              nvarchar(max) '$.Name'     

       ,Value        nvarchar(max) '$.Value'          

    )

 

) a

where a.Header = 'TotalPages' ;

 

 

 SELECT

                            activity           

                           ,subject     

                           ,createdOn                

                           ,previewUrl         

                           ,resourceId         

                           ,resourceType       

                           ,resourcehref

                           ,clickTrackid

                           ,clickTrackname

                           ,clickTrackurl

                           ,resourceJSON

                           ,clickTrackJSON

 

into ##z01_test1

 

 FROM (

                     SELECT Json_Result

                       from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Result) 

  WITH

    (

                            activity            nvarchar(max) '$.activity'     

                            ,subject             nvarchar(max) '$.subject'     

                           ,createdOn           nvarchar(max) '$.createdOn'  

                           ,previewUrl          nvarchar(max) '$.previewUrl'                          

                           ,resourceId          nvarchar(max) '$.resource.resourceId'                               

                           ,resourceType        nvarchar(max) '$.resource.resourceType'                             

                           ,resourcehref        nvarchar(max) '$.resource.href'                              

                           ,clickTrackid        nvarchar(max) '$.clickTrack.id'     

                           ,clickTrackname      nvarchar(max) '$.clickTrack.name'     

                           ,clickTrackurl       nvarchar(max) '$.clickTrack.url'     

                           ,resourceJSON        nvarchar(max) '$.resource' as JSON    

                           ,clickTrackJSON      nvarchar(max) '$.clickTrack' as JSON    

      

    );

 

WHILE (@wsCurrentPage < @wsTotalPages)

BEGIN

       set @wsCurrentPage = @wsCurrentPage + 1 ;

 

       set @URL = concat (@URLBASE , '?&query[createdOn][from]=2010-01-01&page=',cast ( @wsCurrentPage as nvarchar (255)) , '&perPage=' ,cast ( @wsRowsPerPage  as nvarchar (255))) ;

 

       DELETE FROM @ts ;

 

       INSERT INTO @ts 

 

       EXECUTE @i =  [dbo].[APICaller_Web_Extended]

                            @httpMethod

                            ,@URL

                            ,@Headers

                            ,@JsonBody ;

 

       SELECT ContentType,ServerName,Statuscode,Description FROM @ts

 

       insert into ##z01_test1

 

 SELECT

                            activity           

                           ,subject     

                           ,createdOn                

                           ,previewUrl         

                           ,resourceId         

                           ,resourceType       

                           ,resourcehref

                           ,clickTrackid

                           ,clickTrackname

                           ,clickTrackurl

                           ,resourceJSON

                           ,clickTrackJSON

 

 FROM (

                     SELECT Json_Result

                       from @ts

              )tb

       OUTER APPLY OPENJSON  (Json_Result) 

  WITH

    (

                            activity            nvarchar(max) '$.activity'     

                            ,subject             nvarchar(max) '$.subject'     

                           ,createdOn           nvarchar(max) '$.createdOn'  

                           ,previewUrl          nvarchar(max) '$.previewUrl'                          

                           ,resourceId          nvarchar(max) '$.resource.resourceId'                               

                           ,resourceType        nvarchar(max) '$.resource.resourceType'                             

                           ,resourcehref        nvarchar(max) '$.resource.href'                              

                           ,clickTrackid        nvarchar(max) '$.clickTrack.id'     

                           ,clickTrackname      nvarchar(max) '$.clickTrack.name'     

                           ,clickTrackurl       nvarchar(max) '$.clickTrack.url'     

                           ,resourceJSON        nvarchar(max) '$.resource' as JSON    

                           ,clickTrackJSON      nvarchar(max) '$.clickTrack' as JSON    

      

    );

 

END

GO

 

 

With that?

 

I would like to say thank you very much for reading this blog post!

 

I hope you liked it.

 

If you need any help getting your Get Response data down from your account and you would like to pay me for my time?

 

I am more than happy to help out!

 

Best Regards

 

Peter.

Carphone Warehouse Reference Video:

Previous articleBI4ALL-012-The History Of BI Models
Next articleIBI-041-Celebrating-30-Years-In-BI
Peter Nolan is one of the worlds leading thought leaders in Business Intelligence. Across his 29+ years in BI Peter has consistently invented new and innovative ways of designing and building data warehouses. SeETL now stands alone as the worlds most cost effective data warehouse development tool.

LEAVE A REPLY

Please enter your comment!
Please enter your name here