Create a report for all user ids that has campaign and goal conversion

Support & Bugs
1

Hi,

We need to create a report for all recorded user ids, that also have related campaign and at least one goal conversion for any visit. Is it possible to create custom reports like this from Matomo, or do I need to write some manual SQL to query the database directly? Not sure the report API can do this.

Thanks in advance
Olle

2

This query worked pretty well:

SELECT 
  DISTINCT(v1.user_id),
  v1.campaign_name,
  gl.name
FROM piwik_log_visit v1 
JOIN piwik_log_visit v2 ON v1.idvisitor = v2.idvisitor 
JOIN piwik_log_conversion con ON con.idvisit = v2.idvisit
JOIN piwik_goal gl ON gl.idgoal = con.idgoal
WHERE
v2.visit_goal_converted = 1 AND
v1.campaign_name = <your campaign name>

I will just make a plugin to get the results.