SingleStore 是一個功能強大的多模型數據庫系統和平台，旨在支持各種業務使用案例。其獨特功能使企業能將多個數據庫系統統一到一個平台中，降低總擁有成本（TCO），並通過消除對複雜集成工具的需求來簡化開發者工作流程。
在本文中，我們將探討 SingleStore 如何改變網絡分析公司的電子郵件活動，從而實現創建個性化和高度針對性的電子郵件內容。
本文中使用的筆記本文件可在 GitHub 上找到。
介紹
網絡分析公司依賴電子郵件活動來與客戶互動。然而，對客戶的通用定位方法通常會錯過最大化業務潛力的機會。一個更有效的解決方案將涉及使用大型語言模型（LLM）來製作個性化的電子郵件消息。
考慮一個情況，其中用戶行為數據存儲在 NoSQL 數據庫，如 MongoDB 中，而有價值的文檔存放在向量數據庫中，比如 Pinecone。管理這些多個系統可能變得複雜且消耗資源，進一步凸顯了統一解決方案的必要性。
SingleStore是一個多功能多模型數據庫，支持各種數據格式，包括JSON，並提供內置的向量函數。它與LLMs無縫集成，成為管理多個數據庫系統的強大替代方案。在本文中，我們將演示SingleStore如何輕鬆取代MongoDB和Pinecone，簡化操作而不影響功能。
在我們的示例應用程序中，我們將使用LLM為客戶生成唯一的電子郵件。為了幫助LLM學習如何針對我們的客戶，我們將使用一些知名的分析公司作為LLM的學習材料。
我們將根據用戶行為進一步定製內容。客戶數據存儲在MongoDB中。用戶行為的不同階段存儲在Pinecone中。用戶行為將使LLM能夠生成個性化的電子郵件。最後，我們將使用SingleStore整合存儲在MongoDB和Pinecone中的數據。
創建SingleStore Cloud帳戶
一篇先前的文章展示了創建免費SingleStore Cloud帳戶的步驟。我們將使用標準版，並使用默認的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提供角色並提供一些信息，輕鬆將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,
}
)
例如，這是為約瑟夫生成的一封電子郵件：
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