Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Provide export of all Actions in Piwik (one row for each action in the CSV) to use it as import data for BI tools #7970

Open
RMastop opened this issue May 22, 2015 · 4 comments
Open

Provide export of all Actions in Piwik (one row for each action in the CSV) to use it as import data for BI tools #7970

RMastop opened this issue May 22, 2015 · 4 comments
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone
Backlog (Help wan...

Comments

@RMastop
Copy link
Contributor

RMastop commented May 22, 2015

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
Copy link
Member

mattab commented May 24, 2015

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).

@mattab mattab added the Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. label May 24, 2015
@RMastop
Copy link
Contributor Author

RMastop commented Jun 3, 2015

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.

@mattab mattab added this to the Mid term milestone Jul 15, 2015
@mattab mattab modified the milestones: Long term, Mid term Dec 5, 2016
@mattab mattab changed the title Improve the way Piwik exports CSV data, to use it as import data for BI tools Provide export of all Actions in Piwik (one row for each action in the CSV) to use it as import data for BI tools Jun 19, 2017
@avargasd
Copy link

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
Copy link
Member

mattab commented Apr 25, 2019 •
edited

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.

Notes:

This was referenced Jul 31, 2022
Open
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Milestone
Backlog (Help wanted)
Development

No branches or pull requests
3 participants
@mattab @RMastop @avargasd