1. 出海笔记首页
  2. 推广

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

这篇文章对提高优化师效率非常有帮助,强烈推荐。(建议pc端打开方便对其中链接及代码进行操作)

 

Google Colaboratory(python)
Google data studio
Google sheet
通过上述三个免费的工具制作自动更新展现小时层级数据的BI系统。

 

本文导航:

  1. 工具介绍

  2. 代码及工具设置步骤

  3. 如何完全自动化及代码链接

 

 

下面简单介绍一下上述三个工具的作用以及为什么使用他们:

Google Colaboratory:

https://colab.research.google.com/

稍微了解python+jupyter的朋友可以理解为一个网页版内置了python2/3以及几乎所有libiray的编译器,主要用来做数据分析和机器学习。刚接触的朋友可以理解为,你如果想写python 但不知道要下载什么软件,如何设置环境之类的话,那么打开它的网址就可以开写了。它可以用本地文件,google文件,bq等很多地方直接读取/存入数据,但是google data studio不在“很多地方”之内。

 

Google Data Studio:

https://datastudio.google.com/

2016年谷歌发布的一款线上数据可视化工具,它可以从google文件,bq,本地文件等内外部一共181个地方拉取数据并依据设定好的模型直接变成可视化的透视表/饼状图/散点图/走势图等形式。甚至包含了Facebook,amazon等竞争对手的借口,但唯独不能直接链接google colaboratory。如果你对tableau的价格望而却步的话,那么打开它就对了:

https://datastudio.google.com/

 

Google sheet:

https://drive.google.com/

线上版的excel,由于上述的两位同学彼此之间没有接口,所以为了实现全部线上操作,尽可能人为步骤解放双手,我选择用google sheet作为colaboratory 和 data studio的中转站。

 

总体的思路总结为:

  1. 在云盘里预先制作一个google sheet空表并命名(仅需初次操作),后续每次代码运行自动打开,清空前一个小时的数据,并把新数据写入(该步骤由google colaboratory实现,以下简称colab)

  2. 接下来我要有数据,可以是本地上传的,可以是通过api在不同地方拉出来的,把这些数据导入colab(该步骤由colab.python实现)

  3. 那么得到这个数据之后,需要对数据做一下清理变成想要的小时维度,以及去掉多余的列。清理好数据之后,把数据写入到中转站google sheet里(该步骤同样由colab.python+gspread实现)

  4. 在google data studio设置好可视化模型(仅需初次操作),后续全部自动更新(在不断网的情况下...)

 

接下来我们以上述4个步骤来按顺序进行讲解

 

 

 

步骤一:创建中转站 google sheet

 创建一个google sheet空表,大概耗时2分钟,打开google云盘:https://drive.google.com/drive/u/1/my-drive,新建sheet,双击表名命名。如图:

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

(步骤一结束)

 

步骤二:获得数据

这里的话要先分两个方向了

2.1 第一个方向是用api 拉取数据+
from threading import Timer
import time

来定时运行,不过由于不同平台api不同,所以不在这里做过多讨论,感兴趣对朋友可以case by case沟通。

#thanks Devon

 

 2.2 所以这里介绍一下第二种方式做过度:假设你已把原始数据下载到本地(请保证为UTF-8的csv格式),那么直接上传csv数据到colab做处理。这种方式也更使用大多数没有api方式的朋友。

从google colab里导入files库,并且利用里面uploaded功能把文件上传到google上。

从google colab里导入files库,并且利用里面uploaded功能把文件上传到google上。

 

from google.colab import auth
auth.authenticate_user()
#上述两行代码用来做谷歌账户授权的认证
from google.colab import files
uploaded = files.upload()
#从本地上传文件

 

如图,运行后会出现一个非常友好的选择文件窗口,点击后在弹窗里找到自己的excel再点确定便会自动上传,上传后完成后你会看到100%done的提示)

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

(弹出输入框点击上传)

 

 

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

(提示100%done后成功)

 

步骤三:处理数据并将数据写入事先创建的中转站google sheet中

3.1处理数据这里我们用世界上最好用的语言python以及python里对数据处理最好对库之一pandas,在此之前,我们看一下之前我们要处理对表格里都有什么。这张表里对时间格式并不是小时格式,并且除了红框里我需要的字段还有很多编号,xxx, aaa等我并不需要的字段,那么所有这些就是我要用python处理对东西。

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

import pandas as pd
#导入pandas库,并在下文用pd代表
df=pd.read_csv('ticket information.csv', header=0)
#用pd读取刚刚上传的表格,转换成dataframe格式,命名为df
df
#输入df,打印出来看看效果,后续请把这行删掉
如果everything is ok的话,那么你会发现本地的excel表就已经出现在colab里了。删掉df,继续。

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

