Tables and columns in the analytics BigQuery dataset.
Click a table in the sidebar to filter, or search below.
| Table ↕ | Column ↕ | Type ↕ | Description ↕ |
|---|
The Cubby data warehouse is the analytics BigQuery dataset that mirrors your
operational data — facilities, units, leases, contacts, payments, communications, and more
— remodeled for analytics and reporting. Tables are denormalized with descriptive columns
and foreign keys, so you can join across the business without reconstructing the underlying
transactional schema.
Data is refreshed on a rolling schedule:
spaces_historical and ga_events are refreshed once per day.Open Tables to browse every table and column, Schema Diagram to explore how the tables relate, or Sample queries for worked examples you can copy and run.
Eleven worked examples covering occupancy, leasing velocity, marketing attribution,
tenure, collections, and revenue management. Every query here has been run against
production data. Each one opens with DECLARE statements — set
target_org to your org_id (query 0 finds it) and adjust the
dates, then run.
org_idSix things about this data that are easy to get wrong.
org_id, and filter date on
spaces_historical. Every table is clustered on
org_id; spaces_historical and ga_events are
also date-partitioned. Your access is already scoped to your organization, so these
filters do not change your results — they cut the data scanned, which makes
queries faster and cheaper.
spaces_historical is a daily snapshot — one row
per unit per day, with no missing days. Use it for anything historical or trended.
units holds current state only.
is_active = 1 means the unit is in
inventory (0 is retired — exclude it from denominators);
is_occupied = 1 is exactly equivalent to
lease_id IS NOT NULL; and is_unrentable means the unit
cannot be rented right now. A unit can be both occupied and unrentable, so
unrentable is not the same as vacant.
width * depth, and parking breaks it.
Parking spaces usually store width = 0, so their area computes to zero.
Every square-foot query below returns a units_missing_dimensions column
so you can see how much of the site is affected before trusting the percentage.
0001, 2108, 5026), so always bound date ranges
explicitly.
rate_changes.rent_change_amount is the new rent, not the size of
the increase. leads.age_of_lead_minutes is now minus
created_at, recomputed on every load — not time to resolution, and it keeps
growing after a lead closes.
Every other query on this page needs your org_id. This is the quickest way
to get it.
SELECT DISTINCT org_id FROM `cubby-partner-data.analytics.facilities` ORDER BY org_id;
org_id —
or a small handful if you manage several organizations. You are not seeing anyone
else's data, and you do not need the filter for privacy.
org_id. Adding WHERE org_id = '…' lets
BigQuery skip the blocks belonging to other organizations instead of reading and then
discarding them. Same rows out, far less data processed, and noticeably faster on the
large tables.
facilities is one of the smallest tables in the dataset, which makes it
the cheapest place to look this up.
SELECT org_id, COUNT(*) AS facilities FROM … GROUP BY org_id.
Month-end physical occupancy on both a unit and a square-foot basis.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE start_date DATE DEFAULT DATE '2025-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
WITH snap AS (
SELECT
sh.date,
DATE_TRUNC(sh.date, MONTH) AS month,
sh.facility_id,
sh.facility_name,
sh.is_occupied,
sh.is_unrentable,
sh.width * sh.depth AS sqft
FROM `cubby-partner-data.analytics.spaces_historical` sh
WHERE sh.org_id = target_org
AND sh.date BETWEEN start_date AND end_date
AND sh.is_active = 1 -- exclude retired units
),
month_end AS (
-- Latest snapshot in each month, so a partial current month uses the most recent day
SELECT month, MAX(date) AS as_of_date
FROM snap
GROUP BY month
),
eom AS (
SELECT s.*
FROM snap s
JOIN month_end m ON s.month = m.month AND s.date = m.as_of_date
)
SELECT
month,
MAX(date) AS as_of_date,
facility_name,
COUNT(*) AS units_total,
COUNTIF(is_occupied = 1) AS units_occupied,
ROUND(SAFE_DIVIDE(COUNTIF(is_occupied = 1), COUNT(*)) * 100, 1) AS unit_occupancy_pct,
-- Denominator excludes units that are vacant AND unrentable
ROUND(SAFE_DIVIDE(COUNTIF(is_occupied = 1),
COUNTIF(is_occupied = 1 OR NOT is_unrentable)) * 100, 1)
AS rentable_unit_occupancy_pct,
ROUND(SUM(sqft), 0) AS sqft_total,
ROUND(SUM(IF(is_occupied = 1, sqft, 0)), 0) AS sqft_occupied,
ROUND(SAFE_DIVIDE(SUM(IF(is_occupied = 1, sqft, 0)), SUM(sqft)) * 100, 1) AS sqft_occupancy_pct,
COUNTIF(sqft IS NULL OR sqft = 0) AS units_missing_dimensions
FROM eom
GROUP BY month, facility_name
ORDER BY month DESC, facility_name;
unit_occupancy_pct covers all units in inventory — the standard
external-reporting number. rentable_unit_occupancy_pct removes
vacant-and-unrentable units from the denominator, which is the fairer read on how
the site is actually selling.
units_missing_dimensions before quoting
sqft_occupancy_pct. If it is a large share of
units_total, the square-foot figure only describes part of the site.
month_end and
eom CTEs and group snap directly by month,
so every day contributes equally.
Current occupancy and pricing by sellable unit type, with in-place rent against street rate.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE as_of DATE DEFAULT (
SELECT MAX(date) FROM `cubby-partner-data.analytics.spaces_historical`
);
SELECT
sh.facility_name,
sh.pricing_group_name AS unit_type,
CONCAT(CAST(sh.width AS STRING), 'x', CAST(sh.depth AS STRING)) AS size,
COUNT(*) AS units_total,
COUNTIF(sh.is_occupied = 1) AS units_occupied,
COUNTIF(sh.is_occupied = 0 AND NOT sh.is_unrentable) AS units_vacant_rentable,
COUNTIF(sh.is_occupied = 0 AND sh.is_unrentable) AS units_vacant_unrentable,
ROUND(SAFE_DIVIDE(COUNTIF(sh.is_occupied = 1), COUNT(*)) * 100, 1) AS unit_occupancy_pct,
ROUND(AVG(sh.street_rate), 2) AS avg_street_rate,
ROUND(AVG(IF(sh.is_occupied = 1, sh.occ_rate, NULL)), 2) AS avg_in_place_rate,
ROUND(SAFE_DIVIDE(AVG(IF(sh.is_occupied = 1, sh.occ_rate, NULL)),
AVG(sh.street_rate)) * 100, 1) AS in_place_vs_street_pct
FROM `cubby-partner-data.analytics.spaces_historical` sh
WHERE sh.org_id = target_org
AND sh.date = as_of
AND sh.is_active = 1
GROUP BY 1, 2, 3
HAVING units_total >= 5 -- suppress thin cells
ORDER BY facility_name, units_total DESC;
pricing_group_name is the unit type you want
(Drive-up 10x20, Climate-Controlled 5x10,
Parking - Uncovered 30'). The units.unit_type column has
only three values — RENT_ENCLOSED_STORAGE,
RENT_PARKING_SPACE, RENT_MOBILE_STORAGE — and is not
reliably maintained; parking pricing groups are sometimes tagged as enclosed
storage. Treat it as a rough category at best.
in_place_vs_street_pct above 100% means existing tenants pay more than
the current asking rate, which is normal for a site with a mature rate-increase
program. Below 100% is loss to lease: sustained readings there, especially on a type
sitting near full, usually mean street rates are set too low.
as_of filter for a BETWEEN range and add
DATE_TRUNC(sh.date, MONTH) to the grouping to trend any of this over
time.
Tenure by cohort, with the pre-migration history problem handled.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
WITH history_start AS (
-- The earliest move-out ever recorded for this org, which approximates the
-- migration/go-live date. Move-outs before this date are absent from the data,
-- so cohorts starting earlier contain only migration survivors.
SELECT DATE_TRUNC(MIN(lease_ended), MONTH) AS first_observable_month
FROM `cubby-partner-data.analytics.leases`
WHERE org_id = target_org
AND lease_ended > DATE '2015-01-01' -- guard against junk dates
),
cohort AS (
SELECT
DATE_TRUNC(l.lease_started, MONTH) AS move_in_month,
l.lease_ended IS NOT NULL AS has_moved_out,
DATE_DIFF(IFNULL(l.lease_ended, CURRENT_DATE()), l.lease_started, DAY) AS days_observed
FROM `cubby-partner-data.analytics.leases` l
CROSS JOIN history_start h
WHERE l.org_id = target_org
AND l.lease_started >= h.first_observable_month
AND l.lease_started <= CURRENT_DATE()
)
SELECT
move_in_month,
COUNT(*) AS move_ins,
COUNTIF(has_moved_out) AS moved_out,
ROUND(SAFE_DIVIDE(COUNTIF(has_moved_out), COUNT(*)) * 100, 1) AS pct_moved_out,
ROUND(APPROX_QUANTILES(IF(has_moved_out, days_observed, NULL), 2)[OFFSET(1)] / 30.44, 1)
AS median_los_months,
ROUND(AVG(IF(has_moved_out, days_observed, NULL)) / 30.44, 1) AS avg_los_months,
IF(SAFE_DIVIDE(COUNTIF(has_moved_out), COUNT(*)) >= 0.5,
'complete',
'incomplete - fewer than half have moved out') AS cohort_status
FROM cohort
GROUP BY move_in_month
ORDER BY move_in_month DESC;
median_los_months where
cohort_status = 'complete'. For recent cohorts most tenants are
still in place, so the median of those who have left is a median of early leavers
only — badly biased downward. A cohort's median means something exactly when at
least half of it has moved out.
complete row for the real number.
avg_los_months runs well above the median because a minority of very
long tenancies pull the mean up. Median is the better headline; the gap between the
two tells you about your long-tail tenants.
history_start guard already
excludes the cohorts where their move-outs are missing. Adding
AND lease_created_by <> 'IMPORT' would truncate your history back
to go-live and throw away real tenure data.
first_observable_month per facility. To be strict, compute
history_start grouped by facility_id and join on it.
Monthly move-in volume split by how the customer found you and how they signed.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE start_date DATE DEFAULT DATE '2025-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
WITH move_ins AS (
SELECT
DATE_TRUNC(l.lease_started, MONTH) AS move_in_month,
CASE
WHEN l.lease_created_by = 'WEBSITE' THEN 'Online (self-serve)'
WHEN l.lease_created_by = 'IMPORT' THEN 'Imported / migrated'
ELSE 'Manager-assisted'
END AS rental_channel,
IFNULL(ld.lead_source, '(no lead record)') AS lead_source,
l.lease_rent_original
FROM `cubby-partner-data.analytics.leases` l
LEFT JOIN `cubby-partner-data.analytics.leads` ld
ON ld.org_id = l.org_id
AND ld.converted_lease_id = l.lease_id
WHERE l.org_id = target_org
AND l.lease_started BETWEEN start_date AND end_date
)
SELECT
move_in_month,
rental_channel,
lead_source,
COUNT(*) AS move_ins,
ROUND(SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER (PARTITION BY move_in_month)) * 100, 1)
AS pct_of_month,
ROUND(AVG(lease_rent_original), 2) AS avg_move_in_rent
FROM move_ins
GROUP BY move_in_month, rental_channel, lead_source
ORDER BY move_in_month DESC, move_ins DESC;
lease_created_by is your rental-channel field.
WEBSITE is an online self-serve rental, IMPORT came from a
prior system or a bulk load, and any other value is a staff member's name — a
manager-assisted rental.
leads.converted_lease_id joins to leases.lease_id one to
one. Coverage is roughly 99% of online rentals and 92% of manager-assisted ones; the
rest appear as (no lead record).
CALL lead that closes as Online (self-serve) means the
customer phoned and then rented on their own — a different operational story
from a call the manager closed. The split shows where staff time is actually
converting.
Imported / migrated rows are not real move-ins for the month; they are
artifacts of a data load. Exclude them when reporting leasing velocity, or use query
5, which avoids the issue.
avg_move_in_rent uses lease_rent_original, the contract
rent at signing before any promotional discount. For actual first-month revenue,
join discounts on lease_id.
Leasing velocity from the purpose-built turnover table.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE start_date DATE DEFAULT DATE '2025-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
SELECT
DATE_TRUNC(move_date, MONTH) AS month,
facility_name,
COUNTIF(move_type = 'MOVE_IN') AS move_ins,
COUNTIF(move_type = 'MOVE_OUT') AS move_outs,
COUNTIF(move_type = 'MOVE_IN') - COUNTIF(move_type = 'MOVE_OUT') AS net_absorption_units,
COUNTIF(move_type = 'TRANSFER') AS internal_transfers,
ROUND(SUM(IF(move_type = 'MOVE_IN', unit_width * unit_depth, 0)), 0) AS sqft_moved_in,
ROUND(SUM(IF(move_type = 'MOVE_OUT', unit_width * unit_depth, 0)), 0) AS sqft_moved_out,
ROUND(AVG(IF(move_type = 'MOVE_IN', lease_rent, NULL)), 2) AS avg_move_in_rent,
ROUND(AVG(IF(move_type = 'MOVE_OUT', lease_rent, NULL)), 2) AS avg_move_out_rent,
ROUND(APPROX_QUANTILES(IF(move_type = 'MOVE_OUT', lease_days_rented, NULL), 2)[OFFSET(1)], 0)
AS median_days_rented_of_move_outs
FROM `cubby-partner-data.analytics.unit_turnover`
WHERE org_id = target_org
AND move_date BETWEEN start_date AND end_date
GROUP BY month, facility_name
ORDER BY month DESC, move_ins DESC;
unit_turnover over deriving move-ins from
leases. It holds one row per lease per event and already
separates internal transfers into their own TRANSFER type, so a tenant
moving from a 5x10 to a 10x10 does not inflate your move-ins and move-outs. Deriving
from lease_started and lease_ended counts both sides of
every transfer as real churn.
avg_move_in_rent against avg_move_out_rent is a rate-roll
indicator. Move-out rents are usually higher because departing tenants have absorbed
years of increases; a persistent large gap means new rentals are priced well below
what the existing base tolerates.
sqft_moved_in with sqft_moved_out catches mix
shift that unit counts hide. Flat net absorption alongside shrinking net square
footage means you are trading large units for small ones.
Which inquiries are sitting unworked, and for how long.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
WITH open_leads AS (
SELECT
IFNULL(lead_source, '(unknown)') AS lead_source,
age_of_lead_minutes / 1440.0 AS age_days
FROM `cubby-partner-data.analytics.leads`
WHERE org_id = target_org
-- Unresolved statuses only; age_of_lead_minutes is meaningless once a lead closes
AND status IN ('NEW','IN_PROGRESS_HOT','IN_PROGRESS_COLD','ON_HOLD','RESERVATION')
)
SELECT
lead_source,
COUNT(*) AS open_leads,
COUNTIF(age_days < 1) AS age_under_1d,
COUNTIF(age_days >= 1 AND age_days < 7) AS age_1_6d,
COUNTIF(age_days >= 7 AND age_days < 30) AS age_7_29d,
COUNTIF(age_days >= 30 AND age_days < 90) AS age_30_89d,
COUNTIF(age_days >= 90) AS age_90d_plus,
ROUND(APPROX_QUANTILES(age_days, 2)[OFFSET(1)], 1) AS median_age_days,
ROUND(AVG(age_days), 1) AS avg_age_days
FROM open_leads
GROUP BY lead_source
ORDER BY open_leads DESC;
age_of_lead_minutes is recomputed as now minus
created_at on every load, so a lead that converted in five minutes two
years ago reports an age of two years. Without the filter you are measuring how long
ago leads were created, not how long they have been waiting. For resolution time use
time_to_convert or time_to_unqualified.
DUPLICATE is a real status and is excluded here along with the other
closed states. It is worth reporting separately — a high duplicate rate on a
source usually means the same person is arriving through several paths.
age_90d_plus bucket is mostly dead pipeline that was never
dispositioned. Cleaning it up makes every downstream conversion rate more honest.
avg_age_days far above median_age_days for a source is the
signature of a stale tail rather than a slow process.
Conversion and speed to close, by where the lead came from.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE start_date DATE DEFAULT DATE '2025-01-01';
-- End the window early so recent leads have had time to convert
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY);
WITH scored AS (
SELECT
IFNULL(lead_source, '(unknown)') AS lead_source,
status,
time_to_convert
FROM `cubby-partner-data.analytics.leads`
WHERE org_id = target_org
AND DATE(created_at) BETWEEN start_date AND end_date
AND status <> 'DUPLICATE' -- duplicates would deflate every rate
)
SELECT
lead_source,
COUNT(*) AS leads,
COUNTIF(status = 'CONVERTED') AS converted,
ROUND(SAFE_DIVIDE(COUNTIF(status = 'CONVERTED'), COUNT(*)) * 100, 1) AS conversion_pct,
COUNTIF(status = 'UNQUALIFIED') AS unqualified,
COUNTIF(status IN ('NEW','IN_PROGRESS_HOT','IN_PROGRESS_COLD','ON_HOLD','RESERVATION'))
AS still_open,
ROUND(APPROX_QUANTILES(IF(status = 'CONVERTED', time_to_convert / 60.0, NULL), 2)[OFFSET(1)], 1)
AS median_hours_to_convert,
ROUND(SAFE_DIVIDE(COUNTIF(status = 'CONVERTED' AND time_to_convert = 0),
NULLIF(COUNTIF(status = 'CONVERTED'), 0)) * 100, 1)
AS pct_converted_instantly
FROM scored
GROUP BY lead_source
HAVING leads >= 20 -- suppress thin cells
ORDER BY leads DESC;
WEBSITE_CHECKOUT records a
completed online rental — the lead row is created by the checkout, so
it converts essentially always. The same applies to WALK_IN, often
logged at the counter as the rental happens. The real prospect sources to compare
against each other are CALL, RESERVATION_FORM,
CONTACT_FORM, SPAREFOOT, and the other aggregators.
pct_converted_instantly is what separates the two groups. A high value
means the lead is a record of a rental rather than an inquiry that was worked. Read
conversion_pct alongside it, never alone.
ABANDONED_CHECKOUT and FAILED_CHECKOUT are
system-generated recovery leads, not inbound demand. Their conversion rate measures
how well you win back a dropped cart — useful, but not a marketing-channel
number. Do not roll them into a blended rate.
lead_source, not source or
marketing_source, for channel reporting.
lead_source is a clean controlled vocabulary; the other two blend that
vocabulary with raw UTM strings, so you get organic, cpc,
gbp, gpb (a typo of the same thing), and occasionally an
entire ad-set name.
end_date matters. Median time to convert is under an
hour for most sources, but CONTACT_FORM runs well over a day, so a
window ending today understates the slower channels.
Current past-due balances bucketed by how long the lease has been delinquent.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
WITH active AS (
SELECT
facility_name,
balance_ar,
is_lease_paid,
is_needs_overlock,
is_in_auction,
is_autopay_enabled,
DATE_DIFF(CURRENT_DATE(), status_late_since_date, DAY) AS days_late
FROM `cubby-partner-data.analytics.leases`
WHERE org_id = target_org
AND is_active = 1
)
SELECT
facility_name,
COUNT(*) AS active_leases,
COUNTIF(is_lease_paid = 0) AS leases_past_due,
ROUND(SAFE_DIVIDE(COUNTIF(is_lease_paid = 0), COUNT(*)) * 100, 1) AS pct_past_due,
ROUND(SUM(GREATEST(balance_ar, 0)), 2) AS total_ar,
ROUND(SUM(IF(days_late BETWEEN 1 AND 30, GREATEST(balance_ar, 0), 0)), 2) AS ar_1_30,
ROUND(SUM(IF(days_late BETWEEN 31 AND 60, GREATEST(balance_ar, 0), 0)), 2) AS ar_31_60,
ROUND(SUM(IF(days_late BETWEEN 61 AND 90, GREATEST(balance_ar, 0), 0)), 2) AS ar_61_90,
ROUND(SUM(IF(days_late > 90, GREATEST(balance_ar, 0), 0)), 2) AS ar_over_90,
COUNTIF(is_needs_overlock = 1) AS needs_overlock,
COUNTIF(is_in_auction = 1) AS in_auction,
ROUND(SAFE_DIVIDE(COUNTIF(is_autopay_enabled), COUNT(*)) * 100, 1) AS pct_on_autopay
FROM active
GROUP BY facility_name
ORDER BY total_ar DESC;
leases holds current state only. To trend delinquency you need to
snapshot this output on a schedule.
GREATEST(balance_ar, 0) matters. balance_ar goes negative
when a tenant is in credit, and summing raw values lets prepaid tenants silently
cancel out delinquent ones. Prepaid balances live in balance_prepaid if
you want them.
status_late_since_date — when the
lease entered delinquency, not the age of each individual charge, so the whole
balance lands in one bucket. That is the right shape for collections triage; it is
not a GAAP AR aging.
pct_on_autopay is the strongest lever here. Sites 10–15 points
below portfolio average on autopay adoption almost always carry proportionally more
ar_1_30.
needs_overlock and in_auction show whether policy is
actually being enforced. Heavy ar_over_90 with near-zero auction
activity is a process problem, not a collections problem.
How many increases went out, how big they were, and what they added to monthly revenue.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE start_date DATE DEFAULT DATE '2025-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
WITH changes AS (
SELECT
DATE_TRUNC(rc.rent_change_scheduled_date, MONTH) AS effective_month,
rc.rent_change_status,
rc.lease_rent_previous AS old_rent,
rc.rent_change_amount AS new_rent, -- NB: this is the new rent, not the delta
SAFE_DIVIDE(rc.rent_change_amount, rc.lease_rent_previous) - 1 AS pct_increase,
rc.street_rate,
rc.lease_id
FROM `cubby-partner-data.analytics.rate_changes` rc
WHERE rc.org_id = target_org
AND rc.rent_change_scheduled_date BETWEEN start_date AND end_date
AND rc.lease_rent_previous >= 10 -- guards against divide-by-near-zero junk
AND rc.rent_change_amount >= 10
),
outcomes AS (
SELECT c.*, l.lease_ended
FROM changes c
LEFT JOIN `cubby-partner-data.analytics.leases` l
ON l.org_id = target_org AND l.lease_id = c.lease_id
)
SELECT
effective_month,
COUNTIF(rent_change_status = 'APPLIED') AS increases_applied,
COUNTIF(rent_change_status = 'SCHEDULED') AS increases_scheduled,
COUNTIF(rent_change_status = 'CANCELLED') AS increases_cancelled,
ROUND(SAFE_DIVIDE(COUNTIF(rent_change_status = 'CANCELLED'), COUNT(*)) * 100, 1)
AS cancel_rate_pct,
ROUND(AVG(IF(rent_change_status = 'APPLIED', pct_increase, NULL)) * 100, 1)
AS avg_increase_pct,
ROUND(APPROX_QUANTILES(IF(rent_change_status = 'APPLIED', pct_increase, NULL), 2)[OFFSET(1)] * 100, 1)
AS median_increase_pct,
ROUND(SUM(IF(rent_change_status = 'APPLIED', new_rent - old_rent, 0)), 2)
AS monthly_revenue_added,
ROUND(AVG(IF(rent_change_status = 'APPLIED', SAFE_DIVIDE(new_rent, street_rate), NULL)) * 100, 1)
AS new_rent_vs_street_pct,
-- Descriptive only. See notes: this is NOT a causal churn estimate.
ROUND(SAFE_DIVIDE(
COUNTIF(rent_change_status = 'APPLIED' AND lease_ended IS NOT NULL
AND DATE_DIFF(lease_ended, effective_month, DAY) BETWEEN 0 AND 90),
NULLIF(COUNTIF(rent_change_status = 'APPLIED'), 0)) * 100, 1)
AS pct_moved_out_within_90d
FROM outcomes
GROUP BY effective_month
ORDER BY effective_month DESC;
rent_change_amount is the new rent, not the increase.
Reading it as a delta overstates your program by roughly an order of magnitude.
monthly_revenue_added above computes
new_rent - old_rent explicitly.
median_increase_pct, not
avg_increase_pct. The average is dragged around by a handful of
extreme records; one month tested at a 25.5% average against an 11.3% median. The
guards on lease_rent_previous and rent_change_amount remove
the worst divide-by-tiny artifacts, but the tail is still heavy.
pct_moved_out_within_90d is descriptive, not causal, and should
not be quoted as the churn cost of ECRI. There is no control group, and
tenants who receive increases are systematically different from those who do not
— they have already survived longer, which makes them lower-risk to begin with.
Comparing them against non-increased tenants biases the result. For a defensible
read, compare across increase size tiers among tenants who all received an
increase in the same month; that holds the selection effect roughly constant.
cancel_rate_pct is the operational health metric worth watching. A jump
usually means managers are pulling increases back after tenant pushback, which is a
leading indicator that next month's applied number will disappoint.
new_rent_vs_street_pct below 100% means even post-increase tenants sit
under the current asking rate — headroom for a larger increase next cycle.
Net cash collected, with refunds netted out and card decline rates alongside.
DECLARE target_org STRING DEFAULT 'org_XXXXXXXXXXX';
DECLARE start_date DATE DEFAULT DATE '2025-01-01';
DECLARE end_date DATE DEFAULT CURRENT_DATE();
SELECT
DATE_TRUNC(payment_date, MONTH) AS month,
facility_name,
ROUND(SUM(IF(payment_type = 'SALE' AND payment_status IN ('APPROVED','REFUNDED'),
payment_amount, 0)), 2) AS gross_collected,
ROUND(SUM(IF(payment_type = 'REFUND' AND payment_status = 'APPROVED',
payment_amount, 0)), 2) AS refunds,
ROUND(SUM(IF(payment_type = 'SALE' AND payment_status IN ('APPROVED','REFUNDED'),
payment_amount, 0))
- SUM(IF(payment_type = 'REFUND' AND payment_status = 'APPROVED',
payment_amount, 0)), 2) AS net_collected,
COUNTIF(payment_type = 'SALE' AND payment_status IN ('APPROVED','REFUNDED'))
AS successful_payments,
COUNTIF(payment_type = 'SALE' AND payment_status = 'DECLINED') AS declined_attempts,
ROUND(SAFE_DIVIDE(
COUNTIF(payment_type = 'SALE' AND payment_status = 'DECLINED'),
COUNTIF(payment_type = 'SALE' AND payment_status IN ('APPROVED','REFUNDED','DECLINED'))) * 100, 1)
AS decline_rate_pct,
ROUND(SAFE_DIVIDE(
SUM(IF(payment_type = 'SALE' AND payment_status IN ('APPROVED','REFUNDED')
AND payment_channel = 'AUTOPAY', payment_amount, 0)),
SUM(IF(payment_type = 'SALE' AND payment_status IN ('APPROVED','REFUNDED'),
payment_amount, 0))) * 100, 1)
AS pct_collected_via_autopay
FROM `cubby-partner-data.analytics.payments`
WHERE org_id = target_org
AND payment_date BETWEEN start_date AND end_date
GROUP BY month, facility_name
ORDER BY month DESC, net_collected DESC;
payment_amount values are positive, including refunds and
declines. A naive SUM(payment_amount) overstates revenue by
roughly 20% because it silently includes failed attempts. The status and type filters
above are not optional.
| payment_type | payment_status | Meaning |
|---|---|---|
SALE | APPROVED | Collected, still stands |
SALE | REFUNDED | Was collected, later refunded — the original row is restamped |
REFUND | APPROVED | The refund transaction itself, positive amount |
SALE | DECLINED / FAILED / ERROR | Never collected |
FAILURE | APPROVED | Failed-payment fee records — exclude from revenue |
APPROVED and REFUNDED sales, then
subtracts the REFUND rows. Because refunds carry their own
payment_date, a refund can land in a later month than its sale, so
monthly netting is approximate — the annual total is exact.
book_entries.
decline_rate_pct typically runs 13–25% by site, and a retry that
later succeeds appears as both a decline and a success — so this measures
attempt quality, not lost revenue. Rising declines alongside flat
pct_collected_via_autopay usually mean expiring cards;
leases.autopay_card_expiration is where to look next.
payments carries contact_id but no
lease_id, so you cannot attribute revenue to a specific unit
through this table. Go through book_entries for unit-level revenue.
| Table | Use it for |
|---|---|
book_entries | Accrual revenue, unit-level charge detail, GAAP-shaped reporting |
spaces_historical | Anything trended — occupancy, rate history, vacancy duration |
unit_turnover | Move-ins, move-outs, transfers; already transfer-aware |
calls, sms, emails, customer_touches | Contact-attempt volume and response times |
ga_events | Web sessions upstream of lead creation (date-partitioned) |
stor_track | Competitor rate benchmarking |
discounts, discount_programs | Promotional concession cost |
date_dim | Calendar joins — pre-built month, quarter, week, and weekday flags |
auctions, lease_auctions | Delinquency resolution outcomes |
date_dim is worth knowing about early: joining to it is cleaner than nesting
DATE_TRUNC and FORMAT_DATE calls, and it gives you gap-free
month rows even in periods with no activity.