4 Replies Latest reply: Oct 3, 2016 5:52 AM by Stefan Koppejan RSS

    Google analytics - authorization and pagination

    Ruben Marin

      Hi, I've been struggling some time retrieving data from google analytics using the REST connector, the posts from and in the REST CONNECTOR  locate the Authorization token thread was a great help but using pagination with the 'WITH CONNECTION' clause returns this error (this is documented in the REST connector help):

      "You cannot use the selected pagination type and the 'WITH CONNECTION' statement at the same time. Please set 'Pagination type' to None on the connection dialog."

       

      I've avoided it in QlikView inserting the authorization code inside the connection string, so I don't require the WITH CONNECTION clause:

      CUSTOM CONNECT TO "Provider=QvRestConnector.exe; 
              url=$(vURL); 
              timeout=30;method=GET; 
              autoDetectResponseType=true; 
              keyGenerationStrategy=-1; 
              queryHeaders=Authorization=Bearer $(vAccessToken);
              useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser; 
              certificateStoreName=My; 
              PaginationType=NextToken;NextTokenFieldName=nextLink;IsNextTokenFieldNameHeader=1;NextTokenFieldPath=root/nextLink;IsNextTokenFieldPathHeader=;
              XUserId=ReGGaHC;XPassword=MUKTRHB;";
      
      

       

      But I can't apply the same solution in Sense as the connection string is fixed when you create the connection and the use of WITH CONNECTION is required to dynamically set the access token.

       

      So far I know scheduled reloads will require to dynamically assign the refreshed access token, meanwhile retrieving large sets of data requires pagination. Is there any way that allows scheduled reloads and pagination with Google Analytics in Sense? or I'm forced to use legacy mode?

        • Re: Google analytics - authorization and pagination
          Karl Fredberg

          As Björn wrote in Re: REST CONNECTOR locate the Authorization token

          When working with APIs you cannot hardcode parameters in the URL when setting up the connection, basically because not all parameters may be known at this stage.

           

          To get around this I simply created two connections using the REST Connector, one for GET (LIB: Google Analytics API) and one for POST (LIB: Google Authorization) and used an open JSON server at jsonplaceholder.typicode.com wheen creating the connections (you cannot alter the Method GET/POST using WITH CONNECTION). I then followed the same process as above, and used WITH CONNECTION to alter the LIB settings and pointed the URL to the entry-points for Google APIs. Here's a small example script for Sense:

          This is how I implemented pagination.

          I request 1 date at a time and use a combination of max-results and start-index to paginate the results.

           

          SET vMetrics = 'ga:transactions';
          SET vDimensions = 'ga:date,ga:transactionId,ga:sourceMedium,ga:campaign,ga:deviceCategory,ga:landingPagePath,ga:searchUsed';
          
          SET vNumRequest = 0;
          
          CALL idList
          
          // This loops through the lists of ids
          FOR iterID = 0 to noofrows('idList')-1
          
              Let vId                = peek('ga_id',iterID,'idList');
                Let vStartDate        = peek('start_date',iterID,'idList');
                Let vEndDate        = peek('end_date',iterID,'idList');
          
              SET vFK_index = 0;
          
              Let vDays = NUM(vEndDate) - NUM(vStartDate);
          
               FOR d = 0 to vDays
          
                     Let vDate = Date(NUM(vStartDate + d),'YYYY-MM-DD');
                  TRACE date: $(vDate) id: $(vId);
                  CALL GetTotalAndHeaders (vDate)
                  CALL GetResults (vDate)      
          
               NEXT d
               
                CALL ResultsToQVD   
          
          NEXT iterID
          
          DROP TABLE idList;
          
          TRACE Total requests $(vNumRequest);
          
          
          
          

           

          SUB GetRefreshToken
          
            IF vTokenExpires <= now() THEN
          
              TRACE Refreshing TOKEN;
              SET vClient_id = 'Client_id';
              SET vClient_secret = 'Client_secret';
              SET vRefresh_token = 'Refresh_token';
          
              LET vRequestBody ='';
              LET vRequestBody = vRequestBody & 'grant_type=refresh_token';
              LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';
              LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';
              LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';
          
              LIB CONNECT TO 'Google Authorization';
          
              access_token:
              SQL SELECT
              "access_token",
              "token_type",
              "expires_in"
              FROM JSON (wrap on) "root"
                WITH CONNECTION (
                URL "https://www.googleapis.com/oauth2/v3/token",  
                HTTPHEADER "content-type" "application/x-www-form-urlencoded",
              BODY "$(vRequestBody)"
              );
              LET vExpiresIn = peek('expires_in',0,'access_token');
              LET vAccessToken = peek('access_token',0,'access_token');
              LET vTokenExpires = timestamp(now() + ($(vExpiresIn))/86400);
          
              TRACE $(vAccessToken);
              LET vNumRequest = $(vNumRequest) + 1; 
          
             EndIf
          
          ENDSUB
          
          
          
          

           

          SUB idList
          // Create a list of ids and date to search for
              idList:
          
               LOAD
                   '9999999999'         as ga_id,
                  '2013-01-01'        as start_date,
                   '2013-12-31'        as end_date
               AutoGenerate(1);
          
                LOAD
                  '7777777777'        as ga_id,
                  '2013-01-01'        as start_date,
                  '2013-12-31'        as end_date
               AutoGenerate(1);
          
          ENDSUB
          
          
          
          

           

          SUB GetTotalAndHeaders (iterDate)
          
            //LET vDate = Date($(iterDate));
          
            LET vRequestBody ='';
            LET vRequestBody = vRequestBody & 'ids=ga:' & $(vId);
            LET vRequestBody = vRequestBody & '&start-date=' & '$(iterDate)';
            LET vRequestBody = vRequestBody & '&end-date=' & '$(iterDate)';
            LET vRequestBody = vRequestBody & '&metrics=' & '$(vMetrics)';
            LET vRequestBody = vRequestBody & '&dimensions=' & '$(vDimensions)';
            LET vRequestBody = vRequestBody & '&start-index=1';
            LET vRequestBody = vRequestBody & '&max-results=1';
            LET vRequestBody = vRequestBody & '&sort=ga:transactionId,-ga:date';
          
            LET vURL  = 'https://www.googleapis.com/analytics/v3/data/ga?' & '$(vRequestBody)';
          
            CALL GetRefreshToken;     
          
            LIB CONNECT TO 'Google Analytics API';
          
            RestConnectorTotalTable:
            SQL SELECT
                "totalResults",
                "__KEY_root",
                (SELECT
                    "start-date",
                    "end-date",
                    "ids",
                    "dimensions",
                    "start-index",
                    "max-results",
                    "__KEY_query",
                    "__FK_query",
                    (SELECT
                        "@Value",
                        "__FK_metrics"
                    FROM "metrics" FK "__FK_metrics" ArrayValueAlias "@Value")
                FROM "query" PK "__KEY_query" FK "__FK_query"),
                (SELECT
                    "profileId",
                    "accountId",
                    "webPropertyId",
                    "internalWebPropertyId",
                    "profileName",
                    "tableId",
                    "__FK_profileInfo"
                FROM "profileInfo" FK "__FK_profileInfo"),
                (SELECT
                    "name",
                    "columnType",
                    "dataType",
                    "__FK_columnHeaders"
                FROM "columnHeaders" FK "__FK_columnHeaders"),
                (SELECT
                    "ga:sessions",
                    "__FK_totalsForAllResults"
                FROM "totalsForAllResults" FK "__FK_totalsForAllResults"),
                (SELECT
                    "__KEY_rows",
                    "__FK_rows",
                    (SELECT
                        "@Value" AS "@Value_u0",
                        "__FK_rows_u0"
                    FROM "rows" FK "__FK_rows_u0" ArrayValueAlias "@Value_u0")
                FROM "rows" PK "__KEY_rows" FK "__FK_rows")
            FROM JSON (wrap on) "root" PK "__KEY_root"
                WITH CONNECTION (
                URL "$(vURL)",
                HTTPHEADER "Authorization" "Bearer $(vAccessToken)"
              );
          
            LET vNumRequest = $(vNumRequest) + 1;
          
            root:
            LOAD
                [totalResults] AS [totalResults]
            RESIDENT RestConnectorTotalTable
            WHERE NOT IsNull([__KEY_root]);
          
            LET vTotalResults = peek('totalResults',0,'root');
            TRACE $(vTotalResults);
          
            [columnHeaders]:
            LOAD    Mid([name],4) AS [name],
                     RowNo() AS RowNumber
            RESIDENT RestConnectorTotalTable
            WHERE NOT IsNull([__FK_columnHeaders]);
          
          
            DROP TABLE RestConnectorTotalTable;
            DROP TABLE root;
          
          END SUB
          
          
          
          

           

          SUB GetResults (iterDate)  
            SET vStartIndex = 1;  
            SET vMaxResults = 10000;  
            
            LET vRequestBody ='';  
            LET vRequestBody = vRequestBody & 'ids=ga:' & $(vId);  
            LET vRequestBody = vRequestBody & '&start-date=' & '$(iterDate)';  
            LET vRequestBody = vRequestBody & '&end-date=' & '$(iterDate)';  
            LET vRequestBody = vRequestBody & '&metrics=' & '$(vMetrics)';  
            LET vRequestBody = vRequestBody & '&dimensions=' & '$(vDimensions)';  
            LET vRequestBody = vRequestBody & '&start-index=' & '$(vStartIndex)';  
            LET vRequestBody = vRequestBody & '&max-results=' & '$(vMaxResults)';  
            LET vRequestBody = vRequestBody & '&sort=ga:transactionId,-ga:date';  
            
            for vStartIndex = 1 to $(vTotalResults)  
            
            TRACE "StartIndex: " $(vStartIndex);  
              
            LET vURL  = 'https://www.googleapis.com/analytics/v3/data/ga?' & '$(vRequestBody)';  
            
            CALL GetRefreshToken  
              
            LIB CONNECT TO 'Google Analytics API';  
            
            TRACE FKindex $(vFK_index);  
            
            RestConnectorResultsTable:  
            LOAD [@Value_u0] AS Value,  
                   [__FK_rows_u0] + $(vFK_index) AS [__FK_rows_u0];  
            SQL SELECT  
                "kind",  
                "id",  
                "itemsPerPage",  
                "selfLink",  
                "containsSampledData",  
                "__KEY_root",  
                (SELECT  
                    "start-date",  
                    "end-date",  
                    "ids",  
                    "dimensions",  
                    "start-index",  
                    "max-results",  
                    "__KEY_query",  
                    "__FK_query",  
                    (SELECT  
                        "@Value",  
                        "__FK_metrics"  
                    FROM "metrics" FK "__FK_metrics" ArrayValueAlias "@Value")  
                FROM "query" PK "__KEY_query" FK "__FK_query"),  
                (SELECT  
                    "profileId",  
                    "accountId",  
                    "webPropertyId",  
                    "internalWebPropertyId",  
                    "profileName",  
                    "tableId",  
                    "__FK_profileInfo"  
                FROM "profileInfo" FK "__FK_profileInfo"),  
                (SELECT  
                    "name",  
                    "columnType",  
                    "dataType",  
                    "__FK_columnHeaders"  
                FROM "columnHeaders" FK "__FK_columnHeaders"),  
                (SELECT  
                    "ga:sessions",  
                    "__FK_totalsForAllResults"  
                FROM "totalsForAllResults" FK "__FK_totalsForAllResults"),  
                (SELECT  
                    "__KEY_rows",  
                    "__FK_rows" ,  
                    (SELECT  
                        "@Value" AS "@Value_u0",  
                        "__FK_rows_u0" AS  "__FK_rows_u0"  
                    FROM "rows" FK "__FK_rows_u0" ArrayValueAlias "@Value_u0")  
                FROM "rows" PK "__KEY_rows" FK "__FK_rows")  
            FROM JSON (wrap on) "root" PK "__KEY_root"  
                WITH CONNECTION (  
                URL "$(vURL)",  
                HTTPHEADER "Authorization" "Bearer $(vAccessToken)"  
              );  
            
            LET vStartIndex = $(vStartIndex) + $(vMaxResults) - 1;  
            LET vNumRequest = $(vNumRequest) + 1;  
            
            NEXT vStartIndex;  
            
            LET vFK_index = vFK_index + vTotalResults;  
            
          ENDSUB 
          

           

           

          SUB ResultsToQVD (id)
          
            TRACE ResultsToQVD;
          
            [rows]:
            LOAD  [Value],
                 [__FK_rows_u0],
              if([__FK_rows_u0]=peek([__FK_rows_u0]),peek(RowNumber)+1,1) as RowNumber
            RESIDENT RestConnectorResultsTable
            WHERE NOT IsNull([__FK_rows_u0]);
          
            LEFT JOIN (rows)
            LOAD
                name,
                RowNumber 
            RESIDENT columnHeaders
            ;
          
            DROP TABLE RestConnectorResultsTable;
            DROP TABLE columnHeaders;
          
            Flags:
            Generic
            LOAD
                [__FK_rows_u0],
                name,
                IF(name='date',Date(Date#(Value,'YYYYMMDD')),Value) AS Value
            RESIDENT rows;
          
            MergeTable:
            LOAD distinct [__FK_rows_u0]   
            RESIDENT rows;
          
            DROP FIELD RowNumber;
          
            DROP TABLE rows;
          
            FOR i = NoOfTables()-1 to 0 STEP -1
              LET vTable=TableName($(i));
              IF WildMatch('$(vTable)', 'Flags.*') THEN
                LEFT JOIN (MergeTable) LOAD * RESIDENT   [$(vTable)];
                DROP TABLE  [$(vTable)];
              ENDIF
            NEXT i
          
            DROP FIELD [__FK_rows_u0];
          
            TRACE SAVING TO FILE GA_DATA_REST_$(vId)_$(vStartDate)_$(vEndDate).qvd;
          
            ResultTable:
            LOAD
                *,
                $(vId) AS id
            RESIDENT MergeTable
            ORDER BY date ASC;
          
            DROP TABLE MergeTable;
          
            store ResultTable into [lib://QVDs/GA REST/HISTORY/GA_DATA_REST_$(vId)_$(vStartDate)_$(vEndDate).qvd];
            DROP TABLE ResultTable;
          
          ENDSUB
          
          
          
          
          • Re: Google analytics - authorization and pagination
            Stefan Koppejan

            Hello Karl,

             

            Thanks! A very nice solution.

             

            However, I don't get it to work in QlikView (I do get it to work in Sense). I get an error, Custom read failed on RestConnectorTotalTable.

             

            The only thing that I thought I needed to edit was the connection string so I guess the problem is in that area.

             

            The GetRefreshtoken and idList routines are working properly. In the routines GetTotalAndHeaders and GetResults I replaced:

             

            LIB CONNECT TO 'Google Analytics API';

             

            with

             

            CUSTOM CONNECT TO "Provider=QvRestConnector.exe;

              url=$(vURL);

              timeout=30;

              method=GET;

              autoDetectResponseType=true;

              keyGenerationStrategy=0;

              useWindowsAuthentication=false;

              useCertificate=No;

              certificateStoreLocation=CurrentUser;

              certificateStoreName=My;

              XUserId=JbHIHeD;

              XPassword=caEbPWD;";

             

            Anybody sees something that doesn't add up? I'm trying for hours now but I don't get it to work. :-S

             

            I hope someone can point me in the right direction, thanks in advance!

             

            Regards,

            Stefan