SingleStore是一款强大的多模型数据库系统和平台，旨在支持各种业务用例。其独特的特性使企业能够将多个数据库系统统一到一个平台上，降低总体拥有成本（TCO），并通过消除复杂的集成工具简化开发人员的工作流程。
在本文中，我们将探讨SingleStore如何改变网络分析公司的电子邮件营销活动，实现个性化和高度定向的电子邮件内容的创建。
本文中使用的笔记本文件可在GitHub上找到。
介绍
网络分析公司依赖电子邮件营销活动来与客户互动。然而，针对客户的通用方法往往会错过最大化业务潜力的机会。更有效的解决方案将涉及使用大型语言模型（LLM）来制作个性化的电子邮件消息。
考虑一个场景，用户行为数据存储在诸如MongoDB之类的NoSQL数据库中，而有价值的文档存储在矢量数据库中，比如Pinecone。管理这些多个系统可能变得复杂且耗费资源，突显了统一解决方案的必要性。
SingleStore是一种多功能多模型数据库，支持多种数据格式，包括JSON，并提供内置的向量函数。它与LLM无缝集成，是管理多个数据库系统的强大替代方案。在本文中，我们将演示SingleStore如何轻松取代MongoDB和Pinecone，简化操作而不影响功能。
在我们的示例应用中，我们将使用LLM为客户生成唯一的电子邮件。为了帮助LLM学习如何定位我们的客户，我们将使用一些知名的分析公司作为LLM的学习材料。
我们将根据用户行为进一步定制内容。客户数据存储在MongoDB中。用户行为的不同阶段存储在Pinecone中。用户行为将使LLM能够生成个性化的电子邮件。最后，我们将使用SingleStore consoli在MongoDB和Pinecone中存储的数据。
创建SingleStore云账户
一篇先前的文章展示了创建免费SingleStore云账户的步骤。我们将使用标准层，并采用Workspace Group和Workspace的默认名称。我们还将启用SingleStore Kai。
我们将在秘密保险库中使用OPENAI_API_KEY和PINECONE_API_KEY分别存储我们的OpenAI API密钥和Pinecone API密钥。
导入笔记本
我们将从GitHub下载笔记本。
在SingleStore云门户的左侧导航窗格中，我们将选择”开发” > “数据工作室”。
在网页的右上角，我们将选择”新建笔记本” > “从文件导入”。我们将使用向导定位并导入从GitHub下载的笔记本。
运行笔记本
通用电子邮件模板
我们将首先生成通用电子邮件模板，然后使用LLM将其转换为每位客户的个性化消息。这样，我们可以通过姓名称呼每位收件人，并向他们介绍我们的网络分析平台的优势。
我们可以按如下方式生成通用电子邮件：
Python
people = ["Alice", "Bob", "Charlie", "David", "Emma"]
for person in people:
message = (
f"Hey {person},\n"
"Check out our web analytics platform, it's Awesome!\n"
"It's perfect for your needs. Buy it now!\n"
"- Marketer John"
)
print(message)
print("_" * 100)
例如，Alice会看到以下消息：
Plain Text
Hey Alice,
Check out our web analytics platform, it's Awesome!
It's perfect for your needs. Buy it now!
- Marketer John
其他用户会收到相同的消息，但会包含他们的姓名。
2. 添加大型语言模型（LLM）
我们可以通过为其提供角色并提供一些信息，轻松地将LLM引入我们的应用程序，如下：
Python
system_message = """
You are a helpful assistant.
My name is Marketer John.
You help write the body of an email for a fictitious company called 'Awesome Web Analytics'.
This is a web analytics company that is similar to the top 5 web analytics companies (perform a web search to determine the current top 5 web analytics companies).
The goal is to write a custom email to users to get them interested in our services.
The email should be less than 150 words.
Address the user by name.
End with my signature.
"""
我们将创建一个调用LLM的函数：
Python
def chatgpt_generate_email(prompt, person):
conversation = [
{"role": "system", "content": prompt},
{"role": "user", "content": person},
{"role": "assistant", "content": ""}
]
response = openai_client.chat.completions.create(
model = "gpt-4o-mini",
messages = conversation,
temperature = 1.0,
max_tokens = 800,
top_p = 1,
frequency_penalty = 0,
presence_penalty = 0
)
assistant_reply = response.choices[0].message.content
return assistant_reply
遍历用户列表并调用LLM会生成独特的电子邮件：
Python
openai_client = OpenAI()
# Define a list to store the responses
emails = []
# Loop through each person and generate the conversation
for person in people:
email = chatgpt_generate_email(system_message, person)
emails.append(
{
"person": person,
"assistant_reply": email
}
)
例如，Alice可能会看到这样的内容：
Plain Text
Person: Alice
Subject: Unlock Your Website's Potential with Awesome Web Analytics!
Hi Alice,
Are you ready to take your website to new heights? At Awesome Web Analytics, we provide cutting-edge insights that empower you to make informed decisions and drive growth.
With our powerful analytics tools, you can understand user behavior, optimize performance, and boost conversions—all in real-time! Unlike other analytics platforms, we offer personalized support to guide you every step of the way.
Join countless satisfied customers who have transformed their online presence. Discover how we stack up against competitors like Google Analytics, Adobe Analytics, and Matomo, but with a focus on simplicity and usability.
Let us help you turn data into your greatest asset!
Best,
Marketer John
Awesome Web Analytics
其他用户也将获得同样独特的电子邮件。
3. 根据用户行为定制电子邮件内容
通过根据用户的行为阶段进行分类，我们可以进一步定制电子邮件内容，以满足他们特定的需求。 一个LLM 将有助于制作鼓励用户在不同阶段进展的电子邮件，最终提高他们对各种服务的理解和使用。
目前，用户数据存储在一个类似以下结构的MongoDB数据库中：
JSON
{
'_id': ObjectId('64afb3fda9295d8421e7a19f'),
'first_name': 'James',
'last_name': 'Villanueva',
'company_name': 'Foley-Turner',
'stage': 'generating a tracking code',
'created_date': 1987-11-09T12:43:26.000+00:00
}
我们将连接到MongoDB以获取数据，如下：
Python
try:
mongo_client = MongoClient("mongodb+srv://admin:<password>@<host>/?retryWrites=true&w=majority")
mongo_db = mongo_client["mktg_email_demo"]
collection = mongo_db["customers"]
print("Connected successfully")
except Exception as e:
print(e)
我们将用MongoDB Atlas中的值替换<password>和<host>。
我们有多个用户行为阶段：
Python
stages = [
"getting started",
"generating a tracking code",
"adding tracking to your website",
"real-time analytics",
"conversion tracking",
"funnels",
"user segmentation",
"custom event tracking",
"data export",
"dashboard customization"
]
def find_next_stage(current_stage):
current_index = stages.index(current_stage)
if current_index < len(stages) - 1:
return stages[current_index + 1]
else:
return stages[current_index]
使用有关行为阶段的数据，我们将要求LLM进一步定制电子邮件，如下：
Python
limit = 5
emails = []
for record in collection.find(limit = limit):
fname, stage = record.get("first_name"), record.get("stage")
next_stage = find_next_stage(stage)
system_message = f"""
You are a helpful assistant, who works for me, Marketer John at Awesome Web Analytics.
You help write the body of an email for a fictitious company called 'Awesome Web Analytics'.
We are a web analytics company similar to the top 5 web analytics companies.
We have users at various stages in our product's pipeline, and we want to send them helpful emails to encourage further usage of our product.
Please write an email for {fname} who is on stage {stage} of the onboarding process.
The next stage is {next_stage}.
Ensure the email describes the benefits of moving to the next stage.
Limit the email to 1 paragraph.
End the email with my signature.
"""
email = chatgpt_generate_email(system_message, fname)
emails.append(
{
"fname": fname,
"stage": stage,
"next_stage": next_stage,
"email": email
}
)
例如，这是为Michael生成的电子邮件：
Plain Text
First Name: Michael
Stage: funnels
Next Stage: user segmentation
Subject: Unlock Deeper Insights with User Segmentation!
Hi Michael,
Congratulations on successfully navigating the funnel stage of our onboarding process! As you move forward to user segmentation, you'll discover how this powerful tool will enable you to categorize your users based on their behaviors and demographics. By understanding your audience segments better, you can create tailored experiences that increase engagement and optimize conversions. This targeted approach not only enhances your marketing strategies but also drives meaningful results and growth for your business. We're excited to see how segmentation will elevate your analytics efforts!
Best,
Marketer John
Awesome Web Analytics
4. 进一步定制电子邮件内容
为了支持用户的进展，我们将使用Pinecone的向量嵌入，让我们能够将用户引导到每个阶段的相关文档。这些嵌入使我们能够轻松地引导用户前往重要资源，并进一步增强他们与我们产品的互动。
Python
pc = Pinecone(
api_key = pc_api_key
)
index_name = "mktg-email-demo"
if any(index["name"] == index_name for index in pc.list_indexes()):
pc.delete_index(index_name)
pc.create_index(
name = index_name,
dimension = dimensions,
metric = "euclidean",
spec = ServerlessSpec(
cloud = "aws",
region = "us-east-1"
)
)
pc_index = pc.Index(index_name)
pc.list_indexes()
我们将创建嵌入，如下：
Python
def get_embeddings(text):
text = text.replace("\n", " ")
try:
response = openai_client.embeddings.create(
input = text,
model = "text-embedding-3-small"
)
return response.data[0].embedding, response.usage.total_tokens, "success"
except Exception as e:
print(e)
return "", 0, "failed"
id_counter = 1
ids_list = []
for stage in stages:
embedding, tokens, status = get_embeddings(stage)
parent = id_counter - 1
pc_index.upsert([
{
"id": str(id_counter),
"values": embedding,
"metadata": {"content": stage, "parent": str(parent)}
}
])
ids_list.append(str(id_counter))
id_counter += 1
我们将搜索Pinecone以找到匹配项，如下：
Python
def search_pinecone(embedding):
match = pc_index.query(
vector = [embedding],
top_k = 1,
include_metadata = True
)["matches"][0]["metadata"]
return match["content"], match["parent"]
使用数据，我们可以要求LLM进一步定制电子邮件，如下：
Python
limit = 5
emails = []
for record in collection.find(limit = limit):
fname, stage = record.get("first_name"), record.get("stage")
# Get the current and next stages with their embedding
this_stage = next((item for item in stages_w_embed if item["stage"] == stage), None)
next_stage = next((item for item in stages_w_embed if item["stage"] == find_next_stage(stage)), None)
if not this_stage or not next_stage:
continue
# Get content
cur_content, cur_permalink = search_pinecone(this_stage["embedding"])
next_content, next_permalink = search_pinecone(next_stage["embedding"])
system_message = f"""
You are a helpful assistant.
I am Marketer John at Awesome Web Analytics.
We are similar to the current top web analytics companies.
We have users at various stages of using our product, and we want to send them helpful emails to encourage them to use our product more.
Write an email for {fname}, who is on stage {stage} of the onboarding process.
The next stage is {next_stage['stage']}.
Ensure the email describes the benefits of moving to the next stage, and include this link: https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/{next_content.replace(' ', '-')}.md.
Limit the email to 1 paragraph.
End the email with my signature: 'Best Regards, Marketer John.'
"""
email = chatgpt_generate_email(system_message, fname)
emails.append(
{
"fname": fname,
"stage": stage,
"next_stage": next_stage["stage"],
"email": email
}
)
例如，这是为Melissa生成的电子邮件：
Plain Text
First Name: Melissa
Stage: getting started
Next Stage: generating a tracking code
Subject: Take the Next Step with Awesome Web Analytics!
Hi Melissa,
We're thrilled to see you getting started on your journey with Awesome Web Analytics! The next step is generating your tracking code, which will allow you to start collecting valuable data about your website visitors. With this data, you can gain insights into user behavior, optimize your marketing strategies, and ultimately drive more conversions. To guide you through this process, check out our detailed instructions here: [Generating a Tracking Code](https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/generating-a-tracking-code.md). We're here to support you every step of the way!
Best Regards,
Marketer John.
我们可以看到我们已经优化了通用模板，并开发出相当有针对性的电子邮件。
使用SingleStore
与管理单独的数据库系统不同，我们将通过使用SingleStore来简化我们的运营。凭借其对JSON、文本和向量嵌入的支持，我们可以高效地将所有必要数据存储在一个地方，降低TCO并简化我们的开发流程。
我们将使用类似以下的管道从MongoDB中摄取数据：
SQL
USE mktg_email_demo;
CREATE LINK mktg_email_demo.link AS MONGODB
CONFIG '{"mongodb.hosts": "<primary>:27017, <secondary>:27017, <secondary>:27017",
"collection.include.list": "mktg_email_demo.*",
"mongodb.ssl.enabled": "true",
"mongodb.authsource": "admin",
"mongodb.members.auto.discover": "false"}'
CREDENTIALS '{"mongodb.user": "admin",
"mongodb.password": "<password>"}';
CREATE TABLES AS INFER PIPELINE AS LOAD DATA LINK mktg_email_demo.link '*' FORMAT AVRO;
START ALL PIPELINES;
我们将用来自MongoDB Atlas的值替换<primary>、<secondary>、<secondary>和<password>。
客户表将由该管道创建。行为阶段的向量嵌入可以按以下方式创建：
Python
df_list = []
id_counter = 1
for stage in stages:
embedding, tokens, status = get_embeddings(stage)
parent = id_counter - 1
stage_df = pd.DataFrame(
{
"id": [id_counter],
"content": [stage],
"embedding": [embedding],
"parent": [parent]
}
)
df_list.append(stage_df)
id_counter += 1
df = pd.concat(df_list, ignore_index = True)
我们需要一个表来存储数据：
SQL
USE mktg_email_demo;
DROP TABLE IF EXISTS docs_splits;
CREATE TABLE IF NOT EXISTS docs_splits (
id INT,
content TEXT,
embedding VECTOR(:dimensions),
parent INT
);
然后，我们可以将数据保存在表中：
Python
df.to_sql(
"docs_splits",
con = db_connection,
if_exists = "append",
index = False,
chunksize = 1000
)
我们将按以下方式在SingleStore中搜索匹配项：
Python
def search_s2(vector):
query = """
SELECT content, parent
FROM docs_splits
ORDER BY (embedding <-> :vector) ASC
LIMIT 1
"""
with db_connection.connect() as con:
result = con.execute(text(query), {"vector": str(vector)})
return result.fetchone()
利用这些数据，我们可以要求LLM按以下方式定制电子邮件：
Python
limit = 5
emails = []
# Create a connection
with db_connection.connect() as con:
query = "SELECT _more :> JSON FROM customers LIMIT :limit"
result = con.execute(text(query), {"limit": limit})
for customer in result:
customer_data = customer[0]
fname, stage = customer_data["first_name"], customer_data["stage"]
# Retrieve current and next stage embeddings
this_stage = next((item for item in stages_w_embed if item["stage"] == stage), None)
next_stage = next((item for item in stages_w_embed if item["stage"] == find_next_stage(stage)), None)
if not this_stage or not next_stage:
continue
# Get content
cur_content, cur_permalink = search_s2(this_stage["embedding"])
next_content, next_permalink = search_s2(next_stage["embedding"])
# Create the system message
system_message = f"""
You are a helpful assistant.
I am Marketer John at Awesome Web Analytics.
We are similar to the current top web analytics companies.
We have users that are at various stages in using our product, and we want to send them helpful emails to get them to use our product more.
Write an email for {fname} who is on stage {stage} of the onboarding process.
The next stage is {next_stage['stage']}.
Ensure the email describes the benefits of moving to the next stage, then always share this link: https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/{next_content.replace(' ', '-')}.md.
Limit the email to 1 paragraph.
End the email with my signature: 'Best Regards, Marketer John.'
"""
email = chatgpt_generate_email(system_message, fname)
emails.append(
{
"fname": fname,
"stage": stage,
"next_stage": next_stage["stage"],
"email": email,
}
)
例如，这是为Joseph生成的一封电子邮件：
Plain Text
First Name: Joseph
Stage: generating a tracking code
Next Stage: adding tracking to your website
Subject: Take the Next Step in Your Analytics Journey!
Hi Joseph,
Congratulations on generating your tracking code! The next step is to add tracking to your website, which is crucial for unlocking the full power of our analytics tools. By integrating the tracking code, you will start collecting valuable data about your visitors, enabling you to understand user behavior, optimize your website, and drive better results for your business. Ready to get started? Check out our detailed guide here: [Adding Tracking to Your Website](https://github.com/VeryFatBoy/mktg-email-flow/tree/main/docs/adding-tracking-to-your-website.md).
Best Regards,
Marketer John.
总结
通过这个实际演示，我们看到SingleStore如何通过其多模型能力和AI驱动的个性化改进我们的电子邮件营销活动。将SingleStore作为我们的唯一真相来源，我们简化了工作流程，并确保我们的电子邮件营销活动为客户带来最大的影响和价值。
致谢
我感谢Wes Kennedy提供的原始演示代码，该代码已经为本文进行了适应。
Source:
https://dzone.com/articles/how-to-build-a-chatgpt-super-app