Problem statement.

Stats for Target Direct billing shows lower values than expected.

Maartje says :- "We saw that the billing for retro games with target was lower than expected. No real changes were made die target so far right? So it might be in their side"

Further on Maartje gave more examples on what the she ment by "Lower than expected" with the below examples.

So if we look at the two thursdays in the weeks before
We see job payout and billed members are:
4 jan 496 members euro 1948 
11 jan 507 members euro 1990 
18 jan 485 members euro 1291

So the 4th is fine, the 11th also fine but for the 18th we see 485 members billed which is - 22 members
that is also ok but the job payout goes from 1990€ to 1291€ minus 700€ that seems a lot for just 22 members less

Steps that I took to see what was wrong.

• My first assumption was that a lot of users had terminated the contracts and thus we were seeing this steep drop in Payout. So checked some contracts and saw that most of them did indeed stay and had not terminated. (So this was not actually the problem: rulled-out)

• Checked the code and billing histories to see if the Partial Payments were again failing. (Checking the contracts that were billing after 11th January and saw that most of them indeed had processed the payments)

• While checking one of such contracts We found that the Payout was wrongly calculated. The payout was being calculated based on the first piece of the PartialPayment Ex: If the payment had 3 peices like so

    { trxid: '20822569', payment_id: 123456, total_pieces: 3, pid: 123456, piece: 1, price_in_cents: 450, status: 'open' }
    { trxid: '20822569', payment_id: 123456, total_pieces: 3, pid: 123457, piece: 2, price_in_cents: 200, status: 'open' }
    { trxid: '20822569', payment_id: 123456, total_pieces: 3, pid: 123458, piece: 3, price_in_cents: 99, status:  'open' }
  
    { trxid: '20822569', payment_id: 123456, total_pieces: 3, pid: 123456, piece: 1, price_in_cents: 450, status: 'ok' }
    { trxid: '20822569', payment_id: 123456, total_pieces: 3, pid: 123457, piece: 1, price_in_cents: 200, status: 'ok' }
    { trxid: '20822569', payment_id: 123456, total_pieces: 3, pid: 123458, piece: 1, price_in_cents: 99,  status: 'ok' }
  

  So the Payout was being calcuated on the First Piece of the Paymet and not on the Sum Total of the Billing

  Ex:

  If PayoutRate = 59.64
  Actual Payout = 7.49 * 59.64 / 100 => 4.44 Euros. 
  Calcuated Payout = 4.50 * 59.64 / 100 => 2.68 Euros.
  

  select c.id as contract_id, bh.payout_amount_in_euro_cents as payout, bh.amount_in_euro_cents as amount_billed, bh.status
  from contracts as c
  join billing_histories as bh
  on c.id=bh.contract_id
  where c.country_id='31' and c.created_at >= '2018-01-01'
  and bh.amount_in_euro_cents = 749
  and bh.payout_amount_in_euro_cents < 396
  and bh.status = 'ok'
  

  Yields 351 contracts all with status OK and a oneoff payout with €2.38 _most likely this is the most recent biiling_history so For the price difference that Maartje was mentioning about.

  Actual Payout that should have happened for these contracts were €3.96 so the difference is €1.58 per Billing.

  If the correction is made in the database then the Payout we got would increase by €554.58. From the stats screen that Maartje was refering to had a difference of €640. When the change is applied on production the difference amount will come down to about €85, which I think is a sane amount where users either don't have Credit or have been barred.

  Then the payout was being calcuated based on the first peice 450 * PayoutRate / 100 but it should have been on the total sum of the prices 745 * PayoutRate / 100

  (I updated the code to handle this and also updated the database to reflect the proper values [Only For T-Mobile])

• Maartje still reports that the stats are a little off for a couple of days specifically the 17th and 18th of this month (January 2018).

  For this I see that the number of signups for NlRetroGamesWire was 2 and 1 respectively. While the number of signups for NlRetroGamesWire between 10th and 11th one week before were 42 and 28 Thus the difference or loss in revenue or Payouts is what I feel. Do you also see the same numbers ?

  Also the price drop shown in the stats might also have to do with the fact that we chagned the price point some time last week mostly on the 15th or 16th. So contracts that are created post this change will have only a 5 Euro price point and a payout of €2,76 as compared to the earlier payout of €3.96 I think the number of signups along with this price point change is what is causing the values to show a lower amount.

  Futher inspection of Billing Histories created for this year for NL I see the following.

  count, amount_in_euro_cents
  362,35
  3,99
  151,110
  25180,150
  4,200
  10,250
  10,299
  16,300
  27,450
  12228,500
  23,549
  225,600
  20,650
  1763,745
  25419,749
  

  At this point I realised that the payout has to be corrected for all other operators as well. And also we have to fix the PartialPayments that had not been processed correctly in the past to get the stats on track.

  So I queried all BillingHistories that had a mismatch in them with the following query.

select count(*) from billing_histories as bh
  join contracts as c
    on c.id=bh.contract_id
  join payment_provider_config_profile_target_direct_billing as cp
      on cp.id=c.payment_provider_config_profile_id
  where c.country_id ='31'
  and bh.created_at >= '2018-01-01'
  and bh.status='ok'
  and bh.amount_in_euro_cents != cp.billing_cycle_price_in_cents

which yields 44764

Questions.

1. Will the stats be set right if I change all partial payments to reflect the correct value and also update the Payout for Other operators like I did so with T-mobile ?
2. Is the above query correct to get all billings that have a partial Payments ?
3. Any thing else that you can think of that can help bring back the stats to a correct state ?