Plugin to send reports by email

[matt]

The idea behind the plugin is to make it possible to export reports by email. It would be possible also to specify preferences such as

• daily / weekly / monthly reports
• reports contained in the email

The implementation is not defined yet though, as we need to finish the task #54 first.

[kpham]

Hi guys,

My company's client want this feature badly and like to have it within this week :), as always. Anyhow, I'd like to implement this feature for our client.

First of, I've started to working on this plug-in yesterday and here is what I've got so far:

I created a plugin called "EmailReports".

1. The user should be able to click on "settings" link and see the plug-in within the admin menu.

• Problem: right now in order for any user to see the settings link, that user must have some admin permission. However, I can live with this since each of our users have their own websites (we have about 30 website that track with one piwik server".

2. I'd like to let the user opt-in or opt-out of getting reports via email.

• Solution: I created 2 radio buttons for user to choose from. onSelectEmailOptionClick() will make ajax call to EmailReports to update the information.

3) need to record who want what report(s) via email.

• Solution: I created additional table to piwik called "piwik_user_email_reports"

  login | email_frequency | activated_date | last_sent_date | modules_actions 
  user1 |   weekly        | default now()  | null           | VisitsSummary.getVisits,VisitsSummary.getUnique
  user2 |   monthly       | some date      | some date      | VisitFrequency.getSummary
  
  If last_sent_date no null then use last_sent_date else use activated_date (most likely very first email report for this user).    
  

4. need to record email subject, body, and footer for admin purpose:

• Solution: I created a table called "piwik_email_reports". This table will hold what kind of message the admin want to use when email the reports. Admin should be able to choose which email to use, add new email message, delete old email message, etc...

5. Right now I only want to sent data as csv for the start. So, which API calls is a report? There is method to get all plugins and their information but I cannot find an easy way to know if this plugins has report methods. e.g I don't want to run query against UsersManager or SitesManager, etc...

• Solution: Currently I hard-code an arrays of API calls that is a report, based on Piwik's API list. So I can show on the page for user to choose as well as make api calls to get the data.

(5) On this list is most crucial, and if there is a way for me to know which method is a report method I would love to know.

Comment please.

Thanks,
--Khanh Pham
--khanh@…

[matt]

• 1) we are planning to fix this (see edit their info (alias, email, password) #526, display the javascript code for their sites #554, define the website to load by default in the UI #401)
• 2) and 3) ok
• 4) what do you mean by "email message" can there be different message by email? it sounds not necessary. All email messages could have same content "Here is your web analytics reports for the period DATE1-DATE2. You will receive the next email on DATE3. Click here to view your Piwik reports.".
• 5) there is no metadata to say "this API call is a report" rather than a admin call. You could use some heuristics like: if parameters idSite, date, period are found, then it's a report api method. Also you should blacklist some plugins all together, eg. SitesManager, UsersManager, etc.

But for the plugin, it would be ideal to send "widgets" rather than csv exports of API. You can get the list of all widgets by calling Piwik_GetWidgetsList() that returns a list of Plugin, methods that you can directly call to get the HTML output of the widgets, which would be much easier to understand than plain CSV reports without labels (eg. the getVisits call would return value:1100 and it would be hard to understand as opposed to returning the VisitsSummary widget output).

Let me know what you think but all in all it would be a great new feature to Piwik and I'd be happy to review your code and answer your questions!

[matt]

btw, there is a plan to use the widgets in a similar way to build RSS feeds (see #151), which is very similar to email reports.

[matt]

Also, how do you plan to provide the UI to choose the reports to send in the emails? If you go the widget way, you could re-use the "widget menu selector" found in the dashboard and in the top menu "widgets" to provide a user friendly way to select the widgets and add them in the "mail template".

[kpham]

sample of plugin ui

[kpham]

Replying to matt:

* 1) we are planning to fix this (see edit their info (alias, email, password) #526, display the javascript code for their sites #554, define the website to load by default in the UI #401) * 2) and 3) ok * 4) what do you mean by "email message" can there be different message by email? it sounds not necessary. All email messages could have same content "Here is your web analytics reports for the period DATE1-DATE2. You will receive the next email on DATE3. Click here to view your Piwik reports.". * 5) there is no metadata to say "this API call is a report" rather than a admin call. You could use some heuristics like: if parameters idSite, date, period are found, then it's a report api method. Also you should blacklist some plugins all together, eg. SitesManager, UsersManager, etc. But for the plugin, it would be ideal to send "widgets" rather than csv exports of API. You can get the list of all widgets by calling Piwik_GetWidgetsList() that returns a list of Plugin, methods that you can directly call to get the HTML output of the widgets, which would be much easier to understand than plain CSV reports without labels (eg. the getVisits call would return value:1100 and it would be hard to understand as opposed to returning the VisitsSummary widget output). Let me know what you think but all in all it would be a great new feature to Piwik and I'd be happy to review your code and answer your questions!

hi Matt,

I've got the plugin somewhat working, but need to improve as you mention above:

• 1) for now I made one little change to the AdminMenu.php to show only what I want for the user (not ideal, but works for our client). I will follow up on #526 for proper implementation.
• 2 & 3 implemented the way you agreed. Please see emailReports.png file on this ticket.
• 4 is not in use at all, you're right we only need one message. It can configure in the Translation part.
• 5, as my original work, it is right now hardcoded all plugins and methods that is available. However, up comming days I will use the "widgets" as you mentions.

