# Reporting View - Database Schema for Business Intelligence (BI) Reporting
**URL:** https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094
**Category:** General Discussion
**Created:** [December 20, 2019, 6:18am UTC](https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094 "2019-12-20T06:18:46Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@tornmarketing](https://forum.mautic.org/u/tornmarketing)
#### Post date: [December 20, 2019, 6:18am UTC](https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094/1 "2019-12-20T06:18:46Z")
Hey Guys
Been trying to map out the database to report on Email Performance
Does anyone have previous built database views they would be willing to share?
Elements I am still struggling with
Per email / contact
- Unique Clicks / total clicks
- Latest / First Device used on email
SELECT DISTINCT
`mautic_email_stats`.`id` AS `id`,
`mautic_emails`.`subject` AS `Email_Subject`,
`mautic_leads`.`firstname` AS `firstname`,
`mautic_leads`.`lastname` AS `lastname`,
`mautic_leads`.`company` AS `company`,
`mautic_leads`.`email` AS `email`,
`mautic_email_stats`.`is_read` AS `is_read`,
`mautic_email_stats`.`is_failed` AS `is_failed`,
`mautic_email_stats`.`open_count` AS `open_count`,
`mautic_email_stats`.`last_opened` AS `last_opened`,
`mautic_email_stats`.`date_read` AS `date_read`,
`mautic_email_stats`.`viewed_in_browser` AS `viewed_in_browser`,
`mautic_email_stats`.`date_sent` AS `date_sent`,
`mautic_email_stats`.`email_address` AS `email_address`,
`mautic_lead_donotcontact`.`date_added` AS `Unsubscribe_Date`,
`mautic_leads`.`phone` AS `phone`,
`mautic_lead_lists`.`name` AS `Email_Segment_Name`,
`mautic_categories`.`title` AS `Email_Category_Name`
FROM
(((((((
`mautic_email_stats`
LEFT JOIN `mautic_leads` ON ((
`mautic_email_stats`.`lead_id` = `mautic_leads`.`id`
)))
LEFT JOIN `mautic_lead_devices` ON ((
`mautic_lead_devices`.`lead_id` = `mautic_leads`.`id`
)))
LEFT JOIN `mautic_email_stats_devices` ON (((
`mautic_email_stats_devices`.`device_id` = `mautic_lead_devices`.`id`
)
AND ( `mautic_email_stats_devices`.`stat_id` = `mautic_email_stats`.`id` ))))
LEFT JOIN `mautic_lead_lists` ON ((
`mautic_email_stats`.`list_id` = `mautic_lead_lists`.`id`
)))
LEFT JOIN `mautic_emails` ON ((
`mautic_email_stats`.`email_id` = `mautic_emails`.`id`
)))
LEFT JOIN `mautic_lead_donotcontact` ON (((
`mautic_lead_donotcontact`.`channel_id` = `mautic_emails`.`id`
)
AND ( `mautic_lead_donotcontact`.`lead_id` = `mautic_email_stats`.`lead_id` ))))
JOIN `mautic_categories` ON ((
`mautic_emails`.`category_id` = `mautic_categories`.`id`
)))
WHERE
( `mautic_categories`.`title` = ‘XXXXX’ )
ORDER BY
`mautic_email_stats`.`date_sent` DESC
---
### Author:  [@tornmarketing](https://forum.mautic.org/u/tornmarketing)
#### Post date: [December 21, 2019, 3:47am UTC](https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094/2 "2019-12-21T03:47:08Z")
Thought I would monitor the queries coming in from the default reports
Selecting all the fields and changed the limit from 100 to 10,000
~600s run time for 30k records
SELECT
l.email AS email1,
e.NAME AS e\_name1,
l.firstname AS firstname1,
l.lastname AS lastname1,
DATE( es.date\_sent ) AS date\_sent1,
DATE( es.date\_read ) AS date\_read1,
IF
( cut.hits IS NULL, 0, 1 ) AS is\_hit1,
IFNULL( cut.hits, 0 ) AS hits1,
IFNULL( cut.unique\_hits, 0 ) AS unique\_hits1,
IF
( dnc.id IS NOT NULL AND dnc.reason = 1, 1, 0 ) AS unsubscribed1,
c.title AS category\_title1,
e.SUBJECT AS subject2,
e.sent\_count AS sent\_count1,
es.is\_read AS is\_read1,
es.viewed\_in\_browser AS viewed\_in\_browser1,
vp.id AS id1,
vp.SUBJECT AS subject1,
DATE( e.variant\_start\_date ) AS variant\_start\_date1,
IF
( dnc.id IS NOT NULL AND dnc.reason = 2, 1, 0 ) AS bounced1,
cmp.NAME AS name1,
clel.campaign\_id AS campaign\_id1,
c.id AS category\_id1,
comp.companyaddress1 AS companyaddress11,
comp.companyaddress2 AS companyaddress21,
comp.companyannual\_revenue AS companyannual\_revenue1,
comp.companycity AS companycity1,
comp.companyemail AS companyemail1,
comp.companyname AS companyname1,
comp.companycountry AS companycountry1,
comp.companydescription AS companydescription1,
comp.companyfax AS companyfax1,
comp.id AS comp\_id1,
comp.companyindustry AS companyindustry1,
comp.companynumber\_of\_employees AS companynumber\_of\_employees1,
comp.companyphone AS companyphone1,
comp.companystate AS companystate1,
comp.companywebsite AS companywebsite1,
comp.companyzipcode AS companyzipcode1,
l.address1 AS address11,
l.address2 AS address21,
l.attribution AS attribution1,
l.attribution\_date AS attribution\_date1,
l.city AS city1,
l.company AS company1,
l.country AS country1,
l.facebook AS facebook1,
l.fax AS fax1,
l.foursquare AS foursquare1,
l.googleplus AS googleplus1,
l.id AS contactId1,
l.instagram AS instagram1,
l.linkedin AS linkedin1,
l.mobile AS mobile1,
l.custom\_field\_a\_id AS custom\_field\_a\_id1,
l.custom\_field\_b AS custom\_field\_b1,
l.custom\_field\_c AS custom\_field\_c1,
l.phone AS phone1,
l.points AS points1,
l.position AS position1,
l.preferred\_locale AS preferred\_locale1,
l.skype AS skype1,
l.state AS state1,
l.title AS title1,
l.twitter AS twitter1,
l.website AS website1,
l.zipcode AS zipcode1,
e.created\_by\_user AS e\_created\_by\_user1,
e.date\_added AS e\_date\_added1,
e.date\_modified AS e\_date\_modified1,
e.description AS e\_description1,
es.email\_address AS email\_address1,
e.id AS e\_id1,
i.ip\_address AS ip\_address1,
es.is\_failed AS is\_failed1,
e.is\_published AS e\_is\_published1,
companies\_lead.is\_primary AS is\_primary1,
e.lang AS lang1,
e.modified\_by\_user AS e\_modified\_by\_user1,
es.source AS source1,
es.source\_id AS source\_id1,
e.publish\_down AS e\_publish\_down1,
e.publish\_up AS e\_publish\_up1,
IF
( es.date\_read IS NOT NULL, TIMEDIFF( es.date\_read, es.date\_sent ), ‘-’ ) AS read\_delay1,
es.retry\_count AS retry\_count1,
e.revision AS revision1
FROM
mauvr\_email\_stats es
LEFT JOIN mautic\_emails e ON e.id = es.email\_id
LEFT JOIN mautic\_leads l ON l.id = es.lead\_id
LEFT JOIN mautic\_ip\_addresses i ON i.id = es.ip\_id
LEFT JOIN mautic\_emails vp ON vp.id = e.variant\_parent\_id
LEFT JOIN mautic\_categories c ON c.id = e.category\_id
LEFT JOIN (
SELECT
COUNT( ph.id ) AS hits,
COUNT(
DISTINCT ( ph.redirect\_id )) AS unique\_hits,
cut2.channel\_id,
ph.lead\_id
FROM
mautic\_channel\_url\_trackables cut2
INNER JOIN mautic\_page\_hits ph ON cut2.redirect\_id = ph.redirect\_id
AND cut2.channel\_id = ph.source\_id
WHERE
cut2.channel = ‘email’
AND ph.source = ‘email’
GROUP BY
cut2.channel\_id,
ph.lead\_id
) cut ON e.id = cut.channel\_id
AND es.lead\_id = cut.lead\_id
LEFT JOIN mautic\_lead\_donotcontact dnc ON e.id = dnc.channel\_id
AND dnc.channel = ‘email’
AND es.lead\_id = dnc.lead\_id
LEFT JOIN mautic\_campaign\_lead\_event\_log clel ON clel.channel = “email”
AND e.id = clel.channel\_id
AND clel.lead\_id = l.id
LEFT JOIN mautic\_campaigns cmp ON cmp.id = clel.campaign\_id
LEFT JOIN mautic\_companies\_leads companies\_lead ON l.id = companies\_lead.lead\_id
LEFT JOIN mautic\_companies comp ON companies\_lead.company\_id = comp.id
WHERE
es.date\_sent IS NULL
OR ( es.date\_sent BETWEEN ‘2019-11-20 13:00:00’ AND ‘2019-12-21 03:27:11’ )
LIMIT 100000 OFFSET 0
---
### Author:  [@tornmarketing](https://forum.mautic.org/u/tornmarketing)
#### Post date: [December 22, 2019, 3:47am UTC](https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094/3 "2019-12-22T03:47:01Z")
After much pain and suffering I have come up with this solution
Daily Scheduled Event in phpmyadmin
Daily roll up of the the the email activity report into one table
I had to play some fun games with setting the table, then emptying it before inserting.
Imported data in Mautic wasnt clean and kept throwing errors, this way the data is structured and pushes through ignoring the errors.
If anyone knows a way to Create a table and ignoring errors, please let me know!
BEGIN
DROP TABLE IF EXISTS `email_stats_daily_rollup`;
CREATE TABLE `email_stats_daily_rollup` AS
SELECT
l.email AS email1,
e.`name` AS e\_name1,
l.firstname AS firstname1,
l.lastname AS lastname1,
DATE( es.date\_sent ) AS date\_sent1,
DATE( es.date\_read ) AS date\_read1,
IF
( cut.hits IS NULL, 0, 1 ) AS is\_hit1,
IFNULL( cut.hits, 0 ) AS hits1,
IFNULL( cut.unique\_hits, 0 ) AS unique\_hits1,
IF
( dnc.id IS NOT NULL AND dnc.reason = 1, 1, 0 ) AS unsubscribed1,
c.title AS category\_title1,
e.`subject` AS subject2,
e.sent\_count AS sent\_count1,
es.is\_read AS is\_read1,
es.viewed\_in\_browser AS viewed\_in\_browser1,
– vp.id AS id1,
– vp.`subject` AS subject1,
DATE( e.variant\_start\_date ) AS variant\_start\_date1,
IF
( dnc.id IS NOT NULL AND dnc.reason = 2, 1, 0 ) AS bounced1,
cmp.`name` AS name1,
clel.campaign\_id AS campaign\_id1,
c.id AS category\_id1,
comp.companyaddress1 AS companyaddress11,
comp.companyaddress2 AS companyaddress21,
comp.companyannual\_revenue AS companyannual\_revenue1,
comp.companycity AS companycity1,
comp.companyemail AS companyemail1,
comp.companyname AS companyname1,
comp.companycountry AS companycountry1,
comp.companydescription AS companydescription1,
comp.companyfax AS companyfax1,
comp.id AS comp\_id1,
comp.companyindustry AS companyindustry1,
comp.companynumber\_of\_employees AS companynumber\_of\_employees1,
comp.companyphone AS companyphone1,
comp.companystate AS companystate1,
comp.companywebsite AS companywebsite1,
comp.companyzipcode AS companyzipcode1,
l.address1 AS address11,
l.address2 AS address21,
l.attribution AS attribution1,
l.attribution\_date AS attribution\_date1,
l.city AS city1,
l.company AS company1,
l.country AS country1,
l.facebook AS facebook1,
l.fax AS fax1,
l.foursquare AS foursquare1,
l.googleplus AS googleplus1,
l.id AS contactId1,
l.instagram AS instagram1,
l.linkedin AS linkedin1,
l.mobile AS mobile1,
– l.pagespeed\_submission\_id AS pagespeed\_submission\_id1,
– l.pagespeed\_url AS pagespeed\_url1,
– l.pagespeed\_url\_report AS pagespeed\_url\_report1,
l.phone AS phone1,
l.points AS points1,
l.position AS position1,
l.preferred\_locale AS preferred\_locale1,
l.skype AS skype1,
l.state AS state1,
l.title AS title1,
l.twitter AS twitter1,
l.website AS website1,
l.zipcode AS zipcode1,
e.created\_by\_user AS e\_created\_by\_user1,
e.date\_added AS e\_date\_added1,
e.date\_modified AS e\_date\_modified1,
e.description AS e\_description1,
es.email\_address AS email\_address1,
e.id AS e\_id1,
– i.ip\_address AS ip\_address1,
es.is\_failed AS is\_failed1,
e.is\_published AS e\_is\_published1,
companies\_lead.is\_primary AS is\_primary1,
e.lang AS lang1,
e.modified\_by\_user AS e\_modified\_by\_user1,
es.source AS source1,
es.source\_id AS source\_id1,
e.publish\_down AS e\_publish\_down1,
e.publish\_up AS e\_publish\_up1,
IF
( es.date\_read IS NOT NULL, TIMEDIFF( es.date\_read, es.date\_sent ), ‘-’ ) AS read\_delay1,
es.retry\_count AS retry\_count1,
e.revision AS revision1
FROM
mautic\_email\_stats es
LEFT JOIN mautic\_emails e ON e.id = es.email\_id
LEFT JOIN mautic\_leads l ON l.id = es.lead\_id
– LEFT JOIN mautic\_ip\_addresses i ON i.id = es.ip\_id
– LEFT JOIN mautic\_emails vp ON vp.id = e.variant\_parent\_id
LEFT JOIN mautic\_categories c ON c.id = e.category\_id
LEFT JOIN (
SELECT
COUNT( ph.id ) AS hits,
COUNT(
DISTINCT ( ph.redirect\_id )) AS unique\_hits,
cut2.channel\_id,
ph.lead\_id
FROM
mautic\_channel\_url\_trackables cut2
INNER JOIN mautic\_page\_hits ph ON cut2.redirect\_id = ph.redirect\_id
AND cut2.channel\_id = ph.source\_id
WHERE
cut2.channel = ‘email’
AND ph.source = ‘email’
GROUP BY
cut2.channel\_id,
ph.lead\_id
) cut ON e.id = cut.channel\_id
AND es.lead\_id = cut.lead\_id
LEFT JOIN mautic\_lead\_donotcontact dnc ON e.id = dnc.channel\_id
AND dnc.channel = ‘email’
AND es.lead\_id = dnc.lead\_id
LEFT JOIN mautic\_campaign\_lead\_event\_log clel ON clel.channel = “email”
AND e.id = clel.channel\_id
AND clel.lead\_id = l.id
LEFT JOIN mautic\_campaigns cmp ON cmp.id = clel.campaign\_id
LEFT JOIN mautic\_companies\_leads companies\_lead ON l.id = companies\_lead.lead\_id
LEFT JOIN mautic\_companies comp ON companies\_lead.company\_id = comp.id
WHERE
es.date\_sent IS NOT NULL
limit 10
```
;
-- Phase 2 of query
```
TRUNCATE TABLE `email_stats_daily_rollup`;
INSERT IGNORE `email_stats_daily_rollup`
SELECT
l.email AS email1,
e.`name` AS e\_name1,
l.firstname AS firstname1,
l.lastname AS lastname1,
DATE( es.date\_sent ) AS date\_sent1,
DATE( es.date\_read ) AS date\_read1,
IF
( cut.hits IS NULL, 0, 1 ) AS is\_hit1,
IFNULL( cut.hits, 0 ) AS hits1,
IFNULL( cut.unique\_hits, 0 ) AS unique\_hits1,
IF
( dnc.id IS NOT NULL AND dnc.reason = 1, 1, 0 ) AS unsubscribed1,
c.title AS category\_title1,
e.`subject` AS subject2,
e.sent\_count AS sent\_count1,
es.is\_read AS is\_read1,
es.viewed\_in\_browser AS viewed\_in\_browser1,
– vp.id AS id1,
– vp.`subject` AS subject1,
DATE( e.variant\_start\_date ) AS variant\_start\_date1,
IF
( dnc.id IS NOT NULL AND dnc.reason = 2, 1, 0 ) AS bounced1,
cmp.`name` AS name1,
clel.campaign\_id AS campaign\_id1,
c.id AS category\_id1,
comp.companyaddress1 AS companyaddress11,
comp.companyaddress2 AS companyaddress21,
comp.companyannual\_revenue AS companyannual\_revenue1,
comp.companycity AS companycity1,
comp.companyemail AS companyemail1,
comp.companyname AS companyname1,
comp.companycountry AS companycountry1,
comp.companydescription AS companydescription1,
comp.companyfax AS companyfax1,
comp.id AS comp\_id1,
comp.companyindustry AS companyindustry1,
comp.companynumber\_of\_employees AS companynumber\_of\_employees1,
comp.companyphone AS companyphone1,
comp.companystate AS companystate1,
comp.companywebsite AS companywebsite1,
comp.companyzipcode AS companyzipcode1,
l.address1 AS address11,
l.address2 AS address21,
l.attribution AS attribution1,
l.attribution\_date AS attribution\_date1,
l.city AS city1,
l.company AS company1,
l.country AS country1,
l.facebook AS facebook1,
l.fax AS fax1,
l.foursquare AS foursquare1,
l.googleplus AS googleplus1,
l.id AS contactId1,
l.instagram AS instagram1,
l.linkedin AS linkedin1,
l.mobile AS mobile1,
– l.pagespeed\_submission\_id AS pagespeed\_submission\_id1,
– l.pagespeed\_url AS pagespeed\_url1,
– l.pagespeed\_url\_report AS pagespeed\_url\_report1,
l.phone AS phone1,
l.points AS points1,
l.position AS position1,
l.preferred\_locale AS preferred\_locale1,
l.skype AS skype1,
l.state AS state1,
l.title AS title1,
l.twitter AS twitter1,
l.website AS website1,
l.zipcode AS zipcode1,
e.created\_by\_user AS e\_created\_by\_user1,
e.date\_added AS e\_date\_added1,
e.date\_modified AS e\_date\_modified1,
e.description AS e\_description1,
es.email\_address AS email\_address1,
e.id AS e\_id1,
– i.ip\_address AS ip\_address1,
es.is\_failed AS is\_failed1,
e.is\_published AS e\_is\_published1,
companies\_lead.is\_primary AS is\_primary1,
e.lang AS lang1,
e.modified\_by\_user AS e\_modified\_by\_user1,
es.source AS source1,
es.source\_id AS source\_id1,
e.publish\_down AS e\_publish\_down1,
e.publish\_up AS e\_publish\_up1,
IF
( es.date\_read IS NOT NULL, TIMEDIFF( es.date\_read, es.date\_sent ), ‘-’ ) AS read\_delay1,
es.retry\_count AS retry\_count1,
e.revision AS revision1
FROM
mautic\_email\_stats es
LEFT JOIN mautic\_emails e ON e.id = es.email\_id
LEFT JOIN mautic\_leads l ON l.id = es.lead\_id
– LEFT JOIN mautic\_ip\_addresses i ON i.id = es.ip\_id
– LEFT JOIN mautic\_emails vp ON vp.id = e.variant\_parent\_id
LEFT JOIN mautic\_categories c ON c.id = e.category\_id
LEFT JOIN (
SELECT
COUNT( ph.id ) AS hits,
COUNT(
DISTINCT ( ph.redirect\_id )) AS unique\_hits,
cut2.channel\_id,
ph.lead\_id
FROM
mautic\_channel\_url\_trackables cut2
INNER JOIN mautic\_page\_hits ph ON cut2.redirect\_id = ph.redirect\_id
AND cut2.channel\_id = ph.source\_id
WHERE
cut2.channel = ‘email’
AND ph.source = ‘email’
GROUP BY
cut2.channel\_id,
ph.lead\_id
) cut ON e.id = cut.channel\_id
AND es.lead\_id = cut.lead\_id
LEFT JOIN mautic\_lead\_donotcontact dnc ON e.id = dnc.channel\_id
AND dnc.channel = ‘email’
AND es.lead\_id = dnc.lead\_id
LEFT JOIN mautic\_campaign\_lead\_event\_log clel ON clel.channel = “email”
AND e.id = clel.channel\_id
AND clel.lead\_id = l.id
LEFT JOIN mautic\_campaigns cmp ON cmp.id = clel.campaign\_id
LEFT JOIN mautic\_companies\_leads companies\_lead ON l.id = companies\_lead.lead\_id
LEFT JOIN mautic\_companies comp ON companies\_lead.company\_id = comp.id
WHERE
es.date\_sent IS NOT NULL
– limit 1000
```
;
```
END
---
### Author:  [@healthstatus1](https://forum.mautic.org/u/healthstatus1)
#### Post date: [December 3, 2022, 12:51am UTC](https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094/4 "2022-12-03T00:51:11Z")
This looks very interesting. We have a single campaign, with multiple emails and multiple segments that the campaign runs against, the segments which are basically various traffic/lead sources. We want to track the performance of the email campaign against these segments. The existing Mautic reporting is severely lacking in the ability to report like this.
Using this method would you be able to generate reporting like I am outlining? What BI tool are you using to generate the final report?
---
### Author:  [@tornmarketing](https://forum.mautic.org/u/tornmarketing)
#### Post date: [December 5, 2022, 1:58am UTC](https://forum.mautic.org/t/reporting-view-database-schema-for-business-intelligence-bi-reporting/12094/5 "2022-12-05T01:58:44Z")
Segments are dynamic (one to many relationship), so you maybe asking a fair bit of mautic to assuming your specific circumstances.
Your requirements
Being a segment report to a campaign you would want to flip the view on how you are reporting it, as a contact could be in multiple dynamic or static segments, it would be very hard to interrupt.
So you would create reporting view for each segment, then join the above report output based on the contact id or email address.
Now - I suspect you have clean segments where you have only 1 segment per contact based on how you framed your question - But you have to build it with the assumption that they maybe in multiple.
If you can make sure that 1 contact is only in 1 segment
A simplier option - I am unsure how you are handling your segments so this is an assumption - if its a contact custom field, stage or what ever. you could potentially add that stage/field onto the contact enrichment and then group it from there.
Thus a few tweaks to the query above and you will have the grouping data available from what you need/want
As for BI - I run alot of my stuff via data studio using a mysql connector, but its the same for powerbi or the like with a mysql connector.