let
fn = (账户,密码,秘钥,开始日期 as date ,结束日期 as date) =>
let
zd = 0,
url="https://api.baidu.com/json/sms/service/ReportService/getRealTimeData",
content="{
""header"":{
""username"":"""&账户&""",
""password"":"""&密码&""",
""token"":"""&秘钥&"""
},
""body"":{
""realTimeRequestType"":{
""performanceData"":[
""impression"",
""click"",
""cost"",
""cpc"",
""ctr""
],
""levelOfDetails"":3,
""reportType"":5,
""startDate"":"""&Date.ToText(开始日期,"yyyy-MM-dd")&""",
""endDate"":"""&Date.ToText(结束日期,"yyyy-MM-dd")&""",
""device"":"""&Number.ToText(zd)&""",
""untilOfTime"":5
}
}
}",
request=Json.Document(Web.Contents(url,[Content=Text.ToBinary(content)])),
data = Table.FromRecords(request[body][data]),
title = {{"展现","点击","消费","平均点击价格","点击率"},{"账户","计划","省","市"}},
kpi = Table.TransformColumns(request[body][data],{"kpis",each Record.FromList(,title{0})}),
name = Table.TransformColumns(kpi,{"name",each Record.FromList(,title{1})}),
expand_kpi = Table.ExpandRecordColumn(name, "kpis", title{0}),
expand_name = Table.ExpandRecordColumn(expand_kpi, "name", title{1}),
delete = Table.RemoveColumns(expand_name,{"平均点击价格", "点击率"}),
types = Table.TransformColumnTypes(delete,{{"展现", Int64.Type}, {"点击", Int64.Type}, {"消费", type number}, {"计划", type text},{"date", type date}})
in
request[body][data]
in fn
