The client's team had a sales department, fulfillment specialists (watching stock and replenishment on the channels) and an analyst expected to assemble the whole picture. Before our work each role had its own Excel, and those Excels didn't reconcile. Where it broke, specifically:

• Manual exports from web back-offices. Everyone downloaded their slice of the data at their own time. By meeting time, different people held different cuts — Friday vs Saturday, a morning Shopify pull vs a nightly Amazon one.
• Plans copy-pasted out of the inventory system. Once a week someone exported the plan figures, copied, stitched. By mid-week the plan already lagged reality.
• Lookups and macros as load-bearing structure. One renamed column from a supplier and the spreadsheet fell apart; the analyst spent half a day fixing it. Every data change was a threat.
• Parallel realities. Fulfillment couldn't see ads, sales couldn't see stock, the owner saw their own summary. The whole picture was assembled by hand — 3–5 hours weekly, with discrepancies that never fully closed.

The summary came out late, and the first half-hour of every meeting went to "whose numbers are right" instead of decisions.

Architecture

Channel data is loaded into ClickHouse by a separate process (Python scripts). On top of the raw CH tables sit SQL views that already carry the business logic: correct types, status filters, store joins. Power Query only ever sees those. No CSV parsing on the Excel side.

The ClickHouse connector: fnCH

The key brick of the ETL pipeline is a custom fnCH function. It's a Power Query record that, on workbook open, reads the CH_Sources table straight from an Excel sheet — that's where the keys, users and passwords for each store live.

// fnCH — a record with ClickHouse connection methods
let
    Sources = Excel.CurrentWorkbook(){[Name="CH_Sources"]}[Content],
    Host    = "[IP:8123]",   // CH HTTP interface address

    fnFetchSql = (key as text, sql as text) as table =>
        let
            src     = Table.SelectRows(Sources, each [Key] = key){0},
            sqlFull = sql & " FORMAT CSVWithNames",
            data    = Table.PromoteHeaders(Csv.Document(
                Web.Contents(Host, [
                    Query = [user = src[User], password = src[Password],
                             query = sqlFull]
                ])
            ))
        in data,

    fnFetch = (key as text, fullTable as text) =>
        fnFetchSql(key, "SELECT * FROM " & fullTable),

    fnCombineTagged = (mapping as list) =>
        Table.Combine(List.Transform(mapping,
            each let src = Table.SelectRows(Sources, each [Key] = _{0}){0}
                 in Table.AddColumn(fnFetch(_{0}, _{1}),
                                    "Store", each src[Model label], type text)))
in
    [ Fetch = fnFetch, FetchSql = fnFetchSql, CombineTagged = fnCombineTagged ]

With that constructor, adding a new store to the report is one line:

// Shopify_Sales — straight from CH, all stores at once
Source = fnCH[CombineTagged]({
    {"[Store A]", "[db].[view_A]"},
    {"[Store B]", "[db].[view_B]"},
    {"[Store C]", "[db].[view_C]"}
})

Plans via Google Drive

SKU plans are kept in online sheets — convenient for the team: collaborative editing, access from any device. Power Query reads them as an XLSX file on Google Drive via a public link — the file downloads on every refresh.

// GD_Excel_Workbook — download the Drive XLSX once,
// reused by both "Plans" and "Price template"
let
    FileId   = "https://drive.google.com/uc?id=...",
    Workbook = Excel.Workbook(Web.Contents(FileId), true)
    // downstream: navigate to the named sheet, promote headers
in Workbook

The GD_Excel_Workbook query is factored out: both plans and the price template reference it, so the Drive file downloads exactly once per refresh. Plans are organized by four dimensions: product code × date (the 1st of the month) × channel × type (units / USD).

OMS: stock, landed cost, shipments

Own-warehouse data is pulled from the OMS along two paths:

• Through ClickHouse — for daily stock snapshots (a Python script loads CH, then PQ reads it).
• Directly via the REST API — for wholesale shipments: fnOMS_ShipmentsExpanded requests shipments by warehouse UUID with pagination.

The Power Pivot data model

The model is a classic star. Central lookups (the "one" side) filter every fact table (the "many" side):

Key fact tables: Shopify_Sales (~650k rows), Amazon_Transactions (~500k rows), funnel aggregates, ad statistics, daily stock.

Key DAX measures

296 measures in all. A few characteristic ones:

ACoS — from profit, not revenue:

-- Shopify ACoS (of profit), %
IF([Actual profit, Shopify],
    DIVIDE(
        [ALL_Ad_Spend] - [Shopify ad tax],
        [Actual profit, Shopify] + ([ALL_Ad_Spend] - [Shopify ad tax])
    )
)

This isn't "ad spend as a share of revenue" but "how much ads ate of profit" — fundamentally different numbers when fees and cost of goods are high.

Actual profit, Shopify — layered assembly:

-- Actual profit, Shopify
[Shopify net revenue after fees and payment, USD]
- (
    [Shopify COGS, USD]
    + [Shopify all costs except taxes and fees]
    + [Shopify fixed internal costs]
    + [Tax, sales tax in sales, Shopify]
    + [Tax, income, Shopify]
    + [Tax, fulfillment, Shopify]
    + [Tax, payment, Shopify]
)
+ [Tax, VAT included in services, Shopify]
+ [Tax, VAT included in COGS, Shopify]

Each cost line is its own measure. You can enter any cut and see exactly what's pressing on profit: channel fee, taxes, logistics, ads. Not "profit fell", but the specific cause.

Plans with daily distribution:

-- Plans_Shopify (with daily values)
-- The monthly plan is split across the actual selling days in the month
SUMX(
    SUMMARIZE('Dim_Calendar', [Year], [Month]),
    VAR MonthStart   = DATE([Year], [Month], 1)
    VAR DaysInMonth  = DAY(EOMONTH(MonthStart, 0))
    VAR MonthlyPlan  =
        CALCULATE(
            SUM('Plans'[Value]),
            'Plans'[Channel] = "Shopify",
            'Plans'[Plan type] = "Units",
            'Plans'[Date] = MonthStart,
            ALL('Dim_Calendar')
        )
    VAR DailyPlan     = DIVIDE(MonthlyPlan, DaysInMonth, 0)
    VAR DaysInContext = COUNTROWS('Dim_Calendar')
    RETURN DailyPlan * DaysInContext
)

The measure computes the cumulative plan to the current day — not the whole month. Look on the 15th: the plan is "trimmed" exactly to the 15th, so the comparison to actual is correct without manual adjustment.