01Semaine type data analyst — Demandes ad hoc / Dashboards / Comité / Support métier

E-commerçant 45k commandes/an (8k clients actifs, 2.5k références, 5M€ CA). Data analyst : 2–4 ans expérience SQL/Excel/Power BI. Semaine : 25% demandes ad hoc managers (urgence, réponse 4–24h), 30% dashboards automatis (création/maintenance, 8–10 dashboards), 25% comité mensuel (préparation 16h, présentation 1h), 15% support métier (formation utilisateurs, data governance, nettoyage données). Équipe : Directeur général (vision 3–5 ans, décisions stratégiques 50M€+), Directeur commercial (ventes, conversion, clients top), Directeur e-commerce (conversion site, AOV panier, stock), Directeur finance (marge, COGS, trésorerie). Database: PostgreSQL/MySQL (commandes, clients, produits, interactions site). Outils: DBeaver/pgAdmin SQL editor, Excel, Power BI Desktop + cloud service, Slack notifications, Git version control requêtes.

Lundi 9h–12h — Intake demandes ad hoc + dashboards urgent

• 9h00–9h30 — Daily standup équipe data (Slack + 15 min call). Tickets ouverts dimanche/lundi matin (priorité). Exemple semaine : (1) Dir commercial « Pourquoi taux conversion baisse semaine de -2.1% (35.2% → 33.1%)? Urgent comité mercredi. » Priorité HIGH, délai 24h. (2) Dir e-commerce « Dashboard mobile vs desktop conversion — créer? » Priorité MEDIUM, délai mercredi. (3) Finance « Audit COGS produit top 50 (coûts unitaires vérifié?). » Priorité MEDIUM, délai vendredi. Assignments: (1) = toi-même lundi/mardi, (2) = junior data analyst mardi, (3) = audit 2–3h jeudi. État CRM: tickets assignés, délai estimation calée.
• 9h30–11h00 — SQL extraction + analyse demande #1 (conversion baisse 2%). Contexte: dimanche 45k appels uniques site, 15.2k sessions > 2 min engagement, 5.3k panier crée, 1.7k commandes réalisées. Conv taux: 1.7k / 5.3k = 32% (not 33.1% avant dit). Wait — discrepancy. Vérif historique dim précédent: 1.8k / 5.4k = 33.3%. Donc réelle baisse = 32% vs 33.3% (-4% relative, pas -2.1% absolu). Manager prob a chiffre KPI dashboard (diff calcul). Action: Query SQL détail exact conversion définition (achat completé only? or ajout panier?) + segmentation par source traffic (organique, paid, direct, social).

SQL — Conversion par source traffic (dimanche dernier):
SELECT
  traffic_source,
  COUNT(DISTINCT session_id) AS sessions,
  COUNT(DISTINCT CASE WHEN cart_created=1 THEN session_id END) AS carts,
  COUNT(DISTINCT CASE WHEN order_completed=1 THEN session_id END) AS orders,
  ROUND(100.0 * COUNT(CASE WHEN order_completed=1 THEN 1 END) /
        COUNT(*), 2) AS conversion_pct
FROM sessions_traffic
WHERE DATE(session_date) = '2026-07-20'
GROUP BY traffic_source
ORDER BY orders DESC;

• 11h00–12h00 — Dashboard maintien / alertes urgent. 3 dashboards live: (1) Daily KPI retail (visites, conversion, AOV, revenu par source). Vérif données lundi 9h (lag <2h normal). (2) Customer lifetime (RFM cohort retention). (3) Inventory stock alert (référence <20 units). État: (1) OK données jusqu'hier 23h, live. (2) Cohorte nov 2024 rétention mois 20 = 42% (conforme). (3) Alert: 7 refs stock critique (commander urgent supply chain? = notification auto Slack channel supply). Action: juste vérif, pas changement code.

Mardi–jeudi 14h–18h — Analysis approfondie + création dashboard

