Հաճախորդի թիմն ուներ վաճառքի բաժին, fulfillment-ի մասնագետներ (հետևում էին մնացորդներին և համալրմանն ալիքներում) և վերլուծաբան, որից սպասվում էր ամբողջ պատկերը հավաքել։ Մինչ մեր աշխատանքը՝ յուրաքանչյուր դերն ուներ իր Excel-ը, և այդ Excel-ները չէին համընկնում։ Որտեղ էր կոտրվում, կոնկրետ՝

• Ձեռքով արտահանումներ վեբ-կաբինետներից։ Ամեն մեկը ներբեռնում էր իր կտորը՝ իր ժամին։ Հանդիպման պահին տարբեր մարդիկ ունեին տարբեր կտրվածքներ՝ ուրբաթ vs շաբաթ, Shopify-ի առավոտյան ներբեռնում vs Amazon-ի գիշերային։
• Պլանները կրկնօրինակ-տեղադրմամբ՝ պահեստի համակարգից։ Շաբաթը մեկ ինչ-որ մեկն արտահանում էր պլանի թվերը, պատճենում, կարում։ Շաբաթվա կեսին պլանն արդեն հետ էր մնում իրականությունից։
• Փնտրումները և մակրոները՝ որպես կրող կառույց։ Մատակարարի մեկ վերանվանված սյունակ՝ և աղյուսակը փլվում էր, վերլուծաբանը կես օր ուղղում էր այն։ Տվյալների ամեն փոփոխություն սպառնալիք էր։
• Զուգահեռ իրականություններ։ Fulfillment-ը չէր տեսնում գովազդը, վաճառքները՝ մնացորդը, սեփականատերը տեսնում էր իր ամփոփումը։ Ամբողջ պատկերը հավաքվում էր ձեռքով՝ շաբաթական 3–5 ժամ, անհամապատասխանություններով, որոնք երբեք ամբողջությամբ չէին փակվում։

Ամփոփումը դուրս էր գալիս ուշ, և ամեն հանդիպման առաջին կես ժամը գնում էր «ո՞ւմ թվերն են ճիշտ»-ին՝ որոշումների փոխարեն։

Ճարտարապետություն

Ալիքների տվյալները ClickHouse են բեռնվում առանձին գործընթացով (Python-սկրիպտներ)։ Չմշակված CH-աղյուսակների վրա նստած են SQL-պատկերներ, որոնք արդեն կրում են բիզնես-տրամաբանությունը՝ ճիշտ տիպեր, կարգավիճակների ֆիլտրեր, խանութների միացումներ։ Power Query-ն տեսնում է միայն դրանք։ Excel-ի կողմում CSV-ի վերլուծություն չկա։

ClickHouse-ի միակցիչը՝ fnCH

ETL-խողովակի առանցքային աղյուսը՝ սեփական fnCH ֆունկցիան է։ Սա Power Query-ի գրառում է, որը ֆայլը բացելիս կարդում է CH_Sources աղյուսակը հենց Excel-թերթից՝ այնտեղ ապրում են բանալիները, օգտատերերն ու գաղտնաբառերը՝ ամեն խանութի համար։

// fnCH — ClickHouse-ի միացման մեթոդներով գրառում
let
    Sources = Excel.CurrentWorkbook(){[Name="CH_Sources"]}[Content],
    Host    = "[IP:8123]",   // CH HTTP ինտերֆեյսի հասցե

    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 ]

Այդ կառուցիչով հաշվետվությանը նոր խանութ ավելացնելը մեկ տող է՝

// Shopify_Sales — ուղիղ CH-ից, բոլոր խանութները միանգամից
Source = fnCH[CombineTagged]({
    {"[Խանութ A]", "[db].[view_A]"},
    {"[Խանութ B]", "[db].[view_B]"},
    {"[Խանութ C]", "[db].[view_C]"}
})

Պլանները Google Drive-ով

SKU-ների պլանները պահվում են առցանց աղյուսակներում՝ հարմար թիմի համար. համատեղ խմբագրում, հասանելիություն ցանկացած սարքից։ Power Query-ն դրանք կարդում է որպես Google Drive-ի վրա XLSX-ֆայլ՝ հանրային հղումով՝ ֆայլը ներբեռնվում է ամեն թարմացման ժամանակ։

