E-commerce Metrics Calculation

2/14/2023

This post details the development of ShopifyQL Notebooks and the underlying commerce data models. It outlines the process of building these models, focusing on making them purpose-driven, explorable, and extensible. Key aspects include defining granularity, ensuring additive metrics, and the step-by-step data modeling process: starting with business questions, creating mock data, finding data sources, assessing data quality and consistency, and evaluating model freshness and performance. It also introduces the concept of ShopifyQL as a query language for these models.

5/18/2022

Introduced and detailed the implementation of double entry transition tables for tracking state changes of multiple entities simultaneously. This involves creating individual attribute tables for each entity's status, merging them, and then transforming them into a double entry format with a `net_change` column. This approach simplifies historical counting and analysis of product usage, such as tracking Shopify Balance account status alongside Shopify account status, and allows for easier scaling with additional attributes without rewriting existing SQL or PySpark jobs.

2/16/2021

This post details strategies for managing rounding precision in financial calculations, which is crucial for accurate e-commerce metrics. It introduces concepts like the rounding dilemma, banker's rounding, using high-precision data types, maintaining consistency in rounding methods, explicit code comments for clarity, referencing government rounding standards, minimizing rounding instances, and transparently communicating rounding rules to users. The author also shares anecdotal stories and emphasizes the importance of stakeholder awareness regarding precision loss in financial calculations.

11/14/2017

This post introduces the BG/NBD (Beta Geometric / Negative Binomial Distribution) model as a probabilistic approach to defining customer churn and measuring retention for non-contractual businesses. It details the model's parameters (rate of purchasing \(\lambda\), probability of churn event \(p\)), the sufficient statistics required (age, recency, frequency), and the implementation using the lifetimes library in Python with Apache Spark for large-scale merchant data processing. It also discusses the interpretability and limitations of the model, such as its inability to handle seasonal trends or easily incorporate additional customer variables.

11/28/2011

This post details the challenges and evolution of defining and calculating churn rate. It critiques two accounting-based definitions for their susceptibility to growth fluctuations and temporal inconsistencies. It then explores a predictive modeling approach, highlighting its limitations in timeliness and currentness. Finally, it presents a refined definition that uses daily customer counts and cancellations, weighted to provide comparable results across different period lengths and reflect actual changes in churn behavior, resolving issues of timeliness, currentness, and comparability.

7/26/2011

This post introduces the core concepts of states and events as the foundation for calculating e-commerce metrics. It clarifies the distinction between measuring points (events) and intervals (states), and how these relate to accounting concepts like balance sheets and income statements. The post advocates for starting metric calculations with well-defined events due to their inherent clarity and argues for the flexibility of event-based calculations when dealing with complex or evolving metric definitions. It sets the stage for future discussions on specific metric implementations and advanced analytics.