Hi
I want to calculate the conversion rate for an onlineshop.
The data is held in two tables.
Upon my konwledge the conversionrate calculates like:
(unique visits per day / 1000) * orders per day
I tried something like this:
SELECT
datum,
((sum(s_statistics_visitors.uniquevisits)) / 1000) * (
SELECT
count(ordernumber)
FROM
s_order
WHERE
ordernumber != 0
AND s_order.subshopID = 1) AS uniquevisits
FROM
s_order
JOIN s_statistics_visitors ON DATE(s_order.ordertime) = s_statistics_visitors.datum
AND s_statistics_visitors.shopID = s_order.subshopID
WHERE
s_statistics_visitors.shopID = 1
GROUP BY
datum
I doesnt return the right conversion Rate because the subquery returns orders overall and not the orders per day.
Has someone in the forum done something similar and might have an answer.
Thanks in advance.