def build_markdown(self, start_row):
rows = rows_until_empty(self.workbook,
start_row=start_row,
test_location="Overview!B")
# CONSIDER it would be nice to have an analogue of Django ORM's values(flat=True)
text_dicts = self.schema_list({"text": StrCell(loc("Overview!B"))},
locations=rows)
texts = [text_dict["text"] for text_dict in text_dicts]
return "\n".join(
texts) + "\n" # Reasonable people demand trailing newlines.
def update_segment_details(self, segment):
_, _, row = find(f"age segment: {segment['age_group']}")(self.workbook)
cols = list(
cols_until(
self.workbook,
matchp(exact="All"),
"B",
test_sheet="Output",
test_row=row,
)
)
def _find(predicate):
return find_row("Output!A", predicate, start_row=row)
schema = {
"income": CurrencyCell(_find("age segment")),
"group_population": IntCell(_find("population")),
"home_owners": {
"total": IntCell(_find("home owners")),
"family": IntCell(_find("family ho")),
"nonfamily": IntCell(_find("non-family ho")),
},
"renters": {
"total": IntCell(_find("renters")),
"family": IntCell(_find("family r")),
"nonfamily": IntCell(_find("non-family r")),
},
"market_size": IntCell(_find("est. market")),
"active_populations": ["renters.nonfamily", "renters.family"],
}
segment["income_groups"] = self.schema_list(
schema, locations=cols, sheet="Output"
)
segment["segment_population"] = self.schema(
IntCell(loc(sheet="Output", row=next_row(row), col=next_col(cols[-1])))
)
class CampaignPlanImporter(ProjectExcelImporter):
expected_type = "campaign"
expected_version = 1
AUDIENCE_CHOICES = ["Acquisition", "Retention"]
STATUS_CHOICES = ["Not Started", "In Progress", "Complete"]
COST_TYPE_CHOICES = ["One-Time", "Monthly", "Weekly"]
META_COL_SCHEMA = {
"campaign_months": IntCell(find_meta("months")),
"campaign_weeks": IntCell(find_meta("weeks")),
"campaign_days": IntCell(find_meta("days")),
}
CATEGORY_SCHEMA = {
"name": StrCell(find_cat("tactic")),
"audience": NullChoiceCell(find_cat("audience"),
choices=AUDIENCE_CHOICES),
"tooltip": NullStrCell(find_cat("tooltip")),
"schedule": NullStrDateCell(find_cat("schedule")),
"status": ChoiceCell(find_cat("status"), choices=STATUS_CHOICES),
"notes": NullStrCell(find_cat("notes")),
"base_cost": DefaultCurrencyCell(find_cat(matchp(iexact="cost"))),
"cost_type": ChoiceCell(find_cat("cost type"),
choices=COST_TYPE_CHOICES),
"total_cost": DefaultCurrencyCell(find_cat("total cost")),
}
FUNNEL_CATEGORY_SCHEMA = dict(
CATEGORY_SCHEMA,
**{
"volumes": {
"usv": IntCell(find_cat("# of usv")),
"inq": IntCell(find_cat("# of inq")),
},
"costs": {
"usv": DefaultCurrencyCell(find_cat("usv cost")),
"inq": DefaultCurrencyCell(find_cat("inq cost")),
},
},
)
OVERVIEW_TARGET_SEGMENT_SCHEMA = {
"ordinal": StrCell(loc("A")),
"description": StrCell(loc("B")),
}
OVERVIEW_TARGET_INVESMENT_SCHEMA = {
"category": StrCell(loc("A")),
"total": DefaultCurrencyCell(loc("B")),
"acquisition": DefaultCurrencyCell(loc("C")),
"retention": DefaultCurrencyCell(loc("D")),
}
CATEGORY_TO_KEY = {
"Reputation Building": "reputation_building",
"Demand Creation": "demand_creation",
"Leasing Enablement": "leasing_enablement",
"Market Intelligence": "market_intelligence",
"Total": "total",
}
def build_category(self, category):
rows = rows_until_empty(self.workbook,
start_row=2,
test_sheet=category,
test_col="A")
tactics = self.schema_list(schema=self.CATEGORY_SCHEMA,
locations=rows,
sheet=category)
return {"tactics": tactics}
def build_funnel_category(self, category):
rows = rows_until_empty(self.workbook,
start_row=2,
test_sheet=category,
test_col="A")
tactics = self.schema_list(schema=self.FUNNEL_CATEGORY_SCHEMA,
locations=rows,
sheet=category)
return {"tactics": tactics}
def locate_overview_header_cell(self, predicate):
predicate = predicate if callable(predicate) else matchp(
iexact=predicate)
return find_row("Overview!A", predicate)(self.workbook)
def build_markdown(self, start_row):
rows = rows_until_empty(self.workbook,
start_row=start_row,
test_location="Overview!B")
# CONSIDER it would be nice to have an analogue of Django ORM's values(flat=True)
text_dicts = self.schema_list({"text": StrCell(loc("Overview!B"))},
locations=rows)
texts = [text_dict["text"] for text_dict in text_dicts]
return "\n".join(
texts) + "\n" # Reasonable people demand trailing newlines.
def build_markdown_for_header(self, predicate):
_, _, row = self.locate_overview_header_cell(predicate)
return self.build_markdown(start_row=row)
def build_overview_target_segments(self):
_, _, row = self.locate_overview_header_cell("target segments")
rows = rows_until_empty(self.workbook,
start_row=row + 1,
test_location="Overview!A")
return self.schema_list(self.OVERVIEW_TARGET_SEGMENT_SCHEMA,
locations=rows,
sheet="Overview")
def build_overview_objective(self, category):
# This will find the first occurence of the header, which given
# our current schema (with target investment headers at the very bottom
# of column A) should work fine.
return {
"title": category,
"description": self.build_markdown_for_header(category),
}
def build_overview_objectives(self):
return [
self.build_overview_objective("Reputation Building"),
self.build_overview_objective("Demand Creation"),
self.build_overview_objective("Leasing Enablement"),
self.build_overview_objective("Marketing Intelligence"),
]
def build_overview_assumptions(self):
return self.build_markdown_for_header("assumptions")
def build_overview_target_investments(self):
# Find "Total" row and work backwards
_, _, row = self.locate_overview_header_cell("total")
rows = rows_until_empty(self.workbook,
start_row=row,
test_location="Overview!A",
next_fn=prev_row)
items = self.schema_list(self.OVERVIEW_TARGET_INVESMENT_SCHEMA,
locations=rows,
sheet="Overview")
# Convert dictionaries with the category key *inside* them
# into nested dictionaries where the category key is *outside*.
# aka {"category": "Reputation Building", "total": "100"} -->
# {"reputation_building": {"total": "100"}}
return dict((self.CATEGORY_TO_KEY[item.pop("category")], item)
for item in items)
def build_overview(self):
def overview_str(predicate):
return self.schema(StrCell(find_overview(predicate)))
return {
"theme": overview_str("theme"),
"target_segments": self.build_overview_target_segments(),
"goal": overview_str("goal"),
"objectives": self.build_overview_objectives(),
"assumptions": self.build_overview_assumptions(),
"schedule": overview_str("schedule"),
"target_investments": self.build_overview_target_investments(),
}
def build_meta(self):
return self.schema(schema=self.META_COL_SCHEMA, col="B")
def clean(self, ctx):
super().clean(ctx)
# Build the meta table
self.cleaned_data["meta"] = self.build_meta()
# Build for each category
self.cleaned_data["reputation_building"] = self.build_category(
"Reputation Building")
self.cleaned_data["demand_creation"] = self.build_funnel_category(
"Demand Creation")
self.cleaned_data["leasing_enablement"] = self.build_category(
"Leasing Enablement")
self.cleaned_data["market_intelligence"] = self.build_category(
"Market Intelligence")
# Build the overview
self.cleaned_data["overview"] = self.build_overview()
def RTI_SCHEMA(category):
return {
"name": category,
"low": FloatCell(loc("B")),
"high": FloatCell(loc("C")),
}
def POPULATION_ZIP_SCHEMA(row):
return {
"zip": StrCell(loc(sheet="Output", row=row)),
"outline": None,
"properties": None,
}
class MarketImporter(ProjectExcelImporter):
expected_type = "tam" # TODO rationalize our naming here.
expected_version = 1
RENT_TO_INCOME_CATEGORIES = [
"Low",
"Moderately Low",
"Target",
"Moderately High",
"High",
]
SEGMENT_OVERVIEW_SCHEMA = {
"age_group": StrCell(loc("Output!A")),
"market_size": IntCell(loc("Output!B")),
"usv": IntCell(loc("Output!C")),
"growth": FloatCell(loc("Output!D")),
"future_size": IntCell(loc("Output!E")),
}
POPULATION_RADIUS_SCHEMA = {
"center": {
"type": "Point",
"coordinates": [
FloatCell(find("coordinates", "C")),
FloatCell(find("coordinates", "B")),
],
},
"radius": FloatCell(find("tam type", "C")),
"units": ChoiceCell(find("tam type", "D"), choices=["mi", "km"]),
}
@staticmethod
def POPULATION_ZIP_SCHEMA(row):
return {
"zip": StrCell(loc(sheet="Output", row=row)),
"outline": None,
"properties": None,
}
@staticmethod
def RTI_SCHEMA(category):
return {
"name": category,
"low": FloatCell(loc("B")),
"high": FloatCell(loc("C")),
}
TOTAL_SCHEMA = {
"segment_population": IntCell(find("est. population")),
"market_size": IntCell(find("total market size")),
"usv": IntCell(find("total usv")),
"future_size": IntCell(find("future population size")),
}
AVERAGE_SCHEMA = {
"age": IntCell(find("total average age")),
"growth": FloatCell(find("total average growth")),
}
def get_location(self):
return self.schema(StrCell(find("city, state")))
def get_estimated_population_radius(self):
return self.schema(self.POPULATION_RADIUS_SCHEMA)
def get_estimated_population_zip(self):
_, _, row = find("tam type")(self.workbook)
cols = cols_until_empty(self.workbook, "C", test_sheet="Output", test_row=row)
zip_codes = self.schema_list(
schema=self.POPULATION_ZIP_SCHEMA(row), locations=cols
)
return {"zip_codes": zip_codes}
def get_estimated_population(self):
tam_type = self.schema(
ChoiceCell(find("tam type"), choices=["radius", "zipcodes"])
)
if tam_type == "radius":
result = self.get_estimated_population_radius()
else:
result = self.get_estimated_population_zip()
result["population"] = self.schema(IntCell(find("est. population")))
return result
def get_rent_to_income_category(self, category):
_, _, row = find(matchp(exact=category))(self.workbook)
return self.schema(schema=self.RTI_SCHEMA(category), sheet="Output", row=row)
def get_rent_to_income_categories(self):
return [
self.get_rent_to_income_category(category)
for category in self.RENT_TO_INCOME_CATEGORIES
]
def get_rent_to_income_incomes(self):
_, _, row = find("rent | incomes")(self.workbook)
cols = cols_until_empty(self.workbook, "B", test_sheet="Output", test_row=row)
return self.schema_list(
CurrencyCell(loc(sheet="Output", row=row)), locations=cols
)
def get_rent_to_income_rental_rates(self):
_, _, row = find("rent | incomes")(self.workbook)
rows = rows_until_empty(self.workbook, next_row(row), test_location="Output!A")
return self.schema_list(CurrencyCell(loc("Output!A")), locations=rows)
def get_rent_to_income_data(self, income_count, rental_rate_count):
_, _, row = find("rent | incomes")(self.workbook)
locations = location_range_rect(
start_col="B",
end_col=advance_col(income_count - 1)("B"),
start_row=next_row(row),
end_row=advance_row(rental_rate_count)(row), # +1-1=0
sheet="Output",
row_major=False,
)
return self.schema_rect(FloatCell(), locations=locations)
def get_rent_to_income(self):
categories = self.get_rent_to_income_categories()
incomes = self.get_rent_to_income_incomes()
rental_rates = self.get_rent_to_income_rental_rates()
data = self.get_rent_to_income_data(
income_count=len(incomes), rental_rate_count=len(rental_rates)
)
return {
"categories": categories,
"incomes": incomes,
"rental_rates": rental_rates,
"data": data,
}
def update_segment_details(self, segment):
_, _, row = find(f"age segment: {segment['age_group']}")(self.workbook)
cols = list(
cols_until(
self.workbook,
matchp(exact="All"),
"B",
test_sheet="Output",
test_row=row,
)
)
def _find(predicate):
return find_row("Output!A", predicate, start_row=row)
schema = {
"income": CurrencyCell(_find("age segment")),
"group_population": IntCell(_find("population")),
"home_owners": {
"total": IntCell(_find("home owners")),
"family": IntCell(_find("family ho")),
"nonfamily": IntCell(_find("non-family ho")),
},
"renters": {
"total": IntCell(_find("renters")),
"family": IntCell(_find("family r")),
"nonfamily": IntCell(_find("non-family r")),
},
"market_size": IntCell(_find("est. market")),
"active_populations": ["renters.nonfamily", "renters.family"],
}
segment["income_groups"] = self.schema_list(
schema, locations=cols, sheet="Output"
)
segment["segment_population"] = self.schema(
IntCell(loc(sheet="Output", row=next_row(row), col=next_col(cols[-1])))
)
def get_segments(self):
_, _, row = find("target segment")(self.workbook)
rows = rows_until_empty(self.workbook, next_row(row), test_location="Output!A")
# Grab the overviews for each segment
segments = self.schema_list(self.SEGMENT_OVERVIEW_SCHEMA, locations=rows)
for segment in segments:
self.update_segment_details(segment)
return segments
def get_future_year(self):
return self.schema(IntCell(find("future year")))
def get_total(self):
return self.schema(self.TOTAL_SCHEMA)
def get_average(self):
return self.schema(self.AVERAGE_SCHEMA)
def clean(self, ctx):
super().clean(ctx)
self.cleaned_data["location"] = self.get_location()
self.cleaned_data["estimated_population"] = self.get_estimated_population()
self.cleaned_data["rent_to_income"] = self.get_rent_to_income()
self.cleaned_data["segments"] = self.get_segments()
self.cleaned_data["future_year"] = self.get_future_year()
self.cleaned_data["total"] = self.get_total()
self.cleaned_data["average"] = self.get_average()
def get_rent_to_income_rental_rates(self):
_, _, row = find("rent | incomes")(self.workbook)
rows = rows_until_empty(self.workbook, next_row(row), test_location="Output!A")
return self.schema_list(CurrencyCell(loc("Output!A")), locations=rows)
def get_rent_to_income_incomes(self):
_, _, row = find("rent | incomes")(self.workbook)
cols = cols_until_empty(self.workbook, "B", test_sheet="Output", test_row=row)
return self.schema_list(
CurrencyCell(loc(sheet="Output", row=row)), locations=cols
)