Duty-free sales funnel analysis for a largest FMCG brand
Overview
Client develops and manages operations of leading airports and wanted to analyze sales data using Big Query and Looker Studio to understand the conversion percentages at each stage of the duty-free sales funnel for direct, organic, and paid channels across app, web, and M-web platforms.
The objective was to identify key drop-off / cart abandonment points, quantify conversion rates for each channel, and provide actionable insights to improve service efficiency and customer experience.
Solution
Data extraction and preparation:
• Extracted and transformed six months of data (with ~2-3 million interactions) using Big Query for seamless integration into Looker Studio.
• Identified and mapped out seven critical stages in customers’ buying journey, from entry to checkout.
Funnel visualization in Looker Studio:
• Built a multi-step funnel visualization, allowing stakeholders to easily monitor each stage of the customer journey.
• Incorporated filtering options for in-depth analysis by device platform, purchase channel, and period.
Key Insights:
• Conversion rates: Tracked conversion rates across each channels and platforms. Such as 10% for direct channel, 6% for organic channel, and 7% for paid channels).
• Drop-off Analysis: Identified stages with the highest drop-off rates, such as 70% drop from ‘view_item’ to ‘add_to_cart’ step in paid channel, to improve retention strategies.
Impact
The dashboard supports the client’s ability to adjust marketing and operational strategies based on customer behavior insights.
Improved understanding of customer flow across buyer journeys
