Analyze data from a log, using a Clearbit company tracker

If you are using dashdash to create a data log by grabbing the data from a service periodically and then using INSERT, you will want to analyze this data. Let’s check out one way how to do this.

1) Grab the data
We build this App as a company tracker with data from Clearbit. You need an API key from them to activate the integration.

In our App, we want to grab data from Clearbit daily, so every 1440 minutes. So we create a control cell and a daily trigger if the control is switched on:

A1: Control
B1: 1
C1: =IF(B1=1,REPEAT(1,1440))

Now we create the output of our daily request:

To explain row 4:

C4: =PARSE(B4,"metrics.employees")
D4: =PARSE(B4,"facebook.likes")
E4: =PARSE(B4,"twitter.followers")
F4: =IF($C$1=1,TODAY())

We copy and paste it into rows 5 and 6.

2- Create the log
To create the log, we write the titles of the columns whose data we want to log in row 13. Then we use the INSERT function in G4:G6 to insert the values of the request into the table below row 13 (see details on the INSERT function here.).

G4: =IF($C$1=1,INSERT($A$3:$F$3,A4:F4),$A$13:$E$13)

If this is properly set up, we create a log as shown below.


3- Analyze the log data
Now we want to analyze the data that’s in the log. We want to use the most recent data that’s being added to the bottom of the log and compare it to less recent data, e.g. to calculate a growth rate of Microsoft’s Twitter followers.

Usually, we’d do this in a different View, but as referencing between Views is still cumbersome, let’s do it in the same (we conveniently left some space in rows 7-11).

To explain row 9:

B9: =SUMIFS($B$14:$B,$A$14:$A,A9,$E$14:$E,F4)
C9: =SUMIFS($B$14:$B,$A$14:$A,A9,$E$14:$E,F4-1)
D9: =B9/C9-1
E9: =SUMIFS($C$14:$C,$A$14:$A,A9,$E$14:$E,F4)
F9: =SUMIFS($C$14:$C,$A$14:$A,A9,$E$14:$E,F4-1)
G9: =E9/F9-1
H9: =SUMIFS($D$14:$D,$A$14:$A,A9,$E$14:$E,F4)
I9: =SUMIFS($D$14:$D,$A$14:$A,A9,$E$14:$E,F4-1)
J9: =H9/I9-1

So you just look up the values for the company name in A14:A and the time stamp in E14:E. In this example, we simple compare today’s with yesterday’s value. We can obviously compare any period we want.

As an aside: Even though we don’t use this here, you may want to count the entries for a company in the log. If so, you can add this in column F:

F14: =COUNTIF(A$14:A14,A14)
F15: =COUNTIF(A$14:A15,A15)

So our entire App looks like this:

In case you don’t want to use our integration for this, you can create the request with our GET formula:

B4: =IF($C$1=1,GET(""&A4,'{"Authorization": "Bearer [YOUR API KEY]"}'))