• Mardi 14h–16h — Deep dive conversion baisse (contexte commercial). SQL résultats : conversion par source traffic (dimanche): Organic 34%, Direct 31%, Paid_social 28%, Paid_search 36%, Referral 18%. Vs dimanche semaine précédente : Organic 35%, Direct 33%, Paid_social 30%, Paid_search 37%, Referral 19%. Analyse: TOUS sources DOWN −1–2 points. Suggère problème transverse site (not source-specific). Hypothèse : (1) Technical issue dimanche 6-8am (checkout slow? = cart abandonment). (2) Promo expired dimanche (« -15% jusqu'samedi soir », client expect continue). (3) Seasonal (juillet courrier vacances = taux conversion baisse normal). Vérif data: (A) Check server logs — dimanche 6-8am aucun timeout/error 500 detecté. (B) Check promo calendar — oui -15% FRI-SAT seulement, dimanche normal tarif = expected. (C) Check historical data — juillet 2024 / 2025 conversion trend: yes, summer baisse 3–5% normal retail. Contexte commercial: Dir commercial « C'est pas technical, c'est commercial baisse » — mais data dit seasonal normal + promo expiration. Action: présent findings mercredi dir general (contexte, pas panic). Recommandation: test promo-extend dimanche prochaine (−15% dimanche aussi) monitor A/B impact.
• Mercredi 10h–12h — Presentation Dir general + commercial (conversion analysis). 20 min slide + 10 min Q&A. Slide 1 : titre + contexte (« Semaine 29 conversion −2 points taux »). Slide 2 : par source (tableau source/conv%, insights: baisse uniform = NOT source problem). Slide 3 : hypothesis + vérification (technical/promo/seasonal, with data proof technical OK, promo normal, seasonal YES). Slide 4: recommendation (test promo-extend dimanche +15% volume expected, pilot n'impacte pas marge). Dir commercial: « Promo-extend, ok go. » Dir général: « Follow up next week data impact. » Action: brief marketing promo-extend dimanche prochain, setup tracking A/B (« order_promo_extend » flag), réserve mardi analyse resultats.
• Jeudi 9h–11h — Création Power BI dashboard mobile vs desktop (demande e-commerce). Objectif: split conversion/AOV/bounce par device (mobile vs desktop vs tablet). Data model: join orders + sessions_traffic (device_type field). Visuels création: (1) Conversion trend mobile/desktop (line chart, 30 jours). (2) AOV avg (bar mobile vs desktop). (3) Bounce rate (mobile vs desktop). (4) Top products mobile vs desktop (different device priority). (5) Device split traffic pie chart. Build time: 90 min (data connection, dax formulas, design). Publish Power BI online (groupe e-commerce partage, refresh daily 7am). Notification: Slack « Dashboard mobile vs desktop, link: [url] »

Vendredi 15h–17h — Comité mensuel prep + clôture

• Vendredi 9h–14h (half-day focus) — Comité mensuel preparation (préparation 16 slides). Agenda: (1) Overview KPI juin (CA, margin, customers acquired, churn). (2) Conversion analysis (weekly trend, source breakdown, device split). (3) Customer acquisition cost CAC vs lifetime value LTV (ROI paid channels). (4) Top 20 products performance (revenue, margin, new vs repeat buyer). (5) Cohort retention (customer age 0–6 months, engagement). (6) Inventory turnover (stock cover days par catégory). (7) Alerts (top issues, action items). Slide content: mix charts (Power BI exports PNG), table data (Excel detail), interpretation texts. Time: 6–8h collect data, create slides, revisions. Participants review: Dir gen 15 min (« tout ok? »), Dir commercial 20 min (« top products vraiment? validation »), Dir finance 15 min (« margin chiffre vérifiée »). Revisions si questions. Final deck: PDF export lundi 9am before meeting. Format: professional, actionable conclusions per section (not just data dump).
• Vendredi 15h–16h — Audit COGS produit top 50 (demande finance). Contexte: Dir finance suspect « produit X dit coût 12€ unit, mais supplier invoice montre 15€ ». Action: SQL join orders (product_id, quantity_sold) + product_master (product_cost_declared) + supplier_invoice (actual_cost). Query top 50 SKU revenue, compare declared_cost vs actual_cost, flag discrepancy >10%. Résultats: 3 produits coût déclaré faux (supplier coût up, pas updated product_master 2 mois, marge underestimated). Action: alert Finance, update product_cost, recalc margin (impact CA reporté July profit −0.8% si produit pas corrigé temps). Ticket escalation: « URGENT fix product_master 3 SKU, marge audit. »
• Vendredi 16h–17h — Weekly review + planning semaine +1. Bilan: demandes closed (conversion analysis ✓, mobile dashboard ✓, COGS audit ✓). Demandes pending (aucune). KPI perfo analyst: SQL queries 18, requêtes ad hoc answered 8, dashboards maintained 3, analysis depth high (conversion deep-dive valued). Semaine +1 planning: Comité mensuel monday (présentation), feedback commercial (top products, expect discussion), audit inventory (par dir finance vendredi). Normal workload.

02Scripts opérationnels (12 dialogues data analyst)

Situations réelles analyst: cadrage demande floue manager (« je veux dashboard »), refus argumenté délai impossible, restitution choc données vs intuition, explication biais chiffres, présentation comité, négociation accès données avec IT, défense méthodologie.

Script 1. Cadrage demande floue — Manager dit « je veux dashboard »

Script 2. Refus argumenté délai impossible

Script 3. Restitution choc données vs intuition dirigeant

Scripts 4–9. Compacts (dialogues essentiels)

Script 4. Explication biais taux conversion baisse (saisonnalité). Dir commercial urgent: « Conversion down 3% week! Crisis? » Analyst: « Data: oui −3% vs last week. Mais context: juillet juillet normal baisse −2 to −5% retail (vacances). Plus, dimanche traffic +15% (promo-extend), conversion diluted par low-intent traffic. Without promo, conversion flat vs semaine avant. Next week normal traffic, conversion normalisé. Pas crisis, seasonal pattern expected. » Dir commercial rassé. Decision: maintain budget, pas panic-cut, monitor post-promo.

Script 5. Présentation comité CODIR (10 min trame). Analyst slot: 10 min max. Structure: (1) KPI headline 30 sec (« june CA +8% YoY, margin +0.3pp, customer churn −1% »). (2) What moved 3 min (« top drivers: paid ads volume, AOV +4% device shift mobile→desktop, retention cohort may'24 +3pp »). (3) What's worrying 2 min (« inventory turnover weak category sportswear −2 weeks cover, recommend promo »). (4) One recommendation 3 min (« pilot promo segment jun cohort (repeat buy rate 45%), expect CAC reduction −15%, pilot 2 weeks »). (5) Next month focus 1 min (« Cohort analysis deeper analysis, customer segmentation modeling »). Total 10 min, no deep data, just insights + action. Slides: max 4 visuals, big fonts, clear takeaways.

Script 6. Négociation accès données avec IT. Analyst → IT manager: « Besoin raw click-level data site (events table: timestamp, user_id, event_type, page_url). Current access lent (warehouse 24h lag). Pour real-time conversion alert dashboard, besoin streaming data ou 2h lag max. » IT manager: « Can't give full events real-time (database load, 10 millions events/day). Option: aggregated hourly snapshot? » Analyst: « Hourly ok, if level detail: device, source traffic, page_url, order_id. » IT: « Deal, ready jeudi. » Analyst: « Confirmé, dashboard ready jeudi soir. »

Script 7. Defensa méthodologie audit COGS. Finance manager: « Pourquoi tu dit produit X coût déclaré wrong? Notre supplier dit 12€ juste prix. » Analyst: « Comparison: supplier invoice derniers 6 mois shows 14€ avg unit cost (last 5 purchase order). Product_master dit 12€. Invoice docs attachés ticket — voir achat history. Conclusion: data obsolete 2–3 mois (prix supplier augmenté, product_master not updated). Solution: update product_master 14€, recalc margin impact (−2.8% cette produit contribution). » Finance manager vérif invoices: « D'accord, analyst right. Jira update master cost. »

Script 8. Refus anonymisation donnée sensible (privacy compliance). Marketing manager: « Besoin customer email list top 1000 spenders for mailing. » Analyst: « Non, privacy rule : customer data PII pas exportable CSV analyst team. Pour mailing, process: (1) Analyst identify segment (top 1000), create SQL query, save segment_id only in encrypted table (no email). (2) Email system query segment_id directly, pull email encrypted server (audit log). (3) Mailing send, campaign tracking, no raw customer data leave warehouse. Policy GDPR compliance. » Marketing: « OK process, setup jeudi? » Analyst: « Jeudi. »

03Procédures pas-à-pas chiffrées (SQL, Excel, Power BI, RFM, cohortes)

Proc 1. 10 requêtes SQL essentielles (schéma commandes-clients-produits)

-- SQL 1: Revenue total par jour (30 derniers jours)
SELECT DATE(order_date) AS jour, COUNT(DISTINCT order_id) AS orders,
       ROUND(SUM(order_total),2) AS revenue,
       ROUND(AVG(order_total),2) AS aov
FROM orders
WHERE order_date >= NOW() - INTERVAL '30 days' AND order_status='completed'
GROUP BY DATE(order_date) ORDER BY jour DESC;

-- SQL 2: Revenue par source traffic
SELECT traffic_source, COUNT(DISTINCT order_id) AS orders,
       ROUND(SUM(order_total),2) AS revenue, ROUND(SUM(order_total)/COUNT(DISTINCT order_id),2) AS aov
FROM orders o JOIN sessions s ON o.session_id = s.session_id
WHERE o.order_date >= NOW() - INTERVAL '90 days' AND o.order_status='completed'
GROUP BY traffic_source ORDER BY revenue DESC;

-- SQL 3: Top 20 produits par revenue
SELECT p.product_name, SUM(oi.quantity) AS qty_sold,
       ROUND(SUM(oi.quantity * oi.unit_price),2) AS revenue,
       ROUND(AVG(oi.unit_price - p.cost_unit),2) AS margin_unit
FROM order_items oi JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= NOW() - INTERVAL '30 days' AND o.order_status='completed'
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC LIMIT 20;

-- SQL 4: Customer acquisition cost CAC par source (mois courant)
SELECT traffic_source, COUNT(DISTINCT c.customer_id) AS new_customers,
       ROUND(SUM(ad_spend),2) AS total_ad_spend,
       ROUND(SUM(ad_spend) / COUNT(DISTINCT c.customer_id),2) AS cac
FROM customers c JOIN sessions s ON c.first_session_id = s.session_id
LEFT JOIN ad_spend a ON s.traffic_source = a.channel AND DATE(a.date) >= DATE_TRUNC('month', CURRENT_DATE)
WHERE DATE(c.created_at) >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY traffic_source ORDER BY cac DESC;

-- SQL 5: Repeat purchase rate par cohort (client première achat)
SELECT DATE_TRUNC('month', c.created_at)::DATE AS cohort_month,
       COUNT(DISTINCT c.customer_id) AS cohort_size,
       COUNT(DISTINCT CASE WHEN (SELECT COUNT(*) FROM orders WHERE customer_id=c.customer_id) > 1 THEN c.customer_id END) AS repeat_buyers,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN (SELECT COUNT(*) FROM orders WHERE customer_id=c.customer_id) > 1 THEN c.customer_id END) / COUNT(DISTINCT c.customer_id), 1) AS repeat_rate_pct
FROM customers c
GROUP BY cohort_month ORDER BY cohort_month DESC LIMIT 12;

-- SQL 6: Customer lifetime value LTV (top 100 clients)
SELECT c.customer_id, c.email,
       ROUND(SUM(o.order_total),2) AS lifetime_revenue,
       COUNT(DISTINCT o.order_id) AS total_orders,
       ROUND(AVG(o.order_total),2) AS avg_order_value,
       MAX(o.order_date) AS last_purchase_date,
       DATEDIFF(DAY, MIN(o.order_date), MAX(o.order_date)) AS customer_age_days
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_status='completed'
GROUP BY c.customer_id, c.email
ORDER BY lifetime_revenue DESC LIMIT 100;

-- SQL 7: Conversion rate par device (mobile vs desktop)
SELECT s.device_type, COUNT(DISTINCT s.session_id) AS sessions,
       COUNT(DISTINCT CASE WHEN o.order_id IS NOT NULL THEN s.session_id END) AS sessions_converted,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN o.order_id IS NOT NULL THEN s.session_id END) / COUNT(DISTINCT s.session_id), 2) AS conversion_pct
