weekly revenue e-mails

[smirolo]

Create a manage.py command that can be run on a weekly basis to send an e-mail report of the revenue for the past week (Same numbers as shown on the Revenue table, i.e. "Total Sales", "New Sales", "Churned Sales", "Payments", "Refunds"). Furthermore, show the percentage increase/decrease vs. the past week and the same week a year ago.

[knivets]

Which organization the report should be generated for? Any guidelines on the UI?

[smirolo]

By default, one report per Organization where is_provider == True and a --provider flag on the command line to pick a specific one only. Sending charts in e-mails will be a bit complicated, most likely requiring to pre-generate images. Let's have some a sober table with correct numbers and percentages for now. Maybe something like:

This week's numbers:

Descr	Total in usd	last week	same week a year ago
Total Sales	$X	+2%	+12%
New Sales	$X	+2%	+12%
Churned Sales	$X	+2%	+12%
Payments	$X	+2%	+12%
Refunds	$X	+2%	+12%

[knivets]

Not sure how to calculate the numbers exactly. For example could you give me a formula to calculate these, in a format like this total_in_usd = sum(transactions from monday_of_prev_week by monday this week)? I'm mostly confused about which dates to use.

[smirolo]

Let's assume start of week is Sunday 0:00 and end of week is Saturday 23:59:59.999. From the current/specified date (i.e. datetime_or_now), find the previous Sunday (see Calculate a Week Range for a Date) then use python-dateutil relativedelta to derive other ranges.

last_week = [last_sunday - relativedelta(weeks=1), last_sunday]
week_before = [last_week[0] - relativedelta(weeks=1), last_week[0]]
week_last_year =  [last_sunday - relativedelta(years=1, weeks=1), last_sunday - relativedelta(years=1)]

The code from the API call RevenueMetricAPIView returns the trailing 12 months Total, New and Churned Sales (through aggregate_monthly_transactions(...), Payments (through payment_amounts = aggregate_monthly(...) and Refunds (through refund_amounts = aggregate_monthly(...).

The implementation pattern for those functions is:

def aggregate_monthly(organization, account, from_date=None, tz=None, orig='orig', dest='dest', **kwargs):
    dates = convert_dates_to_utc(month_periods(13, from_date, tz=tz))
    period_start = dates[1]
    for period_end in dates[2:]:
        ... compute monthly aggregate ...

It seems the best way to implement this feature is thus to replace from_date and tz in the function argument list by a period_dates argument. The code would then look like:

def aggregate_monthly(organization, account, period_dates, orig='orig', dest='dest', **kwargs):
    period_start = period_dates[1]
    for period_end in period_dates[2:]:
        ... compute monthly aggregate ...

And call sites would look like (example):

 _, payment_amounts = aggregate_monthly(self.provider, Transaction.RECEIVABLE,
        orig='dest', dest='dest', 
        orig_account=Transaction.BACKLOG, orig_organization=self.provider,
       period_dates=convert_dates_to_utc(month_periods(13, self.ends_at, tz=self.timezone)))

With these changes, to compute weekly revenue is a matter of computing period_dates as a pair of dates (start_of_week, end_of_week) and copy/pasting the aggregate calls in RevenueMetricAPIView.

[smirolo]

merges #122 for this feature and commit 910d6129 for last fixes on implementing this feature. closing.