Tracking accounts and contracts with Cloud Functions & Big Query

Millions of African households are still living off-grid. Living without access to electricity usually means families have to spend income on candles, dirty and expensive kerosene lighting and expensive batteries for flashlights. To make electricity more accessible to individuals, off-grid solar companies offer solar home system (SHS) to off-grid households on a pay as you go basis (PAYG). PAYG SHS’s allow customers to pay in affordable instalments (~30–50 cents a day they would spend on poor kerosene alternative). SHS includes: solar panel, battery, 2-5 modern LED lights, radio, an optional TV and allows users to pay with mobile money. SHS can be turned off automatically if payments are overdue. When a payment plan is complete and no further payments are due the customer owns the equipment and can benefit from free electricity. PAYG SHS’s providers currently require hundreds of millions of dollars in working capital financing. Solar Frontier Capital (SFC) is a dedicated impact investment company, looking to change lives by providing working capital to SHS’s providers acquiring existing and future SHS receivables.

Overview and Challenge

African Frontier Capital (AFC) sponsors the installation of SHS. These SHS are signed up on a contract by vendors that are on the ground installing the SHS. These vendors send through contract files for AFC to evaluate and select contracts that will be sponsored.

The project objectives were to automate the process of ingesting the vendors files, provision is made for contract, payment, mobile and adjustment files. Then using this data to create calculations on the contracts, their value, and payment history.

Our Solution

These files are uploaded into Google Cloud Storage by the vendor. The files are processed using a Google Cloud Functions and inserted into Big Query. The data is then analysed using Big Query.

The Result

Using this solution AFC is able to easily select files that will be sponsored as well as track the contracts they are already sponsoring. Doing such a process in Excel is time consuming and prone to errors. Automating this process into scheduled queries and views ensures that the data is readily available in various pivot tables, either on Google sheets or Microsoft Excel, and that the formulae are uniformly applied, thereby achieving the objectives of automation, speed and accuracy.