Drip campaigns (aka email sequences) are a powerful way to re-engage users using a linear fashion. Many email marketers use this technique…
I Automated Weekly Updates w/ Slack Bot
Save yourself time (and save your sanity) by automating weekly metric update messages to your co-workers. In this video, you'll learn how to make a Slackbot that pulls data from your Google Sheet and sends it out as a curated and stylized message to anyone of your choice through Slack, as a direct message or group message. After this video, you'll be able to take time off with peace of mind.
The first set of code is our requestSlack() function:
function requestSlack(method, endpoint, params) {
const base_url = "https://slack.com/api/"
const headers = {
'Authorization': "Bearer " + YOUR_BOT_OAUTH_TOKEN
'Content-Type': 'application/json'
}
const options = {
headers: headers,
method: method
}
let request_url
if (method == "POST") {
request_url = base_url + endpoint
options.payload = JSON.stringify(params)
} else {
request_url = base_url + endpoint + params
}
const response = UrlFetchApp.fetch(request_url, options).getContentText();
const json = JSON.parse(response);
return {
response_code: json.ok,
response_data: json
}
}
From there, we start writing our weeklyMetricsSlackNotificationJob() function:
function weeklyMetricsSlackNotificationJob() {
let user_request = requestSlack("GET", "users.lookupByEmail", "?email=email@email.com")
console.log(`user: ${user_request.response_data}`)
let user_id = user_request.response_data.user.id
let conversationsOpen_payload = {
users: user_id
}
let conversation_request = requestSlack("POST", "conversations.open", conversationsOpen_payload)
let conversation_id = conversation_request.response_data.channel.id
let postMessage_payload = {
text: `TEST`,
channel: conversation_id
}
let post_message_request = requestSlack("POST", "chat.postMessage", postMessage_payload)
}
To grab the metrics from our Google Sheet, we'll need the getLatestMetrics() function and also the sumSheetValues() function.
function getLatestMetrics(){
const ss = SpreadsheetApp.openById(YOUR_SPREADSHEET_ID)
const sheet = ss.getSheetByName(YOUR_SHEET_NAME)
const latest_metrics = sheet.getRange(6,1,8,7).getValues();
const metric_deltas = sheet.getRange(2,4,2,4).getValues();
const metric_array_positions = {
queries: 3,
first_page: 4,
impressions: 5,
clicks: 6
}
const metrics = {
period: {
week: {
start: Utilities.formatDate(latest_metrics[0][1],"EST", "yyyy-MM-dd"),
end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
},
month: {
start: Utilities.formatDate(latest_metrics[3][1],"EST", "yyyy-MM-dd"),
end: Utilities.formatDate(latest_metrics[0][2],"EST", "yyyy-MM-dd")
}
},
queries: {
this_week: latest_metrics[0][metric_array_positions.queries],
last_week: latest_metrics[1][metric_array_positions.queries],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.queries),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.queries),
wow_delta: Math.round(metric_deltas[0][0]*100) + '%',
mom_delta: Math.round(metric_deltas[1][0]*100) + '%'
},
first_page: {
this_week: latest_metrics[0][metric_array_positions.first_page],
last_week: latest_metrics[1][metric_array_positions.first_page],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.first_page),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.first_page),
wow_delta: Math.round(metric_deltas[0][1]*100) + '%',
mom_delta: Math.round(metric_deltas[1][1]*100) + '%'
},
impressions: {
this_week: latest_metrics[0][metric_array_positions.impressions],
last_week: latest_metrics[1][metric_array_positions.impressions],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.impressions),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.impressions),
wow_delta: Math.round(metric_deltas[0][2]*100) + '%',
mom_delta: Math.round(metric_deltas[1][2]*100) + '%'
},
clicks: {
this_week: latest_metrics[0][metric_array_positions.clicks],
last_week: latest_metrics[1][metric_array_positions.clicks],
this_month: sumSheetValues(latest_metrics, 0, 3, metric_array_positions.clicks),
last_month: sumSheetValues(latest_metrics, 4, 7, metric_array_positions.clicks),
wow_delta: Math.round(metric_deltas[0][3]*100) + '%',
mom_delta: Math.round(metric_deltas[1][3]*100) + '%'
}
}
return metrics
}
function sumSheetValues(data, array_row_start, array_row_end, array_column) {
let result = 0
for(let i = array_row_start; i <= array_row_end; i++) {
result += data[i][array_column]
}
return result
}
Here's a sample Spreadsheet with data in the right cells that'll let you test out the script: Link to Google Sheet
Now that we have both of those, the final version of your weeklyMetricsSlackNotificationJob() function should look like this:
function weeklyMetricsSlackNotificationJob() {
let user_request = requestSlack("GET", "users.lookupByEmail", "?email=email@email.com")
console.log(`user: ${user_request.response_data}`)
let user_id = user_request.response_data.user.id
let conversationsOpen_payload = {
users: [user_id, "user_id2", "user_id3"]
}
let conversation_request = requestSlack("POST", "conversations.open", conversationsOpen_payload)
let conversation_id = conversation_request.response_data.channel.id
let metrics = getLatestMetrics();
let postMessage_payload = {
text: `*Metrics Update* \n *_Weekly_* _(${metrics.period.week.start} - ${metrics.period.week.end})_` +
`\n- Queries: ${metrics.queries.this_week} (${metrics.queries.wow_delta})` +
`\n- First Page: ${metrics.first_page.this_week} (${metrics.first_page.wow_delta})` +
`\n- Impressions: ${metrics.impressions.this_week} (${metrics.impressions.wow_delta})` +
`\n- Clicks: ${metrics.clicks.this_week} (${metrics.clicks.wow_delta})` +
`\n \n *_Monthly_* _(${metrics.period.month.start} - ${metrics.period.month.end})_` +
`\n- Queries: ${metrics.queries.this_month} (${metrics.queries.mom_delta})` +
`\n- First Page: ${metrics.first_page.this_month} (${metrics.first_page.mom_delta})` +
`\n- Impressions: ${metrics.impressions.this_month} (${metrics.impressions.mom_delta})` +
`\n- Clicks: ${metrics.clicks.this_month} (${metrics.clicks.mom_delta})`
,
channel: conversation_id
}
let post_message_request = requestSlack("POST", "chat.postMessage", postMessage_payload)
}
🔔 Subscribe for more videos just like this: https://www.youtube.com/c/BootstrappingTools
🎉Are you interested in a Bootstrapping Tools Community? Let me know! https://forms.gle/4tzvffHiRqS7pVZY6 🎉