I have to send reports in csv format per our client requests. in the future I will reformat the csv contents. please see reports.csv for example

What I'm thinking between widgets and csv is giving the user another option (dropdown box on ui) for formats: e.g: if they want csv in the email attachment we'll give it to them. If they want widgets we'll give them widgets.

also, I added the feature called "email on Demand". This would give the user the option of sending email based on the date range that the user picked. Once the user click on "Send Email" button, ajax called to the server to send the email. Now, this feature does not required the user to opt-in to get email on schedule. However the system required the user to picked the report(s) that they want, before they can send email "on demand".

Please see emailReports.png, datepicker.png and reports.csv and let me know what you think. I definitely need more feedback.

Thanks again for your feedback,
--Khanh Pham
--khanh@…

[matt]

Hi Khahn, good work, that will be a nice new feature! I assume the code is in GPL and will be included in Piwik core. Can you post your code in the ticket, and it's OK if it's not finished, at least we can review it if necessary, and other people could give their feedback.

ok for "Email on demand", great idea! As you know custom date range reports are not yet a feature in Piwik even though we could build it, so I'm sure how we will integrate that. Does the custom date range work for any report of any plugins? Last time I heard it wasn't working for all metrics?

Here is my feedback.

Slightly simplify UI

• Rather than having optin and optout and period selector I would simplify and just have a section "Schedule Email Reports": Don't email me the reports, Email the reports daily, Email the reports weekly, Email the reports monthly.
• Below this section, another section "Email reports on demand": 'You can also receive your custom report on demand, for a custom date range. 'Select the date range', 'Send the email report now'. When clicked, ajax request with standard piwik "Please wait while your report is generated and sent. It can take a few minutes." and when done, a feedback message "Message sent to abc@…!".
• Below a section "Customize reports in your email" with the options to customize email content

Review

• I updated #526 with quick spec
• next to email weekly, can you specify "Email will be sent out every Monday"
• next to montly email, can you specify "Email will be sent out on the first day of every month"
• can you add "Email daily"? next to "Email daily" can you specify "Email will be sent out every day when the reports are generated"
• can you remind the user at the top of the screens: "All Email reports will be emailed to youremail@…" in case the user specified wrong email or just as a reminder
• can you rename the submit "done" in "Update report content
• please don't hardcode the list of available reports, as we will soon release new reports. If you use the heuristic "idSite, date, period" available, the plugin will automatically work with new versions of piwik and new reports.
• your reports.csv is not a valid CSV file, but maybe that's not an issue?
• if you need this custom CSV file, create a new "Renderer" that you can call "CustomCsv" for example. You custom csv renderer can query the website name and add it in the csv export.

Proposal for email customization: Widgets and API calls

Your idea of proposing widgets and CSV export at same time is very good idea! There is a difference between the way API calls and Widgets are generated:

• csv/xml/json exports are available for API calls, eg. "VisitsSummary.getVisits" is an API call. "Referers.getKeywords" is the API call that returns the list of keywords. You can get the list of all existing API methods by using Piwik_API_Proxy::getInstance()->getMetadata() (see how it's done in DocumentationGenerator.php. It may require small refactoring but should be easy, and if you need help on this, let me know (this is how we currently generate the API documentation it is all automatic so will be reusable to get the list of all API calls).
• Widgets are controllers methods, eg. "VisitsSummary.index" is the controller method that outputs the flash graph with sparklines below. "Referers.getKeywords" is the controller method that returns the "widget" that shows the html table with the keywords, inline search, etc. You can get the list of all existing widgets by calling Piwik_GetWidgetsList().

Some API calls and widgets have the same name, but they are different (API.php contains API, Controller.php contains the widget which are methods in the controller).

Therefore if we give user the choice between csv/xml/etc. and widgets, here is a proposal:

• (checkbox) "Send widgets attached as html". When checked, the "widget selector" would appear in overlay. The widget selector is currently used in dashboard and in the top menu "widgets" and could be reused in this plugin to allow easy preview & selection of widgets to add in the report. To select widget in the email report, UI experience would be exactly same as selecting widget for the dashboard. When a widget is clicked, overlay closes, and widget is added to the list of widgets in the email. The full list appears below the checkbox and could read "List of widgets in your report: List of Keywords List of Search Engines User Settings In this list, user can move widget titles up and down by drag n drop (you can use jquery ui sortable already included in piwik) and "delete" some widgets (by click on a red cross on the right of the name), allowing full customization of the order in which widgets will appear in the html report. The report will then be sent as an HTML file containing the html export file "reports-$date1-$date2.html" of all these widgets.

• (checkbox) "Send reports attached as [csv/customCsv/xml]". When checked, the list of all API methods is displayed below, and user can choose which API calls to receive by email (like in your current implementation).

Open question

• how can the user choose which websites he wants to receive in the reports? or is it all websites by default? (that's OK I just want to check)

Hope it helps. It's a bit long but this is very exciting new feature so.. :-) Thanks.

[JeanRey]

Is it possible to have the code of this plugin ?

[kpham]

Replying to JeanRey:

Is it possible to have the code of this plugin ?

Hi JeanRey,

Sorry for the delay on this plugin, I will try to remove all text and information regards to my company and will submit the code for you to try, Its not done the way we wanted :( Yet, this plugin needs sometime.

thanks, Khanh.

[mmimlav]

what's about this plugin? finished?

[matt]

no, kpham never released the source code of his alpha version.

[mmimlav]

Thanks for the answer. I wish that the PDF export comes soon