Easy-digital-downloads: CSV earnings reports

[pippinsplugins]

We have a PDF report that shows earnings over time. As we have discussed getting rid of this report in #1808, I think we should seriously consider adding a new export option to generate a CSV of earnings . sales over time.

The new report that Stripe just introduced is a perfect example of what this could look like.

[pippinsplugins]

Punting to 2.5 for the reports release.

[pippinsplugins]

This won't be able to be finished for 2.5.

[sunnyratilal]

@pippinsplugins As this is already implemented in EDD core as the Earnings and Sales Stats Export, can we close this out?

[pippinsplugins]

@sunnyratilal No, this is different.

The earnings / sales export provides a CSV of records. The idea for this report was to provide an actual report that includes summed totals, subtotals, taxes, discounts, total items, etc, like the screenshot above.

[sunnyratilal]

@easydigitaldownloads/core-devs Ready for general testing.

[sunnyratilal]

The new report can be generated from the Export page which displays the form under the Export Earnings Report heading.

[brashrebel]

Just tested. Works great for me. Pretty cool feature too.

I think we might also want to consider some ways to add a little more information for users here so they will better understand the difference between the stats and the report. Maybe a tooltip, small screenshot or link to docs so users wondering what these files are for and what they'll look like will more likely comprehend.

[pippinsplugins]

@sunnyratilal does this use the batch processing API? It will need to if not (haven't had a chance to test it yet).

[pippinsplugins]

@sunnyratilal confirmed it does not use batch processing. It will need to for performance reasons. Can you update it please?

[sunnyratilal]

@pippinsplugins The SQL query is written such that all the data is pulled in at once requiring _no_ further processing of the data with edd functions.

The query to pull in the payments of an entire year looks like this:

SELECT SUM(meta_value) AS total, DATE_FORMAT(posts.post_date, '%m') AS m, YEAR(posts.post_date) AS y, COUNT(DISTINCT posts.ID) AS count, posts.post_status AS status
FROM wp_posts AS posts
INNER JOIN wp_postmeta ON posts.ID = wp_postmeta.post_ID
WHERE posts.post_type IN ('edd_payment')
AND wp_postmeta.meta_key = '_edd_payment_total'
AND posts.post_date >= '2016-02-01'
AND posts.post_date < '2017-02-01'
GROUP BY YEAR(posts.post_date), MONTH(posts.post_date), posts.post_status
ORDER by posts.post_date ASC

The data returned looks like this:

| total | m | y | count | status |
| ----- | - | - | ----- | ------ |
| 3 | 11 | 2016 | 1 | publish |
| 610.8 | 12 | 2016 | 11 | publish |
| 24 | 12 | 2016 | 1 | refunded |
| 29 | 12 | 2016 | 1 | cancelled |
| 39 | 12 | 2016 | 1 | revoked |
| 6764 | 01 | 2017 | 279 | publish |

Best way of converting this to batch processing would be to do a query for month-by-month but that would mean a lot of extra SQL queries to fetch one row of data. Is that how you'd like to me to proceed with this?

[sunnyratilal]

@cklosowski Would love your input here too please

[cklosowski]

@sunnyratilal it does need to use batch processing because of this: INNER JOIN wp_postmeta

On sites like the EDD site it can cause the thread to just die b/c it takes too long.

[sunnyratilal]

@cklosowski I'll get it updated to do batch processing.

[cklosowski]

Looks like we missed something in the commit, I'm getting the following when clicking to export:
( ! ) Notice: Undefined index: args in /app/public/wp-includes/functions.php on line 5050
Warning: require_once(/app/public/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/class-export-earnings-report.php): failed to open stream: No such file or directory in /app/public/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/export/export-actions.php on line 59
Fatal error: require_once(): Failed opening required '/app/public/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/class-export-earnings-report.php' (include_path='.:') in /app/public/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/export/export-actions.php on line 59

[sunnyratilal]

@pippinsplugins @cklosowski This has been moved to over batch processing and is ready for retesting.

[pippinsplugins]

@sunnyratilal Seeing a couple of issues:

My store has sales. I exported a file without selecting any date ranges, so I should have received a report for all time. The CSV, however, came up blank. Looks like we need to account for date defaults.

Second, when the CSV came up blank, there was a single row of zeros and a date of 1970-01-01. We shouldn't add any data at all when there is no data.

Third, we need to remove the old CSV export of earnings and sales.

Fourth, since we're showing month-by-month summaries, I don't think we should allow admins to pick exact dates. Instead we should do date / year like the old report has.

[sunnyratilal]

@pippinsplugins PR updated and ready for re-testing.

[cklosowski]

I think this is working as expected for me. All the include issues are fixed up, and the above concerns from @pippinsplugins seem to be addressed.

[pippinsplugins]

Testing now as well.

[pippinsplugins]

This is working _great_! A few of things though:

1. Cancelled orders should not show as a negative earning (they do currently). The cancelled order status is left over from old Recurring Payments and will likely be removed in the future. It simply indicates that the subscription for the purchase was cancelled, but does not indicate a refund, so no funds were reversed. I would actually vote that we exclude _Cancelled_ from the report as it's really just a confusing status now and Recurring Payments doesn't even use it any longer.

2. Should Failed and Abandoned payments be considered a negative? They represent sales that didn't succeed so it's definitely not a positive revenue, but it also wasn't revenue reversed, so it's not a negative.

3. Revoked sales should not be negative. They are successful purchases that have had their customer access revoked but no funds were reversed.

[pippinsplugins]

Looks like we have a PHP notice too:

PHP Notice: Undefined variable: col_data in /Users/pippin/Sites/edd/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/export/class-batch-export-earnings-report.php on line 76

[sunnyratilal]

@pippinsplugins @cklosowski PR updated

[pippinsplugins]

@sunnyratilal Is the NET Activity column set supposed to have sales and amounts? Neither column has a header for me. Intentional?

[sunnyratilal]

Intentional. It's been made to mimic that of Stripe's report in the example you posted above.

Net Activity represents the total of sales with publish status.

[sunnyratilal]

As discussed in Slack, the Gross Amount of Sales is determined by Publish + Refunded + Revoked + Cancelled.

The Net is just Publish.

[pippinsplugins]

Works for me! Just making sure :)

[pippinsplugins]

@sunnyratilal That's fixed!

I'm seeing some notices still though:

Jan 18, 20:44:35 PHP Notice: Undefined index: revoked in /Users/pippin/Sites/edd/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/export/class-batch-export-earnings-report.php on line 149

Jan 18, 20:44:35 PHP Notice: Undefined index: cancelled in /Users/pippin/Sites/edd/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/export/class-batch-export-earnings-report.php on line 149

Jan 18, 20:44:36 PHP Notice: Undefined index: refunded in /Users/pippin/Sites/edd/wp-content/plugins/easy-digital-downloads/includes/admin/reporting/export/class-batch-export-earnings-report.php on line 149

[sunnyratilal]

@pippinsplugins Added the isset checks - my bad.

[pippinsplugins]

Looks good!