Połączenie Google Analytics i Shopify: tworzenie hurtowni danych eCommerce w BigQuery

Opublikowany: 2023-02-15

Google Analytics to świetne narzędzie, które pozwala zrozumieć zachowania użytkowników odwiedzających Twoją witrynę. Podczas gdy Shopify jest fantastyczny w zapewnianiu szczegółowego omówienia wydajności z perspektywy handlu elektronicznego. Oba są świetne w tym, co robią, ale przychodzi moment, w którym dane z Shopify i Google Analytics (GA) w izolacji nie zapewniają głębszych informacji, których możesz potrzebować. Chociaż każdy z nich ma zdolność dostarczania potężnych informacji, istnieje znacznie większy poziom wglądu, który można uzyskać, łącząc dane.

W tym konkretnym przykładzie omówimy, jak osiągnęliśmy to dla naszych przyjaciół z Pooch & Mutt, brytyjskiej firmy produkującej naturalną i zdrową karmę dla psów. Chcieliśmy ulepszyć wgląd, biorąc dane zebrane w Google Analytics i wykorzystując je w połączeniu z danymi eCommerce z Shopify, a także danymi subskrypcji z Recharge i łącząc je ze starszymi danymi subskrypcji od Bold. Korzystając z tej różnorodności źródeł danych, moglibyśmy zbudować bogatszy, pełniejszy obraz.

Ten blog będzie starał się przedstawić przegląd tego, w jaki sposób agregujemy te dane, a następnie nakreślić procesy myślowe stojące za łączeniem ich w celu wyodrębnienia i wizualizacji tych spostrzeżeń opartych na danych. W szczególności wyjaśnimy, w jaki sposób odpowiadaliśmy na następujące trzy pytania;

  1. Które kanały zwiększają liczbę rejestracji subskrypcji?
  2. Kombinacje koszyków poza GA; jakie są najpopularniejsze koszyki dla abonentów i nieabonentów i które są najbardziej opłacalne?
  3. Ile czasu i ile zakupów zajmuje średnio użytkownik, zanim zostanie subskrybentem?

Metodologia

Zanim przejdziemy do odpowiedzi na te pytania, omówmy najpierw, w jaki sposób zebraliśmy wszystkie dane z odpowiednich platform w scentralizowaną lokalizację i zbudowaliśmy tabelę, której moglibyśmy użyć do zestawienia tych danych.

Struktura danych

Zdecydowaliśmy się na użycie BigQuery (BQ) do centralizacji danych ze względu na jego wszechstronność i możliwość obsługi kombinacji zestawów danych. Poniższy schemat blokowy przedstawia ogólne powiązania między danymi i sposób, w jaki zostało to osiągnięte:

Szew

Stitch to niedrogie narzędzie, które umożliwia wyodrębnianie i ładowanie danych do gotowych hurtowni danych.

Z łatwością połączyliśmy się z Shopify i Recharge przez Stitch, gdzie po prostu wybraliśmy żądane stoły. W tym przypadku najważniejsze tabele to „klienci” i „subskrypcje” z Recharge oraz „zamówienia” z Shopify.

Informacje te są następnie synchronizowane z BQ co 6 godzin poprzez zautomatyzowany proces planowania Stitch.

Pogrubiony

W przypadku historycznych danych subskrybentów wykonaliśmy ostateczny eksport i przesłaliśmy go do BQ za pośrednictwem Arkuszy Google.

Google Analytics

Chociaż do tego celu można również użyć Stitch, zdecydowaliśmy się użyć RStudio, aby przenieść te dane do BQ. Jeśli chcesz dowiedzieć się więcej o tym, jak to zrobić, możesz przeczytać blog naszego własnego eksperta R Studio, Danny'ego Smitha, na temat korzystania z interfejsu API GA w języku R.

Budowanie tabeli głównej

Kiedy już mieliśmy wszystkie wymagane dane w BQ, mogliśmy zacząć je łączyć. Kroki, które podjęliśmy w tym celu, są następujące:

  1. Połączyliśmy i dopasowaliśmy starsze dane abonentów z Bold z aktualnymi danymi abonentów z Recharge.
  2. Następnie przeanalizowaliśmy dane eCommerce z GA, które obejmują identyfikatory zamówień (np. SPFYXXXXX) wraz ze źródłem, medium i kanałem.
  3. Utworzyliśmy nowe kolumny w oparciu o tabele GA i dane subskrybentów zrzeszonych przed dołączeniem do danych zamówień Shopify. W tym przypadku uwzględniono niektóre z tych dodatkowych kolumn;
    1. Identyfikator abonenta „Aktywny”.
    2. Pierwotna data subskrypcji
    3. Źródło, medium i kanał zamówienia
    4. Pierwsze lub powtórne zamówienie klienta
  4. Następnie wybraliśmy odpowiednie pola z danych zamówienia Shopify, do których chcieliśmy dołączyć te nowe dane. Niektóre kluczowe przykłady to;
    1. Identyfikator klienta
    2. Identyfikator zamówienia
    3. Data zamówienia
    4. SKU
    5. Ilość przedmiotów
    6. Cena przedmiotu
    7. Całkowita cena zamówienia
    8. Kod rabatowy
    9. Kwota rabatu
    10. Tag (używany jako dodatkowa metoda kategoryzowania subskrybentów i osób niebędących subskrybentami)
  5. Ostatnim etapem było tutaj utworzenie kilku dodatkowych kolumn, aby łatwiej radzić sobie z duplikatami wierszy dla różnych elementów w tej samej kolejności;
    1. Średnia cena
    2. Średni rabat
    3. Status subskrybenta (tagi + jeśli są obecne w tabeli Bold & Recharge)

