Traktanalyse med SQL i warehouse-native produktanalyse

Å analysere og forstå salgstrakter er en viktig ferdighet for enhver produktsjef (PM). Det hjelper dem å forstå og optimalisere produkt- og funksjonsbruk, ved å gjøre det mulig for dem å spore og visualisere kundereisen fra anskaffelse, via aktivering, retensjon, henvisning og inntekter; eller brukerens underreiser gjennom enhver arbeidsflyt i produktet – samtidig som konverterings- og frafallsrater fremheves underveis. Ved hjelp av traktanalyse kan PM-er definere eventuelle suksesskriterier og spore trinnene en bruker tar for å nå suksesskriteriene i tilfelle konvertering, eller punktet i produktet der brukeren avbrøt arbeidsflyten, i tilfelle et frafall.

Denne typen analyse er ganske forskjellig fra forretningsanalyse, som aggregerer målinger på et sett med dimensjonale attributter for å forstå forretningsresultater. Basert på mine over 10 års erfaring med å bygge høytytende databaser, mener jeg imidlertid at **moderne datavarehus er godt rustet til å effektivt utføre slike (faktisk all produktanalyse) arbeidsbelastninger i stor skala**.

I dette innlegget skal jeg dykke dypt ned i det indre av beregningen som ligger bak beregning av trakter, og detaljere hvordan moderne datavarehus kan utføre traktspørringer i stor skala.

Hva er traktanalyse?

Wikipedia definerer **traktanalyse** som kartlegging og analyse av en serie hendelser som fører mot et definert mål, som en reise fra annonse til kjøp i nettannonsering. Eksemplet nedenfor følger konverteringen av brukere til «betalt aktivering» etter å ha tatt opplæringskurs på en nettbasert læringsplattform – 18,6 % av brukerne tar «analysekurset» etter «Komme i gang»; og av disse konverterer 31 % av brukerne til «betalt aktivering», for en total konvertering på 5,8 %.

En prosjektleder som ønsker å øke engasjementet, kan se på konverterings- og frafallsrater mellom ulike stadier og bestemme seg for å fokusere på å forbedre konverteringen mellom «Kom i gang» og «Analysekurs» for å øke «Betalt aktivering».

Hvordan bygge en trakt

Konseptuelt sett følger en trakt en ordnet sekvens av hendelser etter «event_time» for hver bruker for å beregne det lengste traktstadiet de har nådd. Det finnes flere måter å uttrykke denne beregningen på ved hjelp av SQL. Vi vil dekke to slike mønstre og gi innsikt i ytelsen for begge. Men før vi hopper til SQL, la oss konkret definere datamodellen som brukes for denne analysen.

Datamodell

La oss anta et skjema med en enkelt «Hendelser»-tabell. Dataene i «Hendelser» kan tenkes å ha følgende form:

event_name	event_time	user_id	OS	nettleser
Komme i gang	0	user_id0	ios	safari
Analyse Kurs	1	bruker_id0	ios	safari
Analytics-kurs	1	bruker_id1	android	chrome
Betalt aktivering	4	bruker_id0	ios	safari

Jeg har gjort to forenklende antagelser i denne modellen, men som jeg forklarer nedenfor, er ingen av disse antagelsene et problemområde for moderne datavarehus.

Strukturerte data - "Hendelser" antas å være en strukturert tabell, selv om hendelsesdata nesten alltid er semistrukturerte. Se min forrige blogg for å forstå hvordan datavarehus håndterer semistrukturerte data effektivt.
Ingen sammenføyninger - Første generasjons verktøy som Amplitude og Mixpanel modellerer skjemaet sitt som en enkelt hendelsesstrøm. Denne modellen kan være svært restriktiv, spesielt siden enhver analyse i den virkelige verden krever forretningskontekst fra andre kilder. I motsetning til førstegenerasjons produktanalyseverktøy som Amplitude og Mixpanel, som tilbyr ekstremt begrensede spørringsmuligheter, er moderne datalagre bygget for å koble sammen flere tabeller effektivt.

Funnel Query

Underspørring i fase 1 sender ut unike bruker-ID-er som oppstod på hendelsen «Komme i gang»
Underspørring i fase 2 kobler seg til tabellen «Hendelser» med utdata fra fase 1 for å sende ut individuelle bruker-ID-er som nådde hendelsen «Analytics Course» etter hendelsen «Komme i gang»
Underspørring i fase 3 kobler seg til tabellen «Hendelser» med utdata fra fase 2 for å sende ut unike bruker-ID-er som nådde hendelsen «Betalt aktivering» etter hendelsene «Komme i gang» og «Analytics Course», i den rekkefølgen
Utdata fra alle tre fasene aggregeres for å telle antall unike brukere i hvert fase

Moderne datavarehus har ekstremt sofistikerte spørreplanleggere for å optimalisere relasjonstrær. Det er to optimaliseringer i planen ovenfor som er verdt å nevne:

