我如何使用SQL和Excel预测电子商务的LTV ?

客户终身价值(“LTV”)是个人客户将来与特定业务共度的金额。它通常用于在客户群中重视群组,确定在给定的队列,排名客户中获取或保留新用户,并从基线LTV预测中衡量营销活动的成功。manbetx客户端应用下载

对于电子商务来说,LTV的计算并不简单,因为未来的支付并不是契约性的:在任何时候,用户都可能不会再进行一次购买。此外,预测未来购买需要统计模型,这是许多当前LTV公式所缺乏的。

本指南显示如何使用SQL和Excel计算非合同业务的前瞻性LTV。这种分析方法允许您准确地对客户进行排名,并预测其未来的购买规模,以帮助重点营销努力。manbetx客户端应用下载

在本指南中,我们假设您正在使用我们描述的跟踪方案如何实施电子商务跟踪计划并在a中存储数据段仓库

和产品专家谈谈了解Warby Parker和Crate & Barrel等公司如何利用数据仓库提高用户粘性和销量。

计算LTV:购买至死

在非合同设置中,我们无法使用简单的保留率来确定客户何时终止其关系。manbetx客户端应用下载这是因为保留率是一种线性模型,无法准确预测客户是否已经与公司的关系结束或仅仅是在交易之间长期的中间的关系。新万博app2.0下载

最准确的非合同LTV模型,名为“Buy Di Die”(“BTYD”),重点是根据上次购买,购买频率和平均购买价值的折扣估算未来购买。该模型使用非线性建模来预测用户是否是“活着”或“死”给定的历史性交易,以预测未来购买的概率和大小。

因为LTV是电子商务公司的关键指标,所以我们必须使用这种模型(而不是基于留存率的简单线性公式)进行计算。

我们首先将使用SQL来构建必要的表,该表将被导出为CSV并在Google纸张中打开。然后,我们将使用求解器来估计预测模型参数,最终计算每个客户的未来购买。最后,LTV计算只是每个客户未来购买的净目前价值。我们将通过LTV对它们进行排名,然后在我们的前10名或50家客户中找到行为模式,以弄清楚如何最好地瞄准或保留此队列。manbetx客户端应用下载

最近,频率和平均大小

作为我们虚拟点播的工匠敬酒公司的增长分析师,我们希望了解哪些客户对业务的价值比其他公司更值得。manbetx客户端应用下载新万博app2.0下载最重要的是,我们想了解这些客户所有人都必须帮助指导我们的营销团队的努力。manbetx客户端应用下载

创建BTYD模型的第一步是获得至少一个月的历史采购数据。在我们的分析中,我们将使用过去六个月的数据。数据必须包括列用户标识(电子邮件很酷,我们也将在下面的例子中使用),在指定的时间窗口中购买数量,自上次购买以来的天数,并且自第一次购买以来的日子。

然后,我们将使用这个Google表,它提供了估计模型参数的所有复杂计算,以及预测每个客户的未来销售。这张表是只查看,所以请确保完全复制它,以便您可以使用它。

要检索一个包含正确列的表进行分析,我们使用下面的SQL查询:

first_transaction.作为(选择u电子邮件,datediff('日',最小值(ocreceived_at)::日期,当前日期)作为第一的兜售order_completedoc加入兜售用户uocuser_id=u电子邮件在哪里ocreceived_at>返回(“月”,-6,当前日期)团体通过1),频率作为(选择u电子邮件,(清楚的occheckout_id.)作为频率兜售order_completedoc加入兜售用户uocuser_id=u电子邮件在哪里ocreceived_at>返回(“月”,-6,当前日期)团体通过1),last_transaction作为(选择u电子邮件,datediff('日',马克斯(ocreceived_at)::日期,当前日期)作为最后的兜售order_completedoc加入兜售用户uocuser_id=u电子邮件在哪里ocreceived_at>返回(“月”,-6,当前日期)团体通过1),普通_transaction_size.作为(选择u电子邮件,Avg.(oc总计)作为Avg.兜售order_completedoc加入兜售用户uocuser_id=u电子邮件在哪里ocreceived_at>返回(“月”,-6,当前日期)团体通过1订单通过2去世)选择清楚的u电子邮件,NVL.(f频率,0)作为频率,NVL.(z最后的,0)作为days_since_last_transaction,NVL.(一个第一的,0)作为days_since_first_transaction.,tAvg.作为普通_transaction_size.兜售用户u加入first_transaction.一个u电子邮件=一个电子邮件加入频率fu电子邮件=f电子邮件加入last_transactionzu电子邮件=z电子邮件加入普通_transaction_size.tu电子邮件=t电子邮件订单通过2去世

这将返回一个表,其中每一行是唯一的用户,列是电子邮件、时间窗口内的购买数量、自上次购买以来的离散时间单位数量和平均购买订单。

下面是Mode Analytics中查询返回的前12行的截图。

导出此数据到CSV,然后复制并粘贴在谷歌表的第一个表,其中蓝色类型是在下面的截图:

另一定要在单元格B6中添加总时间。这是重要的,因为第二张表使用了计算未来付款的净目前价值的这次持续时间。

如何使用谷歌电子表格

将CSV从表中粘贴到表格的第一个选项卡后,下一步是估算模型参数(纸张左上角的变量)。为此,我们需要使用名为Solver的Microsoft Excel的功能。

您可以将谷歌表导出为Excel文档。然后,使用Excel Solver最小化单元格B5的对数似然数,同时保持B1:B4大于0.0001的参数。

