from pathlib import Path
import click
import jinja2
import pandas as pd
import plotly.graph_objects as go
from google.cloud import storage
from plotly.offline import plot
WORK_DIR = Path(__file__).parent
STATIC_DIR = WORK_DIR / "static"
TEMPLATE_DIR = WORK_DIR / "templates"
GCS_PREFIX = "mobile-mau-2020"
forecast_and_actuals_query = (
Path(__file__).parent / "forecast_and_actual.sql"
).read_text()
PRODUCTS = [
# product_name y_min y_max plot_forecast plot_end_date
("Fenix", 0, 40000000, False, "2020-01-01"),
("Fennec", 18000000, 38000000, False, "2020-01-01"),
("Firefox iOS", 4000000, 9000000, True, "2019-07-01"),
("Firefox Lite", 0, 2000000, True, "2019-07-01"),
("Firefox Echo", 0, 900000, True, "2019-07-01"),
("Focus Android", 1100000, 3000000, True, "2019-07-01"),
("Focus iOS", 250000, 900000, True, "2019-07-01"),
("Lockwise Android", 0, 300000, True, "2019-07-01"),
]
GOAL_DATE_2019 = "2019-12-15"
GOAL_DATE_2020 = "2020-12-15"
PLOT_END_DATE = "2020-12-31"
def extract_mobile_product_mau(project):
"""
Read query results from BigQuery and return as a pandas dataframe.
"""
df = pd.read_gbq(forecast_and_actuals_query, project_id=project, dialect="standard")
df["date"] = pd.to_datetime(df["date"])
df.columns = ["datasource", "type", "date", "value", "low", "high"]
return df
def commafy(x):
"""Return comma-formatted number string."""
return f"{int(x):,}"
def create_table(template, platform, actual, forecast):
# end of year 12/15 YoY (last year vs current year)
eoy_yoy = 100 * (
forecast.query("date=='{}'".format(GOAL_DATE_2020)).value.iloc[0]
/ actual.query("date=='{}'".format(GOAL_DATE_2019)).value.iloc[0]
- 1
)
# data_end_date = actual["date"].max().date()
return template.render(
platform=platform,
metric="MAU",
current=commafy(actual.query("date==@data_end_date").value.iloc[0]),
current_pm=commafy(
actual.query("date==@data_end_date").value.iloc[0]
- actual.query("date==@data_end_date").low.iloc[0]
),
actual_2019=commafy(
actual.query("date=='{}'".format(GOAL_DATE_2019)).value.iloc[0]
),
forecast=commafy(
forecast.query("date=='{}'".format(GOAL_DATE_2020)).value.iloc[0]
),
forecast_pm=commafy(
forecast.query("date=='{}'".format(GOAL_DATE_2020)).value.iloc[0]
- forecast.query("date=='{}'".format(GOAL_DATE_2020)).low.iloc[0]
),
forecast_color=("green-text" if (eoy_yoy >= 0) else "red-text"),
eoy_yoy=f"{eoy_yoy:.2f}",
)
def create_plot(platform, y_min, y_max, actuals, forecast, plot_start_date, slice_name):
"""
Display a plot given a platform (each mobile product),
data for actuals and forecast, and slice (Global or Tier 1).
"""
main_metric_color = "#CA3524"
main_metric_color_ci = "#DDDDDD"
main_palette = [main_metric_color, main_metric_color_ci]
ci_fillcolor = "rgba(68, 68, 68, 0.2)"
ci_markercolor = "#444"
data_end_date = actuals["date"].max().date()
plotly_data = [
go.Scatter(
name="Actuals Lower Bound",
x=actuals.date,
y=actuals["low"],
showlegend=False,
line={"color": "rgba(0,0,255, 0.8)", "width": 0},
mode="lines",
marker=dict(color=ci_markercolor),
hoverlabel={"namelength": -1},
),
go.Scatter(
name="Actuals Upper Bound",
x=actuals.date,
y=actuals["high"],
showlegend=False,
line={"color": "rgba(0,0,255, 0.8)", "width": 0},
mode="lines",
marker=dict(color=ci_markercolor),
hoverlabel={"namelength": -1},
fillcolor=ci_fillcolor,
fill="tonexty",
),
go.Scatter(
name="Actuals",
x=actuals.date,
y=actuals["value"],
showlegend=True,
line={"color": "rgba(0,0,255, 0.8)"},
hoverlabel={"namelength": -1},
),
go.Scatter(
name="Credible Interval",
x=[0],
y=[0],
showlegend=True,
line={"color": ci_fillcolor, "width": 10},
mode="lines",
hoverlabel={"namelength": -1},
),
go.Scatter(
name="Previous Year Actuals",
x=actuals.date + pd.Timedelta("365 day"),
y=actuals["value"],
showlegend=True,
line={"color": "rgba(68,120,68,0.5)", "dash": "dashdot"},
hoverlabel={"namelength": -1},
),
]
if forecast is not None:
plotly_data.extend(
[
go.Scatter(
name="Forecast Lower Bound",
x=forecast.date,
y=forecast["low"],
showlegend=False,
line={"color": "rgba(0,0,255, 0.8)", "width": 0},
mode="lines",
marker=dict(color=ci_markercolor),
hoverlabel={"namelength": -1},
),
go.Scatter(
name="Forecast Upper Bound",
x=forecast.date,
y=forecast["high"],
showlegend=False,
line={"color": "rgba(0,0,255, 0.8)", "width": 0},
mode="lines",
marker=dict(color=ci_markercolor),
hoverlabel={"namelength": -1},
fillcolor=ci_fillcolor,
fill="tonexty",
),
go.Scatter(
name="Forecast",
x=forecast.date,
y=forecast["value"],
showlegend=True,
line={"color": main_palette[0], "dash": "dot"},
hoverlabel={"namelength": -1},
),
]
)
layout = go.Layout(
# autosize=force_width is None,
# width=force_width,
# height=force_height,
title="""\
{} {} MAU at end of day {}\
""".format(
slice_name,
platform,
data_end_date,
).strip(),
titlefont={
"size": 24,
},
xaxis=dict(
title="Date",
titlefont=dict(family="Courier New, monospace", size=18, color="#7f7f7f"),
range=[plot_start_date, PLOT_END_DATE],
tickmode="linear",
tick0=[plot_start_date],
dtick="M1",
tickfont=dict(color="grey"),
),
yaxis=dict(
title="MAU",
titlefont=dict(family="Courier New, monospace", size=18, color="#7f7f7f"),
hoverformat=",.0f",
range=([y_min, y_max] if y_min is not None else None),
tickfont=dict(color="grey"),
),
legend=dict(
x=0.5,
y=1.0,
traceorder="normal",
font=dict(family="sans-serif", size=12, color="#000"),
bgcolor="#FEFEFE",
bordercolor="#A0A0A0",
borderwidth=2,
orientation="h",
),
)
return plot(
{"data": plotly_data, "layout": layout},
output_type="div",
include_plotlyjs=False,
)
def create_table_and_plot(
product,
mobile_product_mau_data,
y_min,
y_max,
plot_start_date,
table_template,
plot_forecast=True,
):
print(f"Generating table and plot for {product}")
actual = mobile_product_mau_data[
(mobile_product_mau_data.datasource == product)
& (mobile_product_mau_data.type == "actual")
]
forecast = mobile_product_mau_data[
(mobile_product_mau_data.datasource == product)
& (mobile_product_mau_data.type == "forecast")
]
table = create_table(
table_template,
product,
actual,
forecast,
)
plot = create_plot(
product,
y_min,
y_max,
actual,
forecast if plot_forecast else None,
plot_start_date,
slice_name="Global",
)
return table, plot
def generate_html(project):
template_loader = jinja2.FileSystemLoader(TEMPLATE_DIR)
template_env = jinja2.Environment(loader=template_loader)
main_template = template_env.get_template("main.template.html")
table_template = template_env.get_template("table.template.html")
mobile_product_mau_data = extract_mobile_product_mau(project)
tables_and_plots = {}
for product_name, y_min, y_max, plot_forecast, plot_start_date in PRODUCTS:
product_table, product_plot = create_table_and_plot(
product_name,
mobile_product_mau_data,
y_min,
y_max,
plot_start_date,
table_template,
plot_forecast,
)
formatted_product_name = product_name.replace(" ", "_").lower()
tables_and_plots[f"{formatted_product_name}_table"] = product_table
tables_and_plots[f"{formatted_product_name}_plot"] = product_plot
output_html = main_template.render(**tables_and_plots)
(STATIC_DIR / "index.html").write_text(output_html)
def upload_files(project, bucket_name):
storage_client = storage.Client(project=project)
bucket = storage_client.bucket(bucket_name=bucket_name)
for filename in STATIC_DIR.glob("*"):
if (STATIC_DIR / filename).is_file():
blob = bucket.blob(str(Path(GCS_PREFIX) / filename.relative_to(STATIC_DIR)))
blob.upload_from_filename(str(STATIC_DIR / filename))
@click.command()
@click.option("--project", help="GCP project id", required=True)
@click.option("--bucket-name", help="GCP bucket name")
def main(project, bucket_name):
generate_html(project)
if bucket_name is not None:
upload_files(project, bucket_name)
if __name__ == "__main__":
main()