Hi,
I’m looking for a way to export my custom variable values to a csv. I figure it will probably be easier to do it on the back end using a simple INTO OUTFILE dump but… I can’t find where the app puts them.
If someone could give me a gentle nudge in the right direction that would be most appreciated.
Kind regards
Leslie
What exactly do you mean by exporting - values or just names, page or visit scope, last day, week, month, all? If you describe exactly what you want to achieve there are various ways how to do that …
Hi and thank you for the response,
What I want to export - per site - per day - are my custom variables x1 x2 x3 x4 values for every hit/visit. They show up nicely on the dashboard on a per site, per date basis, but I would like to export the values to a csv.
Warm regards
Leslie
ok, for the db part those aggregate values are stored in piwik_archive_blob_xxx e.g. piwik_archive_blob_2012_12 for December, which is not of much use for you.
The proper way is to use API, here’s couple of examples from piwik demo site
this will get sum from custom variables for week, note last column _idSubtable
now to get separated values for each, we need to reference that subtable
http://demo.piwik.org//index.php?module=API&method=CustomVariables.getCustomVariablesValuesFromNameId&idSite=7&period=week&date=yesterday&format=html&token_auth=anonymous&idSubtable=1545
and to get this in csv, just add format parameter
see http://piwik.org/docs/analytics-api/reference/ - for more details on how to change various stuff like perioed etc.
cheers …
It’s good - and a starting point - but doesn’t give me what I want.
Custom Variable x3 holds a unique identifier per visit - I need to recover a full list of values in x3 as a csv. The example just gives me a count - I need a full list of values in a csv. Doing this through a thin strawer of an API is probably not very efficient, hence querying the underlying database.
Back to the drawing board methinks - but thanks anyway.
Also - is this a bug? If you do:
http://demo.piwik.org//index.php?module=API&method=CustomVariables.getCustomVariables&idSite=7&period=week&date=yesterday&format=html&token_auth=anonymous
You get the subtable OK
but if you do
(so you can parse it easily) that column is completely missing - weird.
Well, then you’re looking for per visit scope x3, right?
You can query piwik_log_visit table directly, each visit is one row and column custom_var_v3 holds the value.
Note, you must not use “Delete old visitor logs and reports” feature, if you want to keep historical data as this wipes this particular table (data in piwik are read from archive tables)
cheers …
That’s perfect - thanks. Just what I was looking for.
Just for completeness - why the two versions?
| custom_var_k1 | varchar(200) | YES | | NULL | |
| custom_var_v1 | varchar(200) | YES | | NULL | |
| custom_var_k2 | varchar(200) | YES | | NULL | |
| custom_var_v2 | varchar(200) | YES | | NULL | |
| custom_var_k3 | varchar(200) | YES | | NULL | |
| custom_var_v3 | varchar(200) | YES | | NULL | |
| custom_var_k4 | varchar(200) | YES | | NULL | |
| custom_var_v4 | varchar(200) | YES | | NULL | |
| custom_var_k5 | varchar(200) | YES | | NULL | |
| custom_var_v5 | varchar(200) | YES | | NULL | |
custom_var_k - holds key (name of your variable)
custom_var_v - holds the actual value
Thank you Mahdi1234 - that covers everything I need. Awesome, many thanks for your time Sir.