使用工具
power bi 或Excel
实现效果
实时获取百度推广账户详情(账户 计划 单元 )等消费状况
实现代码
let fn = (账户,密码,秘钥,开始日期 as date ,结束日期 as date, optional 媒介 as text,optional 类别 as text) => if 类别="关键词" then let zd = if 媒介="WAP" then 2 else if 媒介="PC" then 1 else 0, url="https://api.baidu.com/json/sms/service/ReportService/getRealTimeData", content="{ ""header"":{ ""username"":"""&账户&""", ""password"":"""&密码&""", ""token"":"""&秘钥&""" }, ""body"":{ ""realTimeRequestType"":{ ""performanceData"":[ ""impression"", ""click"", ""cost"", ""cpc"", ""position"", ""ctr"" ], ""levelOfDetails"":11, ""reportType"":14, ""startDate"":"""&Date.ToText(开始日期,"yyyy-MM-dd")&""", ""endDate"":"""&Date.ToText(结束日期,"yyyy-MM-dd")&""", ""device"":"""&Number.ToText(zd)&""", ""untilOfTime"":5, ""number"":10000 } } }", request=Json.Document(Web.Contents(url,[Content=Text.ToBinary(content)])), data = Table.FromRecords(request[body][data]), title = {{"展现","点击","消费","平均点击价格","平均排名","点击率"},{"账户","计划","单元","关键词"}}, kpi = Table.TransformColumns(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}}), shuju = Table.AddColumn(try types otherwise Table.FromRows({{null, null, null,null, null,null,null, null} },{"账户", "计划", "id","展现","点击","消费","平均排名","date"}), "媒介", each if 媒介="WAP" then "WAP" else if 媒介="PC" then "PC" else "-") in shuju else let zd = if 媒介="WAP" then 2 else if 媒介="PC" then 1 else 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"":10, ""startDate"":"""&Date.ToText(开始日期,"yyyy-MM-dd")&""", ""endDate"":"""&Date.ToText(结束日期,"yyyy-MM-dd")&""", ""device"":"""&Number.ToText(zd)&""", ""untilOfTime"":5, ""number"":10000 } } }", request=Json.Document(Web.Contents(url,[Content=Text.ToBinary(content)])), data = Table.FromRecords(request[body][data]), title = {{"展现","点击","消费","平均点击价格","点击率"},{"账户","计划"}}, kpi = Table.TransformColumns(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}}), shuju = Table.AddColumn(try types otherwise Table.FromRows({{null, null, null,null, null,null, null} },{"账户", "计划", "id","展现","点击","消费","date"}), "媒介", each if 媒介="WAP" then "WAP" else if 媒介="PC" then "PC" else "-") in shuju, fnType = type function (账户 as text ,密码 as text ,秘钥 as text ,开始日期 as date ,结束日期 as date, optional 媒介 as(type text meta [ Documentation.FieldCaption = "终端:PC 或 WAP ; 默认为空,不分终端", Documentation.FieldDescription = "终端:填写:PC 或 WAP ; 默认为空,不分终端", Documentation.AllowedAllowedvalues={"PC","WAP","汇总"} ] ), optional 类别 as(type text meta [ Documentation.FieldCaption = "类别:关键词 或 计划 ; 默认为计划", Documentation.FieldDescription = "类别:关键词 或 计划 ; 默认为计划", Documentation.AllowedAllowedvalues={"计划","关键词"} ] ) ) as list meta[] in Value.ReplaceType(fn ,fnType)