Statusupdate. InstaLOC: Zusammenführung und Bereinigung (Duplicate!) der Datenbanken

Heute wurde der vorhandene Datenbestand aus den einzelnen Dateien zusammengeführt und entsprechend gespeichert.
Der Befehl für den technischen Vorgang ist:

INSERT INTO instaloc_main.locations(url,tag,likes,comments,erstellzeit,post_id,username,location,filter,pruefzeit) select url,tag,likes,comments,erstellzeit,post_id,username,location,filter,pruefzeit FROM _main_10112018.locations group by url;

Die Datenbank hat folgende Daten

Speicher: ca 30GB

Analysierbar sind:
49.676.379 Beiträge
36.574.115 Tagwolken
12.705.076 (codierte) Nutzer
1.794.336 Loations
23.358.582 Zeitstempel

Themenweltendatenbank „Tourismus & Urlaub“: aktueller Bestand

Die Datenbank (SQLite-Datei) hat nun folgende Kennzahlen:

Speichervolumen: ~13GB
Beiträge (Unique): 18.411.849
Tagwolken (Unique): 17.490.694
(codierte) Nutzer_innen: 6.180.654
Locations (Unique): 1.037.477
Zeitstempel (Unique): 9.165.025

Mit Stand 27.10.2018 wurden 120.000 von 176721 Abfragen über die API innerhalb von ca 2 Wochen mit einem täglichen Scrapingzeitraum i.H.v. 11Stunden durchgeführt.

Ich rechne mit einem Abschluss der Themenwelten-Erfassungsfunktionen zum kommenden Mittwoch (31.10.2018) und im nächsten Turnus widme ich mit den Themen „Kunst“, „Kultur“, „Mode“, „Lifestyle“. Hier rechne ich mit ~300.000 Abfragen.

InstaLOC, Datenauszüge / Freigabe (Stand: 22.10.2018)

Folgende (Roh)datenauszüge beschreiben den Umfang der gesammelten Informationen.

(1) Die Locations
export_locs_22102018.rar

(2) Die Zeitstempel
times.rar

(3) Die Tagwolken
export_tags_22102018.rar

Bitte beachten.
Die Locationsdatei umfasst 3 Pakete: Auflistung, Zuordnung zu den gesammelten Likes und Zuordnung zu den gesammelten Comments.
Die Zeitstempeldatei umfasst ein Paket.
Die Tagwolkendatei umfasst 3 Pakete: Auflistung, Zuordnung zu den aktiven User_innen und Zuordnung zu den gesammelten Likes.

Die entpackten CSV-Daten schwanken zwischen wenigen 100MB bis zu 5GB und man muss über entsprechende Parser / Ressourcen verfügen, um die Daten interpretieren zu können. Hier gilt: Support wird ausschließlich den Kund_innen und den (Geschäfts)freund_innen geleistet.

InstaLOC, Datenbestand – Überblick

Nach Zusammenführung der fehlerbereinigten Datensätze (Duplikate etc.) in eine Master-DB mit den Inhalten:

-> Städte aus dem RolandBerger-Projekt
-> div. deutsche Groß- und Kleinstädte
-> politische Analyse
-> Themenwelt „Home & Living“
-> 1/3 – Datenbank aus Themenwelt „Tourismus“

kommen wir nun auf den folgenden Bestand:

24.321.643 Beiträge
19.123.483 Tagwolken
8.031.350 (codierte) Nutzer_innen
1.084.069 Locations
14.540.475 Zeitstempel

Die Datenbankdatei ist umfasst ca. 13GB und wird je Durchlauf um 3-5GB wachsen. Der aktualisierte Datenbestand wird zeitnah an die W&L GbR Partner_innen verteilt.

InstaLOC, Zwischenfreigabe der Themendatenbank „Tourismus“

Die aktuelle Datenbank umfasst die Ergebnisse aus 23575 Abfragen und speichert folgende Daten:

4.546.235 Beiträge
4.376.548 Tagwolken
2.031.490 codierte User
380.723 Locations
2.674.339 Zeitstempel

Datenauszüge (unverschlüsselt)

(1) Auflistung Tagwolken und deren Locations
Datei: exp_urlaub_tag_loc.zip
Dateivolumen: ZIP->339MB, Entpackt->1.6GB

(2) Auflistung der Locations und Aufsummierung der Likes JE Location
Datei: exp_urlaub_like_locs.zip
Dateivolumen: ZIP->13MB, Entpackt->42MB

