First we need to change the date format, convert the payouts from cents to thousands dollars and introduce a new variable weekday.

Next we merge the payouts table with the countries table to get the country of each merchant. Since only 1% of payouts don't have country associated with them, we can drop these rows.

We can clearly see below that Tuesday (weekday=1) has by far the highest payout compared to other days. Since we're required to estimate the payouts on Jan. 1, 2019 and this date falls on Tuesday, we would want to make sure we're accounting for that in our prediction.

For simplicity, we can assume that each Tuesday is similar in terms of payouts and therefore we can calculate the total payouts on all Tuesdays of year 2018 by country and divide these numbers by 52 to find an average payout amount on a typical Tuesday.

On average, ~5.2 million dollars are being paid out on each Tuesday to the US constituting over 92% of all payouts on each Tuesday to all countries. All the amounts paid out on Jan.1, 2019 by country can be seen in the 4th column.

To better forecast payouts made to each country on Jan. 1,2019, we can build a Time-Series model (ex. SARIMA) which takes care of trend and seasonality, but it require more time than the time given for this project (6 hours).

Below we can see the positive trend in the number of transactions on the platforms, which is a good indicator. The total weekly payout amounts are also positively trending with a peak in July, which may indicate a seasonality happening in July. However, we need more data for other years to confirm that.

When we look at the weekly average payout amount per transaction, we can see a slight negative trend, meaning that that customers, on average, are spending less money per transaction over time.


We can see that there are 4 merchants that were assigned to industries that appear only once in the data and that may be due to manual error. We're assigning Financial Services and Real Estate to Professional Services (non-medical), internet software & services to Other Software & Content and hotels, restaurants & leisure to Travel & Hospitality industry.

We can see a daily breakdown of payouts made by Education platforms, Food & Beverage platforms and by Travel & Hospitality platforms. Platforms from Education and Travel & Hospitality industries have the highest total payout on Tuesdays, while platforms from the Food & Beverage industry have the highest total payout on Wednesdays in 2018.

In the breakdown above we can see that currently we have 15 platforms in the Food & Beverage industry, 5 in Education industry and 13 in Travel and Hospitality industry.

Since we're expecting an increase in the number of platforms in the Education and Food & Beverage industries and a decrease in the Travel & Hospitality industry, we need to multiply the total payouts by respective coefficients.

After getting the expected average payouts on each typical day of the week, we can calculate the total expected payout by each relevant industry in 2019.

We can see that platforms from the Food & Beverage industry are expected to have the highest payouts (112479.5 thousands) in 2019, while Travel & Hospitality platforms the least payouts.