76 lines
2.2 KiB
Plaintext
76 lines
2.2 KiB
Plaintext
RESUM
|
|
A1: Metrica
|
|
B1: Valor
|
|
A2: Sessions uniques
|
|
B2: =COUNTUNIQUE(FILTER(EVENTS!J2:J,EVENTS!J2:J<>""))
|
|
A3: Page views
|
|
B3: =COUNTIF(EVENTS!B:B,"page_view")
|
|
A4: Veure gran
|
|
B4: =COUNTIF(EVENTS!B:B,"view_large")
|
|
A5: Clicks WhatsApp
|
|
B5: =COUNTIF(EVENTS!B:B,"whatsapp_click")
|
|
A6: Clicks Comprar
|
|
B6: =COUNTIF(EVENTS!B:B,"buy_click")
|
|
A7: Checkout submit
|
|
B7: =COUNTIF(EVENTS!B:B,"checkout_submit")
|
|
A8: Compres pagades
|
|
B8: =COUNTIF(EVENTS!B:B,"payment_success")
|
|
A9: CTR comprar
|
|
B9: =IF(B3=0,0,B6/B3)
|
|
A10: Checkout rate
|
|
B10: =IF(B6=0,0,B7/B6)
|
|
A11: Conversio final
|
|
B11: =IF(B3=0,0,B8/B3)
|
|
A12: Checkout -> paid
|
|
B12: =IF(B7=0,0,B8/B7)
|
|
A13: Facturacio
|
|
B13: =IFERROR(SUM(FILTER(ORDERS!I2:I,ORDERS!J2:J="paid")),0)
|
|
A14: Ticket mitja
|
|
B14: =IF(B8=0,0,B13/B8)
|
|
|
|
FUNNEL
|
|
A1: Pas
|
|
B1: Valor
|
|
C1: Ratio vs pas anterior
|
|
A2: page_view
|
|
B2: =COUNTIF(EVENTS!B:B,"page_view")
|
|
C2:
|
|
A3: buy_click
|
|
B3: =COUNTIF(EVENTS!B:B,"buy_click")
|
|
C3: =IF(B2=0,0,B3/B2)
|
|
A4: checkout_submit
|
|
B4: =COUNTIF(EVENTS!B:B,"checkout_submit")
|
|
C4: =IF(B3=0,0,B4/B3)
|
|
A5: payment_success
|
|
B5: =COUNTIF(EVENTS!B:B,"payment_success")
|
|
C5: =IF(B4=0,0,B5/B4)
|
|
|
|
TOP_MODELS
|
|
A1: product_code
|
|
B1: view_large
|
|
C1: whatsapp_clicks
|
|
D1: buy_clicks
|
|
E1: checkout_submit
|
|
F1: payment_success
|
|
G1: conversion_rate
|
|
H1: revenue
|
|
A2: =SORT(UNIQUE(FILTER(EVENTS!C2:C,EVENTS!C2:C<>"")))
|
|
B2: =ARRAYFORMULA(IF(A2:A="","",COUNTIFS(EVENTS!C:C,A2:A,EVENTS!B:B,"view_large")))
|
|
C2: =ARRAYFORMULA(IF(A2:A="","",COUNTIFS(EVENTS!C:C,A2:A,EVENTS!B:B,"whatsapp_click")))
|
|
D2: =ARRAYFORMULA(IF(A2:A="","",COUNTIFS(EVENTS!C:C,A2:A,EVENTS!B:B,"buy_click")))
|
|
E2: =ARRAYFORMULA(IF(A2:A="","",COUNTIFS(EVENTS!C:C,A2:A,EVENTS!B:B,"checkout_submit")))
|
|
F2: =ARRAYFORMULA(IF(A2:A="","",COUNTIFS(EVENTS!C:C,A2:A,EVENTS!B:B,"payment_success")))
|
|
G2: =ARRAYFORMULA(IF(A2:A="","",IF(D2:D=0,0,F2:F/D2:D)))
|
|
H2: =ARRAYFORMULA(IF(A2:A="","",SUMIF(ORDERS!D:D,A2:A,ORDERS!I:I)))
|
|
|
|
FILTERS
|
|
A1: filter_key
|
|
B1: uses
|
|
A2: =SORT(UNIQUE(FILTER(EVENTS!E2:E,EVENTS!B2:B="filter_use",EVENTS!E2:E<>"")))
|
|
B2: =ARRAYFORMULA(IF(A2:A="","",COUNTIFS(EVENTS!B:B,"filter_use",EVENTS!E:E,A2:A)))
|
|
|
|
Notes
|
|
- Si el teu Google Sheets et dona error de sintaxi, substitueix les comes "," per punt i coma ";".
|
|
- Formata B9:B12, C3:C5 i G2:G com a percentatge.
|
|
- Formata B13:B14 i H2:H com a moneda EUR.
|