Shopify Communities Program

The Communities program is a group-buy sales campaign. A school or PTO organiser runs a bulk purchase, participants order using a shared discount code, and pricing improves as the group hits volume tiers.

The campaign roster is RAW_DB.SHOPIFY.METAOBJECT_COMMUNITY, one row per Shopify community metaobject entry. Its FIELD_VALUES column is a dictionary holding every field the Shopify admin shows — discount_code, status_label, window_opens_at, window_closes_at, current_count, target_devices, charged_tier, final_tier, org_type, organiser contact details, and so on.

Two unrelated things are named "community"

Never join these two tables. They share a name and nothing else.

Table	What it is
RAW_DB.SHOPIFY.METAOBJECT_COMMUNITY	The group-buy sales campaign roster. Use this for anything about the Communities program.
RAW_DB.TINCAN.COMMUNITY	An unrelated in-app product feature — a roster of families so kids can call each other. Nothing to do with sales campaigns.

A question about the Communities program — how many communities, which schools, how much they bought, whether a campaign closed — is always about METAOBJECT_COMMUNITY. Answering it from RAW_DB.TINCAN.COMMUNITY returns a far smaller number and a completely different concept, with nothing in the output indicating the error.

The two tables have no shared key. Any join between them is wrong by construction, even if it runs.

Joining the roster to orders

The join key is the discount code, and it is not a column on either side — it sits inside semi-structured data in both tables, and the orders side must be flattened first. This join cannot be inferred from the schema; use this pattern.

with order_codes as (
    select lower(dc.value:code::string) as code,
           o.id                         as order_id
    from raw_db.shopify.orders o,
         lateral flatten(input => o.discount_codes) dc
    where o.tags ilike '%community_order%'
)
select c.handle,
       c.display_name,
       c.field_values:discount_code::string as discount_code,
       c.field_values:status_label::string  as status_label,
       count(distinct oc.order_id)          as orders
from raw_db.shopify.metaobject_community c
left join order_codes oc
       on lower(c.field_values:discount_code::string) = oc.code
group by 1, 2, 3, 4
order by orders desc, c.display_name;

Five things this pattern is doing deliberately:

The roster drives, with a LEFT JOIN. Every community appears, including those with no orders at all — which is most of the point of having this table. An inner join, or driving from orders, silently drops exactly the campaigns that order data alone can never show you. count(distinct oc.order_id) correctly returns 0 for them; count(*) would wrongly return 1.

The flatten sits in a CTE. lateral flatten is correlated to orders, so it cannot be left-joined to directly. Flattening to one row per order-and-code first makes the roster available as the driving table.

lateral flatten on orders.discount_codes. That column is an array of objects, so the code lives at dc.value:code, not on the order row.

lower() on both sides. Shopify treats discount codes as case-insensitive at redemption, so the casing recorded on an order is not guaranteed to match the casing stored on the metaobject.

The community_order tag filter. Without it the flatten also picks up staff-entered manual discounts recorded by reason text — replacement, custom discount, failed delivery — plus referral and test codes, none of which are community campaigns.

Group on handle, not display_name. Handles are unique per entry; display names are not — two distinct entries can carry the same name, and grouping without the handle silently merges them into one row.

To narrow to communities that did sell, filter the result rather than changing the join: having count(distinct oc.order_id) > 0.

Identifying community orders without the roster

orders.tags ilike '%community_order%' is the reliable flag for whether an order belongs to the program. It is applied automatically. It does not tell you which community — that requires the join above.

The roster contains campaigns that never sold

Many roster entries have no matching orders at all. This is expected, not a join failure: a campaign that opened and never sold anything leaves no trace in order data. Answering "how many communities do we have?" from orders alone undercounts, sometimes badly. Count from the roster.

Use field_values:status_label and the window_opens_at / window_closes_at fields for lifecycle questions, which order data cannot answer at all.

Do not sum per-community figures across rows

discount_code is not unique in this table. A school district runs one campaign under a single code but gets a separate roster entry for each participating school. So one code can map to several rows.

Critically, target_devices and current_count belong to the campaign, not to the school, and the same value is copied onto every school's row. Summing them across rows counts a district's campaign once per school and materially overstates the total — with nothing in the data signalling the error.

Aggregate per code first:

-- correct: one row per campaign
with per_campaign as (
    select lower(field_values:discount_code::string)         as discount_code,
           max(try_to_number(field_values:target_devices::string)) as target_devices,
           max(try_to_number(field_values:current_count::string))  as current_count
    from raw_db.shopify.metaobject_community
    group by 1
)
select count(*)             as campaigns,
       sum(target_devices)  as target_devices,
       sum(current_count)   as current_count
from per_campaign;

The same duplication makes an orders-to-roster join many-to-many rather than one-to-one, which matters in a way that is easy to miss. A per-community count(distinct o.id) is correct for that community — but adding those per-community counts together exceeds the true number of orders, because an order placed under a district-wide code is counted once for every participating school.

So do not derive a program-wide order total by summing a per-community breakdown. Count it directly against orders instead:

select count(distinct id) as community_orders
from raw_db.shopify.orders
where tags ilike '%community_order%';

"How many communities do we have?" has two valid answers

Roster entries and distinct campaigns are different numbers, and both are defensible. State which one is being reported. Entries are the right unit for "how many schools are participating"; distinct discount codes are the right unit for "how many campaigns are running".

What this data cannot answer

Per-school attribution inside a district. The discount code is district-wide, so an order can only be attributed to the district, never to a specific school within it. This is not derivable at any grain — do not attempt to infer it.

Querying FIELD_VALUES

Values are strings as Shopify returned them; cast as needed (try_to_number, try_to_timestamp_tz).

Shopify returns unset fields as JSON null rather than omitting them, so every field key appears on every row. Checking whether a key is present proves nothing about whether it has a value, and a bare is not null on a VARIANT path is always true. Use is_null_value():

-- entries that have actually recorded a final tier
select count(*)
from raw_db.shopify.metaobject_community
where not is_null_value(field_values:final_tier);

hero_image holds a gid://shopify/MediaImage/... reference, not an image URL.

Freshness and load behaviour

This table is loaded by a scheduled Retool Workflow against the Shopify Admin GraphQL API, not by Airbyte — Shopify exposes metaobjects only through GraphQL, and no ELT connector supports them. Each run replaces the table with a full snapshot, so an entry deleted in Shopify disappears here too.

_LOADED_AT records when the load last succeeded and is uniform across all rows. Use it, not UPDATED_AT, to judge whether the data is current: UPDATED_AT is Shopify's last-edited time and goes stale whenever organisers simply stop editing the roster.