Beregning for hvert trinn skjer én gang - Optimalisatoren tar ut overflødige beregninger i planen som delplaner eller fragmenter for å gjenbruke resultater på tvers av spørringen. De beste motorene i sin klasse planlegger delplaner adaptivt for å bruke resultater og statistikk for videre planlegging.
Aggregering i "Endelig utdata" skyves under sammenføyningen - De beste optimalisatorene i sin klasse bør kunne utlede at aggregering kan skyves under venstre sammenføyning for mange-til-én-sammenføyning, som her, for å eliminere kostnadene for kostbar venstre sammenføyning mellom de tre trinnene. Å lage en spørring som aggregerer før sammenføyning er også et levedyktig alternativ for en analysemotor i tilfelle optimalisatoren ikke klarer å optimalisere for dette mønsteret.

Aggregerings- og sammenføyningsoperasjoner er basisløsninger for moderne datavarehus, slik at slike spørringer kan utføres effektivt i stor skala med massiv parallellitet. Det finnes imidlertid fortsatt et par forbedringer som kan forbedre spørringsytelsen ytterligere:

Koble til mellom delspørringer – Koble til mellom delspørringer er dyrere enn basistabellkoblinger, siden delspørringer ikke har forhåndsberegnet koblingsindekser. I vårt eksempel kobles «Hendelser» og hver trinnutgang sammen på brukerens intervall, noe som kan være kostbart for brukere med høy kardinalitet. Merk at krysskoblingen i den endelige spørringen er ganske billig, siden den kobler sammen nøyaktig én rad fra hver inngang.
Flere skanninger – Selv etter at redundant beregning er fjernet, skannes «Hendelser»-tabellen tre ganger, én for hver delspørring. En raskere og mer effektiv algoritme ville skannet «Hendelser»-tabellen bare én gang.

Spørring om stablede vindusfunksjoner

En alternativ tilnærming for å deklarere denne beregningen er å bruke vindusfunksjoner. Vindusfunksjoner muliggjør analyse av imperativ hendelsessekvensstil via det deklarative SQL-grensesnittet. I denne tilnærmingen lager vi en stabel med vindusfunksjoner (partisjonert over), én for hvert trinn i trakten, etterfulgt av et forskjellig antall «user_ids» øverst i denne stabelen. Vi kaller dette mønsteret for Stacked Window Functions-mønsteret. Her er SQL-en for å opprette den samme trakten ved hjelp av Stacked Window Functions.

Som før gjør spørreplanleggeren i de fleste moderne datavarehus to kritiske optimaliseringer for forbedret ytelse av planen ovenfor:

Enkel sortering på tvers av Windows - Alle vindusfunksjoner er partisjonert på "user_id" og sortert på "event_time". I et slikt tilfelle oppretter optimalisatoren en enkelt sorteringsoperator som mater data i ønsket rekkefølge til alle vindusfunksjoner.
Lokal distinkt aggregering - Spørringen har en distinkt aggregering på "user_id". Denne aggregeringen kan utføres innenfor en partisjon (lokalt) hvis data er forhåndspartisjonert på "user_id", siden brukerbasert partisjonering garanterer at brukere ikke trenger å aggregeres på tvers av partisjoner.

Begge optimaliseringene som er oppført ovenfor, er standard i de fleste moderne datavarehus. Denne formuleringen av traktspørringen har ingen av manglene til Join Sequence-stilen til traktspørringen. Det er imidlertid ett problem som må løses. Sort-operatoren oppå Events-skanningen kan virke dyr å beregne. Heldigvis er det ikke et krav at denne sorteringen er global; sortering lokalt innenfor partisjonen er tilstrekkelig siden vindusoperatoren krever sortert input per bruker og ikke på tvers av brukere.

Det er potensial for å optimalisere denne spørringen ytterligere. De fleste datavarehus tilbyr en knapp for å klynge tabeller ved hjelp av en brukerdefinert klyngenøkkel. Klyngenøkkel er et sett med kolonner eller kolonneuttrykk som brukes til å plassere rader i en tabell nær hverandre for forbedret ytelse. Det har flere fordeler å gruppere «Hendelser»-tabellen etter «hendelsestidspunkt»:

Reduser (kan til og med eliminere) kostnaden for sorteringsoperatoren hvis dataene nesten (eller fullstendig) er sortert etter hendelsestidspunkt
Forbedre skanneytelsen på «Hendelser» for spørringer med tidsperiodefiltre i «hendelsestidspunkt»-kolonnen

Konklusjon

I denne bloggen presenterte vi to forskjellige formuleringer av SQL for traktanalyse og analyserte deres respektive spørreplaner for å forstå ytelsesprofilen deres i moderne datavarehus.

Førstegenerasjonsverktøy, som Mixpanel og Amplitude, har en utdatert arkitektur som krever dataflytting, skaper datasiloer og duplisering, og er også uoverkommelig dyre i stor skala. Moderne datalagre har modnet til et punkt der de kan tilby en interaktiv opplevelse for produktanalysearbeidsbelastninger uten disse manglene og fleksibiliteten til justerbare kostnader for ønskelig ytelse.