FROM sessions s LEFT JOIN orders o ON s.session_id = o.session_id AND o.order_date >= s.session_date AND o.order_status='completed'
WHERE s.session_date >= NOW() - INTERVAL '30 days'
GROUP BY device_type;

-- SQL 8: Churn rate par cohort (customer inactivité > 60 days)
SELECT DATE_TRUNC('month', c.created_at)::DATE AS cohort_month,
       COUNT(DISTINCT c.customer_id) AS cohort_size,
       COUNT(DISTINCT CASE WHEN MAX(o.order_date) < (CURRENT_DATE - INTERVAL '60 days') THEN c.customer_id END) AS churned,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN MAX(o.order_date) < (CURRENT_DATE - INTERVAL '60 days') THEN c.customer_id END) / COUNT(DISTINCT c.customer_id), 1) AS churn_pct
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_status='completed'
GROUP BY cohort_month ORDER BY cohort_month DESC;

-- SQL 9: Product margin analysis (cost vs retail)
SELECT p.category, p.product_name, p.cost_unit, p.retail_price,
       ROUND((p.retail_price - p.cost_unit), 2) AS margin_abs,
       ROUND(100.0 * (p.retail_price - p.cost_unit) / p.retail_price, 1) AS margin_pct,
       SUM(oi.quantity) AS qty_sold_30d
