使用Excel中的PowerQuery进行广告URL编码及解码

今天带大家一起学习如何通过Excel商务智能插件PowerQuery中的两个函数快速建立广告投放中URL跟踪标记的方法。

在介绍编码解码之前，我们先看下一般互联网常用的编码规则，我们百度搜索商务智能，我们看到Tableau的广告，我们点击链接，如下所示

https://www.tableau.com/zh-cn/asset/2018-top-10-business-intelligence-trends?utm_campaign=Prospecting-BI-ALL-ALL&utm_medium=Paid+Search&utm_source=Baidu&utm_language=CN&utm_country=GrCHINA-CN&kw=%E5%95%86%E5%8A%A1%E6%99%BA%E8%83%BD&adgroup=gen-BI&ag_kwid=17202-3-f5dc4341c080ef11.eb4d089fd7aac412

编码说明

1、”?”之前的部分为URL部分我们比较熟悉，不做过多介绍：https://www.tableau.com/zh-cn/asset/2018-top-10-business-intelligence-trends

2、”?”之后部分即我们的标记跟踪区域：utm_campaign=Prospecting-BI-ALL-ALL&utm_medium=Paid+Search&utm_source=Baidu&utm_language=CN&utm_country=GrCHINA-CN&kw=%E5%95%86%E5%8A%A1%E6%99%BA%E8%83%BD&adgroup=gen-BI&ag_kwid=17202-3-f5dc4341c080ef11.eb4d089fd7aac412

我们分解可以看到，都是以一个参数名称，一个参数内容的方式，如：utm_campaign=Prospecting-BI-ALL-ALL，然后多个参数之间联系起来的，我们在PowerQuery中看下标记的总体属性：

我们可以看到8个参数及参数对应的内容

软件需求

16版Excel（专业增强版及以上版本）或者安装了PowerQuery的10/13版Excel

注：需要注意公式大小写

编码需要用到的两个函数

1、Uri.BuildQueryString(编码的记录)

功能：编码，建立查询字符串，将记录Query汇入查询URI字符串，根据需要转义字符。

参数：一个Record参数，即一个记录，我们可以理解为如下即可：[a=”1”,b=”2”]这种形式

官方介绍：

2、Uri.Parts(提取编码各部分，可自动解码URL编码)

如公式：=Uri.Parts(“http://powerbi.cc/excel/?kid=13239&kw=sem“)

返回结果：

PowerQuery中显示如下：

编码实战

1、我们准备我们的测试数据，将参数分列展示出来，如下所示：

2、我们将数据加载到PowerQuery界面：

3、我们点击PowerQuery中的【添加列】【自定义列】开始书写公式：

4、输入公式：

5、点击确定，我们编码即完成：

6、点击【开始】【关闭并上载】，将数据加载到Excel文件中

快速解码

其实一个简单的函数就为我们完成了编码的整个工作，那么解码如何完成呢？我们虚拟一列网址一起看看吧：

1、我们将虚拟的URL列用前边的方法加载到PowerQuery中

2、使用添加列输入公式：

//注：[URL]为我们的网址列

我们看到公式返回一个Record，其实我们的编码就被存储在了[Query]列

3、我们点击添加列上的两个扩展箭头，选中Query列

4、我们接着展开Query列，我们看到我们所有标记就被我们解码提取出来了，接着我们把表格加载到Excel中即可

通过今天的分享，我们可以根据各媒介的实际需求，制作一个全智能的Excel编码模板。后续我们分析编码的URL中也能快速提取参数进行相关分析，更多问题欢迎大家留言咨询。