Visualizer DB charts: Suggestions for charts that can be created using EDD, WP and other plugins

[contactashish13]

Suggestions here would help in creating use cases, test scenarios and marketing material to push this new feature (#217). The intent is to be able to extract data from the WP database and show them as (almost) real-time charts.

Similar to #217 (comment)

A few more:

• net revenue from EDD per day for the last week/month
• Average sales in the last week/month

Suggestions welcome @ineagu @selul @HardeepAsrani @rodica-andronache

[selul]

@contactashish13 we can go with those and the ones from #217 except pirate forms and wppr

[contactashish13]

NOTE: All queries to be collected here.

EDD

• total revenue per payment gateway

SELECT pm1.meta_value, SUM(pm2.meta_value) FROM `wp_postmeta` pm1, `wp_postmeta` pm2  WHERE pm1.meta_key='_edd_payment_gateway' AND pm1.post_id=pm2.post_id AND pm2.meta_key='_edd_payment_total' AND pm1.meta_value<>'' GROUP BY pm1.meta_value

• number of subscriptions per product

SELECT prod.post_title, COUNT(*) FROM `wp_edd_subscriptions`, `wp_posts` prod, (
SELECT pm.post_id AS 'postid', pm.meta_value AS 'subsid' FROM `wp_posts` p, `wp_postmeta` pm WHERE p.post_type='edd_payment' AND p.id=pm.post_id AND pm.meta_key ='subscription_id' ) subs
WHERE `wp_edd_subscriptions`.id = subs.subsid
AND prod.id = product_id
GROUP BY product_id
ORDER BY prod.post_title

• total payments per subscribed product

SELECT prod.post_title, SUM((SELECT pp.meta_value FROM wp_postmeta pp WHERE pp.post_id=subs.postid AND pp.meta_key='_edd_payment_total')) FROM `wp_edd_subscriptions`, `wp_posts` prod, (
SELECT pm.post_id AS 'postid', pm.meta_value AS 'subsid' FROM `wp_posts` p, `wp_postmeta` pm WHERE p.post_type='edd_payment' AND p.id=pm.post_id AND pm.meta_key ='subscription_id' ) subs
WHERE `wp_edd_subscriptions`.id = subs.subsid
AND prod.id = product_id
GROUP BY product_id
ORDER BY prod.post_title

• payment per month/year

SELECT DATE_FORMAT(p.post_date, '%M, %Y') AS dt, CAST(SUM(pm1.meta_value) AS DECIMAL(10,2)) FROM `wp_postmeta` pm1, `wp_posts` p WHERE p.id=pm1.post_id AND pm1.meta_key='_edd_payment_total'  GROUP BY dt, YEAR(p.post_date), MONTH(p.post_date) ORDER BY YEAR(p.post_date), MONTH(p.post_date)

WooCommerce

• http://www.w3programmings.com/woocommerce-basic-sql-queries-to-fetch-data-for-reports-and-api/
• https://www.green-box.co.uk/handy-woocommerce-sql-queries/
• https://zjstech.com/basic-woocommerce-mysql-queries-for-orders-products-and-categories/

WP core

• comments per post

SELECT post_title, COUNT(DISTINCT c.`comment_ID`) AS cc FROM wp_posts p LEFT JOIN wp_comments c ON p.id = c.`comment_post_ID` GROUP BY p.ID ORDER BY cc DESC

• Popularity of authors on the basis of comments per author

SELECT u.user_nicename, COUNT(DISTINCT c.`comment_ID`) AS cc FROM wp_users u LEFT JOIN wp_posts p ON p.`post_author` = u.id  LEFT JOIN wp_comments c ON p.id = c.`comment_post_ID` GROUP BY u.id ORDER BY cc DESC

[contactashish13]

closing this as https://docs.themeisle.com/article/970-visualizer-sample-queries-to-generate-charts now contains all the queries

[ineagu]

as a user I would love to see how those looks like with some sample data, a small video, image, like just 1 image of a page with all those reports

…

On Mon, Dec 3, 2018 at 10:45 AM contactashish13 ***@***.***> wrote: Closed #309 <#309>. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#309 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABw6DABRbEgNz1SLCSHziAwR9vfWvHfGks5u1QDJgaJpZM4Xd8VQ> .

-- Ionut Neagu http://themeisle.com | http://codeinwp.com | http://ionutn.com

[contactashish13]

@sab2ina can you help?

[sab2ina]

@contactashish13 having a visual resource for DB charts would certainly help explain this feature better. How do you suggest we can create it so we can include it in the plugin page and docs? I am not sure how to tackle this, do you think you can give us a hand?

cc @dascalescuro

[dascalescuro]

No the best way to present this, but we tried to add this to a video: https://youtu.be/sbm8Q0bJGRM?t=73

are we talking about a video for the doc or something else?

[contactashish13]

@sab2ina the only way to create charts for real life scenarios is to use the plugin on known databases - maybe themeisle?

[sab2ina]

@contactashish13 I think we can talk to @selul to give you access?

@dascalescuro cool, the timed video should be okay for the doc https://docs.themeisle.com/article/970-visualizer-sample-queries-to-generate-charts

[selul]

@contactashish13 @sab2ina I can not do that.

• For woocommerce, we can import the default sample data and do some payments.
• For edd this sample xml and do some sample payments.
• For core the unit test data

If none of you has time to build the sample data we can look over Fiverr and ask someone to do that.

[contactashish13]

@sab2ina I think we need outside help to generate this data for us.