@RMastop opened this Issue on May 22nd 2015 Contributor

Hi,

We would like to use Live.getLastVisitsDetails with a set of parameters.
We run into the following problem:
The structure of the output of a CSV file is not consistent, because the number of columns changes with the amount of actions in a visit, or custom variables etc..

A lot of BI tools are not able to handle XML files as a source file.
What are the options to export data in CSV, in a fixed column count, where you would create a new line for every action, event etc? All data that is part of the nested structure (like visitorId, idVisit, idSite, etc…) should be repeated in the lines.

I think it would be a valuable feature for connecting Piwik to any other BI tool.

Thanks,
Richard

@mattab commented on May 24th 2015 Member

Hi @RMastop

thanks for the suggestion.

What BI tools are you looking to integrate with Piwik?

It would be possible to change the output of Live.getLastVisitsDetails or maybe create a new API Live.getLastActionsDetails or so, that will return one action per line.

Alternatively we are currently discussing to release on the Marketplace a new plugin that will let you export the log_* tables as CSV. This could maybe solve the issue of integrating Piwik data with BI tools (although I can't confirm yet as the plugin is not released).

@RMastop commented on June 3rd 2015 Contributor

Hi @mattab

Tableau would be one of them.

Thing is, some data is encrypted / stored in a different encoding. One needs to decrypt / translate those values first. Second, data from log_* tables is enriched in the piwik code base before providing it via API.

In some cases the data from the export log* tables plugin would an option for people not wanting to connect directly to the database.
In some cases one needs more info than just the data available in the log
* tables.

@avargasd commented on April 16th 2018

Good morning!

How it would be possible to access to database from Piwik in order to integrate with Google Data Studio?

Thanks in advance,

Adriana

@mattab commented on April 25th 2019 Member

Reported recently from a customer, who suggested a new API similar to Live.getLastVisitsDetails but having one row for each action. There will be a lot of repetition in this file but it will be nice and easy to consume format.

URL: /index.php?date=2019-04-08&expanded=1&filter_limit=-1&format=TSV&format_metrics=1&idSite=2&language=en&method=Live.getLastVisitsDetails&module=API&period=week&token_auth=xxxx&translateColumnNames=1

As you can see, the exported file extends to column CRB (2,498 columns) - whereas to be usable within Excel, really this file should only extend to column AJ, with subsequent columns on a new row.

Whilst this would require the repetition of some values, these would only be the first 4 columns of visit specific information - as the export doesn't appear to include visit specific dimensions or custom variables (which I find somewhat surprising - probably they should be included as well…)

In its current format, the file cannot be filtered within Excel by any of the page specific values, as doing so would require 77 separate references.

Powered by GitHub Issue Mirror