Monthly Report for CP67

Overview

This document provides information about Monthly Report for CP67.

Every month we deliver a report of transactions to CP67 where they can get the following information:

• Number of users that registered to the store
• Number of users that paid a subscription or a single publication
• Number of users that cancelled a plan

The report is delivered as a shared Google Sheets

The following query has been used to generate the report:

SELECT
 --  `users_plans`.*,
 `users_plans`.id AS users_plans_id,
 `users_plans`.amount AS users_plans_amount,
 `users_plans`.currency_id AS users_plans_currency_id,
 `users_plans`.status AS users_plans_status,
 `users_plans`.valid_from AS users_plans_valid_from,
 `users_plans`.valid_to AS users_plans_valid_to,
 `users_plans`.created_at AS users_plans_created_at,
 `users_plans`.updated_at AS users_plans_updated_at,
 ':',
 `payments`.*
FROM
 `farfalla`.`payments`
 JOIN users_plans ON users_plans.id = payments.user_plan_id
WHERE (`user_plan_id` IN(
  SELECT
   id FROM `farfalla`.`users_plans`
  WHERE (`created_at` >= '2019-12-01'
   AND `created_at` <= '2021-02-28')
  and(`valid_to` IS NULL
   OR `valid_to` <= '2021-02-28')
  AND(`status` <> 'pending')
  AND(`gateway` IN("stripe", "mercadopago"))
  AND(`plan_type` = 'recurring')
  AND(`sandbox` = '0')
  AND(`tenant_id` = '26')))
AND(`payments`.`plan_type` = 'recurring')
AND(`payments`.`tenant_id` = '26')
ORDER BY
 `payments`.`email`, `payments`.`payment_date`;

---