df['ticket time'] = pd.to_datetime(df['ticket time],format='%Y-%m-%d %H:%M:%S')
#ticket time有点乱,所以我们要把他转换成datetime这种 ’年-月-日 时-分-秒‘ 格式
df['ticket time'] = df['ticket time'].dt.strftime('%H')
#因为我们要按小时数据,所以我们只把小时 h 输入到srttime去读取并更新ticket time列,同理你如果想要年月日层级,做相应替换就好

 
‍‍3.2上述代码已经处理好了ticket time到格式问题,接下来就是把我们想要的列摘出来,并生成一个utf-8的新csv表
hourlyreport=pd.DataFrame(df,columns=['ticket time','type','channel','clinet ID'])
#只摘出我们需要的几列,并将其命名为hourlyreport
hourlyreport.to_csv('hourlyreport.csv',encoding='utf-8',index=False) 
#将新生成的dataframe转换成hourlyreport.csv,utf-8 编译格式,index=flase意在去掉colab自动添加的行数标签


扩展:关于如何将生成的csv下载到本地,以及如何直接在里面做数据透视并生成图标和图片。

将生成的csv下载到本地:

from google.colab import files
files.download("hourlyreport.csv")

如何直接做数据透视并生成图片和报表

hourlyreport=pd.pivot_table(hourlyreport, index=['ticket time'],columns='type',values='type',aggfunc='count')
hourlyreport.plot(kind='bar')
hourlyreport

3.3回到正文,此时我们已经把数据报表处理好,接下来就是把它存放到google sheet里,并用google sheet与data studio联动了,那么如何把数据输出到google sheet里
from gspread_dataframe import get_as_dataframe, set_with_dataframe
import gspread
from oauth2client.client import GoogleCredentials
gc = gspread.authorize(GoogleCredentials.get_application_default())
sheet = gc.open('hourly information').sheet1
sheet.resize(rows=1)
sheet.resize(rows=30)
sheet.delete_row(1)
set_with_dataframe(sheet,hourlyreport,include_column_header = True,resize=True)
#thanks bob for helping me sort out the problem


这里前3行用来导入可以使google colab读写,修改,存入google sheet的代码库和验证。第5行代表打开提前已经创建的google sheet,双引号内为google sheet名字。6~8行以此实现把之前表格只缩短成一行,再生成30行,再删除第一行,这样就删掉了表格里之前的数据方便导入新数据并后续用data studio自动刷新。第9行将之前整理好的 hourlyreport 这个dataframe放入到google sheet。运行后可以打开你先前创建的google sheet,看看里面是否变成了这样:

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

对于数据导入步骤,之前尝试过import.sheet,数据确实会覆盖到新表,但是如果你没关掉表格的话会在浏览器上面看到sheet页面刷新了以此,实际上这样是生成一个新表来替换之前的表格。这样在后续data studio从数据源再刷新数据的时候就已经不认得之前的表格了,好下面我们说最后一步从sheet将数据导入到data studio中并自动刷新。

 

步骤四:将data studio与中转站google sheet链接,并搭建好可视化模版,并后续自动刷新数据
 

4.1 在datastudio中链接刚刚的google sheet,依次是:Resource-Manage added data sources-Google sheet-选定好特定的表-Connect

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

4.2 链接好后便是数据拼接的阶段,这里我们可以在data stuido里随意插入pivot,bar,line,geo map(没错如果你有国家数据,google会自动识别然后生成一张酷炫的地图,scratter,pivot table等。无论你插入什么样式,都会在右边看到你之前插入的数据源google sheet,只需要简单的拖动就可以让刚刚的图形里按你的数据显示了,这里的数据并不只是简单的图片,而是可以点击联动的,比如你只想查看某个小时或者某个channel或者某个type,只需要点击一下,面板就会刷新。
 
Dimension数据统计的维度,可以理解为pivot里的row,这里我们选的时间(小时)
Breakdown dimension:根据所选维度把dimension细分,比如我想看每小时不同channel给我带来的客户数量,那么这里选为channel
Metric:上面分为了维度,这里再放入你想根据不同维度看的值,如果是bar或者line,那么一般是看breakdown dimension里的数字,如果是pivot,可以继续插入字段,比如查看每小时不同渠道对于不同类型电影的贡献情况,可以再把type放在这里
Sort:排序

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

以上为一个示范,大家可以根据自己想要的情况来自由搭配, 比如我这里都做了电影类型饼状图,每小时客户购票数纵览,每小时不同类型电影购票情况,每小时不同推广渠道带来的客户数量,以及一个pivot,里面拆分了从小时-type的不同渠道表现。

搭建一个自动化BI查看每小时数据变化 via Python+Google sheet+Datastudio

最后说一下如何实现完全自动化:
  1. 有一个api定时可以拉取数据到colab里进行处理(case by case)

  2. datatudio已经联网就可以每15分钟自动刷新,而你的sheet1小时更新一次,so~

     

代码链接:

https://colab.research.google.com/drive/17_BnzjSZD76o5fW_BqJbLYRt9mSbSewO

作者:Alex 来源:Alex学习笔记

本文为作者独立观点,不代表出海笔记立场,如若转载请联系原作者。