FROM products p LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.order_date >= NOW() - INTERVAL '30 days'
GROUP BY p.product_id, p.category, p.product_name, p.cost_unit, p.retail_price
ORDER BY margin_pct DESC;

-- SQL 10: Customer segmentation RFM base (Recency, Frequency, Monetary)
SELECT c.customer_id, c.email,
       DATEDIFF(DAY, MAX(o.order_date), CURRENT_DATE) AS recency_days,
       COUNT(DISTINCT o.order_id) AS frequency_orders,
       ROUND(SUM(o.order_total), 2) AS monetary_value
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_status='completed'
GROUP BY c.customer_id, c.email
ORDER BY monetary_value DESC;

Proc 2. Créer un TCD Excel « Question business »

Scénario: Dir e-commerce question: « Quel device (mobile/desktop/tablet) génère meilleur ROI ? » → Revenue, conversion, AOV, CAC par device.

1. Export données SQL → Excel. Query: SELECT device, revenue, orders, cac_est, conversion_pct FROM device_analytics month=juin. Export CSV, ouvert Excel. Nettoyage: pas headers répétés, format monétaire (€ 2 decimales), trier revenue DESC.
2. Créer TCD base. Select data + Insert > Pivot Table. Rows: device (mobile, desktop, tablet). Values: SUM revenue, SUM orders, AVG cac, AVG conversion. Résultat table 4 colonnes × 4 rows.
3. Ajouter colonnes calculées. Formule Excel supplémentaire : (A) CAC ROI = revenue / cac (par device). (B) Revenue per visitor = revenue / unique_visitors (depuis data brute). Exemple résultat : mobile (ROI 4.2x, rev/visitor 18€), desktop (ROI 5.1x, rev/visitor 22€), tablet (ROI 3.1x, rev/visitor 14€).
4. Conditional formatting + présentation. Color scale revenue (vert high, rouge low). Bold best performer (desktop) revenue/ROI. Ajouter footer note: « Basé 30 jours data, desktop priority marketing budget. »
5. Partage Power BI / Sheets. Export TCD comme PNG chart (bar chart revenue par device), upload Power BI tile. Link shared email stakeholders (dir e-commerce, marketing).

