Question

Best way to replace table calculations for month over month retention/results

  • 11 October 2021
  • 0 replies
  • 10 views

I need to track 3 data points month over month that all have to do with the volume of partners on our delivery platform. Right now I’m doing this with a table Look that has one row for each partner and a pivot set up by calendar month so that if a partner delivered in that month, there’s a 1 in the ‘Active’ column for that month. Then I use table calcs to return a 1 or 0 for each of the 3 data points. Data point descriptions and their respective table calcs below:



New - a partner that starts delivering in a given month after not doing so for 30 days (so if a partner last delivered 6 months ago and ago started up again tomorrow, they would be counted as ‘New’)

if(is_null(pivot_offset(${active},-1))
AND ${active} = 1,
1,0
)

Retained - a partner that delivered in a given month AND the previous calendar month

if(
pivot_offset(${active},-1)=1
AND
${active}=1,
1,0
)

Churned - a partner that did not deliver in a given month, but did deliver in the previous month

if(
pivot_offset(${active},-1)=1
AND
is_null(${active}),
1,0
)

 

Then I just use a sum table calc to get the total each month in New, Retained, Churned. 

 

My question: what’s the best way to do these calculations inside a View file, instead of table calculations? Doing it via table calc serverely limits our visualization options, as well as makes it hard to drill into the data further. 

I have a derived table with a column for deliveryid, partnerid, and deliverydate. What I can’t seem to figure out is how to create dimensions/measures within the view file that let me show these 3 data points month over month. I used a combination of CASE/WHEN and DATEDIFF to get it working on a rolling 30 day basis, but I can’t figure how to make it work for calendar month over month. 

We’re using Amazon redshift for our DBMS if that helps. 

 

 

 

 


0 replies

Be the first to reply!

Reply