(3) Auflistung Tagwolken (ungefiltert!) und deren Likes
Datei: exp_urlaub_tag_likes.zip
Dateivolumen: ZIP->168MB, Entpackt->600MB

(4) Auflistung der Locations + Postingzeitstempel + Likes
Datei: exp_urlaub_loc_time_likes.zip
Dateivolumen: ZIP->52MB, Entpackt->265MB

(5) Auflistung der Locations + Aufsummierung der Postings
Datei: exp_urlaub_postings_locs.zip
Dateivolumen: ZIP->12MB, Entpackt->42MB

(6) Auflistung der Tagwolken + Aufsummierung der User, welche die Wolken verwendeten
Datei: exp_urlaub_tags_user.zip
Dateivolumen: ZIP->333MB, Entpackt->1.5GB

(7) Auflistung der Locations + Aufsummierung der User, welche an den Locations aktiv waren
Datei: exp_urlaub_loc_user.zip
Dateivolumen: ZIP->13MB, Entpackt->41MB

Die aufgelisteten Datenauszüge geben einen ersten und sehr guten Überblick.
Im Bezug zu den erfassten Datensätzen sind weitere Nachforschungen die Beantwortung hochfaszinierender Fragen, wie:

(a) Wieviele User schreiben in welchem Zeitfenster über den Urlaub an welchen Orten?
(b) Welche Orte werden an Hotspots von welchen Usergruppen besucht und was wird da geschrieben?
(c) Welche User / Usergruppen neigen zur Trendbildung an welchen Orten und / oder zu welchen Tags (Themen, Themenwelten)?
(d) Welche Orte werden zu welchen Zeitfenstern|Jahreszeiten|Monaten besonders stark frequentiert und was wird da geschrieben?

möglich.

Die Anwendungsszenarien sind vielfältig. Ich denke an die folgenden Punkte:

-> Influenceraccountanalysen
-> Schnittstellen zu Socialmediastrategien inkl. Hashtag- und Postzeitplanungen
-> Schnittstellen zu div. Textanalysetools mit Anbindung an WDF*IDF / KWD-Analysen
-> gezielte Steuerung von ortsbezogenen Werbeanzeigen|Flyeraktionen|Plakataktionen|sonst.Promomaßnahmen
-> Gegenprobe auf die üblichen Auswertungstools aus der Socialmedia
-> gezielte Steuerung von ortsbezogenen Werbeanzeigen in den Suchmaschinen, den Socialmedia

Interesse am Austausch? Fragen? Anfragen?
Konkretes Projektangebot?
Ich freue mich auf Rückmeldung unter der eMail: office(at)pontipix.de

Projekt „InstaLOC“ – Update der Scrapertechnologie + Bugfixing

Während der letzten Datendurchläufe stellte ich immer wieder fest, dass der Scraper den Arbeitsspeicher zu stark beanspruchte. Konkret bedeutet das, dass ca. 250 Abfragen an die API einen Scrapingabbruch und einen RAM-Verbrauch i.H.v. 1,8GB (!) provozierte.

Dieses Problem wurde nun final über die Quellcodeanpassung
jsonvalue.Free;
JSON.Free;

gelöst.

Der Speicherverbrauch wurde von 1.8GB auf ca. 100MB / je 250 Abfragen reduziert.

Die fehlerbereinigte Prozedur ist:
procedure TForm1.getmedia_db(mytable: TStringGrid; tagsearch: string; rounds: integer);
var
JSONArray: tJSONArray;
JSONValue,jvalue: tJSONValue;
JSONPair: TJSONPair;
JSON, json_sub: TJSONObject;
size: integer;
j_array: tJSONArray;
s: string;
i,j: integer;
next_id: string;
zaehl: integer;
url,tag,likes,comments,post_id,username,location,filter,pruefzeit: widestring;
erstellzeit: string;
gedoens: TIdHTTP;
zw_db: tmemo;
begin
gedoens := TIdHTTP.Create;
zw_db :=tmemo.Create(self);
sql_befehle.Clear;
try
zw_db.text:=gedoens.Get('https://api.instagram.com/v1/tags/'+escape(tagsearch)+'/media/recent?access_token='+token.text);
JSONValue := TJSONObject.ParseJSONValue(zw_db.text);
JSON := TJSONObject.ParseJSONValue(zw_db.Lines.Text) as TJSONObject;
JSONArray := TJSONArray(JSON.Get('data').JsonValue);
try next_id:= JSONValue.GetValue('pagination.next_url');
except
//-> hier user, locations etc. einbauen!!!
next_id:='N/A';
end;