Następnie zaplanowaliśmy uruchamianie tego zapytania raz dziennie i wykorzystaliśmy wynik jako tabelę główną dla dodatkowych zapytań. Dzięki temu nie musieliśmy wielokrotnie uruchamiać pełnego zapytania, ponieważ jest to tabela statyczna, która jest aktualizowana tylko zgodnie z harmonogramem, a nie za każdym razem, gdy wysyłane jest żądanie za pośrednictwem pulpitu nawigacyjnego Looker Studio.

Spostrzeżenia

Teraz, gdy omówiliśmy metodologię i przedstawiliśmy kroki podjęte w celu uzyskania wszystkich potrzebnych danych, możemy przejść do omówienia, w jaki sposób wykorzystaliśmy te dane, aby odpowiedzieć na nasze pytania.

Które kanały zwiększają liczbę rejestracji subskrypcji?

  1. Aby to obliczyć, przyjrzeliśmy się pierwszym zamówieniom złożonym przez klientów, korzystając z pola utworzonego w tabeli głównej, które zwraca wartość „Pierwsze” lub „Powtórz” na podstawie daty pierwszego zamówienia.
  2. Korzystając z dodatkowych kolumn grupowania kanałów i statusu subskrybenta w głównej tabeli, jesteśmy w stanie spojrzeć na pierwsze zamówienia osób, które zostały subskrybentami, czy to przy pierwszym zakupie, czy po ponownym zakupie, oraz do jakiego kanału zostały przypisane w GA .
  3. Rezygnując z etapu patrzenia tylko na subskrybentów, mogliśmy również zobaczyć, które kanały w przeważającej mierze napędzają pierwsze zakupy.

Kombinacje koszyków poza GA; jakie są najpopularniejsze koszyki dla abonentów i nieabonentów, a które są najbardziej opłacalne?

  1. Korzystając z funkcji STRING_AGG, mogliśmy połączyć różne produkty z pojedynczych zamówień w jednym wierszu, oddzielone znakiem „ | '. Zasadniczo tworzy to koszyk dla każdego zamówienia.
  2. Wraz z tymi informacjami zsumowaliśmy przychody z każdej z tych pozycji jako „Suma koszyka”, mając na celu utworzenie wiersza dla każdego zamówienia ze wszystkimi istotnymi informacjami.
  3. Oczywiście istnieje wiele różnic między produktami (smak, rozmiar itp.), dlatego dodaliśmy kolumnę, w której usunięto tę różnicę, aby przyjrzeć się podstawowym kombinacjom koszyka produktów.
  4. Następnie użyliśmy „ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS Identifier”, aby przypisać kolejne wartości do każdego zamówienia. Zostało to następnie użyte jako filtr na pulpicie nawigacyjnym w celu wyizolowania pojedynczych wierszy, które nadal zawierały wszystkie informacje.

Ile czasu i ile zakupów zajmuje średnio użytkownik, zanim zostanie subskrybentem?

  1. Korzystając z naszej głównej tabeli, wyciągnęliśmy wszystkie identyfikatory klientów, o których wiedzieliśmy, że byli subskrybentami i przyjęliśmy datę pierwszej subskrypcji.
  2. Następnie wzięliśmy minimalną datę zamówienia i użyliśmy funkcji DATE_DIFF do obliczenia różnicy między tymi datami w „dniach”.
  3. Ostatni krok obejmował kanał i średnią liczbę dni poprzedzających subskrypcję, w wyniku czego powstała tabela przedstawiająca średnią liczbę dni, po których klienci zazwyczaj stali się subskrybentami.
  4. Aby obliczyć średnią liczbę zamówień, które klienci złożyli, zanim zostali subskrybentami, po prostu podzieliliśmy liczbę unikalnych identyfikatorów zamówień przez liczbę unikalnych identyfikatorów klientów, gdzie byli oni „subskrybentami” i mieli daty zamówień krótsze niż minimalna data subskrypcji.

To tylko niektóre przykłady widoków, które wygenerowaliśmy za pomocą tych raportów. Dane dają nieskończone możliwości – inne przykłady z tego projektu obejmują spojrzenie na wartość życiową klienta z różnych perspektyw oraz ocenę wykorzystania kodów rabatowych i ich wpływu na całe życie klientów. Dzięki tym spostrzeżeniom można je wykorzystać do uzupełniania decyzji biznesowych. Na przykład wiedząc, że zanim zostaniesz subskrybentem, potrzeba średnio 3 zamówień, możesz udostępniać oferty i zachęty, które zachęcą klientów do zostania subskrybentami po 2 zakupach. Wyniki tego można następnie zmierzyć w późniejszym czasie.

Streszczenie

To studium przypadku pokazuje potencjał niezwykle cennych spostrzeżeń opartych na danych, które można uzyskać dzięki połączeniu wielu źródeł danych, od GA po Shopify i nie tylko. Po utworzeniu tej infrastruktury można ją dynamicznie utrzymywać i wykorzystywać do raportowania wielu różnych aspektów działalności.

Jeśli pytania, na które odpowiedzieliśmy na tym blogu, są podobne do tych, które mogą Cię zainteresować w przypadku Twojej własnej firmy, możesz skontaktować się z nami, wypełniając nasz formularz kontaktowy.