Querying Analytics Data

I am inquiring about the Analytics API of Piwik Pro.

I am trying to get these metrices/dimensions in a python request:

  • sessions (metric - session)

  • page_views (metric - session)

  • bounces (metric - session)

  • referrer_type (dimension - session)

  • source (dimension - session)

  • medium (dimension - session)

  • campaign_name (dimension - session)

  • event_url (dimension - event)

  • event_title (dimension - event)

  • time_on_page (dimension - event)

  1. Why does the /query/ endpoint does not give session_id etc while the /events/ endpoint does?
  2. What is the best way to set up this query where I can also combine event_url with the correct number of sessions and bounce rate etc.?

Thanks.

Hi @laurens_hendrikx,

Using the raw data API (HTTP API — Piwik PRO Analytics Suite 16.40 documentation), one can retrieve the individual dimensions and metrics.

  1. Session ID is not a dimension you can retrieve vie the /query/ endpoint.

Both raw data session API as well as the raw data events API will return the column session_id.
You can use it to blend and calculate the data you require.

  1. You can either use the /query/ api to retrieve the required dimensions and metrics and let the calculation be done by Piwik PRO:
{
	"date_from": "2023-01-01",
	"date_to": "2023-12-31",
	"website_id": "dae17aa5-85fe-412b-9f2e-4123bd52cd97",
	"offset": 0,
	"limit": 10,
	"columns": [
		{
			"column_id": "referrer_type"
		},
		{
			"column_id": "source"
		},
		{
			"column_id": "medium"
		},
		{
			"column_id": "campaign_name"
		},
		{
			"column_id": "session_entry_url"
		},
		{
			"column_id": "session_entry_title"
		},
		{
			"column_id": "event_url"
		},
		{
			"column_id": "event_title"
		},
		{
			"column_id": "time_on_page"
		},
		{
			"column_id": "is_bounce"
		},
		{
			"column_id": "sessions"
		}
	],
	"order_by": [
		[
			10,
			"desc"
		]
	],
	"filters": {
		"operator": "and",
		"conditions": [
			{
				"operator": "or",
				"conditions": [
					{
						"column_id": "event_type",
						"condition": {
							"operator": "eq",
							"value": 1
						}
					}
				]
			}
		]
	},
	"metric_filters": null
}

If you need to filter based on campaign data only, you can use this:

{
	"date_from": "2023-01-01",
	"date_to": "2023-12-31",
	"website_id": "dae17aa5-85fe-412b-9f2e-4123bd52cd97",
	"offset": 0,
	"limit": 10,
	"columns": [
		{
			"column_id": "referrer_type"
		},
		{
			"column_id": "source"
		},
		{
			"column_id": "medium"
		},
		{
			"column_id": "campaign_name"
		},
		{
			"column_id": "session_entry_url"
		},
		{
			"column_id": "session_entry_title"
		},
		{
			"column_id": "event_url"
		},
		{
			"column_id": "event_title"
		},
		{
			"column_id": "time_on_page"
		},
		{
			"column_id": "is_bounce"
		},
		{
			"column_id": "sessions"
		}
	],
	"order_by": [
		[
			10,
			"desc"
		]
	],
	"filters": {
		"operator": "and",
		"conditions": [
			{
				"operator": "or",
				"conditions": [
					{
						"column_id": "event_type",
						"condition": {
							"operator": "eq",
							"value": 1
						}
					}
				]
			},
			{
				"operator": "or",
				"conditions": [
					{
						"column_id": "referrer_type",
						"condition": {
							"operator": "eq",
							"value": 6
						}
					}
				]
			}
		]
	},
	"metric_filters": null
}

You can basically build the report in Piwik PRO Analytics and check the API definition in the advanced section (as seen in the documentation: HTTP API — Piwik PRO Analytics Suite 16.40 documentation):

Keep in mind the offset and limit, in case you need to retrieve a lot of rows.

Sometimes it’s better to call the API multiple times (for instance because you want to know the bounce rate per campaign, instead of per campaign, per source, per medium, per entry page URL, etc…)

If you want to calculate it yourself, you can use the Raw data API and retrieve all the required dimensions and metrics, as documented here:

It will automatically return the session_id as part of the returned dataset, for both the events as well as the sessions API, so you can use it to blend the dataset.

Does this help?

Kind regards,
Richard

Hi, but when I am trying to use the sessions API with the endpoint /sessions/, I get this error:
{‘errors’: [{‘status’: ‘400’, ‘detail’: ‘Metric may not be used here.’, ‘source’: {‘parameter’: ‘columns.1.column_id’}}, {‘status’: ‘400’, ‘detail’: ‘Metric may not be used here.’, ‘source’: {‘parameter’: ‘columns.2.column_id’}}]}

For the following query:
query = {
“date_from”: date_from,
“date_to”: date_to,
“website_id”: website_id,
“offset”: 0,
“limit”: 100000,
“columns”: [{
“column_id”: “referrer_type”}, {
“column_id”: “sessions”}, {
“column_id”: “bounces”
}
],
“order_by”: [
[0,“asc”]
]
}

I cannot figure out how to get these metrics and dimensions in the /sessions/ endpoint?

Hi @laurens_hendrikx,

The sessions API provides you with raw session data, meaning you would need to calculate it based on the dimensions you retrieve from the API;

  • session_id (will be available by default)
  • is_bounce
  • referrer_type

Based on this info you can calculate the amount of sessions (count unique session_id)
Split them by bounce / not bounce ( 0 / 1 )
split them by referrer type, using the referrer_type.json (As seen in Columns — Piwik PRO Analytics Suite 16.40 documentation

[
    [
        1,
        "Direct entry"
    ],
    [
        2,
        "Search engine"
    ],
    [
        3,
        "Website"
    ],
    [
        6,
        "Campaign"
    ],
    [
        7,
        "Social"
    ]
]

)

Does it help?