求解器运行后,将更新单元格B1:B4以表示模型的估计。现在,您可以将那些返回到Google Sheet上的纸张中的硬编码。下一张纸张依赖于这些模型估计,以计算每位客户的预期购买。

模型和预测未来的客户购买

The model requires four pieces of information about each customer’s past purchasing history: her “recency” (how many “time units” her last transaction occurred), “frequency” (how many transactions she made over the specified time period), the length of time over which we have observed her purchasing behavior, and the average transaction size.

在我们的例子中,我们有6个月期间的购买行为数据,每个时间单位是一天。

我们将对这些输入应用-几何分布和负二项分布(“BG/NBD”),然后使用Excel估计模型参数(另一种方法是帕累托/NBD模型)。使用这些概率分布是因为它们准确地反映了现实个人购买行为的基本假设。(了解有关这些模型的更多信息)。

在估计模型参数之后,我们将通过将相同的历史购买数据应用于贝叶斯定理来预测特定客户的条件预期交易,这描述了基于与事件相关条件的先验条件的事件的概率。

估计模型参数

第一页的左上角代表BG/NBD模型的参数,必须与您粘贴的历史数据相吻合。这四个参数(r、alpha、a和b)的“初始值”将为1.0,因为我们将使用Excel求解器来确定它们的实际值。

F ~ J列的值代表BG/NBD模型中的变量。F列,特别是,定义了一个单一的客户的贡献,对一个首要的功能,我们将使用求解器来确定参数。在统计学中,这个函数称为似然函数,它是一个统计模型的参数的函数。

在这种特殊情况下,该函数是对数似然函数,即B5,作为f列中所有单元格的和计算。对数函数比较容易处理,因为它们在函数本身的同一点达到最大值。使用Solver,我们将找到给定B1:B4中的参数的B5的最大值。

有了新的参数估计,我们现在可以预测客户未来的购买。

预测客户未来的购买行为

在下一张表中,我们将贝叶斯定理应用于历史性的购买信息,以预测下一个时期的交易数量。然后我们将使用平均交易规模将预期数量乘以计算该期间的预期收入,我们可以将其推断为年金,其中我们可以找到目前的折扣价值(假设折扣率为10%)。

贝叶斯定理公式的中心是高斯超几何函数,它由m列中的“2F1”定义。我们把超几何函数看作一个截断的级数来计算:通过在级数中添加项,直到每个项足够小,使其变得平凡。在电子表格中,我们把级数相加到它的第50项。

贝叶斯定理中的其余变量在第I列到第L列中,它们使用来自客户的历史购买信息的输入,以及由求解器(单元格B1:B4)确定的模型参数估计。

在下一个时间段的预期采购数量在H栏中计算。

最后,将其与平均交易规模相乘,就可以得到下一个时间段的预期收益。

为您的客户排名manbetx客户端应用下载

这个练习允许您通过将列F按降序排序,将您的客户从最有价值到最低。manbetx客户端应用下载你可以乘坐用户标识S of The Mode的几个客户,并介manbetx客户端应用下载绍他们的购物体验,以确定他们分享的任何模式,了解如何成为高价值客户的行为。

下面是一个简单的查询,用于获得一个列有用户操作的表。只是替换user_id与用户有问题。

anonymous_ids作为(选择Anonymous_id.兜售跟踪在哪里user_id=“46 x8vf96g6”团体通过1),page_views作为(选择*兜售页面p在哪里puser_id=“46 x8vf96g6”Anonymous_id.(选择Anonymous_id.anonymous_ids)订单通过preceived_at去世),径赛项目作为(选择*兜售跟踪t在哪里tuser_id=“46 x8vf96g6”Anonymous_id.(选择Anonymous_id.anonymous_ids)订单通过treceived_at去世)选择url,received_atpage_views联盟选择event_text,received_at径赛项目订单通过received_at去世

以上查询的用户user_id“46x8vf96g6”返回下表:

在烤面包,大多数最高前瞻性的预期LTV客户共同分享了一件事:平均每月两个订单,平均购买大小为20美元。manbetx客户端应用下载

考虑到这一点,我们可以在我们的电子邮件工具中定义一个行为队列,客户。以及创建一个触发器工作流,这样我们就可以发送电子邮件报价给这些客户。manbetx客户端应用下载

学习如何使用电子邮件工具来瞄准这批高价值客户。manbetx客户端应用下载

奖励你最好的客户manbetx客户端应用下载

该练习不仅是客户LTV的前瞻性预测模型,而且是作为优质排名系统,以了解您的业务的客户。manbetx客户端应用下载再加上浏览给定客户的整个购物体验的能力,您可以识别可能是高价值购物者的早期信号的广泛模式或特定动作。认识到这些高价值购物者意味着主动培养,奖励和保留它们。

而这仅仅是个开始。拥有丰富的未加工客户数据允许您为LTV创建准确的投万博官方购彩影模型,因此您不仅知道您可以花多少以获取它们,而且还如何按价值对客户进行排名。manbetx客户端应用下载最终,这些见解导致正确的行动,可以建立一个吸引人的购物体验和推动销售。

和产品专家谈谈了解Warby Parker和Crate & Barrel等公司如何利用数据仓库提高用户粘性和销量。

最后更改:2020年6月12日



开始分段

段是将网站和移动应用程序数据集成到超过300个分析和增长工具的最简单方法。
创建免费帐户