Cohort retention dashboard με Looker Studio & BigQuery

Το standard interface του GA4 υπόσχεται cohort exploration, αλλά στην πράξη για ένα e-commerce κατάστημα είναι σχεδόν άχρηστο: δεν επιτρέπει δυναμικό grouping με custom business metrics, μπλέκει τα web sessions με app events και έχει όριο στις 30 ημέρες ή 12 εβδομάδες χωρίς ευελιξία. Αν θέλουμε πραγματικό cohort analysis Looker Studio dashboard που να δείχνει πώς συμπεριφέρονται οι πελάτες μας μήνα με τον μήνα, η μόνη σοβαρή λύση είναι να τραβήξουμε τα raw GA4 data στο BigQuery και να υπολογίσουμε τα cohorts απευθείας στη βάση.

TL;DR:

• Το native GA4 UI αποτυγχάνει στο LTV και retention reporting λόγω ορίων στα date ranges και cookie decay.
• Γράφουμε ένα BigQuery SQL query που εντοπίζει τον μήνα πρώτης αγοράς κάθε πελάτη (cohort_month) και μετράει τις επαναληπτικές παραγγελίες στους επόμενους μήνες.
• Συνδέουμε το query ως Custom Query στο Looker Studio και δημιουργούμε ένα Pivot Table heatmap για άμεση εποπτεία του customer retention rate.

Γιατί τα raw data στο BigQuery είναι απαραίτητα για το ecommerce customer retention

Όταν αναλύουμε το ecommerce customer retention, το μεγαλύτερο πρόβλημα στο default GA4 είναι η ταυτοποίηση του χρήστη. Λόγω ITP στο Safari και ad-blockers, το user_pseudo_id (cookie) μηδενίζεται συχνά μέσα σε 7 ημέρες. Αν βασιστείς μόνο στα visual reports του GA4, ένας πιστός πελάτης που επιστρέφει μετά από δύο μήνες καταγράφεται ως νέος χρήστης.

Στα projects μας με απαιτητικό advanced analytics setup, ενεργοποιούμε πάντα το BigQuery export και αξιοποιούμε το user_id από το backend (π.χ. hashed customer ID από Shopify ή WooCommerce) ή το transaction_id. Έτσι παρακάμπτουμε τους περιορισμούς του browser storage και έχουμε ακριβή εικόνα για το ποιος αγόρασε, πότε αγόρασε για πρώτη φορά και πότε επέστρεψε για δεύτερη ή τρίτη παραγγελία.

Αν δεν αναλύεις τα cohorts σε επίπεδο παραγγελίας και customer ID στο BigQuery, δεν μετράς πραγματικό retention, αλλά το πόσο συχνά διαγράφονται τα cookies των χρηστών σου.

Το BigQuery SQL query για τον υπολογισμό των cohorts

Για να τροφοδοτήσουμε το dashboard μας, χρειαζόμαστε ένα query που εκτελείται πάνω στα daily export tables (events_*). Το query βρίσκει την πρώτη ημερομηνία αγοράς κάθε πελάτη, ορίζει το cohort του και υπολογίζει τη διαφορά σε μήνες (index) για κάθε επόμενη αγορά.

Ακολουθεί το SQL template που χρησιμοποιούμε:

WITH user_orders AS (
  SELECT
    -- Προτιμούμε user_id αν υπάρχει, αλλιώς user_pseudo_id
    COALESCE(user_id, user_pseudo_id) AS customer_id,
    PARSE_DATE('%Y%m%d', event_date) AS order_date,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
    event_value_in_usd AS order_value
  FROM
    `your-project-id.analytics_xxxxxxxxx.events_*`
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
),

first_purchase AS (
  SELECT
    customer_id,
    MIN(order_date) AS first_order_date,
    DATE_TRUNC(MIN(order_date), MONTH) AS cohort_month
  FROM
    user_orders
  GROUP BY
    customer_id
),

cohort_activity AS (
  SELECT
    uo.customer_id,
    fp.cohort_month,
    DATE_TRUNC(uo.order_date, MONTH) AS order_month,
    DATE_DIFF(DATE_TRUNC(uo.order_date, MONTH), fp.cohort_month, MONTH) AS month_number,
    uo.order_value
  FROM
    user_orders uo
  JOIN
    first_purchase fp ON uo.customer_id = fp.customer_id
)

