How to Calculate ROI for Date Range in MySQL

database-designMySQL

I have a SELECT with calculated columns and Im doing some filtering on it.

The issue Im having with ROI where Im calculating average for the date range, which is incorrect as ROI needs to be calculated from actual revenue and costs (ie. ROI = (revenue-costs/costs)*100 )

So the question is, how I can calculate the correct ROI?

SELECT 
                 c.campaign_name, 
                 c.traffic_source_name, 
                 cvr.custom_variable, 
                 SUM(cvr.visits) as visits, 
                 SUM(cvr.clicks) as clicks, 
                 AVG(cvr.ctr) as ctr, 
                 SUM(cvr.conversions) as conversions, 
                 SUM(cvr.cost) as cost, 
                 SUM(cvr.revenue) as revenue, 
                 SUM(cvr.profit) as profit, 
                 ROUND(AVG(cvr.roi), 2) as roi,
                 AVG(cvr.ap) as ap
                 FROM custom_variable_report as cvr
                 INNER JOIN campaigns as c
                 ON c.campaign_id = cvr.campaign_id AND c.date = cvr.date
                 WHERE cvr.campaign_id = 'b881eb19-8a47-4a60-9373-b0981006733a' 
                 AND (cvr.date BETWEEN '2017-03-03' AND '2017-03-09')
                 GROUP BY cvr.custom_variable
                 HAVING ABS(profit) > 2.51*1
                 AND roi < -25
                 ORDER BY cvr.profit ASC

In this case if I have 2 data points: -20% and 100% the average is: 40% which is incorrect as ROI needs to be reflected from the revenue and costs instead for each row.

Best Answer

Unless I'm missing something, wouldn't it be:

ROUND((SUM(revenue) - SUM(cost)) * 100.0 / SUM(cost), 2) as roi

Related Solutions

Postgresql – Joining based on date (or date range)

That is going to be horribly messy, since you'll need to find the single (staffid, effectivefrom) pair that's the newest prior to the dateParameter.

What I'd do is add another field effectiveTo to go with effectiveFrom, and then when you add a new target for a staff member, before you add it check and see if there are any current targets (effectiveTo IS NULL) and if there are then set their end date to the new target start date (optionally: throw an error if there's more than one current target).

So, the table turns out looking like:

 staffID | target | effectiveFrom | effectiveTo
 --------|--------|---------------|-------------
  1      |  6.0   | 2012-01-01    | 2012-03-01
  2      |  6.0   | 2012-01-01    | NULL
  3      |  6.0   | 2012-01-01    | NULL
  1      |  7.0   | 2012-03-01    | NULL

That makes your search query look like this:

SELECT  othertable.*, targets.target
FROM    othertable
            INNER JOIN 
        targets ON othertable.staffid = targets.staffid
WHERE dateParameter > effectiveFrom AND (dateParameter <= effectiveTo OR effectiveTo IS NULL)

The potential downside of that is data inconsistency (i.e. staff members having overlapping targets) - you can fairly easily write a script to check that though. I'd recommend making an addTarget(staffID, target, fromDate) stored procedure and wrapping all the checking/updating logic in that, also.

(A couple of style notes - I rewrote your query slightly to seperate the join condition from the where clause. Also, I know it's only an example, but SELECT table.* is a bad habit to get into - you land up transferring far more data than you need.)

Design database to query a date range for scheduling

...if I put fields like begin_time, end_time in every row, then when I do the query the database engine has to walk all rows, for example with a consult like

If you create an index on begin_time and an index on end_time, your dbms should use the indexes to avoid a full table scan. I just tried that with PostgreSQL and a table of 50k rows. It used the index on begin_time, and returned in 0.02ms. A multi-column index (an single index on both begin_time and end_time) might perform even better.

You might want to update a column in those rows to make sure the notification (or whatever) doesn't happen every minute between begin_time and end_time.

Related Question