Google analytics - authorization and pagination

[rubenmarin]

Hi, I've been struggling some time retrieving data from google analytics using the REST connector, the posts from bmw and Fredberg 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?

[#2 · Accepted solution]

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

[rubenmarin]

Thanks Karl!

[ralph_graham]

Hello,

I'm attempting to implement this approach as the logic in the online Qlik guides weren't giving me reliable data. I'm a bit stuck, should the script be used in the order that you have stated above?

Thanks,

Ralph

[skoppe]

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