We wind up with too many messages that get Skipped for one reason or another. And we don’t have time to research each one at the Profile level. To improve our messaging and re-messaging we need to know why a Profile got skipped, punted, dropped, lost, whatever.
We built a Power Query in Excel using the proper metric_IDs (i.e. filter = "equals(metric_id,'XXXXXX')" // Dropped Email), the columns are created but the values are null. A quick trip to ChatGPT and we were told that Klaviyo has three different metric levels and the ones we need aren’t available. Which raised another question, why publish a metric list if they are available for use?
I think it’s because we aren’t handling the calls right. The M Code we are using for this particular metric is below. Anyone that can point us in the right direction would highly esteemed and revered eternally. OK, maybe just a day or two, but still…..
************************************************************************
let
// =========================
// PARAMETERS (best practice)
// =========================
apiKey = pKlaviyoPrivateKey,
// =========================
// CONSTANTS
// =========================
BaseUrl = "https://a.klaviyo.com",
PageSize = 100,
Headers = [
Authorization = "Klaviyo-API-Key " & apiKey,
accept = "application/vnd.api+json",
revision = "2025-01-15"
],
// =========================
// FIRST REQUEST (EVENTS)
// =========================
FirstRequest = [
RelativePath = "/api/events/",
Query = [
#"page[size]" = Text.From(PageSize),
// placeholder — replace METRIC_ID later
filter = "equals(metric_id,'R25rRH')" // Dropped Email
]
],
// =========================
// FETCH ONE PAGE
// =========================
GetPageByRequest = (req as record) as record =>
let
Raw =
Web.Contents(
BaseUrl,
[
RelativePath = req[RelativePath],
Query = req[Query],
Headers = Headers
]
),
Json = Json.Document(Raw)
in
Json,
// =========================
// NEXT PAGE HANDLER
// =========================
NextUrlToRequest = (nextUrl as nullable text) as nullable record =>
let
Out =
if nextUrl = null or nextUrl = "" then
null
else
let
Parts = Uri.Parts(nextUrl),
P = try Parts[Path] otherwise null,
Q = try Parts[Query] otherwise null
in
if P = null then null else [RelativePath = P, Query = if Q = null then [] else Q]
in
Out,
// =========================
// PAGINATION
// =========================
GetAllPages = (req as record, optional acc as list) as list =>
let
Page = GetPageByRequest(req),
Data = try Page[data] otherwise {},
Links = try Page[links] otherwise [],
NextUrl = try Links[next] otherwise null,
NextReq = NextUrlToRequest(NextUrl),
NewAcc = if acc = null then Data else List.Combine({acc, Data}),
Result = if NextReq <> null then @GetAllPages(NextReq, NewAcc) else NewAcc
in
Result,
RawList = GetAllPages(FirstRequest),
// =========================
// LIST → TABLE
// =========================
EventsTable =
Table.FromList(
RawList,
Splitter.SplitByNothing(),
{"event_record"},
null,
ExtraValues.Error
),
// =========================
// EXPAND CORE EVENT FIELDS
// =========================
Expanded =
Table.ExpandRecordColumn(
EventsTable,
"event_record",
{"id", "type", "attributes"},
{"event_id", "event_type", "attributes"}
),
ExpandedAttr =
Table.ExpandRecordColumn(
Expanded,
"attributes",
{"datetime", "properties", "profile_id", "metric_id"},
{"event_datetime", "properties", "profile_id", "metric_id"}
),
// =========================
// SCHEMA-RESILIENT PROPERTIES
// =========================
DesiredProps = {
"flow_id",
"flow_name",
"message_name",
"action_id",
"action_type",
"reason",
"skip_reason",
"email",
"phone_number",
"campaign_name"
},
ExistingPropNames =
List.Intersect({
DesiredProps,
List.Union(
List.Transform(
Table.Column(ExpandedAttr, "properties"),
each if _ is record then Record.FieldNames(_) else {}
)
)
}),
ExpandedProps =
if List.Count(ExistingPropNames) > 0 then
Table.ExpandRecordColumn(
ExpandedAttr,
"properties",
ExistingPropNames,
ExistingPropNames
)
else
ExpandedAttr,
AddMissingProps =
List.Accumulate(
List.Difference(DesiredProps, ExistingPropNames),
ExpandedProps,
(state as table, col as text) => Table.AddColumn(state, col, each null)
),
Clean =
if List.Contains(Table.ColumnNames(AddMissingProps), "properties")
then Table.RemoveColumns(AddMissingProps, {"properties"})
else AddMissingProps,
// =========================
// REFRESH TIMESTAMP (UTC)
// =========================
AddRefreshTimestamp =
Table.AddColumn(
Clean,
"Refresh_Timestamp",
each DateTimeZone.UtcNow(),
type datetimezone
),
// =========================
// SANITY CHECK
// =========================
RowCount = Table.RowCount(AddRefreshTimestamp),
CheckedOutput =
if RowCount = 0 then
error "Klaviyo returned ZERO rows. Stopping refresh to protect dashboards."
else
AddRefreshTimestamp
in
CheckedOutput
**********************************************************************************
And the obligatory screenshot:
Any help would be greatly appreciated.
