Reading data from Notion
import os
import requests
import pandas as pd
api_key = os.environ["NOTION_API_KEY"]
database_id = "12345"
headers = {
"Authorization": f"Bearer {api_key}",
"Notion-Version": "2021-08-16",
"Content-Type": "application/json",
}
response = requests.post(
f"https://api.notion.com/v1/databases/{database_id}/query", headers=headers
).json()
companies = response["results"]
while response["has_more"]:
response = requests.post(
f"https://api.notion.com/v1/databases/{database_id}/query",
json={"start_cursor": response["next_cursor"]},
headers=headers,
).json()
companies.extend(response.get("results", []))
print(f"Got {len(companies)} entries from CRM in Notion.")
def get_name(properties):
if len(properties['Name']['title']) > 0:
return properties['Name']['title'][0]['text']['content']
return None
def get_text_property(properties, property_name):
if len(properties[property_name]['rich_text']) > 0:
return properties[property_name]['rich_text'][0]['text']['content']
return None
def get_select_property(properties, property_name):
status = c['properties'].get(property_name, {}).get('select', {})
if status:
return status.get('name', None)
else:
return None
def get_owners(properties):
if len(properties['Owner']['people']) > 0:
return ", ".join([p.get('name') for p in properties['Owner']['people']])
return None
def get_url(properties):
return properties['URL'].get('url', None)
def get_email(properties):
return properties['Email (point of contact)'].get('email', None)
formatted_companies = []
skipped = []
for c in companies:
try:
properties = c['properties']
company = {
"notion_page_id": c['id'],
"name": get_name(properties),
"email": get_email(properties),
"status": get_select_property(properties, 'Status'),
"owner": get_owners(properties),
"url": get_url(properties),
"team_id": get_text_property(properties, 'Deepnote Team ID')
}
formatted_companies.append(company)
except:
skipped.append(c)
notion_data = pd.DataFrame(formatted_companies)
notion_data
Reading data from the warehouse
Read modeled data from the warehouse, which joins data from Stripe, application database and product analytics.
I recommend modeling the data in a separate workflow, ideally using dbt. The
SELECT
customers.id as customer_id,
customers.mrr,
teams.id as team_id,
teams.name as team_name,
teams.owner_email,
team_activity.number_of_projects as project_count,
team_activity.number_of_blocks as block_count
FROM analytics_views.customers
LEFT JOIN analytics_views.teams ON customers.id = teams.stripe_customer_id
LEFT JOIN analytics_view.team_activity ON teams.id = team_activity.team_id
WHERE
owner_email NOT LIKE '%@deepnote.com' -- filter internal accounts
ORDER BY mrr DESC
Updating Notion
Update existing entries with revenue and engagement data
DataFrame SQL makes it easy to join data from Notion with data from your warehouse
SELECT
*
FROM warehouse_customers
LEFT JOIN notion_data ON warehouse_customers.team_id = notion_data.team_id
WHERE notion_page_id IS NOT NULL
headers = {
"Authorization": f"Bearer {api_key}",
"Notion-Version": "2021-08-16",
"Content-Type": "application/json",
}
for i, c in enriched_entries.iterrows():
print("Patching: ", c.notion_page_id)
data = {
"properties": {
"MRR": c.mrr,
"# Projects": c.project_count,
"# Blocks": c.block_count,
}
}
response = requests.patch(
f"https://api.notion.com/v1/pages/{c.notion_page_id}",
headers=headers,
json=data
).json()
Add new customers
Add customers who self-served, that you haven't talked to yet
SELECT
warehouse_customers.*,
FROM warehouse_customers
LEFT JOIN notion_data ON warehouse_customers.team_id = notion_data.team_id
WHERE
notion_page_id IS NULL
AND mrr > 200
headers = {
"Authorization": f"Bearer {api_key}",
"Notion-Version": "2021-08-16",
"Content-Type": "application/json",
}
for i, c in new_entries_to_add.iterrows():
print("Adding: ", c.customer_id)
data = {
"parent": {"database_id": database_id},
"properties": {
"Name": {"title": [{"text": {"content": c.team_name}}]},
"Email (point of contact)": {"email": c.owner_email},
"Status": {"select": {"name": "New teams review"}},
"Lead source": {"select": {"name": "Self-service"}},
"MRR": {"number": c.mrr},
"Deepnote Team ID": {
"rich_text": [
{"type": "text", "text": {"content": c.team_id}}
]
},
},
}
response = requests.post(
f"https://api.notion.com/v1/pages", headers=headers, json=data
).json()
Flag teams with high engagement
You may want to continue here by creating some segments based on your data, to surface which teams have recently started hitting the limits of the free tier, or just ramped up their usage.
The flow is pretty similar – query the warehouse to identify the right teams, join it with the Notion data, then update the Status column.
Schedule it
When you make all this work, it's time to Schedule the notebook on a regular basis. Since you'll be relying on it for weeks to come, make sure to set up email alerts in case it fails.
You can also embed a little note in your CRM to know the last time the notebook successfully ran.
import datetime
print(f"Last update: {datetime.datetime.now()}")