// GD_Excel_Workbook — Drive-ի XLSX-ը ներբեռնել մեկ անգամ,
// օգտագործվում է թե՛ «Պլանների», թե՛ «Գնապատկերի» կողմից
let
    FileId   = "https://drive.google.com/uc?id=...",
    Workbook = Excel.Workbook(Web.Contents(FileId), true)
    // հետո՝ նավարկել դեպի անվանված թերթ, բարձրացնել վերնագրերը
in Workbook

GD_Excel_Workbook հարցումը առանձնացված է. թե՛ պլանները, թե՛ գնապատկերը հղվում են դրան, այնպես որ Drive-ֆայլը ներբեռնվում է ճիշտ մեկ անգամ՝ ամեն թարմացմանը։ Պլանները կազմակերպված են չորս չափումով՝ ապրանքի կոդ × ամսաթիվ (ամսվա 1-ին) × ալիք × տեսակ (հատ / ֏)։

OMS՝ մնացորդ, ինքնարժեք, առաքումներ

Սեփական պահեստի տվյալները OMS-ից քաշվում են երկու ճանապարհով՝

• ClickHouse-ի միջոցով — օրական մնացորդի կտրվածքների համար (Python-սկրիպտը բեռնում է CH, հետո PQ-ն կարդում է այն)։
• Ուղիղ REST API-ով — մեծածախ առաքումների համար. fnOMS_ShipmentsExpanded-ը հարցում է առաքումներն ըստ պահեստի UUID-ի՝ էջատմամբ։

Power Pivot-ի տվյալների մոդելը

Մոդելը դասական աստղ է։ Կենտրոնական փնտրումները («մեկ» կողմը) ֆիլտրում են ամեն փաստ-աղյուսակ («շատ» կողմը)՝

Հիմնական փաստ-աղյուսակները՝ Shopify_Sales (~650հզ տող), Amazon_Transactions (~500հզ տող), ձագարի ագրեգատներ, գովազդի վիճակագրություն, օրական մնացորդ։

Հիմնական DAX-չափիչներ

Ընդամենը 296 չափիչ։ Մի քանի բնորոշ՝

ACoS — շահույթից, ոչ թե հասույթից՝

-- Shopify ACoS (շահույթից), %
IF([Փաստ շահույթ, Shopify],
    DIVIDE(
        [ALL_Ad_Spend] - [Shopify ad tax],
        [Փաստ շահույթ, Shopify] + ([ALL_Ad_Spend] - [Shopify ad tax])
    )
)

Սա «գովազդի ծախսը՝ որպես հասույթի մաս» չէ, այլ «որքա՞ն գովազդը կերավ շահույթից»՝ սկզբունքորեն տարբեր թվեր, երբ վճարներն ու ինքնարժեքը բարձր են։

Փաստ շահույթ, Shopify — շերտավոր հավաքում՝

-- Փաստ շահույթ, Shopify
[Shopify զուտ հասույթ վճարներից և վճարումից հետո, ֏]
- (
    [Shopify COGS, ֏]
    + [Shopify բոլոր ծախսերը՝ բացի հարկերից և վճարներից]
    + [Shopify ֆիքսված ներքին ծախսեր]
    + [Հարկ, վաճառքի հարկ վաճառքում, Shopify]
    + [Հարկ, եկամուտ, Shopify]
    + [Հարկ, fulfillment, Shopify]
    + [Հարկ, վճարում, Shopify]
)
+ [Հարկ, ԱԱՀ ներառված ծառայություններում, Shopify]
+ [Հարկ, ԱԱՀ ներառված COGS-ում, Shopify]

Ծախսի ամեն տող իր չափիչն է։ Կարող եք մտնել ցանկացած կտրվածք և տեսնել, թե կոնկրետ ինչն է ճնշում շահույթին՝ ալիքի վճար, հարկեր, լոգիստիկա, գովազդ։ Ոչ թե «շահույթն ընկավ», այլ կոնկրետ պատճառը։

Պլանները՝ օրական բաշխմամբ՝

-- Plans_Shopify (օրական արժեքներով)
-- Ամսական պլանը բաշխվում է ամսվա փաստացի վաճառքի օրերին
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
)

Չափիչը հաշվում է կուտակային պլանը մինչ ընթացիկ օրը՝ ոչ թե ամբողջ ամիսը։ Նայեք 15-ին՝ պլանը «կտրված» է ճիշտ մինչ 15-ը, որպեսզի փաստի հետ համեմատումը ճիշտ լինի առանց ձեռքով ճշգրտման։