for i := 0 to JSONArray.Size - 1 do
begin
url:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('link')).JsonValue.Value);
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('tags')).ToString);
s:= StringReplace(s, '"tags":[', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, ']', '', [rfReplaceAll,rfIgnoreCase]);
tag:=escape(s);
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('likes')).ToString);
s:= StringReplace(s, '"likes":{"count":', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '}', '', [rfReplaceAll,rfIgnoreCase]);
likes:=s;
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('comments')).ToString);
s:= StringReplace(s, '"comments":{"count":', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '}', '', [rfReplaceAll,rfIgnoreCase]);
comments:=s;
erstellzeit:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('created_time')).JsonValue.Value);
erstellzeit:=datetimetostr(UnixToDateTime(strtoint(erstellzeit)));
post_id:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('id')).JsonValue.Value);
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('user')).ToString);
s:= StringReplace(s, '"user":{"username":', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '}', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '"', '', [rfReplaceAll,rfIgnoreCase]);
username:=s;
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('location')).ToString);
s:= StringReplace(s, '"location":', '', [rfReplaceAll,rfIgnoreCase]);
location:=s;
filter:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('filter')).JsonValue.Value);
pruefzeit:=datetimetostr(now);
with sql_befehle.Lines do
begin
add('INSERT INTO `locations` (`url`, `tag`, `likes`, `comments`, `erstellzeit`, `post_id`, `username`, `location`, `filter`, `pruefzeit`) Values ('''+url+''', '''+tag+''', '''+likes+''', '''+comments+''', '''+erstellzeit+''', '''+post_id+''', '''+username+''', '''+location+''', '''+filter+''', '''+pruefzeit+''');');
end;
end;
fdquery3.ExecSQL(sql_befehle.text);
sql_befehle.Clear;
// sql_befehle.Free;
except
tr_break:='1';
end;

// -> Speicherfreigabe
jsonvalue.Free;
JSON.Free;
if next_id<>'N/A' then
begin
repeat
// -> tiefenpruefung
if next_id='N/A' then
break;
delay(strtoint(frequenz1.Text));
try
zw_db.text:=gedoens.Get(next_id);
JSONValue := TJSONObject.ParseJSONValue(zw_db.text);
JSON := TJSONObject.ParseJSONValue(zw_db.Lines.Text) as TJSONObject;
JSONArray := TJSONArray(JSON.Get('data').JsonValue);
try next_id:= JSONValue.GetValue('pagination.next_url');
except
next_id:='N/A';
break;
end;
for i := 0 to JSONArray.Size - 1 do
begin
url:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('link')).JsonValue.Value);
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('tags')).ToString);
s:= StringReplace(s, '"tags":[', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, ']', '', [rfReplaceAll,rfIgnoreCase]);
tag:=escape(s);
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('likes')).ToString);
s:= StringReplace(s, '"likes":{"count":', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '}', '', [rfReplaceAll,rfIgnoreCase]);
likes:=s;
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('comments')).ToString);
s:= StringReplace(s, '"comments":{"count":', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '}', '', [rfReplaceAll,rfIgnoreCase]);
comments:=s;
erstellzeit:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('created_time')).JsonValue.Value);
erstellzeit:=datetimetostr(UnixToDateTime(strtoint(erstellzeit)));
post_id:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('id')).JsonValue.Value);
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('user')).ToString);
s:= StringReplace(s, '"user":{"username":', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '}', '', [rfReplaceAll,rfIgnoreCase]);
s:= StringReplace(s, '"', '', [rfReplaceAll,rfIgnoreCase]);
username:=s;
s:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('location')).ToString);
s:= StringReplace(s, '"location":', '', [rfReplaceAll,rfIgnoreCase]);
location:=s;
filter:=(TJSONPair(TJSONObject(JSONArray.Get(i)).Get('filter')).JsonValue.Value);
pruefzeit:=datetimetostr(now);
with sql_befehle.Lines do
begin
add('INSERT INTO `locations` (`url`, `tag`, `likes`, `comments`, `erstellzeit`, `post_id`, `username`, `location`, `filter`, `pruefzeit`) Values ('''+url+''', '''+tag+''', '''+likes+''', '''+comments+''', '''+erstellzeit+''', '''+post_id+''', '''+username+''', '''+location+''', '''+filter+''', '''+pruefzeit+''');');
end;
end;
fdquery3.ExecSQL(sql_befehle.text);
sql_befehle.Clear;
except
tr_break:='1';
break;
end;
// -> Speicherfreigabe
jsonvalue.Free;
JSON.Free;
// -> tiefenpruefung, ende
zaehl:=zaehl+1;
until zaehl=rounds;
end;
form1.Caption:=version+' alle Posts herunter geladen';
gedoens.free;
zw_db.Free;
fdquery.Free;
end;

Projekt InstaLOC, Aktuelle Statistiken

Der aktuelle Datenbestand umfasst:

587.903 Locations inkl. Geocodes, IDs und Namen
12.669.502 Unique Tagclouds
5.633.457 Beiträge ohne Locationzuordnung
8.516.617 Beiträge mit Locationzuordnung
4.863.200 codierte User
10.700.170 Unique Zeitstempel (Beiträge)

Die Masterdatenbank umfasst alle erfassten Datensätze und die Scrapingtechnologie schafft das genannte Volumen innerhalb von 3-4 Werktagen.

InstaLOC: Freigabe der ersten Themendatenbank (Home&Living)

Gestern veranlasste ich die Freigabe der ersten Datenbank, welche den DACH-Raum und Teile der EU nach den Themenwelten der Sparte „Home & Living“ abbildet.

Die Grunddaten der SQLite-Datei sind:

Speicher: ca. 5GB
Beiträge: 8.532.446
User_innen: 2.495.311
Locations: 473.903
Medienzeitstempel: 4.863.183

Interessierte Leser_innen des Arbeitsblogs können sich zu der Datenbank unter den folgenden Auszügen weitergehende Eindrücke verschaffen:

(1) Locationliste + Sum(Postings), unverschlüsselt
https://drive.google.com/open?id=18rVNt2zTfKBmUl606O-L1mMsExvhI4iH
(2) Locationliste + Sum(Likes), unverschlüsselt
https://drive.google.com/open?id=1S3QMkwyo82w3SkH7YhrlZoixZRNlsSiI
(3) Medienpostzeitstempel, unverschlüsselt
https://drive.google.com/open?id=1EtRVSRuFhI5oyE9s6KHBcGKrDF_ARMRv
(4) Tagwolkenliste, unverschlüsselt
https://drive.google.com/open?id=14tXw59I3ky7_bbQ5znG_D4dSGo0rYfkw

Hinweise:
Hinter den CSV-Dateien stehen entsprechende SQL-Statements, die in diesem Blog dokumentiert wurden und dokumentiert werden. Die Datensätze sind unkategorisiert und nicht interpretiert: sie befinden sich quasi in einem Rohdatenzustand.

Supportanfragen, Interpretationsanfragen oder Anfragen zur Strategieableitung bitte an:
Anne Lehmann: office(at)pontipix.de (Wobus & Lehmann GbR), Preisliste unter: https://www.pontimania.de/
Dr. Klaus Holthausen: https://www.linkedin.com/in/dr-klaus-holthausen-1464b043/

Notiz: Diverse SQL-Abfragen für den Komplex „InstaLOC“

(A) Statistiken, Allgemeines
(1) Komplettüberblick
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations;

(2) Überblick nach Locationfilter
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations where location like '%germany%';

(3) Überblick nach Tagfilter
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations where tag like '%hausbau%'

(4) Überblick nach Tag- und Locationfilter
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations where (tag like '%hausbau%') and (location like '%germany%');

(5) Auflistung der Nutzer_innen mit Summe der „produzierten“ Likes
select distinct(username) , Anz_Likes from(
Select username, sum(likes) as Anz_likes from locations GROUP BY username ) as my_table order by Anz_likes DESC;

(6) Auflistung der Nutzer_innen mit Summe der verbundenen Locations, absteigend
select distinct(username) , Anz_Locations from(
Select username, count(location) as Anz_Locations from locations GROUP BY username ) as my_table order by Anz_Locations DESC;

(7) Auflistung der Nutzer_innen+Location zzgl. der Medienanzahl (Summe), absteigend
select username , location , Anz_Locations from(
Select username, location, count(location) as Anz_Locations from locations GROUP BY username ) as my_table where location like '%berlin%' order by Anz_Locations DESC;

(8) Tiefenprüfung auf einen Useraccount+Anzahl-Postings/Locations, absteigend
select username , location , Anzahl_Postings from(
Select username, location, count(location) as Anzahl_Postings from locations GROUP BY username ) as my_table where (location like '%berlin%') and (username = 'share') order by Anzahl_Postings DESC;

(9) Stadtrecherchen aus den Themenweltendatenbanken
select location from locations where (location like '%titude":51.339%') and (location like '%itude":12.377%')
Hinweise:
– Beispiel ist Leipzig
– weitere Städte unter http://www.fwiegleb.de/geo-a.htm

(10) Bereinigte Ausgabe der Tagwolken
select distinct(REPLACE(tag,'%22','"')) as Tagwolke from locations;

(11) Bereinigte Ausgabe der Locations inkl. Aufsummierung der Likes
SELECT distinct(location) as Orte, Anz_Likes from(
Select location, sum(likes) as Anz_likes from locations GROUP BY location ) as my_table order by Anz_Likes DESC;

(12) Bereinigte Ausgabe der Locations inkl. Aufsummierung der Postings
SELECT distinct(location) as Orte, Anz_User from(
Select location, count(username) as Anz_User from locations GROUP BY location ) as my_table order by Anz_User DESC

Hinweise:
– Durch die DB-Struktur werden die Beiträge „username“ zugeordnet und „username“ = „Beitrag“.

(B) Recherchen: Tagwolken
(1) Auflistung aller Tagwolken mit Aufsummierung der Likes, absteigend

SELECT distinct(tag) as Tagwolke, Anz_Likes from(
Select tag, sum(likes) as Anz_likes from locations GROUP BY tag ) as my_table order by Anz_Likes DESC

Hinweise:
(a) Bei Recherche nach den Comments, kann „likes“ zu „comments“ ausgetauscht werden.
(b) Die Abfrage listet alle Tagwolken – ungefiltert – auf und sortiert die Ergebnisse nach der Summe der Likes, welche durch die Tagwolken „produziert“ wurden.

(2) Auflistung der Tagwolken nach Suche mit Aufsummierung der Likes, absteigend

SELECT tag as Tagwolke, Anz_Likes from(
Select tag, sum(likes) as Anz_likes from locations GROUP BY username ) as my_table where tag like '%leipzig%' order by Anz_Likes DESC

Hinweise:
(a) Bei Recherche nach den Comments, kann „likes“ zu „comments“ ausgetauscht werden.
(b) Die Abfrage listet alle Tagwolken – ungefiltert – auf und sortiert die Ergebnisse nach der Summe der Likes, welche durch die Tagwolken „produziert“ wurden.

InstaLOC: Etablierung der Themenwelten-Datenbanken und Datenauszüge aus dem Komplex „Home & Living“

Inhalte der Datenbank: ca. 12.000 * 2000 Beiträge aus „Home&Living“-bezogenen Themenabfragen
Speichergröße der Datenbank: ca. 1.6GB, Format: SQLite

Beispielrohdatensätze
Abfrage via SQLiteStudio
select REPLACE(tag,'%22','"') as Tagwolke, likes, comments, location, erstellzeit from locations where tag like '%[keyword]%'

Datenlinks (CSV)
(1) Tagwolke: Carport.csv (2MB)
(2) Tagwolke: Hausbau.csv (gepackt: 10MB, entpackt: 64MB)
(3) Tagwolke: Haus.csv (gepackt: 27MB, entpackt: 147MB)

CSV-Schema
Tagwolke|Likes|Coments|Location(Geocode+ID+Titel)|Erstellzeitstempel

Allgemeine Datensätze
(1) Liste: Locations.csv (gepackt: 7MB, entpackt: 23MB)

Hinweise
Durch die modifizierte Abfrage wurde die Tagwolke etwas lesbarer gemacht. Man muss bei der Sichtung und Prüfung jedoch die Umlaute und Piktogramme bei Bedarf noch umwandeln, oder eben auf unseren Client zurückgreifen. Die Beispieldatensätze beinhalten nicht die Postingurl, Fotofilter und Usernames und erlauben daher keinerlei Rückschlüsse auf eventuelle Influencer. Diese Angaben werden bei Buchung der kostenpflichtigen Angebote (Workshops, Datenabfragen etc.) offen gelegt und – bei Bedarf – erklärt.