Proc 3. Construire dashboard Power BI (4 étapes)

1. Connexion données. Power BI Desktop → Get Data → PostgreSQL (server: data.company.com, database: warehouse). Authentif, load tables: orders, customers, sessions, products (total 4 tables, ~2M rows). Data model: relationship order.customer_id ↔ customer.id, order.session_id ↔ session.id.
2. DAX mesures clés. Create measure: (1) Total_Revenue = SUM(orders[order_total]). (2) Conversion_Rate = COUNTROWS(DISTINCT orders) / COUNTROWS(sessions) × 100. (3) AOV = Total_Revenue / COUNTROWS(orders). (4) New_Customers = DISTINCTCOUNT(customers[customer_id]) filtered by first_order_month. (5) CAC = SUM(ad_spend) / New_Customers. Time: 45 min DAX coding.
3. 5 visuels principaux. (1) Line chart : revenue trend 30 jours (x-axis: date, y-axis: Total_Revenue). (2) Clustered bar : revenue par source traffic (mobile/desktop/organic/paid). (3) KPI cards : 4 cartes (Revenue, Orders, AOV, Conversion_Rate), format gros numbers. (4) Table : top 20 products (name, revenue, orders, margin). (5) Pie chart : customer split by cohort (new/repeat, % labels). Layout: 2 × 3 grid, professional colors (blue/grey). Time: 2h design + tweaks.
4. Publication + refresh. Publish Power BI Online. Share groupe e-commerce (permission: View only, except analyst: Edit). Set automatic refresh 7am daily (2h lag warehouse acceptable). URL dashboard + pin Slack channel. Initial feedback: « Looks clean, love top products table. »