SELECT
  cohort_month,
  month_number,
  COUNT(DISTINCT customer_id) AS active_customers,
  SUM(order_value) AS total_revenue,
  COUNT(DISTINCT transaction_id) AS total_orders
FROM
  cohort_activity
GROUP BY
  cohort_month,
  month_number
ORDER BY
  cohort_month ASC,
  month_number ASC;

Σύμφωνα με τα επίσημα Google Analytics BigQuery export specs, το unnesting των event parameters είναι απαραίτητο για να απομονώσουμε το transaction ID και τα σχετικά e-commerce values.

Πώς στήνουμε το retention matrix dashboard στο Looker Studio

Αφού ελέγξουμε ότι το query επιστρέφει σωστά δεδομένα στην κονσόλα του BigQuery, περνάμε στο κομμάτι του visual representation στο Looker Studio.

Τα βήματα για την υλοποίηση:

1. Προσθήκη Data Source: Στο Looker Studio, επιλέγουμε Create > Data Source > BigQuery > Custom Query. Επιλέγουμε το Google Cloud billing project μας και κάνουμε επικόλληση το SQL query παραπάνω.
2. Ρύθμιση πεδίων: Βεβαιωνόμαστε ότι το πεδίο cohort_month έχει τύπο Date (Year Month) και το month_number είναι Number.
3. Δημιουργία Pivot Table: Προσθέτουμε ένα νέο chart τύπου Pivot Table with Heatmap στον καμβά του report.
4. Ρύθμιση διαστάσεων:
   • Row dimension: cohort_month (ταξινόμηση αύξουσα ή φθίνουσα ανάλογα με την προτίμηση).
   • Column dimension: month_number (ταξινόμηση αύξουσα: 0, 1, 2, 3...).
   • Metric: active_customers (ή υπολογιζόμενο πεδίο για retention percentage).

Για να εμφανίζεται το ποσοστό διατήρησης αντί για τον απόλυτο αριθμό πελατών, δημιουργούμε ένα custom calculated field στο Looker Studio ή υπολογίζουμε το ποσοστό απευθείας με window functions μέσα στο SQL (COUNT(DISTINCT customer_id) / MAX(cohort_size) OVER(PARTITION BY cohort_month)). Αυτό το στήσιμο αποτελεί βασικό πυλώνα στο custom looker studio reporting που παραδίδουμε σε e-commerce brands.

Συχνές ερωτήσεις

Πώς συνδέω το BigQuery με το Looker Studio χωρίς επιπλέον κόστος;

Η απευθείας σύνδεση Custom Query στο Looker Studio χρησιμοποιεί το δωρεάν tier του BigQuery (1 TB queries/μήνα). Για να μην αυξάνεται το κόστος σε μεγάλα e-commerce sites, αποθηκεύουμε το αποτέλεσμα του query σε έναν ενδιάμεσο scheduled table στο BigQuery μία φορά την ημέρα και συνδέουμε το Looker Studio σε αυτόν.

Γιατί τα νούμερα του BigQuery διαφέρουν από το GA4 UI;

Το GA4 UI εφαρμόζει thresholding, Google Signals και HLL estimations σε εκτιμήσεις μοναδικών χρηστών, ενώ ταυτόχρονα φιλτράρει data με βάση το consent state. Το BigQuery περιέχει τα ασυμπίεστα raw events, δίνοντας 100% ακριβή αριθμητικά αποτελέσματα για τις συναλλαγές που καταγράφηκαν.

Πώς υπολογίζω το repeat purchase rate στο Looker Studio;

Δημιουργούμε ένα Calculated Field που διαιρεί τον αριθμό των πελατών του Month 1 (ή μεταγενέστερων) με τους πελάτες του Month 0 για το ίδιο cohort. Ο τύπος υπολογίζει άμεσα το ποσοστό των χρηστών που επέστρεψαν για δεύτερη αγορά εντός 30-60 ημερών.

Χωρίς direct πρόσβαση στα raw transaction data, οποιοδήποτε retention report καταλήγει να βασίζεται σε υποθέσεις. Στήνοντας τη διαδικασία στο BigQuery εξασφαλίζουμε πλήρη έλεγχο των business rules και ένα Looker Studio dashboard που ενημερώνεται αυτόματα κάθε πρωί.