Background of the Project

The Door-to-Door Sampling Caravan Project aims to visit 100 barangays and give out free Globe prepaid SIM cards to households in these barangays in 100 days.

The goal is to increase the subscriber base of the company and increase revenue flow from the activated SIM to achieve more than 15% Return of Investment by the end of the year.

To achieve these goals we are to utilize the following resources:

1. PhP5,000,000 contract
2. 10 samplers, hired at Minimum Wage per day (8 hours)
3. 1 van & 1 driver for travel
4. 100 days to visit 100 barangays
5. SIM cards worth PhP40 each, with PhP50 load

hit = 20 seconds waiting time + 20 secends for spiel + 10 seconds when signing 
hit = 50 seconds per interaction

no hit = 30 seconds waiting time

interaction but no hit = 20 seconds waiting time + 20 seconds for spiel 
interaction but no hit = 40 seconds

h2h <- read.csv("h2h.csv")
knitr::kable(h2h[1:6, 1:13], caption = 'h2h.csv Dataset'
)

attributes(h2h)$names

##  [1] "Island"            "Major_Island"      "Region"           
##  [4] "Province"          "ProvinceCity"      "CityMunicipcality"
##  [7] "Barangay"          "TotalPopulation"   "NHouseholds"      
## [10] "AreaBarangay"      "lat"               "long"             
## [13] "Size"

library(geosphere)
h2h$TGT_lat <- 14.55369457
h2h$TGT_long <- 121.0500166
h2h$Distance <- 0

for (x in 1:nrow(h2h)) {
  h2h$Distance[x] <- distm(c(h2h$long[x], h2h$lat[x]), c(h2h$TGT_long[x], h2h$TGT_lat[x]), fun = distHaversine)
}

h2h$Distance <- h2h$Distance/1000
sorted_h2h <- h2h[order(h2h$Distance),]
row.names(sorted_h2h) <- NULL

knitr::kable(sorted_h2h[1:6, 1:16], caption = 'Sorted dataset based on Distance'
)

Barangays to be visited to maximize hits per day

When choosing the barangays that will be visited within the 100 days, the following criteria were applied to narrow down the list:

1. 75% of the number of households must be greater than 5760

Potential Hits per 10 samplers = ( 8 hours X 10 samplers ) / 50 seconds = 5760 hits (maximum hits per day)

2. Projected Hit is greater than 75% of the number of households

3. Distance of the barangay from TGT must be less than 60 km.

4. Those nearest to TGT in terms of distance are considered first.

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

data_hh <- select (sorted_h2h, c(Region, CityMunicipcality, Barangay, NHouseholds, Size, Distance, AreaBarangay))
data_hh <- mutate (data_hh, Hit = NHouseholds*0.75)
data_hh <- mutate (data_hh, TravelTimeHr = 2*Distance/60)
data_hh <- mutate (data_hh, TravelTimeSec = TravelTimeHr*3600)
data_hh <- mutate (data_hh, LaborTime = 28800-TravelTimeSec)
data_hh <- mutate (data_hh, AccHit = floor(10*LaborTime/50))
data_hh <- filter(data_hh, (Hit >= 5760))
data_hh <- filter (data_hh, Hit >= AccHit)
data_top <- head(data_hh, 100)
knitr::kable(data_top[1:10, 1:11], caption = '100 Barangays to visit to maximize hits. For the full list, see Miscellaneous.'
)

All of the Barangays that met the criteria are Large in size. On average, the household density of these barangays is equal to 19101.28.

AVE Household Density = AVE( Number of Household / Area of Barangay )

mean(data_top$NHouseholds / data_top$AreaBarangay)

## [1] 19101.28

library(ggplot2)
knitr::kable(data_top %>% count(CityMunicipcality), caption = 'Number of Choosen Barangays per City'
)

ggplot(data_top) +
  geom_bar(aes(y = CityMunicipcality), fill = 'purple')

library(ggplot2)
knitr::kable(data_top %>% count(Region), caption = 'Tally per Region'
)

ggplot(data_top) +
  geom_bar(aes(x = Region), fill = 'purple')

Realistic Hits for 100 days of Sampling

Get the summation of the AccHit column in data_top to compute the total actual hits.

total_acchits <- sum(data_top$AccHit)
total_acchits

## [1] 550010

Geographic breakdown of hits

Sampler Profile

A work day is equal to 8 hours, with the daily minimum wage equal to PhP537, we compute the hourly breakdown of utilization of samplers as,

Hourly breakdown/utilization of samplers = P537 per day / 8 hours
Hourly breakdown/utilization of samplers = P67.13 per hour 

Considering the hourly breakdown of utilization of samplers divided by 50 seconds which is the total time per hit interaction we get the Interaction with Potential Hit as,

Interaction with Potential Hit = P67.13 per hour / 50 sec per interaction 
Interaction with Potential Hit = P0.93 per interaction

The total cost of renting the van for 100 days is computed as,

Travel by van = P10,000 per day
Travel by van = P100,000 for 100 days

Assume that Sampler walks 4 km/hr, the cost for walking is,

Walking = ( P537 / 8 hr ) X ( 1 hr / 4km ) 
Walking = P16.78 / km

What about the other barangays?

Provincial Barangays

The feasibility to include provincial barangays was concluded using the following computation, which explains the values in the graph above.

Potential Hits per 10 samplers = ( ( Labor Time - Travel Time in Hours )  X 10 samplers ) / 50 sec

Considering this projection using the same resources, it is advisable to not pursue reaching out to provincial barangays. Aside from the time consuming travel, the potential hit decreases the further the location of the barangay.

We recommend to acquire samplers already residing in the locations to be able to make the caravan more feasible to pronvicial barangays.

Target Market Profile

Return of Investment

Below states two options in the execution of this project. Outsourced uses the services of the marketing agency while the other scenario, Not Outsourced, uses internal efforts from Globe Telecom.

Percentage Sensitivities

From 100-day expense of P 23,538,600.00…

If conversion rate (25%) is constant, minimum success rate to earn income is 53%.

If success rate (75%) is constant, minimum conversion rate to earn income is 21.4%.

Miscellaneous

brgy_100 <- select (data_top, c(Region, CityMunicipcality, Barangay))
knitr::kable(brgy_100, caption = 'List of 100 Barangays that met the criteria.'
)