Bounce rate & Exit rate calculation in mysql database schema

Support & Bugs
1

We need to calculate bounce rate and exit rate of website page, We installed piwik DB using mysql having tables like piwik_log_visit , action table as well.

We are able to view the bounce and exit rate of website on reporting dashboard but unable to find columns in piwik db table. We search these columns in all the table

Is Piwik provide any columns to calculate these values in db or we need to calculate using track data? If yes then please give the details about that.

2

We are able to view the bounce and exit rate of website on reporting dashboard

We need to calculate bounce rate and exit rate of website page

It seems you can view the variables you need, why do you want to get them from the database?

In Piwik data is not stored in MySQL tables so you can’t get the metrics like that.

3

We need these metrics for reporting along with my data

4

Those metrics are shown in Piwik, so you got them.

Do you want to use them in another application? Then use the CSV or XML exports. Anyway if you don’t explain more I can’t help you more.

5

Yes ,We are using these metrices in another application. We copy the data from local mysql piwik to sql server. Using these data we creates reporting on our side.

We does not want to use xls or csv because we are running job in every 3 sec. so rather than to write any reporting code we need these metrics in sql query.

Is any way to do this except the reporting.

6

No you can’t get data from the database, the HTTP API should be used.

Get the URL of the report (below the report table), choose the format you want (XML, JSON, CSV, …) and fetch that URL every 3s.

7

Ok,
Thanks Matthieu for your support !

8

Hi vasant,

I had a similar issue.
I used the piwik_log_link_visit_action table - this gives a list of all pages visited.

Do a
JOIN piwik.piwik_log_action
ON (piwik_log_link_visit_action.idaction_name = piwik.piwik_log_action.idaction)

To get the page names.
(I also have my product categories and skus in my custom variables)

Then write an algorithm to determine if the page was the last in ‘server_time’ by an ‘idvisit’

Hope this helped

9

Hi Vasant, I want to do exactly the same as you have been asking here: calculate the bounce rate using tables in Piwik’s My SQL database rather than using the API. Have you succeeded?? I would love to hear! Many thanks, Sandra