topic Re: Piwik API - lastX in Connectivity & Data Prep

Piwik API - lastX

rbartley — Tue, 22 Sep 2020 07:02:07 GMT

Hi everyone,

I have just begun working on an app to report on PiWik data using a REST connector. I can connect and return data for a single date, but when I try to return data by day for the last year, I receive an error:

Date format must be: YYYY-MM-DD, or 'today' or 'yesterday' or any keyword supported by the strtotime function (see http://php.net/strtotime for more information):

If I copy and paste the URL I am using into my browser, the result returns fine, but when I use the Select Data to load window, I receive the error.

The url is:

https://<servername>/?module=API&method=VisitsSummary.get&idSite=<site number>&date=last365&period=day&format=xml&token_auth=<token hash>

Any ideas?

Thanks in advance

Richard

Re: Piwik API - lastX

stevedark — Tue, 22 Sep 2020 09:20:07 GMT

Hi @rbartley

Looking at the PHP documentation I can't see that last365 is supported. You could try last year instead?

Seems strange it would work in a browser and not in the REST connector though.

Are you pasting the entire URL in the URL window, or are you specifying the parameters separately later on in the connector properties?

If you have spaces in the URL (for last year) you may need to put %20 in place of the space.

Have you tried other date periods in there? If so, what results did you get there.

Steve

Re: Piwik API - lastX

rbartley — Tue, 22 Sep 2020 11:33:40 GMT

Hi Steve,

I'm passing the entire URL, including the date and period parameters in the url text box of the REST connection. I have tried last year (and last%20year),but this gives me summary values for the whole of the last year rather than a break down by day

e.g. lastX gives me something like this.......

<results>
<result date="2020-09-13">
<nb_uniq_visitors>48</nb_uniq_visitors>
<nb_users>0</nb_users>
<nb_visits>61</nb_visits>
<nb_actions>273</nb_actions>
<nb_visits_converted>0</nb_visits_converted>
<bounce_count>22</bounce_count>
<sum_visit_length>18227</sum_visit_length>
<max_actions>26</max_actions>
<bounce_rate>36.07%</bounce_rate>
<nb_actions_per_visit>4.48</nb_actions_per_visit>
<avg_time_on_site>299</avg_time_on_site>
</result>
<result date="2020-09-14">
<nb_uniq_visitors>217</nb_uniq_visitors>
<nb_users>0</nb_users>
<nb_visits>249</nb_visits>
<nb_actions>760</nb_actions>
<nb_visits_converted>0</nb_visits_converted>
<bounce_count>133</bounce_count>
<sum_visit_length>53160</sum_visit_length>
<max_actions>35</max_actions>
<bounce_rate>53.41%</bounce_rate>
<nb_actions_per_visit>3.05</nb_actions_per_visit>
<avg_time_on_site>213</avg_time_on_site>
</result>
....

while last year gives me a single set

Re: Piwik API - lastX

stevedark — Tue, 22 Sep 2020 12:19:23 GMT

Hi @rbartley

My guess is that Qlik is not happy with the format of the data coming back when it has multiple results in the record set.

What I would suggest is that enumerate around for days 0 to 365, concatenating the results.

To do this you will need to not put the date parameter in the URL and inject it using WITH CONNECTION. This allows you to alter your API call in your load script.

There is help on this here:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Load-REST-data/Load-data.htm#WITH-CONNECTION-pagination

The code would be something like;

LIB CONNECT TO 'REST Library'; for iDay - 0 to 365 let sDay = date(today()-iDay, 'YYYY-MM-DD'); RESTData: SQL SELECT "Field1", "Field2" FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION ( QUERY "date" "$(sDay)" ); next

To avoid having too many REST connection libraries set up on my servers I tend to just have a 'Generic GET' and a 'Generic PUT' connector which points to a placeholder endpoint and then overwrite the whole URL, headers and parameters using WITH CONNECTION. This keeps things tidier and makes code easier to read.

This blog post describes this in more detail:
https://www.quickintelligence.co.uk/reading-rss-feeds-with-qlik-rest-connector/

Hope that helps.

Steve

Re: Piwik API - lastX

rbartley — Wed, 23 Sep 2020 07:50:47 GMT

Hi Steve,

Thanks for responding again. In fact, that's the approach I had taken in the meantime (sorry, I was going to update my post to include this information but you got there before). The WITH CONNECTION is a great tip and I already use this in another app using a REST connector to connect to the JIRA issue tracking system (I probably found it on your quickintelligence at the time) .

It's funny that, when I use lastX for the date parameter, directly through the browser, it appears to process the command/interpret the data in two stages (so that the screen seems to flicker before showing the result).

Regards,

Richard