HomeVal analytics dashboard #139

Goal

Let's build a Quarto report that we can use as a dashboard to help answer some questions about how users are interacting with the HomeVal service.

Log message format

Our dashboard will download and analyze log messages from AWS CloudWatch. Here are some details about the format of those log messages.

Example log message

Here's an example log message for convenience. See the /aws-cloudfront/pinval-prod/ CloudWatch log group for more examples.

{
    "date": "2025-08-13",
    "time": "20:21:41",
    "sc-bytes": "13682",
    "c-ip": "000.000.000.000",
    "cs-method": "GET",
    "cs(Host)": "homeval.cookcountyassessoril.gov",
    "cs-uri-stem": "/2025/01011000040000.html",
    "sc-status": "200",
    "cs(Referer)": "-",
    "cs(User-Agent)": "Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64;%20rv:141.0)%20Gecko/20100101%20Firefox/141.0",
    "cs-uri-query": "-",
    "x-host-header": "homeval.cookcountyassessoril.gov",
    "cs-protocol": "https",
    "cs-bytes": "44",
    "time-taken": "0.193",
    "x-forwarded-for": "-",
    "x-edge-response-result-type": "Miss",
    "cs-protocol-version": "HTTP/2.0",
    "c-port": "12944",
    "time-to-first-byte": "0.167",
    "sc-content-type": "text/html",
    "sc-content-len": "-"
}

Log attribute descriptions

Data extraction

We will want to use the paws API for the CloudWatch GetLogEvents operation to extract log messages. We will also need to query some data from Athena to augment logs.

Cache implementation

• Cache pinval.vw_assessment_card dataframe to a local parquet file since we expect it to change rarely if ever
• Cache log messages by date, with one file corresponding to each date
  • Start the date range on 9/4/2025
  • Query CloudWatch for logs on any dates between 9/4/2025 and today for which we do not already have a cache file
  • Always refresh the cache for the current date so that we can get any new logs for that date; otherwise, skip requests for dates for which we already have cache files

Charts and tables

Report details

For the following charts, it probably would make sense to put charts that use the same source metric together into one tabset so that we group by source metric and allow the user to flip through tabs to show different views on the same source metric. However, I leave the organization of the charts up to your discretion.

Request count by class

• Chart type: Bar
• Key log attributes:
  • cs-uri-stem
    • Extract PIN to derive the class from pinval.vw_assessment_card.char_class
• Chart:
  • X-Axis: Class
  • Y-Axis: Count

Request count by class over time (total)

Same as above, just with stacked bars and the X-Axis set to the date.

Request count by class over time (%)

Same as above, just with stacked bars, the X-Axis set to the date, and the Y-axis value as a proportion that adds up to 100% for each date.

Request count by reassessment year

• Chart type: Bar
• Key log attributes:
  • cs-uri-stem
    • Extract year
• Chart:
  • X-Axis: Year
  • Y-Axis: Count

Request count by reassessment year over time (total)

Same as above, just with stacked bars and the X-Axis set to the date.

Request count by reassessment year over time (%)

Same as above, just with stacked bars, the X-Axis set to the date, and the Y-axis value as a proportion that adds up to 100% for each date.

Request count by report eligibility

• Chart type: Bar
• Key log attributes:
  • cs-uri-stem
    • Extract PIN to derive report eligibility from pinval.vw_assessment_card.is_report_eligible
• Chart:
  • X-Axis: Eligibility (One for Eligible and one for Not Eligible)
  • Y-Axis: Count

Request count by report eligibility over time (total)

Same as above, just with stacked bars and the X-Axis set to the date.

Request count by report eligibility over time (%)

Same as above, just with stacked bars, the X-Axis set to the date, and the Y-axis value as a proportion that adds up to 100% for each date.

Request count by report eligibility reason

• Chart type: Bar
• Key log attributes:
  • cs-uri-stem
    • Extract PIN to derive report eligibility reason from pinval.vw_assessment_card.reason_report_ineligible
• Chart:
  • X-Axis: Elibility reason
  • Y-Axis: Count

Request count by report eligibility reason over time (total)

Same as above, just with stacked bars and the X-Axis set to the date.

Request count by report eligibility reason over time (%)

Same as above, just with stacked bars, the X-Axis set to the date, and the Y-axis value as a proportion that adds up to 100% for each date.

Request count by township and reassessment year

• Chart type: Bar
• Key log attributes:
  • cs-uri-stem
    • Extract PIN and year to derive township from pinval.vw_assessment_card.meta_township_code
• Chart:
  • X-Axis: Township
  • Y-Axis: Count
  • Group township bars visually by triad
    • Not sure how best to represent this in the chart... maybe we could add vertical bars separating triads? I'm open to ideas [NJ note: in addition to the spatial grouping by triad, could also do different colors for each tri]
• Tabset with one tab for each reassessment year

HTTP request and response details

Distribution of HTTP response status codes (all requests)

• Chart type: Multiline
• Key log attributes:
  • sc-status
• Chart:
  • X-axis: Date
  • Y-axis: Count
  • Line: One per status code (404, 200, etc)

Distribution of HTTP response status codes (PINs only)

• Chart type: Multiline
• Key log attributes:
  • sc-status
  • Filter where PIN is not null (derived from cs-uri-stem)
• Chart:
  • X-axis: Date
  • Y-axis: Count
  • Line: One per status code (404, 200, etc)

Recent request paths that caused errors

• Chart type: Table
• Key log attributes:
  • sc-status
  • cs-uri-stem
• Table columns:
  • Timestamp (date + time)
  • Status (sc-status)
  • Path (cs-uri-stem)
• Limit to 100 most recent requests whose status was not 2xx or 3xx

Most common browsers

• Chart type: Bar
• Key log attributes:
  • cs(User-Agent)
    • There's a lot of info contained in this field (see example above) so you will need to parse it out
• Chart:
  • X-axis: Browser
  • Y-axis: Count
    • If there are a lot of browsers, this may end up looking better with the axes flipped, or as a table

Most common referer domains (all requests)

I.E. what websites are sending users to our reports; I would expect this to mostly be cookcountyassessoril.gov or homeval.cookcountyassessoril.gov but it would be interesting to see if folks are coming from elsewhere (e.g. social media)

• Chart type: Bar
• Key log attributes:
  • cs(Referer)
    • Extract only the domain (i.e. strip everything after the top-level domain)
• Chart:
  • X-Axis: Domain
  • Y-Axis: Count
    • If there are a lot of referers, this chart may end up looking better with the axes flipped, or as a table

Most common referer domains (PINs only)

• Chart type: Bar
• Key log attributes:
  • cs(Referer)
    • Extract only the domain (i.e. strip everything after the top-level domain)
  • Filter where PIN is not null (based on cs-uri-stem)
• Chart:
  • X-Axis: Domain
  • Y-Axis: Count
    • If there are a lot of referers, this chart may end up looking better with the axes flipped, or as a table

Cache hit percentage over time (all requests)

• Chart type: Line
• Key log attributes:
  • x-edge-response-result-type
• Chart:
  • X-Axis: Date
  • Y-Axis: Cache hit %

Cache hit percentage over time (PINs only)

• Chart type: Line
• Key log attributes:
  • x-edge-response-result-type
  • Filter where PIN is not null (based on cs-uri-stem)
• Chart:
  • X-Axis: Date
  • Y-Axis: Cache hit %