New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Ecommerce Life Time Value metrics + New Ecommerce section in Visitor Profile #11294
Conversation
+ new fields returned in Live APIs: ecommerceLifeTimeValue, ecommerceLifeTimeOrdersCount
…nstead of just last 100 visits
COALESCE(SUM(" . LogAggregator::getSqlRevenue('items') . "), 0) as lifeTimeEcommerceItems | ||
FROM " . Common::prefixTable('log_visit') . " AS log_visit | ||
LEFT JOIN " . Common::prefixTable('log_visit') . " AS log_visit_visitors | ||
ON (log_visit.idsite = log_visit_visitors.idsite AND log_visit.idvisitor = log_visit_visitors.idvisitor) |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
wouldn't we safe one join when matching by idvisitor directly? do we not have the idVisitor in Live API?
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
Good point, will do this
private function getSqlEcommerceConversionsLifeTimeMetricsForIdGoal($ecommerceIdGoal) | ||
{ | ||
$sql = "SELECT | ||
COALESCE(SUM(" . LogAggregator::getSqlRevenue('revenue') . "), 0) as lifeTimeRevenue, |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
Wondering why we need COALESCE ?
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
SUM
returns NULL
when there are not matching rows (ie. no ecommerce conversions)
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
sure, usually just doing it in PHP as usually better readable
All Ecommerce metrics shown in the Visitor Profile now apply to "Life time" (before they used to apply to last 100 visits only).
Need to fix system test before merging
Fixes #2750