Proc 4. Segmentation RFM complet (Recency/Frequency/Monetary)

Objective: Segmenter 8k customers en 4 segments (VIP, Loyal, At-Risk, Inactive) pour targeting marketing différent.

1. Calcul RFM base SQL (SQL 10 ci-dessus). Recency (days since last purchase), Frequency (#orders), Monetary (total spend). Export CSV : customer_id, recency, frequency, monetary.
2. RFM scoring Excel. Tertiles per dimension : Recency (low = good, 0–30 days = score 3, 31–60 = score 2, >60 = score 1). Frequency (1–2 orders = 1, 3–5 = 2, >5 = 3). Monetary (0–50€ = 1, 51–150€ = 2, >150€ = 3). Formules Excel: =IF(A2<=30, 3, IF(A2<=60, 2, 1)) etc. Résultat : RFM score 111–333 (min–max 27 segments).
3. Segmentation mapping. RFM 333 = VIP (high all), 321–323 = Loyal (high recency/frequency), 123–221 = At-Risk (low recent), 111 = Inactive. Formule segment: =IF(R3_score=3&F_score=3&M_score=3, "VIP", IF(R_score>=2&F_score>=2, "Loyal", IF(R_score<=1&M_score<=2, "At-Risk", "Inactive"))). Résultat distribution: VIP 8% (640 customers), Loyal 35% (2800), At-Risk 40% (3200), Inactive 17% (1360).
4. Action par segment. VIP: exclusive offer (−10% sale, early access new product). Loyal: upsell (bundle cross-sell). At-Risk: win-back (« miss you » email −15%, referral bonus). Inactive: re-engagement (« come back » promo, survey. Marketing setup jeudi (segment upload CRM, email campaigns live lundi).

Proc 5. Analyse cohortes (rétention mois/mois)

Question business: « Customer retention behavior? Cohort may 2024 rétention mois 6 ok? »

1. Cohort data extraction SQL. Group customer par first_purchase_month. Pour chaque cohort, calculé retention mois suivants (% active customer repeat purchase mois N vs mois 0). Query résultat: cohort_month, month_0 (100% par def), month_1 (%), month_2 (%), ... month_12 (%).
2. Excel cohort table. Rows: cohort_month (jan–jun 2024). Columns: month_0 to month_6. Values: retention % per cohort-month. Exemple : may cohort month_6 retention = 42% (42% customer may cohort still active achat dernier 30 days mois 6 après). Format: color scale 100% (vert) → 0% (rouge).
3. Analysis pattern. Typical retail: month_1 retention 55–65% (churn initial post-purchase), month_3 plateau 35–45%, month_6 stabilise 25–35%. May cohort actual 42% month_6 = good vs ecommerce average 30%. Insight: customer quality good, retention trend positive.
4. Action. Recommendation: increase customer acquisition spend (+20% budget), cohort quality confirms payback acceptable. Expected LTV payback = 8–10 months (good margin > 2–3 month CAC).

04Tableau de bord type e-commerce (KPI essentiels)

05Grille qualité dashboard (15 points essentiel)

06Prompts IA data analyst (8 modèles)

Prompt 1. Générer requête SQL (description donnée)

Tu es data analyst expert SQL PostgreSQL.
Table schéma: sessions (session_id, device_type: mobile/desktop/tablet, session_date, user_id).
                orders (order_id, session_id, order_total, order_date, order_status).

Écris query SQL : count sessions per device, count sessions converted (order exists),
conversion rate pct per device. Last 30 days, only completed orders.

Output: SQL complète, copyable, tested syntax, column names clairs, optimised.

Prompt 2. Interpréter données choc (contradiction intuition)

Data analyst role. Data insight : top revenue client (business.com 240k€/an) has LTV 180k€ et CAC 60k€.
Autre client (retail_pro 160k€/an) has LTV 140k€ et CAC 12k€. CAC ROI très different.

Explique : (1) Why cost different? (2) Implication business? (3) Recommendation action?

Output: texte 150 mots, langage affaires clair, pas technique jargon.

Prompt 3. Data storytelling CODIR (10 min deck)

Tu présentes comité 10 min (4 slides max). Data: juin CA 485k€ (vs target 500k€, vs mai 500k€),
conversion 3.2% (vs target 3.5%), CAC 28€ (up vs 25€), LTV 320€ (healthy 11x ROI).
Retention cohort may 42% M6 (good).

Slide structure: (1) Headline KPI 30sec. (2) What moved 2 min (drivers). (3) What worried 2 min.
(4) Recommendation 3 min. (5) Next month focus 1 min. Make actionable, not just data dump.

Output: bullet points, chart suggestions, recommendation concrete.

Prompts 4–8. Compacts (formats court)

Prompt 4. Validation méthodologie. « Je ai calculé CAC sum(ad_spend) / count(new_customers). Correct? Ou inclure offline channels? » Response: « CAC formula correct digital only. Offline (retail stores) = separate channel, calculate separately (offline_spend / offline_new_customers). Si blended CAC needs, sum both spend / total new. Clarify channel attribution strategy first. »

Prompt 5. Détection biais données. « Conversion up 5% semaine de, great! » Analysis: « Check context first: (1) Promo active semaine? → selection bias (promo users convert high). (2) Traffic quality changed? (3) Device mix shift? (4) Seasonality? » Vérif : oui, promo -15% live. Conclusion: « Sans promo, conversion flat. Ne reportez pas 5% gain comme growth. »

Prompt 6. Recommandation pricing test. « Current AOV 262€ target 280€. Comment augmenter? » Response: « Test A/B free shipping threshold: control 30€, test 40€ (increase 10€). Monitor AOV lift, cart abandon rate. Hypothesis: free ship 40€ = average order boost +15€ (AOV 277€). Profit impact: +15€ order − free ship cost 8€ = +7€ contribution. ROI positive if conversion not drop. Pilot 1 week, 1000 sessions/variant. »

Prompt 7. Análisis cohorte cohort mois/mois. « Cohort jan 2024 retention mois 12 = 28%. Bon? » Response: « Contexte : avg retail 20–30% year-1 retention. Jan 28% = good quartile. Mais trend: si mois 6 (50%) drop mois 12 (28%) sharp, investigate churn drivers (promo expired, competitive pressure, season). Compare mois après mois curve: if jan curve flatter vs feb, jan cohort quality lower ou retention strategy changed. »

Prompt 8. Communication non-technical director. « Comment expliquer CAC/LTV ratio à dir general? » Response: « Simplified: « Each customer costs 28€ to acquire (ads, promo). Over lifetime, they spend 320€. Profit 320€ − 28€ − product cost 160€ = 132€ net per customer. Scale: 1850 new customer mois × 132€ = 244k€ profit monthly acquisition seul. »Avoid jargon, lead ROI/profit, 1–2 sentences max. »

07Check-list avant diffusion analyse + Tableau « Combien ça coûte »

Check-list diffusion (8 points essentiels)

• Données validées. SQL query run 2x (vérifier résultats cohérents). Compare sample vs source system (10–20 rows manual check). Pas anomalies (NULL unexpected, chiffres impossibles, dates futur).
• Méthodologie claire. Note technique 50 mots: calcul/formula (« Revenue = SUM(order_total) where status=completed »), période (« last 30 days »), exclusions (« test orders filtered »). Share avec stakeholders pour vérification.
• Contexte business documenté. Note assumptions: « Promo active semaine → selection bias conversion. Seasonal context juillet −3% normal. » Évite interpretation fausse reader.
• Outliers identifiés. Highlight anomalies: « Single customer 50k€ order week (B2B test) — excluded from AOV. » Transparency → trust.
• Recommandation actionable. Pas juste « data says X ». Lead action: « Conversion 3.2%, target 3.5%. Test A/B checkout step reduction mercredi, expect +0.3% lift. Budget 2k€, 1-week pilot. »
• Destinataire conforme. Valider role access (dir commercial = revenue/source, dir e-commerce = device/conversion, finance = margin/cost). Pas sensitive data leak (customer PII, supplier costs détail).
• Format livrable OK. File naming (« June_KPI_Analysis_2026-07-19.pdf »), date included, version control (v1/v2 if revisions). Slack link + email attachment redundancy.
• Q&A anticipé. List 3 questions probables stakeholders (« Why CAC up? », « Retention vs may? », « Action plan july? »). Préparer réponses 1 min chaque.

Tableau « Combien ça coûte » (outils et formation 2026)